索引的所用:提高数据查询效率。
常见索引模型:

• 哈希表:存储键值对;把值放入数组中,用一个哈希函数把key换算成一个确定的位置,然后将value放入数组的这个位置;使用链表处理冲突的问题;适用于等值查询的场景。
• 有序数组:按照顺序存储;查询用二分法可以快速查询,时间复杂度为O(logN);查询效率高,更新效率低;适用于静态存储引擎。
• 二叉索引树:每个节点的左儿子小于父节点,父节点小于右儿子;查询时间复杂度为(O(logN)),更新时间复杂度为O(logN);数据库存储大多不适用于二叉树,因为树高过高,会适用N叉树。

InnoDB中的索引模型:B+ 树。

索引类型:

• 主键索引:叶子节点存的都是整行的数据(聚集索引)
• 非主键索引:非主键索引的叶子节点内容是主键的值(二级索引)

主键索引与非主键索引的区别:主键索引主要搜索主键的这个B+ 树即可拿到数据。普通索引先搜索索引拿到主键值,再到主键索引树搜索一次(回表)。

一个数据页满了,按照B+树算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率大概降低50%。当相邻的两个数据页利用很低的时候，会做
数据页合并,合并的过程是分类过程的逆过程。

从性能和存储空间方面考量，自增主键往往是更合理的选择。

如何避免长事务对业务的影响？
从应用开发端来看:

• 确认是否使用了set autocommit = 0。打开MySQL的general_log打开。
• 确认是否有不必要的只读事务。在读事务中去掉begin/commit 去掉。
• 业务链接数据库的时候,根据业务本身的预估,通过set max_execution_time命令,来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

从数据库端来看:

• 监控information_schema.innodb_trx表,设置事务阈值,超过就报警/或者kill
• 功能测试时输出general_log,分析日志行为提前发现问题。
• MySQL 5.6或者更新版本，把innodb_undo_tablesapes设置为2或者更大的值。

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

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

在建立联合索引的时候,如何安排索引内的字段。
第一原则是如果通过调整顺序,可以少维护一个索引,那么这个顺序往往是需要优先考虑采用的。

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

普通索引和唯一索引，如何选择

InnoDB的数据是按数据页为单位来读写的。每个数据页的大小默认是16KB。

当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,
在不影响数据一致性的前提下,InnoDB会将这些更新操作缓存在change buffer中,这样就不需要从磁盘中读入这个数据页了。
在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作。
通过这种方式就能保证这个数据逻辑的正确性。
change buffer中的数据也可以进行持久化,将change buffer中的操作应用到原数据页，得到最新结果的过程成为merge。
在数据库正常关闭的过程中,也会执行merge操作。

什么条件下可以使用change buffer(不能保证唯一性)？
唯一索引的更新不能使用change buffer,只有普通索引可以使用。

change buffer用的是buffer pool里的内存,因此不能无限增大。可以通过innodb_change_buffer_max_size来动态设置change buffer。

目标数据页在内存中:

• 唯一索引,判断数据是否存在,不存在插入
• 普通索引,找到位置直接插入

目标数据页不在内存中:

• 唯一索引,将数据页读入内存中,判断是否有冲突,没有冲突,直接插入
• 普通索引,将更新记录在change buffer

change buffer因为减少了随机磁盘访问,所以对更新性能的提升会很明显的。

change buffer的使用场景:写锁读少。页面在写完以后马上被访问到的概率比较小,此时change buffer的使用效果最好。
这种模式常见的就是账单类、日志类的系统。

WAL提升性能的核心机制，核心是尽量减少随机读写。

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

如果某次写入使用了change buffer机制,之后主机异常重启,是否会丢失change buffer和数据？
不会,虽然只更新内存,但是在事务提交的时候,我们把change buffer的操作页记录到redo log里了,所以崩溃恢复的时候,
change buffer也能找回来。

merge的过程是否会把数据直接写回磁盘:

• 从磁盘读入数据页到内存;
• 从change buffer里面找到这个数据页的change buffer记录,一次应用,得到新版数据页;
• 写redo log。这个redo log包含了数据的变更和change buffer的变更。

MySQL为什么有时候会选错索引？

优化器选择索引的目的，就是找到一个最优的执行方案,并且最小的代价去执行语句。
在数据库里面,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的CPU资源越少。

扫描行数是怎么判断的？
一个索引上不同的值的个数越多,索引的区分度越好。可以通过show index from t来看。

MySQL是怎样得到索引的基数的？通过采样统计,取平均值。

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

• 设置为on,统计信息会持久化存储。N(取样时,选择数据页的个数)默认是20,M(变换行数的分母)默认是10。
• 设置为off,统计信息值存储在内存中。N的默认值为8。M的默认值为16。

使用analyze table t命令,可以用来重新统计索引信息。

索引选择异常和处理

• 使用force index强行选择一个索引。
• 通过修改sql语句来正确使用索引。
• 新建一个更合适的索引,或者删掉误用的索引。

经过上面的顺序,explain的结果不同啦？
delete语句删除了所有的数据，然后通过call idata()插入10w行数据,看上去是覆盖了原来的10w行数据。
SessionA开启了事务但没有提交,之前插入的10W行数据是不能删除的。拥有两个版本的数据:delete之前的数据(旧数据);标记位deleted的数据(新数据)。
索引A上就有两份数据。不过这个是主键,主键是按照表的行数来估计的。表的行数,优化器直接用的是show table status的值。

怎么给字符串字段加索引

MySQL支持前缀索引。前缀索引可以指定建立索引的位数,这样占用的空间会更小,这就是使用前缀索引的优势。会增加额外的记录扫描次数。
使用前缀索引的时候,需要定义好长度,就可以做到既节省空间又不用额外增加太多的查询成本。

为字符串创建索引的时候,需要考虑区分度,区分度越高越好。因为区分度越高,意味着重复的健值越少。可以通过查询字符串的长度,只要符合95%即可。

前缀索引对覆盖索引的影响:
前缀索引可能会增加扫描行数,这回影响到性能。
使用前缀索引就用不上覆盖索引对查询性能的优化了,这也是在选择是否使用前缀索引时考虑的一个因素。

身份证号这种使用什么索引:

• 使用倒序存储。将身份证号反转存储。
• 使用hash字段。创建一个整数字段,用来保存身份证的校验码,同时在这个字段上创建索引。

使用倒序存储和使用hash字段这种方法的异同点:

• 相同点:不支持范围查询。hash只能支持等值查询。
• 不同点:倒序存储方式在主键索引上,不会消耗额外的存储空间,hash字段方法需要增加一个字段;倒序方式每次都需要调用reverse函数,
  hash字段需要调用crc32()函数;查询效率,hash字段方式的查询性能相对稳定一些，倒序存储增加扫描行数。

为字符串创建索引:

• 直接创建完整索引,这样可能比较占用空间;
• 创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;
• 倒序存储，在创建前缀索引时,用于绕过字符串本身前缀的区分度不够的问题;
• 创建hash字段索引,查询性能稳定,有额外的存储和计算消耗,不支持范围查询。

为什么我的MySQL会抖一下

当内存数据页根磁盘数据页内容不一致时,称这个内存页为脏页。内存数据写入到磁盘后,内存和磁盘上的数据页内容就一致了,成为干净页。

什么情况会引发数据库的flush过程:

• redo log写满了:这时系统会停止所有更新操作，把checkpoint往前推进,redo log留出空间可以继续写。尽量避免这种情况。
• 系统内存不足:就淘汰一部分数据页,写到磁盘上。脏页一定回写盘,就保证了每个数据页有两个状态:
  • 内存里存在,内存里就肯定是正确的结果,直接返回。
  • 内存里没有数据,可以肯定数据文件上是正确的结果,读入内存后返回。
• 系统空闲的时候,将数据写入到磁盘上。
• mysql正常关闭:mysql会把内存的脏页都flush到磁盘上,下次MySQL启动的时候,就可以直接从磁盘上读取数据，启动速度会很快。

InnoDB用缓存池管理内存,缓存池中的内存页有三种状态:

• 还没有使用的
• 使用了并且是干净页
• 使用了并且是脏页

影响刷脏页的情况:

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

InnoDB刷脏页的控制策略

使用innodb_io_capacity来表示磁盘能力。
InnoDB的刷盘速度就是要考虑这两个因素:一个是脏页比例,一个是redo log写盘速度。

innodb_max_dirty_pages_pact是脏页比例上线，默认值为75%,

innodb_flush_neighbors参数来控制是否刷新邻居，如果值为1,是有连坐机制,值为0表示各自刷各自的(MySQL 8.0的时候,已经默认值为0)。

如果将redo log值设置的小,就会将redo log快速写满,磁盘的压力也很小,但是数据库会出现间歇性的性能下降。

为什么表数据删除一半,表文件大小不变

一个InnoDB表包含两部分:表结构定义和数据。MySQL8.0之前,表结构是存在以.frm为后缀的文件里。
MySQL8.0,已经允许把表结构定义放在系统数据表中。

参数innodb_file_per_table

表数据既可以存在共享表空间里,也可以是单独的文件。设置为OFF时,表的数据放在系统共享表空间,也是根数据字典放在一起。
设置为ON时,每个InnoDB表数据存储在一个以.idb为后缀的文件里。MySQL 5.6版本开始,默认值就是ON。

一个表单独存储为一个文件,便于管理,可以直接删除这个文件;如果放在共享空间里,即使表删除了,空间也是不会回收的。

drop table可以收回表空间，删除部分行,不能回收表空间。

在删除数据时,会将数据添加上删除标签,不会造成文件的缩小,如果有其他的数据可以直接复用这个位置。如果删除一个数据页上的所有记录,
整个数据页就可以被复用了。

记录的复用只限于符合条件的数据,当整个数据页从B+树里面摘掉以后,可以复用到任何位置。

如果相邻的两个数据页利用率很小,系统就会把这两个页上的数据喝到其中一个页上,另一个数据页就被标记为可复用。

delete命令删除整个表的数据,所有数据页标记为可服用,磁盘上,文件不可变小。不能回收表空间。

经过大量增删改的表,都是可能是存在空洞的。所以,如果能够把这些空洞去掉,就能达到收缩表空间的目的。可以通过重建表,达到目的。

重建方法都会扫描原表数据和构建临时文件,这些操作都收消耗CPU和IO资源的。

DDL如果是Online的，一定是inplace的。反过来就不一定，MySQL 8.0的全文索引和空间索引就是inplace。

从MySQL 5.6开始,alter table t engine=InnoDB(也即是recreate),analyze table t 不是重建表,
只是对表的索引信息做重新统计，没有修改数据,这个过程中加了DML读锁。
optimize table t 等于 recreate+analyze。

Count(*) 这么慢,我该怎么办？

count(*) 的实现方式:

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

为什么InnoDB不跟MyISAM一样,把数字存起来:因为即使是在同一时刻的多个查询,
由于多版本控制的原因,InnoDb表应该返回多少行也是不确定的。

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

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

执行效率来看,count(字段)<count(主键id)<count(1)=count(),建议使用count()。

崩溃恢复的判断规则:

• 如果redo log里面的事务是完整的,也就是已经有了commit标识,则直接提交;
• 如果redo log里面的事务只有完整的prepare,则判断对应的事务binlog是否存在并完整:
  • a,如果是，则提交事务；
  • b,否则，回滚事务。

MySQL如何知道binlog是完整的:
一个事务的binlog是有完整格式的:statement格式的binlog,最后会有commit;row格式的binlog,最后会有一个xid event;
MySQL5.6.2版本以后,还引入binlog-checksum参数,用来验证binlog内容的正确性.

redo log和binlog是怎么关联起来的？
因为它们有一个共同的数据字段,叫XID，崩溃回复的时候,会按顺序扫描redo log:

• 如果碰到既有prepare、又有commit的redo log,就直接提交;
• 如果碰到只有prepare、而没有commit的redo log,就拿着xid去binlog里面找对应的事务。

处于prepare阶段的redo log 加上完整binlog,重启就能恢复,MySQL为什么要这么设计?
binlog写完以后MySQL发生崩溃,这时候binlog已经写入了，之后就会被从库使用。

Order By 是怎么工作的

在进行sql优化的时候,需要使用explain命令,如果extra这个字段中的"Using filesort"表示需要排序,
MySQL会给每个线程分配一块内存用于排序,成为sort_buffer。

sort_buffer_size就是MySQL为排序开辟的内存的大小。如果数据太大,超过内存,需要使用磁盘辅助临时文件辅助排序。

需要打开optimizer_trace,查看number_of_tmp_files查看是否使用了临时文件。

sort_buffer_size越小,需要分成的分数越多,number_of_tmp_files的值越大。

sort_mode里面的packed_additional_fields的意思是排序过程对字符串做了紧凑处理。

max_length_for_sort_data 是MySQL中专门控制用于排序的行数据的长度的一个参数。如果超过这个值,需要换一个算法。

覆盖索引是指索引上的信息足够满足查询请求,不需要再回到主键索引上去取数据。

如何正确地现实随机消息

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

上面语句可以使用explain,看到extra字段记录显示temporary。using temporary,表示需要使用临时表;using filesort,表示需要执行排序操作的。

对于InnoDB表来说,执行全字段排序会减少磁盘访问,因此会被优先选择。

对于内存表,回表的过程只是简单第根据数据行的位置,直接访问内存得到数据,根本不会导致多访问磁盘。

如果没有创建主键,InnoDB会自己生成一个长度为6自己的row_id来作为主键。

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

不是所有的临时表都是内存表。内存临时表tmp_table_size默认值为16M。如果超过这个限制，就会使用磁盘临时表。

磁盘临时表使用的引擎默认是InnoDB,是有参数internal_tmp_disk_storage_engine控制的。

为什么sql语句逻辑相同,性能差异巨大

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

在MySQL中,字符串和数字做比较的话,是将字符串转换成数字。

表之间的编码格式不同,进行表连接查询操作不使用索引(连接过程中要求在被驱动表的索引上加函数操作)。

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

如果MySQL数据库本身就有很大的压力,导致数据库服务器CPU占用率很高或ioutil很高,这种情况下所有语句的执行都有可能会变慢。

查询长时间不返回

大概率是表被锁住啦,使用show processlist命令,查看当前语句处于什么状态。

等DML锁

使用show processlist命令查看Waiting for table metadata lock.表示一个线程正在表t上请求或者持有MDL写锁,把select语句堵住了。
通过查询sys.schema_table_lock_waits这张表,可以直接找出造成阻塞的process id。

等flush

可以通过select * from information_schema.processlist where id = 1;查看这个process的状态，如果出现waiting for table flush。
出现flush table with read lock(只关闭表t);flush tables with read lock(关闭所有的打开的表);

等行锁

执行更新操作将整个表的大部分数据行锁住,导致其他操作阻塞。

查询慢

使用show log来查询慢log,可以设置查询的长度,set long_query_time = 0,
判断这个查询是否使用了索引,如果使用了索引，是否是当前读的原因，如果不是当前读，为了保证一致，需要会回退指定步骤之前的值，所以慢。

幻读

在可重复读隔离级别下,普通的查询是快照读，是不会看到别的事物插入的数据的。因此，幻读在当前读下才会出现。当前读的规则，就是要能读到所有已经提交的记录的最新值。
锁的设计是为了保证数据的一致性。这个一致性，不止是数据库内部数据状态在此刻的一致性,还包含了数据和日志在逻辑上的一致性。
产生幻读的原因:行锁只能锁住行,但是新插入记录这个动作，要更新的是记录之间的间隙。为了解决幻读，引入间隔锁。
行锁之间的冲突关系

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是为了影响并发度。

为什么我只改一行的语句，锁这么多?

加锁规则前提：

MySQL 版本：5.x 系列  <= 5.7.24, 8.0系列 <= 8.0.13

加锁原则:

1.加锁的基本单位是next-key lock。next-key lock是前开后闭区间。
2.查找过程中访问到的对象才会加锁。
3.索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
4.索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
5.唯一索引上的范围查询会访问到不满足条件的第一个值为止。