数据库索引优化

最近由于项目数据量巨增，对于数据库压力增大，需要从多方面对数据库进行调优，这里就从索引来给大家做下简单分享

什么是索引(Index)

这里引用Mysql的官方解释

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。使用索引的目的在于提高查询效率

Mysql常用索引

由于Mysql有多种存储引擎(MyISAM、InnoDB、MEMORY)，这里只列出各种存储引擎的特点，不做展开

其索引模式为B-Tree和Hash

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）负向条件查询不能使用索引

select * from order where status!=0 and stauts!=1

not in/not exists都不是好习惯

可以优化为in查询：

select * from order where status in(2,3)

（2）前导模糊查询不能使用索引

select * from order where desc like '%XX'

而非前导模糊查询则可以：

select * from order where desc like 'XX%'

（3）数据区分度不大的字段不宜使用索引

select * from user where sex=1

原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。

经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。

（4）在属性上进行计算不能命中索引

select * from order where YEAR(date)

即使date上建立了索引，也会全表扫描，可优化为值计算：

select * from order where date

或者：

select * from order where date

（5）允许为null的列，查询有潜在大坑

单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集

select * from user where name != 'shenjian'

如果name允许为null，索引不存储null值，结果集中不会包含这些记录。所以，请使用not null约束以及默认值。

（6）复合索引最左前缀，并不是值SQL语句的where顺序要和复合索引一致

用户中心建立了(login_name, passwd)的复合索引

select * from user where login_name=? and passwd=?

select * from user where passwd=? and login_name=?

都能够命中索引

select * from user where login_name=?

也能命中索引，满足复合索引最左前缀

select * from user where passwd=?

不能命中索引，不满足复合索引最左前缀

（7）使用ENUM而不是字符串

ENUM保存的是TINYINT，别在枚举中搞一些“中国”“北京”“技术部”这样的字符串，字符串空间又大，效率又低。

（8）如果明确知道只有一条结果返回，limit 1能够提高效率

select * from user where login_name=?

可以优化为：

select * from user where login_name=? limit 1

原因：

你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动

（9）把计算放到业务层而不是数据库层，除了节省数据的CPU，还有意想不到的查询缓存优化效果

select * from order where date

这不是一个好的SQL实践，应该优化为：

$curDate = date('Y-m-d');

$res = mysql_query(

'select * from order where date

);

原因：

释放了数据库的CPU

多次调用，传入的SQL相同，才可以利用查询缓存

著作权归作者所有。

商业转载请联系作者获得授权,非商业转载请注明出处。

原文: https://leishunyu.github.io/