数据库索引优化

B-tree索引的特点：

B-tree索引以B+树的结构存储数据

B-tree索引能够加快数据的查询速度

B-tree索引更适合进行范围查找

 

叶子节点指针指向被索引的数据

innode中叶子节点指向主键

myisam中叶子节点指向数据的物理地址

 

在什么情况下可以用到B树索引

1、全局匹配的查询：order_sn = '9876432119900'（和索引中的所有列进行匹配）

2、匹配最左前缀的查询：只要联和索引的第一列符合查询条件，这个索引就会被用到。如果是联合索引的第二列

符合查询条件，这个联合索引不会被用到

3、匹配列前缀查询    ：order_sn like '9876%' (匹配列的开头部分，可以用到order_sn 开头的联合索引）

4、匹配范围值的查询：order_sn > '9876432119900'

and order_sn < '9876432119900'

5、精确匹配左前列并范围匹配另外一列（对于order_sn 和order_data联合索引来说，可以使用在精确匹配order_sn列，而order_data是在一个范围内的查询）

6、只访问索引的查询（称为覆盖索引，只需访问索引，无需访问数据行的查询）

7、还可以使用在orderBy从句中

 

关于B-tree的限制：

如果不是按索引的最左列开始查找，则无法使用索引

使用索引时不能跳过索引中的列 （这里说的是左边的列，而不是最左边的列

例子：订单日期，下单人姓名，下单人电话这三列组成的联合索引，在查找中只包括订单日期和下单人电话这两列，这个查询只能用到联合索引中的订单日期来进行过滤，不能用到下单人电话这列索引）

Not in 和 <>操作无法使用索引

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

 

Hash索引的特点：

Hash索引是基于Hash表实现的，只有查询条件精准匹配Hash索引中的所有列时，才能够使用到hash索引。

对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码

 

Hash索引的限制：

Hash索引必须进行二次查找

Hash索引无法用于排序

Hash索引不支持部分索引查找也不支持范围查找

Hash索引中Hash码的计算可能存在Hash冲突

 

 

为什么要使用索引：

索引大大减少了存储引擎需要扫描的数据量

索引可以帮助我们进行排序以避免使用临时表（造成IO消耗）

索引可以把随机I/O变为顺序I/O

 

索引是不是越来越多：

索引会增加写操作的成本

太多的索引会增加查询优化器的选择时间（一个字段有多个索引）

 

索引优化策略：

索引列上不能使用表达式或函数

前缀索引和索引列的选择性

create index index_name on table(col_name(n));

索引的选择性是不重复的索引值和表的记录数的比值（唯一索引和主键索引选择性最高）

联合索引：

如何选择联合索引列的顺序：

1、经常会被使用到的列优先原则

2、选择性高的列优先

3、宽度小的列优先

 

覆盖索引：

优点：可以优化缓存，减少磁盘IO操作

           可以减少随机IO，变随机IO操作变为顺序IO操作

           可以避免对Innodb主键索引的二次查询

           可以避免MyISAM表进行系统调用

无法使用覆盖索引的情况：

存储引擎不支持覆盖索引

查询中使用了太多的列

使用了双%号的like查询

 

 

使用索引来优化查询：

使用索引扫描来优化排序：通过排序操作，按照索引顺序扫描数据

①索引的列顺序和Order By子句的顺序完全一致

②索引中所有列的方向（升序，降序）和Order by子句完全一致

③Order by中的字段全部在关联表中的第一张表中

 

 

利用索引优化锁：

索引可以减少锁定的行数

索引可以加快处理速度，同时也加快了锁的释放

 

索引的维护和优化：

删除重复索引和冗余索引 （也可适当添加冗余索引）

 

检查索引重复，冗余：pt-duplicate-key-checker h=127.0.0.1