索引
1为什么要添加索引?
字段不加索引而该字段恰好经常被查询时,按照顺序查找去查找该字段,也就是引起全表扫描,非常耗费时间。
字段添加索引,实现对字段的不相同值建立b+树结构的索引,数据存储在叶子节点上,每个叶子节点存储相邻节点的数据,查询效率要比未建立索引(顺序查询)时高。
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。