MySQL tips

MySQL tips

一些日常接触到的MySQL优化tips，比较散乱。

适时地使用union代替or

    假设有一个用户表，对于一句很简单的查询语句：

SELECT * FROM `user` WHERE name = "小明" OR age = 25;

    假设name与age字段均有单列索引，容易想到的是，MySQL应该会分别走两次索引，并将其结合起来，EXPLAIN也是如此，大多数时候MySQL会进行优化，我们可能会看到EXPLAIN的结果中有Using union或Using soft union，这是MySQL针对OR做了隐性的优化，但当SQL复杂或数据极端情况下，这一语句极容易变成全表扫描，偶尔使用联合索引可能解决问题，更多情况则是MySQL“昏了头”，即使OR条件均涉及数据条数不多，依旧没能在查询语句中使用索引，此时应调整为UNION语句(可以权衡一下重复及顺序是否有影响，可以使用更快的UNION ALL)：

SELECT * FROM `user` WHERE name = "小明" 
UNION
SELECT * FROM `user` WHERE age = 25;

    这一语句相当于走两次查询，只要结果数据不多，就都会使用索引了。

    需要注意的是，最好在意识到出现问题再调整为union，不要无脑使用union，日常情况下可能会比OR慢并且语句更为复杂可能不易维护。但是上面的情况中union要优于联合索引。

合理配置与使用联合索引

    一个常识是：一句SQL只能使用一个索引，这显而易见，一旦条件同时包含两个字段，他只能走其中一个字段的索引或是联合索引（上面OR或UNION算是特殊情况因为实际也是拆分成了两个SQL），如果单列区分度不高或是涉及数据集过大，联合索引便很有必要，同时注意联合索引的顺序，太多的索引会使MySQL插入和更新性能大打折扣。

MVCC和锁不是互斥的

     MVCC主要是用于读取，使用了 MVCC的SELECT语句为快照读，可以不必加锁，从而防止阻塞查询。而使用了加锁语句(LOCK IN SHARE MODE/FOR UPDATE)的查询和数据更新修改等操作依旧是依赖锁的， MVCC是为了解决查询被阻塞，与当前读中更新和主动上锁使用的锁并不互斥，准确地说，MySQL中实现事务隔离级别使用的是锁+ MVCC。

警惕批量更新带来的死锁

    在使用批量更新时多多考虑这个SQL可能对数据库带来的影响，在 MVCC的事务解决方案下，虽然已经能避免一些事务的SQL阻塞其他更新和查询SQL，但是加锁的操作仍旧难以避免，对于每一个批量更新，我们都要作出权衡，有些批量更新可能操作了较多的列，同时存在多个这样的SQL时可能造成死锁的，即使他们并不操作多张表，也并不在事务中，但是因为加锁顺序可能不一致。譬如两个SQL的条件筛选是不同的字段，由于UPDATE语句是逐条加锁，这种因为顺序不一致导致的死锁会经常发生，尤其是在WHERE条件未命中的情况下或是未使用索引，所涉及的数据涵盖了全表，这时将一方调整为单条update即可（按主键id）。

使用正确的数据格式

    查询时，字符串字段就要用字符串搜索，数字类型就要用数字搜索，使用字符串搜索数字字段会进行隐性的数据类型转换，所以影响还小。但是使用数字搜索字符串则一般会对表中的全部数据做数据转换，很有可能变为全表扫描，所以切记使用正确的数据类型进行查询。

最左前缀联合索引生效条件

    我们都知道联合索引必须从最左进行匹配，但是某些情况索引会匹配失败，例如LIKE %在前的语句会导致后面索引一并失效，其实范围查询也相同。

    譬如有一联合索引(a,b,c) 执行如下的SQL语句，因为b为范围查询，索引a和b的部分会生效，而c无效：

SELECT * FROM table WHERE a = x AND b < 2 AND c = x;