55-MySQL索引优化与查询优化

作者: 紫荆秋雪_文 | 来源:发表于2022-10-28 18:27 被阅读0次

一、数据库调优

1.1、调优维度

1、索引失效、没有充分利用到索引——索引建立
2、关联查询太多JOIN（设计缺陷或不得已的需求）——SQL优化
3、服务器调优及各个参数设置（缓存、线程数等）——调整 my.cnf
4、数据过多——分库分表

1.2、物理查询优化

物理查询优化是通过索引和表连接方式等技术来进行优化

1.3、逻辑查询优化

逻辑查询优化是通过 SQL 等价变换 提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高

二、数据准备

2.1、建表

class 表

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

student 表

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

2.2、设置参数

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

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

2.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 ;

用于随机产生多少到多少的编号

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 ;

创建往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 ;

调用存储过程

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

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

销毁索引

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

三、索引失效

MySQL中 提高性能 的一个最有效的方式是对数据表 设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。
- 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能
- 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度回很慢
大多数情况下采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引
其实，用不用所以，最终都是优化器说了算。优化器是基于什么的优化器？基因cost开销（CostBaseOptimizer），它不是基于规则（Rule-BasedOptimizer），也不是基于语义，怎么样开销小就怎么样来。
SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系

3.1、案例1——全值匹配我最爱

3.1.1、查看`student表`索引情况

查看命令

SHOW INDEX FROM student;

student表索引.png

3.1.2、通过 `age` 来查询

SQL

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;

EXPLAIN结果 EXPLAIN结果.png

3.1.2、通过 `age` 和 `classId`来查询

SQL

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

EXPLAIN结果 EXPLAIN结果.png

3.1.3、通过 `age` 和 `classId`和`NAME`来查询

SQL

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

EXPLAIN结果 EXPLAIN结果.png

3.1.3、小结

由于student表没有索引，所以上述查询都是全表扫描

3.1.4、给`age`添加索引

SQL

CREATE INDEX idx_age ON student (age);

查询

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

EXPLAIN结果 EXPLAIN结果.png
查询使用了索引idx_age

3.1.5、使用`age`和`classid`创建联合索引

创建联合索引

CREATE INDEX idx_age_classid ON student(age,classId);

3.1.5.1 通过 `age` 查询

EXPLAIN
SELECT SQL_NO_CACHE *
FROM student
WHERE age = 30;

EXPLAIN结果 EXPLAIN结果.png
小结
- 可选 idx_age,idx_age_classid 两个索引
- 最终选用了idx_age
- key_len=5：age为INT类型占用 4字节+可以为NULL

3.1.5.2 通过 `age` 和`classid` 查询

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

EXPLAIN分析 EXPLAIN分析.png
小结
- 可选 idx_age,idx_age_classid 两个索引
- 最终选用了idx_age_classid
- key_len=10：age为INT类型占用 4字节+可以为NULL；同理classid为INT类型占用 4字节+可以为NULL

3.1.5.3 通过 `age` 和`classid`和`name` 查询

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

EXPLAIN分析 EXPLAIN分析.png
小结：使用索引情况与上面一致

3.1.6、使用`age`和`classid`和`name`创建联合索引

创建联合索引

CREATE INDEX idx_age_classid_name ON student (age, classid, name);

3.1.6.1 通过 `age` 查询

EXPLAIN
SELECT SQL_NO_CACHE *
FROM student
WHERE age = 30;

EXPLAIN分析 EXPLAIN分析.png
小结
- 可选 idx_age,idx_age_classid,idx_age_classid_name 3个索引
- 最终选用了idx_age
- key_len=5：age为INT类型占用 4字节+可以为NULL

3.1.6.2 通过 `age` 和`classid` 查询

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

EXPLAIN分析 EXPLAIN分析.png
小结
- 可选 idx_age,idx_age_classid,idx_age_classid_name 3个索引
- 最终选用了idx_age_classid
- key_len=10：age为INT类型占用 4字节+可以为NULL；同理classid为INT类型占用 4字节+可以为NULL

3.1.6.3 通过 `age` 和`classid`和`name` 查询

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

EXPLAIN分析 EXPLAIN分析.png
小结
- 可选 idx_age,idx_age_classid,idx_age_classid_name 3个索引
- 最终选用了idx_age_classid_name
- key_len=93：age为INT类型占用 4字节+可以为NULL(1字节)；同理classid为INT类型占用 4字节+可以为NULL(1字节) + name为varchar(20)类型占用80字节+可以为NULL(1字节)+可变字符(2字节)

3.1.7、查看索引

SHOW INDEX FROM student;

student表索引 student表索引.png

3.1.8、查看冗余索引

SELECT *
FROM sys.schema_redundant_indexes;

冗余索引.png

解决冗余索引.png

3.1.9、删除冗余索引

ALTER TABLE `atguigudb2`.`student` DROP INDEX `idx_age`;

ALTER TABLE `atguigudb2`.`student` DROP INDEX `idx_age_classid`;

3.1.10、再次查看索引

SHOW INDEX FROM student;

image.png

3.1.11、再次查看上述

使用age来查询
使用`age`来查询.png
使用age和classid来查询
使用`age`和`classid`来查询.png
使用age和classid和name来查询使用`age`和`classid`和`name`来查询.png

3.1.12、小结

删除索引idx_age和idx_age_classid后，上述查询并不受影响
所以直接使用idx_age_classid_name就可以满足上述查询

3.2、案例2——最佳左前缀法则

在MySQL建立联合索引时会

网友评论

MySQL

本文标题：55-MySQL索引优化与查询优化

本文链接：https://www.haomeiwen.com/subject/ejfftdtx.html

延伸阅读

深度阅读

您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

55-MySQL索引优化与查询优化

一、数据库调优

1.1、调优维度

1.2、物理查询优化

1.3、逻辑查询优化

二、数据准备

2.1、建表

2.2、设置参数

2.3、创建函数

三、索引失效

3.1、案例1——全值匹配我最爱

3.1.1、查看`student表`索引情况

3.1.2、通过 `age` 来查询

3.1.2、通过 `age` 和 `classId`来查询

3.1.3、通过 `age` 和 `classId`和`NAME`来查询

3.1.3、小结

3.1.4、给`age`添加索引

3.1.5、使用`age`和`classid`创建联合索引

3.1.5.1 通过 `age` 查询

3.1.5.2 通过 `age` 和`classid` 查询

3.1.5.3 通过 `age` 和`classid`和`name` 查询

3.1.6、使用`age`和`classid`和`name`创建联合索引

3.1.6.1 通过 `age` 查询

3.1.6.2 通过 `age` 和`classid` 查询

3.1.6.3 通过 `age` 和`classid`和`name` 查询

3.1.7、查看索引

3.1.8、查看冗余索引

3.1.9、删除冗余索引

3.1.10、再次查看索引

3.1.11、再次查看上述

3.1.12、小结

3.2、案例2——最佳左前缀法则

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL

55-MySQL索引优化与查询优化

一、数据库调优

1.1、调优维度

1.2、物理查询优化

1.3、逻辑查询优化

二、数据准备

2.1、建表

2.2、设置参数

2.3、创建函数

三、索引失效

3.1、案例1——全值匹配我最爱

3.1.1、查看student表索引情况

3.1.2、通过 age 来查询

3.1.2、通过 age 和 classId来查询

3.1.3、通过 age 和 classId和NAME来查询

3.1.3、小结

3.1.4、给age添加索引

3.1.5、使用age和classid创建联合索引

3.1.5.1 通过 age 查询

3.1.5.2 通过 age 和classid 查询

3.1.5.3 通过 age 和classid和name 查询

3.1.6、使用age和classid和name创建联合索引

3.1.6.1 通过 age 查询

3.1.6.2 通过 age 和classid 查询

3.1.6.3 通过 age 和classid和name 查询

3.1.7、查看索引

3.1.8、查看冗余索引

3.1.9、删除冗余索引

3.1.10、再次查看索引

3.1.11、再次查看上述

3.1.12、小结

3.2、案例2——最佳左前缀法则

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL

3.1.1、查看`student表`索引情况

3.1.2、通过 `age` 来查询

3.1.2、通过 `age` 和 `classId`来查询

3.1.3、通过 `age` 和 `classId`和`NAME`来查询

3.1.4、给`age`添加索引

3.1.5、使用`age`和`classid`创建联合索引

3.1.5.1 通过 `age` 查询

3.1.5.2 通过 `age` 和`classid` 查询

3.1.5.3 通过 `age` 和`classid`和`name` 查询

3.1.6、使用`age`和`classid`和`name`创建联合索引

3.1.6.1 通过 `age` 查询

3.1.6.2 通过 `age` 和`classid` 查询

3.1.6.3 通过 `age` 和`classid`和`name` 查询