网站建设与运营主营业务收入,网站建设能做什么工作,网络营销外包哪家好,重庆seo技术教程B树索引的使用
1 不同应用中B树索引的使用
在OLTP中#xff0c;B树索引建立后#xff0c;对该索引的使用应该只是通过该索引取得表中少部分的数据。这时建立B树索引才是有意义的#xff0c;否则即使建立了#xff0c;优化器也可能不选择使用索引。
在OLAP中#xff0c;…B树索引的使用
1 不同应用中B树索引的使用
在OLTP中B树索引建立后对该索引的使用应该只是通过该索引取得表中少部分的数据。这时建立B树索引才是有意义的否则即使建立了优化器也可能不选择使用索引。
在OLAP中如果是复杂的查询要涉及多张表之间的连接操作因此添加索引是有意义的。但是如果连接使用的hash join那么索引可能又变的不那么重要了。不过在OLAP中通常会需要对时间字段进行索引这是因为大多数统计需要根据时间维度来进行数据的筛选。
2 联合索引
联合索引是指对表上的多个列进行索引。联合索引的创建方法和单个索引的创建方法一样不同之处仅在于有多个索引列。
比如对创建索引index_a_b (a,b),那么在查询的时候select * from table where axx and bxx是可以使用(a,b)这个索引的。对于单列的查询select * from table where axxx也可以使用这个(a,b)索引。但是select * from table where bxxx是用不到联合索引的。
联合索引的另一个好处就是已经对第二个键值进行了排序处理。例如使用(userid,sys_date)联合索引在查询userid并且要求有序的时候就会默认使用(userid,sys_date)索引。
1创建索引
mysql create table index_test(- userid int unsigned not null,- sys_date date,- key key_u (userid),- key key_u_s (userid,sys_date)- )engineinnodb;
Query OK, 0 rows affected (0.03 sec)2插入数据
mysql insert into index_test values(1,2020-01-01);
mysql insert into index_test values(2,2022-01-01);
mysql insert into index_test values(3,2022-05-01);
mysql insert into index_test values(4,2021-05-01);3查询userid不要求排序
只查询userid的时候可以看到在possible_keys中可以有两个索引提供使用分别是单个userid索引的ley_u和(user_id,sys_date)的联合索引key_u_s。但是最终优化器选择是userid因为该索引的叶子节点包含单个键值所以理论上一个页能存放的记录应该更多。
mysql explain select * from index_test where userid 2\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: index_testpartitions: NULLtype: ref
possible_keys: key_u,key_u_skey: key_ukey_len: 4ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)
4查询userid要求排序
当查询userid并要求排序的时候possible_keys既可以使用key_u索引也可以使用key_u_s索引。但是优化器选择了ley_u_s索引因为这个联合索引中sys_date字段已经排序好了。只需要根据联合索引取出数据无须再对sys_date做一次额外的排序操作。
mysql explain select * from index_test where userid 2 order by sys_date desc limit 3\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: index_testpartitions: NULLtype: ref
possible_keys: key_u,key_u_skey: key_u_skey_len: 4ref: constrows: 1filtered: 100.00Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
5查询userid要求排序并强制使用key_u索引
如果是强制使用key_u索引可以在Extra中看到有using filesort即需要额外一次排序才能完成查询而这次显然需要对列sys_date排序。
mysql explain select * from index_test force index(key_u) where userid 2 order by sys_date desc limit 3\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: index_testpartitions: NULLtype: ref
possible_keys: key_ukey: key_ukey_len: 4ref: constrows: 1filtered: 100.00Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)
3 覆盖索引
InnoDB存储引擎支持覆盖索引即从辅助索引中就可以得到查询的记录而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息故其大小要远小于聚集索引因此可以减少大量的IO操作。
例如当一个表里有聚集索引和辅助索引时在count数据的时候innodb会自动优化使用辅助索引而不是使用聚集索引。
4 优化器选择不适应索引的情况
在某些情况下当执行explain命令进行SQL语句分析时会发现优化器并没有选择索引去查找数据而是通过扫描聚集索引也就是直接进行全表扫描来得到数据。这种情况多发生于范围查找、join连接等情况下。
5 索引提示
MySQL数据库支持索引提示(index hint)显式的告诉优化器使用哪个索引。大概两种情况下需要用到索引提示
MySQL数据库的优化器错误的选择了某个索引导致SQL语句运行很慢。对于目前的SQL版本可能很少会遇见这种问题。如果存在的话可以强制优化器使用某个索引以此来提高运行的速度。某SQL语句可以选择的索引非常多这时优化器选择执行时间的开销可能会大于SQL语句本身。例如优化器分析range查询本身就是比较耗时的操作。这时可以通过Index Hint来强制优化器不进行各个执行路径的成本分析直接指定索引完成查询。
使用force index来指定索引是可行的。
6 Multi-Range Read优化
MySQL5.6开始支持Multi-Range Read(MRR)优化。其目的就是减少磁盘的随机访问并且随机访问转化为较为顺序的数据访问这时对于IO-bound类型的SQL查询可以带来性能极大的提示。Multi-Range Read优化可适用于rangerefeq_ref类型的查询。
MRR优化的好处
MRR使数据访问变得较为顺序。在查询辅助索引时首先恩据得到得查询结果按照主键进行排序并按照主键顺序进行书签查找。减少缓冲池中页被替换的次数批量处理对键值的查询操作
对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作MRR的工作方式如下
将查询得到的辅助索引键值存放于一个缓存中这时缓存中的数据是根据辅助索引键值排序的。将缓存中的键值根据RowID进行排序根据RowID的排序顺序来访问实际的数据文件。
此外若InooDB存储引擎或者MyISAM存储引擎的缓冲池不是足够大即不能存放下一张表中所有的数据此时频繁的离散读操作还会导致缓存中的页被替换出缓冲池然后又不断的被读入缓冲池。若是按照主键顺序进行访问则可以将此重复行为降为最低。
是否启用Multi-Range Read优化可以通过参数optimizer_switch中的标记来控制。当mrron时表示启用multi-range read优化。mrr_cost_based标记表示是否通过cost based的方式来选择是否启用mrr。
若mrr设为onmrr_cost_based设为off则总是启用multi-range read优化。例如可通过如下指令设置multi-range read优化总是处于开启状态
mysql set optimizer_switchmrron,mrr_cost_basedoff;
参数read_rnd_buffer_size用来控制键值的缓冲区大小当大于该值时则执行器对已经缓冲的数据根据RowID进行排序并通过RowID来取得行数据。该值默认为256K。
mysql select read_rnd_buffer_size\G;
*************************** 1. row ***************************
read_rnd_buffer_size: 262144
1 row in set (0.00 sec)7 Index Condition Pushdown (ICP)优化
和Multi-Range Read一样Index COndition Pushdown也是MySQL5.6开始支持的查询优化的方式。之前在进行索引查询的时候首先根据索引来查找记录然后根据where条件来过滤记录。在支持Index Condition Pushdown后MySQL数据库会在去除索引的同时判断是否可以进行where条件的过滤也就是将where的部分过滤操作放在存储引擎层。在某些查询下可以大大减少上层SQL对记录的索取(fetch)从而提高数据库的整体性能。
Index Condition Pushdown优化支持range、ref、eq_ref、ref_or_null类型的查询当前支持MyISAM和InnoDB存储引擎。当优化器选择Index Condition Pushdow优化时可在执行记录的列Extra看到Using index condition的提示。
