SQL 优化的几种方式

1.SQL语句的优化

1.1 SQL语句中In包含的值不应过多。

如 select id from t where num in (1,2,3)对于连续的值，应尽量使用Between，避免使用In。如select id from t where num between 1 and 3。

1.2 SELECT语句必须指明字段名称。

禁止使用*进行查询。
如select * from t需要修改为指明字段的查询select id, name from t。

1.3 在只查询一条数据的时候，使用Limit 1。

1.4 Where子句编写不当，会造成索引失效。

以下行为都会造成引擎放弃使用索引而进行全表扫描。

  1. 在Where子句中使用!=或<>操作符。建议使用>=或<=
  2. 在where子句中对字段进行null值判断。如select id from user where name = null（null判断中会根据null和非null的数量来进行判断是否走索引）
  3. 在where子句中使用or来连接条件。如select id from user where num = 10 or num =20，应修改为 select id from user where num = 10 union all select id from user where num = 20
  4. 在where子句中对字段进行表达式判断。如select id from user where num/2 = 100 ，应修改为select id from user where num = 100*2。
  5. 在where子句中对字段进行函数操作，如select id from user where substring(name,1,3) = 'abc'。
  6. 在where子句中的=的左边进行函数、算数运行或其它表达式运算。

1.5 对于联合索引来说，要遵守最左前缀法则。

例如组合索引(id,name,sex) 使用的时候,可以id 或者id,name . 禁止直接name,或者sex.会导致联合索引失败
注意: id, name,sex 这三个字段填写顺序不会有影响, mysql会自动优化成最左匹配的顺序.
前三条sql都能命中索引,中间两条由于不符合最左匹配原则,索引失效.
最后一条sql 由于有最左索引id 所以索引部分成功,部分失效. id字段索引使用成功.

1
2
3
4
5
6
explain select * from  `user_test` where uid=10  ; 
explain select * from  `user_test` where uid=10 and name='张三';
explain select * from  `user_test` where uid=10 and name='张三' and phone='13527748096';
explain select * from  `user_test` where name='张三' and phone='13527748096';
explain select * from  `user_test` where name='张三';
explain select * from  `user_test` where uid=10 and phone='13527748096';

1.6 尽量使用inner join，避免left join。

如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

1.7 不建议使用%前缀模糊查询。

如like "%name"或者like "%name%"，会造成索引失效，应修改为like "name%"。

1.8 字符串类型的字段 查询的时候如果不加引号’’ ,会导致自动进行隐式转换,然后索引失效

1.9 order by 排序优化 (排序时,使用有索引的字段进行排序)

1.10 count 优化 速度:count(*)>count(1)>count(字段)

inndb引擎的使用如下 (MyIASM默认存了数据总数,所以效率最高)
1:count(字段):遍历整张表 会把每一行的字段值取出来,然后返回
2:count(1): 便利整张表,但不取值,对于返回的数据,放入1进去.然后累加
3:count(*):inndb引擎,特意做了优化,不会取出值,直接服务层进行累加