MySQL 索引与优化
原创
作者：邵建永
索引基础
索引用途
索引有很多用途，并不仅仅是优化查询性能，这些用途包括：
- 保持数据完整性（主键和唯一索引）
- 优化数据检索性能（使用索引进行条件匹配和模式匹配）
- 改进表的连接操作（使用索引连接表）
- 优化结果排序操作（ORDER BY）
- 优化聚合数据操作（GROUP BY）
创建索引
创建表时指定索引
主键索引：
PRIMARY KEY index-name
非主键索引：
UNIQUE KEY|INDEX index-name
下面的示例创建了一个主键索引和两个非主键索引，创建非主键索引时KEY和INDEX可以互换。
CREATE TABLE db_school.t_student (
field_id varchar(10) NOT NULL,
field_name varchar(80) NOT NULL,
field_gender enum('M','F') DEFAULT NULL,
field_dob datetime NOT NULL,
field_grade int DEFAULT 0,
field_class int DEFAULT 0,
PRIMARY KEY (field_id),
INDEX idx_name (field_name),
INDEX idx_gc(field_grade,field_class)
) ENGINE=InnoDB;在已有表上添加索引
主键索引：
ALTER TABLE table ADD PRIMARY KEY index-name
非主键索引：
ALTER TABLE table ADD UNIQUE KEY|INDEX index-name
查看索引
可以用SHOW INDEXES命令查看索引的信息，该命令输出包括索引的类型和当前报告的MySQL索引基数等信息。
mysql> SHOW INDEXES FROM t_student;
--+----------+--------------+-------------+-------------+------+------------+--
| Key_name | Seq_in_index | Column_name | Cardinality | Null | Index_type |
--+----------+--------------+-------------+-------------+------+------------+--
| PRIMARY | 1 | field_id | 2689 | | BTREE |
| idx_name | 1 | field_name | 2689 | | BTREE |
| idx_gc | 1 | field_grade | 224 | YES | BTREE |
| idx_gc | 2 | field_class | 224 | YES | BTREE |
--+----------+--------------+-------------+-------------+------+------------+--选定索引
索引有一些时间和空间上的缺点。首先，索引加快了检索速度，但却降低了许多写入操作的速度，包括插入、删除，以及包含索引列的更新。其次，索引要占用磁盘空间，索引越多，它所占用的磁盘空间也就越大。因此，索引并不是越多越好，我们应该有选择地使用索引。
筛选索引
通过阅读上节中的索引用途，我们不难得出，适合作为索引的是以下这些数据列：
- WHERE子句中的列
- ORDER BY子句中的列
- GROUP BY子句中的列
- 用于表连接的列
上面只是数据列作为表索引的入选条件，满足这些条件的列可能有很多，我们还需要结合考虑以下因素进一步筛选。
- 考虑列在查询中覆盖的广度。
- 考虑数据列的区分度（维度），选择区分度高的列作为索引。
- 优先考虑短小的列作为索引。越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快，整型有余字符串。
- 考虑为字符串前缀编制索引。
- 考虑为关联度高的字段编制复合索引，并将它们按区分度的高低从左到右排序。
单列索引vs.复合索引
多个单列索引和一个包含这些列的符合索引，在时间和空间上孰优孰劣？下面是两者对比的一些结论，仅供参考。
- 复合索引中最左边的列可以当作单列索引高效地使用（单列索引对它的优势并不明显）。
- 复合索引中最左边之外的列单独作为索引时，相比单列索引有明显的劣势。
- 作为两个用OR连接的条件，单列索引有一定优势，因为复合索引将导致全表扫描。
- 作为两个用AND连接的条件：
- 如果两个列之间的关联度较低，复合索引有一定的优势。
- 如果两个列之间的关联度较高，复合索引有明显的优势。
- 单列索引占用的空间更多，对写入操作的性能影响更大。
两者的对比数据可以参考这个的文档：Multi Column indexes vs Index Merge。
避免冗余索引
MySQL没有限制索引的数量，用户甚至可以在一个表上创建完全相同的多个索引。如上所述，添加索引会影响写操作的性能，我们应该尽量控制索引的数量，避免创建重复的索引。
本文第一节，我们为t_student创建了一个名字为idx_gc的索引：
INDEX idx_gc(field_grade,field_class)
如果我们再为t_student增加一个名字为inx_grade的索引：
INDEX idx_grade(field_grade)
新增的idx_grade实际上是一个重复的索引，因为idx_gc已经为field_grade创建了索引。
正确使用索引
定制了正确的索引还不够，我们还要正确使用它。
规则1：不能将索引放在表达式中，必须是独立的列，否则无法启用索引带来的高效。
例如，下面这个查询无法使用field_id列的索引。
SELECT field_name FROM t_student WHERE field_grade+1=3;凭肉眼很容易看出WHERE中的表达式其实等价于 field_grade=2，但是MySQL无法自动解析这个方程式。我们应该养成习惯，将索引列单独放在比较操作符的一侧。
同样，也不能将索引放在函数的参数中，本文不再赘述。
规则2：避免隐式类型转换。
这个规则不仅适用于索引字段，也适用于其它所有字段。有些隐式类型转换会导致索引失效，例如：
SELECT field_name FROM t_student WHERE field_id=123456;注意，field_id是varchar类型的，而上述语句中的查询条件是 field_id=123456。MySQL将数值类型隐式转换成字符串类型来匹配表。
我们来看一下这条SQL语句的执行计划（QEP）：
mysql> EXPLAIN SELECT * FROM t_student WHERE field_id=123456\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_student
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 3 warnings (0.00 sec)看以看到，查询并没有使用索引，我们再看一下告警信息：
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1739
Message: Cannot use ref access on index 'PRIMARY' due to type or collation conversion on field 'field_id'
*************************** 2. row ***************************
Level: Warning
Code: 1739
Message: Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'field_id'
*************************** 3. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `db_school`.`t_student`.`field_id` AS `field_id`,`db_school`.`t_student`.`field_name` AS `field_name`,`db_school`.`t_student`.`field_gender` AS `field_gender`,`db_school`.`t_student`.`field_dob` AS `field_dob`,`db_school`.`t_student`.`field_grade` AS `field_grade`,`db_school`.`t_student`.`field_class` AS `field_class` from `db_school`.`t_student` where (`db_school`.`t_student`.`field_id` = 123456)
3 rows in set (0.00 sec)隐式类型转换使field_name上的索引失效，这将导致全表扫描。我们应该养成习惯，让索引的类型与你打算进行比较操作的（值）类型保持匹配。
规则3：验证索引的有效性。
要确定一条SQL语句能否按照我们的预期使用特定索引高效地执行，EXPLAIN命令是必不可少的工具。
EXPLAIN命令用于查看SQL语句的执行计划（QEP）。我们可以借助这条命令深入了解MySQL基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节（possible_keys，被评估的索引），以及当运行SQL语句时哪种策略会被优化器采用。
理想情况下，我们应该对系统中的每条SQL语句都执行EXPLAIN命令。所有SELECT语句前都可以直接加上EXPLAIN关键字。而对于UPDATE和DELETE语句，需要把查询改写成SELECT语句，以确保有效地使用索引。
总结
索引是对查询性能优化最有效的手段之一。正确地使用索引能够轻易地将查询性能提高几个数量级。编写查询语句时，应尽可能地选择合适的索引，以避免全表遍历。如果一个查询无法从现有的索引中获益，则应看看是否可以创建一个更合适的索引来提升性能。如果不行，也可以看看是否可以重写查询语句，将其转化成一个能够利用现有索引或者新创建索引的查询。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。