mysql的索引优化如何运用

对于数据库来讲系统表（如：sysobjects等）就是目录，而标字段上的索引就如同书本后面的关键字索引。

数据库中，目录（数据字典）和索引的区别：目录纵向、索引横向。

一、影响索引作用的因素

区分度（检索比率）

优化器根据统计信息来生成执行计划，如果数据库没有收集索引的统计信息，优化器就无从下手，只能按部就班，通过全表扫描来执行查询。所以，新创建的索引需要重新运行统计，否则索引无效。

举一个例子，有个表TABLE1，其中有一个字段COL1取值是“1”、“2”、“3”三种，运行统计的结果是告诉数据库TABLE1中的数据其中字段COL1的各种取值所占的比重。示意如下：

“1” － 12%；

“2” － 66%；

“3” － 22%。

假设还有个字段COL2取值和数据所占的百分比如下：

“A” － 50%；

“B” － 50%。

则查询语句1：

select * from TABLE1 where COL1 = “1”and COL2 = “A”，

数据库优化器会优先选择字段COL1上的索引来定位表中的数据，因为通过COL1上的索引就可以将结果集迅速定位在一个小范围内12%。而相反的，对于查询语句2：

select * from TABLE1 where COL1 = “2”and COL2 = “A”，

数据库会优先选择COL2上的索引，因为对于语句2的查询条件COL2上的索引具有更好的区分度。

从上面可以看出，数据库的优化器通常会优先选择区分度较高的索引（针对于查询条件，条件不同选择的索引可能不同）。

数据库里的数据是变化的，所以某个时候采集的统计信息，过一段时间后可能会过时，甚至误导数据库优化器，这样同样会造成运行性能的低下。所以除了，最初建立索引时需要运行统计，在表中的数据发生变化时也需要运行统计。经验：当表中数据量变化达到10%时，需要重新运行统计。

二、聚集度

范围扫描

表大小：

小型表

中大型表

超大型表

业务类型

OLTP和OLAP

函数与索引

函数，like语句。。。

Substring(col_name,1, 3)vs. Substring(col_name, 3, 3)

like ‘QQQ% vs. like ‘%QQQ’

索引开销

性能利器

双刃剑

索引对插入操作的影响（Oracle）

索引对插入操作的影响（MySQL）

比较索引与促发器对性能的影响

索引总结

使用索引实现关键数据的高效访问。但是需要知道每个索引都会给数据库更新带来额外的开销。这就意味着，低效的索引会给数据库带来灾难。

对于数据库，我们必须关注关键数据的读取，为他们提供最高效的访问路径。对此，基本策略就是建立索引。在索引提供高效访问的同时，也带来了额外的系统开销。开销分为磁盘空间的开销和处理器开销。下面我们讨论一下处理器开销。每当在表中插入或删除记录时，该表的所有索引必须进行相应调整。每当对已建立索引的字段进行更新时，这种调整也会发生。举例子说，如果在未建立索引的表中插入数据需要100个单位时间，那么每增加一个索引就会增加100到250个单位时间。有趣的是，维护索引的开销与简单触发器带来的开销大致相当。

在建立索引前线介绍一些最通俗的信息，这些信息来自developWorks，列出这些信息是因为我觉得这些信息通常情况下是值得参考的：

1.当要在一个合理的时间内结束查询时，应避免添加索引，因为索引会降慢更新操作的速度并消耗额外的空间。有时候还可能存在覆盖好几个查询的大型索引。

1.基数较大的列很适合用来做索引。

3.考虑到管理上的开销，应避免在索引中使用多于5个的列。

4.对于多列索引，将查询中引用最多的列放在定义的前面。

5.避免添加与已有的索引相似的索引。因为这样会给优化器带来更多的工作，并且会降慢更新操作的速度。相反，我们应该修改已有的索引，使其包含附加的列。例如，假设在一个表的 (c1,c2)上有一个索引i1。您注意到查询中使用了"wherec2=?"，于是又创建一个(c2)上的索引i2。但是这个相似的索引没有添加任何东西，它只是i1的冗余，而现在反而成了额外的开销。

6.如果表是只读的，并且包含很多的行，那么可以尝试定义一个索引，通过CREATE INDEX中的INCLUDE子句使该索引包含查询中引用的所有列（被INCLUDE子句包含的列并不是索引的一部分，而只是作为索引页的一部分来存储，以避免附加的数据FETCHES）。

对于数据仓库（查询系统数据库）可以建立较多的索引（索引和数据的比例可以是1：1）。

决定是否使用索引，可以重点考虑检索比率。即，判断索引有效性的依据，就使用键值作唯一性条件检索出的数据的百分比。百分比越低，索引越有效。做出这个论断的前提是一些假设，如磁盘访问的相关性能。

索引键值相关记录的物理位置是否相邻也很重要，因为是通过块来操作数据的。建立了索引之后，如果索引键所指向的记录散布于整个表中，即使这些记录在表中占的比率很小，但因为它们分散在整个磁盘上，所以索引的性能就会大打折扣。

另外值得注意的是，函数和类型转换可能导致索引失效。

以上就是mysql的索引优化如何使用的详细内容，更多请关注php中文网其它相关文章！