TheWe1MySQL
1、数据操作
DQL
select
[DISTINCT]
字段
from
表1 as 别名1
[left|right|inner join 表2 on xx=xx]
where
条件
group by
分组字段
having
分组条件
order by
字段 asc|desc
limit
(currentPage-1)*pageSzie,pageSzieselect
[DISTINCT]
字段
from
表1 as 别名1
[left|right|inner join 表2 on xx=xx]
where
条件
group by
分组字段
having
分组条件
order by
字段 asc|desc
limit
(currentPage-1)*pageSzie,pageSzie... limit beginIndex,pageSize
beginIndex:表示从第多少条数据开始 pageSize:表示每页显示的数据条数
beginIndex=(当前页数-1)*pageSize
DDL
-- 创建数据库
CREATE DATABASE if not exists 数据库名 default character set ='utf8';-- 创建数据库
CREATE DATABASE if not exists 数据库名 default character set ='utf8';-- 查看建表语句
show create table + 表名
-- 查看表详情
desc + 表名-- 查看建表语句
show create table + 表名
-- 查看表详情
desc + 表名索引操作
-- 创建索引
CREATE INDEX 索引名称 ON 表名 (列名)
-- 删除索引
DROP INDEX 索引名 ON 表名
-- 修改索引
ALTER TABLE 表名 DROP INDEX 索引名-- 创建索引
CREATE INDEX 索引名称 ON 表名 (列名)
-- 删除索引
DROP INDEX 索引名 ON 表名
-- 修改索引
ALTER TABLE 表名 DROP INDEX 索引名普通索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )ALTER TABLE `table_name` ADD INDEX index_name ( `column` )ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )唯一索引
ALTER TABLE `table_name` ADD UNIQUE ( `column` )ALTER TABLE `table_name` ADD UNIQUE ( `column` )全文索引
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)ALTER TABLE `table_name` ADD FULLTEXT ( `column`)DML
-- 插入单条数据
insert into 表名(字段1,字段2..) values(值1,值2..);
-- 插入多条数据
insert into 表名(字段1,字段2) values(值1,值2),(值1,值2);
-- 针对全表所有字段进行插入操作
insert into 表名 values(值1,值2);
-- 查询结果插入
insert into 表名(字段) select 字段 from 表2;
-- 查询结果，全表插入
insert into 表名 select 字段 from 表2;
-- 带条件修改指定数据，否则修改全表
update 表 set 字段=值 where 条件;
-- 删除数据带条件指定数据，否则删除全表数据
delete from 表 where 条件;
-- 完全清空表数据
TRUNCATE TABLE 表名;-- 插入单条数据
insert into 表名(字段1,字段2..) values(值1,值2..);
-- 插入多条数据
insert into 表名(字段1,字段2) values(值1,值2),(值1,值2);
-- 针对全表所有字段进行插入操作
insert into 表名 values(值1,值2);
-- 查询结果插入
insert into 表名(字段) select 字段 from 表2;
-- 查询结果，全表插入
insert into 表名 select 字段 from 表2;
-- 带条件修改指定数据，否则修改全表
update 表 set 字段=值 where 条件;
-- 删除数据带条件指定数据，否则删除全表数据
delete from 表 where 条件;
-- 完全清空表数据
TRUNCATE TABLE 表名;函数操作
聚合函数
max -- 最大值
min -- 最小值
sum -- 求和
avg -- 平均值
count -- 记录个数(若统计的是列，列中为Null，那么count将不会计算值)
ABS -- 绝对值
CEILING() FLOOR() -- 向上取整 向下取整
RAND() -- 返回0~1之间的随机数
SIGN() -- 判断一个数的符号位。负数返回-1，正数返回1max -- 最大值
min -- 最小值
sum -- 求和
avg -- 平均值
count -- 记录个数(若统计的是列，列中为Null，那么count将不会计算值)
ABS -- 绝对值
CEILING() FLOOR() -- 向上取整 向下取整
RAND() -- 返回0~1之间的随机数
SIGN() -- 判断一个数的符号位。负数返回-1，正数返回1数据函数
abs(x) -- 绝对值 abs(-10.9) = 10
format(x, d) -- 格式化千分位数值 format(1234567.456, 2) =1,234,567.46
ceil(x) -- 向上取整 ceil(10.1) = 11
floor(x) -- 向下取整 floor (10.1) = 10
round(x) -- 四舍五入去整
mod(m, n) -- m%n m mod n 求余 10%3=1
pi() -- 获得圆周率
pow(m, n) -- m^n
sqrt(x) -- 算术平方根
rand() -- 随机数
truncate(x, d) -- 截取d位小数abs(x) -- 绝对值 abs(-10.9) = 10
format(x, d) -- 格式化千分位数值 format(1234567.456, 2) =1,234,567.46
ceil(x) -- 向上取整 ceil(10.1) = 11
floor(x) -- 向下取整 floor (10.1) = 10
round(x) -- 四舍五入去整
mod(m, n) -- m%n m mod n 求余 10%3=1
pi() -- 获得圆周率
pow(m, n) -- m^n
sqrt(x) -- 算术平方根
rand() -- 随机数
truncate(x, d) -- 截取d位小数字符串函数
length(string) -- string长度，字节
char_length(string) -- string的字符个数
substring(str, position [,length]) -- 从str的position开始,取length个字符
replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str
instr(string ,substring) -- 返回substring首次在string中出现的位置
concat(string [,...]) -- 连接字串
charset(str) -- 返回字串字符集
lcase(string) -- 转换成小写
left(string, length) -- 从string2中的左边起取length个字符
load_file(file_name) -- 从文件读取内容
locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length
ltrim(string) -- 去除前面空格
repeat(string, count) -- 重复count次
rpad(string, length, pad) -- 在str后用pad补充,直到长度为length
rtrim(string) -- 去除后端空格
strcmp(string1 ,string2) -- 逐字符比较两字串大小length(string) -- string长度，字节
char_length(string) -- string的字符个数
substring(str, position [,length]) -- 从str的position开始,取length个字符
replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str
instr(string ,substring) -- 返回substring首次在string中出现的位置
concat(string [,...]) -- 连接字串
charset(str) -- 返回字串字符集
lcase(string) -- 转换成小写
left(string, length) -- 从string2中的左边起取length个字符
load_file(file_name) -- 从文件读取内容
locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length
ltrim(string) -- 去除前面空格
repeat(string, count) -- 重复count次
rpad(string, length, pad) -- 在str后用pad补充,直到长度为length
rtrim(string) -- 去除后端空格
strcmp(string1 ,string2) -- 逐字符比较两字串大小时间函数
SELECT CURRENT_DATE(); /*获取当前日期*/
SELECT CURDATE(); /*获取当前日期*/
SELECT NOW(); /*获取当前日期和时间*/
SELECT LOCALTIME(); /*获取当前日期和时间*/
SELECT SYSDATE(); /*获取当前日期和时间*/
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
-- 时间日期函数
now(), current_timestamp(); -- 当前日期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss','%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间SELECT CURRENT_DATE(); /*获取当前日期*/
SELECT CURDATE(); /*获取当前日期*/
SELECT NOW(); /*获取当前日期和时间*/
SELECT LOCALTIME(); /*获取当前日期和时间*/
SELECT SYSDATE(); /*获取当前日期和时间*/
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
-- 时间日期函数
now(), current_timestamp(); -- 当前日期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss','%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间窗口函数
CUME_DIST() 累积分配值
DENSE_RANK()当前行在其分区中的排名，稠密排序（分数一致排名一致，分数不一致排名+1）FIRST_VALUE() 指定区间范围内的第一行的值
LAG() 取排在当前行之前的值
LAST_VALUE() 指定区间范围内的最后一行的值
LEAD() 取排在当前行之后的值
NTH_VALUE() 指定区间范围内第N行的值
NTILE() 将数据分到N个桶，当前行所在的桶号
PERCENT_RANK() 排名值的百分比
RANK()当前行在其分区中的排名，稀疏排序（相同分数有重复排名，但是重复后下一个人按照实际排名）
ROW_NUMBER()分区内当前行的行号（依次递增排名，无重复排名）
NTILE(4)分组排名，里面的数字是几，最多排名就是几，里面的数字是4，最多的排名就是4
select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名）
row_number()
select *,row_number() OVER(order by number ) as row_num from numselect *,row_number() OVER(order by number ) as row_num from num注意：在使用row_number() 实现分页时需要特别注意一点，over子句中的order by 要与SQL排序记录中的order by保持一致，否则得到的序号可能不是连续
-- 错误例子
select *,row_number() OVER(order by number ) as row_num from num ORDER BY id-- 错误例子
select *,row_number() OVER(order by number ) as row_num from num ORDER BY idrank()
select *,rank() OVER(order by number ) as row_num from numselect *,rank() OVER(order by number ) as row_num from numdense_rank()
select *,dense_rank() OVER(order by number ) as row_num from numselect *,dense_rank() OVER(order by number ) as row_num from numntile()
-- Ntile(group_num) 将所有记录分成group_num个组，每组序号一样
select *,ntile(2) OVER(order by number ) as row_num from num-- Ntile(group_num) 将所有记录分成group_num个组，每组序号一样
select *,ntile(2) OVER(order by number ) as row_num from num示例
CREATE TABLE 成绩单 (学号 VARCHAR (8), 姓名 VARCHAR (8), 科目 VARCHAR (8), 得分 INT )
ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO 成绩单
VALUES
('1000', '小明', '语文' ,112 ),
('1000', '小明', '数学' ,120 ),
('1000', '小明', '英语' ,92 ),
('1001', '云朵', '语文' ,112 ),
('1001', '云朵', '数学' ,118 ),
('1001', '云朵', '英语' ,99 ),
('1002', '库里', '语文' ,101 ),
('1002', '库里', '数学' ,111 ),
('1002', '库里', '英语' ,90 ),
('1003', '才子', '语文' ,112 ),
('1003', '才子', '数学' ,120 ),
('1003', '才子', '英语' ,112 ),
('1004', '小华', '语文' ,112 ),
('1004', '小华', '数学' ,112 ),
('1004', '小华', '英语' ,112 ),
('1005', '强森', '语文' ,92 ),
('1005', '强森', '数学' ,120 ),
('1005', '强森', '英语' ,92 );CREATE TABLE 成绩单 (学号 VARCHAR (8), 姓名 VARCHAR (8), 科目 VARCHAR (8), 得分 INT )
ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO 成绩单
VALUES
('1000', '小明', '语文' ,112 ),
('1000', '小明', '数学' ,120 ),
('1000', '小明', '英语' ,92 ),
('1001', '云朵', '语文' ,112 ),
('1001', '云朵', '数学' ,118 ),
('1001', '云朵', '英语' ,99 ),
('1002', '库里', '语文' ,101 ),
('1002', '库里', '数学' ,111 ),
('1002', '库里', '英语' ,90 ),
('1003', '才子', '语文' ,112 ),
('1003', '才子', '数学' ,120 ),
('1003', '才子', '英语' ,112 ),
('1004', '小华', '语文' ,112 ),
('1004', '小华', '数学' ,112 ),
('1004', '小华', '英语' ,112 ),
('1005', '强森', '语文' ,92 ),
('1005', '强森', '数学' ,120 ),
('1005', '强森', '英语' ,92 );SELECT *,
RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名 ,
DENSE_RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS DENSE_RANK_排名 ,
ROW_NUMBER() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS ROW_NUMBER_排名
FROM
成绩单SELECT *,
RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名 ,
DENSE_RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS DENSE_RANK_排名 ,
ROW_NUMBER() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS ROW_NUMBER_排名
FROM
成绩单
TOP N 问题
select * from
(select *,
row_number() over (partition by 要分组的列名 order by 要排序的列名 desc)
as ranking from 表名）
as t where ranking <= N;select * from
(select *,
row_number() over (partition by 要分组的列名 order by 要排序的列名 desc)
as ranking from 表名）
as t where ranking <= N;2、SQL执行过程
sql执行顺序
执行状态
通过命令:show full processlist，展示所有的处理进程
内连接|外连接区别
下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。
-- INNER JOIN（内连接）
SELECT * FROM `t1` inner join t2 on t1.id = t2.id
-- LEFT JOIN（左连接）
SELECT * FROM `t1` left join t2 on t1.id = t2.id
-- LEFT JOIN EXCLUDING INNER JOIN（左连接-内连接）
SELECT * FROM `t1` left join t2 on t1.id = t2.id WHERE t2.id is null
-- RIGHT JOIN（右连接）
SELECT * FROM `t1` right join t2 on t1.id = t2.id
-- RIGHT JOIN EXCLUDING INNER JOIN（右连接-内连接）
SELECT * FROM `t1` right join t2 on t1.id = t2.id WHERE t1.id is null-- INNER JOIN（内连接）
SELECT * FROM `t1` inner join t2 on t1.id = t2.id
-- LEFT JOIN（左连接）
SELECT * FROM `t1` left join t2 on t1.id = t2.id
-- LEFT JOIN EXCLUDING INNER JOIN（左连接-内连接）
SELECT * FROM `t1` left join t2 on t1.id = t2.id WHERE t2.id is null
-- RIGHT JOIN（右连接）
SELECT * FROM `t1` right join t2 on t1.id = t2.id
-- RIGHT JOIN EXCLUDING INNER JOIN（右连接-内连接）
SELECT * FROM `t1` right join t2 on t1.id = t2.id WHERE t1.id is null3、存储引擎
连接层：主要完成客户端的连接处理，授权，检查连接数。
服务层：绝大部分的核心功能都是在服务层完成，sql接口，解析器，优化器，缓存。
引擎层：可插拔式的存储引擎。索引实在存储引擎层实现的。
存储层：日志，数据，索引等。
InnoDB
InnoDB 是sql5.5 版本之后的默认的存储引擎。
InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准隔离级别(未提交读、提交读、可重复读、可串行化)。其默认级别时可重复读（REPEATABLE READ），在可重复读级别下，通过 MVCC + Next-Key Locking 防止幻读。
主索引时聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对主键查询有很高的性能。
InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读，能够自动在内存中创建 hash 索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。
InnoDB 支持真正的在线热备份，sql 其他的存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合的场景中，停止写入可能也意味着停止读取。
下面是InnoDB 架构图，左侧为内存结构，右侧为磁盘结构。
存储文件
xxx.ibd：xxx表示表名，InnoDB 引擎的每张表都会对应一个这样的表空间文件，存储该表的表结构（frm（8.0之后表结构存储到了sdi）、sdi）、数据和索引。
参数：innodb_file_per_table 8.0版本这个参数是打开的，每一张表有自己的表空间
逻辑存储结构
表空间（ibd文件），一个sql 实例可以对应多个表空间，用于存储记录、索引等数据。
段（Segment），分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollbak segment）。InnoDB 是索引组织表，数据段就是B+tree的叶子节点，索引段即为B+Tree的非叶子节点。段用来管理多个Extent。
区（Extent）表空间的单元结构，每个区的大小为1M，默认情况下InnoDB 存储引擎页大小为16k，即一个区中一共有64个连续的页。
页（Page），是InnoDB 存储引擎磁盘管理的最小单元，每个页默认大小为16k。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请4-5个区。
行（Row），InnoDB 存储引擎数据是按行存放的。
Trx_id: 每次对某条记录进行改动时，都会把对应的事务id 赋值给trx_id 隐藏列
Rool_pointer：每次对某条记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列相当于一个指针，可以通过它来找到该记录的修改前的信息。
内存结构
Buffer Pool
缓冲池是主内存中的一个区域，里面可以缓存磁盘上进场操作的真实数据，在执行增删改查操作时，先操作缓冲池的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。
缓冲池有一个一个的块，叫做缓冲池。缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态Page 被分为3类：
- free page：空闲page，未被使用。
- clean page：被使用page，数据没有被修改过。
- dirty page：脏页，被使用page，数据被修改过，数据与磁盘的数据产生了不一致
Change Buffer
更改缓冲区（针对于非一二级所以页），在执行DML 语句时，如果这些数据Page 没有在Buffer Pool 中，不会直接操作磁盘，而会将数据变更存在缓冲区Change Buffer 中，在未来数据被读取时，再将数据合并恢复到Buffer Pool 中，再讲合并后的数据刷新到磁盘中。
Change Buffer的意义
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响数中不相邻的二级索引页。如果每一次都操作磁盘，会造成大量磁盘IO，有了Change Buffer 之后，我们可以在缓冲池进行合并处理，减少磁盘IO。
Adaptive Hash Index
hash 索引最大优势在于快，因为他只需要一次匹配就可以完成（前提是不存在hash冲突的情况下），B+Tree 往往需要2-3次。但是他的弊端是不能够支持范围查询，只能做等值匹配。所以InnoDB 引擎就做了这个自适应hash 。
自适应hash 索引，用于优化对Buffer Pool 数据查询。InnoDB 存储引擎会监控对表上各索引的查询，如果观察到hash 索引可以提高速度，则建立hash 索引，称之为自适应hash 索引。
自适应hash 索引，无须人工干预，是系统根据情况自动完成。
参数：adaptive_hash_index
Log Buffer
日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log，undo log），默认大小是16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。
参数：innodb_log_buffer_size（缓冲区大小），innodb_flush_log_at_trx_commit(日志刷新到磁盘的时机)
刷新时机默认是1,1 日志在每次事务提交时写入并刷新到磁盘，0 每秒将日志写入并刷新到磁盘一次， 2 日志在每次事务提交后写入并每秒刷新到磁盘一次。
磁盘结构
System Tablespace
系统表空间是更新缓冲区存储的区域。如果表示在系统表空间而不是每个表文件或者通用表空间中创建的，它也可能包含表和索引的数据（在sql5.x版本中还包含InnoDB数据字典、undolog 等）
参数：innodb_data_file_path
File-Per-Table Tablespaces
每个表的文件表空间包含单个InnoDB 表的数据和索引，并存储在文件系统上的单个数据文件中。
参数：innodb_file_per_table
-- 创建表空间
create tablespace xxx add datafile 'xxx.ibd' engine = innodb;-- 创建表空间
create tablespace xxx add datafile 'xxx.ibd' engine = innodb;General Tablespaces
通用表空间，需要通过Create tablespace语法创建通用表空间，在创建表时，可以指定该表空间。
CREATE TABLE xxx(...) TABLESPACE xxxx;CREATE TABLE xxx(...) TABLESPACE xxxx;undo Tablespaces
撤销表空间，sql 实例在初始化时会自动创建2个默认的undo表空间(初始大小为16M)，用于存储undo log日志。
Temporary Tablespaces
InnoDB 使用会话临时表空间和全局临时表空间。存储用户会话和零时表等等数据。
Doublewrite Buffer Files
双写缓冲区，innoDB 引擎将数据页从Buffer Pool 刷新到磁盘前，先将数据写入到双写缓冲区文件中，便于系统异常时恢复数据。
Redo Log
Redo Log 是用来实现事务的持久性。该日志文件由两部分组成。重做日志缓冲（redo log buffer）已经重做日志文件（redo log），前者是在内存中，后者在磁盘中。事务提交之后会把所有修改信息都会存到该日志中，用于在刷新脏页到磁盘时，发送错误时，进行数据恢复使用。以循环方式写入重做日志文件。
后台线程
后台线程的作用就是将内存里缓冲池的数据在合适的时机刷新到磁盘文件当中。
Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池的数据异步刷新到磁盘中，保持数据一致性，还包括脏页的刷新、合并插入缓存、undo页的回收。
IO Thread
在InnoDB 存储引擎中大量使用了AIO 来处理IO 请求，这样可以极大地提高数据库的性能，从IO Thread 主要负责这些IO 请求的回调。
| 线程类型 | 默认个数 | 职责 |
|---|---|---|
| Read thread | 4 | 负责读操作 |
| Write thread | 4 | 负责写操作 |
| Log thread | 1 | 负责将日志缓冲区刷新到磁盘 |
| Insert buffer thread | 1 | 负责将写缓冲区内容刷新到磁盘 |
Purge Thread
主要用于回收事务已经提交了的undo log，在事务提交之后，undo log 可能不用了，就用它来回收
Page Cleaner Thread
协助 Master Thread 刷新脏页到磁盘的线程，他可以减轻Master Thread 的工作压力，减少阻塞。
当我们业务在操作的时候会直接操作sql 的缓冲区，如果缓冲区内没有数据，会将磁盘的数据加载进来，然后存储在缓冲区当中。在增删改查的时候会操作这个缓冲区里的数据。缓冲区的数据会以一定的频率，一定的时机通过后台线程刷新到磁盘当中，然后才持久化
MyISAM
提供了大量的特性，包括压缩表、空间数据索引等
不支持事务
不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）
如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。
存储文件
xxx.sdi: 存储表结构信息
xxx.MYD: 存储表数据
xxx.MYI: 存储索引
Memory
Memory 引擎的表数据是存放在内存中的，由于受到硬件问题或断点问题的影响，只能将这些表作为临时表或者缓存使用。
- 内存存放
- hash索引(默认)
存储文件
xxx.sdi: 存储表结构信息
存储引擎选择
InnoDB 和 MyISAM 的比较
- 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
- 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
- 外键：InnoDB 支持外键。
- 备份：InnoDB 支持在线热备份。
- 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
- 其它特性：MyISAM 支持压缩表和空间数据索引。
| 特点 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 |
| 事务安全 | 支持 | - | - |
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | - | - | 支持 |
| 全文索引 | 支持(5.6版本之后) | 支持 | - |
| 空间使用 | 高 | 低 | N/A |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 | - | - |
InnoDB ：是sql 的默认引擎，支持事务外键，对事务完整性要求比较高，要求一定并发条件下数据一致性，除查询和插入外还包括更新删除操作，InnoDB 比较适合
MyISAM ：以读操作插入操作为主少有更新和删除操作，并对事务的完整性，并发要求不是很高，MyISAM 比较适合
Memory 访问速度快，对于大数据表无法缓存在内存中，而且无法保证数据安全性。
4、缓冲池
sql作为一个存储系统，具有缓冲池(buffer pool)机制，以避免每次查询数据都进行磁盘IO。
InnoDB的缓冲池缓存什么？有什么用？
缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。
速度快，那为啥不把所有数据都放到缓冲池里？
凡事都具备两面性，抛开数据易失性不说，访问快速的反面是存储容量小：
（1）缓存访问快，但容量小，数据库存储了200G数据，缓存容量可能只有64G；
（2）内存访问快，但容量小，买一台笔记本磁盘有2T，内存可能只有16G；
因此，只能把“最热”的数据放到“最近”的地方，以“最大限度”的降低磁盘访问。
什么是预读？
磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据（一般是4K），如果未来要读取的数据就在页中，就能够省去后续的磁盘IO，提高效率。
预读为什么有效？
数据访问，通常都遵循“集中读写”的原则，使用一些数据，大概率会使用附近的数据，这就是所谓的“局部性原理”，它表明提前加载是有效的，确实能够减少磁盘IO。
按页(4K)读取，和InnoDB的缓冲池设计有啥关系？
（1）磁盘访问按页读取能够提高性能，所以缓冲池一般也是按页缓存数据；
（2）预读机制启示了我们，能把一些“可能要访问”的页提前加入缓冲池，避免未来的磁盘IO操作；
InnoDB是以什么算法，来管理这些缓冲页呢？
最容易想到的，就是LRU(Least recently used)。
画外音：memcache，OS都会用LRU来进行页置换管理，但sql的玩法并不一样。
传统的LRU是如何进行缓冲页管理？
最常见的玩法是，把入缓冲池的页放到LRU的头部，作为最近访问的元素，从而最晚被淘汰。这里又分两种情况：
（1）页已经在缓冲池里，那就只做“移至”LRU头部的动作，而没有页被淘汰；
（2）页不在缓冲池里，除了做“放入”LRU头部的动作，还要做“淘汰”LRU尾部页的动作；
传统的LRU缓冲池算法十分直观，OS，memcache等很多软件都在用，sql为啥这么矫情，不能直接用呢？
这里有两个问题：
（1）预读失效；
（2）缓冲池污染；
什么是预读失效？
由于预读(Read-Ahead)，提前把页放入了缓冲池，但最终sql并没有从页中读取数据，称为预读失效。
如何对预读失效进行优化？
要优化预读失效，思路是：
（1）让预读失败的页，停留在缓冲池LRU里的时间尽可能短；
（2）让真正被读取的页，才挪到缓冲池LRU的头部；
以保证，真正被读取的热数据留在缓冲池里的时间尽可能长。
具体方法是：
（1）将LRU分为两个部分：
- 新生代(new sublist)
- 老生代(old sublist)
（2）新老生代收尾相连，即：新生代的尾(tail)连接着老生代的头(head)；
（3）新页（例如被预读的页）加入缓冲池时，只加入到老生代头部：
- 如果数据真正被读取（预读成功），才会加入到新生代的头部
- 如果数据没有被读取，则会比新生代里的“热数据页”更早被淘汰出缓冲池
上述原理，对应InnoDB里哪些参数？
有三个比较重要的参数。
参数：innodb_buffer_pool_size
介绍：配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。
参数：innodb_old_blocks_pct
介绍：老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。
画外音：如果把这个参数设为100，就退化为普通LRU了。
参数：innodb_old_blocks_time
介绍：老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。
总结
（1）缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；
（2）缓冲池通常以页(page)为单位缓存数据；
（3）缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；
（4）InnoDB对普通LRU进行了优化：
将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题
页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题