开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第9天，点击查看活动详情

前言

上篇我们学习了MySQL中的数据库优化之分析查询语句实战演示。有兴趣的小伙伴可以阅读(MySQL优化之分析查询语句实战演示（三）)。
下面我们继续学习MySQL中的数据库优化之索引优化。

索引优化

首先创建2张用来测试的表，分别是class和student表。

创建表

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)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建函数

为了保证每条数据都不同，创建一个产生随机字符串的函数。

#随机产生字符串
DELIMITER //
CREATE FUNCTION rang_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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 rang_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 ;

创建存储过程

创建往student表插入数据的存储过程

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(strudo, name, age, classId)
    VALUES((START + i), rang_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(rang_string(8), rand_string(10), rand_num(1, 100000));
    UNTIL i = max_num
    END REPEAT;
    COMMIT;
END //
DELIMITER ;

调用存储函数插入数据

CALL insert_class(10000);
CALL insert_stu(100000, 500000);

索引失效的情况

全值匹配

全值的等值匹配时，索引会失效。

最佳左前缀法则

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

主键插入顺序

如果有一个数组：[1],[2],[6],[10],...[100]。如果此时再插入一条主键值为9的记录，那它插入的位置是在6和10之间。可是这个数据页已经满了，再插进来怎么办？我们需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移动意味着性能损耗，所以如果我们想尽量避免创建的这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生性能损耗了。

比如让主键值具有AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入。比如上面两个表的id列，在插入记录时存储引擎会自动为我们填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

今天先学习到这里，明天继续。

MySQL优化之索引优化（一）

前言