MySQL索引(一)：正确、有效地使用索引

2017-08-04

索引

  1. 什么是索引？
  2. 索引分类
  3. 创建原则
  4. 使用注意
  5. 案例

什么是索引？

MySQL官方定义：“Index (索引)”是帮助MySQL高效获取数据的数据结构。“Index (索引)”本质是一种数据结构，存储在磁盘上。MySQL普遍使用B+Tree作为索引结构。

Linear Searh: tcp_keepalive

B+Tree Search: tcp_keepalive

索引分类

按照索引列的值必须唯一：

  • 普通索引 ：允许索引列中插入重复值和空值
  • 唯一索引 ：索引列的值必须唯一，但允许为空值。主键索引是一种特殊的唯一索引，不允许有空值。

按照索引列组成的个数：

  • 单列索引：索引只包含单个列，一个表可以有多个单列索引。
  • 组合索引：多个字段组合，创建的索引。遵循“最左前缀原则”

其他索引：

  • 全文索引（FULLTEXT）：在定义索引列上支持值得全文查找。

创建原则

  1. 2000条以上建议建索引
  2. 组合索引：最左前缀原则，“选择性”高的列排在前面。
  3. “选择性”低的列不建议建立索引。Index Selectivity = Cardinality / #T
  4. 尽量地扩展原有索引，不建议新建索引
  5. 字符串尽量使用短索引

使用注意

  1. 隐式类型转换，无法命中索引，扫描全表（建议按照字段类型传值）。
  2. 参与计算（函数和表达式）的列，不走索引。
  3. “负向查询”不走索引。
  4. 前导模糊查询不能走索引。如： like ‘%xxx’

案例

  • 隐式类型转换

explain SELECT * from employees where first_name =1234;

+----+-------------+-----------+------------+------+----------------+------+---------+------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys  | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_first_name | NULL | NULL    | NULL | 299556 |    10.00 | Using where |
+----+-------------+-----------+------------+------+----------------+------+---------+------+--------+----------+-------------+
1 row in set, 3 warnings (0.03 sec)


EXPLAIN SELECT * FROM salaries where salary ='60929';

+----+-------------+----------+-----------------------------------------------------------------------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions                                                                  | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+-----------------------------------------------------------------------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | salaries | p01,p02,p03,p04,p05,p06,p07,p08,p09,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19 | ref  | idx_salary    | idx_salary | 4       | const |   63 |   100.00 | NULL  |
+----+-------------+----------+-----------------------------------------------------------------------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

第一种（int -> varchar）：’first_name’字段为‘varchar(14)’类型，传入int时，发生“隐式类型转换“，扫描全表。
第二种（varchar -> int）：’salary’字段为‘int’类型，传入varchar时，能走索引。

  • 最左前缀原理

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------
| titles |          0 | PRIMARY  |            1 | emp_no      | A         |      431121 |     NULL | NULL   |      | BTREE      |
| titles |          0 | PRIMARY  |            2 | title       | A         |      431417 |     NULL | NULL   |      | BTREE      |
| titles |          0 | PRIMARY  |            3 | from_date   | A         |      442344 |     NULL | NULL   |      | BTREE      |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+

有效索引组合：① emp_no ② emp_no , title ③ emp_no , title , from_date 组合内顺序可以颠倒，mysql查询优化器按索引建立的顺序，“靠左”排序。

  • 隔离列、填坑

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

“from_date”索引未用到，中间隔了个“title”，优化方案如下：


SELECT DISTINCT(title) FROM employees.titles;
+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no='10001'
AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
AND from_date='1986-06-26';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 59      | NULL |    7 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

“填坑”可以提升性能，如果title值过多，用“填坑”不合适，必须建立辅助索引。

  • 匹配前缀字符串

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 56      | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

如果通配符%不出现在开头，则可以用到索引。

  • 范围查询

EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 4       | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
  • 查询条件中含有函数或表达式
  1. 函数

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
  1. 表达式
EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | titles | ALL  | NULL          | NULL | NULL    | NULL | 443308 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+





▲