MYSQL

目录

数据库之MYSQL相关知识

数据库基础知识

1、MySQL基本架构

2、连接器
进行数据查询前，第一步就是连接数据库，这时就是连接器和我们对接。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

连接的时候会经过TCP握手，然后身份验证，输入用户名密码。

验证OK连上MySQL服务，这时我们处于空闲状态。

注：查看空闲连接列表：
show processlist，下图就是我在自己的数据库表执行命令的结果，其中的Command列显示为Sleep的这一行，就表示现在系统里面有一个空闲连接。

这里需要注意的是，我们数据库的客户端太久没响应，连接器就会自动断开了，这个时间参数是wait_timeout控制住的，默认时长为8小时。

除了重新连接（建立连接是比较麻烦），还可以使用长连接，但是需要注意：
使用长连接之后，内存会飙得很快，我们知道MySQL在执行过程中临时使用的内存是管理在连接对象里面的。只有在链接断开的时候才能得到释放，那如果一直使用长连接，那就会导致OOM（Out Of Memory），会导致MySQL重启，在JVM里面就会导致频繁的Full GC。

一般解决方案是：一般会定期断开长连接，使用一段时间后，或者程序里面判断执行过一个占用内存比较大的查询后就断开连接，需要的时候重连就好了。

或者，执行比较大的一个查询后，执行mysql_reset_connection可以重新初始化连接资源。这个过程相比上面一种会好点，不需要重连，但是会初始化连接的状态。

3、查询缓存
MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。大家是不是好奇同一条语句在MySQL执行两次，第一次和后面的时间是不一样的，后者明显快一些，这就是因为缓存的存在。他跟Redis一样，只要是你之前执行过的语句，都会在内存里面用key-value形式存储着。查询的时候就会拿着语句先去缓存中查询，如果能够命中就返回缓存的value，如果不命中就执行后面的阶段。

注：但缓存的弊大于利，缓存的失效很容易，只要对表有任何的更新，这个表的所有查询缓存就会全部被清空，就会出现缓存还没使用，就直接被清空了，或者积累了很多缓存准备用来着，但是一个更新打回原形。这就导致查询的命中率低的可怕，只有那种只查询不更新的表适用缓存，但是这样的表往往很少存在，一般都是什么配置表之类的。

如果不想用缓存，或者用缓存，可以显示调用，把query_cache_type设置成DEMAND，这样SQL默认不适用缓存，想用缓存就用SQL_CACHE。（不过缓存在MySQL8.0之后就取消了。）

4、分析器
在缓存没有命中的情况下，就开始执行语句了，你写的语句有没有语法错误，这是接下来MySQL比较关心的点。那他会怎么做呢？会先做词法分析，你的语句有这么多单词、空格，MySQL就需要识别每个字符串所代表的是什么，是关键字，还是表名，还是列名等等。然后就开始语法分析，根据词法分析的结果，语法分析会判断你sql的对错，错了会提醒你的，并且会提示你哪里错了。

5、优化器
优化就比较简单了，因为我们建立表可能会建立很多索引，优化有一步就是要确认使用哪个索引，比如使用你的主键索引，联合索引还是什么索引更好。还有就是对执行顺序进行优化，条件那么多，先查哪个表，还是先关联，会出现很多方案，最后由优化器决定选用哪种方案。

6、执行器
第一步可能就是权限的判断，执行的时候，就一行一行的去判断是否满足条件，有索引的执行起来可能就好点，一行行的判断就像是接口都提前在引擎定义好了，所以他比较快。

数据库的慢日志有个rows_examined字段，扫描多少行可以看到，还有explain也可以看到执行计划，我们扫描了多少行。

储存引擎（即表类型）

MYSQL常用的两个储存引擎是MyIsam和InnoDB，主要的差别在于，MyISAM不支持事务处理等高级处理，强调的是性能，性能优先，执行速度比InnoDB快，而InnoDB支持事务处理和外部键等高级数据库功能。
总：InnoDB支持事务与外键和行级锁。而MyISAM不支持。

1. MyISAM ，是MYSQL默认的引擎，不支持行级锁和外键，因此当INSERT（插入）或update(更新)数据时即写操作需要锁定整个表，效率会低一些。但执行读取操作的速度很快，而且不占用大量的内存的储存资源。
2. InnoDB，底层存储结构为B+树，每个节点对应的InnoDB的一个page，page大小是固定的，一般设为16k。其中非叶子节点只有键值，叶子节点包含完成数据。比较适用于经常更新的表，适合处理多重并发的更新请求。
   ①MySQL架构图
   
   ②InnoDB架构图
   
   通常我们说的MySQL高性能高可靠，都是指基于InnoDB存储引擎的MySQL。
   InnoDB主要分为两块：

• InnoDB In-Memory Structures
• InnoDB On-Disk Structures
  内存和磁盘。

InnoDB内存架构
1、Buffer Pool
缓冲池是主存中InnoDB缓存被访问的表和索引数据的区域。

MySQL 不会直接去修改磁盘的数据，因为这样做太慢了，MySQL 会先改内存，然后记录 redo log，等有空了再刷磁盘，如果内存里没有数据，就去磁盘 load。

而这些数据存放的地方，就是Buffer Pool。

MySQL 是以「页」（page）为单位从磁盘读取数据的，Buffer Pool 里的数据也是如此，实际上，Buffer Pool是一个以页为元素的链表。

为什么是链表？因为和缓存一样，它也需要一套淘汰算法来管理数据。

Buffer Pool 采用基于 LRU（least recently used） 的算法来管理内存：

2、Change Buffer
3、Adaptive Hash Index
4、Log Buffer

Operating System Cache

InnoDB磁盘架构
1、表空间(Tablespaces)
2、Doublewrite Buffer

数据库索引

关于索引常考的面试题有：
索引有哪些数据类型？
索引是怎么样的一种结构？
哪些字段又适合索引呢？
B+树的优点？
聚合索引和非聚合索引的区别？
为什么说索引会降低插入、删除、修改等维护任务的速度？
…
##1.索引有哪些数据类型？
答：Hash 和 B+树，在设计索引的时候，会发现索引类型是可以选择的。
①字段值所对应的数组下标是哈希算法随机算出来的，所以可能出现哈希冲突，哈希表的特点就是可以快速的精确查询，但不支持范围查询。
eg：
select * from table_1 where name = ‘xiaozhu’(支持)；
select * from table_1 where name > ‘xiaozhu’(不支持)。
题外话：Hash表比较适合于只有Key-Value的情况。
②Hash表是无序的数据结构，有序的数据结构比如有序数组，它在等值查询和范围查询都很不错，它的缺点就是适合静态数据，如果数据发生新增、删除、修改时就会改表这个有序数组的结构，成本比较高。所以比较适合做静态存储引擎，用来保存静态数据，如过去发生的事情。
③二叉树是有序的，支持范围查询，但是时间复杂度是O(log(N))，为了维持这个时间复杂度，更新的时间复杂度也得是O(log(N))，那就得保持这棵树是完全平衡二叉树了。
④索引不只是在内存里面存储，是需要落盘到持久化的，当数据较多时，树高会很高，查询成本就会随着树高的增加而增加。
⑤B树高度要比完全平衡二叉树表示同样的数据要低，磁盘IO效率提高了，原因在于B树中的一个节点可以存储多个元素。

⑥同样的元素，B+树的表示要比B树宽，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。B+树是B树的升级版，只是把非叶子节点冗余了一下，这样做的好处是为了提高范围查找的效率，原因是会有指针指向下一个节点的叶子节点。
⑦总：Mysql选用B+树这种数据结构作为索引，可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。
⑧B+树中一个节点为一页或页的倍数最为合适。因为如果一个节点的大小小于1页，那么读取这个节点的时候其实也会读出这一整页，造成资源的浪费。如果一个节点的大小大于1页，比如1.2页，那么读取这个节点的时候会读出2页，也会造成资源的浪费。
⑨关于页，Mysql的基本存储结构是页（记录都在页里面）：

• 各个数据页可以组成一个双向链表；
• 每个数据页中的记录可以组成一个单向链表；
• 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录；
• 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。
  eg：select * from user where username=‘xioazhu’
  会定位到记录所在页，需要双向链表找到所在页，从所在页之内查找相应的记录，由于不是根据主键查询，只能遍历所在页的单链表了。
  缺点明显：在数据量大的情况下查询会很慢，所以一般不建议写 select * from …

⑩回表：回表大概就是我们有个主键为ID的索引，和一个普通name字段的索引，我们在普通字段上搜索：
select * from table where name = ‘xiaozhu’
执行的流程是先查询到name索引上的“xiaozhu”，然后找到他的id是2，最后去主键索引，找到id为2对应的值。
回到主键索引树搜索的过程，就是回表。不过也有方法避免回表，那就是覆盖索引。

什么是覆盖索引？
　　1）只需要在一棵索引树上就可以获取sql所需所有的列数据，不需要回表，较之回表速度要更快。它也是我们在实际开发过程中经常用来优化查询效率的方法。
　　2）explain输出结果extra字段为Using index时，触发了索引覆盖。
如何实现覆盖索引？
　　办法：将被查询的字段建立到联合索引中
最左匹配原则

• 索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即联合索引。
• 如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。
• 因此，列的排列顺序决定了可命中索引的列数。
  eg：如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)。

##2.索引是如何加速查询的？
①聚簇索引
（存储有完整行数据的索引，就叫聚簇索引）
执行建表语句：

CREATE TABLE `student` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '主键id',
  `student_no` VARCHAR(64) COMMENT '学号',
  `name` VARCHAR(64) COMMENT '学生姓名',
  `age` INT COMMENT '学生年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='学生信息表';

插入5条数据：

insert into student(student_no,name,age) values(101,"Alice",18);
insert into student(student_no,name,age) values(102,"Bob",19);
insert into student(student_no,name,age) values(104,"Brandt",15);
insert into student(student_no,name,age) values(105,"David",19);
insert into student(student_no,name,age) values(109,"David",18);

在插入的过程中，MySQL 会用你指定的主键，在这里是递增主键，维护起一棵 B+树。
此B+树维护起来有几个特性：

• 有序：左边节点比右边小
• 自平衡：左右两边数量趋于相等
• 节点分裂：节点数量超过节点容量时，分裂成两个：
  
• B+树的叶子节点是带有行的全部数据，非叶子节点存放的是主键值：
  
  注：如果没有主键索引，数据是无序的，查询某条具体记录时，只会全表扫描。
• 建立索引之后，查找 id=5，查询步骤变为：
• 从上到下，先找到 3，5 比它大，找右节点；
• 接着找到 4，发现 5 还是比它大，继续找右节点
• 这次到达叶子节点了，叶子节点是一个递增的数组，那就用二分法，找到 id=5 的数据

你要访问磁盘的次数，是由这棵树的层数决定的。

②二级索引（不带行数据完整信息的索引，就叫二级索引，也叫辅助索引）
如果建立了主键索引，但现在想根据姓名查询还是需要全表扫描。如果不想全表扫描，就需要给“姓名”字段加索引，让数据按照姓名有规律的进行组织：

create index idx_name on student(name);

这时候MySQL又会建一颗新的B+树：

会发现这棵树的叶子节点，只有姓名和主键ID两个字段，没有行的完整数据，这时候执行：

select * from student where name = "David";

MySQL 到你刚刚创建的这棵 B+树 查询，快速查到有两条姓名是“David”的记录，并且拿到它们的主键，分别是 4 和 5，但是你要的是select *呀，怎么办？

别忘了，MySQL 在一开始就给你建了一棵 B+树 了，把这两棵树，放在一起，拿着从这棵树上查到的两个主键ID，去聚簇索引找，事情就解决了。

③复合索引
如果想根据 姓名 和 年龄 同时查询呢？

select * from student where name = "David" and age = 18;

还是那个道理，数据虽然按照 name 有规律的组织了，但是没有按照 age 有规律组织，所以我们要给 name 和 age 同时建索引：

create index idx_name_age on student(name,age);

这时候MySQL又会建一颗B+树，这下B+树的节点里面，不只有name，还有age。

注意观察我用红色虚线框出来的那两个节点，这是这棵树和上面那棵只给 name 建索引的树的唯一区别，两个元素换了个位，因为排序时，是先用 name 比较大小，如果 name 相同，则用 age 比较。

数据库调优

数据库调优一般情况下都是我们SQL调优，SQL调优可以解决大部分问题，也包括SQL执行环节的调优。

数据库调优是在执行器执行之前的分析器、优化器阶段完成的。

一般在开发涉及SQL业务，建议可以去本地环境跑一遍SQL，用explain去看一下执行计划，看看执行结果是否符合自己的预期，有没有用到相关的索引，再去线上环境跑一下看看执行时间。

1、排除缓存干扰
MYSQL8.0之后的版本，不支持缓存，就不用考虑缓存的问题。

在MYSQL8.0之前，数据库是存在缓存的，第一次查询时间会比较慢，后面会快很多，但是也会经常遇到缓存失效的情况，就会导致（Response time）时高时低。所以在执行SQL时，记得加上SQL NoCache去跑，这样跑出来的时间就是真实的查询时间了。

如果我们当前的MySQL版本支持缓存而且我们又开启了缓存，那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的，一个请求会先去看缓存是否存在，不存在才会走解析器。

缓存失效比较频繁的原因是，只要对表进行更新，那这个表所有的缓存都会被清空。

2、Explain
explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。

Expain作用：
1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询

执行计划包含的信息：

①id：查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序，

两种情况：id相同，执行顺序从上往下
id不同，id值越大，优先级越高，越先执行

②select_type：查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询。
simple ——简单的select查询，查询中不包含子查询或者UNION，
primary ——查询中若包含任何复杂的子部分，最外层查询被标记
subquery——在select或where列表中包含了子查询
derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
union result:UNION 的结果

③table：输出的行所引用的表type显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序：
system：表中仅有一行（=系统表）这是const联结类型的一个特例。
const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量
eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描
ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体
range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描
index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。
all：遍历全表以找到匹配的行
注意:一般保证查询至少达到range级别，最好能达到ref。

⑤possible_keys指出MySQL能使用哪个索引在该表中找到行

⑥key显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。

⑦key_len表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。

⑧ref显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值

⑨rows根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数

⑩Extra包含不适合在其他列中显示，但是十分重要的额外信息：

• Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”；
• Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
• Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。
• Using where :表明使用where过滤
• using join buffer:使用了连接缓存
• impossible where:where子句的值总是false，不能用来获取任何元组
• select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
• distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

SQL执行顺序

执行顺序如下：


extend关键字
extended关键字：仅对select语句有效，在explain后使用extended关键字，可以显示filtered列显示了通过条件过滤出的行数的百分比估计值。
也可以通过show warnings显示扩展信息，输出中的 Message值SHOW WARNINGS显示优化程序如何限定SELECT语句 中的表名和列名， SELECT应用重写和优化规则后的外观，以及可能有关优化过程的其他说明。

实操部分讲解
总行数10w行，用explain去分析SQL时，可能只会得到9.5w行，行数是个近似值。

因为MySQL中数据的单位都是页，MySQL又采用了采样统计的方法，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

所以数据一直在变，索引的统计信息也会变，会根据一个阈值，重新做统计。

至于MySQL索引可能走错也很好理解，如果走A索引要扫描100行，B所有只要20行，但是他可能选择走A索引。

一般走错都是因为优化器在选择的时候发现，走A索引没有额外的代价，比如走B索引并不能直接拿到我们的值，还需要回到主键索引才可以拿到，多了一次回表的过程，这个也是会被优化器考虑进去的。他发现走A索引不需要回表，没有额外的开销，所有他选错了。

如果是上面的统计信息错了，可以用analyze table tablename 就可以重新统计索引信息了，所以在实践中，如果发现explain的结果预估的rows值跟实际情况差距比较大，可以采用这个方法来处理。

还有一个方法就是force index强制走正确的索引，或者优化SQL，最后实在不行，可以新建索引，或者删掉错误的索引。

3、覆盖索引

如果在我们建立的索引上就已经有我们需要的字段，就不需要回表了，在电商里面也是很常见的，我们需要去商品表通过各种信息查询到商品id，id一般都是主键。

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

4、联合索引

如果需要根据商品的名称，去查商品的库存，假设这是一个很高频的查询请求，你会怎么建立索引呢？大家可以思考上面的回表的消耗对SQL进行优化。是的建立一个，名称和库存的联合索引，这样名称查出来就可以看到库存了，不需要查出id之后去回表再查询库存了，联合索引在我们开发过程中也是常见的，但是并不是可以一直建立的，大家要思考索引占据的空间。

5、最左匹配原则
顾名思义：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and a = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

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

如：
select * from itemcenter where name like ‘小%’ and size=20 and age = 23;
这个语句在搜索的时候，只能用只能用 “敖小”，找到第一个满足条件的记录ID，当然比全表扫描要好。然后再判断其他条件是否满足，比如size、age。

在MYSQL5.6之前，只能从ID开始一个个回表，到主键索引上找出数据行，再对比字段值。

7、唯一索引普通索引选择

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。

除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

那么，什么条件下可以使用change buffer呢？对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。change buffer用的是buffer pool里的内存，因此不能无限增大，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置，这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一，change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer的使用场景
因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价，所以，对于这种业务模式来说，change buffer反而起到了副作用。

8、前缀索引
即，可以定义字符串的一部分作为索引，如果创建索引的语句不能指定前缀长度的话，索引会包含这个字符串的。（应用于要创建的索引 字段名 很长的情况）。

那么如何建立一个区分度很高的前缀索引来达到优化和节约空间的目的呢？

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。上面说过覆盖索引了，覆盖索引是不需要回表的，但是前缀索引，即使你的联合索引已经包涵了相关信息，他还是会回表，因为他不确定你到底是不是一个完整的信息，就算你是一个完整的邮箱去查询，他还是不知道你是否是完整的，所以他需要回表去判断一下。

面试题：很长的字段，想做索引怎么优化？
可以把字段的hash做为另外一个字段存起来，每次校验hash就好了，hash的索引也不大。也可以采用倒序，或者删减字符串的情况去创建自己的区分度，但删减字段、可以substring()函数截取掉前面的子字符串，再建立索引，也可以使用reverse()函数。

9、条件字段函数操作
MySQL规定：字段做了函数计算，就用不上索引了，对索引字段进行函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
可以使用取巧的办法：
比如 select * from tradelog where id + 1 = 10000 就走不上索引，select * from tradelog where id = 9999就可以。

隐式类型转换
select * from t where id = 1如果id是字符类型的，1是数字类型的，你用explain会发现走了全表扫描，根本用不上索引，为啥呢？因为MySQL底层会对你的比较进行转换，相当于加了 CAST( id AS signed int) 这样的一个函数，上面说过函数会导致走不上索引。

10、隐式字符编码转换
如果两个表的字符集不一样，一个是utf8mb4，一个是utf8，因为utf8mb4是utf8的超集，所以一旦两个字符比较，就会转换为utf8mb4再比较。转换的过程相当于加了CONVERT(id USING utf8mb4)函数，那又回到上面的问题了，用到函数就用不上索引了。

11、flush
redo log大家都知道，也就是我们对数据库操作的日志，他是在内存中的，每次操作一旦写了redo log就会立马返回结果，但是这个redo log总会找个时间去更新到磁盘，这个操作就是flush。在更新之前，当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页“。

那什么时候会flush呢？
1、InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。
2、系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

这里其实是从性能考虑的，如果刷脏页一定会写盘，就保证了每个数据页有两种状态：一种是内存里存在，内存里就肯定是正确的结果，直接返回；另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样的效率最高。MySQL认为系统“空闲”的时候，只要有机会就刷一点“脏页”。MySQL正常关闭，这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

怎么做才会把握flush的时机呢？

Innodb刷脏页控制策略，我们每个电脑主机的io能力是不一样的，你要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力，这个值建议设置成磁盘的IOPS，磁盘的IOPS可以通过fio这个工具来测试。正确地设置innodb_io_capacity参数，可以有效的解决这个问题。这中间有个有意思的点，刷脏页的时候，旁边如果也是脏页，会一起刷掉的，并且如果周围还有脏页，这个连带责任制会一直蔓延，这种情况其实在机械硬盘时代比较好，一次IO就解决了所有问题。

但是现在都是固态硬盘了，innodb_flush_neighbors=0这个参数可以不产生连带制，在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了。

数据库三大范式

背景：数据库作为底层的存储系统，直接影响业务层的性能，因此，为了能够让开发人员科学规范地使用数据库，三大范式应运而生。
1、第一范式：每个列都不可以再拆分
2、第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能依赖于主键的一部分。
3、第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。
注：在设计数据库结构时，要尽量遵守三范式，如果不遵守，必须要有足够的理由，比如性能，事实上在实际的数据库结构设计中经常会为了性能而妥协数据库的设计。

系统解释：

• 第一范式：1NF
  第一范式是指关系表中的每列都是原子不可分的项，即每个属性都是最基本的数据项。

// 员工类
typedef Employee struct {
  Id    string  // 员工id
  Name  string  // 员工姓名
  Age   int     // 员工名字 
  Dept  Department  // 员工所属部门
}

// 部门类
typedef Department struct {
  Id      string  // 部门id
  Name    string  // 部门名字
  Detail  string  // 部门详情
}

如果我们在数据库中建立一张员工表emp(id, name, age, dept)，对于前三个属性都是基本类型，不可再分，而对于第四个属性dept，它在程序中其实对应于结构体，为复合属性，因此，按照第一范式正确的建表方式应该是将复合属性拆分为多个原子不可分的基本属性，即emp(id，name，age，dept_id，dept_name，dept_detail)。

• 第二范式：2NF
  第二范式是指在满足第一范式的情况下，关系表R中的所有非主属性都完全依赖于R的每一个候选关键属性。
  eg:假如有一个学生课程表student_course(学号, 姓名, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，因为只有这两个属性一起才能决定一条记录，即(学号, 课程名称) → (姓名, 成绩, 学分) ，这个关系表便不符合第二范式，因为"姓名"仅依赖于"学号"，“学分"仅依赖于"课程名”，因此，不满足第二范式条件。

那可以按照第二范式来改造上述关系表，将表拆分成student(学号，姓名)，cource(课程名称，学分)，student_cource(学号， 课程名称， 成绩)，这样便避免了上述问题。

• 第三范式：3NF
  第三范式是指，在满足第二范式的前提下，关系表R中的所有非主属性由主键直接决定，不存在间接依赖关系，简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

如表emp(id，name，age，dept_id，dept_name，dept_detail)，这张表中的员工id能够决定所有非主属性，但是我们发现dept_name，dept_detail这两个非主属性也可以由非主属性dept_id决定，而dept_id又依赖于id，因此存在间接传递依赖，不满足第三范式。同时，可以看出，不满足第三范式的问题是存在大量的冗余数据，解决该问题的方式很简单，只需将原关系表拆分为emp(id，name，age， dept_id)， dept(dept_id，dept_name, dept_detail)，这样不管是对于表emp还是表dept，各自的非主属性都直接依赖于主键，满足第三范式，同时也解决了数据冗余的问题。

参考链接：https://www.jianshu.com/p/3b91a7484ff1）

事务

① 事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

文章参考：
1、https://mp.weixin.qq.com/s/NDL1Q6nqdPq5oMBWSpq4ug
2、https://mp.weixin.qq.com/s?__biz=MzAwNDA2OTM1Ng==&mid=2453141549&idx=1&sn=19cc83341aea9a65b7bb639a3a994c7f&chksm=8cf2daaebb8553b80c3f1fe53d829f7f41e9f1be9498b190f4fab57106aba214dfd7836b83c6&scene=21#wechat_redirect