MYSQL索引最佳实践

你做了一个明智的选择

理解索引对开发和dba来说都是极其重要

差劲的索引对产品问题负相当大的一部分责任

索引不是多么高深的问题

MySQL 索引一览表

理解索引

为你的应用创建最佳索引

拥抱MySQL的限制

简述索引

索引有什么用

为从数据库读取数据加速

强制约束 (唯一索引 UNIQUE, 外键 FOREIGN KEY)

没有任何索引的情况下查询页能正常运行

但是那可能需要执行很长的时间

你可能听说过的索引类型

BTREE索引 – mysql中主要的索引类型

RTREE索引 – 只有MyISAM支持, 用于GIS

HASH 索引 – MEMORY, NDB 支持

BITMAP 索引 – MySQL 不支持

FULLTEXT 索引 – MyISAM, Innodb（MySQL 5.6以上支持）

类BTREE索引家族

有很多不同的实现

在可加速的操作中共享相同的属性

内存相比硬盘使生活变得美好

B+树通常用于硬盘存储

数据存储于叶子节点

B+Tree 示例

MyISAM、Innodb索引对比

MyISAM

数据指针指向数据文件中的物理位置

所有索引都是一样的（指向物理位置））

Innodb

主键索引 (显式或隐式) - 直接将数据存储于索引的叶子节点，而不是指针

二级索引 – 保存主键索引的值作为数据指针

BTREE索引能用于什么操作 ?

查询所有 KEY=5 的记录 (点查询)

查询所有 KEY>5 的记录 (开合间)

查询所有 5

不适用于：查询KEY最后一个数字等于0的所有记录

因为这不能定义为范围查询操作

字符索引

这（和数值）没什么区别… 真的

collation是为字符串定义的排序规则

如： “AAAA” < “AAAB”

前缀LIKE 查询是一种特殊的范围查询

LIKE “ABC%” 的意思是：

“ABC[最小值]”

LIKE “%ABC” 无法使用索引查询

联合索引

是这样进行排序的， 比较首列，然后第二列，第三列以此类推，如：

KEY(col1,col2,col3)

(1,2,3) < (1,3,1)

使用一个BTREE索引，而不是每个层级一个单独的BTREE索引

索引的开销

索引是昂贵的，不要添加多余的索引

多数情况下，扩展索引比添加一个新的索引要好

写 - 更新索引常常是数据库写操作的主要开销

读 - 需要再硬盘和内存开销空间; 查询优化中需要额外的开销

索引成本的影响

长主键索引（Innodb） – 使所有相应的二级索引 变得更长、更慢

“随机”主键索引（Innodb） – 插入导致大量的页面分割

越长的索引通常越慢

Index with insertion in random order – SHA1(‘password’)

低区分度的索引是低劣的 – 在性别字段建的索引

相关索引是不太昂贵的– insert_time与自增id是相关的

Innodb表的索引

数据按主键聚集

选择最佳的字段作为主键

比如评论表 – (POST_ID,COMMENT_ID) 是作为主键的不错选择，使得单个post的评论聚在一起

或者 “打包” 单个 BIGINT（字段）

主键隐式地附加到所有索引中

KEY (A) 实质上是 KEY (A,ID)

覆盖索引，有利于排序

MySQL是如何使用索引的

查询

排序

避免读取数据（只读取索引）

其他专门的优化

使用索引进行查询

SELECT * FROM EMPLOYEES WHERELAST_NAME=“Smith”

这是典型的索引 KEY(LAST_NAME)

可以使用复合索引

SELECT * FROM EMPLOYEES WHERELAST_NAME=“Smith” AND DEPT=“Accounting”

将会使用索引 KEY(DEPT,LAST_NAME)

复合索引比较复杂

Index (A,B,C) - 字段顺序问题

下列情形将会使用索引进行查询（全条件）

A>5

A=5 AND B>6

A=5 AND B=6 AND C=7

A=5 AND B IN (2,3) AND C>5

下列条件将不会使用索引

B>5 – 条件没有B字段前的A

B=6 AND C=7 - 条件没有B、C字段前的A

以下情形使用索引的一部分

A>5 AND B=2 - 第一个字段A的范围查询，导致只用上了索引中A字段的部分

A=5 AND B>6 AND C=2 - B字段的范围范围查询，导致只使用了索引中A和B两个字段的部分

MySQL优化器的第一法则

在复合索引中，MySQL在遇到返回查询(<,>,BETWEEN)时，将停止中止剩余部分（索引）的使用；但是使用IN(…)的"范围查询"则可以继续往右使用索引（的更多部分）

所用索引进行排序

SELECT * FROM PLAYERS ORDER BY SCOREDESC LIMIT 10

将使用索引 KEY(SCORE)

不使用索引将进行非常昂贵的“filesort”操作(externalsort)

常常使用组合索引进行查询

SELECT * FROM PLAYERS WHERE COUNTRY=“US”ORDER BY SCORE DESC LIMIT 10

最佳选择是 KEY(COUNTRY,SCORE)

高效排序的联合索引

变得更加受限!

KEY(A,B)

以下情形将会使用索引进行排序

ORDER BY A - 对索引首字段进行排序

A=5 ORDER BY B - 对第一个字段进行点查询，对第二个字段进行排序

ORDER BY A DESC, B DESC - 对两个字段进行相同的顺序进行排序

A>5 ORDER BY A - 对首字段进行范围查询，并对首字段进行排序

以下情形将不使用索引进行排序

ORDER BY B - 对第二个字段进行排序（未使用首字段）

A>5 ORDER BY B – 对首字段进行范围查询，对第二个字段进行排序

A IN(1,2) ORDER BY B - 对首字段进行IN查询，对第二个字段进行排序

ORDER BY A ASC, B DESC - 对两个字段进行不同顺序的排序

MySQL使用索引排序的规则

不能对两个字段进行不同顺序的排序

对非ORDER BY部分的字段只能使用点查询（=）– 在这种情形下，IN()也不行

避免读取数据（只读取索引）

“覆盖索引”– 这里指 适用于特定查询的索引，而不是一种索引的类型

只读取索引，而不去读取数据

SELECT STATUS FROM ORDERS WHERECUSTOMER_ID=123

KEY(CUSTOMER_ID,STATUS)

索引通常比数据本身要小

（索引）读取起来更有次序– 读取数据指针通常是随机的

Min/Max的优化

索引可以帮助优化 MIN()/MAX() 这类的统计函数– 但只包含以下这些：

SELECT MAX(ID) FROM TBL;

SELECT MAX(SALARY) FROM EMPLOYEEGROUP BY DEPT_ID

将受益于 KEY(DEPT_ID,SALARY)

“Using index for group-by”

联表查询中索引的使用

MySQL 使用 “嵌套循环（Nested Loops）”进行联表查询

SELECT * FROM POSTS,COMMENTS WHEREAUTHOR=“Peter” AND COMMENTS.POST_ID=POSTS.ID

扫描表POSTS查询所有复合条件的 posts

循环posts 在表COMMENTS 中查找 每个post的所有comments

使每个关联的表（关联字段）都使用上索引显得非常的重要

索引只有在被查询的字段上是必要的– POSTS.ID字段的索引再本次查询中是用不上的

重新设计不能很好的所有索引的联合查询吧

使用多索引

MySQL可以使用超过1个索引

“索引合并”

SELECT * FROM TBL WHERE A=5 AND B=6– 可以分别使用索引 KEY(A)和 KEY(B)

索引 KEY(A,B) 是更好的选择

SELECT * FROM TBL WHERE A=5 OR B=6– 两个索引同时分别被使用

索引 KEY(A,B) 在这个查询中无法使用

前缀索引

你可以在字段最左前缀建立索引

ALTER TABLE TITLE ADD KEY(TITLE(20));

需要对BLOB/TEXT类型的字段建立索引

能显著的减少空间使用

不能用于覆盖索引

选择前缀长度成为一个问题

选择前缀长度

前缀应该有足够的区分度

比较distinct前缀、distinct整个字段的值

mysql> select count(distinct(title)) total,count(distinct(left(title,10))) p10,count(distinct(left(title,20))) p20 from title;

1 row in set (44.19 sec)

检查异常值

确保不会有很多记录使用相同的前缀

使用最多的Titlemysql> select count(*) cnt, title tl from title group by tl order by cnt desc limit 3;

3 rows in set (27.49 sec)

使用最多的Title 前缀 mysql> select count(*) cnt, left(title,20) tl from title group by tl order by cnt desc limit 3;

3 rows in set (33.23 sec)

MySQL如何选择使用哪个索引的?

每次查询动态选择– 查询文本中常量很重要

评估需要查询的行数 对给定的索引，在表中进行"dive"

如果（dive）不可行时，使用 “Cardinality” 进行统计– 这是进行 ANALYZE TABLE时 更新的

更多关于索引的选择

并不只是最小化扫描行数

很多其他的heuristics（尝试） and hacks– 对Innodb来说主键是很重要的

覆盖索引效益

Full table scan is faster, all being equal（这句不是太明白）

我们也可以使用索引进行排序

须知

验证MYSQL实际使用的执行计划

注意是可以根据常量和数据动态改变的

使用EXPLAIN

EXPLAIN 是一个很好的工具，可以看到MYSQL将如何进行查询

mysql> explain select max(season_nr) from title group by production_year;

http://dev.mysql.com/doc/refm...

记住，真实的查询可能跟执行计划不同

1 row in set (0.01 sec)

MySQL Explain 101

“type” 从好到差排序如下：– system,const,eq_ref,ref,range,index,ALL

注意 “rows” – 更大的数值意味着更慢的查询

检查 “key_len” – 显示索引的哪些部分真实使用到了

留意"Extra"

Using Index - 好

Using Filesort, Using Temporary - 差

索引策略

为你的关键性能查询集建立索引– 整体取审视他们，而不是一个个看

最好所有的查询条件和联表条件都使用索引– 起码区分度最高的部分是

一般来说，可以的话，扩展索引，而不是创建新的索引

修改时记得验证对性能的影响

索引策略示例

按能支持更多查询的顺序建立索引

SELECT * FROM TBL WHERE A=5 AND B=6

SELECT * FROM TBL WHERE A>5 AND B=6– 对两个查询来说 KEY(B,A) 是更好的选择

把所有都是点查询的字段放到索引的首位

不要添加非性能关键查询的索引– 太多的索引会使MYSQL慢下来

Trick #1: 枚举范围

KEY (A,B)

SELECT * FROM TBL WHERE A BETWEEN 2AND 4 AND B=5

将只使用索引的第一个字段部分

SELECT * FROM TBL WHERE A IN (2,3,4) ANDB=5

索引的两个字段部分都使用

Trick #2: 添加一个假的条件

KEY (GENDER,CITY)

SELECT * FROM PEOPLE WHERE CITY=“NEWYORK”

完全用不上索引

SELECT * FROM PEOPLE WHERE GENDER IN(“M”,”F”) AND CITY=“NEW YORK”

将用上索引

这个Trick在低区别度的字段上可以很好的使用

Gender, Status, Boolean Types etc

Trick #3: 虚实Filesort

KEY(A,B)

SELECT * FROM TBL WHERE A IN (1,2) ORDER BYB LIMIT 5;

无法使用索引进行排序

(SELECT FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALL (SELECT FROM TBL WHERE A=2 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5;

将会用上索引，而“filesort”只用于对不超过10行记录