mysql架构设计与SQL优化

mysql架构设计

索引

mysql的索引底层是B+Tree或哈希表，innodb是B+tree

为什么要使用B+tree作为索引

二叉树的缺点

根据二分查找的思想，二叉树可以转变为二叉查找树

可是在极端条件下他会退化为链表、直接变成线性查找，效率没有任何提升

于是就有了二叉平衡树

左右子树高度不会超过1

但是他还是太慢了，二叉树会导致层数过高，于是就有了专门为数据库而生的b树

b-树

虽然b-tree很好地利用二分查找树的思想大幅度减少查找次数，但是他的效率还是很低

比如我们要查询10

• 读取根节点数据（1次IO）

• 判断10<17进入左子树（两次IO）

• 判断8<10<12 读取p2 （三次IO）

我们发现我们的IO次数没办法缩减了，于是就减少节点存储的大小（我一次读取1kb肯定比一次读取4kb快于是就有了B+tree)

使用B+tree的好处

• 减少IO

• 更加稳定

• 范围查询非常快（因为B+tree叶子节点都是通过指针连接起来的，范围扫描就不用走父节点）

• 数据全部都在叶节点，父节点存储的仅仅是叶子节点的最小值或者最大值，以及指向该节点的指针（大大减少磁盘大小）

由于mysql的索引也是存储在硬盘当中的，设计者要考虑时间复杂度的同时还要考虑IO，而IO是非常慢的，所以要减少IO次数，而B+tree的高度一般为2-4层足够满足千万数据的存储，如图所示，我们只需要进行3次IO操作就可以找到数据了

回表

在innodb中聚簇索引的叶子节点存放的是索引值和想要的数据，而二级索引存放的是索引值和主键索引的指针所以如果使用二级索引查询会导致回表第一次根据二级索引获得主键索引的指针，再根据这个值搜索主键索引的B+tree（等于说查询了两颗B+tree）

索引的设计原则

• 针对where、orderBy、gourpBy、去设计索引

• 联合索引需包含where、order by 、group by的字段（遵循最左原则）

• 建立索引需选择基数较大的字段（值比较多的字段）

• 经常查询的字段需要建立索引，经常修改的字段不要建立索引

myslq执行计划

 -- 使用explain查看mysql执行计划
 explain select * from comment where  id =10

• key：key为PRIMARY代表使用了主键索引

• type: system > const > eq_ref > ref* > range > index > all all就代表了全表扫描

• ref : 哪些列或常量被用于查找索引列上的值

• rows: 估算所需的记录需要读取的行数

• Filtered: 结果行数占读取行数的百分比，越大越好

SQL优化

• 不要使用select *

• 避免in和not in，会导致引擎走全表扫描

• 避免使用or，引擎会放弃索引走全表

• 避免进行null的判断，会走全表

• where条件里等号的左侧进行表达式、函数操作会走全表

• 索引列作为条件时不要使用 <> 和 !=

• 多表关联查询需小表在前，大表在后并且最好不要超过三张表进行join

• 少使用聚合函数

• 使用覆盖索引进行查询避免回表（索引的字段覆盖了查询条件设计的字段）

慢查询分析

原因：

• 硬件问题（内存小，网络慢...）

• 没有索引，索引失效

• 数据过多（一张表的数据超过2500万条就要考虑分库分表）

排查：

查看执行计划

因为rows行数太多，如果使用age索引，则需要在走一次主键索引，mysql的调优器分析之后，还不如全表扫描划算于是就走了全表

主键的设计

核心业务的主键是全局唯一且单调递增的单调递增插入的时候不影响性能（也就是说核心业务使用UUID）

非核心业务：使用自增id（非核心业务使用自增ID）

推荐的主键设计：UUID（@uuid_to_bin()）

为什么不使用自增id呢

• 可靠性不高

• 不安全(自增id不是随机的，很容易猜到下一行的id是多少被不法分子进行利用)

• 局部唯一性（分库分表可能导致ID重复）

UUID

UUID通过MAC地址、时间戳、随机数等等进行生成固定大小36个字节的UUID

优点

• 保证了数据的安全性

• 在分库分表中保证了全局唯一性

缺点

• 因为UUID通过MAC地址生成，会暴露MAC地址

• 因为是无序的插入的数据量越多性能越差

• 占用36个字节，bigint才8个字节

如何跟mysql进行交互

跟mysql进行连接

通过mysql驱动跟mysql进行连接，因为mysql是TCP/IP连接所以需要经过三次握手，完成连接后连接器会校验你的账号密码，如果正确就会存储下来的你的权限，之后的任何操作都会进行一次权限判断，所以即使中途管理员修改了你的权限，你在这次连接中的权限都不会改变，于是这样就跟mysql连接好了

数据库连接池

因为mysql连接是基于TCP/IP协议的，所以每次跟mysql进行交互(比如select) 都要进行一次三次握手(耗时)，于是就要使用连接池(比如druid)将数据库连接对象放在连接池用户直接在连接池拿对象，减少用户跟数据库的连接次数

mysql架构设计

通过连接器查询了缓存，缓存没有则进入解析器，解析语法，如果错误就抛出异常，在预处理器中比如：

ps：这一块缓存很鸡肋,比如我存了很多select的数据，但是我update后缓存里又要清空，所以在8.0就把这块缓存删除了

 select * from user -- 没有user表，在预处理器prepare会报错

优化器能做什么：

• 提前终止查询（limit）

• 覆盖索引扫描

• 子查询优化等等

在优化器中会将sql的执行方案确定下来，比如像之前说的一样，因为读取的行太多，又是二级索引,于是优化器会走回表（是不是很智能）不过它利用的是存储引擎提供的统计信息来计算成本，innoDB的统计信息是抽样的可能不准确

之后执行器根据执行计划再调用InnoDB接口执行

InnoDB架构设计

BufferPoll架构

BufferPoll默认128MB，会缓存很多数据,也就是说,执行器调用InnoDB接口后，innodb会先查询BUffer Pool，缓冲池没有 才去查询磁盘，还有一部分是日志的缓冲

而当修改数据时，会先修改BufferPoll的数据所在的页然后将其设置为脏页后台程序再将脏页写入磁盘

脏页的刷新