一、数据准备

• 创建数据库（InnoDB存储引擎）

CREATE TABLE person_info
(
    id           INT          NOT NULL AUTO_INCREMENT,
    name         VARCHAR(100) NOT NULL,
    birthday     DATE         NOT NULL,
    phone_number CHAR(11)     NOT NULL,
    country      VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

• 数据库索引

SHOW INDEX FROM person_info;

image.png

1.1、使用存储过程写入数据

• 批量生成数据

DELIMITER $
CREATE PROCEDURE insert_persion(start INT, max_num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0; #  设置手动提交事务
    REPEAT
        #循环
        SET i = i + 1; #  赋值
        INSERT INTO person_info (name, birthday, phone_number, country)
        VALUES (rand_string(6), DATE_ADD('2021-01-01 00:00:00', INTERVAL FLOOR(1 + (RAND() * 10800)) DAY), rand(10) * 100000000000, rand_num(1, 1000));
    UNTIL i = max_num
        END REPEAT;
    COMMIT; #  提交事务
END $
DELIMITER ;

• 调用存储过程，创建100W数据

CALL insert_persion(1, 1000000);

二、 用于排序

2.1、使用索引字段排序

SELECT *
FROM person_info
ORDER BY name, birthday, phone_number;

全表扫描.png

2.2、使用索引字段排序，并且限制 LIMIT

SELECT *
FROM person_info
ORDER BY name, birthday, phone_number
LIMIT 10;

使用了索引.png

2.3、使用索引字段排序，不需要回表，直接使用覆盖索引

SELECT id, name, birthday, phone_number
FROM person_info
ORDER BY name, birthday, phone_number;

使用了索引.png

小结

1、例子(2.1)和例子(2.2)对比

• 这两个例子都使用idx_name_birthday_phone_number字段来排序
• 这两个例子的不同点在于，2.2例子使用了LIMIT
• 最终为什么有的使用了索引(2.2)而有点没有使用索引(2.1)

这个两个SQL都是需要查询表的所有信息，而排序使用的是二级索引idx_name_birthday_phone_number。所以需要回表，查询优化器会自动判断这个回表成本，如果判断成本大(2.1)，就会直接选择全表扫描。由于（2.2）中使用了LIMIT限制了查询数据量，所以查询优化器判断回表成本较小，所以选择使用二级索引idx_name_birthday_phone_number

2、例子(2.1)和例子(2.3)对比

• 这两个例子都使用idx_name_birthday_phone_number字段来排序
• 这两个例子的不同点在于，2.3例子中只查询idx_name_birthday_phone_number索引中数据，不需要回表

3、书写SQL需要注意

• 3.1、禁止使用*全量返回，防止回表

• 3.2、查询尽量按需返回使用覆盖索引

• 3.3、查询时尽量使用LIMIT条件

三、用于分组

四、如何挑选索引(适合做索引的列)