mysql索引注意事项

1.模糊查询前导不会走索引

select id,user_name,price_code from user_activity_info where user_name like '%zhang';

如果非要使用前导索引的话可以借助Apache的Lucence索引工具

2.字段默认值不要设置成null

如果可以请设置为 not null

3.不要在SQL中对字段进行计算，这样会使索引失效

select user_name,price_code,create_time from user_activity_info where from_unixtime(create_time) > now();

4.不要对字段进行隐式转换

select user_name,price_code,create_time from user_activity_info where user_name=12345;

user_name是varchar类型，但是检索匹配值却是int型12345，这样会使user_name的索引失效

5.复合索引类的第一个字段列必须出现在条件中，复合索引中区分度高的要放在第一列

select id,user_name,price_code,create_time from user_activity_info where user_name="simons";

price_code和user_name是联合索引，但是where条件中只有user_name，且user_name是联合索引的第二列，所以这个SQL不会命中索引

6.筛选后的数据只有一条也得加上limit 1

select id,user_name,price_code,create_time from user_activity_info where id='256' limit 1;

这样也可以提高SQL效率，减少数据库游标移动

7.不要在低基数列上建索引，如性别字段，"墙裂"建议在区分度高的字段上建索引

例如性别字段，值只有男或女，区分度低，mysql会认为直接扫描全表数据比扫描索引快一些，所以不会使用索引

8.负向查询不会使用索引，会导致全表扫描

select id,user_name,price_code,create_time from user_activity_info where id not in('1','5');

9.join查询列字段最好相同，否则可能导致发生隐式转换，不命中索引

select a.user_name,b.price_code,create_time from user_activity_info a join reward_info b on a.price_code=b.price_code;

user_activity_info和reward_info表中的price_code字段类型要一致

10.通过延迟关联（覆盖索引查询返回需要的主键，再根据主键关联原表获取所需数据）的方式

通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据,而不是通过二级索引获取主键再通过主键去遍历数据页。例如存在这么一条SQL：

explain SELECT id,actid,kbid,phaseid,security_code,code,validdays,enddate,status,vip_type,createtime 
FROM suning_act_card where status ='0' AND createtime >='2014-05-29' ORDER BY id asc LIMIT 2638327 ,20;

表数据量为4千万左右，根据上面的原则，可以改写为：

explain SELECT a.* FROM suning_act_card a, (select id from suning_act_card where status ='0' AND createtime >='2014-05-29'
  ORDER BY id asc LIMIT 2638327 ,20 ) b where a.id=b.id;

测试结果是执行速度提升了12%左右，且随着表数据量的增加提升效率越来越明显。看官可亲测下！！！

---

引申阅读：mysql开发规范