3.2.1字段具有唯一性限制
适合创建唯一性索引，适合创建唯一性索引，当然，如果该字段被Unique修饰，具有唯一性约束，会自动创建一个唯一性索引(如果给字段添加了唯一性索引，同样也会自动添加唯一性约束)。这是因为唯一性的字段没有重复值，很适合作为查询条件(可以结合B+树来理解，在叶子节点查找到唯一数据后，无须再进行遍历了)，给他们加索引可以在使用其作为查询条件时提升效率。
🙋♀️ 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。
3.2.2频繁作为 WHERE 查询条件的字段
某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。
尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。
查看student_info表中的索引
mysql> SHOW INDEX FROM student_info; +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | student_info | 0 | PRIMARY | 1 | id | A | 960509 | NULL | NULL | | BTREE | | | YES | NULL | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.06 sec)
可以看出student_id没有建立索引。用它作为查询条件查询下。耗时1.07s
mysql> SELECT course_id,class_id,NAME,create_time,student_id -> FROM -> student_info -> WHERE student_id = 123110; +-----------+----------+--------+---------------------+------------+ | course_id | class_id | NAME | create_time | student_id | +-----------+----------+--------+---------------------+------------+ | 10058 | 10014 | SyNuJn | 2022-05-25 09:30:46 | 123110 | | 10053 | 10007 | YYVLTl | 2022-05-25 09:31:15 | 123110 | | 10053 | 10008 | XVIHkg | 2022-05-25 09:32:22 | 123110 | +-----------+----------+--------+---------------------+------------+ 3 rows in set (1.07 sec)
添加索引。耗时5.39s
mysql> AlTER TABLE student_info -> ADD INDEX idx_sid(student_id); Query OK, 0 rows affected (5.39 sec) Records: 0 Duplicates: 0 Warnings: 0
再查询。耗时0.00s。性能提升杠杠的
mysql> SELECT course_id,class_id,NAME,create_time,student_id -> FROM -> student_info -> WHERE student_id = 123110; +-----------+----------+--------+---------------------+------------+ | course_id | class_id | NAME | create_time | student_id | +-----------+----------+--------+---------------------+------------+ | 10058 | 10014 | SyNuJn | 2022-05-25 09:30:46 | 123110 | | 10053 | 10007 | YYVLTl | 2022-05-25 09:31:15 | 123110 | | 10053 | 10008 | XVIHkg | 2022-05-25 09:32:22 | 123110 | +-----------+----------+--------+---------------------+------------+ 3 rows in set (0.00 sec)
3.2.3 经常 GROUP BY 和 ORDER BY 的列
索引其实就是让数据按照某种顺序进行存储或者检索，而GROUP BY分组查询或者ORDER BY进行排序，如果添加了索引，本身索引的数据就已经排好序了，进行分组查询和排序操作性能不是很nice吗？另外，如果待排序的列有多个，可以在这些列上建立联合索引。
⚽下面在有student_id索引的情况下，查询.
```mysql> SELECT student_id,COUNT(*) AS num -> FROM student_info -> GROUP BY student_id LIMIT 100; +------------+-----+ | student_id | num | +------------+-----+ | 1 | 9 | //笔者省略了...... | 100 | 4 | +------------+-----+ 100 rows in set (0.00 sec)
删除索引，再来。慢的像蜗牛
mysql> SELECT student_id,COUNT(*) AS num -> FROM student_info -> GROUP BY student_id LIMIT 100; +------------+-----+ | student_id | num | +------------+-----+ | 1 | 9 | // ... | 100 | 4 | +------------+-----+ 100 rows in set (10.31 sec)
🏀 如果同时使用GROUP BY和ORDER BY，先看看不加索引的情况
mysql> SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100; ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'atguigudb1.student_info.create_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
出现了一个异常信息，这是因为我们使用的sql_mode是only_full_group_by.修改下再来，时间代价是6.61秒
mysql> SELECT @@sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SET @@sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@sql_mode; +----------------------------------------------------------------------------------------------------+ | @@sql_mode | +----------------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100; +------------+-----+ | student_id | num | +------------+-----+ | 90433 | 1 | ... | 144379 | 1 | +------------+-----+ 100 rows in set (6.61 sec)
再看看两个字段分别建立单列索引的情况,5.26s，快了一点点
mysql> ALTER TABLE student_info ADD INDEX idx_sid(student_id); Query OK, 0 rows affected (3.61 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE student_info ADD INDEX idx_cre_time(create_time); Query OK, 0 rows affected (3.52 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100; +------------+-----+ | student_id | num | +------------+-----+ | 90433 | 1 | | 88221 | 1 | //...... | 144379 | 1 | +------------+-----+ 100 rows in set (5.26 sec)
分析下它的查询过程,原来我们只用了一个索引，由于我们是先GROUP BY student_id,后ORDER BY create_time，我们实际上只使用了索引idx_sid
mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100; +----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+---------------------------------+ | 1 | SIMPLE | student_info | NULL | index | idx_sid | idx_sid | 4 | NULL | 997449 | 100.00 | Using temporary; Using filesort | +----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+---------------------------------+ 1 row in set, 1 warning (0.01 sec)
建立联合索引的情况,芜湖起飞
mysql> ALTER TABLE student_info ADD INDEX idx_sid_cre_time(student_id,create_time DESC); Query OK, 0 rows affected (4.71 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100; +----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------+--------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------+--------+----------+----------------------------------------------+ | 1 | SIMPLE | student_info | NULL | index | idx_sid,idx_sid_cre_time | idx_sid_cre_time | 10 | NULL | 997449 | 100.00 | Using index; Using temporary; Using filesort | +----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------+--------+----------+----------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
再来,交换字段顺序建立联合索引idx_cre_time_sid，下面查询真正使用的索引key是idx_sid ，当然，由于这里存在缓存，所以查询速度很快，实际上它应该比使用idx_sid_cre_time慢。读者自己测试可以关闭缓存，作者这里偷个懒
mysql> ALTER TABLE student_info ADD INDEX idx_cre_time_sid(create_time DESC,student_id); Query OK, 0 rows affected (4.50 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DROP INDEX idx_sid_cre_time ON student_info; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT student_id,COUNT(*) AS num FROM student_info -> GROUP BY student_id -> ORDER BY create_time DESC -> LIMIT 100; +----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+ | 1 | SIMPLE | student_info | NULL | index | idx_sid,idx_cre_time_sid | idx_sid | 4 | NULL | 997449 | 100.00 | Using temporary; Using filesort | +----+-------------+--------------+------------+-------+--------------------------+---------+---------+------+--------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)
