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与索引优化 | 架构师之路
1832
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
