10 | MySQL为什么有时候会选错索引 优化器的逻辑 找到最优的执行方案，考虑的因素包括但不限于：扫描的行数、是否使用临时表、是否排序
索引的区分度 索引的区分度即是说一个索引中不同值的个数，又称之为“基数”，基数越大区分度越好
通过“采样统计”获取索引的区分度 InnoDB 默认选择N个页，统计每个页上的不同值，求平均数，然后乘以页面数，得到索引的基数。 索引的更新导致变更的行数超过 1/M 时，从新采样。 innodb_stats_persistent 设置为 on 时：N 为 20，M 为 10 innodb_stats_persistent 设置为 off 时：N 为 8，M 为 16
优化器有时会选择扫描行数更多的那个方案 如果使用普通索引预估扫描行数 3 万，不使用普通索引直接全表扫描 10 万行，由于使用普通索引还需要回表，这部分时间也考虑进去的话，优化器会认为全表扫描会更快
主动修正统计信息 analyze table table_name
索引选择异常的处理方案 使用 force index(index_name) 强制使用指定的索引 新建一个更合适的索引给优化器使用或者删掉误用的索引 问题讨论 通过 session A 的配合，让 session B 删除数据后又重新插入了一遍数据，然后就发现 explain 结果中，rows 字段从 10001 变成 37000 多。而如果没有 session A 的配合，只是单独执行 delete from t 、call idata()、explain 这三句话，会看到 rows 字段其实还是 10000 左右。你可以自己验证一下这个结果。这是什么原因呢？
11 | 怎么给字符串字段加索引？ 给字符串加索引的方式 直接创建完整的索引 前缀索引 倒序存储，再创建前缀索引 使用hash字段 使用前缀索引的优缺点 优点 节省空间 缺点 可能会增加额外的记录扫描数 使用前缀索引导致无法使用覆盖索引（每行都需要回表确认） 使用前缀索引，定义好长度，可以打到既节省空间、又不会增加太多的查询成本。这里可以参考索引的区分度，区分度越高越好 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; 问题讨论 如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号 @gmail.com", 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？
答案 只存入学年份加顺序编号，它们的长度是 9 位。而其实在此基础上，可以用数字类型来存这 9 位数字。比如 201100001，这样只需要占 4 个字节。其实这个就是一种 hash，只是它用了最简单的转换规则：字符串转数字的规则，而刚好我们设定的这个背景，可以保证这个转换后结果的唯一性
12 | 为什么我的MySQL会“抖”一下？ SQL 语句为什么变“慢”了？ 和数据相关的三个部分：数据文件、日志文件、内存
脏页：内存数据页和磁盘数据页内存不一致，这个内存页称为“脏页”
干净页：内存数据写入磁盘后，内存和磁盘上的数据页就一致了，我们称为“干净页”
平时执行很快的更新操作，其实就是在写内存和日志；偶尔变得很慢，很可能是在刷“脏页”（flush）
哪些场景下会刷脏页？ redo log 满了 系统内存不足 系统空闲的时候，主动刷脏 Mysql正常关闭前 刷脏是常态，但是要避免两种情况 一个查询要淘汰的脏页个数过多，会导致查询的响应时间明显变长 日志写满，更新全部堵住，写性能跌为0 InnoDB 刷脏页的控制策略 正确告诉 InnoDB 所在主机的IO能力，使其能够确定全力刷脏页的时候能刷多快；相关参数：innodb_io_capacity 避免刷脏页的时候顺带刷旁边的脏页；相关参数：innodb_flush_neighbors 问题讨论 一个内存配置为 128GB、innodb_io_capacity 设置为 20000 的大规格实例，正常会建议你将 redo log 设置成 4 个 1GB 的文件。
但如果你在配置的时候不慎将 redo log 设置成了 1 个 100M 的文件，会发生什么情况呢？又为什么会出现这样的情况呢？
答案 磁盘压力很小，但是数据库出现间歇性的性能下跌。
13 | 为什么表数据删掉一半，表文件大小不变？ 表数据的存储 存储在共享表空间里 存储在单独的文件 通过innodb_file_per_table参数控制，且5.6.6开始，默认为 on
推荐设置为 on，单独存储易管理，执行 drop table 命令，系统会直接删除表对应的文件，如果存放在共享表里则不会删除
为什么删除数据，文件大小不变 前置须知：InnoDB 的数据模型是 B+ 树
执行删除操作后，对应的记录不会被真正的删除，仅仅是标记此条记录原本存在的位置可复用，如果恰好有一条数据需要插入到此位置，则复用，否则它就一直存在，像一个“空洞”；
如果是整页数据被删除，也是采用标记的形式，不会真正删除，只是这一页下次被使用时，位置可以改变，不局限于原来的位置。
类似的还有插入和更新操作，都会导致“空洞”，这些空洞就是导致删除记录，但文件大小不变但原因。
通过重建表，优化“空洞”问题 alter table A engine=InnoDB
建立一个临时文件，扫描表 A 主键的所有数据页； 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中； 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中，对应的是图中 state2 的状态； 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态； 用临时文件替换表 A 的数据文件。 问题讨论 假设现在有人碰到了一个“想要收缩表空间，结果适得其反”的情况，看上去是这样的：一个表 t 文件大小为 1TB；对这个表执行 alter table t engine=InnoDB；发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了 1.01TB。你觉得可能是什么原因呢 ？
答案 表本身就已经没有空洞的了，比如说刚刚做过一次重建表操作。在 DDL 期间，如果刚好有外部的 DML 在执行，这期间可能会引入一些新的空洞。
14 | count(*)这么慢，我该怎么办？ count(*)在不同引擎的实现方式 MyISAM 引擎单独存储了表的总行数，执行 count(*) 时可以直接返回，效率高。（查询不能带有where条件） InnoDB 执行 count(*)，需要将数据从引擎里一行一行地读出来进行计数 为什么 innodb 不像 myisam 一样单独存储总行数 因为 MVCC。 在同一时刻的多个查询，由于多版本并发控制的原因，应该返回多少行是不一致的
innodb对count(*)做了哪些优化 对于 count(*) 这中操作，遍历数据表的任何一棵索引树结果都是一样的，因此 MySQL 优化器会选择最小的那棵树来进行遍历。一般不会是主键索引树，因为它的叶子节点存储的是数据，而普通索引树的叶子节点存储的是主键id，数据量会小很多。
在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。
show table status 返回的 table_rows 为什么不能使用 不能。TABLE_ROWS 是通过采样估算得来的，不准确。
计数的实现方案 用缓存系统保存计数 问题： 缓存系统可能会丢失更新； 逻辑上可能不精确；
两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图
数据库保存计数 count() 的语义 count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值
count(*)、count(主键 id)、count(1)、count(字段） count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数 效率：count(字段) < count(主键 id) < count(1) ≈ count(*) 问题讨论 从并发系统性能的角度考虑，你觉得在这个事务序列里，应该先插入操作记录，还是应该先更新计数表呢？
16 | “order by”是怎么工作的？ MySQL 在哪里排序 内存排序 : MySQL为每个线程分配一块内存用于排序，数据量少的时候在这里进行。具体大小通过 sort_buffer_size 控制。 磁盘临时文件排序 : 数据量大于 sort_buffer_size 时，使用多个磁盘临时文件排序，有点类似于归并排序 MySQL排序方案 建表如下：
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; 排序语句如下：
select city,name,age from t where city='杭州' order by name limit 1000; 1. 全字段排序 (1)初始化 sort_buffer，确定放入 name、city、age 这三个字段； (2)从索引 city 找到第一个满足 city=‘杭州’条件的主键 id (3)到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中； (4)从索引 city 取下一个记录的主键 id； (5)重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y； (6)对 sort_buffer 中的数据按照字段 name 做快速排序； (7)按照排序结果取前 1000 行返回给客户端。 2.
17 | 如何正确地显示随机消息？ 排序的选择 对于 InnoDB 表的排序，全字段排序会减少回表的次数，性能更佳，会被优先选择 对于存在于内存里的表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘，所以行越小排序性能越好 order by rand() 的执行过程 建表语句
mysql> CREATE TABLE `words` ( `id` int(11) NOT NULL AUTO_INCREMENT, `word` varchar(64) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; 查询语句
select word from words order by rand() limit 3; explain 结果 Using temporary表示需要使用临时表；Using filesort 表示需要执行排序操作;
全字段排序流程图 rowid排序流程图 执行流程（假设内存足够放下临时表，内存不够时使用磁盘临时表）
创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。 现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型(记录位置信息)。 从内存临时表中一行一行地取出 R 值和位置信息(类似rowid)，分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。 在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。 执行流程图 优先队列排序算法 如果内存不足以存放临时表，除了使用磁盘临时表外，还有一个选择：使用优先队列排序算法。
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; 查询语句
select count(*) from tradelog where month(t_modified)=7; 隐式类型转换相当于调用了 CAST 函数，无法使用索引 查询语句
select * from tradelog where tradeid=110717; 相当于调用
select * from tradelog where CAST(tradid AS signed int) = 110717; 隐式字符编码转换相当于调用了 CONVERT 函数，无法使用索引 建表语句
19 | 为什么我只查一行的语句，也执行这么慢？ 建表，插入10万行数据
CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB; delimiter ;; create procedure idata() begin declare i int; set i=1; while(i<=100000) do insert into t values(i,i); set i=i+1; end while; end;; delimiter ; call idata(); 查询操作长时间不返回 select * from t where id=1; 原因：表被锁住了
等 MDL 锁 通过 show processlist 命令查看，发现 State 为 Waiting for table metadata lock，这是因为当前有某个线程持有表的 MDL 写锁，阻塞了 select 语句
20 | 幻读是什么，幻读有什么问题？ 建表语句
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); 什么是幻读 幻度指的是一个事务在前后两次查询同一个范围的时候，后一次的查询看到了前一次没有看到的行 场景 tips
在可重复读隔离级别下，普通的读是快照读，不会读取到其它事务新插入的数据；所以幻读只在“当前读”下发生 幻读专指“新插入的行”，Q2 读取到的 session B 的更新结果，不是幻读 幻读有什么问题 语义问题：事务A 试图对满足条件的所有行加锁（比如列值=5）；加锁以后，事务B 新增了一行（列值也=5），但是它不会被锁住；这就导致 A 想要锁住所有列值=5 的行，但事实是新增但那一行还是可以被更新
数据一致性问题：无论是只对要更新行的行上锁，还是把所有扫描的行都上锁，都无法控制新增的数据行的结果
语义上的问题 场景 session A 的 Q1 语句，语义上是要锁住所有 d=5 的行；但是 session C 的操作，它新增了一条 d=5 的行，而这个行却没有被 session A 锁住，session C 对 d=5(id=1) 的这一行还是可以做更改
