MySQL的优化

优化

负向查询不能使用索引

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

还有 not in/not exists都不是好习惯

select name from order where status not in (0,1);

可以优化为in查询：

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

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

如:

select name from user where name like '%xxx'

非前导则可以:

select name from user where name like 'xxx%'

MyISAM 存储引擎也可以做全文检索，不过只支持英文，相信现在应该也没人使用它了。建议使用solr 、es 等第三方开始工具实现全文检索功能。

数据区分不明显的不建议创建索引

如 user 表中的性别字段，可以明显区分的才建议创建索引，如身份证等字段。

select * from user where sex=1

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

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

字段的默认值不要为 null

这样会带来和预期不一致的查询结果，建议参考注意事项。

在属性上进行计算不能命中索引

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

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

select * from order where date < = CURDATE()

复合索引最左前缀

用户中心建立了(login_name, passwd)的复合索引

select * from user where login_name=? and passwd=?

select * from user where passwd=? and login_name=?

但是使用

select * from user where passwd=?

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

如果明确知道只有一条记录返回

select name from user where username='xxxx' limit 1

提高效率，可以让数据库停止游标移动，停止全表扫描。

强制类型转换会全表扫描

select * from user where phone=13800001234

这样虽然可以查出数据，但会导致索引失效。

需要修改为

select * from user where phone='13800001234'

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

注意事项

不要听信你看到的关于优化的“绝对真理”，包括本文所讨论的内容，而应该是在实际的业务场景下通过测试来验证你关于执行计划以及响应时间的假设。

• 单条查询最后添加 LIMIT 1，停止全表扫描。

• 对于char(4) 或者vachar(4)，无论是中文还是英文都是存储四个字符，注意是字符而不是字节。

• 如果一个字段未int类型，此类型只有0、1两个状态，需要为此建立索引吗？过度索引，影响更新速度，必须在唯一性较高的字段上建立非聚集索引。

• 在创建表的时候如果在业务中能保证非null的字段，建议明确标示not null 因为mysql中对null需要特殊的标示。使用not null 字段更节省空间。对接下来的索引构建也有好处。

• count() 和count(name) name 代表某个字段，可以为NULL。在mysql中count()会把null统计进去、而count(name) 不会。如果统计的字段中含有null，这个两个统计的结果是不同的。

• 在sql语句等号左边用函数，会使该查询在该字段无法使用索引。如LENGTH(str) 函数。

• 索引也是需要存储到物理空间的，经常增删的表不适合建太多的索引，因为索引的维护会很耗时间。一张表最多建立15个索引。索引的长度越小越好，索引是有序的。如果查询Max（）之类用索引的话，连表都不用查询了，快得飞起。

• mysql中null不参与比较运算，name <>'小米' 得出的结果中不包含 name=null的情况。在业务不能保证某字段是否为null的情况，写代码的时候需要注意null的坑。保证取得的数据是对而全，然后再考虑查询速率问题。

• MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

• 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。

• UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。

• 通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

• TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。

• schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

• 大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。