创建索引
1、单列索引
单列索引是最基础的索引，是一种建立在表中特定列上的索引。
1.1语法规则
ALTER TABLE <table>
ADD PRIMARY KEY [index-name] (<column>);
ALTER TABLE <table>
ADD [UNIQUE] KEY | INDEX [index-name]
(<column>); 注意：创建非主键索引时，KEY和INDEX关键字可以互换，但创建主键索引时只能使用KEY关键字。
1.2利用索引限制查询读取的行数
如果一个表没有索引，则当进行查询时，会扫描整张表，为了提高查询速度，我们可以通过创建索引来避免每次都扫描整张表。观察表的QEP，如果type属性为NULL,或key=NULL,则可以判断这条查询扫描了整张表。
eg：表结构
SHOW CREATE TABLE user100;
CREATE TABLE `user100` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(10) NOT NULL,
`last_name` varchar(10) NOT NULL,
`sex` varchar(5) NOT NULL,
`score` int(11) NOT NULL,
`copy_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci EXPLAIN，观察是否使用索引，type属性或key属性
EXPLAIN SELECT id,first_name,last_name
FROM user100
WHERE score > 95;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | user100 | NULL | ALL | NULL | NULL | NULL | NULL | 942200 | 33.33 | Using where 直接进行查询，所需要的时间：
SELECT id,first_name,last_name
FROM user100
WHERE score > 95
> OK
> Time: 0.628s 添加索引，并查看QEP
ALTER TABLE user100 ADD INDEX(score);
EXPLAIN SELECT id,first_name,last_name FROM user100 WHERE score > 95;
1 SIMPLE user100 range score score 4 123056 100.00 Using index condition 注意：MySQL不会限制一个表上的索引的数目，甚至可以创建重复的索引。用户不需要指定索引的名称，MySQL会根据索引所在的首列的名称自动为索引命名，并添加可选的附加信息。
再次进行查询：（每次结果不确定）
SELECT id,first_name,last_name FROM user100 WHERE score > 95
> OK
> Time: 0.199s
1.3使用索引连接表
提高关系表连接操作的性能，通过将连接属性设置为索引，这样表就不会执行全表查询。
1.4索引的基数
当一个表中的索引不止有一条时，MySQL必须在possible_keys列出的索引中选择，优化器会根据工作量最少的估算来选择索引，索引基数就是用来判断如何选择的。
mysql> SHOW INDEX FROM user100;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| user100 | 0 | PRIMARY | 1 | id | A | 942200 | NULL | NULL | | BTREE | | | YES |
| user100 | 1 | score | 1 | score | A | 61 | NULL | NULL | | BTREE | | | YES |
| user100 | 1 | copy_id | 1 | copy_id | A | 942200 | NULL | NULL | | BTREE | | | YES 上表中，可以通过Cardinality属性来看出，索引为PRIMARY ,SCORE,COPY_ID的索引基数分别为922200，61，942200.
索引基数越高，证明该列的唯一值的数量越多，这样可以使用更少的读操作来找到需要的记录，反之，越少，意味着唯一值少，重复的多，则在进行查询时，需要更进一步的属性来进行筛选。
注意，索引基数只是一个参考，真正的抉择在于索引基数值和索引中的总行数的比较，最好的关系是1：1，即每一个值都是一个非空唯一值。
1.5使用索引进行模式匹配
可以通过索引来做模式匹配的工作，注意如果查找的词的开头以通配符开头，则MySQL是不会使用索引。MySQL也不支持基于索引的函数。
1.6唯一索引
唯一索引即列中的值都只出现一次，其次，告诉优化器对给定的记录最多只能有一行结果返回。当使用唯一索引的时候，最多只能返回一行数据，但找到一个匹配结果之后就不需要继续扫描了。
1.7结果排序
索引可以对查询结果进行排序，如果没有使用索引，则MySQL会使用内部文件排序算法对返回的行按照指定顺序进行排序。
2.多列索引
2.1语法规则
ALTER TABLE <table>
ADD PRIMARY KEY [index-name]
(<column>,<column2>...);
ALTER TABLE <table>
ADD [UNIQUE] KEY | INDEX [index-name]
(<column>,<column2>...); 注意：创建多列索引的时候，尽可能合并索引，这样会获得更高的效率。创建索引是一个非常耗时的工作，并且会阻塞其他操作。
2.2创建索引
ALTER TABLE user100 ADD INDEX U12 (score,first_name);
ALTER TABLE user100 ADD INDEX U21 (first_name，score); 注意，顺序不同的索引其查询时不一样的，交换顺序有可能会创建出更好的索引。
2.3合并WHERE 和 ORDER BY 语句
我们可以创建一个同时满足WHERE 语句和 ORDER BY语句的索引，优化器也可能为WHERE 条件和ORDER BY语句使用一个索引，
注：MySQL可以在WHERE,ORDER BY,GROUP BY列中使用索引，但是，一般来说，MySQL在一个表上只选择一个索引，从MySQL5.0开始，在个别情况下，MySQL使用一个以上的索引，最常见的索引合并就是操作时两个索引取并集，也可以取交集。
3.创建更好的索引
3.1覆盖索引
覆盖索引满足查询中给定表用到的所有的列，创建一个覆盖索引，这个索引必须包含指定列表上包括的WHERE语句，ORDER BY 语句，GROUP BY语句以及SELECT 语句中的所有列。在InnoDB中，主键的值会被附加到非主键索引的每个对应记录后面，因此没有必要在非主键索引中指定主键，即意味着，InnoDB引擎中所有非主键索引都隐含主键列了。
在生茶环境中，覆盖索引有可能不是理想的索引。由于数据集大小有限，随着数据列长度的增加，为一个大型列创建索引可能会对系统整体性能有影响，覆盖索引适合那些使用了很多较小长度的主键和外键约束的表。
3.2局部索引
即创建更紧凑的索引,可以减少索引占用的空间，降低更少的磁盘I/O开销，意味着更快的访问到需要访问的行。
参考《Effective MySQL之SQL语句最优化》 建议亲自阅读本书，加深理解。
京公网安备 11010502036488号