• 索引优化与查询优化

    文章目录

    简介

    • 物理查询优化是通过索引和表连接方式等技术来进行优化，这里重点需要掌握索引的使用。
    • 逻辑查询优化就是通过SQL等价变换提升查询效率，直白一点就是说，换一种查询写法效率可能更高。

    数据准备

    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;

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

#随机产生字符串
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;

#创建往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表添加随机数据
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;

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

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    # 去除索引
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 ;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    索引失效案例

    最佳左前缀法则（联合索引）

    联合索引B+树模型
    在这里插入图片描述

    联合索引字段顺序
    在这里插入图片描述

    # 索引失效，因为索引B+树模型中，优先就是先判断第一个联合字段age,在判断age的情况下才会触发classId和name
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=1 AND student.name = 'abcd';

    • 1
    • 2

    在这里插入图片描述

    # 联合索引字段全部用到，在age触发情况下会继续精确到classId,映射到上面模型图，根数据页定位到页42，发现有两个目录项符合查询条件，这时候就走classId字段的索引判断o和m，根据查询条件定位到页35，接着走name字段索引定位最后的用户数据
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId = o AND (student.age=4 OR student.age=5) AND student.name = '1';

    • 1
    • 2

    在这里插入图片描述

    # 先走了age字段索引，但没触发classId字段，也就是连着name字段一起失效了，B+树模型就是先定位age=4，也就是走页59，接着走页55，但不会定位name了，因为没有classId触发，所以失效，只能按照链表遍历该页55的数据，所以key_len=5，联合索引只有部分生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=4 AND student.name = 'abcd';

    • 1
    • 2

    在这里插入图片描述

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

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

    # 函数
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
# 计算
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
# 类型转换，name是varchar,这里是数字，会自动类型转化，导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    上面情况都会失效，主要是B+树模型不能判断函数等，都是遍历全表，拿数据转换后对比查询条件，一条一条比较

    范围条件右边的列索引失效（联合索引）

    # 联合索引字段classId在name之前，classId范围查询后导致name字段索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc'

    • 1
    • 2
    • 3

    在这里插入图片描述

    没有用到name索引长度（63）

    范围查询的字段，比如时间日期，余额等，创建联合索引时应该把范围查询字段放置在最后

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

    # ALL 
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE name != 'SyQIRj'

    • 1
    • 2
    • 3

    我测试只测出来对于单值索引会失效和联合索引第一个字段也会失效，如果联合索引第二个字段不等于则不会失效

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

    # 也用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

    • 1
    • 2
    # 不可用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

    • 1
    • 2

    结论：最好在设计数据库的时候就将字段设置为 NOT NULL 约束，比如你可以将 INT 类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串('')。
    扩展：同理，在查询中使用not like也无法使用索引，导致全表扫描。

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

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

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

    • 1
    • 2

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

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

    在WHERE子句中，如果在OR前的条件列进行了索引，而在OR后的条件列没有进行索引，那么索引会失效。也就是说，OR前后的两个条件中的列都是索引时，查询中才使用索引。因为OR的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此所以的条件列也会失效。

    # 未使用到索引,因为classId字段上没有索引，所以上述查询语句没有使用索引。
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

    • 1
    • 2

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

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

    总结

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

    查询优化

    数据准备

    # 分类
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)));

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56

    Join原理

    join方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL5.5版本之前，MySQL只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)。如果关联表的数据量很大，则join关联的执行时间会很长。在MySQL5.5以后的版本中，MySQL通过引入BNLJ算法来优化嵌套执行。

    Simple Nested-Loop Join (简单嵌套循环连接)

    算法相当简单，从驱动表中取出一条数据1，遍历表B，将匹配到的数据放到result.. 以此类推，驱动表A中的每一条记录与被驱动表B的记录进行判断，假如表A数据100条，表B数据1000条计算，则A*B=10万次。
    在这里插入图片描述

    Index Nested-Loop Join （索引嵌套循环连接）

    从驱动表取一条数据，现在匹配值对于被驱动表已经是一个固定值了，我们就可以走索引查询匹配当前A数据的表B数据集
    在这里插入图片描述

    # 1.取type表的一条记录A
# 2.记录A的card值为1
# 3.此时要检索表book的匹配数据集，可以抽象成第二条sql，如果表book的card字段加索引，会大大加快检索效率
# 4.循环1-3，直到遍历完表type
EXPLAIN SELECT SQL_NO_CACHE * 
FROM `type` LEFT JOIN book 
ON `type`.card = book.card;

# 第二条sql
SELECT * FROM book
WHERE card = 1

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    Block Nested-Loop Join（块嵌套循环连接）

    但索引失效或者没有索引情况下，在mysql5.5之后，并不会采用SNLJ(效率太低)，而是采用BNLJ，他不会一条一条去加载，而是加载一块数据缓存起来去比较匹配
    在这里插入图片描述
    这里缓存的不只是关联表的列，select后面的列也会缓存起来。在一个有N个join关联的sql中会分配N-1个join buffer。所以查询的时候尽量减少不必要的字段，可以让join buffer中可以存放更多的用户记录。

    • block_nested_loop

    通过show variables like '%optimizer_switch% 查看 block_nested_loop状态。默认是开启的。

    • join_buffer_size

    驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256k。

    join_buffer_size的最大值在32位操作系统可以申请4G，而在64位操作系统下可以申请大于4G的Join Buffer空间（64位Windows除外，其大值会被截断为4GB并发出警告）。

    Join小结

    • 整体效率比较：INLJ > BNLJ > SNLJ
    • 永远用小结果集驱动大结果集（小的度量单位指的是表行数 * 每行大小，小结果集是where过滤后的数据量）

    大结果集可以走索引，开销相对较小，而对于外连接，驱动表都是要全表遍历的，所以选择小表驱动大表

    • 为被驱动表匹配的条件增加索引(减少内存表的循环匹配次数)
    • 增大join buffer size的大小（一次索引的数据越多，那么内层包的扫描次数就越少）
    • 减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）
    • 从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join

    索引建议

    • 保证被驱动表的JOIN字段已经创建了索引
    • 需要JOIN 的字段，数据类型保持绝对一致。
    • LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
    • INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
    • 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
    • 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
    • 衍生表建不了索引

    子查询优化

    子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。原因：
    ① 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
    ② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引 ，所以查询性能会 受到一定的影响。
    ③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

    # 物化成多表查询
EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
	SELECT monitor
	FROM class c
	WHERE monitor IS NOT NULL
)

# 推荐用多表连接
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` is NOT NULL;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    排序优化

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

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

    优化建议

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

    失效案例

    order by 时不limit,索引失效

    # 需要排序的数据量太大，如果走二级索引，因为查询列为* 号，需要回表，还不如加载到内存走filesort
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid

    • 1
    • 2

    在这里插入图片描述

    order by 时顺序错误，索引失效

    # 索引为idx_age_stuno_name
# 联合索引顺序对不上导致失效，一定要最左优先（age在前）
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY stuno, age, classid LIMIT 10
# 可以，符合联合索引最左边两个的顺序
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, stuno LIMIT 10

    • 1
    • 2
    • 3
    • 4
    • 5

    order by 时规则不一致，索引失效（顺序错，不索引；方向反，不索引）

    # 升序和降序同时，方向反导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age DESC, stuno ASC LIMIT 10
# 不遵从左前缀法则，没用age,失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY stuno ASC LIMIT 10

    • 1
    • 2
    • 3
    • 4

    不能使用索引进行排序

    不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    GROUP BY优化

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

    优先考虑覆盖索引

    什么是覆盖索引

    • 理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

    • 理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列 （即建索引的字段正好是覆盖查询条件中所涉及的字段）。

    • 简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

    CREATE INDEX idx_age_stuno_name ON student (age,stuno,name);
# 会使用索引，因为查询结果集字段都在联合索引中，扫描索引就能得到全部数据，不用回表，开销代价比全表扫描小，如果用*就会导致索引失效，因为要回表，代价会比全表扫描大
EXPLAIN SELECT age, stuno, name FROM student WHERE NAME LIKE '%abc';

    • 1
    • 2
    • 3

    注意点

    使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

    好处

    1.避免Innodb表进行索引的二次查询(回表)

    2.可以把随机IO变成顺序IO加快查询效率

    如果查询二级索引，同一个数据页的叶子节点是连续的，但当需要回表时，两个连续的叶子节点回表就是随机IO,如果不用回表就是顺序IO

    索引下推ICP

    ICP的开启/关闭

    # 打开索引下推
SET optimizer_switch = 'index_condition_pushdown=on';

# 关闭索引下推
SET optimizer_switch = 'index_condition_pushdown=off';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 当使用索引条件下推是，EXPLAIN语句输出结果中Extra列内容显示为Using index condition。

    案例

    EXPLAIN SELECT * FROM student
# 第一个条件过滤后还剩50条
WHERE age = 22 
# 假如过滤第二个条件则剩2条，则回表次数会比第一个条件过滤少，这种情况就会索引条件下推到name LIKE '%AaAAcL%'(索引会失效但没有完全失效)
AND name LIKE '%AaAAcL%';
AND classid = 974

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

  • 相关阅读:
    Linux和Windows之间文件自动同步
    css---定位
    C3P0和Druid数据库连接池的使用
    java_equals的使用
    springboot流浪狗领养管理系统毕业设计源码260839
    v-if条件判断及v-show
    通过FIR滤波器的输出，出现信号的延迟，校正信号的延迟量
    电池故障估计：Realistic fault detection of li-ion battery via dynamical deep learning
    Oracle（1）：Oracle简介
    【算法刷题】1 Python基础篇
  • 原文地址：https://blog.csdn.net/pmc0_0/article/details/126337038