mysql 45 讲学习

 数据库45讲
     01|基础架构:一条sql查询语句是如何执行的
         连接器
             权限 -> wait_timeout默认8小时
                 定期断开长连接
                 5.7版本后执行mysql_reset_connection
         查询缓存
             命中低
                 quety_cache_type=demand
                     mysql8.0移除查询缓存
         分析器
             语法分析
         优化器
             索引
             关联
         执行器
     02|日志系统:一条sql更新语句是如何执行的
         redo log(重做日志)
             wal: 先写日志再写磁盘
             固定大小,循环写磁盘
                 checkpoint/write pos
             innodb特有
         bin log(归档日志)
         两阶段提交
             写redolong 除于prepare阶段
                 写binlog
                     提交事务,保证两个文件已经写完
         innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘
         sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘
     03|事务隔离:为什么你改了我还看不见
         innodb
             隔离性
                 你隔离得越严实，效率就会越低
                 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到
                 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到
                 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的
                 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行
                 原理,数据库的多版本并发控制（MVCC）
                 你尽量不要使用长事务
     04|深入浅出索引(上)
         常见索引模型: 哈希,有序数组,搜索树
             哈希:挂一个链表
                 适用于只有等值查询的场景
             有序数组
                 在等值查询和范围查询的场景中性能都非常优秀
                     二分法查找
                     只适用于静态存储引擎
             二叉树搜索效率高但是磁盘查询慢
                 n叉树
         跳表/lsm树
         innodb
             表根据主键顺序以索引的形式存放
                 每个索引对应一颗b+树
             主键索引
                 叶子节点存的是整行数据,聚簇索引
                     尽量使用主键查询
                         主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小
             非主键索引
                 叶子节点存放的是主键的值,二级索引
                     先查询到索引所在的id,再根据id走主键索引查询--->回表
                         业务字段直接做主键的场景:只有一个索引,该索引必须是唯一索引(KV场景)
         如何避免长事务
             应用端
                 测试环境开启general_log,查看set autocommit
                 确定是否有不必要的只读事务
                 设置 SET MAX_EXECUTION_TIME 控制每个语句执行的最长时间
             数据库端
                 监控information_schema.Innodb_trx表, 设置长事务阈值,超过就报警
                 innodb_undo_tablespaces设置成2
                     不为0表示使用独立的表空间
     05|深入浅出索引(下)
         索引覆盖
             指定查询id的值
             联合索引
                 根据身份证号查询他的姓名(身份证+姓名)
         最左前缀原则
             最左前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符
             第一原则是,如果通过调整顺序可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的
             空间原则,(a,b),语句只查询b无法命中索引,需要新建b的索引,同时考虑字段大的作为左边的索引
         mysql5.6之后,索引下推
             回表时在索引内部就判断了主键索引记录中的条件,避免了无效的回表
         重建索引
             重建普通索引是合理的,重建主键过程不合理(alter table T engine=InnoDB)
                 不论是删除主键还是创建主键,都会将整个表重建
     06|全局锁和表锁
         全局锁
             加全局读锁的方法(Flush tables with read lock)
             mysqldump, -single-transaction, 导数据之前就会启动一个事务,来确保拿到一致性视图
             客户端断开会自动释放这个全局锁
             主要用在逻辑备份过程中
         表级锁
             表锁(innodb一般不使用)
                 lock tables ... read/write 除了会限制别的线程的读写外,也限定了本线程接下来的操作对象
                 一般是数据库引擎不支持行锁的时候才会被用到
             元数据锁(mysql5.5版本之后)
                 保障读写的正确性
                 读锁之间不互斥,读写锁之间,写锁之间是互斥的
                 如何安全的给小表加字段
                     alter table tb1_name NOWAIT add column ...
                     alter table tb1_name WAIT add column ...
     07|行锁
         innodb支持行锁
         两阶段锁协议
             并不是不需要了就立即释放,而是要等事务结束时才释放
             事物中需要锁多个行,要把最可能造成锁冲突,最可能影响并发的锁尽量往后放
         死锁和死锁检测
             超时时间:通过参数innodb_lock_wait_timeout来设置(默认是50s)
             主动回滚锁链条中的某一个事务,参数innodb_deadlock_detect设置为on(默认为on)
         控制并发
             使用中间件
             修改mysql,进入引擎前排队
         排它锁(for update)
             当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新
         共享锁(lock in share mode)
             允许其它事务也增加共享锁读取 不允许其它事务增加排他锁 (for update) 当事务同时增加共享锁时候，事务的更新必须等待先执行的事务 commit 后才行，如果同时并发太大可能很容易造成死锁
     08|事务到底是隔离还是不隔离的
         视图
             view
             一致性视图
         快照
             启动事务时打快照(使用事务id)
                 一行记录会有多个版本
             更新数据都是先读后写,只能读当前的值,称为当前读
                 select语句如果加锁,也是当前读
                     select k from t where id = 1 lock in share mode / select k from t where id = 1 for update
         innodb的行数据有多个版本,每个数据版本有自己的row trx_id,每个事物或者语句有自己的一致性视图,普通查询语句是一致性读,一致性读会根据row trx_id和一致性视图确定数据版本的可见性
         对于可重复读,查询只承认在事务启动前就已经提交完成的数据
         对于读提交,查询只承认在语句启动前就已经提交完成的数据
         当前读,总是读取已经提交完成的最新版本
     09|普通索引和唯一索引
         查询差异: 性能微乎其微
         更新差异:change buffer(普通索引) / merge
             用的是buffer pool里的内存,通过参数innodb_change_buffer_max_size来动态设置,设置50表示用50%
         写多读少的业务,change buffer的使用效果最好
             账单类,日志类的系统
                 加大change buffer
         建议尽量选择普通索引
         redo log主要节省的是随机写磁盘的io消耗(转成顺序写),而change buffer主要节省的则是随机读磁盘的io消耗
     10|mysql为什么有时候会选错索引
         不断地删除历史数据和新增数据的场景
         索引的区分度,索引上不同的值越多,索引的区分度就越好,show index from t; 索引上不同的值的个数,称为基数,(cardinality)基数越大,索引的区分度越好
         当变更的数据行数超过1M的时候,会自动触发重新做一次索引统计
         analyze table t; 可以用来重新统计索引信息
         解决方法
             强制索引选择(force index)
             修改语句,引导mysql使用期望的索引,order by b limit1 改成 order by b,a limit 1
         show table status like 't'\G
             table_rows通过采样估算,误差达到40%-50%
     11|怎么给字符串字段加索引
         使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本
             select count(distinct left(email)) as L from SUser
         前缀索引对覆盖索引的影响
             使用前缀索引就用不上覆盖索引对查询性能的优化
         倒序存储,再创建前缀索引
             用于绕过字符串本身前缀的区分度不够的问题,不支持范围扫描
         创建hash字段索引,查询性能稳定,有额外的存储和计算消耗,不支持范围扫描
     12|数据库抖动
         当内存数据跟磁盘数据页内容不一致的时候,我们称这个内存页为脏页
         redo log写满了
             不接收更新
         内存满了
             写入磁盘
                 从缓冲池里的内存申请数据页,如果是脏页需要刷到磁盘
                     脏页多,响应时间长
             innodb刷脏页的控制策略
                 innodb_io_capacity告诉innodb你的磁盘能力,设置成iops (通过fio这个工具来测试 fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest)
                 刷盘速度参考因素
                     1,脏页比例 2,redo log 写盘速度
                 脏页比例查询(不要让它经常接近75%)
                     select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty'; select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'; select @a/@b;
                 mysql8.0 中,innodb_flush_neihbors参数的默认值已经是0了,机械磁盘可以设置成1,相邻的数据页是脏页会被一起刷掉(查询会变慢,但是可以提升io性能)
         空闲时刻
         正常关闭
     13|为什么表数据删掉一半,表文件大小不变
         innodb表包含两个部分
             表结构定义和数据
         表数据
             innodb_file_per_table 默认值是on, 推荐设置成on(off表示表的数据放在系统共享表空间,on表示每个innodb表数据存在一个以.ibd为后缀的文件中,drop table时会被一起删除)
             删除/插入数据行会标记页为可复用,不能回收表空间,没有被复用的页或者随机插入的数据会造成索引的数据页分裂
             重建表
                 online ddl 重建的时候允许增删改
                     对于大表来说很消耗io和cpu资源,比较安全的操作推荐使用github的gh-ost来做
                 ddl过程如果是online的,就一定是inplace的;如果是inplace的,有可能不是online的
                 optimize table t 等于recreate(alter table t engine=InnoDB) + analyze(MDL读锁)
     14|count(*)这么慢,我该怎么办
         不同的mysql引擎中,coun(*)有不同的实现方式
             myism引擎存在了磁盘上,效率高(加了where条件没有那么快)
             innodb需要把数据一行一行的从引擎里面读出来,然后累积计数
         如何记录总数
             自己计数
                 使用计数表
                     先插入数据表,再更新计数表
         count(*),count(主键id),count(1)都表示返回满足条件的结果集的总行数; count(字段)则表示返回满足条件的数据行里面,参数字段不为null的总个数
         count(*)≈count(1) > count(主键id)>count(字段)
             尽量使用count(*)
     15|日志和索引相关问题
         日志相关问题
             prepare阶段之后,写binlog之前发生崩溃,事务会回滚
             binlog写完,redo log还没commit前发生崩溃 判断事务是否完整(statement格式最后会有commit,row格式最后会有一个xid event,引入binlog-checksum参数), 如果完整则提交事务,否则回滚事务
             redo log 大小,几T的磁盘,可以设置成4个文件,每个文件1G
         索引相关问题
     16|order by 是怎么工作的
         执行计划的extra的Using filesort表示的就是需要排序
         sort_buffer_size，就是MySQL为排序开辟的内存（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;
         max_length_for_sort_data
             是MySQL中专门控制用于排序的行数据的长度的一个参数。 它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法(rowid)
                 rowid排序会要求回表多造成磁盘读，因此不会被优先选择
         使用联合索引
             使用覆盖索引
     17|如何正确地显示随机消息
         order by rand()
             内存临时表
                 order by rand()使用了内存临时表,内存临时表排序的时候使用了rowid排序方法
                 对于有主键的innodb表来说,这个rowid就是主键id 对于没有主键的innodb表来说,这个rowid就是由系统生成的
             磁盘临时表
                 tmp_table_size这个配置限制了内存临时表的大小，默认值是16M。 如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表
                 优先队列排序算法
         随机算法
             select count(*) into @C from t; set @Y1 = floor(@C * rand()); set @Y2 = floor(@C * rand()); set @Y3 = floor(@C * rand()); select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行 select * from t limit @Y2，1； select * from t limit @Y3，1；
     18|为什么这些sql语句逻辑相同,性能却差异巨大
         条件字段函数操作
             对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能
         隐式类型转换
             字段是varchar,比较却是整数,触发条件字段函数规则
             字段是整数,比较却是varchar,走索引
         隐式字符编码转换
             字符集不一致
                 改字符集
                 主动转换为相同的字符集
     19|查询一行的语句也慢
         查询长时间不返回
             show processlist 查看当前语句处于什么状态
             等DML锁Waiting for table metadata lock)
                 mysql启动时需要设置performance_schema=on(性能会损失)
                     select blocking_pid from sys.schema_table_lock_waits;
                         kill pid
             等flush
                 show processlist
             等行锁
                 mysql5.7
                     select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G
                         KILL 4
         查询慢
             等值查询字段没有加索引
     20|幻读
         幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行
             幻读仅专指“新插入的行”
         行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的间隙
             间隙锁(在可重复读隔离级别下才会生效)
             容易引起死锁
         间隙锁和行锁合称next-key lock
             前开后闭区间
     21|锁为什么这么多
         两个原则
             加锁的基本单位是next-key lock
             查找过程中访问到的对象才会加锁
         两个优化
             索引上的等值查询,给唯一索引加锁的时候,next-key lock退化为行锁
             索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock退化为间隙锁
         一个问题
             唯一索引上的范围查询会访问到不满足条件的第一个值为止
         等值查询间隙锁
         非唯一索引等值锁
         主键索引范围锁
         非唯一索引范围锁
         唯一索引范围锁问题
         非唯一索引上存在等值的例子
         limit语句加锁
             删除数据的时候尽量加limit
         一个死锁的例子
             next-key lock 先加间隙锁再加行锁
     22|mysql有哪些饮鸩止渴提高性能的方法
         短连接风暴
             先处理掉那些占着连接但是不工作的线程
                 从数据库端主动断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。 这会导致从应用端看上去，“MySQL一直没恢复”
             减少连接过程的消耗
         慢查询性能问题
             索引没有设计好
                 紧急处理:online ddl / 备库先执行
                     在备库B上执行 set sql_log_bin=off，也就是不写binlog，然后执行alter table 语句加上索引； 执行主备切换； 这时候主库是B，备库是A。在A上执行 set sql_log_bin=off，然后执行alter table 语句加上索引
             sql语句没有写好
                 改写规则
                     mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1"); call query_rewrite.flush_rewrite_rules();
                     执行原来的查询后,show warnings;
             mysql选错了索引
                 force index
             检查所有的sql语句的返回结果
                 pt-query-digest
                     https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html
             qps突增
     23|mysql是怎么保证数据不丢的
         binlog的写入机制
             begin -> binlog cache ->commit -> binlog cache into binlog
             binlog_cache_size用于控制单个线程内binlog cache所占内存的大小
             write into page cache -> fsync into disk
                 sync_binlog=0的时候，表示每次提交事务都只write，不fsync； sync_binlog=1的时候，表示每次提交事务都会执行fsync； sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync
                 在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能
                 在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值
                 但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志
         redo log写入机制
             innodb_flush_log_at_trx_commit
                 设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中; 设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘； 设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。
                 每隔一秒,就会把redo log buffer中的日志写到page cache,然后调用fsync持久化到磁盘
                 没有提交的事务的redo log写入磁盘
                     redo log buffer占用的空间即将达到 innodb_log_buffer_size一半的时候，后台线程会主动写盘
                     并行的事务提交的时候，顺带将这个事务的redo log buffer持久化到磁盘
             组提交（group commit）机制
                 日志逻辑序列号（log sequence number，LSN）
                     LSN是单调递增的，用来对应redo log的一个个写入点。每次写入长度为length的redo log， LSN的值就会加上length
                 binlog也会组提交
                     binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用fsync binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync 或的关系,设置了都会触发
         提升io性能
             设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count参数，减少binlog的写盘次数。 这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险
             将innodb_flush_log_at_trx_commit设置为2。这样做的风险是，主机掉电的时候会丢数据
                 设置成0性能差不多,风险小
             通常我们说MySQL的“双1”配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成 1
             非双1场景
                 业务高峰期。一般如果有预知的高峰期，DBA会有预案，把主库设置成“非双1”。 备库延迟，为了让备库尽快赶上主库。@永恒记忆和@Second Sight提到了这个场景。 用备份恢复主库的副本，应用binlog的过程，这个跟上一种场景类似。 批量导入数据的时候
                     一般情况下，把生产库改成“非双1”配置，是设置innodb_flush_logs_at_trx_commit=2、sync_binlog=1000
     24|mysql怎么保证主备一致的
         主备的基本原理
             同步更新
             备库推荐设置成只读
         维持长连接
         binlog的三种格式对比
             statement
                 show binlog event in 'master.000001'
                 delete语句带limit,很可能会出现主备数据不一致的情况(多个普通字段带索引)
             row
                 借助mysqlbinlog工具
                     mysqlbinlog  -vv data/master.000001 --start-position=8900;
                 delete带lilmit不会出现主备数据不一致
                     binlog里面记录了真实删除行的主键id
                 设置为row的理由
                     恢复数据
                         Flashback工具
             mixed
                 有些statement格式的binlog可能会导致主备不一致，所以要使用row格式
                 但row格式的缺点是，很占空间
                 MySQL自己会判断这条SQL语句是否可能引起主备不一致，如果有可能，就用row格式，否则就用statement格式
         循环复制问题
             规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系； 一个备库接到binlog并在重放的过程中，生成与原binlog的server id相同的新的binlog； 每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志
                 从节点A更新的事务，binlog里面记的都是A的server id； 传到节点B执行一次以后，节点B生成的binlog 的server id也是A的server id； 再传回给节点A，A判断到这个server id与自己的相同，就不会再处理这个日志。所以，死循环在这里就断掉了
             三节点复制场景
                 数据迁移导致 B -> A <->A'
                     stop slave； CHANGE MASTER TO IGNORE_SERVER_IDS=(server_id_of_B); start slave;
                     stop slave； CHANGE MASTER TO IGNORE_SERVER_IDS=(); start slave;
     25|mysql是怎么保证高可用的
         主备延迟
             show slave status
                 seconds_behind_master
                     当前备库延迟了多少秒
                     主备库机器不一致不会导致主备延迟的值不准
             来源
                 备库非双1模式,大量读
                 主从规格一致,对称部署
                     比较常见
                 备库压力大
                     一主多从
                         大事务会导致延迟
                             不要一次性的用delete删除太多数据
                         大表ddl
                     通过binlog输出到外部系统
                 备库的并行复制能力
             可靠性优先策略
                 切换读写开关
                     建议使用
             可用性优先策略
                 直接切换
                     binlog_format=mixed
                         数据不一致
                     binlog_format=row
                         会记录新插入的行的所有字段值
                             报主键冲突然后不被执行
                                 数据只有一行不一致
                 可用场景: 日志系统
             备库延迟监控
     26|备库为什么会延迟小时级
         备库执行日志的速度持续低于主库生成日志的速度
         备库并行复制能力
             slave_parallel_workers
                 设置成8-16之间最好(32核物理机的情况)
         2012年版本
             按表分发/按行分发
                 binlog格式必须是row
                 表必须有主键
                 不能有外键
         mysql5.6版本
             按库分发
         mariaDB版本
             在一组里面一起提交的事务，有一个相同的commit_id，下一组就是commit_id+1； commit_id直接写到binlog里面； 传到备库应用的时候，相同commit_id的事务分发到多个worker执行； 这一组全部执行完成后，coordinator再去取下一批
                 在备库上执行的时候，要等第一组事务完全执行完成后，第二组事务才能开始执行，这样系统的吞吐量就不够。 另外，这个方案很容易被大事务拖后腿。假设trx2是一个超大事务，那么在备库应用的时候，trx1和trx3执行完成后，就只能等trx2完全执行完成，下一组才能开始执行。这段时间，只有一个worker线程在工作，是对资源的浪费
         mysql5.7版本
             slave-parallel-type来控制并行复制策略
                 database表示使用mysql5.6版本的按库并行策略
                 logical_clock表示的就是类似mariadb的策略
                     同时处于prepare状态的事务，在备库执行时是可以并行的； 处于prepare状态的事务，与处于commit状态的事务之间，在备库执行时也是可以并行的。
         mysql5.7.22的并行复制策略
             基于writeset
                 binlog-transaction-dependency-tracking参数控制是否启用这个策略
                     COMMIT_ORDER，表示的就是前面介绍的，根据同时进入prepare和commit来判断是否可以并行的策略。 WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的hash值，组成集合writeset。如果两个事务没有操作相同的行，也就是说它们的writeset没有交集，就可以并行。 WRITESET_SESSION，是在WRITESET的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。
                         主库都是单线程模式推荐WRITESET
                 支持binlog是statement格式
                 表必须有主键
                 不能有外键
         开发中尽量减少大事务
     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 
             GTID(全局事务id)
                 启动mysql实例的时候,加上参数gtid_mode=on和enforce_gtid_consistency=on
                     每个事务都跟gtid一一对应
                         set gtid_next='aaaaaaaa-cccc-dddd-eeee-ffffffffffff:10'; begin; commit; set gtid_next=automatic; start slave;
             基于gtid的主备切换
                 CHANGE MASTER TO  MASTER_HOST=$host_name  MASTER_PORT=$port  MASTER_USER=$user_name  MASTER_PASSWORD=$password  master_auto_position=1 
                 主备切换不需要找位点
             gtid和在线ddl
                 gtid不需要关闭binlog
     28|读写分离有哪些坑
         客户端主动做负载均衡
             由客户端来选择后端数据库进行查询
         中间代理层proxy(趋势)
         处理过期读的问题
             强制走主库方案
                 对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库。  对于可以读到旧数据的请求，才将其发到从库上。在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的。那么，这类请求就可以走从库
                 金融类业务只能读写在主库
             sleep方案
                 主动sleep
                     如果这个查询请求本来0.5秒就可以在从库上拿到正确结果，也会等1秒； 如果延迟超过1秒，还是会出现过期读。
                         不准确
             判断主备无延迟方案
                 判断seconds_behind_master是否已经等于0,等于0才能执行查询请求
                     不准确
                 对比定位点和gtid
                     如果Master_Log_File和Relay_Master_Log_File、Read_Master_Log_Pos和Exec_Master_Log_Pos这两组值完全相同，就表示接收到的日志已经同步完成
                 对比GTID集合确保主备无延迟
                     Auto_Position=1 ，表示这对主备关系使用了GTID协议。 Retrieved_Gtid_Set，是备库收到的所有日志的GTID集合； Executed_Gtid_Set，是备库所有已经执行完成的GTID集合
                         如果这两个集合相同，也表示备库接收到的日志都已经同步完成
             配合semi-sync方案
                 半同步复制
                     事务提交的时候，主库把binlog发给从库 从库收到binlog以后，发回给主库一个ack，表示收到了 主库收到这个ack以后，才能给客户端返回“事务完成”的确认
                         一主多从的时候，在某些从库执行查询请求会存在过期读的现象； 在持续延迟的情况下，可能出现过度等待的问题
             等主库位点方案
                 trx1事务更新完成后，马上执行show master status得到当前主库执行到的File和Position 选定一个从库执行查询语句 在从库上执行select master_pos_wait(File, Position, 1) 如果返回值是>=0的正整数，则在这个从库执行查询语句 否则，到主库执行查询语句
             等GTID方案
                 trx1事务更新完成后，从返回包直接获取这个事务的GTID，记为gtid1 选定一个从库执行查询语句 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1) 如果返回值是0，则在这个从库执行查询语句 否则，到主库执行查询语句
                     只需要将参数session_track_gtids设置为OWN_GTID，然后通过API接口mysql_session_track_get_first从返回包解析出GTID的值即可
                         https://dev.mysql.com/doc/refman/5.7/en/c-api-functions.html
                 你要使用这个方案的时候，还是应该在你的客户端代码中调用mysql_session_track_get_first这个函数
     29|如何判断一个数据库是不是出问题了
         select 1 判断
             并发连接和并发查询，并不是同一个概念。在show processlist的结果里，看到的几千个连接，指的就是并发连接。 而“当前正在执行”的语句，才是我们所说的并发查询
             建议把innodb_thread_concurrency设置为64~128之间的值
                 在线程进入锁等待以后，并发线程的计数会减一，也就是说等行锁（也包括间隙锁）的线程是不算在128里面的
         查表判断
             外部统计
                 mysql> select * from mysql.health_check;
                     mysql> update mysql.health_check set t_modified=now(); (优先考虑, 再配合performance_schema)
                         mysql> CREATE TABLE `health_check` (   `id` int(11) NOT NULL,   `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`id`) ) ENGINE=InnoDB;  /* 检测命令 */ insert into mysql.health_check(id, t_modified) values (@@server_id, now()) on duplicate key update t_modified=now();
                             判定慢
             内部统计
                 MySQL 5.6版本以后提供的performance_schema库， 就在file_summary_by_event_name表里统计了每次IO请求的时间
                     如果打开所有的performance_schema项，性能大概会下降10%左右
                         mysql> update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';
                         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|用动态的观点看加锁
         不等号条件里的等值查询
             通过索引树搜索过程得到
         等值查询的过程
             锁是在执行过程中一个一个加的
             order by desc 按倒序加锁
                 加锁相同资源(并发)可能会出现死锁
         怎么看死锁
             show engine innodb status
                 LATESTDETECTED DEADLOCK
                     WAITING FOR THIS LOCK TO BE GRANTED，表示的是这个事务在等待的锁信息； index c of table `test`.`t`，说明在等的是表t的索引c上面的锁； lock mode S waiting 表示这个语句要自己加一个读锁，当前的状态是等待中； Record lock说明这是一个记录锁； n_fields 2表示这个记录是两列，也就是字段c和主键字段id； 0: len 4; hex 0000000a; asc ;;是第一个字段，也就是c。值是十六进制a，也就是10； 1: len 4; hex 0000000a; asc ;;是第二个字段，也就是主键id，值也是10； 这两行里面的asc表示的是，接下来要打印出值里面的“可打印字符”，但10不是可打印字符，因此就显示空格。 第一个事务信息就只显示出了等锁的状态，在等待(c=10,id=10)这一行的锁
                     “ HOLDS THE LOCK(S)”用来显示这个事务持有哪些锁； index c of table `test`.`t` 表示锁是在表t的索引c上； hex 0000000a和hex 00000014表示这个事务持有c=10和c=20这两个记录锁； WAITING FOR THIS LOCK TO BE GRANTED，表示在等(c=5,id=5)这个记录锁。
                     lock_mode X waiting表示next-key lock； lock_mode X locks rec but not gap是只有行锁； 还有一种 “locks gap before rec”，就是只有间隙锁；
                     由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问； 在发生死锁的时刻，for update 这条语句占有的资源更多，回滚成本更大，所以InnoDB选择了回滚成本更小的lock in share mode语句，来回滚。
         怎么看待锁等待
             delete会把间隙变大
         update的例子
             可以理解为插入删除
                 delete会把间隙变大
     31|误删数据后怎么办
         主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令
         使用delete语句误删数据行
             Flashback恢复数据的原理，是修改binlog的内容，拿回原库重放。 而能够使用这个方案的前提是，需要确保binlog_format=row 和 binlog_row_image=FULL
             不建议你直接在主库上执行这些操作
             把sql_safe_updates参数设置为on。这样一来，如果我们忘记在delete或者update语句中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错。 代码上线前，必须经过SQL审计
         使用drop table或者truncate table语句误删数据表
             要想恢复数据，就需要使用全量备份，加增量日志的方式了。这个方案要求线上有定期的全量备份，并且实时备份binlog
             取最近一次全量备份，假设这个库是一天一备，上次备份是当天0点； 用备份恢复出一个临时库； 从日志备份里面，取出凌晨0点之后的日志； 把这些日志，除了误删除数据的语句外，全部应用到临时库。
                 如果原实例没有使用GTID模式，只能在应用到包含12点的binlog文件的时候，先用–stop-position参数执行到误操作之前的日志，然后再用–start-position从误操作之后的日志继续执行； 如果实例使用了GTID模式，就方便多了。假设误操作命令的GTID是gtid1，那么只需要执行set gtid_next=gtid1;begin;commit; 先把这个GTID加到临时实例的GTID集合，之后按顺序执行binlog的时候，就会自动跳过误操作的语句
             放回备库
                 从备份系统下载master.000005和master.000006这两个文件，放到备库的日志目录下； 打开日志目录下的master.index文件，在文件开头加入两行，内容分别是 “./master.000005”和“./master.000006”; 重启备库，目的是要让备库重新识别这两个日志文件； 现在这个备库上就有了临时库需要的所有binlog了，建立主备关系，就可以正常同步了
             把这个数据恢复功能做成自动化工具
             延迟复制备库(mysql5.6)
                 CHANGE MASTER TO MASTER_DELAY = N
         使用drop database语句误删数据库
             预防误删库/表的方法
                 账号分离
                     我们只给业务开发同学DML权限，而不给truncate/drop权限。 而如果业务开发人员有DDL需求的话，也可以通过开发管理系统得到支持。 即使是DBA团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账
                 制定操作规范
                     在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。 改表名的时候，要求给表名加固定的后缀（比如加_to_be_deleted)，然后删除表的动作必须通过管理系统执行。 并且，管理系删除表的时候，只能删除固定后缀的表
             线上误truncate表的，最终选择的处理过程如下： 1、创建一个同版本的空mysql实例，建一个名字+结构一模一样的表 2、discard这个表的tablespace 3、从之前的备份集中 innobackupex --apply-log 并记录binlog位置（用innobackupex备份的）。还原后找到误操作表的.ibd文件，copy到新实例对应的位置 4、在之前创建的mysql实例上import tablespace 5、利用mysqlbinlog 处理增量数据 6、最后导出 再导入
         使用rm命令误删整个MySQL实例
             在这个节点上把数据恢复回来，再接入整个集群
             建议只能是说尽量把你的备份跨机房，或者最好是跨城市保存
     32|kill不掉的语句
         一个是kill query +线程id，表示终止这个线程中正在执行的语句
             把session B的运行状态改成THD::KILL_QUERY(将变量killed赋值为THD::KILL_QUERY)； 给session B的执行线程发一个信号
         一个是kill connection +线程id，这里connection可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的
         线程没有执行到判断线程状态的逻辑
         终止逻辑耗时较长
             超大事务执行期间被kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待IO资源，导致耗时较长。 DDL命令执行到最后阶段，如果被kill，需要删除中间过程的临时文件，也可能受IO资源影响耗时较久
         两个误解
             如果库里面的表特别多，连接就会很慢
                 客户端连接(如果在连接命令中加上-A，就可以关掉这个自动补全的功能，然后客户端就可以快速返回了)
             –quick是一个更容易引起误会的参数，也是关于客户端常见的一个误解
                 如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢
                     第一点，就是前面提到的，跳过表名自动补全功能。 第二点，mysql_store_result需要申请本地内存来缓存查询结果，如果查询结果太大，会耗费较多的本地内存，可能会影响客户端本地机器的性能； 第三点，是不会把执行命令记录到本地的命令历史文件。
         通过影响系统环境，让这个Killed状态尽快结束
             InnoDB并发度的问题，你就可以临时调大innodb_thread_concurrency的值，或者停掉别的线程，让出位子给这个线程执行
             如果是回滚逻辑由于受到IO资源限制执行得比较慢，就通过减少系统压力让它加速
     33|我查那么多数据,会不会把数据库内存打爆
         全表扫描对server层的影响
             获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是16k。 重复获取行，直到net_buffer写满，调用网络接口发出去。 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送
                 边读边发
         全表扫描对innodb的影响
             Buffer Pool 还有一个更重要的作用，就是加速查询
                 InnoDB Buffer Pool的大小是由参数 innodb_buffer_pool_size确定的，一般建议设置成可用物理内存的60%~80%
     34|到底可不可以使用join
         可以使用被驱动表的索引
             使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好
             如果使用join语句的话，需要让小表做驱动表
         被驱动表上没有可用的索引
             把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存； 扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回
             join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据话，策略很简单，就是分段放
             join_buffer_size越大，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少
         能不能使用join语句
             如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的； 如果使用Block Nested-Loop Join算法，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用
                 所以你在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样
         如果要使用join，应该选择大表做驱动表还是选择小表做驱动表
             总是应该使用小表做驱动表
                 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表
         能否用上被驱动表的索引，对join语句的性能影响很大
     35|join语句怎么优化
         Multi-Range Read优化
             这个优化的主要目的是尽量使用顺序读盘
                 因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能
             如果你想要稳定地使用MRR优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。 （官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用MRR，把mrr_cost_based设置为off，就是固定使用MRR了。
         Batched Key Access
             如果要使用BKA优化算法的话，你需要在执行SQL语句之前，先设置 set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
                 可能会多次扫描被驱动表，占用磁盘IO资源； 判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的CPU资源； 可能会导致Buffer Pool的热数据被淘汰，影响内存命中率
                 可以考虑增大join_buffer_size的值，减少对被驱动表的扫描次数
         BNL转BKA
             直接在被驱动表上建索引，这时就可以直接转成BKA算法了
             一些不适合在被驱动表上建索引的情况
                 把表t2中满足条件的数据放在临时表tmp_t中； 为了让join使用BKA算法，给临时表tmp_t的字段b加上索引； 让表t1和tmp_t做join操作
             建议使用
         扩展-hash join
             业务端使用hash结构
             MariaDB支持的hash join
         尽量让每一次参与join的驱动表的数据集，越小越好，因为这样我们的驱动表就会越小
         使用内存临时表的速度都比使用InnoDB临时表要更快一些
     36|为什么临时表可以重命名
         内存表
             系统重启的时候会被清空
         临时表
             建表语法是create temporary table …。 一个临时表只能被创建它的session访问，对其他线程不可见。所以，图中session A创建的临时表t，对于session B就是不可见的。 临时表可以与普通表同名。 session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表。 show tables命令不显示临时表
             临时表经常会被用在复杂查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景
                 把各个分库拿到的数据，汇总到一个MySQL实例的一个表中，然后在这个汇总实例上做逻辑操作
                 往往会发现每个分库的计算量都不饱和，所以会直接把临时表temp_ht放到32个分库中的某一个上
             为什么临时表可以重名
                 使用select @@tmpdir命令，来显示实例的临时文件目录
                 在5.6以及之前的版本里，MySQL会在临时文件目录下创建一个相同前缀、以.ibd为后缀的文件，用来存放数据文件； 而从 5.7版本开始，MySQL引入了一个临时文件表空间，专门用来存放临时文件的数据。因此，我们就不需要再创建ibd文件了
                 MySQL维护数据表，除了物理上要有文件外，内存里面也有一套机制区别不同的表，每个表都对应一个table_def_key。 一个普通表的table_def_key的值是由“库名+表名”得到的，所以如果你要在同一个库下创建两个同名的普通表，创建第二个表的过程中就会发现table_def_key已经存在了。 而对于临时表，table_def_key在“库名+表名”基础上，又加入了“server_id+thread_id”
                 每次session内操作表的时候，先遍历链表，检查是否有这个名字的临时表，如果有就优先操作临时表，如果没有再操作普通表
             临时表和主备复制
                 如果当前的binlog_format=row，那么跟临时表有关的语句，就不会记录到binlog里。 也就是说，只在binlog_format=statment/mixed 的时候，binlog中才会记录临时表的操作
                     如果是row,这时候我们就需要在主库上再写一个DROP TEMPORARY TABLE传给备库执行
                 “/* generated by server */”说明了这是一个被服务端改写过的命令
                 临时表传到备库不会冲突
                     MySQL在记录binlog的时候，会把主库执行这个语句的线程id写到binlog中。 这样，在备库的应用线程就能够知道执行每个语句的主库线程id，并利用这个线程id来构造临时表的table_def_key： session A的临时表t1，在备库的table_def_key就是：库名+t1+“M的serverid”+“session A的thread_id”; session B的临时表t1，在备库的table_def_key就是 ：库名+t1+“M的serverid”+“session B的thread_id”
     37|什么时候会使用内部临时表
         union 执行流程
             创建一个内存临时表，这个临时表只有一个整型字段f，并且f是主键字段。 执行第一个子查询，得到1000这个值，并存入临时表中。 执行第二个子查询： 拿到第一行id=1000，试图插入临时表中。但由于1000这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行； 取到第二行id=999，插入临时表成功。 从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是1000和999
         union all
             依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此也就不需要临时表
         group by 执行流程
             Using index，表示这个语句使用了覆盖索引，选择了索引a，不需要回表； Using temporary，表示使用了临时表； Using filesort，表示需要排序
                 参数tmp_table_size就是控制这个内存大小的，默认是16M
         group by 优化方法 --索引
             alter table t1 add column z int generated always as(id % 100), add index(z);
         group by优化方法 --直接排序
             select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;
         如果语句执行过程可以一边读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存，来保存中间结果； join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构； 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。比如我们的例子中，union需要用到唯一索引约束， group by还需要用到另外一个字段来存累积计数
         如果对group by语句的结果没有排序要求，要在语句后面加 order by null； 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort； 如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表； 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果
     38|要不要使用memory引擎
         内存表的数据组织结构
             Memory引擎的数据和索引是分开的
             innodb索引组织表
             menory堆组织表
                 hash索引和B-Tree索引
             不建议你在生产环境上使用内存表
         内存表的锁
             内存表不支持行锁，只支持表锁
         数据持久性问题
         在数据量可控，不会耗费过多内存的情况下，你可以考虑使用内存表
             临时表不会被其他线程访问，没有并发性的问题； 临时表重启后也是需要删除的，清空数据这个问题不存在； 备库的临时表也不会影响主库的用户线程
     39|自增主键为什么不是连续的
         自增值保存在哪里
             在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。 举例来说，如果一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时候，我们删除id=10的行，AUTO_INCREMENT还是11。但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10。 也就是说，MySQL重启可能会修改一个表的AUTO_INCREMENT的值。 在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值
         自增值修改机制
             如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT值填到自增字段； 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。 根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是X，当前的自增值是Y。 如果X<Y，那么这个表的自增值不变； 如果X≥Y，就需要把当前自增值修改为新的自增值
             备注：在一些场景下，使用的就不全是默认值。 比如，双M的主备结构里要求双写的时候，我们就可能会设置成auto_increment_increment=2， 让一个库的自增id都是奇数，另一个库的自增id都是偶数，避免两个库生成的主键发生冲突
         自增值的修改时机
             唯一键冲突是导致自增主键id不连续的第一种原因
             事务回滚也会产生类似的现象，这就是第二种原因
             自增值为什么不能回退
                 事物并发冲突
         自增锁的优化
             在生产上，尤其是有insert … select这种批量插入数据的场景时，从并发插入数据性能的角度考虑，我建议你这样设置： innodb_autoinc_lock_mode=2 ，并且 binlog_format=row.这样做，既能提升并发性，又不会出现数据一致性问题
             MySQL有一个批量申请自增id的策略
                 语句执行过程中，第一次申请自增id，会分配1个； 1个用完以后，这个语句第二次申请自增id，会分配2个； 2个用完以后，还是这个语句，第三次申请自增id，会分配4个； 依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍
     40|insert的锁语句为什么这么多
         无法在申请到自增id以后就立马释放自增锁
             insert … select 语句
                 在可重复读隔离级别下，这个语句会给select的表里扫描到的记录和间隙加读锁
                 MySQL 8.0版本中，已经能够用临时表处理insert … select写入原表的语句了
             insert 循环写入
                 一边遍历一边更新数据的情况需要临时表
                 show status like '%Innodb_row_read%'
                 数据量小使用内存临时表
             insert唯一主键冲突
                 insert into … on duplicate key update
                     插入一行数据，如果碰到唯一键约束，就执行后面的更新语句
                 碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长
     41|怎么最快的复制一张表
         mysqldump方法
             mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
             –single-transaction的作用是，在导出数据的时候不需要对表db1.t加表锁，而是使用START TRANSACTION WITH CONSISTENT SNAPSHOT的方法； –add-locks设置为0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ； –no-create-info的意思是，不需要导出表结构； –set-gtid-purged=off表示的是，不输出跟GTID相关的信息； –result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的
             如果你希望生成的文件中一条INSERT语句只插入一行数据的话，可以在执行mysqldump命令时，加上参数–skip-extended-insert
             mysql -h127.0.0.1 -P13000  -uroot db2 -e "source /client_tmp/t.sql"
         导出CSV文件
             select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
             这条语句会将结果保存在服务端。如果你执行命令的客户端和MySQL服务端不在同一个机器上，客户端机器的临时目录下是不会生成t.csv文件的。 into outfile指定了文件的生成位置（/server_tmp/），这个位置必须受参数secure_file_priv的限制。参数secure_file_priv的可选值和作用分别是： 如果设置为empty，表示不限制文件生成的位置，这是不安全的设置； 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录； 如果设置为NULL，就表示禁止在这个MySQL实例上执行select … into outfile 操作。 这条命令不会帮你覆盖文件，因此你需要确保/server_tmp/t.csv这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。 这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。但是，如果字段中包含换行符，在生成的文本中也会有换行符。不过类似换行符、制表符这类符号，前面都会跟上“\”这个转义符，这样就可以跟字段之间、数据行之间的分隔符区分开
             load data infile '/server_tmp/t.csv' into table db2.t;
             可以同时导出表结构定义文件和csv数据文件
                 mysqldump -h$host -P$port -u$user ---single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv
         物理拷贝方法
             执行 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；这两个命令（这里需要注意的是，拷贝得到的两个文件，MySQL进程要有读写权限）； 执行unlock tables，这时候t.cfg文件会被删除； 执行alter table r import tablespace，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.ibd是相同的，所以表r中就有了和表t相同的数据
             MySQL 5.6版本引入了可传输表空间
                 在第3步执行完flsuh table命令之后，db1.t整个表处于只读状态，直到执行unlock tables命令后才释放读锁； 在执行import tablespace的时候，为了让文件里的表空间id和数据字典中的一致，会修改r.ibd的表空间id。而这个表空间id存在于每一个数据页中。因此，如果是一个很大的文件（比如TB级别），每个数据页都需要修改，所以你会看到这个import语句的执行是需要一些时间的。当然，如果是相比于逻辑导入的方法，import语句的耗时是非常短的
         三种方法优缺点
             物理拷贝的方式速度最快，尤其对于大表拷贝来说是最快的方法。如果出现误删表的情况，用备份恢复出误删之前的临时库，然后再把临时库中的表拷贝到生产库上，是恢复数据最快的方法。但是，这种方法的使用也有一定的局限性： 必须是全表拷贝，不能只拷贝部分数据； 需要到服务器上拷贝数据，在用户无法登录数据库主机的场景下无法使用； 由于是通过拷贝物理文件实现的，源表和目标表都是使用InnoDB引擎时才能使用。 用mysqldump生成包含INSERT语句文件的方法，可以在where参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用join这种比较复杂的where条件写法。 用select … into outfile的方法是最灵活的，支持所有的SQL写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份
     42|grant之后要跟着flush privileges吗
         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 命令对于全局权限，同时更新了磁盘和内存。命令完成后即时生效，接下来新创建的连接会使用新的权限。 对于一个已经存在的连接，它的全局权限不受grant命令的影响
                 如果一个用户有所有权限，一般就不应该设置为所有IP地址都可以访问
             revoke all privileges on *.* from 'ua'@'%';
                 磁盘上，将mysql.user表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为“N”； 内存里，从数组acl_users中找到这个用户对应的对象，将access的值修改为0
         db权限
             grant all privileges on db1.* to 'ua'@'%' with grant option;
                 磁盘上，往mysql.db表中插入了一行记录，所有权限位字段设置为“Y”； 内存里，增加一个对象到数组acl_dbs中，这个对象的权限位为“全1”。
                 grant修改db权限的时候，是同时对磁盘和内存生效的
                 如果当前会话已经处于某一个db里面，之前use这个库的时候拿到的库权限会保存在会话变量中
         表权限和列权限
             create table db1.t1(id int, a int); grant all privileges on db1.t1 to 'ua'@'%' with grant option; GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;
                 这两类权限的操作，也会马上影响到已经存在的连接
         如果内存的权限数据和磁盘数据表相同的话，不需要执行flush privileges。而如果我们都是用grant/revoke语句来执行的话，内存和数据表本来就是保持同步更新的。 因此，正常情况下，grant命令之后，没有必要跟着执行flush privileges命令
         flush privileges使用场景
             当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges语句可以用来重建内存数据，达到一致状态
                 直接操作系统表等
     43|要不要使用分区表
         分区表的引擎层行为
             对于引擎层来说，这是4个表； 对于Server层来说，这是1个表
         分区策略
             MyISAM分区表使用的分区策略，我们称为通用分区策略（generic partitioning），每次访问分区都由server层控制。通用分区策略，是MySQL一开始支持分区表的时候就存在的代码，在文件管理、表管理的实现上很粗糙，因此有比较严重的性能问题
             从MySQL 5.7.9开始，InnoDB引擎引入了本地分区策略（native partitioning）。这个策略是在InnoDB内部自己管理打开分区的行为
             MySQL从5.7.17开始，将MyISAM分区表标记为即将弃用(deprecated)，意思是“从这个版本开始不建议这么使用，请使用替代方案。在将来的版本中会废弃这个功能”。
             从MySQL 8.0版本开始，就不允许创建MyISAM分区表了，只允许创建已经实现了本地分区策略的引擎。目前来看，只有InnoDB和NDB这两个引擎支持了本地分区策略
         分区表的server层行为
             MySQL在第一次打开分区表的时候，需要访问所有的分区； 在server层，认为这是同一张表，因此所有分区共用同一个MDL锁； 在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区
         分区表的应用场景
             分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。还有，分区表可以很方便的清理历史数据。 如果一项业务跑的时间足够长，往往就会有根据时间删除历史数据的需求。这时候，按照时间分区的分区表，就可以直接通过alter table t drop partition …这个语法删掉分区，从而删掉过期的历史数据。 这个alter table t drop partition …操作是直接删除分区文件，效果跟drop普通表类似。与使用delete语句删除数据相比，优势是速度快、对系统影响小
             分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。 分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的12个新分区创建上即可。对于没有数据的历史分区，要及时的drop掉
     44|说一说这些好问题
         如果用left join的话，左边的表一定是驱动表吗？ 如果两个表的join包含多个条件的等值匹配，是都要写到on里面呢，还是只把一个条件写到on里面，其他条件写到where部分
             使用left join时，左边的表不一定是驱动表
                 如果需要left join的语义，就不能把被驱动表的字段放在where条件里面做等值判断或不等值判断，必须都写在on里面
             and
                 语句Q1返回的数据集是6行，表a中即使没有满足匹配条件的记录，查询结果中也会返回一行，并将表b的各个字段值填成NULL。
             where
                 语句Q2返回的是4行。从逻辑上可以这么理解，最后的两行，由于表b中没有匹配的字段，结果集里面b.f2的值是空，不满足where 部分的条件判断，因此不能作为结果集的一部分
                 而如果一条join语句的Extra字段什么都没写的话，就表示使用的是Index Nested-Loop Join（简称NLJ）算法
                 优化器就把这条语句的left join改写成了join，然后因为表a的f1上有索引，就把表b作为驱动表，这样就可以用上NLJ 算法。 在执行explain之后，你再执行show warnings，就能看到这个改写的结果
         Simple Nested Loop Join 的性能问题
             BNL算法的执行逻辑是
                 首先，将驱动表的数据全部读入内存join_buffer中，这里join_buffer是无序数组； 然后，顺序遍历被驱动表的所有行，每一行数据都跟join_buffer中的数据进行匹配，匹配成功则作为结果集的一部分返回
             Simple Nested Loop Join算法的执行逻辑是
                 顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回
             在对被驱动表做全表扫描的时候，如果数据没有在Buffer Pool中，就需要等待这部分数据从磁盘读入； 从磁盘读入数据到内存中，会影响正常业务的Buffer Pool命中率，而且这个算法天然会对被驱动表的数据做多次访问，更容易将这些数据页放到Buffer Pool的头部（请参考第35篇文章中的相关内容)； 即使被驱动表数据都在内存中，每次查找“下一个记录的操作”，都是类似指针操作。而join_buffer中是数组，遍历的成本更低
                 BNL算法的性能会更好
         distinct 和 group by的性能
             select a from t group by a order by null; select distinct a from t;
                 没有了count(*)以后，也就是不再需要执行“计算总数”的逻辑时，第一条语句的逻辑就变成是：按照字段a做分组，相同的a的值只返回一行。而这就是distinct的语义。 所以不需要执行聚合函数时，distinct 和group by这两条语句的语义和执行流程是相同的，因此执行性能也相同
                     创建一个临时表，临时表有一个字段a，并且在这个字段a上创建一个唯一索引； 遍历表t，依次取数据插入临时表中： 如果发现唯一键冲突，就跳过； 否则插入成功； 遍历完成后，将临时表作为结果集返回给客户端
         备库自增主键问题
     45|自增id用完了怎么办
         表定义自增值id
             表定义的自增值达到上限后的逻辑是：再申请下一个id时，得到的值保持不变
         InnoDB系统自增row_id
             写入表的row_id是从0开始到2^48-1。达到上限后，下一个值就是0，然后继续循环
         Xid
             但是如果global_query_id达到上限后，就会继续从0开始计数
             因为global_query_id定义的长度是8个字节，这个自增值的上限是2^64-1
         Innodb trx_id
             对于只读事务，InnoDB并不会分配trx_id
                 只读事务不分配trx_id，有什么好处呢
                     一个好处是，这样做可以减小事务视图里面活跃事务数组的大小。因为当前正在运行的只读事务，是不影响数据的可见性判断的。所以，在创建事务的一致性视图时，InnoDB就只需要拷贝读写事务的trx_id。 另一个好处是，可以减少trx_id的申请次数。在InnoDB里，即使你只是执行一个普通的select语句，在执行过程中，也是要对应一个只读事务的。所以只读事务优化后，普通的查询语句不需要申请trx_id，就大大减少了并发事务申请trx_id的锁冲突
             update 和 delete语句除了事务本身，还涉及到标记删除旧数据，也就是要把数据放到purge队列里等待后续物理删除，这个操作也会把max_trx_id+1， 因此在一个事务中至少加2； InnoDB的后台操作，比如表的索引信息统计这类操作，也是会启动内部事务的，因此你可能看到，trx_id值并不是按照加1递增的
             max_trx_id达到2^48-1的上限，然后从0开始的情况(出现脏读)
                 假设一个MySQL实例的TPS是每秒50万，持续这个压力的话，在17.8年后，就会出现这个情况
         thread_id
             thread_id的逻辑很好理解：系统保存了一个全局变量thread_id_counter，每新建一个连接，就将thread_id_counter赋值给这个新连接的线程变量。 thread_id_counter定义的大小是4个字节，因此达到2^32-1后，它就会重置为0，然后继续增加。但是，你不会在show processlist里看到两个相同的thread_id