MYSQL索引最佳实践

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类型的字段建立索引
• 能显著的减少空间使用
• 不能用于覆盖索引
• 选择前缀长度成为一个问题