索引优化与查询优化
MySQL
|
|
|
208

5968 字
|
38 分钟

索引优化与查询优化

1. 数据准备

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

CREATE DATABASE atguigudb2;
USE atguigudb2;

步骤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万条数据
CALL insert_class(10000);
#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);

步骤 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");

2. 索引失效案例

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响

  • 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能
  • 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢

大多数情况下都（默认）采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器?基于cost开销(CostBaseOptimizer)，它不是基于规则(Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系

开销不是基于时间

2.1 全值匹配

创建联合索引多个索引同时生效

系统中经常出现的sql语句如下:

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

建立索引前执行：（关注执行时间）

image-20231012100732587

建立索引：

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

建立索引后执行：

image-20231012101016641

可以看到，创建索引前的查询时间是0.55秒，创建索引后的查询时间是0.05秒，索引帮助我们极大的提高了查询效率

2.2 最左前缀法则

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

举例1:

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

image-20231012101601367

举例2:

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

image-20231012101758654

举例3:

索引idx_age_classid_name是否能正常使用?

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

image-20231012102047889

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

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

image-20231012102404356

虽然可以正常使用，但是只有部分被使用到了

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

image-20231012102522963

完全没有使用上索引

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

拓展：Alibaba《Java 开发手册》

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

2.3 主键插入顺序

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

image-20231012102827847

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

image-20231012102842472

可这个数据页已经满了，再插进来咋办呢？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？

意味着：性能损耗!所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有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属性，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂

2.4 函数、计算、类型转换

（1）函数

  1. 创建索引 
CREATE INDEX idx_name ON student(NAME);
  1. 这两条 SQL 哪种写法更好
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
  • 第一种：索引优化生效 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

image-20231012103720060

mysql> SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
+--------+--------+--------+------+---------+
| id     | stuno  | name   | age  | classId |
+--------+--------+--------+------+---------+
| 396546 | 496546 | ABcIld |   36 |     344 |
|  34180 | 134180 | ABcJmg |   49 |     464 |
|   3933 | 103933 | AbCJMJ |   11 |      25 |
| 476313 | 576313 | abcjMj |   36 |     951 |
| 492517 | 592517 | AbCJNN |   28 |     478 |
| 365738 | 465738 | aBCjoS |   21 |     463 |
| 460483 | 560483 | aBCjoT |   26 |     957 |
| 406972 | 506972 | AbCJPU |    6 |     897 |
| 477967 | 577967 | abcjPx |   43 |     902 |
| 227494 | 327494 | AbCKQa |   31 |      29 |
| 103552 | 203552 | aBCkrc |   14 |     164 |
| 172760 | 272760 | abckRF |   27 |     800 |
|   6261 | 106261 | aBCksh |   33 |     826 |
| 191784 | 291784 | ABcKsK |   20 |     834 |
| 305015 | 405015 | AbCLVv |   17 |     383 |
|  84688 | 184688 | AbCLVw |   21 |     741 |
|  25805 | 125805 | abclWa |   13 |     215 |
| 143050 | 243050 | ABcLwc |   45 |     278 |
| 188910 | 288910 | ABcLwY |   31 |     136 |
| 363761 | 463761 | AbCLWy |   30 |     492 |
+--------+--------+--------+------+---------+
20 rows in set, 1 warning (0.01 sec)
  • 第二种：索引优化失效 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

image-20231012103945771

mysql> SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
+--------+--------+--------+------+---------+
| id     | stuno  | name   | age  | classId |
+--------+--------+--------+------+---------+
|   3933 | 103933 | AbCJMJ |   11 |      25 |
|   6261 | 106261 | aBCksh |   33 |     826 |
|  25805 | 125805 | abclWa |   13 |     215 |
|  34180 | 134180 | ABcJmg |   49 |     464 |
|  84688 | 184688 | AbCLVw |   21 |     741 |
| 103552 | 203552 | aBCkrc |   14 |     164 |
| 143050 | 243050 | ABcLwc |   45 |     278 |
| 172760 | 272760 | abckRF |   27 |     800 |
| 188910 | 288910 | ABcLwY |   31 |     136 |
| 191784 | 291784 | ABcKsK |   20 |     834 |
| 227494 | 327494 | AbCKQa |   31 |      29 |
| 305015 | 405015 | AbCLVv |   17 |     383 |
| 363761 | 463761 | AbCLWy |   30 |     492 |
| 365738 | 465738 | aBCjoS |   21 |     463 |
| 396546 | 496546 | ABcIld |   36 |     344 |
| 406972 | 506972 | AbCJPU |    6 |     897 |
| 460483 | 560483 | aBCjoT |   26 |     957 |
| 476313 | 576313 | abcjMj |   36 |     951 |
| 477967 | 577967 | abcjPx |   43 |     902 |
| 492517 | 592517 | AbCJNN |   28 |     478 |
+--------+--------+--------+------+---------+
20 rows in set, 1 warning (0.68 sec)

type为ALL，表示没有使用到索引，查询时间为0.68秒，查询效率较之前低很多

（2）计算

  • student 表的字段 stuno 上设置有索引 
CREATE INDEX idx_sno ON student(stuno);
  • 索引优化失效：（假设：student 表的字段 stuno 上设置有索引）
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
  • 运行结果：

image-20231012104352896

  • 索引优化生效（没有计算）：
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

image-20231012104712830

（3）类型转换(自动或手动)

  • student 表的字段 name 上设置有索引 
CREATE INDEX idx_name ON student(NAME);
  • name = 123发生类型转换，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 123;

image-20231012111132623

  • 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = '123';

image-20231012111302271

要注意隐式类型转换，它也会使用到函数使索引失效

2.5 范围查询右边的列

  1. 删除多余索引
SHOW INDEX FROM student;

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

CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);
  1. 范围查询SQL
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc';

image-20231012112205980

因为用上了范围查找，在范围查找的索引后面的索引就失效了

解决办法：

因为范围条件导致的索引失效，可以考虑把确定的索引放在前面

create index idx_age_name_classId on student(age,name,classId);

哪些属于范围？

  1. >=、>、<=、<
  2. between

应用开发中范围查询，例如：金额查询，日期查询往往都是范围查询。应将查询条件放置 where 语句最后（创建的联合索引中，务必把范围涉及到的字段写在最后）

2.6 不等于(!= 或者<>)

  1. 为 name 字段创建索引
CREATE INDEX idx_name ON student(NAME);
  1. 查看索引是否失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';
#或
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';

image-20231012113217573

场景举例：用户提出需求，将财务数据，产品利润金额不等于 0 的都统计出来

并不是说不等于就一定失效，优化器都是基于成本考虑的

例如：使用覆盖索引时，只查询索引直接能查出的字段，不需要回表操作时，是能使用索引的

2.7 IS NULL 可以，IS NOT NULL 无法使用

  • IS NULL: 可以触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

image-20231012113500433

  • IS NOT NULL: 无法触发索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

image-20231012113844497

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

扩展：同理，在查询中使用 NOT LIKE 也无法使用索引，导致全表扫描

2.8 LIKE 以通配符 % 开头

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

  • 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'ab%';

image-20231012141620496

  • 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%ab%';

image-20231012141711833

拓展：Alibaba《Java 开发手册》

【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

并不是说不等于就一定失效，优化器都是基于成本考虑的

例如：使用覆盖索引时，只查询索引直接能查出的字段，不需要回表操作时，是能使用索引的

当使用覆盖索引时，即使是会造成索引失效的 LIKE '%xxx' 过滤，优化器也会认为使用索引成本更小

2.9 OR 前后存在非索引的列

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

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

查询语句使用 OR 关键字的情况：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

image-20231012142133542

因为 classId 字段上没有索引，所以上述查询语句没有使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = 'Abel';

image-20231012142503157

因为 age 字段和 name 字段上都有索引，所以查询中使用了索引。你能看到这里使用到了 index_merge，简单来说 index_merge 就是对 age 和 name 分别进行了扫描，然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描

2.10 MySQL 自动评估

MySQL 在查询时，会评估使用索引的效率与走全表扫描的效率。如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效

2.11 总结

假设: index(a,b,c)

image-20231012143806842

一般性建议:

  • 对于单列索引,尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
  • 在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引
  • 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

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

3. 关联查询优化

3.1 数据准备

#分类
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);

#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

#向分类表中添加20条记录
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));

#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

3.2 左外连接

下面开始 EXPLAIN 分析

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

image-20231013152758931

结论：type 有 ALL

  1. 给被驱动表（book）添加索引
#[被驱动表]，可以避免全表扫描
CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

image-20231013153104482

可以看到第二行的 book 变为了 ref，rows 优化也比较明显。这是由于左外连接特性决定的。LEFT JOIN 条件是逐次用左表的每一条记录与右表的全部记录比较，满足条件才放到结果集中，所以右表是我们的关键点，一定需要建立索引

如果只能添加一边的索引，那就给被驱动表添加上索引

  1. 给驱动表（type）添加索引
#[驱动表]，无法避免全表扫描
CREATE INDEX X ON `type`(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

image-20231013153817077

  1. 删除被驱动表（book）索引
DROP INDEX Y ON book;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

image-20231013153942731

去掉被驱动表索引，又变成了 join buffer

3.3 内连接

前置知识

image-20231013154203094

#如果已经删除了可以不用再执行该操作
DROP INDEX X ON type;
DROP INDEX Y ON book;

换成 INNER JOIN（MySQL 自动选择驱动表）

EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN book ON type.card = book.card;

image-20231013155536907

  1. book 添加索引
CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

image-20231013160413023

  1. type 添加索引
CREATE INDEX X ON `type`(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

image-20231013160740610

  1. 删除 book 索引
DROP INDEX Y ON book;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

image-20231013161055627

  1. book 添加索引
CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

image-20231013161220104

  1. 向驱动表（book）中添加数据（20条数据）
#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

image-20231013161650295

图中发现，由于 book 表中数据大于 type 表中数据，MySQL 选择将 type 作为驱动表

结论：

  • 内连接 驱动表和被驱动表是由优化器决定的。优化器认为哪个成本比较小，就采用哪个表作为驱动表（驱动表要全表扫描）

  • 如果两张表只有一个有索引，那有索引的表作为被驱动表

    • 原因：驱动表要全表扫描，而被驱动表可以通过索引加快查找

  • 两个索引都存在的情况下，数据量小的作为驱动表（小表驱动大表）

    • 原因：驱动表要全表扫描，数据量越少越好

4. 子查询优化

MySQL 从 4.1 版本开始支持子查询，使用子查询可以进行 SELECT 语句的嵌套查询，即一个 SELECT 查询的结果作为另一个 SELECT 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。原因:

  1. 执行子查询时 MySQL 需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询
  2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响
  3. 对于返回结果集比较大的子查询，其对查询性能的影响也就越大

在 MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好

结论: 尽量不要使用 NOT IN 或者 NOT EXISTS，用 LEFT JOIN xxx ON xx WHERE xx IS NULL 替代

即尽量用连接查询代替子查询

5. 排序优化

问题: 在 WHERE 条件字段上加索引但是为什么在 ORDER BY 字段上还要加索引呢?

回答:

在 MySQL 中，支持两种排序方式，分别是Index和FileSort排序

  • Index 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高
  • FileSort 排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低（也有特殊情况 FileSort 速率优于 Index）

优化建议:

  1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描，在 ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率
  2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列;如果不同就使用联合索引
  3. 无法使用 Index 时，需要对 FileSort 方式进行调优

结论：

  1. 两个索引同时存在，MySQL 自动选择最优的方案。但是，随着数据量的变化，选择的索引也会随之变化的
  2. 当【范围条件】和【GROUP bY 或者 ORDER BY】的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然

6. GROUP BY优化

  1. GROUP BY 使用索引的原则几乎跟 ORDER BY 一致，GROUP BY 即使没有过滤条件用到索引，也可以直接使用索引
  2. GROUP BY 先排序再分组，遵照索引建的最佳左前缀法则
  3. 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
  4. WHERE 效率高于 HAVING，能写在 WHERE 限定的条件就不要写在 HAVING 中了
  5. 减少使用 ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做
  6. ORDER BY、GROUP BY、DISTINCT 这些语句较为耗费 CPU，数据库的 CPU 资源是极其宝贵的
  7. 包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢

7. 优化分页查询

  1. 避免大偏移：尽量避免使用大的偏移量来获取数据，因为这会导致数据库扫描并跳过许多行，消耗更多的资源
  2. 锚定点分页：考虑使用锚定点分页（也称为键分页）方法，其中你使用上一页的最后一行的键值来获取下一页的数据，而不是使用 OFFSET
  3. 覆盖索引：确保查询使用覆盖索引，这样数据库可以只通过索引获取数据，而不需要访问表的数据行

8. 覆盖索引

覆盖索引（Covering Index）是数据库优化的一个概念。当一个查询语句的执行可以只用索引来完成，不需要回表查询原数据表时，我们称这个索引为覆盖索引。换句话说，如果一个索引包含（或覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”

优点：

  1. 减少磁盘I/O：由于数据可以从索引中直接获取，因此减少了访问基表的磁盘I/O
  2. 提高查询性能：覆盖索引可以显著提高查询性能，因为存储引擎可以返回索引中的信息，而无需访问表
  3. 减少数据查找时间：由于不需要回表，数据查找的时间也会大大减少

缺点：

  1. 占用额外空间：覆盖索引需要存储额外的列数据，因此会占用更多的磁盘空间
  2. 维护成本：当数据表更新时，相关的覆盖索引也需要更新，这会带来额外的维护成本
  3. 设计复杂度：设计合适的覆盖索引需要充分理解查询模式，可能会增加设计的复杂度
  4. 插入、更新和删除操作性能下降：由于在插入、更新和删除记录时需要同时维护索引，这可能导致这些操作的性能下降

9. 前缀索引

前缀索引是在数据库中创建索引时，只对列的一个子集（即前缀）进行索引的过程。例如，如果有一个存储 URL 的列，我们可以只对 URL 的前 30 个字符创建索引，而不是整个字符串

优点：

  1. 空间效率：由于只索引部分数据，前缀索引通常占用的存储空间较少
  2. 查询性能：在某些情况下，使用前缀索引可以提高查询性能，因为索引的大小较小，搜索速度更快
  3. 适用于长字符串：对于非常长的字符串或文本字段，使用前缀索引是一种有效的优化手段

缺点：

  1. 准确性：由于只索引了部分数据，使用前缀索引可能无法实现非常精确的查询
  2. 复杂性：确定合适的前缀长度可能比较复杂，需要基于数据分布和查询模式进行分析
  3. 不支持所有查询：前缀索引可能不支持所有类型的字符串查询，例如，它通常不支持 LIKE 查询中的后缀通配符

使用场景：

  1. 长文本字段：当表中包含长文本字段，并且这些字段通常用于搜索时，可以使用前缀索引
  2. 存储空间有限：在存储空间有限或者存储成本较高的情况下，可以使用前缀索引来减少索引占用的空间
  3. 查询优化：当查询主要集中在字符串的前缀部分时，使用前缀索引可以提高查询性能

10. 索引下推

索引下推是一种数据库查询优化技术。在使用索引检索数据时，数据库管理系统（DBMS）可以在索引扫描阶段就过滤掉一部分不满足条件的记录，而不是在检索到所有可能的记录后再进行过滤。这样，DBMS 可以减少访问基表的次数和数据量，从而提高查询性能

  • 打开索引下推

    在MySQL中，可以通过设置optimizer_switch变量来启用索引下推：

    SET optimizer_switch='index_condition_pushdown=on';

  • 关闭索引下推

    同样地，在MySQL中，可以通过设置optimizer_switch变量来禁用索引下推：

    SET optimizer_switch='index_condition_pushdown=off';
  1. ICP的应用场景：ICP（索引条件下推）可以在表访问类型为range、ref、eq_ref和ref_or_null时被使用
  2. ICP的适用表类型：ICP 可以被应用于InnoDB和MyISAM表类型，包括它们的分区表形式
  3. ICP在InnoDB表的使用：对于InnoDB表来说，ICP 主要用于二级索引。其核心目标是减少全行读取的次数，进而降低 I/O 操作的数量
  4. ICP与覆盖索引的关系：
    • 当 SQL 查询使用覆盖索引时，ICP 不会被支持，因为在这种情况下，ICP 的使用不会进一步减少 I/O 操作
    • 需要注意的是，覆盖索引的使用场景中不需要回表操作。而 ICP 的主要作用是在回表前就过滤掉一部分数据，减少回表的次数和数据量，因此在使用覆盖索引的情况下，ICP 不会带来额外的优势
  5. ICP与子查询的关系：在涉及到相关子查询的条件中，ICP 是不可用的
🌟 如果您喜欢我的文章，欢迎赞赏支持，您的支持是我创作的最大动力！🌟
🖋 作者：Enndfp
🔗链接：https://blog.enndfp.cn
📜版权声明：您可以自由转载，但请务必注明原文地址，感谢您的尊重与支持~
MySQL
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇 

                    

                    
			        推荐文章
		            

		            

							
日志

							
							

							
多版本并发控制

							
							

							
锁

							
							

							
事务

							
							

							
性能分析工具的使用

							
							

							
索引

							
							

							
存储引擎

							
							

							
逻辑架构