深入分析mysql索引何时创建比较好

索引可以提高数据的检索效率，也可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。排序分组操作主要消耗的就是CPU资源和内存，所以能够在排序分组操作中好好的利用索引将会极大地降低CPU资源的消耗。本篇文章将简单的分析一下如何正确创建MySQL数据索引。

如何判定是否需要创建索引？

1、较频繁地作为查询条件的字段

这个都知道。什么是教频繁呢？分析你执行的所有SQL语句。最好将他们一个个都列出来。然后分析，发现其中有些字段在大部分的SQL语句查询时候都会用到，那么就果断为他建立索引。

2、唯一性太差的字段不适合建立索引

什么是唯一性太差的字段。如状态字段、类型字段。那些只存储固定几个值的字段，例如用户登录状态、消息的status等。这个涉及到了索引扫描的特性。例如：通过索引查找键值为A和B的某些数据，通过A找到某条相符合的数据，这条数据在X页上面，然后继续扫描，又发现符合A的数据出现在了Y页上面，那么存储引擎就会丢弃X页面的数据，然后存储Y页面上的数据，一直到查找完所有对应A的数据，然后查找B字段，发现X页面上面又有对应B字段的数据，那么他就会再次扫描X页面，等于X页面就会被扫描2次甚至多次。以此类推，所以同一个数据页可能会被多次重复的读取，丢弃，在读取，这无疑给存储引擎极大地增加了IO的负担。

3、更新太频繁地字段不适合创建索引

当你为这个字段创建索引时候，当你再次更新这个字段数据时，数据库会自动更新他的索引，所以当这个字段更新太频繁地时候那么就是不断的更新索引，性能的影响可想而知。大概被检索几十次会更新一次的字段才比较符合建立索引的规范。而如果一个字段同一个时间段内被更新多次，那么果断不能为他建立索引。

4、不会出现在where条件中的字段不该建立索引

注意：

并非所有的数据库都以相同的方式使用索引。作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。在多数情况下，索引用于数据检索的速度优势大大超过它的不足之处。但是，如果应用程序非常频繁地更新数据或磁盘空间有限，则可能需要限制索引的数量。

可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。

如果经常同时搜索两列或多列或按两列或多列排序时，索引也很有帮助。例如，如果经常在同一查询中为姓和名两列设置判据，那么在这两列上创建多列索引将很有意义。

确定索引的有效性：

● 检查查询的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以选择的对象。

● 对新索引进行试验以检查它对运行查询性能的影响。

● 考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。

● 检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。

● 检查某列中唯一数据值的数量，并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性，这有助于确定该列是否适合建立索引，如果适合，确定索引的类型。

MySQL何时使用索引

对一个键码使用>, >=, =, <, <=, IF NULL和BETWEEN

SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
SELECT * FROM table_name WHERE key_part1 IS NULL;
当使用不以通配符开始的LIKE

SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
在进行联结时从另一个表中提取行时

SELECT * from t1,t2 where t1.col=t2.key_part
找出指定索引的MAX()或MIN()值

SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
一个键码的前缀使用ORDER BY或GROUP BY

SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3
在所有用在查询中的列是键码的一部分时间

SELECT key_part3 FROM table_name WHERE key_part1=1

MySQL何时不使用索引

如果MySQL能估计出它将可能比扫描整张表还要快时，则不使用索引。例如如果key_part1均匀分布在1和100之间，下列查询中使用索引就不是很好：

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
如果使用HEAP表且不用=搜索所有键码部分。

在HEAP表上使用ORDER BY。

如果不是用键码第一部分

SELECT * FROM table_name WHERE key_part2=1
如果使用以一个通配符开始的LIKE

SELECT * FROM table_name WHERE key_part1 LIKE '%jani%'
搜索一个索引而在另一个索引上做ORDER BY

SELECT * from table_name WHERE key_part1 = # ORDER BY key2

建立MySql数据库索引需要注意的几个问题

1.建立索引的时机：若表中的某字段出现在select、过滤、排序条件中，为该字段建立索引是值得的。
2.对于like %xxx的模糊查询，普通的索引是无法满足的，需要建立全文索引。
3.对于有多个条件的，比如: "...where a=xx and b=yy","...where a=xx order by b","...where a=xx group by b"。需要使用组合索引。但是组合索引只能在SQL语句中满足"最左前缀"的条件下使用。且组合索引有一些副作用，如索引尺寸可能比数据本身大，因为组合索引的组合条目多。所以在实际应用中，要量身定做，使用慢查询分析工具分析。
4.开启索引缓存，直接在内存中查找索引，不用再磁盘中。
5.建立索引是有代价的，当update、delete语句执行时，会使得索引更新，将耗掉更多的时间。可以使用mysqlreport报告，了解select、update、delete、insert、replace各语句所占的百分比.

您可能感兴趣的文章：
mysql索引的建立原则
mysql索引优化注意问题
Mysql索引分类与优化
mysql索引创建与管理笔记
mysql查看索引方法
如何创建mysql索引
深入分析mysql索引何时创建比较好
mysql索引类型有哪些
MySql索引优化注意要点
mysql索引的建立规则