mysql索引

B-tree索引的特点

• B-tree索引能够加快数据的查询速度。
• B-tree索引更适合进行范围查找。

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

• 全值匹配的查询
  order_sn=‘84234234’
• 匹配最左前缀的查询
  索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。如有索引(a, b, c, d)，查询条件a = 1 and b = 2 and c > 3 and d = 4，则会在每个节点依次命中a、b、c，无法命中d，当换成了（a,b,d,c）就可以用到，=和in可以乱序，比如b=1 and a=1 and d=3 and c=1,因为mysql优化器会自动优化改sql，使得该sql符合匹配最左前缀的查询。也就是最左前缀匹配原则。
• 匹配列前缀查询
  order_sn like ‘9876%’
• 匹配范围值的查询
  order_sn >‘123’ and order_sn<‘456’
• 精确匹配左前列并范围匹配另外一列
• 只访问索引的查询

B树索引的使用限制

• 如果不是按照索引最左列开始查找，则无法使用索引
  联合索引（a,b,c），如果只通过b、c或者（b,c）查找，那无法使用到索引。
• 使用索引时不能跳过索引中的列
  联合索引（a,b,c），如果通过（a,c）查找，首先会根据a指定搜索方向，但是第二个字段b缺失，所以将a字段正确的都找到后，然后才会去匹配c的数据。
• not in 和 <>操作无法使用索引
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

Hash索引

Hash索引的特点

1. hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列时，才能够使用到hash索引。
2. 对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就算hash码。

Hash索引的限制

1. hash索引必须进行二次查找
2. hash索引无法用于排序
3. hash索引不支持部分索引查找也不支持范围查找
4. hash索引中hash码的计算可能存在hash冲突

为是什么要使用索引

1. 索引大大减少了存储引擎需要扫描的数据量
2. 索引可以帮助我们进行排序以避免使用临时表
3. 索引可以把随机I/O变为顺序I/O

索引是不是越多越好

1. 索引会增加写操作的成本
2. 太多的索引会增加查询优化器的选择时间

索引优化策略

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

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

由于索引键值的大小限制，innodb最大为767，MyISAM中最大为1000，导致对于大字符串列上建立索引存在限制，对于大字符串可以使用前缀索引。
create index index_name on table(col_name(n));（n索引长度）

索引的选择性是不重复的所有值和表的记录数的比值。

模拟hash索引优化查询

在表中建立新增一列，存放大字符串的MD5值，并给MD5值这列建立索引，可以通过触发器，或者代码实现。

select * from table md5_name=md5(name) and name=‘name’
局限性：

1. 只能处理键值的全值匹配查找
2. 所使用的hash函数决定着索引键的大小

联合索引优化策略

如何选择索引列的顺序

1. 经常会被使用到的列优先
2. 选择性高的列优先
3. 宽度小的列优先

覆盖索引

概念：如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作。

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点

1. 索引项通常比记录要小，所以MySQL访问更少的数据
2. 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O
3. 大多数据引擎能更好的缓存索引，比如MyISAM只缓存索引
4. 覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

无法使用覆盖索引的情况

1. 存储引擎不支持覆盖索引
2. 查询中使用太多的列
3. 使用了双%号的like查询

使用索引来优化查询

使用索引扫描来优化排序

1. 索引的列顺序和order by子句的顺序完全一致
2. 索引中所有列的方向（升序，降序）和order by子句完全一致
3. order by中的字段全部在关联表中的第一张表

利用索引优化锁

1. 索引可以减少锁定的行数
2. 索引可以加快处理速度，同时也加快了锁的释放

如何定位并优化慢查询Sql

1. 根据慢日志定位慢查询SQL
   开启mysql数据库慢查询日志（set slow_query_log ‘ON’),设置慢查询时间（set long_query_time ‘2’）,设置慢查询日志存放路径（set slow_query_log_file ‘db-slow.log’）
   
2. 使用explain等工具分析sql
   
3. 修改sql语句或者让sql尽量走索引