mysql

mysql索引

Posted on 2021-03-24，34 min read

[TOC]

索引

索引是谁实现的？

  索引是第三方存储引擎实现的。

索引是什么？

  索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。

为什么要用索引？

  1. 索引能极大的减少存储引擎需要扫描的数据量。
  2. 索引可以把随机IO变成顺序IO
  3. 索引可以帮助我们在进行分组、排序等操作时，避免使用临时表。
    总之：索引能提升数据查找速度。

二叉树

二叉查找树（Binary Search Tree）

  又称二叉排序树（Binary Sort Tree）
该树的右子节点总是大于父节点，左子节点总是小于父节点。缺点：当一系列数据呈递增或递减插入时，二叉搜索树则变为链表形式，性能下降。
4385f386e3d14eadddd9f35f9f4c8c41.png@w=200eae4f407986857c413e631c9812af866.png@w=200

平衡二叉查找树（Balanced Binary Tree）

40c123c425cacaaf3e98eca6b55df399.png@w=500
平衡二叉树在二叉查找树的基础上添加了LR操作，使得二叉树子节点的高度差不大于1。解决了二叉查找树线性问题。
bf194db5036f588f4510979888971f2b.png@w=500
常见平衡二叉树：

  • 红黑树
  • AVL树

平衡二叉树缺点：

  1. 树太深了
    数据所处的深度决定查找它所需的io次数，深度越深，io耗时越久。
  2. 太小了
      每一个磁盘块（节点/页）保存的数据量太小了，没有很好的利用操作磁盘IO的数据交换特性，也没有利用好磁盘IO的预读能力（空间局部性原理），从而带来频繁的IO操作 。

多路平衡查找树B-Tree（balance tree）

7bec7f560c97e6f925eaca7126d45a45.png@w=500
树的路数取决于关键字的大小。

  • 路数=关键字个数+1

  • 关键字个数=磁盘块大小/（关键字大小+冗余空间）
    优点：

  • 树的深度相对于平衡二叉查找树降低很多，所以查询所需的io操作次数减少。

  • 充分利用了磁盘块空间。

加强版的多路平衡查找树B+Tree

8fa8c7ae90b6a096aef430a346e436ed.png@w=500
  B+Tree是B-Tree的的升级版，B+Tree使用的是左闭合的关键字节点集合，因为mysql建议使用自增的int型id作为主键。每个节点都不会存储关键字的数据，数据都存储在叶子节点上，所以查询数据都最终落到叶子节点上。B+Tree的叶子节点上的数据是按顺序存储的，每个叶子节点的末尾数据都会指向相邻的下一个叶子节点的起始数据区。
  mysql在表中没有主键时，会使用具有唯一性的字段作为主键存储，如果连唯一性字段都没有，则会创建一个隐式的6位int型主键。

B+Tree与B-Tree的区别：
  • B+节点关键字搜索采用左闭合区间。
  • B+非叶子节点不保存数据相关信息，只保存关键字和子节点的引用。
  • B+关键字对应的数据都保存在叶子节点中。
  • B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系。
B+Tree的优点：
  • B+树是B-树的变种（PLUS版）多路绝对平衡查找树，他拥有B-树的优势
  • B+树扫库、表能力更强
    因为B+树的叶子节点是按顺序排列的，所以扫描叶子节点就可以将整个表数据扫描完成。所以效率更高。
  • B+树的磁盘读写能力更强
    因为B+树的非叶子节点上不存关键字对应的数据，所以每个磁盘块（节点）可以存储的关键字个数越多，所以磁盘的读写能力更强。
  • B+树的排序能力更强
    叶子节点具有顺序性。
  • B+树的查询效率更加稳定（仁者见仁、智者见智）
      因为B树将关键字和其对应的数据存储在同一个节点上，所以当搜索数据时，因为数据对应的关键字所处节点的深度不同，所以io次数不同，导致查询时间可能差别很大。
      因为B+树将关键字数据都存储在叶子节点上，所以查询任何数据都会落到叶子节点上，所以查询效率更加稳定。
      在程序设计上，程序的可控性要高，程序运行时间要稳定和可知，这样的软件才更加健壮。

Mysql B+Tree索引体现形式

MyISAM中B+Tree

d0ae8bab28493c122a45971698ff7c79.png@w=500
在myisam引擎中，索引和数据分为两个文件存储。索引的叶子节点存储数据行的存储地址。
89173119419bd66deffcd71046830c87.png@w=600
辅助索引和主键索引结构一样。

InnoDB中B+Tree

92ece67ceb3a4c9d673505b81498776b.png@w=500
在InnoDB中索引和数据存储在一个文件中。
9c69dceb9e6395ecf19a462f5b98aec3.png@w=500
InnoDB中辅助索引在叶子节点会存储主键索引值，在使用辅助索引时，会先通过辅助索引树查找到主键索引值，然后再通过主键索引树搜索到对应的数据。因此，辅助索引会进行两次树的搜索。

InnoDB VS MyISAM

27f60d3580d84d8cf6d6ccadd5fb9cb3.png@w=600

索引知识

列的离散性

4c3c9f7da95a5f02d0a49836bf9fba65.png@w=300
离散性最好的列：name
count(column)越大离散性越高。
d69a549616f2966c5bc45018291c88f3.png@h=100
  因为列的离散性越高，选择性就越好。上图是对sex列建立的索引，可以看出当进行性别查询的时候，可能会存在很多个分支选择，索引的效率会下降，同时mysql查询优化器，会在使用索引查询时判断效率，如果效率过低，还不如走全表扫描查询。
总结：离散性越高，选择性就越好，使用索引查询效率就越高。

最左匹配原则

对索引中关键字进行计算（对比），一定是从左往右依次进行， 且不可跳过。索引中关键字的匹配：对于int型的直接比对数字大小。如果是字符串型的则根据字段建立时的排序规则进行比对，例如使用字母ASCII码进行比对。
b8c5bc1cfd35bf626f5d8b8257787ed9.png@w=300

联合索引

  1. 单列索引
    节点中关键字[name]
  2. 联合索引
    节点中关键字[name,phoneNum]
    单列索引是特殊的联合索引

联合索引列选择原则：

  • 经常用的列优先 【最左匹配原则】
  • 选择性（离散度）高的列优先【离散度高原则】
  • 宽度小的列优先【最少空间原则】
李二狗的sql优化

经排查发现最常用的sql语句：
Select * from users where name = ? ;
Select * from users where name = ? and phone_num = ?;
机灵的李二狗的解决方案：
索引一：create index idx_name on users(name);
索引二：create index idx_name_phoneNum on users(name,phone_num);
  查看李二狗创建的索引，可以看到索引二是个联合索引，根据最左匹配原则，索引一这属于冗余索引，所以索引一可以去掉。

覆盖索引

  如果查询列可通过索引节点中的关键字直接返回，则该索引称之为覆盖索引。
例如：
create index idx_name_phoneNum on users(name,phone_num);
则语句：
Select name,phone_num from users where name = ? and phone_num = ?;
可以直接通过索引关键字返回name和phone_num的值。则idx_name_phoneNum称之为覆盖索引。
  覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能。

总结

  1. 索引列数据的长度能少则少。
    B+Tree是个多路平衡树，索引列的数据长度越少，树的路数就越多，则树的高度也就越低，每次查询所需的IO次数就越少，能提升查询性能。
  2. 索引一定不是越多越好，越全越好，一定是建合适的。
  • 每个索引都需要单独维护一个索引列数据的存储，过多的建立索引会浪费磁盘空间。
  • 每次进行表中数据的修改、插入或删除操作都需要进行索引重建，建立过多的索引会使修改、插入或删除操作的效率下降。
  1. 匹配列前缀可能用到索引 like 9999%，like %9999%、like %9999用不到索引；
    like 9999%也可能存在索引失效的情况，当索引列的数据离散性较低时，使用该索引进行搜索时，会匹配过多的关键字，mysql优化器会放弃索引而进行全表扫描查询。导致索引失效。
  2. where 条件中 not in 和 <>操作无法使用索引； <>表示不等于
  3. 匹配范围值，order by 也可用到索引；
  4. 多用指定列查询，只返回自己想到的数据列，少用select * ；
    使用select * 将不会命中覆盖索引，使用具体字段时可能命中覆盖索引，提升效率。
  5. 联合索引中如果不是按照索引最左列开始查找，无法使用索引；
    因为索引采用最左匹配原则。
    create index idx_name_phoneNum on users(name,phone_num);
    如果：
    Select name,phone_num from users where phone_num = ?;
    则不会使用索引。
  6. 联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；
  7. 联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引；

存储引擎

存储引擎介绍

  1. 插拔式的插件方式。
  2. 存储引擎是指定在表之上的，即一个库中的每一个表都可以指定专用的存储引擎。
  3. 不管采用什么样的存储引擎，都会在数据区产生对应的一个frm文件（表结构定义描述文件）。

CSV存储引擎（Comma-Separated Values）了解

数据存储以CSV文件。
特点：

  1. 不能定义没有索引、列定义必须为NOT NULL、不能设置自增列
    -->不适用大表或者数据的在线处理。
  2. CSV数据的存储用,隔开，可直接编辑CSV文件进行数据的编排
    -->数据安全性低
    注：编辑之后，要生效使用flush table XXX 命令

应用场景：

  • 数据的快速导出导入
  • 表格直接转换成CSV

Archive存储引擎（压缩）了解

  • 压缩协议进行数据的存储
  • 数据存储为ARZ文件格式

特点：

  1. 只支持insert和select两种操作
  2. 只允许自增ID列建立索引
  3. 行级锁
  4. 不支持事务
  5. 数据占用磁盘少

应用场景：

日志系统，大量的设备数据采集

Memory存储引擎 了解

数据都是存储在内存中，IO效率要比其他引擎高很多，服务重启数据丢失，内存数据表默认只有16M 。

特点：

支持hash索引，B tree索引，默认hash（查找复杂度0(1)）字段长度都是固定长度varchar(32)=char(32) 不支持大数据存储类型字段如 blog，text 表级锁。

应用场景：

等值查找热度较高数据 查询结果内存中的计算，大多数都是采用这种存储引擎 作为临时表存储需计算的数据

MyISAM存储引擎

Mysql5.5版本之前的默认存储引擎，较多的系统表也还是使用这个存储引擎，系统临时表也会用到Myisam存储引擎。

特点：

  • select count(* ) from table 无需进行数据的扫描
  • 数据（MYD）和索引（MYI）分开存储
  • 表级锁
  • 不支持事务

InnoDB 重点

Mysql5.5及以后版本的默认存储引擎。

特点

  • Its DML operations follow the ACID model [事务ACID]
  • Row-level locking[行级锁]
  • InnoDB tables arrange your data on disk to optimize queries based on primary keys[聚集索引（主键索引）方式进行数据存储]
  • To maintain data integrity, InnoDB supports FOREIGN KEY constraints[支持外键关系保证数据完整性]

mysql体系结构

0d9c4adaaee0f8e66d1a7550decc2a56.png@w=500

  • Client Connectors
    接入方，支持协议很多。
  • Management Serveices & Utilities
    系统管理和控制工具，mysqldump、 mysql复制集群、分区管理等。
  • Connection Pool
    连接池：管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求。
  • SQL Interface SQL
    接口：接受用户的SQL命令，并且返回用户需要查询的结果。
  • Parser
    解析器，SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的。
  • Optimizer
    查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。
  • Cache和Buffer
    高速缓存区） 查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
  • pluggable storage Engines
    插件式存储引擎。存储引擎是MySql中具体的与文件打交道的子系统。
  • file system
    文件系统，数据、日志（redo，undo）、索引、错误日志、查询记录、慢查询等。

mysql查询优化详解

mysql查询执行路径

6930827b1a3d52a38547b487a8801ada.png@h=300
上图执行路径可以分为以下五条：

  1. mysql 客户端/服务端通信
  2. 查询缓存
  3. 查询优化处理
  4. 查询执行引擎
  5. 返回客户端
mysql 客户端/服务端通信

mysql客户端和服务端间得通信方式是：半双工
全双工：双向通信，发送同时也可以接收

半双工：双向通信，同时只能接收或者是发送，无法同时做操作

单工：只能单一方向传送

半双工通信：在任何一个时刻，要么是有服务器向客户端发送数据，要么是客户端向服务端发送数据，这两个动作不能同时发生。所以我们无法也无需将一个消息切成小块进行传输
半双工特点和限制： 客户端一旦开始发送消息，另一端要接收完整个消息才能响应。 客户端一旦开始接收数据没法停下来发送指令。

mysql 客户端/服务端通信状态查询

对于一个mysql连接，或者说一个线程，时刻都有一个状态来标识这个连接正在做什么。
查看命令 show full processlist / show processlist
https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html (状态全集)

  • Sleep 线程正在等待客户端发送数据
  • Query 连接线程正在执行查询
  • Locked 线程正在等待表锁的释放
  • Sorting result 线程正在对结果进行排序
  • Sending data 向请求端返回数据
查询缓存
  • 工作原理：
    缓存SELECT操作的结果集和SQL语句；
    新的SELECT语句，先去查询缓存，判断是否存在可用的记录集；
  • 判断标准：
    与缓存的SQL语句，是否完全一样，区分大小写 (简单认为存储了一个key-value结构，key为sql，value为sql查询结果集)
  • 缓存属性设置
  1. query_cache_type 值：0 -– 不启用查询缓存，默认值； 值：1 -– 启用查询缓存，只要符合查询缓存的要求，客户端的查询语句和记录集 都可以缓存起来，供其他客户端使用，加上 SQL_NO_CACHE将不缓存 值：2 -– 启用查询缓存，只要查询语句中添加了参数：
  2. SQL_CACHE，且符合查询 缓存的要求，客户端的查询语句和记录集，则可以缓存起来，供其他客户端使用
  3. query_cache_size 允许设置query_cache_size的值最小为40K，默认1M，推荐设置 为：64M/128M；
  4. query_cache_limit 限制查询缓存区最大能缓存的查询记录集，默认设置为1M
  5. show status like 'Qcache%' 命令可查看缓存情况
  • 查询缓存不会缓存得情况
    1.当查询语句中有一些不确定的数据时，则不会被缓存。如包含函数NOW()， CURRENT_DATE()等类似的函数，或者用户自定义的函数，存储函数，用户变量等都不会被缓存。
    2.当查询的结果大于query_cache_limit设置的值时，结果不会被缓存。
    3.对于InnoDB引擎来说，当一个语句在事务中修改了某个表，那么在这个事务提交之前，所有与这个表相关的查询都无法被缓存。因此长时间执行事务，会大大降低缓存命中率。
    4.查询的表是系统表。
    5.查询语句不涉及到表。
  • 查询缓存的弊端
    为什么mysql默认关闭了缓存开启？？
    1.在查询之前必须先检查是否命中缓存,浪费计算资源。
    2.如果这个查询可以被缓存，那么执行完成后，MySQL发现查询缓存中没有这 个查询，则会将结果存入查询缓存，这会带来额外的系统消耗。
    3.针对表进行写入或更新数据时，将对应表的所有缓存都设置失效。
    4.如果查询缓存很大或者碎片很多时，这个操作可能带来很大的系统消耗。
  • 查询缓存适合业务场景
    以读为主的业务，数据生成之后就不常改变的业务，比如门户类、新闻类、报表类、论坛类等。
查询优化处理

查询优化处理的三个阶段：

  1. 解析sql
    通过lex词法分析,yacc语法分析将sql语句解析成解析树 https://www.ibm.com/developerworks/cn/linux/sdk/lex/
  2. 预处理阶段
    根据mysql的语法的规则进一步检查解析树的合法性，如：检查数据的表 和列是否存在，解析名字和别名的设置。还会进行权限的验证
  3. 查询优化器（重点）
    优化器的主要作用就是找到最优的执行计划
查询优化器如何找到最优执行计划？
  • 使用等价变换规则
    例如：5=5 and a>5 改写成 a>5
    a<b and a=5 改写成 b>5 and a=5
  • 优化count 、min、max等函数
    min函数只需找索引最左边
    max函数只需找索引最右边
    myisam引擎count( * )有单独存储，可以直接查询。
  • 覆盖索引扫描
  • 子查询优化
    *** 提前终止查询**
    用了limit关键字或者使用不存在的条件
  • IN的优化
    先对条件进行排序，然后使用二分查找

Mysql的查询优化器是基于成本计算的原则。他会尝试各种执行计划。 数据抽样的方式进行试验（随机的读取一个4K的数据块进行分析）

mysql查询优化-执行计划
1fbe81391cfd3eff001e9ad1e391d50e.png
执行计划-id

select查询的序列号，标识执行的顺序

  1. id相同，执行顺序由上至下。
  2. id不同，id大的先执行。（子查询的id会增大）
  3. id存在相同的和不同的：id相同的为一组，执行顺序同1、2。
执行计划-select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等

  • SIMPLE：简单的select查询，查询语句中不包括子查询或者union。
  • PRIMARY：查询中包含子查询，最外层查询标记为PRIMARY。
  • SUBQUERY/MATERIALIZED
    SUBQUERY：SUBQUERY表示在select 或 where列表中包含了子查询；
    MATERIALIZED：表示where 后面in条件的子查询；
  • UNION：若第二个select出现在union之后，则被标记为union；
  • UNION RESULT：从union表获取结果的select；
执行计划-table

查询涉及到的表

  • 直接显示表名或者表的别名;
  • <unionM,N> 由ID为M,N 查询union产生的结果;
  • 由ID为N查询生产的结果;
执行计划-type

访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：
system > const > eq_ref > ref > range > index > ALL

  • system：表只有一行记录（等于系统表），const类型的特例，基本不会出现，可以忽略不计；
  • const：表示通过索引一次就找到了，const用于primary key或unique索引；
  • eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或 唯一索引扫描；
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质是也是一种索引访问；
  • range：只检索给定范围的行，使用一个索引来选择行；
  • index:Full Index Scan，索引全表扫描，把索引从头到尾扫一遍；
  • all：Full Table Scan，遍历全表以找到匹配的行；
执行计划其它属性
  • possible_keys
    查询中可能用到的索引；
  • key
    实际使用的索引，如果为null，则没有使用索引；
  • rows
    根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数；
  • filtered
    它指返回结果的行占需要读到的行(rows列的值)的百分比，表示返回结果的行数占需读取行数的百分比，filtered的值越大越好；
执行计划-Extra

十分重要的额外信息
1、Using filesort：
mysql对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取；
2、Using temporary：
使用临时表保存中间结果，也就是说mysql在对查询结果排序时使用了临时表，常见于order by 或 group by；
3、Using index：
表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高；
4、Using where：
使用了where过滤条件；
5、select tables optimized away：
基于索引优化MIN/MAX操作或者MyISAM存储引擎优化COUNT( * )操作，不必等到执行阶段在进行计算，查询执行 计划生成的阶段即可完成优化；

执行引擎执行

调用插件式的存储引擎的原子API的功能进行执行计划的执行。

返回客户端

1、有需要做缓存的，执行缓存操作。
2、增量的返回结果：开始生成第一条结果时,mysql就开始往请求方逐步返回数据 好处： mysql服务器无须保存过多的数据，不会浪费内存。用户体验好，马上就拿到了数据。

定位慢sql

  1. 业务驱动
  2. 测试驱动
  3. 慢查询日志
慢查询日志配置

show variables like 'slow_query_log'
set global slow_query_log = on
set global slow_query_log_file = '/var/lib/mysql/gupaoedu-slow.log'
set global log_queries_not_using_indexes = on
set global long_query_time = 0.1 (秒)

慢查询日志分析

cd6465616357a1f081d164b1e93eac16.png
Time ：日志记录的时间
User@Host：执行的用户及主机
Query_time：查询耗费时间 Lock_time 锁表时间 Rows_sent 发送给请求方的记录 条数 Rows_examined 语句扫描的记录条数
SET timestamp 语句执行的时间点
select .... 执行的具体语句

分析工具：
mysqldumpslow -t 10 -s at /var/lib/mysql/gupaoedu-slow.log

事务

什么是事务

数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作； 事务是一组不可再分割的操作集合（工作逻辑单元）;
典型事务场景(转账)：
update user_account set balance = balance - 1000 where userID = 3;
update user_account set balance = balance +1000 where userID = 1;
mysql中如何开启事务：
begin / start transaction -- 手工
commit / rollback -- 事务提交或回滚
set session autocommit = on/off; -- 设定事务是否自动开启
JDBC 编程：
connection.setAutoCommit（boolean）;
Spring 事务AOP编程：
expression=execution(com.gpedu.dao.* . * (..))

事务ACID特性

原子性（Atomicity）
一个事务时最小的工作单元，不可分割。事务内的操作要么全部成功提交，要么全部失败回滚。
一致性（Consistency）
事务操作数据的结果要具有一致性，数据的新增或删除要和预期结果一致。
隔离性（Isolation）
事务之间对事务内部的操作是不可见的，事务是个独立的单元。
持久性（Durability）
事务所做的修改就会永久保存，不会因为系统意外导致数据的丢失。

事务并发带来的问题

脏读——读未提交

b0b4a4c47fe4f602870cf212b6482e5b.png
事务A会读到另一个事务B未提交的数据，叫做脏读。

不可重复读——读已提交

970c0d26637d04c4bd59e1bf20b3d568.png
事务A中多次读取多行数据时,事务B对这行数据进行了修改，导致事务A多次读取结恶不一样，这条数据出现不可重复读的问题。

幻读

539e50212c14b57cc63aae135f43b1b7.png
事务A在进行范围查询的时候，事务B对查询范围内的数据进行了新增或者修改，导致事务A两次查询结果条数不一样，这种现象叫做幻读。

事务四种隔离级别

Read Uncommit (读未提交)

事务并发的问题全部不能解决。隔离级别最低。

Read Commit（读已提交）

可以解决脏读问题。

Repeatable Read (可重复读)

可以解决不可重复读的问题，但是并不能解决幻读问题。

Serializable（串行化）

所有事务并发问题都能解决。隔离级别最高。

InnoDB对四种隔离级别的支持程度

266ad919d0369d2f78050b7f5c3c6f21.png

锁

理解表锁和行锁

锁是用于管理不同事务对共享资源的并发访问。
表锁与行锁的区别：
锁定粒度：表锁>行锁
加锁效率：表锁>行锁
冲突概率：表锁>行锁
并发性能：表锁<行锁
InnoDB存储引擎支持行锁和表锁（另类的行锁）,InnoDB的表锁是通过给每行数据加锁实现。

mysql InnoDB锁类型

  • 共享锁（行锁）：Shared Locks
  • 排它锁（行锁）：Exclusive Locks
  • 意向锁共享锁（表锁）：Intention Shared Locks
  • 意向锁排它锁（表锁）：Intention Exclusive Locks
  • 自增锁：AUTO-INC Locks

共享锁和排它锁

共享锁: 又称为读锁，简称S锁。共享锁就是多个事务对于同一个数据可以共享一把锁，都能访问数据，但是只能读不能修改。
加锁释锁方式：
select * from user where id = 1 LOCK IN SHARE MODE;
commit/rollback
排他锁:
又称为写锁，简称X锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他 锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有该获取了排他锁的事务是可以对 数据行进行读取和修改，（其他事务要读取数据可来自于快照）
加锁释锁方式：
delete / update / insert 默认加上X锁
SELECT * FROM table_name WHERE ... FOR UPDATE
commit/rollback

InnoDB行锁到底锁了什么？

InnoDB是通过给索引上的索引项加锁实现的。

只有通过索引条件进行数据检索，InnoDB才使用行级锁，否则，InnoDB 将使用表锁（锁住索引的所有记录）

表锁：lock tables xx read/write；

意向锁共享锁（IS）和意向锁排它锁（IX）

意向共享锁(IS)
表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先取得该表的IS锁， 意向共享锁之间是可以相互兼容的。
意向排它锁(IX)
表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的IX锁， 意向排它锁之间是可以相互兼容的。

意向锁(IS、IX)是InnoDB数据操作之前自动加的，不需要用户干预
意义：
当事务想去进行锁表时，可以先判断意向锁是否存在，存在时则可快速返回该表不能启用表锁

自增锁

针对自增列自增长的一个特殊的表级别锁
show variables like 'innodb_autoinc_lock_mode';
默认取值1，代表连续，事务未提交ID永久丢失

临键锁（Next-key）&间隙锁（Gap）&记录锁（Recoed）

临键锁：
锁住记录+区间（左开右闭）
当sql执行按照索引进行数据的检索时,查询条件为范围查找（between and、<、>等）并有数据命中则此时SQL语句加上的锁为Next-key locks，锁住索引的记录+区间（左开右闭）
间隙锁：
锁住数据不存在的区间（左开右开）
当sql执行按照索引进行数据的检索时，查询条件的数据不存在，这时SQL语句加上的锁即为 Gap locks，锁住索引不存在的区间（左开右开）
记录锁：
锁住具体的索引项
当sql执行按照唯一性（Primary key、Unique key）索引进行数据的检索时，查询条件等值匹 配且查询的数据是存在，这时SQL语句加上的锁即为记录锁Record locks，锁住具体的索引项。

临键锁

9116eeb61557a3780c864d3cb3facda2.png
为什么InnoDB使用临键锁作为行锁的默认算法？
临键锁可以解决再RR（可重复读）事务下的幻读问题。

间隙锁

40e92ce14d6359d172771ca41f39a66a.png
Gap只在RR事务隔离级别存在

记录锁
b508ede1bd73997eba392ed5d6bc1bed.png

利用锁怎么解决事务问题？

解决脏读：
2c857f6c6c474f2a9e88a02cf8aee727.png
解决不可重复读：
8a836c7315a8512463cab26bdef1eeee.png
解决幻读：
f3d5a0d144239bd89b3a6d9eff5dbe5a.png

死锁介绍

多个并发事务，每个事务都持有锁，并且每个事务都需要再次获得锁，当两个或多个事务都持有对方需要再次获得的锁的时候就会产生死锁。

死锁的避免

  1. 类似的业务逻辑按固定的顺序访问表和行。
  2. 将大事务拆分为小事务。
  3. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
  4. 降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择。
  5. 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁（或者说是表锁）

MVCC是什么？

MVCC： Multiversion concurrency control (多版本并发控制)
普通话解释：
并发访问(读或写)数据库时，对正在事务内处理的数据做多版本的管理。以达到用来避免写操作的堵塞，从而引发读操作的并发问题。

mysql在数据行后面会追加DB_TRX_ID（数据行的版本号）和DB_ROLL_PT（删除版本号）两个字段。这两个字段对数据进行版本控制。

Undo log

52f1e59c306cf914323c17c82cf38e92.png
Undo log是什么：
undo意为撤销的意思，即为RollBack操作。
undo log指事务开始之前，在操作任何数据之前,首先将需操作的数据备份到一个地方 (Undo Log)。

UndoLog是为了实现事务的原子性而出现的产物

Undo Log实现事务原子性：
事务处理过程中如果出现了错误或者用户执行了 ROLLBACK语句,Mysql可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

UndoLog在Mysql innodb存储引擎中用来实现多版本并发控制

Undo log实现多版本并发控制：
事务未提交之前，Undo保存了未提交之前的版本数据，Undo 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

当前读和快照读

快照读：
SQL读取的数据是快照版本，也就是历史版本，普通的SELECT就是快照读。
innodb快照读，数据的读取将由 cache(原本数据) + undo(事务修改过的数据) 两部分组成
当前读：
SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改
UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE都是当前读。

Redo log

de82ded7bb040be9cbd5094360985dd8.png
Redo log是顺序io，较直接持久化的随机io要快。
Redo log是什么：
redo意为重做的意思，是对已经commit数据的恢复操作。
Redo log指事务中操作的任何数据,将最新的数据备份到一个地方 (Redo Log)。
Redo log的持久：
不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo 中。具体的落盘策略可以进行配置。

RedoLog是为了实现事务的持久性而出现的产物

Redo Log实现事务持久性：
防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

Redo log知识点：

  1. 指定Redo log 记录在{datadir}/ib_logfile1&ib_logfile2 可通过innodb_log_group_home_dir 配置指定目录存。
  2. 一旦事务成功提交且数据持久化落盘之后，此时Redo log中的对应事务数据记录就失去了意义，所以Redo log的写入是日志文件循环写入的。
  • 指定Redo log日志文件组中的数量 innodb_log_files_in_group 默认为2
  • 指定Redo log每一个日志文件最大存储量innodb_log_file_size 默认48M
  • 指定Redo log在cache/buffer中的buffer池大小innodb_log_buffer_size 默认16M
  1. Redo buffer 持久化Redo log的策略， Innodb_flush_log_at_trx_commit：
  • 取值 0
    每秒提交 Redo buffer --> Redo log OS cache -->flush cache to disk[可能丢失一秒内的事务数据]
  • 取值 1
    默认值，每次事务提交执行Redo buffer --> Redo log OS cache -->flush cache to disk [最安全，性能最差的方式]
  • 取值 2
    每次事务提交执行Redo buffer --> Redo log OS cache 再每一秒执行 ->flush cache to disk操作

配置优化

mysql服务器参数

基于参数的作用域：

  1. 全局参数
    set global autocommit = ON/OFF;
  2. 会话参数(会话参数不单独设置则会采用全局参数)
    set session autocommit = ON/OFF;
    注意：
    全局参数的设定对于已经存在的会话无法生效 会话参数的设定随着会话的销毁而失效 全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效。

寻找配置文件方法：
mysql --help 寻找配置文件的位置和加载顺序
Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
mysql --help | grep -A 1 'Default options are read from the following files in the given order'

全局文件配置

最大连接数配置 max_connections
系统句柄数配置 /etc/security/limits.conf ulimit -a
mysql句柄数配置 /usr/lib/systemd/system/mysqld.service
最大连接数配置计算：
sort_buffer_size
connection排序缓冲区大小 建议256K(默认值)-> 2M之内
当查询语句中有需要文件排序功能时，马上为connection分配配置的内存大小。 join_buffer_size
connection关联查询缓冲区大小 建议256K(默认值)-> 1M之内
当查询语句中有关联查询时，马上分配配置大小的内存用这个关联查 询，所以有可能在一个查询语句中会分配很多个关联查询缓冲区。
上述配置4000连接占用内存： 4000*(0.256M+0.256M) = 2G

Innodb_buffer_pool_size 重点：
图中的缓冲区，并不只是查询缓存。
innodb buffer/cache的大小（默认128M）
Innodb_buffer_pool
数据缓存 索引缓存 缓冲数据 内部结构
大的缓冲池可以减小多次磁盘I/O访问相同的表数据以提高性能
参考计算公式：
Innodb_buffer_pool_size = （总物理内存 - 系统运行所用 - connection 所用）* 90%

https://www.cnblogs.com/wyy123/p/6092976.html 常见配置的帖子

数据库表设计

第一范式（ 1NF）：
字段具有原子性,不可再分。 所有关系型数据库系统都满足第一范式）数据库表中的字 段都是单一属性的， 不可再分；
第二范式（ 2NF）：
要求实体的属性完全依赖于主键。 所谓完全依赖是指不能存在仅依赖主键一部分的属性， 如果存在， 那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体， 新实体与原 实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。 简而言之， 第二范式就是属性完全依赖主键。
第三范式（ 3NF）：
满足第三范式（ 3NF） 必须先满足第二范式（ 2NF）。 简而言之， 第三范式（ 3NF） 要求一个数据库表中不包含已在其它表中已包含的非主键信息。
简单一点：
1， 每一列只有一个单一的值，不可再拆分
2， 每一行都有主键能进行区分
3， 每一个表都不包含其他表已经包含的非主键信息。

充分的满足第一范式设计将为表建立太量的列
数据从磁盘到缓冲区，缓冲区脏页到磁盘进行持久的过程中，列的数量过多会导致性能下降。过多的列影响转换和持久的性能
过分的满足第三范式化造成了太多的表关联
表的关联操作将带来额外的内存和性能开销
使用innodb引擎的外键关系进行数据的完整性保证
外键表中数据的修改会导致Innodb引擎对外键约束进行检查，就带来了额外的开销

日志

bin_log是主从复制
undo_log是事务回滚
redo_log是崩溃恢复

下一篇： servlet解析→

▲