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<10 的记录 (闭合间)
- 不适用于:查询KEY最后一个数字等于0的所有记录
- 因为这不能定义为范围查询操作
字符索引
- 这(和数值)没什么区别… 真的
- collation是为字符串定义的排序规则
- 如: “AAAA” < “AAAB”
- 前缀LIKE 查询是一种特殊的范围查询
- LIKE “ABC%” 的意思是:
- “ABC[最小值]”<KEY<“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;
total | p10 | p20 |
---|---|---|
998335 | 624949 | 960894 |
1 row in set (44.19 sec)
- 检查异常值
- 确保不会有很多记录使用相同的前缀
使用最多的Titlemysql> select count(*) cnt, title tl from title group by tl order by cnt desc limit 3;
cnt | tl |
---|---|
136 | The Wedding |
129 | Lost and Found |
112 | Horror Marathon |
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;
cnt | tl |
---|---|
184 | Wetten, dass..? aus |
136 | The Wedding |
129 | Lost and Found |
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将如何进行查询
- http://dev.mysql.com/doc/refm…
- 记住,真实的查询可能跟执行计划不同
mysql> explain select max(season_nr) from title group by production_year;
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | title | range | NULL | production_year | 5 | NULL | 201 | Using index for group-by |
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行记录
更多细节问答:http://www.oschina.net/translate/mysql-indexing-best-practices-webinar-questions-followup