mysql索引优化的前篇（一）

前言：

由一个索引降维引发的思考炸弹。关于MySQL索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。没有对比就没有伤害。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。

个人理解：

MySQL索引是一种数据结构,可以是BTREE,RTREE,或者HASH结构.
BTREE适合用于查找某范围内的数据,可以很快的从当前数据找到下条数据.
RTREE常用于查询比较接近的数据.
HASH结构则适用于随机访问的场合,查找每条数据的时间几乎相同.
显然,若要查找某个时间段的数据,用BTREE结构要比HASH结构快得多.
另外还有FULLTEXT(全文)和SPATIAL(空间)这两个索引类型

一、简单说下大概逻辑下几个常有的索引：

• 唯一索引
  

  与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

• 普通索引

  这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

• 组合索引（最左原则）

  平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引

插语：

虽然索引有利于查询搜索，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

二、下面是一些总结以及收藏的MySQL索引的注意事项和优化方法：

下面引用一张图解释下：

1. 何时使用聚集索引或非聚集索引？

2. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

3. 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作

4. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引

5. like语句操作—最左原则可使用得到

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%xxx%” 不会使用索引而like “xxx%”可以使用索引。

6. 不要在列上进行运算–因为会使得索引失效

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。

7、索引降维思想

这个需要在不同场景进行考虑，当使用多个单列索引的时候，因为mysql查询的时候只能用一个索引，索引这个时候就要考虑下条件，如果不需要组合索引，那么使用单列索引，在这些单列索引中，你要考每个条件对查询的影响,例如:一个月内，有一万个账户，每天打出三万通话记录，可知，单用户的通话频度不高，因此，先定位phone索引集再排除时间的搜索方式，肯定比先定时间再定账户的效率高。
==注：这是特定场景！！！具体请以explain与profiling去分析，MYSQL的执行解释器，没有这么简单。==

8、组合索引最左原则

使用组合索引的时候也需要考虑没个索引字段的排序，在查询的时候最左的字段需要是第一个字段，若不是，则索引不生效，索引匹配的时候会单遇到>= /<= 就会停止匹配，但这里并不代表>=不能使用索引

最后后总结一下：

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，但索引不是越多越好，要在合适的地方合适的场景用到，切记切记。

我的个人博客：blog.taobaoroom.cn

我的CSDN地址：https://blog.csdn.net/Fa_Ker

备注：后续内容会不断更新