mysql 学习笔记(实践篇)

更新于 2月26日  约 12 分钟

实践篇

09 | 普通索引和唯一索引

  • 查询过程:性能几乎无差别
  • 更新过程:普通索引 更新的目标页不在内存中可以使用到change buffer。建议使用普通索引。

    change buffer:当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
  • redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

10 | MySQL为什么有时候会选错索引

  • explain 查询语句执行情况

  • show index 可以查看索引基数

  • 优化器的选择主要基于扫描行数(mysql使用采样统计计算,非准确数字)判断

  • analyze table t 可以优化查询分析

  • 索引选择异常和处理

    • force index 强行选择一个索引(一般不需要,只有在查询分析判断问题时使用,基本还是使用mysql内部优化器机制)
    • 更新语句,如使用 order by b,a 就会使用a,b索引
    • 业务沟通,删除不必要的查询索引
    • *

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

  • 前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本。alter table SUser add index index2(email(6));
  • 前缀索引长度:建立索引时关注的是区分度,区分度越高越好。因为区分度越高,意味着重复的键值越少。因此,我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

    mysql> select 
      count(distinct left(email,4))as L4,
      count(distinct left(email,5))as L5,
      count(distinct left(email,6))as L6,
      count(distinct left(email,7))as L7,
    from SUser;
  • 使用前缀索引很可能会损失区分度,所以你需要预先设定一个可以接受的损失比例,比如 5%。然后,在返回的 L4~L7 中,找出不小于 L * 95% 的值,假设这里 L6、L7 都满足,你就可以选择前缀长度为 6。
  • 前缀索引就用不上覆盖索引对查询性能的优化
  • 对身份证类似的索引,如果用前缀的话,区分度太低,就不合适,解决方案有几种。

    • 使用身份证倒序存储,reverse()
    • 增加整形的hash字段,如crc32()
    • *

12 | 为什么我的MySQL会“抖”一下?

  • 更新主要是写内容和日志,更新内容会先存入内存(没有同步的叫脏页),等时机(4种情况)再写入磁盘。

    1. redo log 写满了。这时候系统会停止所有更新操作,把 checkpoint 往前推进,redo log 留出空间可以继续写
    2. 内存不足。当需要新的内存页,而内存不够用的时候,就要淘汰一些数据页,空出内存给别的数据页使用。如果淘汰的是“脏页”,就要先将脏页写到磁盘。
    3. 系统空闲
    4. 系统重启
  • InnoDb使用刷新内存机制。InnoDB 用缓冲池(buffer pool)管理内存

image.png

  • InnoDB 的刷盘速度就是要参考这两个因素:一个是脏页比例,一个是 redo log 写盘速度。

image.png
image.png
image.png


13 | 为什么表数据删掉一半,表文件大小不变?

  • 将innodb_file_per_table 设置为 ON,表数据是单独的文件ibd,drop table后会删除文件,并回收空间,否则不行。
  • 记录复用:删除记录的空间,符合条件的数据下次插入时候会使用
  • 数据页复用:删除后整个数据页被标记为可复用。
  • 插入数据:如果当前数据页已满,则会数据页分裂,可能造成数据空洞。
  • 重建表:
    image.png

14 | count(*)这么慢,我该怎么办?

  • Myisam 有一个地方专门记录总数
  • InnoBb 因为是事物,并发等属性,不能直接获取,只能通过遍历最小的那颗索引树实现。
  • count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数;而 count(字段),则表示返回满足条件的数据行里面,参数“字段”不为 NULL 的总个数。
  • 尽量使用count(*)
    • *

16 | “order by”是怎么工作的?

  • 全字段排序:

    • 需要查询的字段 初始化 放入sort_buffer。
    • sort_buffer_size,就是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size,排序就在内存中完成。但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序。
  • rowid排序:

    • 设置 max_length_for_sort_data 大小,如果单行数据太大,使用rowid排序,rowid排序需要结果再回表查询
  • mysql默认使用全字段排序,原则尽量使用内存,尽量少回表。

17 | 如何正确地显示随机消息?

  • order by rand() 使用了内存临时表,内存临时表排序的时候使用了 rowid 排序方法。要尽量避免使用该方法。
  • image.png
  • 扫描行数是C+(Y1+1)+(Y2+1)+(Y3+1)
  • 进一步优化:
  • image.png

18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?

  • 索引字段做函数操作,会破坏索引值的有序性。
  • 隐式类型转换:mysql 默认比较会把字符串转换成数字。如果数字转换成字符串,优化器会默认添加函数,所以破坏索引。需使用 where id=''的单引号。
  • 隐式字符编码转换:

    • mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

19 | 为什么我只查一行的语句,也执行这么慢

  • 第一类:简单查询长时间不返回

    • 等MDL锁,等flush,等行锁
    • show processlist
    • image.png
    • MySQL 启动时需要设置 performance_schema=on,相比于设置为 off 会有 10% 左右的性能损失。通过查询 sys.schema_table_lock_waits 这张表,我们就可以直接找出造成阻塞的 process id,把这个连接用 kill 命令断开即可。
  • 第二类:查询慢

    • 慢查询日志
    • lock in share mode 的 SQL 语句,是当前读,因此会直接读到 1000001 这个结果。
    • select * from t where id=1 这个语句,是一致性读,因此需要从 1000001 开始,依次执行 undo log,执行了 100 万次以后,才将 1 这个结果返回
    • *

20 | 幻读是什么,幻读有什么问题?

  • innoDB 间隙锁,next-key lock。

22 | MySQL有哪些“饮鸩止渴”提高性能的方法?

  • 第一种方法:先处理掉那些占着连接但是不工作的线程。
  • 第二种方法:减少连接过程的消耗。
  • 慢查询性能问题
  • QPS 突增问题
  • 全量回归测试工具(pt-query-digest
    • *

23 | MySQL是怎么保证数据不丢的?

  • redo log 和 binlog 都是顺序写,磁盘的顺序写比随机写速度要快;* 组提交机制,可以大幅度降低磁盘的 IOPS 消耗。
  • IO瓶颈解决方法:

    • image.png
  • 通常我们说 MySQL 的“双 1”配置,指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说,一个事务完整提交前,需要等待两次刷盘,一次是 redo log(prepare 阶段),一次是 binlog。
  • 2,15,23章节redo log,bin log 和crash safe.

24 | MySQL是怎么保证主备一致的?

  • 主备切换,binlog格式

    • statement:可能引起不一致
    • row:全量信息,可以恢复所有数据。缺点,占用空间大。
    • mixed:有风险的sql语句,会使用row格式记录。
  • MariaDB 的flashback 工具:恢复数据误操作。

25 | MySQL是怎么保证高可用的?

  • 主备延迟的几种情况

    • 主备机器性能
    • 备库压力偏大(查询等等)
    • 大事物
  • 策略

    • 可靠性优先策略
    • 可用性优先策略
阅读 89更新于 2月26日

推荐阅读
目录