MySQL--优化（索引--索引失效场景）

MySQL–优化（索引–索引失效场景）

1. 定位慢查询
2. SQL执行计划
3. 索引
   • 存储引擎
   • 索引底层数据结构
   • 聚簇和非聚簇索引
   • 索引创建原则
   • 索引失效场景
4. SQL优化经验

---

常见的索引失效场景

1、场景准备：

• 给 tb_user 表创建联合索引，字段为：username, name, age,gender
  

// 创建联合索引
mysql> ALTER TABLE tb_user  ADD INDEX idx_username_name_age (username, name, age, gender ) ;

// 查看索引结构
mysql> show index from tb_user;

---

2、失效 – 违反最左前缀法则

• 违反最左前缀法则
  • 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。匹配最左前缀法则，走索引：

正常情况下：

// 命中索引 idx_username_name_age    长度  75
mysql>  explain select * from tb_user where username = 'bingyi';
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_username_name_age | idx_username_name_age | 75      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set (0.09 sec)


// 命中索引 idx_username_name_age    长度  108
mysql> explain select * from tb_user where username = 'bingyi' and name = '彬懿';
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_username_name_age | idx_username_name_age | 108     | const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set (0.09 sec)


// 命中索引 idx_username_name_age    长度  113
mysql> explain select * from tb_user where username = 'bingyi' and name = '彬懿' and age = '18' ;
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_username_name_age | idx_username_name_age | 113     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set (0.09 sec)

索引会失效情况下：

// 没有命中索引
mysql> explain select * from tb_user where name = '彬懿' and age = '18' ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.07 sec)



// 没有命中索引
mysql> explain select * from tb_user where age = '18' ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tb_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.07 sec)


// 命中索引 idx_username_name_age    长度  75，但只命中了一个
mysql> explain select * from tb_user where username = 'bingyi'  and age = '18' ;
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_username_name_age | idx_username_name_age | 75      | const |    1 |    33.33 | Using index condition |
+----+-------------+---------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set (0.08 sec)

---

3、失效 – 范围查询右边的列，不能使用索引

• 根据前面的三个字段 username name age 查询是走索引的，但是最后一个条件 gender 没有用到索引。
  

---

4、失效 – 不要在索引列上进行运算操作，索引将失效

---

5、失效 – 字符串不加单引号，造成索引失效

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。