【高并发基础】MySQL索引优化

前言

本文整理自MySQL官方网站，官网的连接比较琐碎。防止反复多开页面，这边记录一些重点。

How MySQL Uses Indexes

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes.

PRIMARY KEY, UNIQUE, INDEX　被存储在Ｂ树中（官网是说的B树，实质是以B+树作为技术实现）

• 索引分聚集索引和二级索引 原文地址

The Physical Structure of an InnoDB Index

• 聚集索引
  InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
  更详细的数据结构图解

MySQL uses indexes for these operations

• 命中where 语句中的条件
• 筛选掉不合适的行（列上的值超过了存储长度，无法等值命中，只能筛掉不等的值）

If a search term exceeds the index prefix length, the index is used to exclude non-matching rows, and the remaining rows are examined for possible matches. (原文地址)

For additional information about index prefixes,

• 最左匹配原则

if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

最左匹配原则官网示例：

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);
1
2
3
4
5
6
7

（1）可以使用name索引：

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';
1
2
3
4
5
6
7
8
9
10
11
12

（2）无法使用name索引

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
 WHERE last_name='Jones' OR first_name='John';
1
2
3
4

联合索引的隐含兼容能力：(col1, col2, col3) 相当于建立了 (col1), (col1, col2), and (col1, col2, col3) 索引

if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

SELECT * FROM tbl_name WHERE col1=val1; -- 可以用索引
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; -- 可以用索引

SELECT * FROM tbl_name WHERE col2=val2; -- 不可以用索引
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; -- 不可以用索引
1
2
3
4
5

• 表连接中，同样类型、长度、字符集的索引作为连接条件可以提效

To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size.

• 用索引的不同部分提升效率

To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX() expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example:
SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;

转化一下上文的意思：max(key_part2) where col = key_part1； 如果存在联合索引index(key_part1，key_part2) 那么这个查询很快。查询优化依据的是索引B+树的有序性取最大最小值非常快，max声明的列处于联合索引的第二个位置，那么第一个位置也需要用等值命中。以此类推，index(key_part1，key_part2，key_part3) max(key_part3) 需要让前两个索引列做where的等值命中。值得一提的是index(key_part1，key_part2，key_part3) 在 SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10; 依旧能走索引。因为max(key_part2) 只要求key_part1 等值命中

• order by 的时候使用组合索引，注意最左匹配和倒序情况。DESC读取索引的顺序跟表达式相反。

To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable index (for example, ORDER BY key_part1, key_part2). If all key parts are followed by DESC, the key is read in reverse order.

1. order by 优化
2. group by 优化

• 索引覆盖

In some cases, a query can be optimized to retrieve values without consulting the data rows

直接查索引的B树就能获得数据，不用在通过索引

SELECT key_part3 FROM tbl_name
  WHERE key_part1=1
1
2

• 避免全表扫描(EXPLAIN 出来Type字段取值为ALL )

The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query.
会产生全表扫描的情况：

1. 行数非常少的表
2. ON 或者 WHERE 选择的列没有建立索引
3. 查询优化器对 where 子句进行优化，发现全表扫描比索引更快。
4. 在索引上使用的查询关键字，在索引分布中区分度不大

• 对于数据量大的表，避免查询优化器错误得选择索引

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
  WHERE t1.col_name=t2.col_name;
1
2