数据库笔记总结

5 数据库

5.0 数据库串讲

事务及spring事务传播

1. 事务的概念：满足ACID特性的一组操作，可以提交和回滚。

2. 事务的四大特性：

• 原子性：事务被视为不可分割的最小单元，要么全被成功执行，要么全部失败回滚；

• 一致性：事务执行前后数据一致。如A和B之间转账，无论怎么转，执行前后的总金额保证不变。

• 隔离性：同时执行的事务是相互隔离的

• 持久性：事务一旦提交，做的修改永久保存在数据库

3. 事务的并发一致性问题：

• 丢失修改：两个事务对同一个事务修改，先修改的数据会被覆盖丢失。
• 脏读：读取未提交的数据。
• 不可重复读：一个事务两次读取的数据不一致。
• 幻读：范围查找时，两次相同语句执行查找后查找的数据行数不一致（其他事务新插入数据）

4. 事务隔离级别：

• 未提交读：啥都没有，最低级别。
• 提交读：事务所做的修改在提交之前对其他事务不可见，解决脏读问题。
• 可重复读：（默认隔离级别）事务多次读取同一个数据结果一样，解决不可重复读问题。
• 可串行化：强制事务串行执行，最高级别不会有并发问题，解决幻读问题。

5. MVCC多版本并发控制

MVCC用于实现提交读和可重复读两种隔离级别。MVCC的实现区别与储存引擎有关，下面仅针对InnoDB而言。

MVCC通过每行记录中隐藏的两个版本号实现，一个是创建时版本号，一个是删除版本号。每开启一个事务，系统版本号都会递增并分配给该事务该版本号。

• 查询时，只查找早于当前事务版本的数据行
• 插入时，数据的行的版本号为事务版本号
• 更新时，其实执行的是插入操作，将新插入的行的版本号设置为事务版本号，并将原先数据的删除版本号设置为当前版本号。
• 删除时，将数据行的删除版本号设置为当前版本号。

当我们正常使用select语句时，读取的是快照版本，若select ........for update则是表示当前读，即读取的是最新版本（不能保证可重复读）。

MVCC的隔离级别是可重复读，但是还通过间隙锁策略防止幻读的出现。

6. Spring中的事务传播

Spring的事务传播有七条规则：其中常见的两种：

• Required：调用该方法时，当前没有事务，则新建；有事务，则将该方法加入该事务中。
• Requires_new：不管当前有没有事务，都新开启一个事务。

MySQL的事务如何实现

通过两个日志文件实现，redo log和undo log。Undo log实现原子性和一致性，在操作数据之前，将数据备份到UndoLog，然后修改数据，如果需要回滚的话就利用UndoLog恢复。RedoLog实现持久化：防止提交事务时，数据库宕机，此时更新的数据页还在writeBuffer中没有持久化到磁盘。RedoLog日志文件的写入会在执行事务操作时就写入，而不是提交时才写入。

SQL语句的执行流程

一条SQL语句的执行会经历以下几个步骤：

• 连接器：用于身份认证和权限认证（是否有操作这个表的权限）
• 分析器：检查SQL的语句语法等
• 优化器：选择最优方案，走什么索引等
• 执行器：执行语句，返回结果

一条SQL语句查询慢的原因

在面试时最好分两种情况说明：这条SQL语句是每次执行都慢还是偶尔很慢。

若大多数正常，偶尔慢，可能的原因有：

• 数据库在执行其他IO操作
• 这条SQL语句在等待锁的释放：表锁、行锁、间隙锁等

若总是很慢，可能的原因如下：

• 字段没有索引，数据量大时全表扫描当然慢！
• 字段有索引，但依然慢，可能是索引失效。索引失效的原因很多
  • where条件中有函数操作、类型转换等导致索引失效
  • 索引不满足最左前缀原则
  • or、limit、like等导致索引失效

分布式ID方案

数据库的ID有多种方案，在分布式场景下，有以下解决方案

• 自增ID：（依赖于数据库）专门去建立一个数据库表用于生成自增ID，每次要获取ID时往表中插入一个数据然后返回最后插入数据的ID，并且在事务中完成。
  • 缺点明显：请求数据库再返回ID性能低下，且可靠性不足，数据库挂掉了无法生成ID。
  • 为解决可靠性，两个数据库集群，但可能存在主库挂掉后还未及时复制到从库，可能产生重复ID。
  • 还有一种解决方案就是，每次从数据库中获取一段ID缓存下来，用完后再去请求数据库获取一段。
• 雪花算法：不依赖于数据库。由三部分组成：时间戳+机器ID+序列号。
  • 优点：不依赖与数据库，可以写成一个工具类，需要时直接生成即可
  • 缺点：机器ID难以维护
• **Redis实现：**在Redis中存一个自增ID，每次获取的时候将ID自增（incr命令）。
  • 优点：Redis效率非常高
  • 缺点：需要考虑Redis持久化问题

MySQL中的日志文件

MySQL中有六种日志文件：

1）redo log：用于保证事务的持久性。防止数据库宕机后，write buf中还有脏页没来得及持久化到磁盘。

2）undo log：用于事务的回滚。保存了事务执行之前的数据的版本，当事务需要回滚时，根据此日志文件恢复。

3）binlog：记录执行过的SQL语句，用于主从复制或基于时间点的还原。会在事务提交时一次性写入该日志。

4）errorlog：错误日志

5）slow query log：慢查询日志

6）general log：普通查询日志

5.1 索引

5.1.1 索引的原理

索引：帮助数据库高效的获取数据的数据结构，MySQL索引有哈希表，B+树作为索引。

哈希表原则上查找更快，但基本不使用，因为哈希表解决不了范围查找，只能等值查询，无法用于排序！

磁盘的读取数据是以磁盘块（block）为单位的，而b树利用了磁盘块的特性，每个磁盘块一个节点，保证读取一个节点尽量刚好一个磁盘块，每个节点包含了很多关键字（一般200-300），能明显降低读取和查找次数。

B+树相对于B树，最大区别在于：叶子结点包含全部关键字以及指向相应记录的指针，而且叶结点中的关键字按大小顺序排列，相邻叶结点用指针连接；非叶子节点仅作为索引；

**B+**树相对于B树的优点： 1）非叶子结点不存放真正的数据，如此一来一个磁盘block能放更多的关键字，每个节点的索引范围更大，i/o次数相应更少；2）所有叶子结点按照顺序链表存储，增大区间查找效率！！

建立索引的缺点：1）空间上的代价；每个索引一个B+树，每个节点一个数据页，一个数据页16KB，空间大；

2）时间上代价；每次增删改操作时，需要维护B+树索引；

5.1.2 索引分类

1. 单值索引
2. 唯一索引:字段值必须唯一，允许有空值；
3. 复合索引（联合索引）

5.1.3 索引创建

索引创建

create index idx_city on city(city_name);  # 创建普通索引
# 创建唯一索引。
create unique index uk_mobile_password_nickname on user(mobile,password,nickname); 

索引添加

查看数据库表的索引

show index from acl_role;

删除索引

drop index idx_city on city;

5.1.4 索引设计原则

• 高选择性：反例就是在性别字段上建索引，显然违反高选择性，因为只有两种选择。

• 经常查询的字段，在where、order中经常出现的字段，可以考虑。

• 避免大型数据类型字段建立索引

• 尽量使用唯一索引。在阿里巴巴Java开发手册中强制件唯一特性的字段建立成唯一索引。

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0FiTwiNL-1596509569163)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200527192827192.png)]

• 索引并不是越多越好，索引需要占用空间和时间去维护索引。

• 利用最左原则建立联合索引

5.2 MySQL数据类型及优化

1. 整数类型：tinyint、smallint、mediumint、int、bigint，分别占8/16/24/32/64位

有符号和无符号性能一样，只是表示范围不一样，可以适当的指定，如存储年龄用tinyInt并指定unsigned。

2. 实数类型：Decimal存储精确的小数，也可以存储比bigint还大的整数。float、double是浮点数，有误差。

Decimal的计算代价会更大，因为CPU直接支持浮点数，所以尽量避免使用decimal，除非必须使用，如财务数据。

3. Char和Varchar类型：char长度固定，varchar长度可变并需要指定最大长度。

4. Bolb和Text类型：分别采用二进制和字符串存储，用于存储很大的数据

5. datatime和timestamp类型：用于存储时间。两种都是存储最大单位为秒，timestamp存储的是1970.1.1以来的秒数。更加推荐使用timestamp，因为空间效率更高。

5.3 视图、存储过程、存储函数、触发器

视图是一个虚拟的表，一般用于简化查询操作；

对于两张表，一个国家表，一个城市表，两者通过城市ID关联。当我们需要多次查询国家表和城市表中的数据时，可以建立一个视图，只需要查询视图即可。

创建视图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YO3w6YkQ-1596509569164)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200527193915180.png)]

视图可以更新，实际更新的是表中的数据，但是不建议使用！！

通过show tables;就可以查询表和视图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nG7SQR5C-1596509569165)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200527194312859.png)]

存储过程就是SQL语句的集合。储存过程也是可以编程的，，也有语法

5.4 触发器

用于对数据库表在某个操作之后或之前执行。

主要作用：如一张日志表记录对emp表增删改操作的记录，就可以创建一个触发器。当emp表数据有修改时，将修改的类型，时间，数据变更前后的数据记录在日志表中。

1. 创建insert的触发器

   create trigger emp_insert_trigger  --创建一个触发器
   after insert		--指定触发器类型。after、insert
   on emp			--对哪个表创建触发器
   for each row   --行记录变量
   begin
   insert into emp_logs(id, operation,opration_time,operation_id,params)  --向日志表添加记录
   values(null, insert,now(),new.id,concat('插入后ID',new.id,'name:',new.name));
   end$
   
   --说明：在begin end之间写触发器中执行的内容。在日志表中添加一个记录，now()函数代表当前时间，new代表当前行（插入emp表的那行数据），通过new.id就可以获取emp表中insert那行的id。同理也可以获取其他数据。
   

   在sql中执行上面触发器，那么当向emp表插入数据成功后，会向emp_logs表插入日志记录。

2. 创建update的触发器

   create trigger emp_update_trigger  --创建一个触发器
   after update		--指定触发器类型。after、insert
   on emp			--对哪个表创建触发器
   for each row   --行记录变量
   begin
   insert into emp_logs(id, operation,opration_time,operation_id,params)  --向日志表添加记录
   values(null, insert,now(),new.id,concat('插入前ID',old.id,
   '插入后ID：',new.id,
   'name:',new.name));
   end$
   
   --说明：old可以获取修改之前的数据，new可以获取修改后的数据
   

5.5 数据库存储引擎

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oCU1hBp6-1596509569166)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200527204130501.png)]

• 事务的支持：InnoDB支持事务，MyISAM不支持事务；
• 外键的支持：InnoDB是唯一支持外键的引擎。
  • 在创建外键时可以加ON Delete restrict：代表删除主表数据时，如果有关联的记录则不删除；
  • 在创建外键时可以加ON update CASCADE：代表更新主表时，子表中的关联记录也要更新；
  • 一对多中，多的一个外键指向一，一为主表。
• 行锁的支持：InnoDB支持行锁，MyISAM不支持；
• 聚集索引：InnoDB按照主键聚集索引存储，所有数据存放在以b+树为结构的树上。

5.6 数据库优化

5.6.1 基础查看性能指令

查看整个数据库中的执行次数

mysql> show global status like'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 9     |
| Com_delete    | 44    |
| Com_insert    | 26    |
| Com_repair    | 0     |
| Com_revoke    | 0     |
| Com_select    | 7117  |
| Com_signal    | 0     |
| Com_update    | 0     |
| Com_xa_end    | 0     |
+---------------+-------+
10 rows in set (0.00 sec)

5.6.2 低效的SQL语句定位

• 慢查询日志：通过慢查询日志定位哪些执行低效的SQL语句；
• show processlist;实时查看当前数据库中每个客户端的查询信息；

5.6.3 explain分析执行计划

在SQL语句之前加上explain就可以查看执行该指令的详细信息。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CFPUycww-1596509569167)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200527212446024.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6xnozIjh-1596509569167)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200527212408266.png)]

• id：表结构中的执行顺序；多表查询时，每个表的加载顺序，id越大优先级越高；

• select_type：查询类型[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cZYr2qHQ-1596509569168)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200527213552269.png)]

• type：显示查询类型（较重要的指标）[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CX77jqnD-1596509569168)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200527214125795.png)]

• key：实际使用的索引，为null表示没有走索引。

• key_len：索引中使用的字节数，长度越短越好

• rows：扫描的行数，越少越好

• extra：其他额外的执行计划信息。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lq40BneT-1596509569169)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200527215607807.png)]

5.7 索引的使用

5.7.1 避免索引失效

• 全值匹配：如创建name，status，address三个的联合索引，将三个数据都加在where语句中，就叫全值索引，一定会走索引。与where后面语句的顺序无关；
• 最左前缀法则：查询从索引的最左边的列开始，并且不跳过索引中的列。
  • where status=’’ and name=’’ 走索引；符合最左前缀法则
  • where name=’’ and address=’’ 仅仅走name索引，address没有走索引，不符合最左前缀；
• 联合索引中出现范围查询，范围查询字段之后的索引将失效；
  • where name=’’ and status>1 and address=’’; status是范围查询，那address的索引将失效。仅仅会走name，status索引；
• 不要在匹配字段上加运算符，会导致索引失效
• **字符串不加单引号会导致索引失效。**因为在底层进行了数值转换运算
• **尽量使用覆盖索引：**可以避免回表查询；在explain执行计划中，若Extra为Using index condition则表示走了索引但是有回表查询，若为Using where；Using index表示没有回表查询；
• 用or分割的查询条件，必须保证or连接的所有条件都有索引，都为单列索引，否则整个索引失效；（联合索引中只有第一个字段为单列索引）
• like模糊匹配
  • select * from t_user where name=’%科技’ 不走索引，当通配符在最前面时将失效
  • select * from t_user where name='科技%’ 走索引，
  • select id,name from t_user where name=’%科技’ 走索引，当覆盖索引时即使通配符在最前面也会走索引
• 全表扫描速度快于走索引时，不会走索引
  • 如一张表对address单独建立了索引，但是address中数据绝大多数都是为‘北京市’，那么当where address='北京市‘时就不会走索引，而是全表扫描。当where address='西安市’时就会走索引
• 条件中出现is null时且字段有索引时：
  • 此时走不走索引取决于上一条规则，即判断全表扫描快还是走索引快。
• in 和 not in索引情况：
  • 尽量使用in而不要使用not in。in会走索引而not in不会

5.7.2 单列索引和复合索引

1. 尽量使用复合索引。当创建复合索引时，相当于创建多个索引：

   create index id_name_sta_address on t_user(name.status,address);
   
   相当于创建了三个索引：
   name
   name + status
   name + status + address
   

2. 当where语句中出现多个单列索引时，只会走一个索引并不会使用全部索引

5.8 SQL优化

SQL语句的编写顺序

select distinct
	<name, id>
from 
	<left_table> <join_type>
join
	<right_table> on <join_condition>
where
	<where_condition>
group by
	<group_by_list>
having
	<having_condition>
order by
	<order_by_conduition>
limit
	<params>

5.8.1 大规模插入数据时的优化

通过文件插入指令

load data local infile '文件名' into table 表名 fields terminated by ',' lines terminated by '\n';

说明： fields terminated by ',' 表示字段之间使用逗号分隔
lines terminated by '\n'  每一条数据之间使用换行符分隔

• load大规模顺序时，数据顺序按照主键顺序导入会快很多。
• 如果有唯一性索引，在插入的时候会进行唯一性校验。如果关闭唯一性索引速度也会变快。
• 手动提交事务

5.8.2 insert优化

• 一条语句中插入多行记录，减少数据库连接

  insert into user values(1, 'a'),(2,'b'),(3,'c');
  

• 手动提交事务，插入多条数据时，手动提交事务，因为默认每一条insert语句开启一个事务然后自动提交。

  start transaction;
  insert into ...
  commit;
  

• 数据有序性插入

5.8.3 orderBy优化

• order By一定要使用覆盖索引才会走索引进行排序，否则就是filesort（文件系统排序）不会走索引。
• order By多字段排序时（排序字段顺序满足最左原则），在使用覆盖索引的情况下，若全部升序或全部降序则会走索引，若一个升序一个降序那么只会走一个索引，另一个不会走索引；
• 若多字段排序不满足最左前缀原则，也不会走索引；

尽量保证通过索引直接返回有序数据，减少额外的排序，即保证覆盖索引。保证orderBy顺序于联合索引的顺序相同且都是升序或都是降序；

5.8.4 groupBy优化

groupBy操作默认会进行排序操作，然后进行分组。

通过explain指令查看执行计划，可以看到有[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A5Lm0hiO-1596509569170)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200528111417636.png)]

可以使用order by null来取消排序，提升效率；[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ad16n9vC-1596509569170)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200528111650681.png)]

可以加入索引来将提升效率[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7vTOLskI-1596509569171)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200528111711280.png)]

5.8.5 优化嵌套查询

在多表联查的时候，不要使用这样的语句

select * from user where id in (select user_id from user_role);

正解：

select * from user u, user_role ur where u.id=ur.user_id;

5.8.6 Or优化

Or走索引的情况：or连接的所有字段都有索引且为单列索引。

优化的解决方案：使用union来替换or。union是指求并集的意思；

使用union时，会尽可能的走索引，而不是像or导致全部索引失效！

优化前

select * from user where mobile='111111' or name='aaa';

优化后

select * from user where mobile='111111' union select * from user where name='aaa';

若只对mobile建立了索引，优化前不会走任何索引，因为name没有建立索引；

优化后mobile会走索引，能更高效；

5.8.7 limit优化

当limit使用时默认是不走索引的，而是全表扫描。若limit使用时起始值很大，那么执行过程中将起始值之前的值查出来并丢掉，效率很低。

• 优化方案一：使用索引完成排序操作，然后再进行分页查询。

  # 优化前
  select * from user limit 1000000,10;
  
  #优化后
  select * from user u,
  (select id from user order by id limit 1000000,10) uo
  where u.id=uo.id;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FkSKdD8i-1596509569172)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200528135139222.png)]

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t5UVs0Zx-1596509569173)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200528135407194.png)]

• 优化方案二：在数据库严格主键自增且中间无断层的时候，即第200万条数据的id就是200万的时候，直接获取值。但是，实际情况中基本不可能满足这种情况；

5.8.8 索引提示

索引提示：建议数据库在SQL执行中使用哪个索引，不使用哪个索引；

当有多个索引可以使用时，可以建议数据库执行哪个索引；

5.9 应用层面优化

5.9.1 数据库连接池

5.9.2 减少对Mysql的访问

5.9.3 读写分离，主从复制，负载均衡

5.10 Mysql查询缓存

SQL执行流程图：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eU5LxtH9-1596509569173)(E:%5Cjava%E7%AC%94%E8%AE%B0%5Cimage-20200528141407043.png)]

mysql支持缓存

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

默认是关闭缓存的

mysql> show variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

缓存大小

mysql> show variables like 'query_cache_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 1048576 |
+------------------+---------+

SQL缓存的特点：表数据有任何修改，则有关于该表的数据缓存全部失效

• SQL缓存失效的几个情况：
  • SQL语句不一致导致失效：select 与 Select不一样也不会走缓存
  • 表有任何修改（增删改）
• 注意：在实际中，一般不会开启mysql缓存，表有任何修改都失效导致mysql缓存不适用。一般使用mybatis的一级二级缓存。

5.11 Mysql内存管理及优化

5.11.1 MyISAM的内存优化

MyISAM存储引擎使用keyBuffer缓存索引块，加速索引读写速度，对于数据块，没有任何缓存；

mysql中key_buffer_size参数决定MyISAM索引块缓存的大小。建议物理内存的1/4分配给该参数。

5.11.2 InnoDB内存优化

InnoDB用一块内存区做IO缓存，该缓存池不仅缓存InnoDB的索引块，而且也缓存InnoDB的数据块。

• innodb_buffer_pool_size:InnoDB储存引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序内存够用的情况下，这个值越大越好；值越大，缓存命中率越高，io就越少；

5.12 MySQL并发参数优化

• max_connections：mysql允许的最大连接量，默认151；超过151将一直等待；
• back_log：mysql达到最大连接数时，会有back_log个请求放在堆栈中等待，
• table_open_cache：所有请求打开表的数量
• thread_cache_size：MySQL缓存的一定数量的连接，相当于线程池
• innodb_lock_wait_timeout：行锁的等待时间

5.13 Mysql锁机制

• 表锁：偏性MyISAM，开销小加锁块，锁定力度大，锁冲突的概率最高；
• 行锁：偏性InnoDB，开销大但锁冲突概率小；
• 间隙锁：在可重复读的隔离级别中存在，用于预防幻读的出现。

5.13.1 MyISAM表锁

当执行select语句时，默认对表执行表锁，当执行修改操作时，默认加写锁；

当一个连接获取一张表读锁时，其他连接和该连接都不能修改该表，且该连接不能读其他表；

当一个连接对表加上写锁时，该连接可以读写；其他连接不能读写；

读锁会阻塞写，写锁会阻塞读写；

表锁适合做读为主的数据库；

5.13.2 InnoDB行锁

行锁特点：开销大，加锁慢；可能出现死锁；锁冲突概率小，并发高；

并发事务带来的问题：

• 丢失更新：两个事务同时修改同一个数据，最后的事务修改会覆盖之前的修改；
• 脏读：读取了未提交的事务
• 不可重复读：在一个事务中，未提交之前看不到其他事务的修改，即使其他事务提交
• 幻读：两个查询数据发现行数多了或者少了（其他事务插入了数据）

针对并发事务的问题，有四个隔离级别

• 未提交读：最低的隔离级别，未提交的事务对其他事务可见
• 提交读：未提交的事务对其他事务不可见
• 可重复读（默认）：同一个事务读取的数据一致。
• 可串行化：解决所有问题

set autocommit=0;  # 关闭自动提交
commit;   #手动提交

InnoDB中默认支持的是行锁，行锁的特点：

• 事务1获取一个行锁，若执行写操作（加写锁），其他事务不能对该行进行写操作；
• 事务1获取一个行锁，若执行读操作（读锁），其他事务可以对该行读写，但是对事务1不可见

**行锁升级为表锁：**索引失效时行锁升级为表锁；

5.13.3 间隙锁

间隙锁发生在可重复读的隔离级别中，如若是提交读隔离级别，是不存在间隙锁的。

什么是间隙锁？

间隙锁就是将锁加在了不存在的空闲空间，防止其他事务去插入数据造成幻读。

间隙锁什么时候加？

数据库使用可重复读隔离级别时，当开启一个事务后，使用select ......where ... for update或update/insert/delete语句时会加间隙锁。**注意：正常的select ... where ...语句不会造成间隙锁，**因为这样是读取快照版本而不是最新版本，不会有间隙锁产生。

相同的，间隙锁锁住的行不影响其他事务读取快照版本！

间隙锁怎么加？

谈间隙锁时必须谈索引！！不同索引处理不同！！以下结论在MySQL5.7下实验得出，实验可能有不完善的情况导致结论不正确，欢迎指出。

当a无索引的时候

• where a=1时：会将所有行加间隙锁
• where a>5时：会锁所有行
• where a<5时：会锁所有行

当a是唯一索引/主键索引时

• where a=1时：只锁住a=1的左右间隙行。
• where a>5时：会锁住a>5的行。 由于是唯一索引，查询操作不会因为间隙锁阻塞。
• where a<5时：会锁a<5的行。由于是唯一索引，查询操作不会因为间隙锁阻塞。
• where a>5 and a<10时：锁全部行？a=1的行也被锁？

当a是普通索引时

• where a=1时：只锁住a=1的左右间隙行。
• where a>5时：会锁住所有行。
• where a<5时：会锁住所有行。
• where a>5 and a<10时：锁全部行。

优化总结：

• 尽可能让所有数据检索能通过索引完成，避免无索引行锁升级为表锁
• 尽量减少锁的范围
• 尽可能控制事务大小，减少锁定资源的数量和时间长度

5.14 mysql日志

5.14.1 错误日志

所有错误的信息都储存在错误日志，默认开启。

查看错误日志存放目录

mysql> show variables like 'log_error%';
+---------------------+-----------------------+
| Variable_name       | Value                 |
+---------------------+-----------------------+
| log_error           | /data/mysql/mysql.err |
| log_error_verbosity | 3                     |
+---------------------+-----------------------+

5.14.2 二进制日志

记录了所有DDL（数据定义）语句和DML（数据操纵）语句，但是不包含数据查询记录；此日志对于数据恢复有着极其重要的作用，mysql的主从复制就是通过该日志实现的；

默认不开启，在/etc/my.cnf中添加以下信息开启二进制日志

lon_bin=mysqlbin   #开启二进制日志
binlog_format=MIXED  # 配置格式。

二进制配置格式：

• STATEMENT：存储的是SQL语句
• ROW：存储的是数据行的变更
• MIXED：两个的混合（默认）

5.14.3 查询日志

记录所有客户端的所有操作语句，默认不开启；如果要开启，配置如下：

general_log=1  #开启
general_log_file="query.log"   #文件名。（/data/mysql/query.log)

5.14.4 慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且记录数不小于min_examined_row_limit的所有SQL语句日志。

慢查询日志默认是关闭的，可以通过以下参数开启

slow-query-log=1
long_query_time=1
slow_query_log_files=slow_query.log

开启后，执行大于1s的语句会被记录在日志中；

5.15 MySQL优化思路

1. 合理的设计数据库表，范式与反范式结合。
   1. 完全范式更新操作一般更快，但是需要表关联可能造成性能下降
   2. 反范式将所有数据放在一个表中，可以避免关联，但是维护困难
2. 选择合适的数据类型存储。
   1. 尽可能的选择简单的数据类型去存储，整型比字符串代价大，如IP地址应该用无符号整型存储。
      1. varchar类型占用空间比整形更大，如果有索引的话，效率也会更低，两个原因：第一因为占用空间大导致B+树中每个索引页的索引更少；第二varchar类型数据的比较为逐字符比较，效率低于整形。
   2. 尽量避免NULL，通常情况下指定列为not null。因为null的列使得索引和值比较更加复杂。
3. 分析业务，必要时建立高性能的索引，索引也是优化效果最明显的方法。
   1. 最左前缀原则，覆盖索引等原则建索引
   2. 尽量unique
   3. 一般在where后的字段或orderBy/group By后的字段上考虑索引
   4. 避免不同值较少的字段上建立索引，如性别
4. SQL查询优化
   1. 是否向数据库请求了不需要的数据，是否可以使用覆盖索引。
   2. 利用好缓存！
   3. explain查看执行计划，看索引方式和扫描行数是否理想中的值
   4. 复杂查询尝试切分查询、分解关联查询。
5. 选择合适的事务隔离级别：看提交读事务的隔离级别是否满足要求，能减少间隙锁带来的开销。
6. 数据库连接的优化：数据库连接池大小设置为CPU核心数*2