MySQL

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

2022-10-28  本文已影响0人  紫荆秋雪_文

一、数据库调优

1.1、调优维度

1.2、物理查询优化

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

1.3、逻辑查询优化

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

二、数据准备

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

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 ;

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 ;

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

三、索引失效

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

3.1.1、查看student表索引情况

SHOW INDEX FROM student;
student表索引.png

3.1.2、通过 age 来查询

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;

3.1.2、通过 age 和 classId来查询

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

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

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

3.1.3、小结

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

3.1.4、给age添加索引

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

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;

3.1.5.2 通过 age 和classid 查询

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

3.1.5.3 通过 age 和classid和name 查询

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

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;

3.1.6.2 通过 age 和classid 查询

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

3.1.6.3 通过 age 和classid和name 查询

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

3.1.7、查看索引

SHOW INDEX FROM student;

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、再次查看上述

3.1.12、小结

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

在MySQL建立联合索引时会

上一篇 下一篇

猜你喜欢

热点阅读