Mysql高级篇学习总结12:索引失效的11种情况
可以在哪些维度进行数据库调优?
- 索引失效、没有充分利用到索引。——建立有效索引
- 关联查询提案多join。——优化SQL
- 服务器调优及各个参数设置。——调整my.cnf
- 数据过多。——分库分表
sql查询优化的技术有很多,大方向上可以分为物理查询优化和逻辑查询优化两大块。
- 物理查询优化是通过索引和表连接方式等技术来进行优化,重点要掌握索引的使用;
- 逻辑查询优化是通过sql等价变换来提升查询效率,也就是换一种查询写法执行效率可能更高;
1、数据准备
1.1 建表
首先建2张表:班级表(class)、学生表(student)
CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
1.2 构建存储函数
构建2个存储函数:产生随机字符串函数(rand_string)、产生随机编号函数(rand_num)
SET GLOBAL log_bin_trust_function_creators=1;
#随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
#用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;
1.3 构建存储过程
构建存储过程,往class表和student表里插入数据
#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES ((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
#执行存储过程,往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
1.4 执行存储过程
执行存储过程:往class表添加1万条数据 、往stu表添加100万条数据
#执行存储过程,往class表添加1万条数据
CALL insert_class(10000);
#执行存储过程,往stu表添加100万条数据
CALL insert_stu(100000,1000000);
查看是否插入成功:
mysql> SELECT COUNT(*) FROM class;
+----------+
| COUNT(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.12 sec)
1.5 构建删除索引的存储过程
为了方便后面删除某个表的所有索引,建立一个存储过程:
DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY' ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=2 ;
#若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END //
DELIMITER ;
2、索引失效的11种情况
2.1 尽可能全值匹配
在没有索引的时候,此时检索是没有用到索引的:
mysql> 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 | 997565 | 0.10 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1)对字段age构建索引idx_age,此时查询会用索引:idx_age
mysql> CREATE INDEX idx_age ON student(age);
Query OK, 0 rows affected (7.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | ref | idx_age | idx_age | 5 | const | 37626 | 1.00 | Using where |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+-------+----------+-------------+
2)再对字段age, classId构建索引idx_age_classid,此时查询会用索引:idx_age_classid
mysql> CREATE INDEX idx_age_classid ON student(age,classId);
Query OK, 0 rows affected (10.52 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | ref | idx_age,idx_age_classid | idx_age_classid | 10 | const,const | 22 | 10.00 | Using where |
+----+-------------+---------+------------+------+-------------------------+-----------------+---------+-------------+------+----------+-------------+
3)再对字段age, classId, Name构建索引idx_age_classid_name,此时查询会用索引:idx_age_classid_name
mysql> CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);
Query OK, 0 rows affected (10.94 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | ref | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid_name | 73 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
因此当用AND时,如果有多个多个列的索引可以匹配时,优化器会选择匹配最多的。
2.2 最佳左前缀匹配
建立联合索引时,检索数据会从联合索引的最左边开始匹配。
1)虽然在1中已经建了3个索引:idx_age,idx_age_classid,idx_age_classid_name。但是这3个索引的最左边的索引列都是age,因此检索classid和name时,就无法使用索引。
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.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 | 997565 | 1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
对于多列索引,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。
2.3 主键插入顺序
主键插入的顺序如果忽大忽小,那么可能发生页分裂,这样就有严重的性能损耗。因此最好让插入的记录主键值依次递增。
2.4 计算、函数导致索引失效
使用函数时也会导致索引无效!
比如此时给name添加索引,使用函数的sql却无法使用创建的索引。
mysql> CREATE INDEX idx_name ON student(NAME);
Query OK, 0 rows affected (11.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | 54 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> 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 | 997565 | 100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
2.5 类型转换导致索引失效
类型转换也会导致索引失效。
比如下面的name是varchar类型,需要先把123转化为’123’,因此没有用上索引。
mysql> 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 | ALL | idx_name | NULL | NULL | NULL | 997565 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 4 warnings (0.00 sec)
mysql> 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 |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
2.6 范围条件右边的列索引失效
首先先把student表的索引都删除掉:
CALL proc_drop_index('dbtest1','student');
然后对列age,classId,NAME 创建联合索引,此时检索sql里的classId右边的name索引没有被用上,因为key_len只有10,因为范围条件右边的列索引失效。
mysql> CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);
Query OK, 0 rows affected (11.93 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student
-> WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
| 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 | 37590 | 10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
应用开发中,如果要进行范围查询,应将范围查询放到where的最后。
此时先对列age,NAME,classId创建索引,然后将范围查询放到最后,就可以用到所有索引列了。
mysql> CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);
Query OK, 0 rows affected (10.71 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 |
+----+-------------+---------+------------+-------+---------------------------------------+------------------+---------+------+------+----------+-----------------------+
2.7 不等于索引失效
即使对列name创建一个索引,如果使用不等于,也无法使用到索引:
mysql> CREATE INDEX idx_name ON student(NAME);
Query OK, 0 rows affected (7.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 | 997565 | 50.15 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
2.8 is null可以使用索引,is not null无法使用索引
和2.7的原理一致,is not null也无法使用索引
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
+----+-------------+---------+------------+------+---------------------------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------------------------------+----------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | student | NULL | ref | idx_age_classId_name,idx_age_name_cid | idx_age_classId_name | 5 | const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------------------------------+----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
+----+-------------+---------+------------+------+---------------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------------------------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | student | NULL | ALL | idx_age_classId_name,idx_age_name_cid | NULL | NULL | NULL | 997565 | 50.00 | Using where |
+----+-------------+---------+------------+------+---------------------------------------+------+---------+------+--------+----------+-------------+
因此,在设计表的时候,最好就将字段设置为NOT NULL约束,比如可以将INT类型的字段,默认值设为0,将字符串类型的默认值设置为空字符串’'。
同理,not like也无法使用索引,也会导致全表扫描。
2.9 like以通配符%开头索引失效
和2.7的原理一致,like 以通配符%开头也无法使用索引。
mysql> 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 | 1483 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> 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 | 997565 | 11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
2.10 OR前后存在非索引的列,索引失效
OR前后2个条件中的列都是索引时,查询才会使用索引。只有一个条件列是索引,该查询还是会进行全表扫描。
2.11 数据库和表的字符集应该统一使用utf8mb4
统一字符集可以避免由于字符集转换产生的乱码,不同的字符集进行比较前需要进行转换,会造成索引失效。