美文网首页MySQL
55-MySQL索引优化与查询优化

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建立联合索引时会

相关文章

网友评论

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

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

    延伸阅读

    深度阅读

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

    栏目导航

    热点阅读

    MySQL

    关于我们|服务条款|联系我们|55-MySQL索引优化与查询优化|投稿指南|网站地图|RSS订阅|排版工具|手机版

    提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

    Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

    备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

    本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

    所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！