MySQL的一些相关问题，B+树高度，分库分表，以金字塔形调优

到这篇为止，丁奇大佬的专栏《MySQL实战45讲》已经看完了，再次感谢前人的经验总结。之后会在这篇文章持续更新自己在实际中碰到，学到的MySQL其他相关知识。

---

1.join操作驱动表的选择

1. 使用join时MySQL优化器会自动选择使用那张表作为驱动表；
2. 使用left join时如果没有where判断那么左侧的就是驱动表，如果存在where判断那么MySQL经过优化器分析会选择最合适的表作为驱动表（不一定时用户指定的左侧的表）。

2.group by与distinct执行效率的问题

group by默认是将数据进行分组，然后进行排序，如果不需要排序就要在sql语句中加order by null。

group by如果不加**count()**那么相同字段的行只会返回一次，效率跟distinct一样；如果加了count()，那么group by还要有一个字段统计相同分组的行出现的次数，效率比distinct低。group by标准写法是：

select a, count(*) from t group by a order by null; 

3.自增主键问题

创建表时尽量自定义自增主键，对于自定义的自增主键，当主键值达到上限时会报错。而如果没有自定义自增主键，InnoDB会默认创建一个不可见的主键，当主键值达上限时会再次从初始值开始获取主键，覆盖已有的记录数据行，造成数据丢失。

4.Xid

每一条redo log和bin log会绑定一个对应的Xid，表示他们指向的是哪一个事务，用来确保crash以后数据可以安全恢复。结合下图，每次crash后，redo log会拿当前未提交的事务的Xid在bin log中查询是否有该Xid记录，有就继续在redo log执行提交，否则redo log执行事务回滚。

补充：B+树高度计算
为什么官方规定MySQLB+树一般为2，3层：
默认数据存放在叶子节点，每个节点上存放一张16kb的内存页，对于非叶子节点，每个节点上的页只存放字段key和指向下一个节点的指针，一个key和一个point大概占用14子节，所以非叶子节点存放16kb/14个key，根节点再加上第一层节点就可以得到(16kb/14) *(16kb/14)个根节点，每个根节点上一页还是16kb，假设一行记录占用100字节，那么一页即一个根节点有160条记录，乘上一共有 (16kb/14) *(16kb/14)个根节点，大概可以存放**(16kb/14) (16kb/14)160上亿条数据，一般一张表建议存放最多1000万条左右的记录。

补充：分库分表

• 分库：主要是为了缓解单主库的高写请求压力（从节点只能解决读请求），可以按照不同的服务类别进行分库，将用户类信息放一个库，商品类信息放一个库等；
• 分表：
  水平分：主要解决单张表记录行过多，如上面说的，建议单表最多不要超过1000万行记录；
  垂直分：主要解决冷热数据问题，可以将热数据字段放到一张表，该表分配较小的空间。大量的冷数据放到另一张表，分配较大的空间。

补充：MySQL性能优化的着手顺序：

应该按照以下优先级：
1. SQL语句以及索引
2. 数据库表结构设计
3. MySQL实例配置优化
4. 服务器硬件设备优化

• 如何排查sql语句执行情况：先开启慢查询，定位到执行慢的一些sql语句，然后在使用explain语句的具体执行情况，针对性的进行优化；

• 数据库表结构设计：表的字段类型+分库分表；

• MySQL配置文件：缓存池大小（一般推荐为总内存的75%），脏页刷新设置，bin log格式选择等；

• 设备硬件：CPU、内存、磁盘空间；