极客时间——MySQL实战45讲

时间：Nov. 27, 2020 分类：读书笔记

01 一条SQL查询语句是如何执行

分为server和存储引擎两部分

连接器

用户名密码通过之后，连接器会到权限表查询拥有的权限，权限就存储在连接里，之后的权限修改对这条连接不生效
如果长时间不使用就会断开连接，受wait_timeout控制的，默认值是8小时
连接过程比较复杂，要减少连接动作，但是使用长连接，连接使用的内存是属于连接的，需要断开连接释放，或者在5.7版本之后通过mysql_reset_connection来重新初始化连接资源，不会断开连接和重新鉴权

查询缓存

在8.0版本移除缓存

缓存是以查询语句为key，查询结果为value，缓存应用于表，当表更新缓存就会失效。

query_cache_type设置成DEMAND，对于默认的SQL不使用查询缓存，如果使用需要显式指定

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

分析器

对SQL语句进行解析，识别关键字，表，条件等，识别完成做语法分析，判断是否满足（例如列是否存在），不满足报You have an error in your SQL syntax，只会报第一个错误的位置

优化器

在表里有多个索引的时候，决定使用哪个索引，或者多表关联（join）的时候，决定各表之间的连接顺序

执行器

在执行前判断是否有表的权限，如果是使用了查询缓存也会在查询缓存前做权限判断，查询也会在优化器前做precheck权限验证

如果有权限，调用对应引擎，从第一行开始判断是否满足请求，满足则存在结果集，然后再调用引擎查询下一行，直到最后一行，遍历完返回结果集

慢查询的rows_examined字段就是语句扫描了多少次，有的调用一次

02 一条SQL更新语句是如何执行

更新语句会执行查询语句的流程，还会涉及redolog（重做日志）和binlog（归档日志）

更新的时候，

innoDB引擎（独有操作）会先写到redolog并更新内存，在适当时候写入磁盘。可以配置4个1GB文件循环写，writepos是写入位置，checkpoint是擦除位置。重启的时候也会从redolog获取记录
binlog是Server层实现

区别是

redolog记录的是数据页的修改，binlog记录的是执行的sql语句
redolog循环写入不能持久保存，binlog是追加

innoDB实现update语句

执行器到引擎取数据行，id为主键，直接通过树搜索找到这一行，如果在内存就直接返回给执行器，否则就从磁盘读入内存返回数据
执行器修改数据，调用引擎写入
引擎将数据更新到内存，同时写入redolog，此时redolog处于prepare状态，然后告知执行器完成，随时可以提交事务
执行器生成binlog，并把binlog写入磁盘
执行器调用引擎的事务提交接口，引擎将redolog改成commit状态，update完成

redolog分为了prepare和commit两个阶段，主要是为了防止使用binlog恢复数据与原库数据不符

prepare阶段
写binlog
commit 当在2之前崩溃时，重启恢复：后发现没有commit，回滚。备份：没有binlog，一致当在3之前崩溃时，重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

03 事务隔离

事务有ACID（原子性，一致性，隔离性和持久性），当多个事务执行的时候就会出现，脏读，不可重复读，幻读等问题，就有了隔离级别，隔离级别越高，效率就会越低

读未提交，事务没提交的时候，其他事务就能看到变更，上述过程v1开始就是2了
读提交，事务完成之后，其他事务才能看到变更，上述结果v1为1，v2和v3为2
可重复读，在事务处理的过程中看到的数据都是一致的，上述结果v1和v2为1，v3为2
串行化，事务串行提交，上述结果v1和v2为1，a事务完成才会执行b事务，然后查询v3，v3为2

可重复读可用于银行月底结算，在结算的时候看到的数据都是相同的，不妨碍其他的写入操作

实现是通过视图的方式，访问的时候以视图逻辑为准

读未提交，不使用视图
读提交，视图是在每个SQL开始执行的时候创建的
可重复读是在事务启动时创建，一直使用到事务结束
串行化是加锁的方式避免并行访问

尽量不要使用长事务，长事务会有很多的视图，在事务提交之前，数据库需要的回滚操作都会保留，占用大量内存空间，在MySQL5.5及以前版本，回滚日志数据字典都放在ibdata文件，事务提交也不会文件变小。并且长事务还占用锁

事务启动有两种

显示启动事务，begin或start transaction，配套的回滚是rollback，提交是commit
set autocommit=0，关闭自动提交，直到commit或rollback，或者断开连接

有些client甚至连接的时候就指定了set autocommit=0，所以查询使用使用set autocommit=1

如果提交并启动下一次事务使用commit work and chain，提交后会自动启动事务

查询超60s的事务

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

04 深入浅出索引(上)

索引的目的是提高查询的效率，常见的索引有哈希表，有序数组和搜索树

哈希表，有些key的哈希值相同，所以v是链表的形式，再根据详细信息在链表中查询，但是这样查询范围依然需要全表扫描，所以适合memcache和NoSQL
有序数据，如果是按照id顺序，那么2分查找，时间复杂度为O(log(N))，如果查找范围也是二分查找到开头，然后遍历查找结尾，退出，但是插入数据就会麻烦了，后边的都需要跟着挪，所以适合静态数据存储
二叉树，左节点小于父节点，右节点大于父节点，查询的时间复杂度也是O(log(N))，但是在插入数据的时候需要平衡二叉树，所以更新的时间复杂度也是O(log(N))

二叉树存100万行，树高就是20，访问一个数据需要访问20次数据块，一个磁盘随机读10ms，就是200ms了，对于数据库一般都是N叉树，对于InnoDB这个N就是1200，树高仅为4，查询一个数据最多要访问三次磁盘，树的第二次层大概率在缓存，访问磁盘的平均数量就很小了，并且N叉树也有了适配磁盘的访问模式

InnoDB使用了B+树索引模型，每个索引对应的就是一个B+树

主键索引的叶子节点是整行数据，在innoDB引擎称为聚簇索引
非主键索引的叶子节点是主键ID，在innoDB引擎称为二级索引

通过主键搜索只需要直接使用主键索引查询，而非主键搜索需要先查非主键索引获取到主键ID，然后在主键索引查询数据

当插入数据就需要索引维护，维护方式

在数据页结尾添加
在数据页中间添加，就需要数据页内的数据进行挪动
如果数据页满了，就需要进行页分裂，申请新的页，挪动部分数据过去，当然删除数据也会利用率低进行页合并的情况

有自增主键的数据，符合递增插入的场景，都是追加操作，就不会出现挪动的操作，也就不会有页分裂的情况。使用业务数据作为主键可能会有不能保证有序插入的情况，并且主键过长也会占用的索引空间就会越大

使用业务数据作为主键，适用的场景是

只有一个索引
索引为惟一值

那就是kv的数据库了

05 深入浅出索引(下)

在范围查询的时候，在查询非主键索引时获取到了主键ID，会去主键索引获取数据，（这个过程为回表），然后再回到非主键索引判断下一个是否满足，然后通过主键ID获取数据或者退出。但是如果查询的值为id，非主键索引的数据就直接满足了，不需要进行回表，被称为覆盖索引，是一个优化性能的方式

例如返回了2条数据，显示的是扫描了2行数据，但是实际上是扫描了三行，第三行进行了退出

对于a,b两列，经常有通过a查b的需求，可以建立联合索引(a,b)，索引内也有b，就不会有回表的操作

B+树的索引，利用索引的最左前缀原则，例如"a%"的查询方式也可以用索引，所以联合索引需要很好的安排联合索引的顺序，对于查询a可用(a,b)的索引，查询b再单独维护b的索引，然后就是考虑多个索引占用的空间问题了

索引下推

在5.6版本之后支持，索引下推可以在非主键索引进行一些条件判断，不满足就不进行回表了，会减少回表。在5.6版本之前是回表的时候做条件判断

06 全局锁和表锁

全局锁

全局锁是对数据库实例加锁，MySQL提供了一个全局读锁的方式，命令为

Flush tables with read lock (FTWRL)

执行后会造成部分语句的堵塞：

数据库更新语句（增删改）
数据定义语句（建表，修改表结构）
更新类事务的提交

使用场景是全库逻辑备份，就是把库里的数据select出来保存为文本

整库的只读

如果在主库上备份，就会造成不能执行更新，业务停摆
如是在从库上备份，就不能执行主库同步过来的binlog，导致主从延迟

但是当mysqldump执行的时候，使用了–single-transaction参数就会在导出数据的时候启动事务，来确保一致性视图，在这过程中数据是可以更新的，就是重复读的隔离事务。但是由于MyISAM不支持，所以需要加FTWRL命令

只是全库只读，可以使用set global readonly=true，但是会有两个问题

一些系统readonly会被设置为其他的逻辑，因此修改readonly可能会有影响
异常处理上的机制，FTWRL之后客户端断开连接，MySQL会释放这个全局锁，整个库回到正常状态，而设置readonly之后，连接断开依然不可写

表级锁

表级锁分为两种，表锁和元数据锁

表锁语法，也会在断开连接的时候释放锁，也可以使用unlock tables主动释放锁

lock tables … read/write

元数据锁在表锁执行的时候不需要显示指定就进行了增加，在MySQL5.5版本，引入了MDL，当需要对表做操作加MDL读锁，当对表结构修改的时候添加MDL写锁

读锁之间不互斥，可以多个线程对一张表进行增删改查
读写锁之间，和写锁之间是互斥的，来保障表结构的稳定性

事务中的MDL锁也会等事务提交之后才释放的

在给表加字段，修改字段，需要扫描全表，对大表进行操作的时候需要小心

对于一个热点表，数据量不大但是请求次数很多，kill长事务不能满足，因为新的请求又来了，可以采用设置alter语句的超时时间，拿不到锁就先放弃

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

07 行锁

行锁是在使用的时候加上，然后在commit的时候释放。

但是其他的事务需要修改的时候就会等待锁，所以这些操作尽量放在事务的最后进行，减少锁的等待。但是在并发大的情况，可能很多的线程都在等待锁，甚至互相依赖这些锁，就会造成死锁

出现死锁有两种策略

等待直至超时，通过innodb_lock_wait_timeout设置，默认为50s，设置太低可能会有误伤
进行死锁检测，发现死锁kill掉一个事务，设置innodb_deadlock_detect为on，但是引入了额外的负担，1000个事务检测量是100万的量级，造成没有执行几个事务cpu都满了

可以在业务保证不会有死锁的情况，然后关闭死锁检测。还可以就是控制并发，只有10个事务在执行，死锁检测的数据量较小

设计上可以将一行换成多行的方式，例如账户余额，可以设置多个余额的累加，例如10个记录，这样冲突概率就是原来的1/10，但是要考虑余额为0额外的操作

删10000条数据，可以循环执行20次delete from T limit 500

08 事务是否隔离

begin/start transaction不是事务的起点，而是第一个执行的表操作语句，如果是想立刻启动使用start transaction with consistent snapshot

事务的有唯一的transaction id，按照申请的顺序递增的。每行数据也是有多个版本的，事务更新数据就会生成一个新的版本，数据的row trx_id就是transaction id

所以事务启动的时候，启动后的数据版本就不会认，会寻找上一个版本，找不到就继续往前找，直到是等于或者小于数据版本。innodb还为事务构造了数组，用来保存事务启动的时候，所有在活跃（启动还未提交）的事务id，数组里最小的id为低水位，系统里创建的事务的最高的id+1为高水位，这个数组和高水位构成了一致性视图（read-view）

所以落在低水位和高水位之间的

row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见
row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见

所以可重复读的时候，只会读取低水位及其以下的数据，也就是一致性读

而当前读，就是读取的提交的最新版本，如果表不支持重复读，就只能当前读了。对于更新操作就是使用的当前读

读提交每次执行语句的时候都重新获取视图
可重复读是在启动的时候获取视图

update t set c=0 where id=c;

这种更新失败了，是因为条件的数据更新，导致更新失败

09 普通索引和唯一索引

在读的过程

普通索引查询到满足条件的还会继续查到第一个不满足
唯一索引定义了唯一性，查到第一个值就会停止检索

但是连着的数据都在一个页，也就是是多判断一下，性能消耗忽略不计，除非数据是页的最后一个数据

在写的过程

更新首先更新到changebuffer，就不需要从磁盘读取数据页了，在查询的时候将数据页读入内存，然后执行changebuffer中的有关的操作，这个过程是merge，后台线程也会定期进行merge。changebuffer也会写入到磁盘的。change buffer用的是buffer pool里的内存，通过innodb_change_buffer_max_size设置，例如50为使用buffer pool的50%。

唯一索引需要检测操作是否违反唯一性约束，不使用changebuffer

数据在内存页

唯一索引就是找到位置，判断没有冲突，插入值
普通索引就是找到位置，直接插入值

数据不在内存页

唯一索引需要将数据读入内存，判断没有冲突，插入值
普通索引直接更新到changebuffer

对于写多读少的业务，change buffer的使用效果最好，例如账单类，日志类数据，如果写入立刻被查询，就触发了merge，对IO的访问不会减少，还增加了维护changebuffer的代价

所以两种索引主要差距在更新上，普通索引使用会好一些，机械硬盘更推荐change buffer开大

change buffer和redo log有些类似

redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写）
change buffer主要节省的则是随机读磁盘的IO消耗

10 选错索引

如果在一个事务之后，执行了

delete from t;
call idata();
explain select * from t where a between 10000 and 20000;

就没有使用索引

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

Q1是原来的查询，但是走了全表扫描
Q2是指定了索引，只查询了10000行

这是因为优化器的逻辑，是使用最小的代价去执行语句，扫描行数只是求中一个因素，还有是否查询临时表，是否排序等因素判断

在执行之前，是不知道需要扫描的行数的，是通过估算的方式完成——采样统计，innodb默认会选择N个数据页，统计页面上的值，得到一个平均值，然后乘以索引的页面数，得到索引基数，随着数据表更新，行数超过1/M进行重新索引统计

统计方式通过innodb_stats_persistent配置

on的时候，统计信息持久化，N为20，M为10
off的时候，统计信息在内存中，N为8，M为16

但是explain Q1和Q2，分别是100000和30000，为什么选择了100000的呢，因为索引会回表进行查询，所以认为全表扫描代价更小，但是实际结果时间更长

可以通过analyze table t更正索引信息

在选择错误的时候

可以强制指定索引
删除被使用的无用索引
通过排序等方式修改语句

11 字符串设置索引

可以设置字符串索引的长度，保持一个合适的长度，可以即减少空间，有减少扫描的时间，例如邮箱后缀都一样的

对于前缀相同的，例如一个市的身份证，可以倒叙存储

mysql> select field_list from t where id_card = reverse('input_id_card_string');

使用hash字段，使用crc32()填充字段，但是hash字段可能会冲突，但是冲突概率非常小，需要加where再详细匹配

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

但是这样就都不支持范围查询了

12 flush堵塞写入

flush会将redolog中的记录删除，将内存中的数据页写入到磁盘，出现flush的情况有

redolog写满，就会对应的脏页都写入磁盘
内存不足，需要淘汰一些脏页
MySQL认为系统空闲
MySQL正常关闭

redolog写满会造成的就是更新会被堵住，内存是由buffer pool管理的，而内存不足有三种情况

内存未使用
内存使用，并且为干净的页
内存使用，并且为脏页

在读入数据的时候，数据页的数据没有在内存中，就在buffer poo申请数据页，需要将最久不用的内存页从内存中淘汰，如果是干净页，就直接使用，如果是脏页就需要刷写，但是在有的情况下会影响性能

一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长
日志写满，更新全部堵住，写性能跌为0

innodb_io_capacity可以控制脏页的刷新，建议配置为磁盘的IOPS，可以测试随机写

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

要时刻关注脏页的比例，innodb_buffer_pool_pages_dirty/innodb_buffer_pool_pages_total为脏页的比例

innodb_flush_neighbors控制连坐机制，就是如果需要刷新的数据页旁边为脏页也跟着进行刷新，但是这样查询时间会升高，在MySQL8.0参数默认值已经是0了

13 表数据删除表文件大小不变

innodb_file_per_table配置影响数据是存在单独的文件，还是系统的共享表空间，在MySQL5.6.6默认为On，在drop表的时候，系统会自动删除对应的文件，如果为Off，在drop的时候表空间不会回收

当删除一个数据，只是将数据标记为删除，如果插入数据可以在这个位置，就可以进行复用
当删除的数据都在一个数据页，整个数据页都可以被复用了，这个删除是在B+树上删除了

如果两个数据页利用率都很小，系统会将两个页数据合并到一个页上，另一个数据页被标记为可用。

如果删除表，所有的数据页都标记为可用。

插入数据也会造成空洞，例如随机插入的数据，会造成数据页分页

重建表的时候，数据会从原来的表顺序插入临时表，主键更紧凑，数据页的利用率更高，命令为alter table A engine=InnoDB(recreate)，但是在5.5版本，如果有数据写入，就会造成数据丢失，在5.6引入了Online DDL，创建B+树的临时文件，写入的时候也会写临时文件的日志文件rowlog，进行数据文件替换

DDL的时候是拿的MDL写锁，在拷贝数据的时候变为读锁，DDL锁主要是防止其他线程对这个表做DDL，但是对于大表很消耗CPU和内存，建议使用gh-os来做

analyze table t其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；
optimize table t等于recreate+analyze。

但是整理的时候，也会由于本来就紧凑，未整理之前页已经占用90%以上，收缩之后，还按照90%的比例预留10%给update导致整理后变大了

14 count(*)慢

对于MyISAM，数据行数存储在磁盘了，所以非常快，而Innodb需要每行都读取出来进行计数

因为Innodb涉及到多版本并发控制MVCC，所以应该返回的行号是不确定的。MySQL对count(*)做了些优化，遍历的时候遍历最小的树进行遍历。

show table status中的TABLE_ROWS是根据采样进行估算的，误差可能到40%~50%

这个counts可以自己维护计数，例如用缓存保存，但是缓存需要永久存储，而且在MVCC的情况也不能保证正确，所以要求准确还是需要在事务里进行count

count(1),count(*)，count(id)返回的是总行数，而count(字段)返回的是NULL的个数

遵循的原则

server层要什么给什么
Innodb只给必要的值

所以再分析

count(1)遍历整张表，不取值，server层放入1，判断是否为空，就累加
count(*)遍历整张表，不取值，不判断，直接累加
count(id)遍历整张表，将id取出来，server层判断id是不可能为空，就累加
count(字段)如果字段为not null，就会取出来判断不为null，进行了累加，如果字段为null，判断不是null的进行累加

15 日志和索引相关问题

binlog的完整如何确认？

statement格式的binlog，最后会有COMMIT
row格式的binlog，最后会有一个XID event

并且5.6.2版本引入了binlog-checksum参数

redo log和binlog是怎么关联起来的？

通过XID

为什么要有binlog和redolog？

因为起初innodb不是mysql的引擎，binlog不支持恢复数据页，redolog是循环写不能存储大量数据

16 orderby是如何工作的

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

查询城市为杭州的前1000个

select city,name,age from t where city='杭州' order by name limit 1000  ;

这里city是要有索引了，排序会使用线程的sort_buffer，流程为

初始化sort_buffer，放入city,name,age三个字段
city找到第一个满足city='杭州'条件的主键id
到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中
从索引city取下一个记录的主键id
重复步骤3、4直到city的值不满足查询条件为止
对sort_buffer中的数据按照字段name做快速排序
返回排序结果的前1000行

排序使用的sort_buffer由sort_buffer_size配置，如果超过之后会使用辅助文件

确认排序语句是否使用了临时文件

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

通过OPTIMIZER_TRACE的结果number_of_tmp_files来确认的，如果为0就是在内存中完成的

如果单行太大，就会造成排序的性能不好，可以使用SET max_length_for_sort_data = 16;控制排序的行数据长度

对于city、name、age这三个字段的定义总长度是36，设置参数为16，放入sort_buffer的是name和id，排序的也是name和id，最后返回数据的时候需要根据id再查询一下city、name、age这三个字段

对于Innodb，rowid会造成大量的读磁盘

使用索引的话

alter table t add index city_user(city, name);

这样拿到的数据其实就是排好序的了，但是数据不全需要回表，那就把数据补全

alter table t add index city_user_age(city, name, age);

explain命令中Extra字段里面为"Using index"而不是"Using filesort"

17 随机查询

示例随机推送3个单词

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

优化器会按照排序的行数越少越好使用rowid排序，执行流程为

创建一个临时表，使用的memory引擎，表里有两个字段，第一个为double类型，第二个为varchar(64)类型
会从words表中按照主键取出所有的word值，盗用rand()生成一个0到1的随机数，保存在临时表
初始化sort_buffer，然后进行排序
根据double类型字段排序，取出word
返回word到client

也就是扫描了count()2+3次

并非所有的临时表都是内存临时表，tmp_table_size限制了临时表的大小，默认为16M，如果超过了就转化为磁盘临时表，磁盘临时表的默认引擎为innodb，由internal_tmp_disk_storage_engine控制

复现问题

set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

在MySQL5.6版本引入了优先队列排序算法，而不是归并排序，只排需要的数据

优先队列排序算法通过堆的方式完成

随机排序方法

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的最大值M和最小值N;
用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
取不小于X的第一个ID的行。

但是这样有问题就是有的id的概率会高很多，并且不连续的id有不在的情况

可以生成连续的临时表，进行查询，连续的可以直接通过random+偏移量获取

18 SQL语句逻辑相同，性能却差异巨大

根因是条件进行函数转换

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查询语句虽然使用了t_modified索引，但是使用的mouth函数就不使用索引了，因为对索引做函数的操作，可能会破换索引的有序性，所以优化器选择放弃树搜索功能，但是不意味放弃了索引，只不过依然是遍历的索引

mysql> select count(*) from tradelog where month(t_modified)=7;

可以使用where t_modified='2018-7-1'的时候可以使用索引，

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

存在隐式转换

mysql> select * from tradelog where tradeid=110717;

tradeid的字段类型是varchar(32)，但是输入的是整型，所以要做类型转换，语句约等于

mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

或者连表但是两个表的字符集不一样

例如另一个表

mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询语句

mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

最好的解决方式修改表字符集，暂时不能解决就需要

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

19 查询一行数据也很慢

第一种就是查询长时间不返回，大概率是表被锁住了，可以执行show processlist查看语句的状态

等待MDL锁，会显示Waiting for table metadata lock，可以kill掉持有MDL锁的线程

通过查找sys.schema_table_lock_waits表（需要mysql启动的时候设置sys.schema_table_lock_waits，对比off有10%的性能损耗）

select blocking_pid from sys.schema_table_lock_waits;

等待flush，会显示Waiting for table flush

MySQL进行flush一般有两种

flush tables t with read lock;
flush tables with read lock;

等行锁，例如加读锁碰到了有行锁select * from t where id=1 lock in share mode;

对于MySQL5.7版本，可以通过sys.innodb_lock_waits表查到那个线程占用着写锁

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

再查询慢就是性能问题

没有使用索引，进行了全表扫描

20 幻读是什么，会有什么影响

如果不加锁的话，其他的事务修改就会影响查询的结果
如果加了一行锁，其他行被修改满足条件
如果都加了锁，但是新增的数据是没有锁的

innodb引入了间隙锁（GapLock）对锁住的行，还对行的两边间隙加了锁，间隙锁只和插入记录的操作冲突，但是间隙锁增大了死锁的可能

间隙锁和行组成了next-key lock，是一个前开后闭的区间

对于以下数据

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +suprenum]

当然间隙锁生效于可重复读，对于读提交，就不会有间隙锁了，但是会有数据和日志不一致的情况，需要加一下binlog格式设置为row

21 加锁

加锁的规则，原则上

加锁的基本单位为next-key lock
查询中访问到的对象才会加锁
索引上的等值查询，给唯一索引加锁，next-key lock会退化为行锁
索引上的等值查询，向右遍历时到最后一个不满足等值条件的时候，next-key lock会退化为间隙锁

案例1等值查询间隙锁

以20为例

sessionA	sessionB	sessionC
begin；update t set d=d+1 where id=7;
	insert into t values(8,8,8);(block)
		update t set d=d+1 where id=10;(Query OK)

根据加锁的基本单位为next-key lock，session A加锁范围就是(5,10]；
只是一个等值查询，id=7，而id=10的时候不满足查询条件变为间隙锁所以更新成功了

插入

22 MySQL快速提高性能

减少短链

但是会引入长链连接耗尽问题

max_connections用于控制存在的最大连接数，超过的连接就会报"Too many connections"，所以会处理掉一些不工作的连接，wait_timeout就是用于控制这个超时时间的，超过之后就断开。但是可能有的事务未提交就断开了

show processlist看到的Command为Sleep线程，通过information_schema库的innodb_trx查看是否在完成事务。

对于主动关闭的连接一定要通知开发

还有就是降低短链的连接消耗，在数据库重启可以设置–skip-grant-tables不进行权限验证直接开始接收请求，但是会有安全问题，在8.0版本默认了--skip-networking参数只有本地连接跳过鉴权

慢查询问题可能是三方面

索引没有设计好
SQL语句没有写好
MySQL选错了索引

索引没有设计好加上就行，通过ALTER TABLE的方式创建紧急生效。理想的情况是备库set sql_log_bin=off通过ALTER TABLE创建索引，主备切换，在执行一次

查询语句有问题在MySQL5.7提供了query_rewrite将输入语句换为另一种模式，例如call query_rewrite.flush_rewrite_rules();重写写入

选错索引可能是上边两者引起的，开没上线的时候就测试一下，打开慢查询，long_query_time设置成0模拟线上回归测试，看看Rows_examined是否和预期一致，pt-query-digest开源工具可以帮助检测

QPS突增问题

可能是业务高峰，也可能是业务bug，如果是bug让业务回滚

db这边将ip等加入黑名单
单独的用户就删掉用户断开对应连接
重写将SQL变为select 1

23 MySQL如何保证数据不丢

binlog写入机制

每个线程有一个binlog的cache，参数为binlog_cache_size，超过了就写磁盘，事务执行的时候写入binlogcache，事务提交的时候写到binlog文件，这个过程是先写到文件系统的pagecache，然后fsync到磁盘，这个过程是由sync_binlog控制

sync_binlog=0 每次提交只write，不fsync
sync_binlog=1 每次提交write并fsync
sync_binlog=N 每次提交只write，积累N个再fsync，一般会设置为100~1000，但是主机重启会丢失一部分binlog日志

redolog写入机制

redolog和binlog一样，有innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit=0，每次commit都将redolog写入buffer
innodb_flush_log_at_trx_commit=1，每次commit都将redolog写入磁盘
innodb_flush_log_at_trx_commit=2，每次commit都将redolog写入pagecache

innodb的后台线程，每隔1s都会将buffer中日志调用write写入pagecache，再调用fsync持久化到磁盘，一个没有提交的事务的redolog也会可能写入到磁盘

还有两种情况

redolog buffer占用的空间即将达到innodb_log_buffer_size一半的时候触发写盘
并行的事务提交的时候将事务的redolog写入磁盘

时序上redolog先prepare，再写binlog，最后再把redolog commit

通常sync_binlog和innodb_flush_log_at_trx_commit都为1，一个事务都需要提交两次，但是有group commit，多个写入合并为一个

组binlog的参数binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count

24 MySQL主备一致

备库

设置为readonly，可以用于数据查询
防止切换时双写
用readonly来判断节点角色

主库通过dump_thread将binlog传给从库从库io_thread接收写入到本地，为relaylog，sql_thread进行解析为命令写入数据库

binlog有三种格式statement和row，以及两者的混合模式mixed

statement记录的是操作命令原文，可能导致主从不一致
row记录操作的行主键id，但是占用空间大
mixed是为了解决以上两者的问题，自动判断会不会导致不一致

对于多主，不会存在循环复制的问题

两个库的id相同，不为主备关系
备库接收到binlog的之后，生成与原binlog的serverid相同的binlog
每个从库收到主库发来的日志，判断serverid，如果和自己相同就抛弃

所以A主库生成的，B主库依然使用A的serverid写入，A库接收的时候就进行抛弃了

25 MySQL如何保证高可用

在从库执行show slave status可以看到seconds_behind_master与主库差距的时间，计算方式是binlog上的主库写入时间和从库执行的时间的差值，并且在从库连接主库的时候通过SELECT UNIX_TIMESTAMP()确认与主库时间的差值，在计算延迟的时候去除

主备延迟的原因有

主备IOPS相同，却性能差距很大
备库压力大
存在大事务

在主从切换的时候

可靠性优先：会先将主变为readonly的，等待从库的seconds_behind_master为0，从取消readonly，再进行切换
可用性优先：直接进行切换，最好使用binlog_format=row方式有数据不一致的情况

高可用依赖的主从延迟，延迟越小恢复的时间越短，可用性就越高

26 备库为什么会延迟很久

在MySQL5.6版本之前，从库通过单线程进行复制，即sql_thread，现在被拆分为多个线程

coordinator就是当时的sql_thread，不再负责更新数据和读取中转日志和分发事务
worker线程用于更新日志，个数由slave_parallel_workers决定

但是事务不能通过轮询的方式给到每个线程，因为cpu调度可能会出现先执行后边的线程的情况，每个事务中的就更不能分给多个worker执行了。

conordinator主要是用于调度两种情况

不能造成更新覆盖，同一行的两个事务必须分发到一个worker
同一个事务必须要在一个worker

并行复制策略的演进

5.5版本，有按表分发策略，不同表的事务可以并行执行，每个worker对应一个hash表，冲突的事务就会进行等待，如果有热点表，就变成了单线程了
5.5版本，还有按行分发策略，只要两个事务间没有共同的行即可，这个模式要求binlog格式必须是row，对于连表的冲突判断就不是很好了，对操作多的数据很消耗cpu和内存(每行都需要进行hash表中存储id)
5.6版本，并行复制策略，按照的库级别并行分发，每个db的压力相同的话策略会非常好，hash表数据不会很大，也不要求binlog格式，mariaDB中，能够一组提交的事务，肯定没有修改相同的行，主库可以并发执行，从库也可以，所以同一组提交的数据，commit_id相同，且同一组不会修改相同的行，就会将并行执行了
5.7版本，MySQL支持MariaDB的并行方式，由slave-parallel-type参数控制并行复制策略，DATABASE为5.6的库级别，LOGICAL_CLOCK类似MariaDB的复制策略
5.7.22版本，MySQL新增了基于WRITESET的并行复制通过binlog-transaction-dependency-tracking来控制是否启用

5.7版本的并发控制，只要到了redolog的prepare阶段，就证明没有冲突了，binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数拉长了binlog的fsync的时间，就会存在更多的处于prepare阶段的事务，这样备库的性能可能会更好

5.7.22版本binlog-transaction-dependency-tracking的参数有

COMMIT_ORDER，同时进入prepare和commit来判断是否并行
WRITESET，对更新涉及的每一行计算hash值，判断事务是否有交集
WRITESET_SESSION，在WRITESET的基础上多了按照主库执行顺序的约束

这样的好处是writeset是在主库生成后直接写入到binlog里面的，备库不需要解析binlog，不用扫描事务的binlog决定分发，也不依赖binlog的格式

27 主库出问题了从库怎么办

配置主库

CHANGE MASTER TO 
MASTER_HOST=$host_name 
MASTER_PORT=$port 
MASTER_USER=$user_name 
MASTER_PASSWORD=$password 
MASTER_LOG_FILE=$master_log_name 
MASTER_LOG_POS=$master_log_pos

对于一主多从的。需要将一个从节点升级为主节点，并且将其他的从节点也指向新的主节点，同步的位点为

新主库同步完成
show master status获取最新的File和Position
获取原主库的故障时间
通过mysqlbinlog解析File中的故障时间的位点

在这个过程中会有一些冲突的语句，因为新主库没有执行，但是其他从库执行了，而changemaster之后从库又获取到了，有两种解决方式

主动跳过事务

set global sql_slave_skip_counter=1;
start slave;

设置slave_skip_errors参数，跳过指定错误

1062错误是插入数据时唯一键冲突；
1032错误是删除数据时找不到行。

在5.6版本MySQL引入了GTID，全局事务ID，启动方式为gtid_mode=on和enforce_gtid_consistency=on，格式为GTID=server_uuid:gno

server_uuid 一个实例第一次启动生成的，全局唯一值
gno 事务ID，每次提交+1

这样每个server都维护了一个GTID

gtid_next=automatic，生成binlog的时候先记录SET @@SESSION.GTID_NEXT=‘server_uuid:gno’;，并加入本地集合
gtid_next为指定值，已经存在的current_gtid就会被忽略，没有存在将这个current_gtid的命令执行

再基于GTID进行主备切换，通过master_auto_position=1，就不需要MASTER_LOG_FILE和MASTER_LOG_POS，通过基于GTID的集合比对，确定POS点位

28 读写分离

读写分离有

直连，主从切换等会有感知，排查问题容易
通过proxy代理，会造成排查困难

主从的架构在切换的时候会有一个过渡期，有很多方法

强制走主库
sleep方案
判断主从无延迟
配合semi-sync方案
等主库位点
等GTID方案

29 如何判断一个数据库出问题

set global innodb_thread_concurrency=3;

当有三个线程运行，select 1是可以的，但是select * from t查询功能是不能使用了

innodb_thread_concurrency默认为0，不限制，但是最好设置为64~128，这里是并发线程，不是连接和查询。等待事务提交，都不会是并发线程，因为已经空闲了，但是等待锁是算并发线程的，虽然占用cpu为0，但是不接收查询了

所以需要进行查表的判断，例如创建个表，每次查询一下来检测

当binlog磁盘满，就不能commit了，但是select是正常的，所以改成更新，对于检测主备的，也会有IO过高导致检测失败的情况，也可能是正常的

MySQL的5.6版本之后提供了performance_schema库的file_summary_by_event_name表，event_name='wait/io/file/innodb/innodb_log_file'这一行，COUNT_STAR是所有IO的总次数，SUM、MIN、AVG、MAX分别是总和，最大，最小和平均，单位皮秒，之后读，写和其他操作占的IO

如果要开启performance_schema的所有监控项，会有10%的性能损耗，例如打开redolog的监控

update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';

可以通过MAX_TIMER的值判断IO是否有问题

mysql> select event_name,MAX_TIMER_WAIT  FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;

发现异常清空数据重新统计

mysql> truncate table performance_schema.file_summary_by_event_name;

30 加锁

不等号条件里的等值查询

31 删库之后要怎么做

跑路

误删行

可以用Flashback工具恢复，原理是修改binlog的内容，拿到原库重放，要确保binlog_format=row和binlog_row_image=FULL，对于涉及多个行操作，就会按照执行顺序相反的顺序执行，恢复数据比较安全的做法，是恢复出一个备份，或者从库作为临时库，在临时库执行，确认数据无误恢复到主库

Flashback恢复方式

对于insert语句，对应的binlog event类型是Write_rows event，把它改成Delete_rows event即可；
对于delete语句，也是将Delete_rows event改为Write_rows event；
对于Update_rows的话，binlog里面记录了数据行修改前和修改后的值，对调这两行的位置即可。

误删库/表

这就需要使用全量数据+增量数据的恢复方式了

取出全量备份到临时库
从日志中获取备份后的操作，去掉误删操作

加速恢复，可以指定mysqlbinlog时的数据库。

对于原实例没有使用GTID模式的，–stop-position参数执行到误操作之前的日志，然后再用–start-position从误操作之后的日志继续执行，GTID直接指定gtid_next

延迟复制备库

在MySQL5.6版本引入，设置CHANGE MASTER TO MASTER_DELAY = N即可

预防方式

开发只有DML权限，DDL需求找DBA。DBA日常也用只读账号，必要的时候再登录更新账号。
删除表之前对表进行改名，观察影响
准备好备份脚本、执行脚本、验证脚本和回滚脚本

误删MySQL实例

跨机房备份

32 kill不掉的语句

kill有两种

kill query+id
kill connection+id

对于执行太久的就需要进行kill，例如等待锁。

收到kill命令，session会变为THD::KILL_QUERY，在线程执行到埋点的时候会判断状态变化，也就是在等待状态必须要唤醒才能被终止。即使kill掉连接之后，连接断开语句依然是处于执行状态的，因为流程是线程收到KILL_CONNECTION，然后断开连接的。这是因为没有到埋点所以没有停止。

还有就是终止逻辑时间较长，例如

超大事务回滚
大查询回滚，临时文件较大，文件系统压力大，删除临时表等待IO时间过长
DDL命令执行到最后阶段，中间临时文件较大

在连接客户端时候，连接建立很快的，但是client会生成补全，如果库表多也会影响性能，可以加-A取消补全，-q也可以，-q会采用本地不缓存的方式，会导致如果本地处理慢，导致server发送堵塞，让服务端变慢

33 查询数据会不会把MySQL的内存打爆

主机内存为100G，但是数据表为200G

全表扫描对Server层的影响

mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file

InnoDB的数据是保存在主键索引，实际上是扫描的主键索引，流程就是

获取一行，写到net_buffer中，由net_buffer_length决定，默认为16k
重复的获取行，直到net_buffer写满，通过网络接口发送出去
发送成功清空buffer，继续去读写入net_buffer_length
如果返回了EAGAIN或WSAEWOULDBLOCK，代表本地socket写满就进行等待网络栈可写，网络栈的buffer也是/proc/sys/net/core/wmem_default控制

MySQL的server是边度变法的，所以client接收慢也会影响事务，例如show processlist看到了Sending to client。--quick参数就是mysql_use_result读取一行处理一行，效率非常低，就会出现这种情况

一个查询语句的状态变化：

进入查询就设置为Sending data状态
发送执行结果的给client
继续执行
执行完后才能状态设置为空字符串

bufferpool中有WAL机制，可以用于保存更新结果，也可以加速查询，直接读内存，show engine innodb status命令获取的Buffer pool hit rate为缓存的命中率，命中率越高肯定是越好的，innodb_buffer_pool_size最好设置为物理内存的60~80%了

InnoDB对LRU，防止不常用数据的全表扫描导致的缓存失效，也是按照5：3分成了young和old区，根据生成到下次访问的时间innodb_old_blocks_time控制，默认为1000毫秒，就是1s

34 是否要用join

示例语句

select * from t1 straight_join t2 on (t1.a=t2.a);

执行流程

从t1读取数据
拿着数据中的a,去t2表中查找
取出t2中满足条件的行，与之前数据组成一行，存入结果集
重复执行1~3直到结束

如果我们不使用join也是这样一个流程，所以要选择小表做驱动表（from的为驱动表），因为驱动表进行的是全表扫描，但是前提是驱动表可以使用索引

对于有where条件的，小表为查询出来数据少的是小表

35 join语句的优化

Multi-Range Read优化(MRR)，尽量使用顺序读磁盘
在5.6版本引入了Batched Key Acess(BKA)，不是每次传递一行数去另一个表匹配，而是多个
BNL算法优化，调大join_buffer_size的值，防止join时频繁访问冷表导致缓存命中率失效
修改SQL，使用临时表的形式代替join

36 为什么临时表可以重命名

create temporary table temp_t like t1;
alter table temp_t add index(b);
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

创建临时表，可以使用各种的引擎的，特点是

只能由创建的session访问
可以与普通表重名，同名默认访问临时表
show tables不显示临时表

临时表的主要是为了解决复杂查询的优化

在分库分表的时候也可以通过临时表的方式汇聚数据

创建临时表的时候，需要创建表结构文件，文件名为sql{进程id}_{线程id}_序列号，对于不同的版本临时表也会被存放在不同的位置，主从同步的时候也会根据session的id同步为不同的临时表

37 为什么要使用内部临时表

示例语句

(select 1000 as f) union (select id from t1 order by id desc limit 2）

将两个语句的查询结果取并级

union的执行流程

创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段
执行第一个子查询，得到1000这个值，并存入临时表中
执行第二个子查询
从临时表中按行取出数据，返回结果，并删除临时表

select id%10 as m, count(*) as c from t1 group by m;

将数据按照id%10进行分组

group by执行流程

创建内存临时表，表里有两个字段m和c，主键是m
扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x，如果临时表中没有主键为x的行，就插入一个记录(x,1);如果表中有主键为x的行，就将x这一行的c值加1；
遍历完成后，再根据字段m做排序，得到结果集返回给客户

38 是否要使用Memory引擎

Innodb的数据放在主键索引树，索引组织表(Index Organizied Table)。而Memory的索引和数据是分开的，主键索引内为数据的位置，是堆组织表(Heap Organizied Table)，区别是

innodb的数据是有序存放的，内存表是按照写入顺序
当数据插入的是时候，innodb会找固定位置插入保证顺序，而内存表找到空位就插入
当数据位置发生变化的时候，innodb是需要修改主键索引，内存表需要修改所有索引
innoDB在使用主键索引的时候，查询一遍，而普通索引需要进行两遍索引，而内存表就没有区别，所有的索引都是相同的地位
innoDB支持长数据类型，内存表不支持Blob和Text字段，即使是定义了varchar(N)，也当做char(N)进行固定的长度存储

memory引擎也支持B-Tree索引

alter table t1 add index a_btree_index using btree (id);

但是内存表，只支持表锁，不支持行锁。并且不支持数据持久化，在重启后数据会被清空。一些用户的临时数据可以考虑使用内存表

39 自增主键不连续

表的自增值是不存在表结构中的，MYISAM引擎保存在数据文件中，而innodb引擎，在5.7版本自增值保存在内存中，重启的时候会自动去寻找最大id，将最大id+1作为后来的自增值。而在8.0版本存在了redolog中

对于未指定id的，都会使用自增id，如果指定了就使用，但是插入值的比自增值高就会把自增值变大了

auto_increment_offset和auto_increment_increment分别是自增的id起始值和步长

两个数据插入了相同的id，就会导致id不连续，而事务回滚也会。

自增id，只是为了避免主键冲突，还有两种方式，但是性能并不好

在5.0版本，是一个语句级别的自增锁，锁会等语句结束才释放，会影响并发
在5.1.22版本，引入了一个策略，innodb_autoinc_lock_mode，设置为0，为5.0版本一样，默认为1，普通的insert值申请后就释放，insert … select的批量插入，语句执行完释放，2为所有都申请完就释放

批量插入，语句执行完释放，也是防止主键id浪费

40 insert语句为什么有这么多锁

insert…select是很常见的在两个表之间拷贝数据的方法。你需要注意，在可重复读隔离级别下，这个语句会给select的表里扫描到的记录和间隙加读锁。

如果insert和select的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。

insert语句如果出现唯一键冲突，会在冲突的唯一值上加共享的next-key lock(S锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

41 如何快速复制一张表

mysqldump

mysqldump -h$host -P$port -u$user --add-locks --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

--single-transaction导出的时候加锁
--add-locks导出的结果不需要LOCK TABLES t WRITE;
--no-create-info导出的时候不要表结构
--set-gtid-purged=off不输出GTID相关信息
--result-file文件输出路径

如果希望是一条一条的insert语句，需要使用--skip-extended-insert

导出CSV

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

生成的文件在服务端，然后再导入

load data infile '/server_tmp/t.csv' into table db2.t;

流程是，打开文件，启动事务，执行插入，如果有问题会进行回滚

物理拷贝

在5.6版本，可以导入导出表空间方式，然后拷贝.frm文件和.ibd文件

执行create table r like t，创建一个相同表结构的空表；
执行alter table r discard tablespace，这时候r.ibd文件会被删除；
执行flush table t for export，这时候db1目录下会生成一个t.cfg文件；
在db1目录下执行cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令；
执行unlock tables，这时候t.cfg文件会被删除；
执行alter table r import tablespace，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据。

物理拷贝是最快的了，但是必须是全部数据。mysqldump可以where条件。select … into outfile的方法是最灵活支持的方式的多，但是也只能导出一个表，并且没有表结构

42 grant之后要跟着flushprivileges吗

create user 'ua'@'%' identified by 'pa';

磁盘上，往mysql.user表里插入一行，由于没有指定权限，所以这行数据上所有表示权限的字段的值都是N；
内存里，往数组acl_users里插入一个acl_user对象，这个对象的access字段值为0。

设置全局权限

grant all privileges on *.* to 'ua'@'%' with grant option;

磁盘上，将mysql.user表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为‘Y’；
内存里，从数组acl_users中找到这个用户对应的对象，将access值（权限位）修改为二进制的“全1”。

grant命令执行完成，新ua用户建立连接，也是查询的acl_user表

flush privileges语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。而这种不一致往往是由于直接用DML语句操作系统权限表导致的，所以我们尽量不要使用这类语句

43 要不要使用分区表

创建分区表

CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);

这个表包含了一个.frm文件和4个.ibd文件，就是在Server层是一个表，在引擎层是4个表，锁表也是锁的一个

分区表不能过多，有open_files_limit限制，MyISAM在第一次访问分区表会访问所有的分区

MySQL5.7.9开始，InnoDB引擎引入了本地分区策略

Server层看是一个表

MySQL在第一次打开分区表的时候，需要访问所有的分区；
在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；
在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。

44 说一说这些好问题

略

45 自增id用完怎么办

当表定义自增id，如果满了默认就是只更新最后一个id的数据

innodb没有指定主键，会生成6字节的row_id，数据表的id范围0到2^48-1，达到上线下个值就为0然后循环换

Xid（redolog和binlog对应的id）的id为2^64，基本不会达到

innodb的trx_id为事务间可视id，为2^48，重启的之后也不会重置

thread_id为2^32-1，到了之后会变为0

火眼征信大数据工程师闫大佬