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