#MySQL

提交语句到执行查询的过程(用户侧) 连接器 一切要从连接开始, MySQL使用TCP连接。 mysql -h$ip -P$port -u$user -p"password" 服务端经TCP握手,建立连接,鉴定用户的身份:密码+权限都在这个过程完成校验。 (多久处理idle的连接? 在MySQL对应参数是 wait_timeout, 默认值 28800 秒, 8小时) 提交查询命令, 之前总觉得MySQL有个cache机制,但后来了解到这个查询缓存功能有些鸡肋。 命中率低,失效情况普遍(前后两个查询,变动一个字符都会视作不同的查询)。 在新版8.0已经删除了cache。 分析器 parser & analyzer 接下来MySQL服务端要将客户端的提交查询进行解析,是否合法的查询。 词法分析 优化器 optimizor 执行器 executor ( Hive将query转换成MapReduce的过程,也会经parser & analyzer & optimze 的过程) redo log , binlog 数据持久化,一般有两个思路。 edit Log, 记录所有的指令操作, 当重启时通过replay log 恢复; image(snapshot), 滚动打快照,当重启时通过load快照恢复现场。 我们自然希望edit log 越小越好(恢复操作快), image离目前越近越好(恢复操作快)。 所以editLog和image需要经常合并, 在Hadoop NameNode中,它有个辅助节点称为 Secondary NameNode就是完成的这项工作。 而在MySQL中,采用的是WAL(Write-Ahead Logging),先写日志,再写磁盘。 redo log 的 容量和设计: 几块文件(环形队列), 每个文件写到末尾从到再写。 参考: https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design/ 事务隔离 ACID(我的记忆口决: 毛利兰的朋友—— 园子 一直要抹隔离霜, 持久防晒) SQL的事务隔离级别: 读未提交 read uncommitted A事务查询时,能查到另一个事务B 还没commit的变更 读已提交 read committed 较上面, 只有B事务提交了,A事务才能查到B的变更 可重复读 repeatable read A事务执行过程中看到数据,和启动前看到的数据一致 串行化 serialized 最好理解, 所有操作都加个锁,前面没完事,后面就等着。 用 transaction_isolation 查看当前的隔离级别。 MVCC 与 undo log 每行数据是有多个版本的,每次事务更新,修改了这行数据,都会生成一个新的数据版本。 每个数据版本有一列 transaction id 就对应着修改它的事务id, 标记为 row trx_id 。 如果这行数据被改了多次,就会有多个版本,对应着不同的 row trx_id。 MySQL中的另一个日志: undo log 事务的一致性视图(read-view) InnoDB为每个事务构造了一个数组,用来保存事务启动时,当前正在活跃的所有事务ID,活跃(启动了但未提交) 数组里事务ID最小值记为 低水位,当前系统已经创建的事务ID最大值+1 记为高水位。 这个视图数组和高水位,组成了当前这个事务的一致性视图。 用之来判定数据的可见性: 如果是已提交事务, row trx_id 落在低水位以下, 这个数据看得见; 如果是未提交事务, 分2类: - row trx_id 在数组中,表示这个版本没提交, 不可见; - row trx_id 不在数组中,表示这个版本由已经提交的事务生成,可见 如果落在未开始事务(高水位以后),这个版本是由将来启动事务生成的, 不可见 索引 B+树 关于索引,描述得再多也不过份。 关于索引的生活举例,我个人更喜欢用的例子,并非是书中目录和页的关系,而是图书馆中的书架和书的关系。 有过借阅经历的我们能回忆起自己借书的步骤: 找一台机器,登录图书馆检索系统,录入自己身份; 搜索自己想查的书,根据作者、书名、类型; 得到一个书架编号,自己走到书架 在书架某层中,找到书,走借阅流程。 如没有找到,联系管理员协商解决,或放弃。 以前在工作时,和索引接触最多的场景,就是接到DBA的要求,对线上慢查进行优化。 当时内心第一反应就是“糟了,是不是索引没有用对(没走理想索引)“。 系统来看,索引就是帮助寻地址定位数据的信息量,也是存放在数据页中的。 在数据结构中,哈希表也是一种索引模型 —— 将key通过hash函数,使用hash结果可以快速回答底部元素是否存在的问题。 hash的局限性也非常明显,无法解决范围和带顺序的检索需求。 第二种想法是使用连续数组,使用二分查找,也能快速查找一个或者一片连续的数据。 但数组的问题就是添加和删除数据时的维护成本,远没有链表灵活。所以,有序数组只适用于存储静态数据。 第三种想法是引入非线性数据结构,树。 BST,AVL,RB树。 MySQL使用的是多叉树(N叉的N,一般可达到1000-2000),数据都存放在叶子节点,中间节点只放索引。 如果是4层的树高, 第三层所有叶子节点的数量是1500的三次方,超过30亿数据了。 称为B-树, 或B+树。 根节点常驻内存,中间节点的data page 是在磁盘的。 磁盘的IO 寻址是毫秒级,所以一次定位数据一般要2-3次的树中向下遍历工作。 普通索引和唯一索引 聚簇索引和非聚簇索引 聚簇索引的叶子节点内容是整行的数据。 非主键索引的叶子节点是存放的主键的值, 在InnoDB 非主键索引也称为二级索引。 回表: 二级索引,如果查询的数据是不在索引中的, 还要将查到的主键值再到聚簇索引中再查一次。 所以查询要尽量使用主键查询。 覆盖索引, 查主键树就能返回查询结果,不用回表。 索引建立 索引维护 页分裂: 新插入数据,会占掉原来的链表位置,即data page的空间。 那么如果这个页已经满了,B+树算法的设计是要申请一个新页,挪动部分数据。 就像动态数组的扩容, 页分裂显然是对性能有影响的。 有分裂也有对应的逆过程——合并页。 怎样减少分裂的操作呢。 如果说数据每次插入都是递增的,不会插入到之前的历史数据中,不就好了么? 这恰恰响应了自增主键的表设计规范。 索引下推 Index condition pushdown 在联合索引中,申请回表前增加判断, 减少回表次数。 锁 锁的划分有多种方式,比较直观的分类是按锁的影响范围,分为 全局锁 表级锁 行锁 全局锁, 对整个数据库加锁。 flush tables with read lock 场景,全库备份。 表级锁, lock tables ... read / write 分为表读锁和表写锁。 另一类表级锁是 MDL(Metadata lcok)。 行锁,不是所有的引擎都支持的。 InNoDB支持行锁,在需要的时候加上行锁,事务结束之后释放,即两阶段锁协议。 死锁 以两个事务为例,死锁满足的条件是: AB互斥 AB互不打断对方 AB有对方的依赖资源 AB循环等待对方 死锁检测的成本要在服务端。 附录:相关命令 show processlist; -- show current connections, include users, ip address and port, state show variables like '%timeout%' -- find wait_timeout 28800 seconds