mysql优化之使用索引

来源：互联网发布：java 计算二叉树深度编辑：程序博客网时间：2024/05/19 19:31

用来加快查询速度的技术有很多，可以被调节的参数也有很多，其中最重要的就是索引。有时在调优的时候，一个索引被加入，或者一个索引被删除，能够显著的提升查询响应的速度。特别是在系统CPU使用100%的时候，这时多数时候，都是由于索引使用不当。在没有适当的建立索引的时候，通过其它的方法来提高性能，都是非常难并且效果也不明显的。

在这篇博文中主要讨论索引对查询的影响，包括好的影响和不好的影响。

一，索引的优点

为什么要使用索引，这个就没什么好说的了。其实索引的优点也是不言自明的，一般情况下，主要优点有以下几点，当然不是说，索引除了这以外，就没用了，实际上索引的使用和优化器的优化也是密切相关的。

使用WHERE子句时，索引可以帮助把匹配的数据行快速的找出来。
在数据表之间关联的时候，索引也可以快速的从其它数据表中获取数据。
在使用MIN或者MAX(),使用索引，也可以很快的把数据行找出来。
在使用ORDER BY 或者 GROUP BY子句分类及分组的时候，索引操作也是必不可少的。

二，索引的缺点

索引也不能一味的使用，除了可以看得到的缺点以外，还有很多其它的限制。先说可以看得到的缺点。

索引加快了检索速度，但是在往带有索引的数据列里插入、删除以及更新的时候，会影响操作执行的速度。
索引需要占据磁盘的空间，索引越多，占据磁盘的空间越大。

所以，也不能对索引随意的创建，只有我们确实需要索引来加快查询速度的时候，才去创建索引。

三，选取索引

一般如何确定需要为哪些数据项建立索引以及建立索引过程需要注意的问题有哪些。现在主要讨论这两个问题：

第一个问题比较好处理，只要是我们在查询过程中，需要用到的查询条件，就是在WHERE子句后面，我们都尽可能的为其建立索引，如：

select name from i_node where time > '2012'

此时应为time建立适当的索引，而name暂时不用考虑为其建立索引。

另外在分类或者分组的子句中，也考虑为该数据项建立索引。

下面主要讨论，建立索引的一些注意问题：

在建立索引时，综合考虑各数据列的维度势。数据列的维度等于它所容纳的非重复值的个数。这是因为查询优化程序确定出某一个数值在数据表的数据行中出现频率超过30%时，查询优化程序通常会路过对索引的使用，而进行全数据表的扫描。
尽量对短小的值进行索引。在设计数据列的类型时，只要使用可以容纳需要的存储数据就可以了。短小的值可以让比较操作更快速的完成，还可以使索引文件或数据更小，另外对于键值缓存也可以容纳的更多。
为字符串值的前缀设计索引。这个没什么好说的，如果字段类型是VARCHAR（200），我们只需要给前20左右个字符设计索引就可以了。
充分利用最左边的前缀。这是指如果创建了一个复合索引的时候，最左边的索引可以单独使用，如我们创建了(col_a,col_b,col_c)的复合索引，那也相当于创建了 (col_a) (col_a,col_b)这两个索引。
不要建立过多的索引。这个原因在前面说过，索引不是只有优点，也有缺点的。
让索引的类型与你打算进行的比较操作的类型保持匹配。索引实现一般分为散列索引和B树索引两类，这两种类型的索引都有自己很擅长的比较方式，对于散列索引，在使用”=“操作符时，就会非常快了，但如果用散列索引查找一个范围之内的数据行，这时效果肯定是不理想的。对于B树索引，查找一些具有比较性质或者范围比较操作时，效率就会非常高。
在带有些函数操作数据列时，索引将会失效。如
SELECT * from i_node where YEAR(time) > 2011 ;
此时，索引就会失效，而进行通表搜索。