mysql高级使用和技巧

文章首发于公众号:松花皮蛋的黑板报
作者就职于京东,在稳定性保障、敏捷开发、高级JAVA、微服务架构有深入的理解

一、普通索引和唯一索引
查询上来说，普通索引查找到满足条件的记录后会接着查找下一个记录(innodb的数据是按页读写的)，判断是否满足。然而唯一索引是查询到了就立即返回了。所以如果你明确知道只有一条结果则应该加上limit 1

更新上来说，普通索引会用到charge buffer优化，将更新操作记录在charge buffer中，不需要从磁盘中读取数据然后再更新，当下次查询该数据页时再读入内存然后执行merge相关操作，更新原数据。

二、前缀索引
查询上来说，前缀索引可能会导致在索引树上命中率变高但是原数据命中并不一定高，造成了一定的查询浪费。另外对于索引上的信息足够满足查询条件的情况下，前缀索引会多一次回表操作，整体索引则是直接返回(也就是覆盖索引)。

但是如果提高数据的区分度，比如倒序存储、hash处理后存储等，使用前缀索引存储空间更小，查询次数也不会太差，收益可能会更高。

三、联合索引
对于联合索引来说，遵守最左前缀原则，也就是说如果只有idx-union(type,time,value)联合索引，单纯的type或者type and time作来查询条件也会命中这条索引，但是单纯value作为查询条件则无法命中。另外如果存在范围查询比如between等也会导致无法命中

四、收缩表空间
当需要收缩表空间时，如果只是delete数据，表文件大小是不变的，会被mysql标记为可复用的空间，需要通过alter重建表才能释放。当然如果是要删除全部数据的话，首选应该是Truncate操作。

五、count(*)操作
InnoDb是索引组织表，主键索引树的叶子节点存的是整行数据，而普通索引树的叶子节点是主键值(需要先查找k索引树得到ID，然后再到ID索引树查找，也就是回表)，不管是优化器查询哪个索引树或者不使用索引，都需要将所有数据查出来然后累加返回，所以不推荐在innodb引擎的数据库中频繁执行count(*)操作。

六、显示随机信息
如果使用order by rand()实现，则需要在临时表上进行rowid(有主键则是主键没有则是系统生成标识行的rowid)排序操作,整体过程涉及全表扫描然后将数据放到内存临时表再生成sort_buffer排序再从内存临时表中取数据。如果sort_buffer_size无法存储数据，则需要使用磁盘文件进行分块存储然后再归并排序。

正确的方式应该是：

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； // 在应用代码里面取 Y1、Y2、Y3 值，拼出 SQL 后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；

七、where条件上不要使用函数
对索引字段做函数操作，可能会破坏索引值的有序性，因为b+树中的同一层兄弟节点是有序的

八、where条件不要使用类型转换
当字符串和数字做比较时，会将字符串转换成数字，会触发CAST等函数操作，触发上一条规则

九、数据库用错索引时可以强制force index
我们知道Mysql结合扫描行数、是否使用临时表、是否需要排序等综合考虑选择索引，当然就会出现用错索引的情况。平时我们explain sql时显示的预估扫描行数rows是mysql通过数据采样，选择这个索引其中n个数据页，统计这些页面的不同值，得到平均值，然后乘以这个索引的页面数，得到基数，也就是区分度。另外还有如果回表代价过大，也可能会选错索引。

十、join操作
join操作的过程是先遍历表t1，然后根据从表t1取中的数据到表t2中查找满足条件的记录,也就是说驱动表是走全表扫描，而被驱动表是走树查找(index nested-loop join)。那么理所当然选择小表(过滤后)作为驱动表效果更好。

如果被驱动表没有可用的索引时，join算法会变为表t1和表t2都走全表扫描放入内存中查找满足符合的记录(block nested-loop join)。

但是如果驱动表分段，那么被驱动表就被多次读，那么就有可能把大部分热点数据都淘汰掉了，导致”buffer poll hit rate”命中率低，其他请求需要读磁盘，这种情况就非常不推荐使用join操作了。

接下来来说说常用的left joinright joininner join,比如这条语句Select * from t1 left join t2，可以看出是以t1作为驱动表，如果不能保证t1比t2表小尽量使用inner join，优化器会自动选择较好的那个驱动表。

十一、group by使用磁盘临时表
group by语句是统计不同的值出现的个数，但是每个原数据的操作结果可能都是无序的，那么就需要中间存储表-临时表记录过程了。但是如果数据量过大，会出现先放到内存临时表，插入一部分数据后，发现不够用了再转成磁盘临时表，这种情况我们可以加上sql_big_result提示优化器，直接使用磁盘文件。另外多说一点，如果结果不要求排序，最好使用order by null跳过内存临时表的排序。

十二、分库分表
查询表数据大小，合理分表分库

SELECT CONCAT(table_schema,’.’,table_name) AS ‘Table Name’, CONCAT(ROUND(table_rows/1000000,2),’M’) AS ‘Number of Rows’, CONCAT(ROUND(data_length/(102410241024),2),’G’) AS ‘Data Size’, CONCAT(ROUND(index_length/(102410241024),2),’G’) AS ‘Index Size’ , CONCAT(ROUND((data_length+index_length)/(102410241024),2),’G’) AS’Total’FROM information_schema.TABLES WHERE table_schema LIKE ‘库名’;

十三、乐观锁
乐观锁假设数据一般情况下不会冲突，在数据提交更新的时候才会做冲突检测，常用version版本号的方法实现

select id,version,… from db where id=#{id}; update db set version=version+1,… where id=#{id} and version=#{version};

而相对的悲观锁则是在整体数据处理过程中都加锁，依赖数据库的事务，性能较差

MySQL select…for update的Row Lock与Table Lock

十四、MySQL中的行锁
MySQL的InnoDb引擎是行级锁，需要注意的是，这不是对记录进行锁定，而是对索引进行锁定。在UPDATEDELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相依的健值，也就是所谓的next-key locking,如语句update liangsonghua_me_blog_post set update_time = now() where id > 10000会锁定所有主键大于等于1000的所有记录。另外我们知道通过非主键查询回表时，会先通过二级索引也就是非簇索引查找相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行。实际上当非簇索引(non-cluster index)记录被锁定时，相关的的簇索引记录也需要被锁定才能完成相应的操作

文章来源：www.liangsonghua.me
作者介绍：京东资深工程师-梁松华，长期关注稳定性保障、敏捷开发、JAVA高级、微服务架构