Mysql答疑

周一DBA帮我们梳理业务中的慢查询，解答了我对mysql的一些疑惑。

sql执行顺序？

SELECT DISTINCT
    < select_list >
FROM
    < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
    < where_condition >
GROUP BY
    < group_by_list >
HAVING
    < having_condition >
ORDER BY
    < order_by_condition >
LIMIT < limit_number >

当我们输入类似上面sql时，mysql执行顺序会是：

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT 
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

“FROM”从哪张表开始，“ON”根据哪个字段，“JOIN”关联其他表，“WHERE”设置筛选条件，“GROUP BY”设定根据哪些条件进行分组计算，“HAVING”对分组计算后的数据进行筛选，“SELECT”选择输出哪些列，“DISTINCT”对结果去重，“ORDER BY”对结果进行排序，“LIMIT”限定输出结果数量。

了解了sql执行顺序，对我们优化SQL有很好的指导意义，比如当我们要关联查询多张表，那么ON所在列加上索引无疑是更好的选择，如果我们想获取第100万到第100万零10条数据，使用“SELECT * FROM XXX WHERE id > 100万 LIMIT 0, 10”无疑要比“SELECT * FROM XXX LIMIT 100万, 10”要好的多。

单表数据量多大才好？

其实一直有一个疑问，单表数据量到底多大才好呢，是1000万还是1亿，请教DBA之后明白，其实这个是因业务而异的。
Mysql单表上限非常大，单表数据量可以达到2T，所以不存在Mysql本身的限制。
如果表里面存放的是日志记录，那么可以不限制，放1亿甚至更多数据。
如果表里面存放的是用户需要频繁进行查询修改的数据，那么就需要考虑数据量对于查询修改的影响，查询有没有走索引，频繁修改数据间接导致索引频繁修改。
所以单表数据量大小本质上是一个取舍问题，根据具体业务来定。

如果SELECT语句没有走索引，会有什么问题？

“SELECT X FROM A WHERE c1=x”，如果没有A表中c1列添加索引，那么当执行查询语句的时候，会给A表添加一个S锁(Share Lock)，那么在这条sql执行过程中，如果又来了一条SELECT查询语句，那么会共享S锁；但是如果来了一条“UPDATE A SET x WHERE c1=x”，那么问题来了，需要给A表添加一个X锁(Exclusive Lock)，但是因为有S锁，需要等待S锁释放之后才能添加X锁。
去年就出现过同类型事故，因为没有索引导致加了表锁，大量sql被阻塞。
正确的方式是给“c1”添加索引，那么S锁，X锁都会添加到索引上，而不是表上，避免表锁。

如何查看sql执行计划？

EXPLAIN XXX；
具体可参照

聚簇索引与非聚簇索引区别？

简单的来说，主键就是聚簇索引，B+树为索引结构，叶子结点存储的是索引值和数据本身。
非聚簇索引就是二级索引，叶子结点存储的是索引所在的列字段以及主键值。

为什么主键id要保持自增？

主键id是聚簇索引，底层实现是B+树，如果每次新增id随机，那么就需要对树进行调节，来保持树的平衡；而id自增就不会有这个问题。

事务是如何提交的？

如果想搞清楚这个问题，就需要知道redo undo日志；
具体可参照