索引

1为什么要添加索引？

字段不加索引而该字段恰好经常被查询时，按照顺序查找去查找该字段，也就是引起全表扫描，非常耗费时间。

字段添加索引，实现对字段的不相同值建立二叉树结构的索引，按照二分法查找字段的值，查询效率要比未建立索引时高。

2什么情况下应该添加索引？

1.当某个字段经常需要简单查询或者在where条件中使用，并且该字段的不相同值数量较大时，添加索引能更快地查询；

如果该字段仅有少数几个不相同的值，那么不建议加索引。举个例子，字典中假设只有两三页的字，直接查找比建立目录查找要更快；

如果该字段有null值，也不建议，因为索引是不包含null值得。因为这一点，引申出不给字段添加默认值的建议。Mysql对字段是有默认值的， varchar类型的字段默认值为’’，数值类型的字段默认值为0。

2.多表关联查询时，表与表之间关联的字段的不相同值数量庞大（比如几十万）时，建议添加索引。

3索引的数量

理论上每张表最多添加16个索引，视实际情况定。表中有较多字段经常需要查询，该建索引时就建立，而不用过多担心索引数量太多的问题。可以考虑建复合索引（union_index）减少索引的数量。

4哪些情况下mysql不使用已建立的索引

1.order by column_name,索引不支持order by;；

2.betwwn in不影响索引的使用，但or会影响（innoDB引擎的数据库会影响，MyISAM引擎的数据库不会，未考证），所以在字段已建立索引的情况下，不对字段使用or,而考虑用union；

3.字段中有null值；

4.不要在已建立索引的列上进行运算。例如对order_date建立了索引，就不建议使用DATE_FORMAT(order_date,'%Y-%m-%d'),因为这会放弃使用索引而进行全表扫描。

5索引性能的平衡点

字段添加索引提高了字段的检索速度，同时也降低了update,delete,insert的效率。所以一张表如果查询比其他操作用的多，更可以添加较多的索引；如果其他操作比查询使用更频繁，则不要建立过多的索引，避免对更新效率产生影响。

6索引的类型

表的主键都会被当成唯一非聚集索引，其他字段添加普通索引。

一般情况下使用默认类型btree类型足以满足日常使用。

2.Mysql5.6新特性

索引支持模糊匹配“like”。

无论哪个版本，下面的sql都会使用column上的索引

select column from table where column like ‘xxx%’;

而在mysql5.6及之后，下面的sql才支持使用column上的索引

select column from table where column like ‘%xxx%’;

也就是说，可以不用LOCATE(sbustr, column)函数替代like。

Example:

select commodity_code from t_commodity where LOCATE('OP1011010000003160608000944', commodity_code)

耗时0.88s

select commodity_code from t_commodity where commodity_code like '%OP1011010000003160608000944%';

耗时0.78s

虽然like匹配的字段使用了索引，但效率远不及如下的sql

select commodity_code from t_commodity where commodity_code like 'OP1011010000003160608000944%';

耗时0.08s

原因：这条sql使用了索引，并且只扫描局部的索引值，explain解析出的type未”range”,效率比type为”index”的情况还高。

建议：like模糊匹配能不在开头加%就不加，提高查询速度。

3.其他建议

建议在开发环境（广义的开发环境，指非生产相关环境）给mysql开启Strict Mode,帮助开发人员写出高效的sql。