MySQL优化笔记之创建MySQL索引

创建索引

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语句最优化》     建议亲自阅读本书，加深理解。