Mysql逻辑架构

image.png

日志系统

博客参考

  • redo log : 是InnoDB引擎特有的日志

有了redo log，InnoDB就可以保证即使是数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为Crash-safe，
例子: 以粉板和账本的例子，简单理解就是老板只用账本记账，那么每次记账都要翻账本，效率低，有了粉板，可以先记录到粉板上，空闲了，再将其记录到账本上。

  • binlog : Server层自己的日志

两种日志的区别

  1. redo log是InnoDB引擎特有的；binlog是Mysql的Server层实现的，所有引擎都可以使用
  2. redo log是屋里日志，记录的是”在某个数据页面上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID = 2这一行的c字段加1”。
  3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

image.png

两阶段提交:

保证两份日志之间的逻辑一致。

表索引

主键索引又称为聚簇索引
非主键索引又称二级索引
回表
覆盖索引
索引下沉
左匹配原则
组合索引(a,b)不满足，需要单建索引的话，考虑下a,b所占字节，谁小谁再去建单独的索引

锁

  • 全局锁

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
典型场景: 全库逻辑备份

  • 表级锁

语法: lock tables … read/write,释放 unlock tables

  • 行锁

MDL: metadata lock，不需要显示使用，在访问一个表的时候会被自动加上。MDL的作用是保证读写的正确性。
读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行
如何安全地给小表加字段？
1. 解决长事务
事务不提交，就会一直占用MDL锁
2.alter table 设定等待时间

  1. SET statement_timeout = 50;
  2. ALTER TABLE test add column whatever2 INT4;

减少行锁对性能的影响

调整业务执行顺序

  1. 从顾客 A 账户余额中扣除电影票价；
  2. 给影院 B 的账户余额增加这张电影票价；
  3. 记录一条交易日志。

如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。
死锁和死锁检测
image.png

普通索引与唯一索引的选择

查询过程分析 ： 结论，差别微乎其微
普通索引查到数据之后，会继续向下查询，直到碰到第一个不满足条件的数据，停止查询。而唯一索引因为唯一，所以碰到满足的结果就不再向下查找。但是二者从性能上来看差别微乎其微，因为InnoDB的数据是按照页为单位读写的，一页数据默认大小16KB，对于整形来讲可存放近千条key，因此遇到需要翻页的几率很小。
更新过程分析 ：change buffer(只有普通索引有)，好坏由场景决定
表中要插入(4,400)记录
更新数据的目标页在内存中
唯一索引，找到3和5之间的位置，判断有无冲突，没有冲突，插入值，执行结束
普通索引，找到3和5之间的位置插入值，执行结束
更新数据的目标页不在内存中
唯一索引，需要将数据页读入内存，判断有无冲突，插入值，语句执行结束
普通索引，则是将更新记录在change buffer，语句执行就结束了。
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
change buffer正作用场景
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时
change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统
change buffer副作用场景
假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。
唯一索引的使用对于业务来说控制唯一性也通常是很多人的选择。

mysql为什么有时候会选错索引？

  1.  对应场景: 不断删除历史数据和新增数据的场景<br />     和扫描行数有关，使用explain 查看rows<br />     解决方案:
  1. 可以强制走索引 force index，不友好，增加开发维护成本
  2. 修改sql语句，引导mysql使用我们期望的索引，比如 修改order by 后面的字段
  3. 分析自己的业务，看是否真正需要索引，可以把使用错误的索引干掉

    怎么给字符串字段加索引?

    MySQL 是支持前缀索引的，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串
    使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本
    我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。
    使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。
    实例： 身份证号
    我们国家的身份证号，一共 18 位，其中前 6 位是地址码，所以同一个县的人的身份证号前 6 位一般会是相同的。所以需要创建长度为 12 以上的前缀索引，才能够满足区分度要求，这样就占用的磁盘空间太大，相同的数据页放下的索引值也就越少，搜索效率也就越低。
    解决方案
    a. 身份证号倒序存储 reverse(‘xxx’)
    b. 使用hash字段
    分析:
    相同点:使用a,b方法，就无法支持范围(like操作)查询了
    不同点: 三方面考虑
    1). 占用额外空间
    a方法不会额外占用空间
    b方法hash需要增加一个字段。
    当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
    2). cpu消耗
    a方法每次都需要额外调用一次reverse函数
    b方法需要额外调用一次crc32()函数
    如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些
    3). 查询效率
    从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

    表数据删除一半，标文件大小不变

    delete table 只是对删除数据做了逻辑的标记，物理磁盘的数据并未真正删除。不止删除数据会造成空洞，插入数据也会。如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂

    count(*) 的优化

    MyISAM 表虽然 count() 很快，但是不支持事务；
    show table status 命令虽然返回很快，但是不准确；(索引统计的值是通过采样来估算的。)
    InnoDB 表直接 count(    ) 会遍历全表，虽然结果准确，但会导致性能问题。
    优化分析:
    count()、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。
    count(主键 id) : InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
    count(1) : InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
    count(字段) ：InnoDB 引擎会遍历整张表，把每一行的 字段值 值都取出来，返回给 server 层。server 层拿到 值 后，判断是否为空的，不为空的累加1。
    结论：按照效率排序的话，count(字段)<count(主键 id)<count(1) ≈ count(    )，所以尽量使用 count(*)

    order by 工作

    排序位置: 如果排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
    全字段排序:
    1. 1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
    2. 2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
    3. 3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
    4. 4. 从索引 city 取下一个记录的主键 id；
    5. 5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的
    6. ID_Y；
    7. 6. 对 sort_buffer 中的数据按照字段 name 做快速排序；
    8. 7. 按照排序结果取前 1000 行返回给客户端。
    内存中的排序使用的是快速排序
    外部排序一般使用归并排序算法。sort_buffer_size越小，需要分成的份数越多，number_of_tmp_files的值就越大。
    算法缺点: 返回字段过多，内存放下的行数越少，分成的临时文件也多，排序的性能就会很差。
    rowid排序:
    参数设定max_length_for_sort_data(单位M)，如果单行的长度超过这个值，MYSQL就认为单行太大，要换一个算法。
    1. 1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
    2. 2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；
    3. 3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
    4. 4. 从索引 city 取下一个记录的主键 id；
    5. 5. 重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；
    6. 6. 对 sort_buffer 中的数据按照字段 name 进行排序；
    7. 7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三
    8. 个字段返回给客户端。
    排序比较:
    如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
    如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数
    体现出mysql的设计思想: 如果内存够，就要多利用内存，尽量减少磁盘访问。
    最终优化方案考虑方向: 联合索引(天然排序) > 覆盖索引(减少一次io)
    先通过原理分析算出扫描行数，然后再通过查看慢查询日志，来验证自己的结论。
    如果创建的表没有主键或者把一个表的主键删掉了，那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。

    索引被破坏场景

  • 条件字段函数操作
  • 隐式类型转换 ```sql select * from tradelog where tradeid = 110717; — tradeid 是varchar(32),输入的却是整形，所以需要做类型转换
  1. 隐式转换的产生条件
  1. 当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。则会发生转换隐式
  2. 发生隐式转换的条件： 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换 两个参数都是字符串，会按照字符串来比较，不做类型转换 两个参数都是整数，按照整数来比较，不做类型转换 十六进制的值和非数字做比较时，会被当做二进制串 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较 所有其他情况下，两个参数都会被转换为浮点数再进行比较 ```
  • 隐式字符编码转换

两个表join时，如果使用的字符集不同，比如一个是utf8，一个是utf8mb4，这两个表关联的话，就不会使用到索引了.

为什么mysql扫描全表数据会不会把内存打爆

主机内存只有100G，现在要对一个200G的达标做全表扫描，会不会把数据库主机的内存用光了？
答案是不会，因为 MySQL 是“边读边发的”。

  1. 服务端并不需要保存一个完整的结果集。取数据和发数据的流程是这样的：
  2. 1. 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
  3. 2. 重复获取行，直到 net_buffer 写满，调用网络接口发出去。
  4. 3. 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
  5. 4. 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

join

  • index nested-loop join(被驱动表所关联字段有索引)
  • simple nested-loop join(SNL)太笨重，mysql不用这个算法
  • block nested-loop join(BNL)

BNL相对于SNL优势: 扫描数据行数一样，但比较数据是在内存中
BNL相对于SNL在数据库层做全表扫描关联查询，BNL是t1的数据读入线程内存join_buffer中，然后扫描t2,将t2中的每行数据取出来，跟join_buffer中的数据做比对，作为结果集的一部分返回。如果t1结果集过大，则分段join_buffer处理，join_buffer由参数join_buffer_size设定，默认256k。
能不能用join语句？

  1. 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
  2. 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

所以你在判断要不要使用 join 语句时，就是看 explain 结果里面，Extra 字段里面有没有出现“Block Nested Loop”字样。
如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？
1. 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；

  1. 如果是 Block Nested-Loop Join 算法：

在 join_buffer_size 足够大的时候，是一样的；
在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。
结论: 总是应该使用小表做驱动表
什么叫小表:
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤（on和where），过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表

group by总结

  1. 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
  2. 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
  3. 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size 参数，来避免用到磁盘临时表；
  4. 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果

todo

on 和where 执行顺序和加载数据机制