MySQL系列：（八）索引与执行计划

1、索引作用

对查询进行优化

2、索引分类（算法）

• B树 (默认使用的索引类型)
• FullText 全文索引
• GIS 地理位置索引

3、B树索引在功能上的分类

3.1、辅助索引

• 特点
  • 提取索引列的所有值，然后进行排序
  • 将排序号的值，均匀的存放在叶子节点，然后生成枝节点和根节点
  • 在叶子节点中的值都会对应存储主键ID
• 分类
  • 单列辅助索引
  • 联合索引
  • 唯一索引

3.2、聚集索引

• 特点
  • MySQL会自动选择主键作为聚集索引列，没有主键会选择唯一键，如果都没有会生成隐藏的
  • MySQL进行数据存储时，会按照聚集索引列值的顺序，有序的存储数据行
  • 聚集索引直接将原表数据页作为叶子节点，然后提取聚集索引列向上生成枝节点和根节点

3.3、聚焦索引和辅助索引的区别

• 表中任何一个列都可以创建辅助索引，只要名字不同即可
• 在一张表中，只能有一个聚焦索引，一般是主键
• 辅助索引，叶子节点只存储索引列的有序值和聚焦索引列值
• 聚集索引，叶子节点存储的是有序的整行数据
• MySQL的表数据存储是聚集索引组织表

4、索引的命令操作

4.1、查询索引

• 语法
  desc [表名]
  show index from [表名]

• key/index键值说明
  PRI ：主键
  MUL ：辅助索引
  UNI ：唯一索引

• 示例

  DESC city;
  SHOW INDEX FROM city;
  

4.2、创建索引

4.2.1、创建单列辅助索引

• 语法
  ALTER TABLE [表名] ADD INDEX 索引名(列名)

• 示例

  # 普通单列索引
  ALTER TABLE city ADD INDEX idx_name(name);
  
  # 前缀索引
  ALTER TABLE city ADD INDEX idx_name(name(n));
  

4.2.2、创建多列联合索引

• 语法
  ALTER TABLE [表名] ADD INDEX 索引名(列名1,列名2,...)

• 示例

  ALTER TABLE city ADD INDEX idx_name(name,countrycode);
  

4.2.3、创建唯一索引

• 语法
  ALTER TABLE [表名] ADD UNIQUE INDEX 索引名(列名)

• 示例

  ALTER TABLE city ADD UNIQUE INDEX uidx_dis(district);
  

4.3、删除索引

• 语法
  ALTER TABLE [表名] DROP INDEX [索引名]

• 示例

  ALTER TABLE city DROP INDEX idx_name;
  

5、执行计划分析

5.1、执行计划作用

将优化器选择后的执行计划截取出来，便于管理判断语句的执行效率

5.2、获取执行计划

• 语法
  DESC [sql语句]
  explain [sql语句]

5.3、分析执行计划

5.3.1、执行计划结果说明

• table：表名
• type：查询的类型，自上而下，性能自裂变好
  • 全表扫描：ALL（性能最差）
  • 索引扫描：
    index：全索引扫描
    range：索引范围扫描（>、<、>=、<=、and、or、in、like）,
    对于辅助索引，!=和not、in等语句是不走索引的
    对于主键索引，!=、not、in等语句是走range
    ref：辅助索引等值查询
    eq_ref：多表连接时，子表使用主键列或唯一列作为连接条件
    const：主键或唯一键的等值查询
    null：无查询结果
• possible_keys：可能使用的索引
• key：真正使用的索引
• key_len：索引覆盖长度（索引值最大长度）
  • key_len计算方法：
    字符长度*单个字符字节数+是否为varchar类型+是否能为空
  • 各数据对应字节数
    • int类型：4个字节
    • 字符串类型：utf8：3个字节、utf8bm4：4个字节
    • varchar类型：加2个字节
    • 允许为空：加1个字节
• Extra：在该字段中，如果出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引，需要关注key_len应用的长度

5.3.2、执行计划中各索引类型实现示例

# 全表扫描
DESC SELECT * from city;
# 全索引扫描（index）
DESC SELECT id from city;
# 索引范围扫描（range）
DESC SELECT * FROM city WHERE id > 100;
# 辅助索引等值查询（ref）
DESC SELECT * FROM city WHERE countrycode = 'CHN'  UNION ALL SELECT * FROM city WHERE countrycode = 'USA';
# eq_ref
DESC SELECT city.CountryCode,city.Name,city.Population,country.Name,country.SurfaceArea FROM city JOIN country ON city.countryCode = country.Code WHERE city.population < 1000;
# 主键或唯一键的等值查询（const）
DESC SELECT * FROM city WHERE id = 100;

5.4、联合索引优化

• 所有索引列都是“等值”查询条件下,无关排列顺序，需要将唯一值多的列放在最左侧
• 对于不连续部分条件，将尽可能多的连续部分放在左侧
• 在where查询中如果出现 >、<、>=、<=、like等，尽可能将这些条件放在索引最后面
• 对于多子句，按照语句的逻辑建立索引即可

5.5、索引应用规范

5.5.1、建立索引的原则（DBA运维规范）

• 建表必须要有主键,一般是无关列,自增长
• 经常做为where、order by、group by、join on、distinct条件的列
• 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
• 列值长度较长的索引列,建议使用前缀索引
• 降低索引条目,一方面不要创建没用的索引,另一方面清理不常使用的索引
• 索引维护要避开业务繁忙期
• 小表不建索引

5.5.2、不走索引的情况（开发规范）

• 没有查询条件，或者查询条件没有建立索引
• 查询结果集是原表中的大部分数据，应该是25％以上
• 索引本身失效，统计数据不真实
• 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
• 隐式转换导致索引失效
• <、> 、not in 不走索引（辅助索引）
• like "%aa"