学习自jdh莫老师
MySQL逻辑架构图 redo log 为什么要redo log？如果每一次的更新 *** 作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者采用了WAL技术（Write-Ahead Logging），关键点就是先写日志，再写磁盘，这个日志就是redo log。redo log是InnoDB引擎特有的日志。
binLog 为什么会有两份日志？redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。那为什么会有两份日志呢？因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。
两种日志的区别redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
update T set c=c+1 where ID=2
两阶段提交由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。假设不用两阶段提交，会出现如下情况
先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。 事务隔离 什么是事务？
简单来说，事务就是要保证一组数据库 *** 作，要么全部成功，要么全部失败。MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。
隔离性与隔离级别隔离性是事务ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）中的I，当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）
读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
Oracle 数据库的默认隔离级别是“读提交”，MySQL则是“可重复读”
事务隔离的实现？在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚 *** 作。记录上的最新值，通过回滚 *** 作，都可以得到前一个状态的值。假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）
在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。
create table T(id int primary key, k int not null, name varchar(16),index (k))engine=InnoDB;表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。
在一个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。例如如果有一个高频请求，要根据市民的身份z号查询他的姓名，建立一个（身份z号、姓名）的联合索引就有意义了，可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。
最左前缀原则B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。比如说已经有了（name，age）这个联合索引，SQL 语句的条件是"where name like ‘张 %’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是 ID3，然后向后遍历，直到不满足条件为止。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了
索引下推MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。
全局锁全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用场景是，做全库逻辑备份。一般只有不支持事务的数据库引擎才会用到这种锁，如MyISAM
表级锁MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。表锁的语法是 lock tables … read/write。在 MySQL 5.5 版本中引入了 MDL锁，每执行一条DML、DDL语句时都会申请MDL锁，DML *** 作需要MDL读锁，DDL *** 作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）。MDL读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。MDL读写锁之间、写锁之间是互斥的，用来保证变更表结构 *** 作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
行锁MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。针对死锁，InnoDB有两种解决策略
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用这个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。但是超时时间设置太短的话，会出现很多误伤。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。对唯一索引来说，所有的更新 *** 作都要先判断这个 *** 作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的 *** 作之一。
综上所述，建议尽量选择普通索引，如果所有的更新后面，都马上伴随着对这个记录的查询，应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。在实际使用中，普通索引和 change buffer 的配合使用，对于数据量大的表的更新优化还是很明显的。
随着表数据的不断增删，可能会出现MySQL 选错索引，根本原因是MySQL没能准确地判断出扫描行数或者错误地计算了执行成本，这种是低概率事件，但是某些场景下有可能触发MySQL的这个bug，解决方法一般有以下几种
由于索引统计信息不准确导致的问题，可以用 analyze table 来解决
采用 force index 强行选择一个索引，不过这个弊端也很明显，一来这么写不优雅，二来如果索引改了名字，这个语句也得改，显得很麻烦。而且如果以后迁移到别的数据库的话，这个语法还可能会不兼容。
修改语句，引导 MySQL 使用我们期望的索引。 如把select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1改成select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1
新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
主要看该字段的区分度是不是足够大，建索引是否有必要。如果有必要，建索引有两种方法，一种是整个字段索引，如果字段值普遍较长，则可能会消耗较大的存储空间，另一种是使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。如：alter table SUser add index index2(email(7))。那问题来了，当要给字符串创建前缀索引时，有什么方法能够确定应该使用多长的前缀呢？
select count(distinct email) as L from SUser;
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;
但是使用前缀索引，就用不上覆盖索引对查询性能的优化了
小技巧：倒序存储和使用 hash 字段
SQL语句突然“变慢”？InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘 *** 作，这个日志叫作 redo log（重做日志）。平时执行很快的更新 *** 作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。MySQL在下面这些情境下会做flush *** 作
InnoDB 的 redo log 写满了。这时候系统会停止所有更新 *** 作，把 checkpoint 往前推进，redo log 留出空间可以继续写。这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了
系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。这种情况其实是常态。但是出现以下这两种情况，都是会明显影响性能的
一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。
要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 数据库主机的磁盘能力。这个值建议设置成磁盘的 IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试。并且平时要多关注脏页比例（innodb_max_dirty_pages_pct），不要让它经常接近 75%。如果是SSD盘，innodb_flush_neighbors参数设置成0
MySQL 认为系统“空闲”的时候。
MySQL 正常关闭
delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。不止是删除数据会造成空洞，插入数据也会。也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。
MySQL 5.6 版本开始引入的 Online DDL，对表重建 *** 作流程做了优化，可以通过执行alter table t engine=InnoDB对表进行重建。通过日志文件记录和重放 *** 作，在重建表的过程中，允许对表做增删改 *** 作。重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个 *** 作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，需要很小心地控制 *** 作时间。
为什么 InnoDB 不跟 MyISAM 一样，也把记录总数存起来呢，查询的时候直接返回？这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。对于 count(*) 这样的 *** 作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历，但是如果表的数据十分巨大，因为是整棵树遍历，所以性能会非常差。如果有高频需要获取大表记录总数的场景，需要自己进行计数。
比如可以在数据库里面建一张表，把高频需要获取记录总数的大表的记录数自己进行维护，获取总数时候直接从该表获取。
对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的 *** 作。
对于 count(字段) 来说，如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
所以结论是：按照效率排序的话，count(字段)
select city,name,age from t where city='杭州' order by name limit 1000 ;
图中“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
刚说的按字段排序算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。所以rowid排序的思想是减少查询字段的数量，节省排序内存，但是缺点是有可能需要回到原表去取数据。对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
使用 show processlist 命令查看 Waiting for table metadata lock 的示意图。MySQL 启动时需要设置 performance_schema=on，通过查询 sys.schema_table_lock_waits 这张表，就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。
出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了select 语句。
select * from t where id=1 lock in share mode;由于访问 id=1 这个记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，select 语句就会被堵住。这个问题并不难分析，但问题是怎么查出是谁占着这个写锁。如果是 MySQL 5.7 版本，可以通过 sys.innodb_lock_waits 表查到。查询语句：select * from t sys.innodb_lock_waits where locked_table=‘test.t’\G
session A 先用 start transaction with consistent snapshot 命令启动了一个事务，之后 session B 才开始执行 update 语句。session B 更新完 100 万次，生成了 100 万个回滚日志 (undo log)。
带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。所以后者的执行时间大概是前者的4000倍
在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。
session A 在 T1 时刻就声明了，“我要把所有 d=5 的行锁住，不准别的事务进行读写 *** 作”。而实际上，这个语义被破坏了。
数据一致性问题如何解决幻读？
产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。顾名思义，间隙锁，锁的就是两个值之间的空隙。跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个 *** 作。间隙锁之间都不存在冲突关系。间隙锁的引入，虽然解决了幻读的问题，但是可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。间隙锁是在可重复读隔离级别下才会生效的。所以，如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。
next-key lock:间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。表 t(下图上方数字是主键值) 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
原则 1：加锁的基本单位是 next-key lock。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
由于表 t 中没有 id=7 的记录,加锁单位是 next-key lock()，session A 加锁范围就是 (5,10]；这是一个等值查询 (id=7)，而 id=10 不满足查询条件，根据优化2，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。
session A 要给索引 c 上 c=5 的这一行加上读锁。根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。这个例子说明，锁是加在索引上的。如果要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化。
开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。这样，session B 和 session C 的结果就能理解了。
session A 用字段 c 来判断，加锁规则跟案例三唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10]这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。
session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。
session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。也就是说，这个 delete 语句在索引 c 上的加锁范围，就是下图中蓝色区域覆盖的部分。
session A 的 delete 语句加了 limit 2。表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session B 的 insert 语句执行通过了，跟案例六的结果不同。这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：
这个例子的指导意义就是，在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让 *** 作更安全，还可以减小加锁的范围。
session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。你可能会问，session B 的 next-key lock 不是还没申请成功吗？ 其实是这样的，session B 的“加 next-key lock(5,10] ” *** 作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。
select count(*) from tradelog where month(t_modified)=7;
对索引字段做函数 *** 作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能而遍历整个索引的所有值。需要注意的是，即使是对于不改变有序性的函数，也不会考虑使用索引。比如，对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 *** 作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以，需要在写 SQL 语句的时候，手动改写成 where id = 10000 -1 才可以。
select * from tradelog where tradeid=110717;
tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换，导致走了全表扫描。MySQL默认是将字符串转换成数字进行比较，所以上面的语句等价于select * from tradelog where CAST(tradid AS signed int) = 110717;触发了上面说到的规则：对索引字段做函数 *** 作，优化器会放弃走树搜索功能。
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; 由于tradelog表的tradeid字符集编码是utf8mb4，而trade_detail表的tradeid是utf8，utf8mb4是utf8的超集，所以发生了隐式转换，该语句等价于select d.* from tradelog l, trade_detail d where d.tradeid=CONVERT(d.traideid USING utf8mb4) and l.id=2; 触发了上面说到的规则：对被驱动表索引字段做函数 *** 作，优化器会放弃走树搜索功能。
思考题：不调整字段编码的情况下怎么改才能走对索引？
慢查询导致性能问题的三种可能情况如下
索引没有设计好这种场景一般就是通过紧急创建索引来解决。MySQL 5.6 版本以后，创建索引都支持 Online DDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；执行主备切换；这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。
语句没写好这种就要通过分析explain命令分析语句的执行计划，并进行优化，典型反面案例参考上一页的索引失效反面案例。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式，当出现线上业务bug导致QPS暴涨，数据库也面临宕机风险的时候，可以用这个把压力最大的SQL直接重写为“select 1”返回，以解燃眉之急
MySQL选错了索引这种是低概率事件，具体解决方案参考上面的“MySQL为什么有时候会选错索引”这节
慢查询导致性能问题的三种可能情况，实际上出现最多的是前两种，一般在测试环境可以通过以下手段尽量规避
上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
在测试表里插入模拟线上的数据，做一遍回归测试；观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）