MySQL - mpgcompile/principle GitHub Wiki

一、一条SQL查询语句是如何执行的 (MySQL分为Serve层和存储引擎) 存储引擎：InnoDB、MyISAM、Merony等，一般MySQL默认的存储引擎InnoDB

  • 1、连接器、查询缓存（之前有没有执行过当前语句）、分析器（词法、语法分析，语句是否正确）、优化器（索引、多表关联）、执行器（调用InnoDB引擎接口）

二、一条SQL更新语句是如何执行的 （redo log（重做日志）和 binlog（归档日志））

  • 1、当一条记录需要更新的时候，InnoDB引擎先把记录写到redo log里面，并更新内存（更新完毕）。InnoDB会在系统空闲时写入到磁盘里面。
  • 2、crash-safe是指，有了redo log，InnoDB保证数据库异常发生重启之后数据不会丢失。
  • 3、redo log是InnoDB引擎特有的；bin log是MySQL的Server层实现的，所有引擎都可以使用。
  • 4、redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”
  • 5、两阶段提交,目的：为了两份日志逻辑一致(redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致)

三、事物隔离

  • 1、隔离性与隔离级别
  • A: ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

  • B: 隔离级别，数据库上有多个事务同时执行，就可能出现脏读、不可重复读、幻读
  • 2、事务的启动方式
  • A: 显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback。

  • B: set autocommit=0，这个命令会将这个线程的自动提交关掉

四、深入浅出的索引

  • 1、hash表、有序数组和搜索树 hash数组适用于等值查询场景，有序数组在等值查询和范围查询都是优秀，有序数组索引只是适应于静态存储引擎中
  • 2、InnoDB索引
  • 3、覆盖索引 索引k已经覆盖我们查询需求。由于覆盖索引可以减少查找的次数、所以覆盖索引也是常使用的性能优化
  • 4、最左前缀原则
  • 5、索引下推

五、全局锁和表锁（MySQL锁：全局锁、表锁、行锁）

  • 1、全局锁：使用场景是，做全库逻辑备份

六、怎么减少行锁对性能的影响

  • 1、两阶段锁协议 在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。
  • 2、死锁和死锁的检测
  • 当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源，就会导致几个线程进入互相等待的状态，这就是死锁。
  • 事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。 事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。
  • 死锁解决方案
  • A: 直接进入等待，直到超时。超时时间可以通过innodb_lock_wait_timeout来设置。

  • B: 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以进行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑

  • C: 控制并发度

七、MySQL为什么有时候会选错索引

  • 1、优化器的逻辑 优化器选择索引目的，找到一个最优的执行方案，用最小的代价去执行语句。在数据库里面扫描行数是影响执行代价的的因素之一，扫描次数越少代表访问磁盘次数越少，占用cpu资源越少。优化器还会结合是否使用临时表、是否排序

    2、索引异常和处理

  • A:采用force index强行选择一个索引

  • B:修改语句，引导优化器选择我们期望的索引

  • C:新建一个更合适的索引，或者删掉误用的索引

八、怎么给字符串字段加索引

  • 1、alter table user add index index1(email);
  • 2、alter table user add index index1(email(6)); 前缀索引
  • 使用好前缀索引，定义好长度，即可以做到节省空间，又不用增加额外的查询成本。
  • 3、前缀索引对覆盖索引的影响
  • A: 使用前缀索引可能会增加查询行数,会影响到性能。
  • 4、其他方式
  • A: 使用倒叙存储
  • B: 使用hash字段

九、为什么MySQL的语句会变慢

  • 当内存的数据页和磁盘数据页不一致的时候，我们称为这个内存页为脏页。
  • 内存数据写入到磁盘的时候，内存和磁盘的数据就一致了，称为干净页。 1、InnoDB刷脏页的控制策略
  • A: 真正的原因：InnoDB在后台刷脏页，然而在刷脏页的过程要将内存数据写入到磁盘。 所以无论是你的查询语句在需要内存的时候可能要淘汰一个脏页， 还是在刷脏页的逻辑占用IO资源可能影响到你的更新语句。这些都可能造成你的MySQL的语句感觉慢的原因。
  • B: 要合理地设置innodb_io_capacity的值，并且平时要多关注脏页比例，不要让它经常接近75%。
  • C: 一个内存配置为128GB、innodb_io_capacity设置为20000的大规格实例。
  • C: InnoDB中，innodb_flush_neighbors参来控制这个行为，值为1的时候会有连坐机制，值为0时候表示不找邻居，自己刷自己。 如果SSD这类IOPS较高的话，建议设置成0。因为这个时候IOPS往往不是瓶颈，而只刷自己，就能更快的执行完必要的刷脏页的操作，减少SQL语句的响应时间。
  • D: 在MySQL8.0中，innodb_flush_neighbors参数的默认值就已经是0了。

十、为什么表数据删掉一半，表文件大小不变

  • 1、参数innodb_file_per_table
  • A: 参数设置为OFF表示是：表的数据放在系统共享表空间，也就是跟数据字典放在一起。
  • B: 参数设置为ON表示是：每个InnoDB表数据存储在一个以.ibd为后缀的文件。
  • 2、MySQL5.6开始参数的默认值就是ON了，

十一、如何正确的显示随机消息

  • 1、内存临时表 A: order by rand(),需要临时表，并在内存临时表上排序。 B: 对于内存表，回表过程只是简单根据数据行位置，直接访问内存得到数据，根本不会导致多访问磁盘。 C: order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法。
  • 2、磁盘临时表 A: tmp_table_size这个配置限制了内存临时表的大小，默认值16M。如果临时表大小超过了tmp_table_size。那么内存临时表就会转化成磁盘临时表。
  • 3、随机排序方法 A: 取得表主键id的最大值M和最小值N。 B: 用随机函数生成一个最大值到最小值数 X = (M-N)*rand() + N; C: 取不小于X的第一个ID的行。

十二、为什么我只查一行的语句，也执行这么慢

  • 1、查询长时间不返回
  • A: 等MDL锁：有一个线程正在表t上请求或持有MDL锁，把select语句堵住了。
  • B: 等flush。
  • C: 等行锁。
  • 2、查询慢
  • A: 一致性读 select * from t where id=1，会比较慢。
  • B: 当前读：select * from t where id=1 lock in share mode会直接定位到id=1的行，所有更加快。

十三、MySQL有哪些“饮鸩止渴”提高性能的方法

  • 1、先处理掉那些占着链接不工作的线程
  • 2、减少连接过程的消耗

十四、MySQL是怎么保证主备一致的

  • 1、binlog的特性保证了在备库执行相同的的binlog，可以得到与主库相同的状态。通常情况下我们认为主备的数据是一致的。