SQL语句优化

1. 禁止使用SELECT *，只获取必要的字段，需要显示说明列属性

解读：

a）读取不需要的列会增加CPU、IO、NET消耗

b）不能有效的利用覆盖索引

c）使用SELECT *容易在增加或者删除字段后出现程序BUG

2. 禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性

解读：容易在增加或者删除字段后出现程序BUG

3. 禁止使用属性隐式转换

解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，猜猜为什么？（这个线上问题不止出现过一次）

4. 禁止在WHERE条件的属性上使用函数或者表达式，在属性上进行计算不能命中索引

解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描

正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

例如：

select * from order where YEAR(date) < = '2017'

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

select * from order where date < = CURDATE()

或者：

select * from order where date < = '2017-01-01'

5. 禁止负向查询，以及%开头的模糊查询。

解读：

a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描

b）%开头的模糊查询，会导致全表扫描

6. 禁止大表使用JOIN查询，禁止大表使用子查询

解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能

7. 禁止使用OR条件，必须改为IN查询

解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？

8. 应用程序必须捕获SQL异常，并有相应处理

9. 负向条件查询不能使用索引

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

not in/not exists都不是好习惯

可以优化为in查询：

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

10. 前导模糊查询不能使用索引

select * from order where desc like '%XX'

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

select * from order where desc like 'XX%'

11. 数据区分度不大的字段不宜使用索引

select * from user where sex=1

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

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

12. limit高效分页

limit越大，效率越低

select id from t limit 10000, 10;

应该改为 =>

select id from t where id > 10000 limit 10;

13. 如果业务大部分是单条查询，使用Hash索引性能更好，例如用户中心

select * from user where uid=?
select * from user where login_name=?

原因：

B-Tree索引的时间复杂度是O(log(n))

Hash索引的时间复杂度是O(1)

14. 允许为null的列，查询有潜在大坑

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

select * from user where name != 'shenjian'

如果name允许为null，索引不存储null值，结果集中不会包含这些记录。

所以，请使用not null约束以及默认值。

15. 复合索引最左前缀，并不是指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=?

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

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

select * from user where login_name=?

可以优化为：

select * from user where login_name=? limit 1

原因：

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

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

select * from order where date < = CURDATE()

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

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

$res = mysql_query(

'select * from order where date < = $curDate');

原因：

释放了数据库的CPU

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

18. 强制类型转换会全表扫描

select * from user where phone=13800001234

你以为会命中phone索引么？大错特错了，这个语句究竟要怎么改？

正确查询：

select * from user where phone='13800001234'

加上引号，保持类型一直，避免隐式类型转换

19. union all 肯定是能够命中索引的，简单的in能够命中索引，对于or，新版的MySQL能够命中索引，对于!=，负向查询肯定不能命中索引

20. 分批批量更新

21. 使用union all替代union，union有去重开销

22. 务必请使用“同类型”进行比较，否则可能全表扫面

23. ORDER BY 后面的列默认按升序排序，如果需要指定列的排序规则，需要每个列单独指定。例如：如果想在多个列上进行降序，必须对每一列

指定DESC关键字。ORDER BY prode_pice DESC,prod_name DESC;

24. 不等于，不会返回符合条件的null值。无论有无索引，都不返回值为null的记录。

25. 在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT 进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。

有的DBMS允许数据库管理员施加约束，防止执行不带WHERE子句的UPDATE或DELETE语句。如果所采用的DBMS支持这个特性，应该使用它。

26. MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1).数据库名与表名是严格区分大小写的；

2).表的别名是严格区分大小写的；

3).列名与列的别名在所有的情况下均是忽略大小写的；

4).变量名也是严格区分大小写的；

列名与列的别名在所有的情况下均是忽略大小写的。

表的别名是区分大小写的。下面的查询将不能工作，因为它用 a 和 A 引用别名：

mysql> SELECT col_name FROM tbl_name AS a WHERE a.col_name = 1 OR A.col_name = 2;

27. Mysql默认的字符检索策略：utf8_general_ci，表示不区分大小写；utf8_general_cs表示区分大小写，utf8_bin表示二进制比较，同样也区分大小写。

（注意：在Mysql5.6.10版本中，不支持utf8_genral_cs！！！！）

28. SQL执行顺序：

完整的SQL语句：

select distinct 
        <select_list>
from
    <left_table><join_type>
join <right_table> on <join_condition>
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <limit number>

SQL执行顺序

from <left_table><join_type>
on <join_condition>
<join_type> join <right_table>
where <where_condition>
group by <group_by_list>
having <having_condition>
select
distinct <select_list>
order by <order_by_condition>
limit <limit_number>

29. Explain说明：

explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，

分析查询语句或者表结构的性能瓶颈。

作用

1）表的读取顺序

2）数据读取操作的操作类型

3）哪些索引可以使用

4）哪些索引被实际使用

5）表之间的引用

6）每张表有多少行被优化器查询

执行计划包含的信息如下：

输出字段	描述
id	查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序两种情况 id相同，执行顺序从上往下 id不同，id值越大，优先级越高，越先执行
select_type	查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询 1、simple ——简单的select查询，查询中不包含子查询或者UNION 2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记 3、subquery——在select或where列表中包含了子查询 4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6、union result:UNION 的结果
table	输出的行所引用的表
type	显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序 1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。 2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量 3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描 4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体 5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描 6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。 7、all：遍历全表以找到匹配的行注意:一般保证查询至少达到range级别，最好能达到ref。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。
key_len	表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
ref	显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
rows	根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra	包含不适合在其他列中显示，但是十分重要的额外信息 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false，不能用来获取任何元组 7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。