MySQL实战9-45

1、普通索引和唯一索引，应该怎么选择？查询过程：普通索引来查找到满足条件的第一个记录  后，需要查找下一个记录，直到碰到第一个不满足条件的记录。对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。性能差距微乎其微。InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。普通索引正好在最后一条的几率很低

change buffer：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。change buffer 用的是 buffer pool 里的内存。唯一索引要判断唯一性，需要读取数据进内存，所以用不到change buffer。将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。普通索引使用change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。所以读多写少的数据用change buffer会频繁触发 merge 过程。起了副作用。

merge 流程：从磁盘读入数据页到内存（老版本的数据页）；从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。到这里 merge 过程就结束了。这时候，数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页。

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

PRIMARY KEY（主键索引）、UNIQUE(唯一索引，允许有空值。如果是组合索引，则列值的组合必须唯一)、INDEX(普通索引)、FULLTEXT(全文索引)

查看索引
show index from tb_wz_all;

2、 MySQL选错索引问题

选错索引肯定是在判断扫描行数的时候出问题了。

MySQL 在执行语句之前，不能精确地知道满足这个条件的记录有多少条，只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。基数通过采样统计估算

1、使用 force index(a) 来让优化器强制使用索引 a；2、第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引。3、第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。4、analyze table t 命令修正不对的统计信息。

set long_query_time=0;将慢查询日志的阈值设置为 0。

3、怎么给字符串字段加索引？

直接创建完整索引，这样可能比较占用空间；

创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

4、SQL 语句为什么某一次变“慢”了

可能就是在刷脏页（flush）：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。不论是脏页还是干净页，都在内存中。

1、redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写，推进的部分对应的所有脏页都 flush 到磁盘上。

2、查询语句在内存不够，需要内存的时候可能要求淘汰一个脏页。

flush的时候，整个系统就不能再接受更新了，所以要关注脏页比例，不要让它经常接近 75%。。相邻的数据页也还是脏页的话，也会被放到一起刷，硬盘时代可以减少随机IO。MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了，表示不刷邻居，1则会刷邻居

5、表数据删掉一半，表文件大小不变？

drop table 命令回收表空间，delete 命令是不能回收表空间的，在数据文件当中只是标记为删除，如果需要做空间收缩，为了把表中存在的空洞去掉：重建表：

 MySQL 5.6 版本开始引入的 Online DDL，重建表的流程：建立一个临时文件，扫描表 A 主键的所有数据页；用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；用临时文件替换表 A 的数据文件。语句： alter table t engine=InnoDB

Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。如果想要比较安全的操作的话，我推荐你使用 GitHub 开源的 gh-ost 来做。

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

6、count(*)过程

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数（MVCC机制的限制：每一行记录都要判断自己是否对这个会话可见，因此对于 count(*) 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。优化：In