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值。
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!!!!)
操作数据库前先备份!
学会使用新能分析工具
show profile;
mysqlsla;
mysqldumpslow;
explain;
show slow log;
show processlist;
show query_response_time(percona)
MySQL的or/in/union与索引优化 | 架构师之路