MySQL进阶
01、MySQL进阶：剖析MySQL索引底层数据结构
02、MySQL进阶：MySQL不同存储引擎下索引的实现
03、MySQL进阶：Explain深度剖析
04、MySQL进阶：践行索引优化
05、MySQL进阶：锁等待及死锁初探
06、MySQL进阶：无索引行锁升级为表锁
07、MySQL进阶：共享锁和排它锁初探
08、MySQL进阶：索引优化案例实操
09、MySQL进阶：索引下推IndexConditionPushdown初探
10、MySQL进阶：使用trace工具来窥探MySQL是如何选择执行计划的
11、MySQL进阶：orderby和groupby优化初探
12、MySQL进阶：分页查询优化的两个案例解析
13、MySQL进阶：Join关联查询优化
14、MySQL进阶：In和Exists的优化案例讲解
15、MySQL进阶：存储引擎初探
16、MySQL进阶：体系结构初探
17、MySQL进阶：解读MySQL事务与锁机制
18、MySQL进阶：多版本控制MVCC机制初探
19、MySQL进阶：并发事务问题及解决方案
20、MySQL进阶：锁机制初探
21、MySQL进阶：高效的设计MySQL库表
22、MySQL进阶：库表设计之IP和TIMESTAMP的处理
23、MySQL进阶：orderby出现usingfilesort根因分析及优化
24、MySQL进阶：canal实现mysql数据同步到redis｜实现自定义canal客户端
本文档使用 MrDoc 发布
-
+
首页
04、MySQL进阶：践行索引优化
### **Table Demo** ```python CREATE TABLE employees ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', age int(11) NOT NULL DEFAULT '0' COMMENT '年龄', position varchar(20) NOT NULL DEFAULT '' COMMENT '职位', hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', PRIMARY KEY (id), KEY idx_name_age_position (name,age,position) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表'; INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW()); ``` **DB Version** ```python mysql> select version(); +------------+ | version() | +------------+ | 5.7.29-log | +------------+ 1 row in set (0.00 sec) mysql> ``` **Case** ```python KEY idx_name_age_position (name,age,position) USING BTREE ``` 联合索引 **全值匹配** ```python mysql> explain select * from employees where name = 'LiLei'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> ```  算算这个ke_len key_len ： 显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。 【字符串】 - char(n)：n字节长度 - varchar(n)：如果是utf-8，则长度 3n + 2 字节，加的2字节用来存储字符串长度 【数值类型】 - tinyint：1字节 - smallint：2字节 - int：4字节 - bigint：8字节 【时间类型】 - date：3字节 - timestamp：4字节 - datetime：8字节 **如果字段允许为 NULL，需要1字节记录是否为 NULL** 索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引 name varchar(24) —> 3 * 24 + 2 = 74 , 用了联合索引中的name . ```python mysql> explain select * from employees where name = 'LiLei' and age= 22; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 78 | const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) ```  key_len 变成了 78 ？ 第二个是int , int 占 4个字节 ， 74 + 4 = 78 ，这个SQL用了联合索引中的 name + age ```python mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> ```  key_len = 74 + 4 + 72 = 140 ------------ 那我们跳过age 呢 ？ ```python mysql> explain select * from employees where name = 'LiLei' and position = 'manager'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 33.33 | Using index condition | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> ```  用了联合所以中的 name ### **最左前缀** 如果索引了多列，要遵守最左前缀法则 , 指的是查询从索引的最左前列开始并且不跳过索引中的列。 ```python mysql> explain select * from employees where name = 'LiLei' and age= 22; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 78 | const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> ```  符合最左前缀。 ------------  不符合最左前缀 user where : **使用 where 语句来处理结果，并且查询的列未被索引覆盖**  不符合最左前缀 user where : **使用 where 语句来处理结果，并且查询的列未被索引覆盖** ------------ ### **禁止索引列上做任何操作（计算、函数、（自动or手动）类型转换）** **会导致索引失效而转向全表扫描** ```python mysql> explain select * from employees where name = 'LiLei'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> ``` ```python mysql> explain select * from employees where left(name,2) = 'LiLei'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> ``` 结合索引那个B+Tree , 特征 排好序  **left 函数，MYSQL并没有做优化 ，left(name,2) 在那棵B+Tree上并没有，肯定不会走索引。** 看看函数的操作 加个索引 ```python alter table employees add index idx_hire_time(hire_time) using btree; ``` 查看目前的索引 ```python mysql> show index from employees ; +-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | employees | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | employees | 1 | idx_name_age_position | 1 | name | A | 2 | NULL | NULL | | BTREE | | | | employees | 1 | idx_name_age_position | 2 | age | A | 2 | NULL | NULL | | BTREE | | | | employees | 1 | idx_name_age_position | 3 | position | A | 2 | NULL | NULL | | BTREE | | | | employees | 1 | idx_hire_time | 1 | hire_time | A | 1 | NULL | NULL | | BTREE | | | +-----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.00 sec) mysql> ``` 在索引列上使用函数 ```python mysql> explain select * from employees where date(hire_time)='2018-09-30'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> ```  变幻一下 ```python mysql> explain select * from employees where hire_time>='2018-09-30 00:00:00' and hire_time<='2018-09-30 23:59:59'; +----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_hire_time | idx_hire_time | 4 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> ```  好了，实验完毕 移除索引 ```python alter table employees drop index idx_hire_time; ``` ------------ **存储引擎不能使用索引中范围条件右边的列** 比对一下 ```python mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> ``` **key_len = 140 （74 + 4 + 78） 全部走了 idx_name_age_position (name,age,position)** ```python mysql> explain select * from employees where name = 'LiLei' and age> 22 and position = 'manager'; +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 78 | NULL | 1 | 33.33 | Using index condition | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> ``` **key_len = 78 （74 + 4 ） 走了 idx_name_age_position (name,age,position) 中的 name 和 age** 为什么呢？ 脑海中找到那个B+Tree  **name 是相同的， 所以第二列 age 肯定是有序的， 而age这里取的是大于， age是大于， 第三列没办法保证有序。 如果age是等于，那可以，第三列有序。 上面这个图不是很合适，不要被误导了，放上去仅仅是为了让读者对B+树有个轮廓。** ------------ **尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句** ```python mysql> explain select * from employees where name = 'LiLei' and age= 22 and position = 'manager'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 140 | const,const,const | 1 | 100.00 | NULL | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select name , age from employees where name = 'LiLei' and age= 22 and position = 'manager'; +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 140 | const,const,const | 1 | 100.00 | Using index | +----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> ``` **看到第二个的 Extra ： Using Index 使用了覆盖索引** ------------ **mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描** ```python mysql> mysql> explain select * from employees where name != 'LiLei' ; +----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | 66.67 | Using where | +----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` ------------ **is null,is not null 一般情况下也无法使用索引** ```python mysql> explain select * from employees where name is null ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+ 1 row in set, 1 warning (0.00 sec) mysql> ``` null 值在树中会放到一起和其他节点搞个双向指针 ------------ **like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作** ```python mysql> explain select * from employees where name like '%Lei'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` %在前 还是要回想那个索引B+Tree , % 在前面 意味着前面可能还有其他的字符串， 那在树中的有序性没法保证啊 ```python mysql> explain select * from employees where name like 'Lei%'; +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 74 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> ``` 继续回想那个索引B+Tree , % 不在前面 意味着%前面的字符串固定， 那在树中的就是有序的，当然可以走索引  key_len = 74 ,可以推导出 走了 联合索引中的name ------------ **like 的优化** 【问题：解决like’%字符串%'索引不被使用的方法？】 A： 使用覆盖索引，查询字段必须是建立覆盖索引字段 ```python mysql> explain select * from employees where name like '%Lei'; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> mysql> mysql> explain select name ,age position from employees where name like '%Lei'; +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | employees | NULL | index | NULL | idx_name_age_position | 140 | NULL | 3 | 33.33 | Using where; Using index | +----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> ```  不敢说好太多， index 总比 all 好吧 。 **B： 如果不能使用覆盖索引则可能需要借助搜索引擎 ，Es等** ------------ ### **字符串不加单引号索引失效**  ------------ ### **少用or或in** 用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化  ### **范围查询优化** 增加索引 ```python alter table employees add index idx_age(age) using BTREE; ``` ```python mysql> explain select * from employees where age>=1 and age<=2000; +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | employees | NULL | ALL | idx_age | NULL | NULL | NULL | 3 | 100.00 | Using where | +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> ``` 没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引 **优化方法： 可以将大的范围拆分成多个小范围** ```python mysql> explain select * from employees where age>=1 and age<=10; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_age | idx_age | 4 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from employees where age>=11 and age<=20; +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | employees | NULL | range | idx_age | idx_age | 4 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> ```  还原索引 ```python alter table employees drop index idx_age ; ``` ### **索引总结** ------------  **like KK%相当于=常量，%KK和%KK% 相当于范围** ------------
李智
2025年3月17日 13:31
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
分享
链接
类型
密码
更新密码