我对MySQL的碎碎念念（持续更新...）怒肝16k字

我对MySQL的碎碎念念（持续更新...）怒肝16k字

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

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

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

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

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

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

6、为什么不推荐用外键？

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

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

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

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

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

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

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

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

8、事务：事务就是一个完整的业务逻辑，最小的一个工作单元，只有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只能在读已提交和可重复读两种隔离级别下工作，读未提交总是会读到最新的数据行，而不是符合当前事务版本的事务行的快照读，串行化是通过加锁互斥数据，因此不存在隔离问题。不好理解？

那换句话说：对于使用Read Uncommitted隔离级别的事务来说，只需要读取版本链上最新版本的记录即可；对于使用Serializable隔离级别的事务来说，InnoDB使用加锁的方式来访问记录。

MVCC的作用？

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

那MVCC如何实现可重复读和读已提交？

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解决幻读的原理是什么？

原理依赖于三个核心点：readview、三个隐藏参数、undo log（回滚事务）三个隐藏参数：rowid，事务版本号，回滚事务版本号（上一版本号）。undo log回滚日志，用于保证一致性，用于mvcc快照读，undo log中有一个版本链。修改数据前会先把该版本信息复制一份在undolog，覆盖undolog外的这条记录并更新版本号和回滚上一版本号。rc级别下每次创建一个readview，rr下只在事务开启时创建，解决快照读的对应隔离级别问题。undo log记录历史快照，而readview可见性规则判断当前版本数据是否可见。

首先我们需要知道的一个事实是：事务id是递增分配的。ReadView的机制就是在生成ReadView时确定了以下几种信息：

ReadView的四个基本参数：

m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
max_trx_id：表示生成ReadView时系统中将要分配给下一个事务的id值。
creator_trx_id：表示创建该ReadView的事务的事务id。

这样事务id就可以分成3个区间：

区间(0, min_trx_id)：如果被访问版本的事务id< 活跃事务id列表中的最小值 ，说明生成该版本的事务在 ReadView 生成前就已经提交了，意思是比最小事务id小，这个区间里指定没它，所以该版本可以被当前事务访问。
区间[min_trx_id, max_trx_id): 如果活跃事务id最小值<被访问版本的事务id<活跃事务id最大值在 ，那就有可能存在这个活跃事务id列表中，需要判断一下 当前事务id的值是不是在活跃事务列表中。如果在，说明创建 ReadView 时生成该版本所属事务还是活跃的，因此该版本不可以被访问，需要查找 Undo Log 链得到上一个版本，然后根据该版本的事务id再从头计算一次可见性；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。
区间[max_trx_id, +∞)：如果被访问版本的事务id> 活跃事务id最大值，说明生成该版本的事务在生成 ReadView 后才生成，所以该版本不可以被当前事务访问。需要根据 Undo Log 链找到前一个版本，然后根据该版本的事务id重新判断可见性。

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

1、首先判断版本记录的事务id与ReadView中的creator_trx_id是否相等。如果相等，那就说明该版本的记录是在当前事务中生成的，自然也就能够被当前事务读取；否则进行第2步。

2、根据版本记录的事务id以及上述3个区间信息，判断生成该版本记录的事务是否是已提交事务，进而确定该版本记录是否可被当前事务读取。

如果某个版本记录经过以上步骤判断确定其可被当前事务读取，则查询结果返回此版本记录；否则读取下一个版本记录继续按照上述步骤进行判断，直到版本链的尾结点。如果遍历完版本链没有找到可读取的版本，则说明该记录对当前事务不可见，查询结果为空。
 

9、存储引擎

MySQL默认的存储引擎是InnoDB，默认字符集是UTF-8，建表时指定存储引擎和字符编码方式。

MySQL支持九大引擎，版本不同支持情况不同。 存储引擎表示不同的数据在磁盘中的不同组织形式。

（一）MyISAM存储引擎？

它管理的表具有以下特征：只支持表锁、不支持外键、不支持回滚。

格式文件 - 存储表结构的定义（mytable.frm）

数据文件 - 存储表行的内容（mytable.MYD）

索引文件 - 存储表上的索引（mytable.MYI）:索引就是一本书的目录，缩小扫描范围，提高查询效率。

MyISAM优势：可被转化为压缩、只读表来节省时间。

（二）InnoDB存储引擎

这是MySQL默认的存储引擎，是个重量级的存储引擎。

InnoDB支持事务，支持数据库崩溃后自动恢复机制。支持表锁行锁。

InnoDB存储的特点：非常安全。

（三）MEMOR存储引擎

数据存储在内存中，且表的长度固定，这两个特点使得MEMORY存储引擎非常快。不安全，关机数据消失。

10、数据类型的优化？

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

11、Exist和in的区别？

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

12、select语句执行流程？

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

13、update语句怎么执行？

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

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

     3. 查询并清空查询缓存。查询缓存

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

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

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

14、查询优化怎么做？

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

15、主从复制的作用？

  1.读写分离，降低服务器压力实现了负载均衡。

  2.主服务器出现故障时可切换到从服务器，提高性能。

  3.从服务器备份避免备份过程影响主服务器服务，确保数据安全。

16、主从复制的原理？

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

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

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

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

17、主从复制涉及哪三个线程？

1. binlog线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
2. Io线程：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Relay log）中。
3. Sql线程：负责读取重放日志并重放其中的 SQL 语句。

18、数据库的读的延时问题了解吗？

主库宕机后，数据可能丢失，从库只有一个sql Thread，主库写压力大，复制很可能延时。

19、如何解决？

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

20、主从复制和主主复制区别？

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

21、排查MySQL问题的手段：

1. show processlist命令查询当前所有连接信息。
2. Explain命令查询sql语句执行计划。
3. 开启慢查询日志，查看慢查询的日志。

22、索引

        1.索引不得不知道的基础知识？

                索引是在存储引擎用于提高数据库表的访问速度的数据结构，如何不添加索引查询会加载所有的数据进内存依次检索。索引在数据库表的字段上添加，为了提高查询效率。一张表的一个字段可以添加一个索引，也可以多个字段联合。

                MySQL中如果有unique约束也会自动创建索引对象，其实殊途同归，任何数据库的主键都会自动添加索引对象，其实都是为唯一键建立的索引，而主键是唯一且非空。

                不同的存储引擎以不同形式存在在，MySQL中，索引是一个单独的对象，MyISAM存储引擎中，索引存储在.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名tablespace的当中，在MEMORY存储引擎中，索引存储在内存当中。不管索引存储在哪里，索引在MySQL当中始终都是一个树的形式存在。

                索引要排序，排序了才有区间查找，并且这个索引的排序和TreeSets数据结构相同，一个自平衡的二叉树。

                Mysql的数据存储在磁盘中，查询慢一般卡在IO，尽可能减少io的次数和IO的量。

                去磁盘读取数据时遵循磁盘预读（内存和磁盘发生数据交互时一般有一个最小的逻辑单元称之为页（datepage），页一般由 *** 作系统来决定多大，一般是4k或8k，而我们在数据交互的时候，可以取整数倍来读取，innodb每次读取数据读取16k）。

                索引存在磁盘，查询数据的时候会优先将索引加载到内存中。

                索引分类：单一索引、联合索引、主键索引、唯一性索引。（唯一性弱的字段添加索引用处不大）。

        2.根据索引查询的原理？

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

        3.索引的使用场景？

                1、数据量庞大

                2、该字段常出现在where后面，以条件形式存在，也就是说这个字段总是被扫描。

                3、该字段很少的DML（增删改） *** 作。因为DML之后，索引需要重新排序。

        4.索引的设计原则？

                1、区分度越高越好。

                2、尽量使用短索引，较长字符串进行索引的时候应该指定个较短前缀，因为较小索引io量小查询速度快。

                3、索引不是越多越好，每个索引要花额外的物理空间，维护要需要时间。

                4、利用最左前缀原则。

        5.索引的失效？

                1、对一个索引字段模糊查询时%写在索引左边失效，反之这也是一种调优策略。

                2、使用or的时候可能失效，要求or两端的字段都有索引才会走索引，union不会让索引失效。

                3、（最左原则）使用复合索引的时候没有使用左侧的列查找，索引失效。

                4、where当中索引列参与了运算，索引失效。

                5、在where中索引列使用了函数。Explain select * from zs_case where lower(case_name)=’rxy’;

        6.索引可以用哪些数据结构来存？

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

                那为什么不用哈希表来存(k-v)?

                首先哈希表来存确实有个优点就是：等值查询时，速度很快！但同样它有以下缺点如：

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

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

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

                那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个行记录，第三层16*16*16条记录=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量。

索引的优点：

1. 大大减少了服务器要扫描的数据量。
2. 加速表和表之间的连接，帮助服务器避免排序和临时表。
3. 将随机IO变成顺序IO。

23、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是在事务开始之后就开始逐步写入磁盘。