mysql学习笔记（一）

1 基础架构

mysql逻辑架构图：

Server层
包括连接器、查询缓存、分析器、优化器、执行器等

存储引擎层
负责数据的存储、提取。架构模式是插件式，支持InnoDB、MylSAM、Memory等多个存储引擎。

1.1 查询语句执行过程

1.1.1 连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

先tcp握手，随后开始认证身份，用户名密码正确后，连接器会到权限表查询权限，之后该连接里面的权限判断逻辑都将依赖此时读到的权限。新修改的权限不会影响已经存在连接的权限。连接空闲时间默认为8小时，超出则自动断开。

长连接：

指连接成功后，如果客户端持续有请求，则一直使用同一个连接

短连接：

指每次执行完很少的几次查询就断开连接，下次查询需再重新建立一个

全部使用长连接会导致内存占用太大，被系统强行杀掉，如何解决？

1.1.2 查询缓存

MySQL拿到一个查询请求后，会先到查询缓存查看是否是执行过这条语句。之前执行过的语句及其结果 以key-value对的形式直接缓存在内存中。
key是查询的语句，value是查询的结果。

如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。

但只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。

用SQL_CACHE显式指定，可使用查询缓存

mysql> select SQL_CACHE * from T where ID=10；

8.0开始删除了查询缓存功能

1.1.3 分析器

1、词法分析
MySQL从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。

2、语法分析
根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。

1.1.4 优化器

在开始执行之前，还要先经过优化器的处理。
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

1.1.5 执行器

开始执行语句，先判断对要查询的表是否有权限，没有报错，有则打开表继续执行，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

在数据库的慢查询日志中rows_examined字段，表示这个语句执行过程中扫描了多少行。该值就是在执行器每次调用引擎获取数据行的时候累加的。

1.2 更新语句执行过程

同上类似，
执行语句前要先连接数据库，这是连接器的工作。

在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表T上所有缓存结果都清空。

接下来，分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用ID这个索引。然后，执行器负责具体执行，找到这一行，然后更新。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志） 和 binlog（归档日志）。

1.2.1 redo log

WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log（粉板）里面，并更新内存，此时更新完成。同时，InnoDB引擎会在系统比较空闲的时候，将这个操作记录更新到磁盘里面。

InnoDB的redo log是固定大小的

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos和checkpoint之间的是“粉板”上还空着的部分，可以用来记录新的操作。（图中红线部分）

当write pos追上checkpoint，此时不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

1.2.2 binlog

redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志）。

与redo log不同：

执行器与InnoDB引擎执行更新流程图：
图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的。

1.2.3 两阶段提交

redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

怎样让数据库恢复到半个月内任意一秒的状态？

如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

当需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用binlog来实现的，这个“不一致”就会导致线上出现主从数据库不一致的情况。

2 事务隔离

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的。

2.1 隔离性、隔离级别

当数据库上有多个事务同时执行的时候，为解决可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，产生了“隔离级别”的概念

SQL标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

· 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。

· 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。

· 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。
  当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  
· 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。
  当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

例子:

· 读未提交 ，事务B在提交前，所做改变对事务A是可见的，即事务A 读到的V1、V2、V3值分别为2,2,2
· 读提交，事务B在提交之前，所做改变对事务A是不可见的，即事务A 读到的V1、V2、V3值分别为1,2,2
· 可重复读，事务A在提交前，读到的数据要与开始时读到的保持一致，即事务A 读到的V1、V2、V3值分别为1,1,2
· 串行化，因读写锁冲突，事务B在修改时会被锁住，直到事务A提交，才会被释放，即事务A 读到的V1、V2、V3值分别为1,1,2

2.2 事务隔离的实现

实际上，数据库里会创建视图，访问的时候以视图的逻辑结果为准。
可重复读：这个视图是在事务启动时创建的，整个事务存在期间都用这个视图；
读提交：这个视图是在每个SQL语句开始执行的时候创建的；
读未提交：直接返回记录上的最新值，没有视图概念；
串行化：直接用加锁的方式来避免并行访问；

在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

当系统里没有比这个回滚日志更早的read-view的时候（如图中read-viewA前没有read-view，此时系统会删除read-viewA）

长事务意味着系统里面会存在很老的事务视图。所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

在MySQL 5.5及以前的版本，回滚日志是跟数据字典一起放在ibdata文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库

2.3 事务的启动方式

MySQL的事务启动方式有以下几种：

1、显式启动事务语句， begin 或 start transaction。配套的提交语句是commit，回滚语句是rollback。

2、set autocommit=0，这个命令会将这个线程的自动提交关掉。
   意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交。
   这个事务持续存在直到你主动执行commit 或 rollback 语句，或者断开连接。

由于有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令，意外导致长事务。所以使用set autocommit=1, 通过显式语句的方式来启动事务。

在autocommit为1的情况下，用begin显式启动的事务，如果执行commit则提交事务。如果执行 commit work and chain，则是提交事务并自动启动下一个事务。

在information_schema库的innodb_trx这个表中查询长事务，比如下面这个语句，用于查找持续时间超过60s的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

3 索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

3.1 索引常见模型

哈希表、有序数组、搜索树

3.1.1 哈希表

哈希表是一种以键-值（key-value）存储数据的结构，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。

但多个key值经过哈希函数的换算，会出现同一个值的情况。此时需要拉出一个链表

图中四个ID_card_n的值并不是递增的，
好处是，增加新的User时速度会很快，只需要往后追加。
缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。

哈希表这种结构适用于只有等值查询的场景

3.1.2 有序数组

有序数组在等值查询和范围查询场景中的性能就都非常优秀

如果仅仅看查询效率，有序数组就是最好的数据结构了。但在需要更新数据的时候往中间插入一个记录就必须得挪动后面所有的记录，成本太高。

有序数组索引只适用于静态存储引擎

3.1.3 二叉搜索树

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。

如果要查ID_card_n2，按照图中的搜索顺序就是按照UserA -> UserC -> UserF -> User2这个路径得到。这个时间复杂度是O(log(N))。为了维持O(log(N))的查询复杂度，需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是O(log(N))。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么就要使用“N叉”树。（“N”取决于数据块的大小）

====================================================================
数据库底层存储的核心就是基于这些数据模型的。每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景。

3.2 InnoDB 的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。因InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。

假设，我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。
建表语句：

mysql> 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)，两棵树的示例示意图如下。

主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

· 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
· 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。
  这个过程称为回表。

基于非主键索引的查询需要多扫描一棵索引树 ，因此在应用中应该尽量使用主键查询。

3.2.1 索引维护

B+树为了维护索引有序性，在插入新值的时候需要做必要的维护。

以上面这个图为例，
· 如果插入新的行ID值为700，则只需要在R5的记录后面插入一个新记录。
· 如果新插入的ID值为400，需要逻辑上挪动后面的数据，空出位置。如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。（影响数据页的利用率，大约降低50%）
· 当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

=======================================================

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。

自增主键的插入数据模式。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

3.2.2 覆盖索引

执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

这条SQL查询语句的执行流程:

1、在k索引树上找到k=3的记录，取得 ID = 300；
2、再到ID索引树查到ID=300对应的R3；
3、在k索引树取下一个值k=5，取得ID=500；
4、再回到ID索引树查到ID=500对应的R4；
5、在k索引树取下一个值k=6，不满足条件，循环结束。

回到主键索引树搜索的过程，我们称为回表。

如果执行语句改为 select ID from T where k between 3 and 5。这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。即，索引k已经“覆盖了”我们的查询需求，称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

3.3.3 最左前缀原则

B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

· 当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。
· 如果你要查的是所有名字第一个字是“张”的人，你的SQL语句的条件是"where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

====================================================================
在建立联合索引的时候，如何安排索引内的字段顺序?

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

第二原则是，空间；A字段是比B字段大的 ，则创建一个（A,B)的联合索引和一个(B)的单字段索引。

3.3.4 索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么，SQL语句是这么写的：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

左图，这个过程InnoDB并不会去看age的值，只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此，需要回表4次。

右图，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。本例子中只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次。

3.3 唯一索引和普通索引

非主键索引又可以分为普通索引与唯一索引，都作为普通索引一个具有唯一性，一个可重复。

3.3.1 查询过程

假设，执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

· 对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
· 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

性能差距微乎其微

3.3.2 更新过程

当需要更新一个数据页时，如果数据页在内存中就直接更新;
而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

什么条件下可以使用change buffer呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。即要先判断现在表中是否已经存在要更新的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

所以只有普通索引才会用到change buffer

如果要在这张表中插入一个新记录(4,400)的话，InnoDB的处理流程是：
第一种情况是，这个记录要更新的目标页在内存中。 流程如下：

· 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
· 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

第二种情况是，这个记录要更新的目标页不在内存中。 流程如下：

· 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
· 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

change buffer使用场景
merge才是真正进行数据更新的时刻，change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

3.3.3 change buffer 和 redo log

在表上执行这个插入语句：

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

更新语句操作流程：

1、Page 1在内存中，直接更新内存；
2、Page 2没有在内存中，就在内存的change buffer区域，记录下“我要往Page 2插入一行”这个信息
3、将上述两个动作记入redo log中（图中3和4）。

执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。

随后执行查询操作：

mysql> select * from t where k in (k1, k2)

1、读Page 1的时候，直接从内存返回。不需要进行读盘操作，不需要从redo log里面把数据更新以后才可以
2、要读Page 2的时候，需要把Page 2从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。

直到需要读Page 2的时候，这个数据页才会被读入内存。

由此可得出结论：redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

3.4 优化器

在MySQL中一张表其实是可以支持多个索引的，使用哪个索引是由MySQL来确定的。

选择索引是优化器的工作。优化器选择索引的目的：是找到一个最优的执行方案，并用最小的代价去执行语句。
在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

====================================================================

扫描行数是怎么判断的？
MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息（索引的区分度）来估算记录数。

而一个索引上不同的值的个数，称之为“基数”（cardinality）。
即，这个基数越大，索引的区分度越好。

Mysql通过采样统计得到索引的基数
采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：

· 设置为on的时候，表示统计信息会持久化存储。这时，默认的N是20，M是10。
· 设置为off的时候，表示统计信息只存储在内存中。这时，默认的N是8，M是16。

3.4.1 索引选择异常和处理

对于由于索引统计信息不准确导致的问题，可以用analyze table来解决。

对于其他优化器误判的情况:

方法一：采用force index强行选择一个索引。

方法二：可以考虑修改语句，引导MySQL使用我们期望的索引。

方法三：在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

3.4.2 给字符串字段加索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

当要给字符串创建前缀索引时，有什么方法能够确定应使用多长的前缀？

建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。
首先，使用下面这个语句，算出这个列上有多少个不同的值：

mysql> select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用这个语句：

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。

当遇到前缀的区分度不够好的情况时，可使用：

第一种方式是使用倒序存储；第二种方式是使用hash字段。

4 锁

根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类。

4.1 全局锁

全局锁就是对整个数据库实例加锁。MYSQL通过Flush tables with read lock (FTWRL)来加全局锁，此时整个库为只读状态，其他线程的数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句会被阻塞。

全局锁的典型使用场景是，做全库逻辑备份。

可在可重复读隔离级别下开启一个事务。官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。

但有的引擎不支持支持这个隔离级别。比如，对于MyISAM这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。此时就需要全局锁。

single-transaction方法只适用于所有的表使用事务引擎的库。

====================================================================
为什么不使用set global readonly=true的方式来使全库只读？

主要原因：
1、在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响面更大。
2、在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

4.2 表级锁

表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁的语法是 lock tables … read/write。可用unlock tables主动释放锁，也可在客户端断开的时候自动释放。
注意：lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

另一类表级的锁是MDL（metadata lock)。 MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

· 读锁之间不互斥。
· 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。

举例

1、session A先启动，这时候会对表t加一个MDL读锁。由于session B需要的也是MDL读锁，因此可以正常执行。
2、之后session C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。
3、之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。因为所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了。

由此得出结论：事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

====================================================================
如何安全地给小表加字段？

如果要做DDL（修改表结构等）变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。如果要变更一个热点表，比较理想的机制是，在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

4.3 行锁

行锁就是针对数据表中行记录的锁。MySQL的行锁是在引擎层由各个引擎自己实现的。

比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。

4.3.1 两阶段锁

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

4.3.2 死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

当出现死锁以后，有两种策略：

1、直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。（默认值为50s）
2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on（默认值为on），表示开启这个逻辑。

主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。死锁检测要耗费大量的CPU资源。

怎么解决由这种热点行更新导致的性能问题呢？
1、一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。

2、并发控制

5 MVCC

在MySQL里，有两个“视图”的概念：

· 一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view … ，而它的查询方法与表一样。
· 另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。

5.1 快照在MVCC里如何工作

InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。

数据表中的一行记录，其实可能有多个版本(row)，每个版本有自己的row trx_id。

图中表示三个事务对这一行数据进行修改，使该行存在四个版本

语句更新会生成undo log（回滚日志），即图中的三个虚线箭头（U1、U2、U3）。
而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来。

================================================================

InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID。“活跃”指的就是，启动了但还没提交。

数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。而数据版本的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的。

对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能:

1、如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2、如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3、如果落在黄色部分，那就包括两种情况
		a. 若 row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；
		b. 若 row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

因此之后的更新，生成的版本一定属于上面的2或者3(a)的情况，所以这个事务的快照，就是“静态”的了。InnoDB利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力。

===================================================================

分析为什么事务A查询的值是1，事务B查询的值是3？

对于事务A的查询逻辑来说：

事务A查询语句的读数据流程：

1、找到（1,3）版本，判断row trx_id=101 > 事务A的高水位100，不可见
2、找到（1,2）版本，判断row trx_id=102 > 事务A的高水位100，不可见
3、找打（1,1）版本，判断row trx_id=90 < 事务A的低水位90， 可见

即读到k值为1

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

===================================================================

更新逻辑

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。除了update语句外，select语句如果加锁，也是当前读。

select语句加读锁（S锁，共享锁）和写锁（X锁，排他锁）：

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

当前读必须要读最新版本，且必须要加锁

事务的可重复读的能力是怎么实现的？

可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

而读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

· 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
· 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。

· 对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
· 对于读提交，查询只承认在语句启动前就已经提交完成的数据；

6 MySQL

6.1 数据库为什么会“抖一下”

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。
内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

平时执行很快的更新操作，其实就是在写内存和日志，而MySQL偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

什么情况会引发数据库的flush过程呢？
场景一：InnoDB的redo log写满了。这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。

场景二：系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。如果刷脏页一定会写盘，就保证了每个数据页有两种状态：

· 一种是内存里存在，内存里就肯定是正确的结果，直接返回；
· 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。

场景三：MySQL认为系统“空闲”的时候。即使是系统忙碌的时候，也要见缝插针地找时间，只要有机会就刷一点“脏页”。

场景四：MySQL正常关闭的情况。这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

性能问题
第一种是“redo log写满了，要flush脏页”，这种情况是InnoDB要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。

第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

· 第一种是，还没有使用的；
· 第二种是，使用了并且是干净页；
· 第三种是，使用了并且是脏页。

InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

1、一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
2、日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。

所以，InnoDB需要有控制脏页比例的机制

InnoDB刷脏页的控制策略

通过innodb_io_capacity参数了来告诉InnoDB你的磁盘能力。该值应设置成磁盘的IOPS。
合理地设置innodb_io_capacity的值，并且平时要多关注脏页比例，不要让它经常接近75%。

InnoDB控制引擎按照“全力”的百分比来刷脏页的流程：
InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度。
参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。InnoDB会根据当前的脏页比例（假设为M），算出一个范围在0到100之间的数字，InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值，我们假设为N。InnoDB会根据这个N算出一个范围在0到100之间的数字，这个计算公式可以记为F2(N)。（N值越大，F2N计算出来的值就越大），根据上述算得的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

MySQL中的另一个机制：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

在InnoDB中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为1的时候会有上述的“连坐”机制，值为0时表示不找邻居，自己刷自己的。在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了。

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

一个InnoDB表包含两部分，表结构定义和数据。

参数innodb_file_per_table
表数据既可以存在共享表空间里，也可以是单独的文件。由innodb_file_per_table控制：

· OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起
· ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中（从MySQL 5.6.6版本开始，默认值为ON）

一个表单独存储为一个文件更容易管理，当不需要表的时候，通过drop table命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

6.2.1 数据删除流程

假设，删掉R4这个记录，InnoDB引擎只会把R4这个记录标记为删除。如果之后要再插入一个ID在300和600之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。如果删掉了一个数据页上的所有记录，整个数据页就可以被复用了。

但 数据页的复用跟记录的复用是不同的。

记录的复用，只限于符合范围条件的数据。比如上述例子，R4这条记录被删除后，如果插入一个ID是400的行，可以直接复用这个空间。但如果插入的是一个ID是800的行，就不能复用这个位置了。

当整个页从B+树里面摘掉以后，可以复用到任何位置。以上图为例，如果将数据页page A上的所有记录删除以后，page A会被标记为可复用。这时候如果要插入一条ID=50的记录需要使用新页的时候，page A是可以被复用的。

delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

不止是删除数据会造成空洞，插入数据也会。

如果数据是随机插入的，就可能造成索引的数据页分裂。

由于page A满了，再插入一个ID是550的数据时，就不得不再申请一个新的页面page B来保存数据了。页分裂完成后，page A的末尾就留下了空洞（注意：实际上，可能不止1个记录的位置是空洞）。

重建表

可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A里读出来再插入到表B中。把表B作为临时表，数据从表A导入表B的操作完成后，用表B替换A，从效果上看，就起到了收缩表A空间的作用。

使用alter table A engine=InnoDB命令来重建表。MySQL 5.5版本之前，这个临时表B不需要你自己创建，MySQL会自动完成转存数据、交换表名、删除旧表的操作。在整个DDL过程中，表A中不能有更新。即，这个DDL不是Online的。

在MySQL 5.6版本开始引入的Online DDL，操作流程：

1、 建立一个临时文件，扫描表A主键的所有数据页；
2、用数据页中表A的记录生成B+树，存储到临时文件中；
3、生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中state2的状态；
4、临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；
5、用临时文件替换表A的数据文件。

6.2.2 Online 和 inplace

在上图中，根据表A重建出来的数据是放在“tmp_file”里的，这个临时文件是InnoDB在内部创建出来的（在Server层）。整个DDL过程都在InnoDB内部完成。对于server层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

1、DDL过程如果是Online的，就一定是inplace的；
2、反过来未必，也就是说inplace的DDL，有可能不是Online的。截止到MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引(SPATIAL index)就属于这种情况。

=================================================================

使用optimize table、analyze table和alter table这三种方式重建表的区别

· 从MySQL 5.6版本开始，alter table t engine = InnoDB（也就是recreate）默认的就是上图的流程了；
· analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；
· optimize table t 等于recreate+analyze。	

6.2.3 count(*)

实现方式
在不同的MySQL引擎中，count(*)有不同的实现方式：

· MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高；
· InnoDB引擎执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

· MyISAM表虽然count(* )很快，但是不支持事务；
· show table status命令虽然返回很快，但是不准确；
· InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。

6.2.3.1 用缓存系统保存计数

使用Redis服务，但Redis的数据不能永久地留在内存里，所以需要找一个地方把这个值定期地持久化存储起来。但即使这样，仍然可能丢失更新（如Redis异常重启）

	Redis异常重启以后，到数据库里面单独执行一次count(*)获取真实的行数，再把这个值写回到Redis里就可以了。异常重启毕竟不是经常出现的情况，这一次全表扫描的成本，还是可以接受的。

将计数保存在缓存系统中的方式，还不只是丢失更新的问题。即使Redis正常工作，这个值还是逻辑上不精确的。下图是两种逻辑上不精确的情况：

在并发系统里面，无法精确控制不同线程的执行时刻的，因存在上图的这种操作序列，所以说即使Redis正常工作，这个计数值还是逻辑上不精确的。

6.2.3.2 在数据库保存计数

把这个计数直接放到数据库里单独的一张计数表C中。首先，这解决了崩溃丢失的问题，InnoDB是支持崩溃恢复不丢数据的。其次，解决计数不精确问题

如何解决计数不精确问题？
利用“事务”这个特性

虽然会话B的读操作仍然是在T3执行的，但是因为这时候更新事务还没有提交，所以计数值加1这个操作对会话B还不可见。因此，会话B看到的结果里， 查计数值和“最近100条记录”看到的结果，逻辑上就是一致的。

6.2.3.3 不同的count用法

在select count(?) from t这样的查询语句里面，count(*)、count(主键id)、count(字段)和count(1)等不同用法的性能

基于InnoDB引擎的。count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加。最后返回累计值。

count(*)、count(主键id)和count(1) 都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数。

分析性能差别的原则：

1、server层要什么就给什么；
2、InnoDB只给必要的值；
3、现在的优化器只优化了count(*)的语义为“取行数”，其他“显而易见”的优化并没有做。

对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

对于count(1)来说，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

对于count(字段)来说：

如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；
如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

但是count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加。

按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)

6.3 “order by”是怎么工作的?

全字段排序

假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄。SQL语句：select city,name,age from t where city='杭州' order by name limit 1000 ;

语句执行流程：

1、初始化sort_buffer，确定放入name、city、age这三个字段；

2、从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；

3、到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；

4、从索引city取下一个记录的主键id；

5、重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；

6、对sort_buffer中的数据按照字段name做快速排序；

7、按照排序结果取前1000行返回给客户端。

这个排序过程，称为全字段排序，执行流程示意图：

图中“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。

sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。
如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。
但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

内存放不下时，使用外部排序，外部排序一般使用归并排序算法。即，MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。

rowid排序

通过参数max_length_for_sort_data，控制用于排序的行数据的长度。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。SQL语句：SET max_length_for_sort_data = 16;

city、name、age 这三个字段的定义总长度是36，把max_length_for_sort_data设置为16，新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主键id。

执行流程为：

1、初始化sort_buffer，确定放入两个字段，即name和id；

2、从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；

3、到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；

4、从索引city取下一个记录的主键id；

5、重复步骤3、4直到不满足city='杭州’条件为止，也就是图中的ID_Y；

6、对sort_buffer中的数据按照字段name进行排序；

7、遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

全字段排序 VS rowid排序

如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

即，如果内存够，就要多利用内存，尽量减少磁盘访问。

6.4 如何正确地显示随机消息？

6.4.1 内存临时表

首先，你会想到用order by rand()来实现这个逻辑。

mysql> select word from words order by rand() limit 3;

表示随机排序取前3个。这条语句的执行流程是这样的：

1、创建一个临时表。这个临时表使用的是memory引擎，表里有两个字段，第一个字段是double类型，为了后面描述方便，记为字段R，第二个字段是varchar(64)类型，记为字段W。并且，这个表没有建索引。
2、从words表中，按主键顺序取出所有的word值。对于每一个word值，调用rand()函数生成一个大于0小于1的随机小数，并把这个随机小数和word分别存入临时表的R和W字段中，到此，扫描行数是10000。
3、现在临时表有10000行数据了，接下来你要在这个没有索引的内存临时表上，按照字段R排序。
4、初始化 sort_buffer。sort_buffer中有两个字段，一个是double类型，另一个是整型。
5、从内存临时表中一行一行地取出R值和位置信息，分别存入sort_buffer中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加10000，变成了20000。、
6、在sort_buffer中根据R的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
7、排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出word值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了20003。

流程图如下：

MySQL的表是用什么方法来定位“一行数据”的？
如果你创建的表没有主键，或者把一个表的主键删掉了，那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。（排序模式里面，rowid名字的来历）

每个引擎用来唯一标识数据行的信息：

· 对于有主键的InnoDB表来说，这个rowid就是主键ID；
· 对于没有主键的InnoDB表来说，这个rowid就是由系统生成的；
· MEMORY引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个rowid其实就是数组的下标。

order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法。

6.4.2 磁盘临时表

tmp_table_size这个配置限制了内存临时表的大小，默认值是16M。如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表。磁盘临时表使用的引擎默认是InnoDB，是由参数internal_tmp_disk_storage_engine控制的。

当使用磁盘临时表的时候，对应的就是一个没有显式索引的InnoDB表的排序过程。

采用是MySQL 5.6版本引入的一个新的排序算法，即：优先队列排序算法。
优先队列算法，就可以精确地只得到三个最小值，执行流程如下：

1、对于这10000个准备排序的(R,rowid)，先取前三行，构造成一个堆；

2、取下一个行(R’,rowid’)，跟当前堆里面最大的R比较，如果R’小于R，把这个(R,rowid)从堆中去掉，换成(R’,rowid’)；

3、重复第2步，直到第10000个(R’,rowid’)完成比较。

6.4.3 随机排序方法

如果只随机选择1个word值，

随机算法1 思路可以是：

1、取得这个表的主键id的最大值M和最小值N;

2、用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;

3、取不小于X的第一个ID的行。

SQL语句：

mysql> select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;

但ID中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。

随机算法2 思路：

1、取得整个表的行数，并记为C。

2、取得 Y = floor(C * rand())。 floor函数在这里的作用，就是取整数部分。

3、再用limit Y,1 取得一行。

SQL语句：

mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

尽量将业务逻辑写在业务代码中，让数据库只做“读写数据”的事情。

6.5 为什么这些SQL语句逻辑相同，性能却差异巨大？

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

6.6 为什么只查一行的语句，也执行这么慢？

第一类：查询长时间不返回

1、等MDL锁
使用show processlist命令查看Waiting for table metadata lock的示意图

2、等flush
使用show processlist命令查看Waiting for table flush的示意图

3、等行锁
如果你用的是MySQL 5.7版本，可以通过sys.innodb_lock_waits 表查到。
查询方法是：

mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

第二类：查询慢

坏查询不一定是慢查询

例子：只扫描一行，但是执行很慢的语句。

mysql> select * from t where id=1；

执行时间为800毫秒，

mysql> select * from t where id=1 lock in share mode

执行时间为0.2毫秒。

原因：
带lock in share mode的SQL语句，是当前读，因此会直接读到1000001这个结果，所以速度很快；而select * from t where id=1这个语句，是一致性读，因此需要从1000001开始，依次执行undo log，执行了100万次以后，才将1这个结果返回。