索引使用的情况分析
数据准备
创建表student_info、course
CREATE TABLE `student_info` (
`id` int NOT NULL AUTO_INCREMENT,
`student_id` int NOT NULL,
`name` varchar(20) DEFAULT NULL,
`course_id` int NOT NULL,
`class_id` int DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb3;
CREATE TABLE `course` (
`id` int NOT NULL AUTO_INCREMENT,
`course_id` int NOT NULL,
`course_name` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb3;
创建生成随机字符串函数
-- 函数返回随机字符串
DELIMITER //
CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8mb4
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 ;
首先要确保相信函数的变量log_bin_trust_function_creators为1
SELECT @@log_bin_trust_function_creators variable;
SET GLOBAL log_bin_trust_function_creators = 1;
rand_num函数
-- rand_num
DELIMITER //
CREATE FUNCTION `rand_num`(from_num INT ,to_num INT) RETURNS int
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_course`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO course (course_id, course_name ) VALUES (rand_num(10000,10100),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
创建插入学生信息表存储过程
-- 创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE `insert_stu`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student_info (course_id, class_id ,student_id ,name ) VALUES (rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
执行插入存储过程
CALL insert_course(100);
CALL insert_stu(1000000);
1 字段数值具有唯一性限制
索引本身可以起到约束的作用，比如唯一索引、主键索引都可以起到唯一性约束的，因此如果某个字段是唯一性的，可以通过创建唯一或者主键索引来通过索引快速确定该条记录。
阿里规定具有业务上具有唯一性的字段，必须设置唯一性索引，哪怕是组合字段
唯一性索引对于insert的性能影响可以忽略不计
2 频繁作为where的查询条件
某个字段如果在select语句的where条件中频繁被用到，那么就需要给这个字段添加索引了，尤其在数据量大的情况下，添加普通索引能够大幅提升数据查询的效率。
如对student_id不添加索引和添加索引进行查询：
-- 1559ms
SELECT * FROM student_info WHERE student_id = 196206;
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);
-- 1225ms
SELECT * FROM student_info WHERE student_id = 196206;
使用的云服务器效果可能收到网络的影响
3 经常Group By和Order By的字段
当需要使用Group By对数据进行分组查询，或者使用Order By对数据进行排序的时候，可以对分组、排序的字段添加索引，这是因为索引本身就是让数据按照某种顺序进行存储和检索的，如果待排序的列有多个，那么就可在多个列上建立联合索引。
比如，按照sid进行分组查询：
SELECT student_id, count(*) AS num
FROM student_info
GROUP BY student_id
LIMIT 100;
-- 不对sid设置索引：2671ms
-- 设置索引：36ms
orderby就不演示了，下面呢看一种两个并存的情况：
SELECT student_id, count(*) AS num
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;
Order BY的字段不依赖于Group的字段，因为使用sql_mode包含only_full_group_by所以会报错，因此需要修改sqlmode:
SELECT @@sql_mode;
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
此时添加两个普通索引的话，使用EXPLAIN查看执行计划：
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);
CREATE INDEX idx_cre_time ON student_info(create_time DESC);
-- 2595ms
EXPLAIN SELECT student_id, count(*) AS num
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;
key:idx_sid
Extra:Using temporary; Using filesort
可以看到只使用了idx_sid，而没有使用idx_cre_time索引
此时删除普通索引，并创建一个联合索引：
CREATE INDEX idx_sid_cre_time ON student_info(student_id, create_time DESC);
-- 267ms
EXPLAIN SELECT student_id, count(*) AS num
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;
possible_key: idx_sid,idx_sid_cre_time, key: idx_sid_cre_time
Extra: Using index; Using temporary; Using filesort
注意这时候并没有删除两个普通索引，所以possible_key中还是出现了idx_sid，但最后还是使用了联合索引
如果创建一个这样的联合索引：
DROP INDEX idx_sid_cre_time ON student_info;
CREATE INDEX idx_cre_time_sid ON student_info(create_time DESC, student_id);
-- sql执行时间：2658ms
EXPLAIN SELECT student_id, count(*) AS num
FROM student_info
GROUP BY student_id
ORDER BY create_time DESC
LIMIT 100;
possible_key: idx_sid,idx_cre_time_sid, key: idx_sid
Extra: Using temporary; Using filesort
由于联合索引的最左前缀原则，idx_cre_time_sid在创建B+树的时候会先按照create_time的值降序排列再按照sid排序，因此查询的时候只有create_time起作用，而根据sql的执行顺序，首先执行Group By再执行Order By，因此会使用索引sid的索引idx_sid
4 update、delete的where条件列
和select的where条件同理。
5 distinct字段需要创建索引
当需要对某个字段去重的时候，对字段添加索引能够提升查询的效率，这是由于索引本身是对列进行排序存储和检索的，因此去重不需要再进行排序，效率较高。
SELECT DISTINCT(student_id)
FROM student_info
-- 使用索引：474ms
-- 不使用：1367ms
6 多表join连接操作时，创建索引的注意事项
多表连接的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常大，严重影响效率
应对where条件的字段创建索引
应对连接条件ON的字段创建索引，并且，两个字段的类型必须是相同的（否则会使用隐式转换函数导致索引失效）
7 使用列的类型小的创建索引
这里的类型小，指的是该类型表示的数据范围的大小（如TINYINT < MEDIUMINT < INT < BIGINT），这是因为：
数据类型越小，在查询时比较越快
数据类型越小，意味着数据页能够存放越多的记录，那么磁盘IO的次数也就会越少，也就意味着可以把更多的数据页缓存在内存，从而加快读写效率
这一点对于主键更加使用，因为主键对应的索引不仅用于聚簇索引，非聚簇索引的节点的也需要存储一份记录的主键，使用类型小的主键同样能够提高效率
8 使用字符串前缀创建索引
如果一个字符型的字段很长，那么存储就需要占用很大的空间，也就意味着创建索引的时候：
索引在做字符串比较的时候更加费时
在索引中占用的空间变大，数据页存放记录减少，IO次数增加
这时候就可以通过截取字符串一部分内容创建索引，这个就叫做前缀索引，这样虽然不能够准确定位到记录的位置，但是可以根据前缀相同的记录主键进行回表操作，来查询完整的字符串。既节约了空间，又减少了字符串的比较时间，还大体能够解决排序的问题。
举个栗子，如创建一张商户表，商户地址较长，就可以进行添加前缀索引：
CREATE TABLE shop(
address VARCHAR(120) NOT NULL
);
CREATE INDEX idx_addr ON shop(address(12));
问题来了：该截取多少呢？截取多了，导致空间占用并且比较时间长；少了则重复性太高，字段的散列(选择度)降低。这时候就可以通过选择度计算来判断截取的长度：
SELECT COUNT(DISTINCT address(10)) / COUNT(*) FROM SHOP;
SELECT COUNT(DISTINCT address(20)) / COUNT(*) FROM SHOP;
SELECT COUNT(DISTINCT address(30)) / COUNT(*) FROM SHOP;
SELECT COUNT(DISTINCT address(40)) / COUNT(*) FROM SHOP;
值越接近1，意味着选择度越高
索引前缀对排序的影响
如果对字段address添加了长度为12的前缀索引，那么下面的排序将不会使用索引而是使用文件排序
SELECT * FROM shop
ORDER BY address
LIMIT 12;
阿里开发手册索引前缀规定
规定：在varchar类型字段上建立索引，必须指定索引长度，并根据文本的实际区分度来决定索引长度
9 区分度高的列适合作为索引
列的基数指的是一个列中不重复数据的个数，也就是说，在行数一定的情况下，列的基数越大，列的值越分散，越小则越集中，因此应该选择基数大的列建立索引。可以使用下面的区分度计算来判断基数的大小：
SELECT COUNT(DISTINCT a) / COUNT(*) FROM tableName;
扩展：
联合索引应把区分度大的列放到前面
区分度小的列不适合索引，是因为容易通过创建的索引检索到集中的相同列，还需要回表操作找到具体记录
10 使用最频繁的列放到联合索引的左侧
这样可以少建立一些索引，由于最左前缀原则，可以增加联合索引的使用率
11 在多个字段都需要创建索引的情况下，联合索引要优于单列索引
比如创建的一个联合索引：
ALTER TABLE student_info
ADD INDEX idx_sid_sname_cid(student_id, ...)
则这个索引可以用于sid的查询、sid和sname的查询以及三者的查询，使用频率要高于三个单列索引
限制索引的数目
在实际工作过程中，也要注意平衡，索引的数目不是越多越好。一般情况下，建议单表的索引不超过6个，这是因为：
每个索引都需要占据磁盘空间，索引越多，占用的磁盘空间越大
索引会影响增删改的性能，因为表中数据更改的同时，还需要对索引进行调整和更新，造成负担
优化器在选择如何优化查询的时候，会根据统一的信息，对每一个可以使用的索引进行评估，来生成一个最好的执行计划，如果索引过多则会增加优化器的负担，降低查询的性能
不适合创建索引的情况
在where条件下用不到的字段不要使用索引
数据量较小的表不要使用索引，一般数据低于1000行则不需要
大量重复数据的列不要创建索引，否则会严重降低更新速度，一般重复读高于10%则认为重复读高
频繁更新的字段不要创建索引
不建议使用无序的值作为索引，比如身份证和UUID，在索引比较的时候转换成ASCII，并且插入的时候会造成页的分裂
删除不再使用或者很少使用的索引
不要定义冗余或重复的索引，比如联合索引和单列索引造成冗余、主键索引和唯一性索引造成重复
