MySQL系列:(八)索引与执行计划

admin 2021年01月05日 934次浏览

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"