我对MySQL的碎碎念念（持续更新...）怒肝16k字（MD版本）已修改

本文链接：https://blog.csdn.net/su_rxy/article/details/122780651

学习、面试、开发过程难免遇到MySQL难题，本文记录博主近期学习汇总和一路的成长，和我一起变成大佬吧。

首先关于数据库一个误区，我们用的MySQL、Oracle都是DBMS（数据库管理系统），用来crud数据的，而数据库是用来存数据的，而sql是结构化查询语言。程序员来写sql语句，DBMS来执行sql语句。再一个，数据库是存储数据，数据库实例才是操作数据。

查询语句的执行顺序：
from–>where–>group by–>having–>select–>order by–>limit
group by（分组函数）不能直接在 where 子句中就是因为先执行where才执行分组 group by。
select max (score) from zs_case
该语句可以执行是因为 select 在 group by 后执行。
查询如果分组了，那查询的字段只能和参与分组的字段相关，使用 having 可以对分完组后的数据继续过滤,但他必须和 group by 联合使用，而且 where 比 having 效率高。
如果数据不分组，那整张表就默认为一组，分组函数自动忽略null，判断 null 用 isnull 而不是 = 。

delete、drop、truncate 的区别：
delete（DML）删除数据后，表中数据删除了，但是这个数据在硬盘上的真实存储空间不会被释放，delete效率比较低，但是支持回滚，后悔了可以再恢复数据！
而 truncate（DDL）删除效率高，表物理删除直接被一次截断，但是不支持回滚。
drop 操作之后，整张表结构、数据都将删除。

视图：
分为普通视图、物化视图。
普通视图和物化视图根本就不是一个东西，说区别都是硬拼到一起的。
首先明白基本概念：
普通视图是不存储任何数据的，它只有定义，在查询中是转换为对应的定义SQL去查询，而物化视图是将数据转换为一个表，实际存储着数据，这样查询数据，就不用关联一大堆表，否则如果表很大的话，会在临时表空间内做大量的操作。

两种物化视图的区别：
on demand 物化视图和 on commit 视图两者刷新方式不同，二者的区别在于刷新方法的不同。
ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性；
而 ON COMMIT 是说，一旦基表有了 COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

为什么不推荐用外键？
答：外键保障了数据质量，但是影响性能，每次 delete 或 update 都必须考虑外键约束，会导致开发痛苦，测试数据不方便。

数据库表设计的三大范式：
避免数据冗余、空间浪费。但有时候需要空间换时间的反范式优化，具体情况根据业务具体分析。比如父表冗余一些数据到子表中是为了排序。

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：建立在第一范式之上，要求所有的非主键字段完全依赖主键，不要产生部分依赖。

第三范式：建立在第二范式之上，要求所有的非主键字段直接依赖主键，不要产生传递依赖。

理解1NF：考虑这样一个表：【联系人】（姓名，性别，电话）
如果在实际场景中，一个联系人的电话有家庭电话和公司电话。
那么这种表结构设计就没有达到1NF。
要符合 1NF 我们只需把列（电话）拆分。
即：【联系人】（姓名，性别，家庭电话，公司电话）。
1NF 很好辨别，但是 2NF 和3NF 就容易搞混淆。

理解2NF：考虑一张表【订单明细表】（商品名称，供应商名称，价格，描述，重量，供应商电话，有效期，分类）
由于供应商和商品多对多的关系，所以只有（商品名 + 供应商名称）才能唯一识别一件商品，也就是说这是一组组合关键字。
这张表存在以下依赖：
（商品名称）→（价格、描述、重量、商品有效期），(供应商名称) →（供应商电话）

理解3NF：考虑一张表【班级表】：（学号、姓名、年龄、所在学院、学院联系电话）
该表存在依赖传递: (学号) → (所在学院) → (学院地点，学院电话)

事务：事务就是一个完整的业务逻辑，最小的一个工作单元，只有 DML （增删改）语句和事务有关，操作涉及数据的增删改就要考虑安全。
本质是多条 DML 语句同时成功 or 同时失败。
事务执行过程中，每一条 DML 的操作都会记录到“事务性活动的日志文件”中。
事务的执行过程中我们可以提交事务 or 回滚事务。默认是每执行一条 sql 提交一次，如果要关闭就要使用开启事务命令，这个命令可以理解为关闭每条都自动提交，也可理解为开启事务。

提交事务：清空事务性活动日志，将数据全部彻底持久化到数据库表中。
回滚事务：将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件，回滚事务标志着事务的结束，并且是失败的结束。
a向b转 1w。
将a的-1w。
将b的+1w。这就是一个完整的业务逻辑。

事务四个特性：
A ：原子性：说明事务是最小的工作单元，不可再分。
C ：一致性：所有事务要求，在同一个事务当中，所有的操作必须同时从成功或者同时失败。
I ：隔离性：A 事务和 B 事务必须具有一定的隔离。
D ：持久性：事务最终结束的一个保障。

事务的隔离性（I）涉及到隔离级别（事务的隔离级别）：
读未提交（ Read Uncommitted ）最低隔离级别： A 可以读取到 B 未提交的数据，脏读现象！
读已提交（ Read Committed ）:事务 A 只能读到事务 B 已提交的数据，解决了脏读！但是不可重复读取数据！事务 A 执行到一半，突然事务 B 从头到尾执行完了，在事务 B 执行完到事务 A 结束完这段时间查询数据发生了变化。 Orcle 默认是这个级别。每次读取都是新的快照。
可重复读（Repeatable Read）：事务A开始之后，不管多久，同一事务内读取到的数据都是一致的，相当于读取的是事务开始执行时拍下的快照。即使A事务执行过程中事务B已insert并提交了，事务A读取到的数据条数仍然没变。解决了不可重复读问题，但是可能存在幻读！可能A事务执行到一半，B事务执行完了，在事务中查询到的数据是读到了假象，其实数据条数已经变了，只是在事务中是没有改变，事务后再读取就改变了，改变的如果是事务B插入的就是幻读，如果是事务A插入的就不是幻读。可重复读不允许其他事务进行update操作，但允许其他事务进行insert操作。这是MySQL默认隔离级别。每次读取都是第一次的快照。
序列化读（serializable）最高隔离级别：表示事务排队，不能并发！

如何解决并发问题（写-写冲突）？
悲观锁和乐观锁区别？
悲观锁是在是否能操作数据上就做了处理。假设会发生冲突。
乐观锁下大家都可以同时写数据只是提交时候做处理。只是假设不会发生冲突。
乐观锁可以避免数据库幻读、业务处理时间过长，适合读多写少，可以提高程序的吞吐量解决了写-写冲突，悲观锁是数据库已实现的，如共享锁、排它锁。

而乐观锁要自己实现，如何实现呢？
CAS 实现：Java 中java.util.concurrent.atomic包下面的原子变量使用了乐观锁的一种 CAS 实现方式。
版本号控制：一般是在数据表中加上一个数据版本号 version 字段，表示数据被修改的次数。当数据被修改时，version 值会+1。当线程 A 要更新数据时，在读取数据的同时也会读取 version 值，在提交更新时，若刚才读取到的 version 值与当前数据库中的 version 值相等时才更新，否则重试更新操作，直到更新成功。

什么是共享锁和排它锁？
共享锁：又称为读锁，如果事务T1对数据对象O1加上了共享锁，那么当前事务只能对O1进行读取操作，其他事务也只能对这个数据对象加共享锁读取数据——直到该数据对象上的所有共享锁都被释放。修改是惟一的，必须等待前一个事务commit，才可进行操作，因此，如果同时并发太大可能很容易造成死锁。
排它锁：又称为写锁或独占锁，如果事务 T1 对数据对象 O1 加上了排他锁，那么在整个加锁期间，只允许事务 T1 对 O1 进行读取和更新操作，其他任何事务都不能再对这个数据对象进行任何类型的操作（读、写）——直到 T1 释放了排他锁，结果是超时或者等待第一个事务提交后再执行，相当于行锁。

排它锁和共享锁区别是：加上排他锁后，数据对象只对一个事务可见，而加上共享锁后，数据对所有事务都可见。
误区：对于共享锁大家可能很好理解，就是多个事务只能读数据不能改数据，对于排他锁大家的理解可能就有些差别，我当初就犯了一个错误，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。MySQL 的 InnoDB 引擎自动的给修改数据语句（update,delete,insert）涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，如果加排他锁可以使用 select … for update语句，加共享锁可以使用select … lock in share mode 语句，所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过 for update 和 lock in share mode 锁的方式查询数据，但可以直接通过select … from … 查询数据，因为普通查询没有任何锁机制。

MVCC是什么？
MVCC其实就是乐观锁的一种实现方式，MVCC只能在读已提交（RC）和可重复读（RR）两种隔离级别下工作，读未提交总是会读到最新的数据行，而不是符合当前事务版本的事务行的快照读，串行化是通过加锁互斥数据，因此不存在隔离问题。不好理解？
那换句话说：对于使用Read Uncommitted隔离级别的事务来说，只需要读取版本链上最新版本的记录即可；对于使用Serializable隔离级别的事务来说，InnoDB使用加锁的方式来访问记录。

MVCC的作用？
MVCC是一种用来解决读-写冲突的无锁并发控制，即使有读写冲突时，也能做到不加锁，非阻塞并发读。

那MVCC如何实现可重复读（RR）和读已提交（RC）？
MVCC会在每一行的数据后面增加两个隐藏的列，分别是创建时间、到期时间，这里的时间是用系统的版本号来确定，那系统的版本号如何计算？每当开始一个新事务，系统版本号都会递增：获取当前的系统版本号并作为该事务的事务id（事务版本号），来和每行数据的末尾两列进行比较。由此可见，MVCC 只是一种乐观锁的版本号控制的实现方式。

到底什么才是快照读和当前读？
快照读不会加锁，当前读会加锁。除了 RC 和 RR 这俩隔离级别下的普通 select 操作，其余操作都是当前读。在 RR 下，普通的查询是快照读，当前事务是不会看到别的事务插入的数据的。因此，幻读问题在 “当前读”下才会出现。当前读就是读取最新版本数据。

那 MVCC 能否能解决幻读呢?
幻读：幻读仅仅指读取到了新插入的行的数据。由其他事务 update 产生的当前读不属于幻读的范畴。
先给出结论：MVCC 解决了的快照读的幻读，当前读没有解决。如下所示。
事务1 开启事务，select 只有两行记录，在 T1 时刻，事务2 插入一条数据并且马上提交，insert 时确实会加一个 gap 锁，但是该事务提交后 gap 就释放了可以随意 DML 增删改了，在T2时刻再次 select 读到与 T1 时刻一样的数据（即未读到新插入的数据），到这是没有问题的，满足可重复读。
在 T3 时刻，update 不加条件，即 update 整张表，即 update 了 T1 时刻事务2 插入的这条数据(该条数据在 T3 时刻看不见)，却执行成功了，在 T4 时刻 select 却发现多了一个记录。
原因是RR读不会受到其他事务 update、insert 的影响，但是自己执行了 update 就会把其他事务 insert 的数据更新成自己的版本号，下一次读取就会读到了。可重复读级别避免了不可重复读，即使事务B 在事务A 两次读取数据中间新增了数据，但是两次读取的结果还是一样的。只是 update 的时候会发现多 update 或者少 update 了几条。RR 级别那对于 select 快照读就没有产生幻读，而对于 update 当前读并未产生了幻读，因为版本号被自己的版本号覆盖了，但是如果没有被覆盖的操作不就解决不了当前读的幻读？所以加个锁next-lock key。
总结下 MySQL 在 RR 下解决幻读的手段：快照读 MVCC + 当前读 Next-Lock Key(只在可重复读隔离级别下生效)。

首先MVCC属于快照读，在RC下是每次 select 都会生成新的 read
view ，所以存在不可重复读和幻读，而在 RR 下并不会存在快照读的幻读，只会出现当前读的幻读，而当前读的幻读现象要Next-Lock Key来解决。
关于何时加什么锁：
select … lock in share mode：加共享(S)锁
select … for update：加排他(X)锁
insert / update / delete：加排他(X)锁
大概说下当前读的不可重复读、幻读怎么解决？
RC 加行锁解决了当前读的不可重复读。
RR 加行锁➕间隙锁解决了当前读的不可重复读和幻读。
当前读如何解决幻读？
因为 InnoDB 对于当前读行的查询都是采用了 Next-Key Lock 的算法，锁定的不是单个值，而是一个范围（GAP）。
假设索引 a 有1，3，5，8，11，其记录的GAP的区间如下：是一个左开右闭的区间（原因是默认主键的有序自增的特性）
（-∞,1]，(1,3]，(3,5]，(5,8]，(8,11]，(11,+∞）
select * from t where a = 3 for update;
那么锁住的范围有 (1,3）3，(3,5]
即当前行+两边的间隙 (1,3]，(3,5]，这些范围的行数据和索引都被锁住，所以可以防止 insert 或者 delete 带来的幻读。
但是注意，对于可重复读默认使用的就是 next key lock，但是对于“唯一索引” ，比如主键的索引，next key lock 会降级成行锁 Record Lock ，即仅锁住索引本身，而不会锁住一个区间。唯一索引的等值条件就是从 next lock key降级成行锁。
而范围条件如
select * from t where a > 3 for update;
就降级到间隙锁锁住(3,5]。如果条件是>=3那就是行锁+间隙锁都用上了。

快照读的MVCC解决幻读的原理是什么？
原理依赖于三个核心点：read view、三个隐藏参数、undo log（回滚事务）
三个隐藏参数：rowid，事务版本号，回滚事务版本号（上一版本号）。
undo log 回滚日志，用于保证一致性，用于 mvcc 快照读，undo log 中有一个版本链。修改数据前会先把该版本信息复制一份在 undo log，覆盖undo log 外的这条记录并更新版本号和回滚上一版本号。RC 级别下每次创建一个 read view，RR 下只在事务开启时创建，解决快照读的对应隔离级别问题。undo log 记录历史快照，而 read view 可见性规则判断当前版本数据是否可见。
首先我们需要知道的一个事实是：事务id是递增分配的。
ReadView 的机制就是在生成 ReadView 时确定了以下几种信息：
ReadView 的四个基本参数：
m_ids：表示在生成 ReadView 时当前系统中活跃的读写事务的事务 id 列表。
min_trx_id：表示在生成 Read View 时当前系统中活跃的读写事务中最小的事务 id ，也就是 m_ids 中的最小值。
max_trx_id：表示生成 Read View 时系统中将要分配给下一个事务的 id 值。
creator_trx_id：表示创建该 Read View 的事务的事务 id 。
这样事务id就可以分成3个区间：
区间(0, min_trx_id)：如果被访问版本的事务 id < 活跃事务 id 列表中的最小值，说明生成该版本的事务在 Read View 生成前就已经提交了，意思是比最小事务 id 小，这个区间里指定没它，所以该版本可以被当前事务访问。区间 [min_trx_id，max_trx_id) : 如果活跃事务 id 最小值 < 被访问版本的事务id < 活跃事务id最大值，那就有可能存在这个活跃事务 id 列表中，需要判断一下当前事务 id 的值是不是在活跃事务列表中。如果在，说明创建 Read View 时生成该版本所属事务还是活跃的，因此该版本不可以被访问，需要查找 Undo Log 链得到上一个版本，然后根据该版本的事务 id 再从头计算一次可见性；如果不在，说明创建 Read View 时生成该版本的事务已经被提交，该版本可以被访问。
区间[max_trx_id, +∞)：如果被访问版本的事务 id > 活跃事务 id 最大值，说明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。需要根据 Undo Log 链找到前一个版本，然后根据该版本的事务id重新判断可见性。

接下来实测一下这条规则：

首先判断版本记录的事务 id 与 Read View 中的 creator_trx_id 是否相等。如果相等，那就说明该版本的记录是在当前事务中生成的，自然也就能够被当前事务读取；否则进行第2步。
根据版本记录的事务 id 以及上述3个区间信息，判断生成该版本记录的事务是否是已提交事务，进而确定该版本记录是否可被当前事务读取。
如果某个版本记录经过以上步骤判断确定其可被当前事务读取，则查询结果返回此版本记录；否则读取下一个版本记录继续按照上述步骤进行判断，直到版本链的尾结点。如果遍历完版本链没有找到可读取的版本，则说明该记录对当前事务不可见，查询结果为空。

存储引擎
MySQL默认的存储引擎是InnoDB，默认字符集是UTF-8，建表时指定存储引擎和字符编码方式。
MySQL支持九大引擎，版本不同支持情况不同。存储引擎表示不同的数据在磁盘中的不同组织形式。
MyISAM存储引擎？
它管理的表具有以下特征：只支持表锁、不支持外键、不支持回滚。
格式文件 - 存储表结构的定义（mytable.frm）
数据文件 - 存储表行的内容（mytable.MYD）
索引文件 - 存储表上的索引（mytable.MYI）
MyISAM优势：可被转化为压缩、只读表来节省时间。
InnoDB存储引擎
这是MySQL默认的存储引擎，是个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。支持表锁行锁。
InnoDB存储的特点：非常安全。
MEMOR存储引擎
数据存储在内存中，且表的长度固定，这两个特点使得MEMORY存储引擎非常快。不安全，关机数据消失。

数据类型的优化？
更小的通常更好：尽量使用不会超出范围的最小的占用空间的类型。
简单就好：什么类型就用什么类型去存，如果是 ip 就调用方法转换成 int 类型去存。因为 int 比字符串节省很多空间。
尽量避免使用 null：对于包含可为 null 列时很难优化。
实际类型细则：
整数类型：可以使用的几种整数类型：tiny int、small int、medium int、int、big int 分别是8、16、24、32、64 位存储位置。尽量使用满足需求的最小的数据类型。
字符串类型：char、varchar、blob、text。（一个误区：文件大小和文件占用空间完全不同，因为 4kb 是一个读取数据的基本单元，那 4.01kb 的还是要占用两个 4kb 即两个基础单元（页）来存储，所以文件大小是 4.01kb，占用空间是 8kb 。读一个数据会把该基础单元（页）里的数据都读进来这就是磁盘预读。）varchar：使用最小符合需求的长度，varchar(n)，当 n<=255 时会用额外一个字节保存长度 > 255 时会使用额外两个字节来保存长度，varchar(5) 和 varchar(255) 保存同样内容硬盘存储空间相同，但内存占用空间不同（磁盘预读）。varchar 在 MySQL 5.6 前变更长度从 255 变更到 255 以上会导致锁表。Char 最大长度 255 ，会删除末尾空格。
时间戳类型：datetime 占 8 字节，与时区无关，数据库底层对时区对配置对其无效，不用要字符串类型来存，空间占用大。Timestamp 占 4 个字节，时间范围 1970-1-1 到 2038-1-19，精切到秒。Date类型占3个字节，日期范围 1000-1-1 到 9999-12-31 。

Exist和in的区别？

exists是一个存在判断。
如果后面的查询中有结果,则exists为真，否则为假，检测行的存在。

select 语句执行流程？
应用程序把 sql 发送到 server 端执行。
连接器： Client 到 server 首先检查权限，没有权限就返回错误。
分析器：MySQL8.0之前会查询缓存，命中就直接返回，没有就下一步。查询缓存词法语法分析，提取表名查询条件，检查语法是否错误。
优化器：优化器根据自己的算法选择效率高的执行计划。
执行器：校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。

update语句怎么执行？

应用程序把 sql 发送到 server 端执行。

连接器：首先检查权限，没有权限就返回错误。

查询缓存：查询并清空查询缓存。

分析器 ：词法语法分析，提取表名查询条件。检查语法是否错误。

优化器 ：优化器根据自己的算法选择效率高的执行计划。

执行器 ：校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。 eg：更新会写日志文件。

查询优化怎么做？
减少请求的数据量：只返回有必要的列尽量避免 select * 、只返回有必要的行，用 limit 限制、缓存重复查询的数据。
减少服务端扫描的行数：最有效就是避免回表（走两棵 b+ 树），而是尽量索引覆盖查询（走一棵 b+ 树）。

主从复制的作用？
读写分离，降低服务器压力实现了负载均衡。
主服务器出现故障时可切换到从服务器，提高性能。
从服务器备份避免备份过程影响主服务器服务，确保数据安全。

主从复制的原理？
数据库下bin-log二进制文件，记录了所有的sql语句，把主数据库下的bin-log文件的sql语句拷贝过来让其在从数据库的redo-log（重做日志文件）再执行一遍这些sql语句。需要三个线程操作。

binlog线程：每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。在从库里，当复制开始的时候，从库就会创建下列的两个线程进行处理：

Io线程：当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay
log文件。

Sql线程：从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

主从复制涉及哪三个线程？
binlog 线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
IO 线程：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Relay log）中。
Sql 线程：负责读取重放日志并重放其中的 SQL 语句。

数据库的读的延时问题了解吗？
主库宕机后，数据可能丢失，从库只有一个 sql Thread，主库写压力大，复制很可能延时。

如何解决？
半同步复制解决数据丢失，并行复制解决从表复制延迟。

主从复制和主主复制区别？
最大区别是：
主从复制是对主数据库操作数据，从数据库会实时同步数据。反之对从数据库操作，主数据库不会同步数据，还有可能造成数据紊乱，导致主从失效。
主主复制则是无论对哪一台操作，另一个都会同步数据。一般用作高容灾方案。

排查MySQL问题的手段：
show processlist命令查询当前所有连接信息。
Explain命令查询sql语句执行计划。
开启慢查询日志，查看慢查询的日志。

索引

索引不得不知道的基础知识？
索引是在存储引擎用于提高数据库表的访问速度的数据结构，如何不添加索引查询会加载所有的数据进内存依次检索。索引在数据库表的字段上添加，为了提高查询效率。一张表的一个字段可以添加一个索引，也可以多个字段联合。
MySQL 中如果有 unique 约束也会自动创建索引对象，其实殊途同归，任何数据库的主键都会自动添加索引对象，其实都是为唯一键建立的索引，而主键是唯一且非空。
不同的存储引擎以不同形式存在在，MySQL 中，索引是一个单独的对象，MyISAM 存储引擎中，索引存储在.MYI 文件中。在 InnoDB 存储引擎中索引存储在一个逻辑名 tablespace 的当中，在 MEMORY 存储引擎中，索引存储在内存当中。不管索引存储在哪里，索引在 MySQL 当中始终都是一个树的形式存在。
索引要排序，排序了才有区间查找，并且这个索引的排序和TreeSets数据结构相同，一个自平衡的二叉树。
Mysql的数据存储在磁盘中，查询慢一般卡在IO，尽可能减少io的次数和IO的量。
去磁盘读取数据时遵循磁盘预读（内存和磁盘发生数据交互时一般有一个最小的逻辑单元称之为页（datepage），页一般由操作系统来决定多大，一般是 4k 或 8k ，而我们在数据交互的时候，可以取整数倍来读取， innodb 每次读取数据读取 16k ）。
索引存在磁盘，查询数据的时候会优先将索引加载到内存中。
索引分类：单一索引、联合索引、主键索引、唯一性索引。（唯一性弱的字段添加索引用处不大）。

根据索引查询的原理？
例如 select * from t_user where id =101；发现id字段上有索引先通过索引对象idIndex进行树的查找，通过 id=101 得出物理编号0x666 ，此时会转换SQL语句为：
select * from t_user where 物理编号 = 0x666；

索引的使用场景？

数据量庞大
该字段常出现在where后面，以条件形式存在，也就是说这个字段总是被扫描。
该字段很少的DML（增删改）操作。因为DML之后，索引需要重新排序。

索引的设计原则？

区分度越高越好。
尽量使用短索引，较长字符串进行索引的时候应该指定个较短前缀，因为较小索引io量小查询速度快。
索引不是越多越好，每个索引要花额外的物理空间，维护要需要时间。
利用最左前缀原则。

索引的失效？

对一个索引字段模糊查询时%写在索引左边失效，反之这也是一种调优策略。
使用or的时候可能失效，要求or两端的字段都有索引才会走索引，union不会让索引失效。
（最左原则）使用复合索引的时候没有使用左侧的列查找，索引失效。
where当中索引列参与了运算，索引失效。
在where中索引列使用了函数。
Explain select * from zs_case where lower(case_name)=’rxy’;

索引可以用哪些数据结构来存？
索引存储的时候需存文件地址、偏移量offset、key，可以用哈希表，树（二叉树、红黑树、AVL树、B树、B+树）为什么最后用B+树存。

那为什么不用哈希表来存(k-v)?
首先哈希表来存确实有个优点就是：等值查询时，速度很快！但同样它有以下缺点如：

哈希冲突导致数据散列不均匀，会产生大量的线性查询，比较浪费时间。

必须等值判断来查询，不支持范围查询，当进行范围查询时必须挨个遍历。

对于内存空间的要求比较高，要把所有数据加载到内存才能操作找到对应的数据。

那 MySQL 中常见的存储引擎有没有hash索引呢？
1. memory 存储引擎使用的是hash索引。
2. Innodb 支持自适应 hash 索引，key 是索引列的 hashcode，指向行记录的指针是 value 。查找一条数据的时间复杂度O（1），多用于精确查找。

那为什么不用普通树的结构？
如果用的是普通二叉树，那数据插入是递增的时候就会从二叉树 O（logn）退回成链表 O（n）。但是可以通过左旋或者右旋让树平衡起来弥补这一缺陷，此时形成的是 AVL （平衡二叉树）：最短子树跟最长子树高度只差1，为了保证平衡，在插入数据的时候必须要旋转，通过插入、删除性能的损失来弥补查询性能（插入、删除会调整节点）。但是此时又出现了新的问题：如何读 >> 写，AVL 是划算的，但是读写差不多甚至写 >> 读呢？所以就要左旋右旋的次数减少来提高增删的效率。怎样使得它更少的旋转呢？
那为什么不用红黑树？
通过变色减少旋转的次数，最长子树只要不超过最短子树的两倍即可，既有旋转又有变色，使得插入和查询性能近似取得平衡。但是随着数据的插入，发现树的深度会变深，树的深度越深，意味着 IO 次数越多，影响数据的读取效率，而且由于局部性原理（经常被查询的数据有聚集成群的倾向，同时刚被查询的数据有可能很快被再次查询）、磁盘预读（磁盘读取数据并不是只读到需要的，而是有一个磁盘和内存进行交互的最小逻辑单元，一般都和操作系统有关，是 4k 或者 8k ，而对于 InnoDB 存储引擎也有自己的最小储存单元，页（Page），一个页的大小是 16K ），使得它不得不做出改变，做出了在树的横向做文章的改变，一个头节点对应多个子节点就解决了这个问题，这是采用的数据结构就是 B树了，树的深度会小很多，一般<=3，深度是算出来的，不是指定的。这种情况下实际存储的数据为：key，完整的数据行。但是它叶子节点和非叶子节点都存有数据行使得树在每一深度的索引都存的十分有限，不理解？
举个例子：假设一个磁盘块的大小为 16k ，假设一条行数据 1k ，树深度为三层，第一层 16k 最多存 16 个行记录，第二层能存 16×16 个行记录，第三层 161616条记录 = 4096条，这效率太低了吧？只让叶子节点存数据，非叶子节点只存索引和 key 值，一二层就能存更多的磁盘块索引，这就是 B+ 树。我们来算算，如果没有创建主键约束、唯一键约束、系统会自生成 6字节的 rowId 给它作为聚簇索引的 key 是 6+6（指针大小在 InnoDB 源码中设置为6字节），这能存多少？大概两千多万。而且 B+树的叶子节点用指针连接，提高区间访问性能也方便扫库。那索引是用 int 还是 varchar 呢？Varchar超过四个字节用 int ，小于四个字节用 varchar，因为空间固定是16kb，每个索引*索引个数<= 16kb，占的单个字节越少，存储的字节就可以越多。索引的创建和存储引擎是挂钩的。是否是聚簇索引取决于数据是否和索引放在一起。Innodb 只能有一个聚簇索引（为了防止数据冗余，如果有多个聚簇索引，就会导致一份数据存多份，多个索引，只能有一个作为 key，如果多个作为 key 就冗余了），但是可以有很多非聚簇索引：向 innodb 插入数据时必须要包含一个索引的 key 值，这个索引的 key 值可以是主键，如果没有主键就是唯一值，如果没有唯一值那就是自生成的6字节的 rowId 当聚簇索引 key 。如果一个表中的普通列创建了索引，那么叶子结点存放的值是聚簇索引的 key 值。

Myisam 全是非聚簇索引，但是与 innodb 中不同的是，innodb 直接放数据行，myisam 放的是数据行地址，根据地址再去找数据。聚簇索引是和数据文件和索引文件放在一起的索引。

什么是回表、索引覆盖？
一张表四个字段（id，sex，name，age，name）是索引列，主键是id，select * from table where name=‘rxy’
该语句先会根据非聚簇索引 name 字段查询到 id，再根据聚簇索引 id 字段查询整行记录，走了两棵 b+树，此时这种现象叫做回表。就是根据普通索引查询到聚簇索引的 key 值后，再根据 key 值在聚簇索引中获取行记录。如果是
select id,name from table where name=‘rxy’；
该语句根据 name 可以直接查询到 id ，name 两个字段，直接返回即可，不需要从聚簇索引查询任何数据，此时就不需要回表，叫做索引覆盖。回表会增加IO量，会影响效率。

设计数据库时id要不要自增？
尽量自增，自增不会影响前面的磁盘块，如果不自增，可能插入一个数据在一个容量不够的磁盘块中，则会磁盘块分裂（也分裂），类似的操作删除会导致页合并。分裂调整效率比较低，而自增直接append效率比较高。或者说，就是UUID随机生成，比较分裂，随机插入，会造成频繁页分裂，内存碎片化，大量随机IO。

最左匹配原则？
MySQL内部有优化器：
select * from table where 最左索引 = ‘ss’and 其他索引 = ‘hh’；
把两个索引位置对换也走索引。因为优化器会重新把最左索引放在左边。但是如果where中没有最左索引，就不会走索引。如果用范围查询，就会停止该索引字段之后的字段的匹配，比如 a > = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d 是用不到索引的，因为 c 字段是一个范围查询，它之后的字段会停止匹配。

索引下推？
select * from table where name=? and age=?；
数据库分为三层：client、server、存储引擎。
Client 就是可视化工具。
Server 服务是和引擎做数据交互，server 层其中有包含连接器、查询缓存、分析器、优化器、执行器。
select * from table where name = ？ and age = ？;
在没有索引下推之前：先根据 name 从存储引擎中获取符合规则的数据，然后在 server 对 age 进行过滤。
有索引下推之后：根据 name，age 两个条件从存储引擎中获取对应数据。从service做计算到了存储引擎做计算。减少了server和存储引擎的io量。
索引的优点：
大大减少了服务器要扫描的数据量。加速表和表之间的连接，帮助服务器避免排序和临时表。将随机IO变成顺序IO。

MySQL常见的日志：
redo log（重做日志）、undo log（回滚日志）、binlog（二进制日志）？
redo log 是 InnoDB 引擎特有的，只记录该引擎中表的修改记录。binlog 是MySQL的 Server 层实现的，会记录所有引擎对数据库的修改。
redo log 是物理日志，记录的是在具体某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的，binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
redo log（重做日志）让 Innodb 有了崩溃恢复的能力，MySQL 实例挂了或宕机后重启时 Innodb 会使用 redo log 恢复数据，保证数据的完整和持久。后续的查询直接在查询缓存中查询，没命中再去硬盘加载，减少了 IO 开销。更新表时也是这样，发现缓存里存在要更新的数据，就直接在缓存里更新，然后会记录“哪个数据页做了什么修改”记录在 redo log 的缓存，接着刷盘到 redo log。
理想状态是事务一提交就刷盘，实际上刷盘时机是根据策略来进行，有0，1，2三个策略。0是每次提交不刷，1（默认值）是每次提交都刷，2是每次事务提交都只把 redo log缓存写入 page cache（系统缓存），另外Innodb存储引擎有一个后台线程，每隔一秒，就会把 redo log 缓存写到 page cache（系统缓存），然后刷盘。也就是说一个没提交的 redo log 记录可能也会被刷盘，因为在事务执行过程 redo log 记录是会写入 redo log 缓存，这些 redo log 会被后台线程刷盘，除了后台每秒一次刷盘还有一种情况会导致刷盘，当redo log 缓存占用的空间即将达到 innodb_log_buffer_size 的一半时，后台线程会主动刷盘。所以，为0时，MySQL 挂了或宕机了可能有一秒的数据丢失，为1时，redo log一定在硬盘里不会丢失，就算挂了，日志也会丢失，但是事务并没提交，也不会损失。为2时，只要事务提交成功了，redo
log缓存内容只写入page cache，如果是MySQL挂了不会丢失数据，宕机可能会有1秒数据丢失。
redo log是个包含四个文件环形文件组，写完了会从头覆盖继续写。binlog用于复制，从库利用主库上的bin log进行传播，实现主从同步。
事务提交的时候，一次性将事务中的 sql 语句（一个事物可能对应多个sql语句）按照一定的格式记录到 binlog 中。
binlog 与 redo log 很明显的差异就是 redo log 并不一定是在事务提交的时候刷新到磁盘，redo log 是在事务开始之后就开始逐步写入磁盘。