mysql innodb 索引使用指南

头像
sixsixfly
    阅读 9 分钟
    1

    Btree 在 mysql 中的应用

    索引相关概念

    1. 聚簇索引（clustered index）
      使用innodb引擎时，每张表都有一个聚簇索引，比如我们设置的主键就是聚簇索引
      聚簇是指数据的存储方式，表示数据行和相邻的键值紧凑的储存在一起
      特点：查询数据特别快，因为聚簇索引和行数据存储在磁盘的同一页，这样可以减少磁盘I/O操作次数（MySQL 索引 B+Tree）
      注意：主键应该尽量简短
    2. 二级索引（secondary index）
      除了聚簇索引外的其他索引叫做二级索引（辅助索引），比如我们给除主键外其他字段创建的索引
      特点：二级索引里面存储了聚簇索引，最后要通过聚簇索引找到行数据。可见，聚簇索引的效率会影响其他索引
    3. 覆盖索引（covering index）
      索引包含了查询语句需要的所有数据，这种索引称为覆盖索引
      特点：索引的叶子节点中已经包含要查询的数据，不需要回表操作所以很快（减少了磁盘I/O操作次数）
    4. 组合索引（multiple-column index）
      组合索引也称为复合索引（联合索引），是指把多个字段组合起来创建一个索引（最多16个字段）
      特点：遵循最左前缀匹配原则
    5. 最左前缀匹配原则（leftmost prefix principle）
      mysql会从左向右匹配直到遇到不能使用索引的条件（>、<、!=、not、like模糊查询的%前缀）才停止匹配
      设想用a,b,c字段创建一个组合索引（a,b,c）
      由于a是索引的最左边前缀，所以where条件中必须匹配字段a，mysql优化器才会用到这个索引
      在匹配字段a的前提下，才能匹配字段b
      在匹配字段a的前提下，并且匹配字段b，然后才能匹配字段c

    使用explain查看执行计划

    explain命令用来查看select语句执行计划，确认该SQL语句有没有使用索引，是否做全表扫描，是否使用覆盖索引等

    idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra

    type：代表数据访问类型（由左至右，由最差到最好）
    | All | index | range | ref | eq_ref | const | system | null |

    possible_keys：表示哪些索引可能有利于高效的查找

    key：显示mysql决定采用哪个索引来优化查询

    key_len：显示mysql在索引里使用的字节数

    ref：显示了之前的表在key列记录的索引中查找值所用的列或常量

    rows：为了找到所需的行大致需要读取的行数

    extra：表示额外的信息（左边较差，右边较好）
    | Using filesort| Using temporary | Using where | Using index condition | Using index |
    Using index：使用了覆盖索引，速度很快，限于查询字段都位于同一个索引中的场景
    Using index condition：表示使用了ICP优化（Index Condition Pushdown)，能减少引擎层访问基表的次数和MySQL Server访问存储引擎的次数
    Using where：表示在存储引擎检索行后mysql服务器再进行过滤
    Using filesort：返回结果前需要做一次外部排序（内存或硬盘），速度慢应该尽量避免
    Using temporary：在对查询结果排序时会使用一个临时表，速度慢

    创建一张测试表

    使用InnoDB引擎创建teacher表，id设为自增主键
    mobile、name和birthday建立第一个组合索引idx_one（注意三个字段在索引中顺序）
    email、age和name字段建立第二个组合索引idx_two（同样注意顺序）

    CREATE TABLE `teacher` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `birthday` timestamp NULL DEFAULT NULL,
  `email` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `mobile` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_one` (`mobile`,`name`,`birthday`),
  KEY `idx_two` (`email`,`age`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

    数据量很少时数据库全表扫描和走索引成本没有太大差别，这时候即使创建了索引并且符合走索引的条件，mysql 却不一定使用索引，下面是十万条初始数据(文章内容不支持添加文件只能到其他地址下载)
    表初始数据下载链接

    符合最左前缀场景

    场景一：mobile是索引的左前缀

    explain select * from teacher where mobile = '18600660088';

    说明：ref列只出现了一个const，说明使用索引的第一列
    clipboard.png

    场景二：mobile和name加起来是索引的左前缀

    explain select * from teacher where mobile = '18600660088' and name = 'kevin';

    说明：ref列出现了两个const，说明使用索引的前缀mobile和name
    clipboard.png

    场景三：mobile、name和birthday加起来是索引的左前缀

    explain select * from teacher where birthday = '2019-01-01' and name = 'kevin' and mobile = '18600660088';

    说明：ref列出现了三个const，说明使用索引的第一列、第二列和第三列
    注意：mysql优化器会自动调整mobile、name、birthday在查询条件中出现的顺序以匹配索引
    clipboard.png

    场景四：只有mobile是前缀，中间跳过了索引中第二列（name），birthday不使用索引

    explain select age from teacher where mobile = '18600660088' and birthday = '2019-01-01';

    说明：ref列只出现了一个const，说明使用索引的前缀mobile部分
    clipboard.png

    场景五：mobile和name加起来是索引的前缀，并且%位于模糊查询后缀

    explain select * from teacher where mobile = '18600660088' and name like 'kevin%';

    说明：key_len是246与场景二一致，只使用了索引的前缀部分
    clipboard.png

    场景六：mobile和name加起来是索引的前缀，并且%位于模糊查询的前缀

    explain select * from teacher where mobile = '18600660088' and name like '%kevin';

    说明：mobile字段用到了索引，name不使用索引
    clipboard.png

    场景七：mobile是索引的最左前缀，并且使用了范围查询

    explain select * from teacher where mobile > '18600660088' and name = 'kevin' and birthday = '2019-01-01';

    说明：key_len是51与场景六一致，只使用了索引前缀mobile，name和birthday不使用索引
    结论：索引从左往右匹配，遇到范围查询后停止匹配
    clipboard.png

    场景八：name位于组合索引的中间，并且%位于模糊查询后缀

    explain select * from teacher where mobile = '18600660088' and name like 'kevin%' and birthday = '2019-01-01';

    说明：key_len显示251说明跟场景三一致，使用到了整个组合索引
    结论：%位于模糊查询后缀不影响索引的使用，如果是组合索引可以继续往右匹配
    clipboard.png

    不使用索引的场景

    场景一：缺少前缀mobile字段

    explain select * from teacher where name = 'kevin chen';

    说明：type列显示ALL表示全表扫描，MySQL 从头到尾扫描整张表查找行
    clipboard.png

    场景二：缺少mobile和name组合的前缀字段

    explain select * from teacher where birthday = '2019-01-01';

    说明：type列显示ALL表示全表扫描，MySQL从头到尾扫描整张表查找行
    clipboard.png

    场景三：like模糊匹配%位于前缀

    explain select * from teacher where mobile like '%18600660088';

    说明：type列显示ALL表示全表扫描，MySQL从头到尾扫描整张表查找行
    clipboard.png

    场景四：索引列进行了函数运算

    explain select * from teacher where trim(mobile) = '18600660088';

    说明：正确的做法是在等号的右边做数据运算或函数运算
    clipboard.png

    场景五：字段类型不匹配

    explain select * from teacher where mobile = 18600660088;

    说明：mobile是varchar类型，18600660088是整数
    clipboard.png

    覆盖索引

    场景一：需要的数据都在索引中，走覆盖索引

    explain select mobile,name,birthday from teacher where mobile = '18600660088';

    说明：Extra列显示附加信息，Using index表示使用覆盖索引
    clipboard.png

    场景二：查询的age字段不在索引中，不能使用覆盖索引

    explain select age from teacher where mobile = '18600660088';

    clipboard.png

    使用索引排序

    场景一：查询字段和排序字段是同一个索引

    explain select id,mobile,name,birthday from teacher order by mobile,name,birthday;

    说明：extra显示Using index表示使用了覆盖索引，二级索引中隐含了聚簇索引（主键）
    clipboard.png

    场景二：多个排序字段位于多个不同索引

    explain select * from teacher order by mobile, email;

    说明：mobil和email属于不同索引，Using filesort说明使用外部排序，不能用索引排序
    clipboard.png

    场景三：多个排序字段不符合最左前缀匹配原则

    explain select id,mobile,name,birthday from teacher order by mobile, birthday;

    说明：查询用了索引，排序跳过了组合索引中间字段name，extra显示Using filesort
    clipboard.png

    场景四：查询含索引外字段，用索引扫描后再回表查询比直接扫表成本更高，所以没使用索引

    explain select * from teacher order by mobile,name,birthday;

    说明：extra显示Using filesort表示使用了外部排序
    clipboard.png

    场景五：查询条件字段和排序字段组合起来符合索引最左前缀

    explain select * from teacher where mobile='18600660088' order by name;

    clipboard.png

    使用索引分组

    场景一：分组字段（多字段组合）属于索引最左前缀

    explain select email, age, name from teacher group by email, age, name;

    说明：email、age和name组合起来符合最左前缀，使用索引idx_two，extra显示Using index
    clipboard.png

    explain select distinct email, age, name from teacher;

    说明：这里distinct字段组合起来同样符合索引最左前缀，使用索引idx_two
    clipboard.png

    场景二：min()/max()函数作用于同一列，并且紧跟属于同一索引的分组字段

    explain select email, min(age), max(age) from teacher group by email;

    说明：email是分组字段，age是函数作用字段，email和age组合起来符合idx_two最左前缀
    clipboard.png

    场景三：count(distinct)、avg(distinct)和sum(distinct)组合起来符合最左前缀

    explain select count(distinct email), sum(distinct age) from teacher;

    说明：avg(distinct)和sum(distinct)中distinct只适用单个字段
    clipboard.png

    场景四：count(distinct)，distinct适用于多个字段

    explain select count(distinct email, age) from teacher;

    说明：extra显示Using index for group-by说明使用松散索引扫描（Loose Index Scan）
    clipboard.png

    场景五：缺少组合索引中间部分，不能使用索引排序

    explain select email, name from teacher group by email, name;

    说明：分组字段缺少idx_two索引age部分，extra显示Using filesort说明使用外部排序
    clipboard.png

    场景六：多个分组字段不属于同一个索引

    explain select email, age, birthday from teacher group by email, age, birthday;

    说明：birthday不属于idx_two索引，显示Using filesort
    clipboard.png

    场景七：紧凑索引扫描（Tight Index Scan）

    explain select email, age, name from teacher where age = 18 group by email, name;

    说明：分组字段缺少了完整索引中间部分，但由查询条件 age = 18 补充了这部分常量
    clipboard.png

    场景八：紧凑索引扫描（Tight Index Scan）

    explain select email, age, name from teacher where email = 'kevin@qq.com' group by age, name;

    说明：分组字段不以索引最左前缀开始，但查询条件 email='kevin@qq.com' 提供了这部分常量
    clipboard.png

    处理从右到左匹配的需求

    如果查询条件需要处理从右到左匹配，可以有两种处理方案

    • 表结构新增一列用来存储需要从右到左匹配列的倒序字符并构建索引（新增列和索引都需要占用磁盘空间）
    • Mysql 5.7 版本提供了虚拟列功能，使用 reverse 函数对需要从右到左匹配的列构建虚拟列并创建索引

    teacher 表新增虚拟列 name_reverse，按照 name 列字符倒序计算得到，并创建索引

    alter table teacher add column name_reverse varchar(20) generated always as (reverse(name));
alter table teacher add index idx_name_reverse(name_reverse);

    说明：Generated Column 是 MySQL 5.7 引入的新特性，所谓 Cenerated Column 是数据库中这一列由其他列计算而得，Cenerated Column 支持 Virtual Column 与 Stored Column 两种类型，前者只将 Generated Colum n保存在数据字典中（表的元数据）并不会将这一列数据持久化到磁盘上；后者会将 Generated Column 持久化到磁盘上，而不是每次读取的时候计算所得，后者需要更多的磁盘空间

    参考资料

    How MySQL Uses Indexes
    Multiple-Column Indexes
    ORDER BY Optimization
    GROUP BY Optimization
    EXPLAIN Output Format
    CREATE TABLE and Generated Columns
    Secondary Indexes and Generated Columns

    Btree 在 mysql 中的应用

    mysqlinnodbindex索引
    sixsixfly
    18 声望0 粉丝
    下一篇 »
    Java 并发编程

    引用和评论

    被 1 篇内容引用
    推荐阅读
    头像
    TCP keepalive 概述（译）

    sixsixfly阅读 2.8k

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 11阅读 2k

    头像
    Mysql数据库sql优化案例

    杜若赞 5阅读 2.1k

    头像
    MySQL 回收表碎片实践教程

    MySQL技术赞 2阅读 343

    头像
    高效工程师的七个习惯

    Bytebase赞 3阅读 649

    头像
    Mysql梳理1

    cashew赞 1阅读 2.1k

    头像
    mysql梳理2

    cashew赞 1阅读 1.5k

    0 条评论
    得票最新