MySQL总结

校招面试必备，知识点注解很详细。非科班已入职华为。

MySQL知识树

SQL，全称Structured  Query  Language，结构化查询语言。 SQL是操作关系数据库的标准语言，是所有关系数据库通用的。 SQL只是制定的一个标准，由具体的关系数据库厂商来实现，所以某些关系数据库可能不支持SQL中的少数语句。 如果想让程序跨关系数据库，尽量使用SQL，不要使用某种关系数据库特定的功能/语句。 常见的关系型数据库有Oracle、MySQL， Oracle性能极好，但比较昂贵，大公司用得多； MySQL开源免费，体积小，性能也不错，中小公司用得多。

关系型数据库与非关系型数据库

表的结构

表是存储数据的逻辑单元。数据库中没有相同的名字的表。

记录

字段

主键

主键（primary key）一列（或一组列），其值能够唯一区分表中每个行。

外键

SQL基本语法

create

create databases 数据库名 charset=utf8;

创建数据库

create databases 数据库名 charset=utf8;

创建表

create table table_name( 字段名 数据类型 可选的约束条件,...）； demo: create table students( id int unsigned primary key auto_increment not null, name varchar(20) default '', age tinyint unsigned default 0, height decimal(5,2), gender enum('男','女','人妖','保密'), cls_id int unsigned default 0 )

增

add

添加字段

alter table 表名 add 列名 类型 demo：alter table students add birthday datetime;

insert

主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null) ; 字段默认值 default 来占位，插入成功后以实际数据为准。 1.全列插入：值的顺序与表结构字段的顺序完全一一对应，此时 字段名列表不用填写 insert into 表名 values (...) 例： insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2'); 2.部分列插入：值的顺序与给出的列顺序对应 此时需要根据实际的数据的特点 填写对应字段列表 insert into 表名 (列1,...) values(值1,...) 例： insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2'); 上面的语句一次可以向表中插入一行数据，还可以一次性插入多行数据，这样可以减少与数据库的通信 3.全列多行插入 insert into 表名 values(...),(...)...; 例： insert into classes values(0,'python1'),(0,'python2'); 4.部分列多行插入 insert into 表名(列1,...) values(值1,...),(值1,...)...; 例： insert into students(name) values('杨康'),('杨过'),('小龙女');

删

drop

删除数据库

drop database 数据库名

删除表

drop table 表名 drop table students;

删除字段

alter table 表名 drop 列名 later table students drop birthday;

delete

删除 delete from stu where id=1; 删除表stu中字段id=1的记录（删除表中制定的若干行）

改

alter

修改字段名

alert table 表名 change 原名 新名 类型及约束 demo：alter table syudents change birthday birth datetime not null;

update

update stu set age=20 where number =1; update stu set age=20 (若这样没有指定更新哪个，就作用在所有的字段上)

查

show

查看所有数据库

show databases;

查看所有表

show tables;

desc

查看表结构

desc 表名

select

查看当前使用的数据库

select database();

查看字段

select age,number from stu where name ='小丽'; --asc升序 desc降序 select *from stu (where ...)order by number desc

关键词

as

可以通过 as 给表起别名 select s.id,s.name,s.gender from students as s;

asc/desc

排序查询语法： select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...] select * from students where gender=1 and is_delete=0 order by id desc;

数据过滤

where

where后面支持多种运算符，进行条件的处理 比较运算符 逻辑运算符 模糊查询 范围查询 空判断 例1：查询编号大于3的学生 select * from students where id > 3; 例2：查询编号不大于4的学生 select * from students where id <= 4; 例3：查询姓名不是“黄蓉”的学生 select * from students where name != '黄蓉'; 例4：查询没被删除的学生 select * from students where is_delete=0; 查询编号大于3的女同学 select * from students where id > 3 and gender=0; 例6：查询编号小于4或没被删除的学生 select * from students where id < 4 or is_delete=0;

is

判断为空
例13：查询没有填写身高的学生 select * from students where height is null;

like

模糊查询 like %表示任意多个任意字符 _表示一个任意字符 例7：查询姓黄的学生 select * from students where name like '黄%'; 例8：查询姓黄并且“名”是一个字的学生 select * from students where name like '黄_';

in

in表示在一个非连续的范围内
例10：查询编号是1或3或8的学生 select * from students where id in(1,3,8);

between and

between … and …表示在一个连续的范围内
例11：查询编号为3至8的学生 select * from students where id between 3 and 8;

limit

select * from Customer LIMIT 10;--检索前10行数据，显示1-10条数据 select * from Customer LIMIT 1,10;--检索从第2行开始，累加10条id记录，共显示id为2....11 select * from Customer limit 5,10;--检索从第6行开始向前加10条数据，共显示id为6,7....15 select * from Customer limit 6,10;--检索从第7行开始向前加10条记录，显示id为7,8...16

分组

对数据进行分类汇总。

group by

group_concat(字段名)

having

视图

这里的视图可以理解为数据副本，每次创建视图时，将当前已持久化的数据创建副本，后续直接从副本读取，从而达到数据隔离效果。 视图是一个虚拟表，是sql的查询结果，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成。视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ] ; 创建视图需要create view 权限，并且对于查询涉及的列有select权限；使用create or replace 或者 alter修改视图，那么还需要改视图的drop权限。

创建

create view  视图名  as  select 字段名 from 表名;

修改

alter view 视图名 as select 语句 alter view 视图名 as  select 视图; Mysql视图的定义在from关键字后面不能包含子查询         修改了视图，对基表数据有影响         修改了基表，对视图也有影响

显示视图

show create view 视图名;

删除视图

drop view 视图名[,视图名…];

重命名

Rename table 视图名 to 新视图名;

作用

1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。 2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。 3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。 总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

连接表

MySQL 的内连接、左连接、右连接有什么区别？ 内连接关键字：inner join； 左连接：left join； 右连接：right join。 内连接是把匹配的关联数据显示出来； 左连接是左边的表全部显示出来，右边的表显示出符合条件的数 据；右连接正好相反。

自连接

外部连接

左外连接

关键字：left join on / left outer join on 语句：SELECT  * FROM a_table a left join b_table b ON a.a_id = b.b_id; 左表(a_table)的记录将会全部显示出来，而右表(b_table)只会显示符合搜索条件的记录，右表记录不足的地方均为NULL。

SELECT boy.hid,boy.bname,girl.gname FROM boy LEFT JOIN girl ON girl.hid = boy.hid; 说明： left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

右外连接（与做外链接相反）

内连接

select e.last_name,e.first_name,d.dept_no from dept_emp as d --答案是按employees表中顺序输出的，所以使用内连接查询时，必须将employees表放在前面。 inner(加不加都可以) join employees as e on e.emp_no = d.emp_no;

组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

左表独有

右表独有

并集去交集

全连接

API函数

count()

count(*) 表示计算总行数，括号中写星与列名，结果是相同的
例1：查询学生总数 select count(*) from students;

max(列)

max(列) 表示求此列的最大值 例2：查询女生的编号最大值 select max(id) from students where gender=2;

min(列)

min(列) 表示求此列的最小值 例3：查询未删除的学生最小编号 select min(id) from students where is_delete=0;

sum(列)

sum(列) 表示求此列的和 例4：查询男生的总年龄 select sum(age) from students where gender=1;

avg(列)

avg(列) 表示求此列的平均值 例5：查询未删除女生的编号平均值 select avg(id) from students where is_delete=0 and gender=2;

事务

数据库的事务是指一组sql语句组成的数据库逻辑处理单元，在这组的sql操作中，要么全部执行成功，要么全部执行失败。  这里的一组sql操作，举个简单又经典的例子就是转账了，事务A中要进行转账，那么转出的账号要扣钱，转入的账号要加钱，这两个操作都必须同时执行成功，为了确保数据的一致性。

四大特性(ACID)

1. 原子性

1. 原子性是指事务的原子性操作，对数据的修改要么全部执行成功，要么全部失败，实现事务的原子性，是基于日志的Redo/Undo机制。 但是，原子性并不能完全保证一致性。在多个事务并行进行的情况下，即使保证了每一个事务的原子性，仍然可能导致数据不一致的结果。 例如，事务1需要将100元转入帐号A：先读取帐号A的值，然后在这个值上加上100。但是，在这两个操作之间，另一个事务2修改了帐号A的值，为它增加了100元。那么最后的结果应该是A增加了200元。但事实上，事务1最终完成后，帐号A只增加了100元，因为事务2的修改结果被事务1覆盖掉了。

undo log

回滚日志，事务的原子性是通过 undo log 来实现的 根据上面流程可以得出如下结论： 1.每条数据变更(insert/update/delete)操作都伴随一条undo log的生成,并且回滚日志必须先于数据持久化到磁盘上 2.所谓的回滚就是根据回滚日志做逆向操作，比如delete的逆向操作为insert，insert的逆向操作为delete，update的逆向为update等。 回滚操作就是要还原到原来的状态，undo log记录了数据被修改前的信息以及新增和被删除的数据信息，根据undo log生成回滚语句，比如： (1) 如果在回滚日志里有新增数据记录，则生成删除该条的语句 (2) 如果在回滚日志里有删除数据记录，则生成生成该条的语句 (3) 如果在回滚日志里有修改数据记录，则生成修改到原先数据的语句

2. 一致性

2. 事务的一致性指的是一个事务执行前后都必须是一致性状态。 举个栗子：小明和小红之间总共有520元钱，无论他们彼此转账多少次，各自持有多少元钱，最终还是520元钱。 为了保证并发情况下的一致性，引入了隔离性，即保证每一个事务能够看到的数据总是一致的，就好象其它并发事务并不存在一样。

一致性是通过原子性，持久性，隔离性来实现的！

3. 隔离性

3. 隔离性指的是多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务所干扰，多个并发事务之间要相互隔离。 即达到这么一个效果->并发事务S1和S2，在事务S1看来，事务S2要么在事务S1执行之前就已经结束了，要么在事务S1执行结束之后才开始；S2亦同理。这样一来每个事务都感觉不到有其他事务并发执行。这里涉及到事务的隔离级别，后面再说。

隔离级别

在MySQL的众多存储引擎中，只有InnoDB支持事务，所有这里说的事务隔离级别指的是InnoDB下的事务隔离级别。

1. 读未提交

1. 读未提交，顾名思义，事务之间可以读取彼此未提交的数据。读不会加任何锁，而写会加排他锁。 机智如你会记得，在前文有说到所有写操作都会加排它锁，那还怎么读未提交呢？ 前面我们介绍排它锁的时候，有这种说明： 排他锁会阻止其它事务再对其锁定的数据加读或写的锁，但是对不加锁的读就不起作用了。

脏读

事务A读取了事务B未提交的数据，然后B回滚操作，那么A读取到的数据是脏数据。

2. 读已提交

2. 读提交，顾名思义，事务之间可以读取彼此已提交的数据。 InnoDB在该隔离级别(READ COMMITTED)写数据时，使用排它锁, 读取数据不加锁而是使用了MVCC机制，每次 select的时候新生成一个版本号，所以每次select的时候读的是不同的版本。如果在二次select之间有其他事务更新了我们读取的数据并提交了，那就出现了不可重复读。

不可重复读

如果我们在事务A中执行多次的select，在每次select之间有其他事务更新了我们读取的数据并提交了，那就出现了不可重复读，即：重复读时，会出现数据不一致问题。读不加锁造成的。 事务A 事务B 开始事务 开始事务 读取X=1 读取X=1 更新X=2 提交,结束事务 读取X=2 结束事务

3. 可重复读（mysql默认隔离级别）

3. 当隔离级别设置为Repeatable read 时，可以避免不可重复读。 与READ COMMITTED级别不同的是MVCC版本的生成时机，即：一次事务中只在第一次select时生成版本，后续的查询都是在这个版本上进行，从而实现了可重复读。 但如果这个事务在读取某个范围内的记录时，其他事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，这就是幻读。

MVCC实现

为什么能可重复读？ 因为多次读取只生成一个版本，读到的自然是相同数据。 优点：读写并行 缺点：实现的复杂度高 但是在该隔离级别下仍会存在幻读的问题。

读写锁实现

在一个事务内的多次读取的结果是一样的。这种级别下可以避免，脏读，不可重复读等查询问题。 为什么能可重复度？只要没释放读锁，再次读的时候还是可以读到第一次读的数据。
 优点：实现起来简单 缺点：无法做到读写并行

幻读

幻读是指select时不存在某记录,但准备插入时发现此记录已存在,无法插入,这就产生了幻读. 事务A 事务B 开始事务 开始事务 select某个数据为空,准备插入一个新数据 插入一个新数据 提交,结束事务 插入数据,发现插入失败,由于事务B已插入相同数据 结束事务 其实不可重复读和幻读很相似，只不过不可重复读针对的是对数据的update操作，而幻读针对的是对数据的insert操作。 前者是数据条数不变，但是数据本身变化了；后者是数据条数变化了，但是数据本身不变。 幻读和不可重复读都是读取了另一条已经提交的事务（这点跟脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

4. 序列化

4. 序列化 Serializable 是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读。 读-写，写-写均互斥。

实现机制

事务的机制是通过视图（read-view）来实现的并发版本控制（MVCC），不同的事务隔离级别创建读视图的时间点不同。 可重复读是每个事务重建读视图，整个事务存在期间都用这个视图。 读已提交是每条 SQL 创建读视图，在每个 SQL 语句开始执行的时候创建的。隔离作用域仅限该条 SQL 语句。 读未提交是不创建，直接返回记录上的最新值 串行化隔离级别下直接用加锁的方式来避免并行访问。 这里的视图可以理解为数据副本，每次创建视图时，将当前已持久化的数据创建副本，后续直接从副本读取，从而达到数据隔离效果。 我们每一次的修改操作，并不是直接对行数据进行操作。 比如我们设置 id 为 3 的行的 A 属性为 10，并不是直接修改表中的数据，而是新加一行。 同时数据表其实还有一些隐藏的属性，比如每一行的事务 id，所以每一行数据可能会有多个版本，每一个修改过它的事务都会有一行，并且还会有关联的 undo 日志，表示这个操作原来的数据是什么，可以用它做回滚。 那么为什么要这么做？ 因为如果我们直接把数据修改了，那么其他事务就用不了原先的值了，违反了事务的一致性。 那么一个事务读取某一行的数据到底返回什么结果呢？ 取决于隔离级别，如果是 Read Committed，那么返回的是最新的事务的提交值，所以未提交的事务修改的值是不会读到的，这就是 Read Committed 实现的原理。 如果是 Read Repeatable 级别，那么只能返回发起时间比当前事务早的事务的提交值，和比当前事务晚的删除事务删除的值。这其实就是 MVCC 方式。 undo log undo log 中存储的是老版本数据。假设修改表中 id=2 的行数据，把 Name='B' 修改为 Name = 'B2' ，那么 undo 日志就会用来存放 Name='B' 的记录，如果这个修改出现异常，可以使用 undo 日志来实现回滚操作，保证事务的一致性。 当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着 undo 链找到满足其可见性的记录。当版本链很长时，通常可以认为这是个比较耗时的操作。 假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。 当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。 如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。 同时你会发现，即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。 另外，在回滚段中的 undo log 分为: insert undo log 和 update undo log： insert undo log : 事务对 insert 新记录时产生的 undolog，只在事务回滚时需要，并且在事务提交后就可以立即丢弃。（谁会对刚插入的数据有可见性需求呢！！） update undo log : 事务对记录进行 delete 和 update 操作时产生的 undo log。不仅在事务回滚时需要，一致性读也需要，所以不能随便删除，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被 purge 线程删除。 何时删除？ 在不需要的时候才删除。也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。 就是当系统里没有比这个回滚日志更早的 read-view 的时候。

MVCC(多版本并发控制)

多版本并发控制(Multiversion Concurrency Control)，顾名思义，在并发访问的时候，数据存在版本的概念，可以有效地提升数据库并发能力。 InnoDB采用的MVCC实现方式是：在需要时，通过undo日志构造出历史版本。InnoDB的 MVCC ，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列， 一个保存了行的创建时间，一个保存了行的过期时间， 当然存储的并不是实际的时间值，而是系统版本号。他的主要实现思想是通过数据多版本来做到读写分离，从而实现不加锁读进而做到读写并行。 简单讲，如果没有MVCC，当想要读取的数据被其他事务用排它锁锁住时，只能互斥等待；而这时MVCC可以通过提供历史版本从而实现读取被锁的数据的历史版本，从而避免了互斥等待。 多版本控制（Multiversion Concurrency Control）: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，InnoDB通过undo log保存每条数据的多个版本，并且能够找回数据历史版本提供给用户读，每个事务读到的数据版本可能是不一样的。在同一个事务中，用户只能看到该事务创建快照之前已经提交的修改和该事务本身做的修改。         MVCC在 Read Committed 和 Repeatable Read两个隔离级别下工作。         MySQL的InnoDB存储引擎默认事务隔离级别是RR(可重复读)，是通过 "行级锁+MVCC"一起实现的，正常读的时候不加锁，写的时候加锁。而 MVCC 的实现依赖：隐藏字段、Read View、Undo log。

read view

用来判断当前版本数据的可见性 其实Read View（读视图），跟快照、snapshot是一个概念。        Read View主要是用来做可见性判断的, 里面保存了“对本事务不可见的其他活跃事务”。

undo log

undo log 中记录某行数据的多个版本的数据。 什么是 undo log ？ undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。 每次写入数据或者修改数据之前都会把修改前的信息记录到 undo log。 undo log 有什么作用？ undo log 记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态。 总结：
undo log是用来回滚数据的用于保障 未提交事务的原子性

锁

什么时候会加锁？

在数据库增删改查四种操作中，insert、delete和update都是会加排它锁(Exclusive Locks)的，而select只有显式声明才会加锁: select: 即最常用的查询，是不加任何锁的 select ... lock in share mode: 会加共享锁(Shared Locks) select ... for update: 会加排它锁

读写锁

通过读写锁，可以做到读读可以并行，但是不能做到写读，写写并行 事务的隔离性就是根据读写锁来实现的。

Shared Locks(共享锁/S锁)

也叫读锁 若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

Exclusive Locks(排他锁/X锁)

也叫写锁 若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。 它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。 注意：排他锁会阻止其它事务再对其锁定的数据加读或写的锁，但是不加锁的就没办法控制了。

Record Locks(行锁)

行锁，顾名思义，是加在索引行(对！是索引行！不是数据行！)上的锁。 比如select * from user where id=1 and id=10 for update，就会在id=1和id=10的索引行上加Record Lock。

Gap Locks(间隙锁)

间隙锁，它会锁住两个索引之间的区域。 比如select * from user where id>1 and id<10 for update，就会在id为(1,10)的索引区间上加Gap Lock。

Next-Key Locks(间隙锁)

也叫间隙锁，它是Record Lock + Gap Lock形成的一个闭区间锁。 比如select * from user where id>=1 and id<=10 for update，就会在id为[1,10]的索引闭区间上加Next-Key Lock。

4. 持久性

4. 持久性指的是事务一旦提交，他对数据库中数据的改变是永久性的，接下来的其他操作或故障不应对其执行结果产生任何影响。 先了解一下MySQL的数据存储机制，MySQL的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘IO,非常消耗性能。为了提升性能，InnoDB提供了缓冲池(Buffer Pool)，Buffer Pool中包含了磁盘数据页的映射，可以当做缓存来使用：
读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池；
写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中； 上面这种缓冲池的措施虽然在性能方面带来了质的飞跃，但是它也带来了新的问题，当MySQL系统宕机，断电的时候可能会丢数据！！！因为我们的数据已经提交了，但此时是在缓冲池里头，还没来得及在磁盘持久化，所以我们急需一种机制需要存一下已提交事务的数据，为恢复数据使用，于是 redo log就派上用场了。

redo log

什么是redo log ? redo log叫做重做日志，是用来实现事务的持久性。 该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中。redo log本身也由两部分所构成即重做日志缓冲(redo log buffer)和重做日志文件(redo log file)。这样的设计同样也是为了调和内存与磁盘的速度差异。 redo log 有什么作用？ mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。 那么问题来了，如果还没来的同步的时候宕机或断电了怎么办？还没来得及执行上面图中红色的操作。这样会导致丢部分已提交事务的修改信息！ 所以引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。 总结：
redo log是用来恢复数据的 用于保障，已提交事务的持久化特性。

重做日志缓冲

在内存保存

重做日志文件

在磁盘保存

流程

p start transaction; select balance from bank where name="zhangsan"; update bank set balance = balance - 400; // 生成 重做日志 balance=600 update finance set amount = amount + 400;// 生成 重做日志 amount=400 commit;

既然redo log也需要存储，也涉及磁盘IO为啥还用它？

既然redo log也需要存储，也涉及磁盘IO为啥还用它？ （1）redo log 的存储是顺序存储，而缓存同步是随机操作。随机读写的时候磁头要多次寻道，消耗很长时间。 （2）缓存同步是以数据页为单位的，每次传输的数据大小大于redo log。

长事务

一个事务花费很长时间不能够结束，简称长事务（Long Transaction）。 长事务是数据库用户经常会碰到且是非常令人头疼的问题。长事务处理需要恰当进行，如处理不当可能引起数据库的崩溃，为用户带来不必要的损失。 根据上面的论述，长事务意味着系统里面会存在很老的事务视图。 由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的 undo log 都必须保留，这就会导致大量占用存储空间。 在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。 除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库，这个我们会在后面讲锁的时候展开。 因此，我们要尽量避免长事务。

索引

索引是。总的一句话概括就是索引是一种数据结构。

索引介绍

索引是一种数据结构，用来提高mysql的查询效率。 例如B-Tree，这种数据结构是需要额外的写入和存储为代价来提高表上数据检索的速度。一旦建立了索引后，数据库中查询优化器使用索引来快速定位数据，然后就无需扫描表中给定查询的每一行了。 索引就好比给数据库添加了一个目录，方便我们查询数据。索引本身很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

优点

由于索引底层实现的有序性，使得在进行数据查询时，能够避免在磁盘不同扇区的随机寻址。 使用索引后能够通过磁盘预读使得在磁盘上对数据的访问大致呈顺序的寻址。这本质上是依据局部性原理所实现的。 所以呢，总结一下。索引的存在具有很大的优势，主要表现为以下三点： 索引大大减少了服务器需要扫描的数据量 索引可以帮助服务器避免排序和临时表 索引可以将随机 I/O 变成顺序 I/O

缺点

索引本身以表的形式存储，因此会占用额外的存储空间； 索引表的创建和维护需要时间成本，这个成本随着数据量增大而增大； 构建索引会降低数据的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表； 所以对于非常小的表而言，使用索引的代价会大于直接进行全表扫描，这时候就并不一定非得使用索引了。

数据结构

B树

多路搜索树 为啥不用数组？ 数组是内存中一块连续的内存空间，定义一个数组对象，这个对象的指针指向了这块内存的起始地址，如果知道数组元素的下标，那么就可以计算出该下标所对应的元素的内存地址了，因此可以在 O(1)的时间复杂度内获取到元素，非常快速。 对于一个有序数组，它的查找过程可以使用二分查找法进行查找，时间复杂度为 O(logn)，效率也非常高。因为是有序数组，因此对范围查找也十分友好，只需要找到起始元素即可。如果在知道元素下标的情况下，更新操作也非常快，对于删除操作，如果我们不考虑空洞的话（如果直接将对应下标处的元素置为 null，这样这块连续内存块中相当于有个空洞），删除操作也很快。 这么一分析，数组对查询、删除、更新操作的效率非常高，选数组作为 MySQL 索引的数据结构看起来似乎不错。然而我们忽略了还有插入操作，也就是涉及到了数组的复制操作，这个不仅需要额外开辟内存，复制数据消耗的时间也很长。 因此，从插入数据这一角度来看，数组不太适合作为 MySQL 索引的数据结构。 一、Mysql存储结构为什么不能是hash? 1、不能完成范围查找。Hash(100)-hash(500)与user_id(100,500)不能一一对应，例如Hash(100)=hash(500)发生哈希冲突，则user_id(100,500)对应hash表的搜索区域为null; 2、不能进行联合索引的查找，例如hash(user_id+name)!=hash(user_id) 二、Mysql存储结构为什么不能是红黑树? 我们首先提一个问题， B树比红黑树在索引数据方面要快么？ 大多数人可能认为肯定还是B树快，毕竟存储同样多的数据，100阶的B树肯定比红黑树的高度要低的多。但是别忘了B树在一个结点可能需要比较很多次才能找到下一层的结点，但是红黑树只要比较一次就可以向下走一层。所以综合起来，其实两者索引的速度几乎（甚至说就是）是一样的。那么我们为什么还要使用B树呢？这是因为上面说索引速度相当的前提是两者的数据结构都位于内存中，当我们要在磁盘上索引一个记录时，将磁盘中的数据传输到内存中才是花费时间的大头，而在内存中的索引过程所花的时间基本是可以忽略不计的。在磁盘中以B树的形式组织数据就有着天然的优势。 因此我们现在选择16KB作为内存和磁盘之间的传输单位，那么我们在设计B树的时候，不论是索引结点还是叶子结点都使用16KB作为结点的大小，减少IO操作。

缺点

一是无法定位到数据行。通过 BTree 可以根据主键的排序定位出主键的位置，但是由于数据表的记录有多个字段，仅仅定位到主键是不够，还需要定位到数据行。虽然这个问题可以通过在 BTree 的节点中存储数据行或者增加定位的字段，但是这种方式会使得 BTree 的深度大幅度提高，从而也导致 I/O 次数的提高。 二是无法处理范围查询。在实际的应用中，数据库范围查询的频率非常高，而 BTree 只能定位到一个索引位置。虽然可以通过先后查询范围的左右界获得，但是这样的操作实际上无法很好的利用磁盘预读的局部性原理，先后查询可能会造成通过预读取的物理地址离散，使得 I/O 的效率并不高。 三是当数据量一大时，BTree的高度依旧会变得很高，搜索效率还是会大幅度的下降。

B+树

B+树是在B树基础上改进而来，B+树非叶子节点只存储键值信息，它的数据都存放在叶子节点，并以循环链表的结构存储。 方便范围查找 这也是和业务场景相关的，你想想，数据库中 Select 数据，不一定只选一条，很多时候会选多条，比如按照 ID 排序后选 10 条。用B 树的话需要做局部的中序遍历，可能要跨层访问,而 B+ 树由于所有数据都在叶子结点，不用跨层，同时由于有链表结构，只需要找到首尾，通过链表就能把所有数据取出来了。 看着 B+Tree，像不像是一颗树与一个有序链表的结合体。因而其实 B+Tree 也就是带有树和链表的部分优势。树结构使得有序检索更为简单，I/O 次数更少；有序链表结构使得可以按照键值排序的次序遍历全部记录。

索引树结构

每个索引一个B+树， 一个B+树节点 = 一个物理Page（16K），一个节点一次IO操作。 • 数据按16KB切片为Page 并编号， 编号可映射到物理文件偏移(16K * N）， B+树叶子节点前后形成双向链表， 数据按主键索引聚簇， 二级索引叶节点存储主键值， 通过叶节点主键值回表查找数据。

select * from user_info where id = 23

索引精确查找: 确定定位条件, 找到根节点Page No, 根节点读到内存, 逐层向下查找, 读取叶子节点Page,通过二分查找找到记录或未命中。

select * from user_info where id >= 18 and id < 22

索引范围查找：读取根节点至内存, 确定索引定位条件id=18, 找到满足条件第一个叶节点, 顺序扫描所有结果, 直到终止条件满足id >=22 。

select * from user_info where name = 'abc'

全表扫描：直接读取叶节点头结点， 顺序扫描， 返回符合条件记录， 到最终节点结束。

存储引擎

虽说 B+Tree 结构都可以用在 MyISAM 和 InnoDB，但是这二者对索引的在物理存储层次的实现方式却不相同。InnoDB 实现的是聚簇索引，而 MyISAM 实现的却是非聚簇索引。 树的非叶子节点只存储键值。没错就是这个键值，当这个键值是数据表的主键时，那么所建立的就是主键索引； 当这个键值是其它字段的时候，就是辅助索引。因而可以得出，主键索引只能有一个，而辅助索引却可以有很多个。 聚簇索引和非聚簇索引的区别也就是根据其对应的主键索引和辅助索引的不同特点而实现的。

聚集索引

数据与索引合并到一个文件中，因此查找效率比非聚簇索引要高

InnoDB(默认)

发音：in no dibi Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。 该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。 不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。 当需要使用数据库事务时，该引擎当然是首选。 由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。


索引结构

虽然 InnoDB 也使用 B+Tree 作为索引结构,但具体实现方式却与 MyISAM 截然不同。 1.第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道,MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。 而在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。 聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。 在innodb到的引擎中，默认每个页的大小是16kb，由系统设定，与其他无关； b+tree的叶子节点存储的是行数据，其他节点存储的是主索引（针对primary key来说）；如果是非叶子节点；假设primary key 是int型，那么每个非叶子节点大小是4byte；一个页可以存储 16kb/4byte = 4k个索引（估算值，每个节点存在其他数据）。但是，如果是叶子节点，假设每行数据1kb（业内默认估算值），则每页只能存8条（16k/2/1kb）数据。推荐你看一下《从根上理解mysql》 引申:为什么不建议使用过长的字段作为主键?  因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。

主索引

1 .InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。 在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示： p 这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

辅助索引

第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。 换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。

磁盘文件

主键为啥推荐自增主键

不自增的话，b+树为了维护叶子链表的递增，会进行分裂再平衡，导致性能开销。

非聚集索引

索引与数据分离为两个文件存储

MyISAM

发音：my is am MyISAM没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。 不过和Innodb不同，MyISAM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。 如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyISAM也是很好的选择。


索引结构

MyISAM 引擎使用 B+Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址。 因此,MyISAM 中索引检索的算法为：首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。 MyISAM 的索引方式也叫做“非聚集索引”,之所以这么称呼是为了与 InnoDB的聚集索引区分。

主索引

我们以 Col1 为主键：

辅助索引

主索引和辅助索引在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。 我们在 Col2 上建立一个辅助索引,则此索引的结构如下图所示：

磁盘文件

区别

1. InnoDB 支持事务，MyISAM 不支持事务。 2. InnoDB 支持外键，而 MyISAM 不支持。 3. InnoDB 是聚集索引，MyISAM 是非聚集索引。 聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。 4. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一； 如何选择： 1. 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM； 2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用InnoDB。 3. 系统奔溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB； ❝
一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？ 如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失； 如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。 ❝
哪个存储引擎执行 select count(*) 更快，为什么? MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。 在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。 在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

MySQL数据IO机制

将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB，也就是说，当需要从磁盘中读数据时每一次最少将从磁盘中读取16KB的内容到内存中，每一次最少也会把内存中的16KB内容写到磁盘中。

磁盘

缓冲池

数据存取的时候都要经历磁盘IO,非常消耗性能。为了提升性能，InnoDB提供了缓冲池(Buffer Pool)，Buffer Pool中包含了磁盘数据页（磁盘块）的映射，可以当做缓存来使用：
读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池；
写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中；

内存

数据页

磁盘中的一页数据(4kb)和数据库innodb规定的一页数据(16kb),这两个的概念是不一样的。 磁盘io的大小也是根据指令来规定的。对应数据库读写来说，会按照数据库的配置，每次最少读写一页数据，也就是16kb。

调优

为搜索字段创建索引。 避免使用 select *，列出需要查询的字段。 垂直分割分表。 选择正确的存储引擎。

mysql百万级数据分页查询的优化

索引优化

1 独立的列。索引列不能是表达式的一部分，也不能是函数的参数。 2 前缀索引和索引选择性。这二者实际上是相互制约的关系，制约条件在于前缀的长度。一般应选择足够长的前缀以保证较高的选择性（保证查询效率），同时又不能太长以便节省空间。 3 尽量使用覆盖索引。覆盖索引是指一个索引包含所有需要查询的字段的值，这样在查询时只需要扫描索引而无须再去读取数据行，从而极大的提高性能。 4 使用索引扫描来做排序。要知道，扫描索引本身是很快的，在设计索引时，可尽可能的使用同一个索引既满足排序，又满足查找行数据。 最后，在建立索引时给几个小贴士： 1 优先使用自增key作为主键 2 记住最左前缀匹配原则 3 索引列不能参与计算 4 选择区分度高的列作索引 5 能扩展就不要新建索引

锁

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

行锁

行级锁：开销大，加锁慢，会出现死锁。 锁粒度小，发生锁冲突的概率小，并发度最高。

表锁

表级锁：开销小，加锁快，不会出现死锁。 锁定粒度大，发生锁冲突的概率最高，并发量最低。