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”

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

LIKE “ABC%” 的意思是：

“ABC[最小值]”

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

联合索引

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

KEY(col1,col2,col3)

(1,2,3)

使用一个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 WHERE

LAST_NAME=“Smith”

这是典型的索引 KEY(LAST_NAME)

可以使用复合索引

SELECT * FROM EMPLOYEES WHERE

LAST_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 SCORE

DESC LIMIT 10

将使用索引 KEY(SCORE)

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

sort)

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

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 WHERE

CUSTOMER_ID=123

KEY(CUSTOMER_ID,STATUS)

索引通常比数据本身要小

（索引）读取起来更有次序

– 读取数据指针通常是随机的

Min/Max的优化

索引可以帮助优化 MIN()/MAX() 这类的统计函数

– 但只包含以下这些：

SELECT MAX(ID) FROM TBL;

SELECT MAX(SALARY) FROM EMPLOYEE

GROUP BY DEPT_ID

将受益于 KEY(DEPT_ID,SALARY)

“Using index for group-by”

联表查询中索引的使用

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

SELECT * FROM POSTS,COMMENTS WHERE

AUTHOR=“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)

检查异常值

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

使用最多的Title

mysql> 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时 更新的