Mysql 索引（笔记）

一、mysql 支持的索引类型

1.B-tree 索引

特点

• B-tree 是以 B+ 树的结构存储数据
• B-tree 可以加快数据的查询速度
• B-tree 更适合进行范围查找

适用情况

• 全值匹配的查询（如：order_sn='123123'，可使用此列的索引）
• 匹配最左前缀的查询（如：实际没有 order_sn 这列的索引，但是联合索引 order_sn,order_date 中最左边包含，则可直接利用此索引查找）
• 匹配列前缀的查询（如：order_sn like '123%'）
• 匹配值的范围查询（如：order_sn > '111111' AND order_sn < '222222'）
• 精确匹配左前列并范围匹配另外一列（如：对于联合索引 order_sn,order_date，精确查找 order_sn 列，并范围查找 order_date 时）
• 只访问索引的查询（覆盖索引）
• 适合使用在 order by、group by 中

使用限制

• 不是按照索引最左列开始查找，则无法使用索引（如：对于联合索引 order_sn,order_date，只查找 order_date 时，无法使用此索引）
• 使用索引时不能跳过索引中的列（如：对于联合索引 order_sn,order_date,user_id，查找条件中只有 order_sn 和 user_id 的话，只能使用到 order_sn 的索引，无法使用 user_id）
• NOT IN 和 <> 操作无法使用到索引
• 如果查询中有某个列是范围查询，则其右边所有列无法使用索引（如：对于联合索引 order_sn,order_date,user_id，条件中有 order_sn 精确查找，order_date 的范围查找，user_id 无法使用到索引）

2.Hash 索引

Innodb 会建立自适应的 Hash 索引

特点

• Hash 索引时基于 Hash 表实现的，只有查询条件精确匹配 Hash 索引中的所有列时，才能使用到 Hash 索引（只有等值查询）
• 对于 Hash 引擎中的所有列，存储引擎会为每一行计算一个 Hash 码，Hash 索引中存储的就是 Hash 码

限制

• 使用 Hash 索引会进行两次查找，但不影响效率
• Hash 索引无法用于排序
• Hash 索引不支持部分索引查找也不支持范围查找
• Hash 索引中的 Hash 码的计算可能存在 Hash 冲突（不利于列有很多重复值的查找，如性别）

3.索引作用

• 索引减少了存储引擎需要扫描的数据量
• 索引可以帮助排序，以避免使用临时表
• 索引可以把随机 I/O 变为顺序 I/O

4.索引增加的损耗

• 索引会增加写操作的成本（Innodb 会利用插入缓存减少频繁写操作）
• 太多索引会增加查询优化器的选择时间

二、索引优化

• 索引列上使用表达式或者函数，无法使用索引

1.优化策略

（1）前缀索引和索引列的选择性：

• Innodb 的索引的最大宽度 767 字节，换算到 utf8 中是 255 个字符
• MyISAM 的索引的最大宽度是 1000 字节
• 所以创建列的前缀索引更节约空间，有效利用索引
• 创建方式 CREATE INDEX index_name ON table_name(col_name(n)) 其中 n 就是对 col_name 列索引的宽度
• 前缀索引以不降低索引的选择性为准
• 索引的选择性：不重复的索引值和表的记录数的比值，索引唯一性越高选择性越高

（2）联合索引

选择索引列的顺序

• 经常会被使用到的列优先
• 选择性高的列优先
• 选择性差的索引不宜放在前面，索引优化器不一定会使用（如：status(1,2,3)这种列）
• 宽度小的列优先使用

（3）覆盖索引

即查询的列中包含了被索引的列的

Extra 中 Using index 指 select 中的 last_name 使用了覆盖索引（last_name 有索引 idx_actor_last_name）

优点

• 可以优化缓存，减少磁盘 IO 操作
• 可以减少随机 IO，变随机 IO 为顺序 IO
• 可以避免对 Innodb 主键索引的二次查询
• 可以避免 MyISAM 表进行系统调用

无法使用覆盖索引

• 存储引擎不支持覆盖索引（memery 不能使用，Hash 索引不能作为覆盖索引使用）
• 查询中使用了太多的列（比如 select * 就无法使用）
• 使用了双 % 号的 like 查询

（4）使用索引优化查询

使用索引扫描优化排序

• 索引列的顺序和 order by 子句的顺序完全一致
• 索引中所有列的方向（升序、降序）和 order by 子句完全一致（联合索引中的两列，在 order by 中方向不一致时，无法使用索引）
• order by 中的字段全部在关联表的第一张表中

B-tree 索引模拟 Hash 索引优化查询（Innodb 不能直接使用 Hash 索引）

• 方式：简历一个字段的 md5 后值的字段，并对其建立索引，之后使用触发器等对其进行维护，查询时 where xxx_md5 = md5('nnnn') and xxx = 'nnn',后面条件用于避免 Hash 冲突
• 只能处理键值的全值匹配查找
• 所使用的 Hash 函数决定着索引建的大小（如上 md5 的字段是 32 位）

（5）利用索引优化锁

• 索引可以减少锁的行数
• 索引可以加快处理速度，同时也加快了所的释放

（6）索引的维护和优化

• 删除重复和冗余的索引（主键索引过的字段不需要再加入联合索引中，因联合索引会自动引用主键索引）
• 使用工具查找：pt-duplicate-key-checker h=127.0.0.1
• 查找未被使用过的索引，删除不需要的
• 查找 SQL：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，整理碎片：optimize table table_name