MySQL命中索引代码实验案例
文章目录
数据准备
准备大量数据：数据行数 100 万条，列个数 15 列。
便于我们更清晰的进行测试：
- 修改 MySQL 配置文件，在 [mysqld] 下添加
secure_file_priv=重启 MySQL 服务器，让选项生效 - 执行 db.sql 内的脚本，建表
- 执行
LOAD DATA INFILE 'D:\\big_person.txt' INTO TABLE big_person;注意实际路径根据情况修改
- 测试表 big_person（此表数据量较大，如果与其它表数据一起提供不好管理，故单独提供），数据行数 100 万条，列个数 15 列。为了更快速导入数据，这里采用了 load data infile 命令配合 *.txt 格式数据
数据文件：https://download.csdn.net/download/weixin_45525272/86453420
索引用于排序
/* 测试单列索引并不能在多列排序时加速 */ create index first_idx on big_person(first_name); create index last_idx on big_person(last_name); explain select * from big_person order by last_name, first_name limit 10; /* 多列排序需要用组合索引 */ alter table big_person drop index first_idx; alter table big_person drop index last_idx; create index last_first_idx on big_person(last_name,first_name); /* 多列排序需要遵循最左前缀原则, 第1个查询可以利用索引，第2,3查询不能利用索引 */ explain select * from big_person order by last_name, first_name limit 10; explain select * from big_person order by first_name, last_name limit 10; explain select * from big_person order by first_name limit 10; /* 多列排序升降序需要一致，查询1可以利用索引，查询2不能利用索引*/ explain select * from big_person order by last_name desc, first_name desc limit 10; explain select * from big_person order by last_name desc, first_name asc limit 10;
最左前缀原则
若建立组合索引 (a,b,c)，则可以利用到索引的排序条件是：
- order by a
- order by a, b
- order by a, b, c
索引用于 where 筛选
/* 模糊查询需要遵循字符串最左前缀原则，查询2可以利用索引，查询1,3不能利用索引 */ explain SELECT * FROM big_person WHERE first_name LIKE 'dav%' LIMIT 5; explain SELECT * FROM big_person WHERE last_name LIKE 'dav%' LIMIT 5; explain SELECT * FROM big_person WHERE last_name LIKE '%dav' LIMIT 5; /* 组合索引需要遵循最左前缀原则，查询1,2可以利用索引，查询3,4不能利用索引 */ create index province_city_county_idx on big_person(province,city,county); explain SELECT * FROM big_person WHERE province = '上海' AND city='宜兰县' AND county='中西区'; explain SELECT * FROM big_person WHERE county='中西区' AND city='宜兰县' AND province = '上海'; explain SELECT * FROM big_person WHERE city='宜兰县' AND county='中西区'; explain SELECT * FROM big_person WHERE county='中西区'; /* 函数及计算问题，一旦在字段上应用了计算或函数，都会造成索引失效。查询2可以利用索引，查询1不能利用索引 */ create index birthday_idx on big_person(birthday); explain SELECT * FROM big_person WHERE ADDDATE(birthday,1)='2005-02-10'; explain SELECT * FROM big_person WHERE birthday=ADDDATE('2005-02-10',-1); /* 隐式类型转换问题 * 查询1会发生隐式类型转换等价于在phone上应用了函数，造成索引失效 * 查询2字段与值类型相同不会类型转换，可以利用索引 */ create index phone_idx on big_person(phone); explain SELECT * FROM big_person WHERE phone = 13000013934; explain SELECT * FROM big_person WHERE phone = '13000013934';
最左前缀原则（leftmost prefix）
若建立组合索引 (a,b,c)，则可以利用到索引的查询条件是：
- where a = ?
- where a = ? and b = ? （注意与条件的先后次序无关，也可以是 where b = ? and a = ?，只要出现即可）
- where a = ? and b = ? and c = ? （注意事项同上）
不能利用的例子：
- where b = ?
- where b = ? and c = ?
- where c = ?
特殊情况：
- where a = ? and c = ?（a = ? 会利用索引，但 c = ? 不能利用索引加速，会触发索引条件下推）
索引条件下推
/* 查询 1,2,3,4 都能利用索引，但 4 相当于部分利用了索引，会触发索引条件下推 */ explain SELECT * FROM big_person WHERE province = '上海'; explain SELECT * FROM big_person WHERE province = '上海' AND city='嘉兴市'; explain SELECT * FROM big_person WHERE province = '上海' AND city='嘉兴市' AND county='中西区'; explain SELECT * FROM big_person WHERE province = '上海' AND county='中西区';
索引条件下推
- MySQL 执行条件判断的时机有两处：
- 服务层（上层，不包括索引实现）
- 引擎层（下层，包括了索引实现，可以利用）
- 上面查询 4 中有 province 条件能够利用索引，在引擎层执行，但 county 条件仍然要交给服务层处理
- 在 5.6 之前，服务层需要判断所有记录的 county 条件，性能非常低
- 5.6 以后，引擎层会先根据 province 条件过滤，满足条件的记录才在服务层处理 county 条件
我们现在用的是 5.6 以上版本，所以没有体会，可以用下面的语句关闭索引下推优化，再测试一下性能
SET optimizer_switch = 'index_condition_pushdown=off'; SELECT * FROM big_person WHERE province = '上海' AND county='中西区';
二级索引覆盖
explain SELECT * FROM big_person WHERE province = '上海' AND city='宜兰县' AND county= '中西区'; explain SELECT id,province,city,county FROM big_person WHERE province = '上海' AND city='宜兰县' AND county='中西区';
根据查询条件查询 1，2 都会先走二级索引，但是二级索引仅包含了 (province, city, county) 和 id 信息
- 查询 1 是 select *，因此还有一些字段二级索引中没有，需要回表（查询聚簇索引）来获取其它字段信息
- 查询 2 的 select 中明确指出了需要哪些字段，这些字段在二级索引都有，就避免了回表查询
索引其它注意事项
- 表连接需要在连接字段上建立索引
- 不要迷信网上说法，具体情况具体分析
例如：
create index first_idx on big_person(first_name); /* 不会利用索引，因为优化器发现查询记录数太多，还不如直接全表扫描 */ explain SELECT * FROM big_person WHERE first_name > 'Jenni'; /* 会利用索引，因为优化器发现查询记录数不太多 */ explain SELECT * FROM big_person WHERE first_name > 'Willia'; /* 同一字段的不同值利用 or 连接，会利用索引 */ explain select * from big_person where id = 1 or id = 190839; /* 不同字段利用 or 连接，会利用索引(底层分别用了两个索引) */ explain select * from big_person where first_name = 'David' or last_name = 'Thomas'; /* in 会利用索引 */ explain select * from big_person where first_name in ('Mark', 'Kevin','David'); /* not in 不会利用索引的情况 */ explain select * from big_person where first_name not in ('Mark', 'Kevin','David'); /* not in 会利用索引的情况 */ explain select id from big_person where first_name not in ('Mark', 'Kevin','David');
- 以上实验基于 Mysql 5.7.27，其它如 !=、is null、is not null 是否使用索引都会跟版本、实际数据相关，以优化器结果为准
