MySQL索引失效案例

于 2024-11-23 11:25:08 发布
阅读量932 收藏 24
点赞数 19
CC 4.0 BY-SA版权
分类专栏： MySQL 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/YouPromise/article/details/143989143

目录

1.全值匹配

2.最佳左前缀法则

3.主键插入顺序

4.计算、函数、类型转换(自动或手动)导致索引失效

5.类型转换导致索引失效

6.范围条件右边的列索引失效

7.不等于(!= 或者<>)索引失效

8.is null可以使用索引，is not null无法使用索引

9.like以通配符%开头索引失效

10.OR 前后存在非索引的列，索引失效

11.不同的字符集进行比较前需要进行 转换、会造成索引失效

12.总结

1.全值匹配

#1）全值匹配我最爱
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
/*语句一：没有索引
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498858 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
/*语句二：没有索引
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498858 |     1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
/*语句三：没有索引
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498858 |     0.10 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/

SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
#Empty set, 1 warning (0.13 sec)

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
/*
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                | key     | key_len | ref   | rows  | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age | 5       | const | 10182 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------+
*/
#分别为student表创建三个索引：
CREATE INDEX idx_age ON student(age);#索引一

CREATE INDEX idx_age_classid ON student(age,classId);#索引二

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);#索引三

#显示student表上的索引
SHOW INDEX FROM student;
#再次执行有索引的语句一：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
/*使用了索引一：索引字段为age
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                | key     | key_len | ref   | rows  | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age | 5       | const | 10182 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------+
*/
#再次执行有索引的语句二：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
/*使用了索引二：索引字段为age、classId        
+----+-------------+---------+------------+------+----------------------------------------------+-----------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                | key             | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+-----------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid | 10      | const,const |    9 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------------------------+-----------------+---------+-------------+------+----------+-------+
*/

#再次执行有索引的语句三：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
/*使用了索引三：索引字段为age、classId  、NAME
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                | key                  | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid_name | 73      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
*/
#全值匹配表示索引列和查询条件的字段全部匹配，精度高，key_len长度大
#结论：
#当创建多个索引时，查询优化器通常会选取和查询字段匹配度最高的索引
#因为匹配度越高，查询效率越快
#此时除被选中的索引外，其它索引失效

补充：

上面SQL语句中SQL_NO_CACHE的使用保证不存在查询缓存，使各语句的比较不受“是否缓存”的影响，从而达到了“控制变量”的目的

2.最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配

#2）最佳左前缀法则
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;
/*使用了索引一：索引字段为age
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                                | key     | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age | 5       | const | 10182 |    10.00 | Using where |
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------------+
*/
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';
/*没有使用索引：因为没有classid索引、也没有（classid、name）索引
  没有使用(age,classId,NAME)索引的原因：不符合最佳左前缀法则
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498858 |     1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/
EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE classid=4 AND student.age=30 AND student.name = 'abcd'; 
/*使用了索引三(age,classId,NAME)：索引字段为age、classId  、NAME
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                | key                  | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid_name | 73      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
*/

结论:

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，多列(或联合)索引不会被使用。

3.主键插入顺序

        对于一个使用InnoDB存储引擎的表来说，在没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果插入的记录的主键值是依次增大的话，那每插满一个数据页就换到下一个数据页继续插，而如果插入的主键值忽大忽小的话（一般不让这种情况发生），就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间:

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：

可这个数据页已经满了，再插进来咋办呢？

需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。

页面分裂和记录移位意味着什么？

意味着： 性能损耗 ！所以如果想尽量避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。

所以建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是手动插入 ，比如： person_info 表：

CREATE TABLE person_info(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	birthday DATE NOT NULL,
	phone_number CHAR(11) NOT NULL,
	country varchar(100) NOT NULL,
	PRIMARY KEY (id),
	KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

我们自定义的主键列 id 拥有AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

4.计算、函数、类型转换(自动或手动)导致索引失效

#4)计算、函数、类型转换(自动或手动)导致索引失效
CREATE INDEX idx_name ON student(NAME);#创建索引(NAME)

#此语句比下一条要好！（能够使用上索引）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
/*
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_name      | idx_name | 63      | NULL |   36 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
*/
#LEFT(student.name,3) = 'abc'; 中left函数的使用导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 
/*
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498858 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/
#再举例
CREATE INDEX idx_sno ON student(stuno);#创建索引(stuno)

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
/*使用“stuno+1 = 900001”算术运算导致索引失效
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498858 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
/*使用了索引：
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_sno       | idx_sno | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
*/
#再举例
EXPLAIN SELECT id, stuno, NAME FROM student WHERE SUBSTRING(NAME, 1,3)='abc';
/*索引失效
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498858 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/

5.类型转换导致索引失效


#5)类型转换导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123; 
/*没有使用索引：name是字符串类型，和int匹配要类型转换
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 498858 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123'; 
/*使用了索引：
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name      | idx_name | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
*/

6.范围条件右边的列索引失效

范围条件：含(<) (<=) (>) (>=)和between等的条件

#6)范围条件右边的列索引失效
SHOW INDEX FROM student;

CALL proc_drop_index('atguigudb2','student');#清空所有student表的索引


CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);#创建联合索引 idx_age_classId_name

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ; 
/*Using index condition表示：有些搜索条件中虽然出现了索引列，但却不能使用到索引
#使用了索引 idx_age_classId_name但是只用了联合索引的前两个字段
# 结合`age` INT(3)占5 、 `classId` INT(11)占5 以及key_len=10可知只使用了前两个字段 
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_age_classId_name | idx_age_classId_name | 10      | NULL | 18456 |    10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
*/

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20; 
/*对于优化器来说AND连接的这几个条件可以任意颠倒，故此SQL语句和上一句执行效果一样   联合索引classId在中间
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_age_classId_name | idx_age_classId_name | 10      | NULL | 18456 |    10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
*/
#建一个新的索引
CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);
EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20; 
/*在新索引下三个字段都用上了
+----+-------------+---------+------------+-------+---------------------------------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                         | key              | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------------------------------+------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_age_classId_name,idx_age_name_cid | idx_age_name_cid | 73      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------------------------------+------------------+---------+------+------+----------+-----------------------+
*/
#补充说明：
#对于优化器来说AND连接的字段先写哪个后写哪个无所谓
#具体使用了哪几个字段只和索引中定义字段的位置以及哪个字段使用了范围查询有关
#“范围条件右边的列”中的右-->是左是右要看索引中定义字段的相对位置，而不是字段在where中的位置

启发：

应用开发中范围查询，例如:金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后，建索引时也放在最后

(创建的联合索引中，务必把范围涉及到的字段写在最后)

7.不等于(!= 或者<>)索引失效

#7)不等于(!= 或者<>)索引失效
#不等于时用不上B+树，只能一个一个查找
CREATE INDEX idx_name ON student(NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
#或
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;
/*索引失效
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 498858 |    50.15 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/

8.is null可以使用索引，is not null无法使用索引

#8）is null可以使用索引，is not null无法使用索引
#is null可以使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 
#is not null无法使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

结论：最好在设计数据表的时候就将字段设置为 NOT NULL约束，比如可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串’’’’。

拓展:同理,在查询中使用not like 也无法使用索引，导致全表扫描

9.like以通配符%开头索引失效

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为"%”，索引就不会起作用。只有"%"不在第一个位置，索引才会起作用

#9)like以通配符%开头索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%'; 
/*使用了索引
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_name      | idx_name | 63      | NULL |  711 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
*/
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';
/*
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498858 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/

10.OR 前后存在非索引的列，索引失效

        在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引

因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效

#10)OR 前后存在非索引的列，索引失效
SHOW INDEX FROM student;

CALL proc_drop_index('atguigudb2','student');

CREATE INDEX idx_age ON student(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
/*没有使用索引
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_age       | NULL | NULL    | NULL | 498858 |    11.88 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
*/
#为前后两个索引都创建索引，则OR连接他们时就可以使用索引
CREATE INDEX idx_cid ON student(classid);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
/*因为age字段和classid字段上都有索引，所以查询中使用了索引
+----+-------------+---------+------------+-------------+-----------------+-----------------+---------+------+-------+----------+-------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys   | key             | key_len | ref  | rows  | filtered | Extra                                     |
+----+-------------+---------+------------+-------------+-----------------+-----------------+---------+------+-------+----------+-------------------------------------------+
|  1 | SIMPLE      | student | NULL       | index_merge | idx_age,idx_cid | idx_age,idx_cid | 5,5     | NULL | 10612 |   100.00 | Using union(idx_age,idx_cid); Using where |
+----+-------------+---------+------------+-------------+-----------------+-----------------+---------+------+-------+----------+-------------------------------------------+
能看到这里使用到了index_merge，简单来说index_merge就是对age和classid分别进行了扫描，然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描
*/

11.不同的字符集进行比较前需要进行 转换、会造成索引失效

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行 转换会造成索引失效

建议：数据库和表的字符集统一使用utf8mb4

12.总结

对于单列索引，尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好

在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引

在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

总之，书写SQL语句时，尽量避免造成索引失效的情况

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值