1️⃣ 数据库调优的场景

上一篇mysql进阶优化篇，我们介绍了数据库的性能分析工具，知道了怎么发现数据库的性能问题，这一篇博客我们将介绍索引失效的10种情况及原理

先来了解下可能需要进行数据库优化的场景。

索引失效(按准则编写、调整sql)
没有充分利用到索引(建立索引)
关联查询太多的JOIN(JOIN查询性能与表的数目成指数相关，一般不超过三张，否则需要进行sql优化或者进行反范式化设计，增加必要的冗余)
服务器调优与各个参数的设置，如缓存、线程数等(修改my.conf)
数据过高(已经在软件层面充分调优，但仍不能面对高并发场景，可以考虑分表分库分散服务器压力)

接下来我们介绍下sql查询优化。虽然sql查询优化技术很多，但是大致不离 物理查查询优化 和 逻辑查询优化 两大块。

物理查询优化：通过索引和表连接方式进行优化
逻辑查询优化：通过sql语句的等价代换，实现数据库查询的优化。

2️⃣数据准备

学员表插50万条，班级表插1万条。

(1)建表

#班级表
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;

(2)设置参数

命令开启：允许创建函数设置：

set global log_bin_trust_function_creators=1;   
# 不加global只是当前窗口有效。

(3)创建函数

保证每条数据都不同。

#随机产生字符串
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 ;
#假如要删除
#drop function rand_string;

随机产生班级编号

#用于随机产生多少到多少的编号
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 ;
#假如要删除
#drop function rand_num;

(4)创建存储过程

创建往stu表中插入数据的存储过程

#创建往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 ;
#假如要删除
#drop PROCEDURE insert_stu;

创建往class表中插入数据的存储过程

#执行存储过程，往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 ;
#假如要删除
#drop PROCEDURE insert_class;

(5)调用存储过程

往class表添加1万条数据

#执行存储过程，往class表添加1万条数据 
CALL insert_class(10000);

往stu表添加50万条数据,这个时间会稍微有点长，请耐心等待哟。

#执行存储过程，往stu表添加50万条数据 
CALL insert_stu(100000,500000);

查询下数据是否插入成功。

SELECT COUNT(*) FROM class;
SELECT COUNT(*) FROM student;

(6)删除某表上的索引

创建删除索引存储过程。这是为了方便我们的学习，因为我们在演示某个索引的效果时，可能需要删除其它索引，如果需要一个个手工删除，就太费劲了。

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 ;

执行存储过程(可以先保留不执行)

CALL proc_drop_index("dbname","tablename");

3️⃣索引失效的情况

这里我们以InnoDB的B+树的索引结构作为讲解的重点，讲解索引失效的案例(3.1讲解索引最佳的实践)。之所以会出现索引失效的情况，其实是因为我们的优化器经过了成本开销的计算，决定不用索引。用不用索引都是优化器说了算，Sql语句是否会使用索引，跟数据库版本、数据量和数据选择度都有关系。

3.1 全值匹配我最爱(索引最佳)

全值匹配可以充分的利用组合索引。

在没有建立索引时会进行数据查询速度会比较慢。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

SQL_NO_CACHE表示不使用查询缓存。

下图是在没有创建索引的情况下，第一条sql的执行效果。其查询时间是0.048s。

下面建立下索引。

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);

💌Q 上面三个索引有什么区别，为什么这么建立索引？

上面建立索引是与三条sql的使用场景对应的，遵守了全值匹配的规则，就是说建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。

再次执行查询sql，就可以使用到索引idx_age。并且其查询耗时会变短为0.024s。

执行如下sql。选择的索引则是:idx_age_classid。思考下为什么？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;

这是因为我们在构建索引idx_age_classid的B+树时，会先按照age排序，在按照calssId排序，对于这个sql来说，更加高效。

但是上面的索引可能不生效哦，在数据量较大的情况下，我们进行全值匹配SELECT *，优化器可能经过计算发现，我们使用索引查询所有的数据后，还需要对查找到的数据进行回表操作，性能还不如全表扫描。这里我们没有造这么多数据，所以就不演示效果咯。

3.2 不遵守最左前缀匹配原则

运行如下sql。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name='abcd';

将使用索引idx_age。

下面的sql不会使用索引，因为我没没有创建classId或者name的索引。或者

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.name='abcd';

Q:为什么不会使用idx_age_classid索引？

索引idx_age_classid的B+树会先使用age排序，在使用classId给age相同的数据排序，这个索引根本用不上哟。这就是下面的最左前缀原则。

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

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

💞拓展：Alibaba《Java开发手册》

索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

下面的sql查询就是遵守这一原则的正确打开方式。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.classId=4 AND student.name='abcd';

思考：下面sql会不会使用索引呢？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.age = 30 AND student.name='abcd';

答案是会！因为优化器会执行优化的哦，会调整查询条件的顺序。不过在开发过程中我们还是要保持良好的开发习惯哟。

思考：删去索引idx_age_classid和idx_age,只保留idx_age_classid_name

DROP INDEX idx_age_classid ON student;
DROP INDEX idx_age ON student;

执行如下sql，会不会使用索引？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE  student.age = 30 AND student.name='abcd';

答案是会，但是只会用一部分。看看执行结果。

使用了idx_age_classid_name，但是key_len是5，也就是说只使用了age部分的排序，因为age是int类型，4个字节加上null值列表一共5个字节哦。想想就知道，B+树是先按照age排序，再按照classid排序，最后按照name排序，因此不能跳过classId的排序直接就使用name的排序哦。

3.3 不按照递增顺序插入主键

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

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

可这个数据页已经满了，再插进来咋办呢?我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：性能损耗！所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入

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

🎀Tips：

我们一般将主键策略设置为自动递增AUTO_INCREMENT哦！(核心业务表除外，后面会介绍这种情况)

Mysql进阶优化篇02——索引失效的10种情况及原理 1

1️⃣ 数据库调优的场景

2️⃣数据准备

(1)建表

(2)设置参数

(3)创建函数

(4)创建存储过程

(5)调用存储过程

(6)删除某表上的索引

3️⃣索引失效的情况

3.1 全值匹配我最爱(索引最佳)

3.2 不遵守最左前缀匹配原则

3.3 不按照递增顺序插入主键

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像