MySQL高级设计之四、索引

一、索引的类型：
B-tree索引的特点：
1、以B+树结构存储数据

2、加快数据的查询速度
3、更适合范围查找

使用场景
1、全治匹配的查询
order_sn= ‘11111111’
2、匹配最左前缀的查询
3、匹配列前缀查询
如 order_sn like ‘22222%’
4、匹配范围值的查询
order_sn > ‘jjjj’
5、精确匹配坐前列并范围匹配另外一列
6、只访问索引的查询

使用限制
1、如果不是按照索引最左列开始查找，则无法使用索引
2、使用索引时不能跳过索引中的列
3、not in 和 <>操作无法使用索引
4、如果查询中有某个列的范围查询，则其右边所有列都无法使用索引

Hash索引的特点
1、hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中所有列时才可使用（等值查询，不能查范围 ）
2、对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码

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

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

坏处
索引会增加写操作的成本
太多的索引会增加查询器的操作时间

索引优化策略
1、索引列上不能使用表达式或者函数

2、前缀索引和索引列的选择性
CREATE INDEX index_name ON table（col_name(n)）最大宽度1000，255个字符，前缀索引会降低索引选择性
索引选择性：是不重复的索引值和表的记录数的比值

联合索引
如何选择索引列的顺序
1、经常会被使用到的列优先
2、选择性高的列优先
3、宽度小的列优先

覆盖索引
优点：
可以优化缓存，减少磁盘I/O操作
可以减少随机I/O，变随机I/O为顺序I/O
可以避免对innodb主键索引的二次查询
可以避免myISAM表进行系统调用

无法使用覆盖索引的情况
存储引擎不支持覆盖索引
查询中使用了太多的列
使用了双%的like查询

删除重复和冗余索引
primary key(id),unique key(id) index(id)
index(a),index(a,b)
primary key(id), index(a,id)
pt-duplicate-key-checker h=127.0.0.1检查索引工具

查找未被使用过的索引
select object_schema,object_name,index_name,b.TABLE_ROWS from performance_schema.table_io_waits_summary_by_index_usage a JOIN information_schema.tables b ON a.OBJECT_SCHEMA = b.TABLE_SCHEMA AND a.OBJECT_NAME = b.TABLE_NAME WHERE index_name IS NOT NULL AND count_star=0 ORDER BY object_schema,object_name

更新索引统计信息及减少索引碎片
analyze table table_name

使用索引扫描来优化排序
索引的列顺序和order by 子句的顺序必须完全一致
索引中所有列的方向（升序、降序）和order by 子句完全一致
order by中的字段全部关联表中的第一张表中

模拟hash索引优化查询
只能处理键值的全指匹配查找
所使用的hash函数决定着索引键的大小

利用索引优化锁
索引可以减少锁定的行数
索引可以加快处理速度，同时也加快了锁的释放