前言：最近由于项目数据量巨增，对于数据库压力增大，需要从多方面对数据库进行调优，这里就从索引来给大家做下简单分享
什么是索引(Index)
这里引用Mysql的官方解释
1
2
| >MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。使用索引的目的在于提高查询效率
>
|
Mysql常用索引
由于Mysql有多种存储引擎(MyISAM、InnoDB、MEMORY)，这里只列出各种存储引擎的特点，不做展开
其索引模式为B-Tree和Hash
1
2
3
4
| >B-Tree索引的时间复杂度是O(log(n))
>
>Hash索引的时间复杂度是O(1)
>
|
由此可见如果业务大部分是单条查询，使用Hash索引性能更好
索引创建的几个原则
（1）适合索引的列是出现在WHERE 子句中的列
最适合索引的列是出现在WHERE 子句中的列，或连接子句中指定的列，而不是出现在SELECT 关键字后的选择列表中的列。
（2）使用唯一索引
考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。
比如删除标识，各种数据状态等
（3）使用短索引
如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个CHAR(200) 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。
比如，1000-1999，这1000个值，第一位都是1，所以这时候可以建立短索引后三位
（4）利用最左前缀
在创建一个n 列的索引时，实际是创建了MySQL可利用的n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
比如，对用户ID，部门ID做了索引，用户ID的搜索次数大于部门ID,那么用户ID索引应该在部门索引的左边，因为用户ID先被索引命中后，就不会在去部门ID的索引中继续搜索了(可参照B-Tree的数据结构来理解)
（5）不要过度索引
不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍 过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表 的修改速度。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左 索引。如果是，则就不要费力去增加这个索引了，因为已经有了。
（6）考虑在列上进行的比较类型。
索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于LIKE 运算。如果只将某个列用于其他类型的运算时（如STRCMP( )），对其进行索引没有价值。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
创建索引的时机
（1）什么时候创建索引
较频繁地作为查询条件的字段，也就是说最适合索引的列是出现在WHERE 子句中的列，或连接子句中指定的列，而不是出现在SELECT 关键字后的选择列表中的列。
（2）什么时候不创建索引
表记录太少：
如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。
唯一性太差的字段：如状态字段、类型字段。那些只存储固定几个值的字段，例如用户登录状态、消息的status等。
更新太频繁地字段不适合创建索引：
当你为这个字段创建索引时候，当你再次更新这个字段数据时，数据库会自动更新他的索引，所以当这个字段更新太频繁地时候那么就是不断的更新索引。
如果一个字段同一个时间段内被更新多次，那么不能为他建立索引。
一些常见的SQL实践
（1）负向条件查询不能使用索引
1
| select * from order where status!=0 and stauts!=1
|
not in/not exists都不是好习惯
可以优化为in查询：
1
| select * from order where status in(2,3)
|
（2）前导模糊查询不能使用索引
1
| select * from order where desc like '%XX'
|
而非前导模糊查询则可以：
1
| select * from order where desc like 'XX%'
|
（3）数据区分度不大的字段不宜使用索引
1
| select * from user where sex=1
|
原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。
经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。
（4）在属性上进行计算不能命中索引
1
| select * from order where YEAR(date) < = '2017'
|
即使date上建立了索引，也会全表扫描，可优化为值计算：
1
| select * from order where date < = CURDATE()
|
或者：
1
| select * from order where date < = '2017-01-01'
|
（5）允许为null的列，查询有潜在大坑
单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集
1
| select * from user where name != 'shenjian'
|
如果name允许为null，索引不存储null值，结果集中不会包含这些记录。所以，请使用not null约束以及默认值。
（6）复合索引最左前缀，并不是值SQL语句的where顺序要和复合索引一致
用户中心建立了(login_name, passwd)的复合索引
1
| select * from user where login_name=? and passwd=?
|
1
| select * from user where passwd=? and login_name=?
|
都能够命中索引
1
| select * from user where login_name=?
|
也能命中索引，满足复合索引最左前缀
1
| select * from user where passwd=?
|
不能命中索引，不满足复合索引最左前缀
（7）使用ENUM而不是字符串
ENUM保存的是TINYINT，别在枚举中搞一些“中国”“北京”“技术部”这样的字符串，字符串空间又大，效率又低。
（8）如果明确知道只有一条结果返回，limit 1能够提高效率
1
| select * from user where login_name=?
|
可以优化为：
1
| select * from user where login_name=? limit 1
|
原因：
你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动
（9）把计算放到业务层而不是数据库层，除了节省数据的CPU，还有意想不到的查询缓存优化效果
1
| select * from order where date < = CURDATE()
|
这不是一个好的SQL实践，应该优化为：
1
2
3
4
| $curDate = date('Y-m-d');
$res = mysql_query(
'select * from order where date < = $curDate'
);
|
原因：
释放了数据库的CPU
多次调用，传入的SQL相同，才可以利用查询缓存
