MYSQL知识点

发布于：2022-12-10 ⋅ 阅读:(1266) ⋅ 点赞:(1)

 知识点来源，推荐大家阅读

MySQL实战45讲_MySQL_数据库-极客时间

逻辑架构

重建表

alter table == recreate

optimize table t == recreate + analyze

Count操作

按照效率排序的话，count(字段) < count(主键id)  < count(1) ≈ count(*)

Order By

rowid 排序 , max_length_for_sort_data 参数控制，排序的行数据长度是否超过这个配置值

内存排序： sort_buffer,  排序算法： 优先队列（堆排序），归并排序（sort_buffer_size配置）

rand随机排序

Grant语句

权限

全局权限（新建连接会缓存当前权限位到连接对象中）

修改全局权限的时候，是同时对磁盘（mysql . user表）和内存（ 数组 acl_users ）生效的

db权限

在使用use db命令切换db的时候拿到的库权限会保存在会话变量中，修改db权限的时候，也是同时对磁盘（mysql.db表）和内存（ 数组 acl_dbs ）生效的

表权限（ 表mysql.tables_priv）和列权限（表mysql.columns_priv）

内存的hash结构为column_priv_hash ， 是会同时修改数据表，也会同步修改内存中的hash结构

flush privileges

Flush privileges命令会清空 acl_users 数组，然后从mysql.user表中读取数据重新加载，重新构造一个acl_users 数组。也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍，同样地，对于db权限、表权限和列权限，MySQL也做了这样的处理。

用grant / revoke语句来执行的话，内存和数据表本来就是保持同步更新的，正常情况下，grant命令之后，没有必要跟着执行flush privileges命令

使用场景： 直接用DML语句操作系统权限表，导致磁盘合内存权限数据不一致时

JOIN

相关系统变量：optimizer_switch,  join_buffer_size

straight_join:   straight_join 让MySQL使用固定的连接方式执行查询

inner_join:  优化器有权自己选择哪张表做为驱动表， 一般按照 “小表驱动大表”

left join :  使用left join时，左边的表不一定是驱动表：如果需要满足left join的语义，就不能把被驱动表的字段放在where条件里面做等值判断或不等值判断，必须都写在on里面

join :  join将判断条件是否全部放在on部分就没有区别了

重点掌握：内部算法原理

Simple Nested Loop Join (简称：SNLJ，嵌套循环)

1. 顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。

关联字段索引的必要性

1) 可以用上被驱动表的索引：Index Nested-Loop Join（简称：NLJ） 算法, 只用了索引，没有用join_buffer.

2) 没有使用上被驱动表的索引：Block Nested Loop  Join（简称：BNL）算法  ( 是对 Simple  Nested Loop Join的优化， 使用了join_buffer ）

1. 首先，将驱动表的数据全部读入内存join buffer中，这里join_buffer是无序数组；

2. 然后，顺序遍历被驱动表的所有行，每一行数据都跟join_buffer中的数据进行匹配，匹配成功则作为结果集的一部分返回

特殊情况:  join buffer不够大（ join_buffer_size ）: 分段加载，不过注意, 分段这样是会增加被驱动表全表扫描的次数的,  所以需要尽量减少驱动表上不必要的字段查询， 这样join buffer所能缓存的数据就越多，批量匹配的效率越高

如果使用join：应该总是使用小表做驱动表，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

Join优化

Multi-Range Read优化 （ MRR）:  将主键在（ read_rnd_buffer ）中提前排序好：磁盘的顺序读写性能比随机访问高

Batched Key Access ( BKA ) 算法:  针对Index Nested - Loop Join（简称：NLJ）算法优化，使用join_buffer

Block Nested Loop Join（简称：BNL）算法 转 BKA :  将被驱动表满足条件的数据存放到临时表中，给临时表建字段索引，利用索引优化等值条件的判断，提高效率

总结

1. BKA优化是MySQL已经内置支持的，建议你默认使用

2. BNL算法效率低，建议你都尽量转成BKA算法。优化的方向就是给被驱动表的关联字段加上索引；

3. 基于临时表的改进方案，对于能够提前过滤出小数据的join语句来说，效果还是很好的；

4. MySQL目前的版本还不支持hash join（MYSQL 8.0.18版本开始支持），但你可以配合应用端自己模拟出来，理论上效果要好于临时表的方案。

Group By

group by （默认排好序，使用 order by null 可以使其不排序）

Group by 优化方法 — group by 字段增加索引， 可以不用内部临时表 + 排序 （索引已经是有序了），MYSQL支持generated column机制：用来实现列数据的关联更新

alter table t1 add column z int generated always as(id%100), addindex(z);

Group by优化方法 — 直接排序： 可以在group by语句中加入 SQL_BIG_RESULT 这个提示来让优化器自己选择排序策略（直接走磁盘临时表B+树索引 还是 sort buffer ）

内部临时表的大小是有限制的，参数：tmp_table_size就是控制这个内存大小的，执行过程中会发现内部临时表大小到达了上限，这时候就会把内部临时表转成磁盘临时表

使用内部临时表，在临时表中创建group by字段的唯一索引，最后临时表中的数据就是结果集 （通过 tmp_table_size设置临时表的大小）

explain分析

explain结果输出格式： 传统表格形式，JSON格式，TREE格式

explain返回字段含义

explain之id

 表示多个表是以怎么样的顺序join到一起的

  • id如果相同，可以任务是一组，按从上往下顺序执行的
  • 在所有组中，id值越大哦，优先级越高，越先执行
  • id中每个值，表示一个独立查询，一个sql的独立查询数越少越好

explain之select_type

每个SELECT关键字表示一个小的查询语句，MYSQL称之为select_type关键字属性，对于同于个SELECT关键字的表来说，他们的id值是相同的。

常见值：

SIMPLE： 查询语句中不包含UNION 或者子查询的查询

PRIMARY：大查询中最左边的小查询

UNION：包含UNION的大查询中除最左边的小查询（PRIMARY）之外的子查询，根据子查询的不同类型，取值也有很多种

 .....

explain之type

针对单表的访问方法，也可称访问类型

常见值：

ALL: 全表扫描

const：使用主键或者唯一索引与常数进行等值匹配时，对单表的访问方式

ref：  使用普通二级索引或者唯一索引前缀与常量进行等值匹配时，对单表的访问方式

eq_ref： 使用主键索引或唯一索引，基本等同ref

fulltext： 使用全文索引

range:  多值范围查询

index： 当我们可以使用覆盖索引，但需要扫描全部的索引记录时

 .....

结果值从最好到最坏依次是：

system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_merge -> unique_subquery 

-> index_subquery -> range -> index -> all

比较重要的几个提取出来了（上面蓝色部分）

SQL性能优化目标： 至少要达到range 级别，要求是ref级别， 最好是const级别

explain之possible_keys和key

possible_keys： 单表查询时可能用到的索引项

key：实际用到的索引项

explain之key_len

key_len：实际使用到的索引长度(即：字节数)

帮你检查是否充分的利用上了索引，值越大越好,主要针对于联合索引，有一定的参考意义。

一般计算公式：

  • varchar(10) 变长字段且允许NULL,  key_len = 10 * ( character set：utf8=3,gbk=2,latin1=1) +1 (NULL)  + 2(变长字段)
  • varchar(10) 变长字段且不允许NULL， key_len = 10 * ( character set：utf8=3,gbk=2,latin1=1) + 2(变长字段)
  • char(10) 固定字段且允许NULL ， key_len = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
  • char(10) 固定字段且不允许NULL ， key_len = 10 * ( character set：utf8=3,gbk=2,latin1=1)

explain之ref

当使用索引列等值查询时，与索引列进行等值匹配的对象信息。

比如：只是一个常数（const）或者是某个列。

explain之rows

预估的需要读取的记录条数

explain之filtered

某个表经过搜索条件过滤后剩余记录条数的百分比

如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

explain之Extra

join操作的一些额外信息， 能更准确的理解到底如何执行给定的查询语句

常见值：

Using filesort ： 需要使用排序（内存或者文件）

Using index： 使用覆盖索引，索引即可以返回请求列

Using index condition： 通过索引初步过滤，回表过滤其他条件（索引下推，减少回表的次数）

Using temporary： 使用临时表, 内部临时表（Using temporary，系统自身创建的， 默认是内存临时表）， 比如，在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，不能有效利用索引来完成查询

Using where： 需结合key列看是否命中索引，之后再看，若extra单独出现该值，一般代表出现了除索引之外的条件过滤情况；

Using index & Using where： 使用覆盖索引；同时通过索引过滤

Using MRR： 提前将主键排好序，顺序读

Using intersect(...)： 说明准备使用Intersect索引合并的方式执行查询，括号中的`...`表示需要进行索引合并的索引名称；

Using union(...)：说明准备使用Union索引合并的方式执行查询；

Using sort_union(...)：说明准备使用Sort-Union索引合并的方式执行查询。

Using join_buffer(Block Nested-Loop join) :   使用join_buffer, 具体逻辑实现为Block Nested-Loop join

.......

幻读问题（RR隔离级别下）

声明：在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现，幻读仅专指“新插入的行”

问题：数据一致性问题 （数据库状态与从binlog恢复的数据状态不一致性）

扩展阅读： 数据库隔离级别及mysql的实现

MYSQL通过引入间隙锁来解决幻读，当存在当前读的查询时，查询过程中给数据间加了间隙锁，也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁，这样就确保了无法再插入新的记录。

间隙锁，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的

间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把binlog格式设置为row。这也是现在不少公司使用的配置组合。

死锁场景（间隙锁导致）

select…for update语句（当记录不存在时，然后执行插入该记录），多个事务并发处理这个业务逻辑时，容易在间隙锁上产生竞争，导致死锁

1. 唯一键（包括主键）冲突（冲突数据本不存在，多个事务要插入的时候，其中一个rollback了）导致多个事务同时都占用了next key lock 读锁，无法获取到写锁，而相互等待，出现死锁

2. 对同一组资源，没有尽量按照相同的顺序访问,  导致多个事务加锁的顺序不同，使得相互等待对方释放自己需要的锁资源，而出现死锁 (为什么？ 因为mysql加锁的过程是逐条逐条记录慢慢加锁的，不是一次性全部锁住)

锁的类型

全局锁

仅限在： 不支持事务的引擎才会使用（命令是Flush tables with read lock(FTWRL)， 全局锁主要用在逻辑备份过程中。对于全部是InnoDB引擎的库，建议你使用mysqldump时加上–single-transaction参数

FTWRL 与 set global readonly=true 的方式差异

  1. 在异常处理机制上有差异， 如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高
  2. readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大

表级锁

表锁： S锁（读锁）， X锁（写锁）

  • LOCK TABLES t READ
  • LOCK TABLES t WRITE 

意向锁：也是一种表锁，是Innodb支持的多粒度锁，允许行级锁和表级锁共存，由存储引擎自己维护，同时也分为意向共享锁（IS）和意向排它锁（IX）

注意点：

  • 任何意向锁之间是相互兼容的，不彼此排斥，但是除了IS和S兼容外，意向锁与S/X锁互斥
  • IX 和 IS 是表级锁，不会和行级锁发生冲突，只会和表级别X，S发生冲突

自增锁：用于表中添加了AUTO_INCREMENT属性的列的值自增不重复，可通过参数：innodb_autoinc_lock_mode = 1 控制

元数据锁（meta data lock， MDL）：在访问一个表的时候会被自动加上，不需要显式使用，是为了防止DDL和DML并发时引起冲突。

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。MDL 会直到事务提交才释放

行锁

形式：记录锁 （Record lock）, 间隙锁（Gap Lock），临键锁（Next-key Lock，记录锁+间隙锁联合），插入意向锁（Insert intention locks）

实现方式：通过锁索引（如果某列没有索引呢？ - 主键索引一定有），innodb行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

两阶段锁协议： 在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

建议： 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

注意点：

  • 间隙锁之间是相互不冲突，但是间隙锁和X锁互斥
  • 间隙锁，临键锁只能在事务级别为RR下生效
  • 插入意向锁也是一种间隙锁， 是在插入数据行之前，由 INSERT 操作设置的一种间隙锁，插入意向锁之间不互斥，插入意向锁和间隙锁之间互斥。

页锁

在页的粒度上进行锁定

等等

锁升级

每个层级的锁数量是有限制的，锁空间也是有限的，当某个层级的锁数量超过阈值时，就会进行锁升级，用更大粒度的锁替代多个更小粒度的锁

锁的存储结构

mysql 锁数据结构展示

  • lock_t
    • trx_t //锁所在事务
    • dict_index_t //锁索引
    • un_member(union)
      • lock_table_t //表锁
        • dict_table_t //所在表
      • lock_rec_t 行锁
        • table space //表空间
        • page_no //页号
        • lock bitmap //索引的那条记录加了锁
    • type_mode
      • lock_type
        • LOCK_TABLE
        • LOCK_REC
      • lock_mode
        • LOCK_NONE
        • LOCK_IS
        • LOCK_IX
        • LOCK_S
        • LOCK_X
        • LOCK_AUTO_INC
      • rec_lock_type
        • LOCK_ORDINARY // next-key标识，0，所以默认就是next-key
        • LOCK_GAP // gap标识
        • LOCK_REC_NOT_GAP // record标识
        • LOCK_INSERT_INTENTION // insert intention标识
      • is_waiting // 锁等待，表示事务是否获取到了锁

SQL语句加锁规则

前提说明

MySQL后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即5.x系列 <=5.7.24，8.0系列 <=8.0.13

两个原则，两个优化，一个bug

两原则

原则1：加锁的基本单位是next-keylock。next-keylock是前开后闭区间，（两阶段执行：先加间隙锁，后加行锁）

原则2：查找过程中访问到的对象（索引）才会加锁

注意：使用覆盖索引的时候

1.主键索引如果没有去访问，就不会被加锁；

2.Lock in share mode（读锁） 是只锁覆盖索引，但是如果是for update（写锁） 就不一样了。执行for update时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁

两优化

优化1：索引上的等值查询，给唯一索引 （注：主键索引也是唯一索引）加锁的时候，next-keylock退化为行锁。

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-keylock退化为间隙锁。

一个bug

一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。（高版本的mysql基本修复了）

加锁强化（用动态的观点看加锁规则）

锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问,  否则容易出现死锁。

所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的（ 如果左边的记录被另一个事务删除的话， 锁的范围就突然“变大”了）

临时表

概念： create temporary table， 可以使用各种引擎类型

临时表只能被创建它的session访问，对其他线程不可见； 临时表可以与普通表同名； session内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表；show tables命令不显示临时表；

不能用rename修改临时表的表名： 执行rename table语句的时候，要求按照“库名/表名.frm”的规则去磁盘找文件，但是临时表在磁盘上的frm文件是放在tmpdir目录下的，并且文件名的规则是“#sql{进程id}_{线程id}_序列号.frm”，因此会报“找不到文件名”的错误

使用场景

分库分表系统的跨库查询； join 优化；

比较： join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表结构

区别：内存临时表

概念： create temporary table engine=memory;

内存表

概念： create table…engine=memory， 指的是使用Memory引擎的表

数据组织结构： Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，称之为堆组织表； 索引是支持hash索引

劣势： 1. 锁粒度问题， 内存表不支持行锁，只支持表锁； 2. 数据持久性问题

事务

概念：事务的前提： ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

当有多个事务同时执行的时候，就可能出现**脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）**的问题，为了解决这些问题，就有了“隔离级别”的概念。

区别：MariaDB有两种支持事务的存储引擎，分别是InnoDB和Sequence，而MYSQL只有InnoDB。

隔离级别

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

  • 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
  • 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
  • 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  • 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

参数： transaction_isolation (after 8.0)  before using tx_isolation

事务并行操作例子：

不同隔离级别下获取的数据结果分析：

  • 若隔离级别是“读未提交”， 则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结果已经被 A 看到了。因此，V2、V3 也都是 2。
  • 若隔离级别是“读提交”，则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看到。所以， V3 的值也是 2。
  • 若隔离级别是“可重复读”，则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的（底层原理：一致性视图）。
  • 若隔离级别是“串行化”，则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务 A 提交后，事务 B 才可以继续执行。所以从 A 的角度看， V1、V2 值是 1，V3 的值是 2

一致性读视图

只支持：RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别

实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

该字段数据的当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC），当没有事务再需要用到这些回滚日志时，回滚日志会被删除，也就是当系统里没有比这个回滚日志更早的 read-view 的时候。

MVCC 多版本并发控制，一致性读视图的底层具体无锁实现，InnoDB 利用了“所有数据都有多个版本”的这个特性，实现了“秒级创建快照”的能力，不是简单的拷贝整个库的数据（这样效率也太低了）

在MVCC中有两种读：

  • 快照读(一致性读）：读取的是当前事务的可见版本。最简单的select操作；

  • 当前读：读取的是当前版本。显示带lock的读操作，Insert/Update/Delete操作, 只有在对表中的记录做改动时（INSERT、DELETE、UPDATE）才会为事务分配事务id。

当前读概念： 读到的数据始终是最新的数据版本,  必须加锁，因此可能需要等待其他session当前读的锁的释放。

以下情况都是当前读：

  • select ... lock in share mode （加锁类型：共享锁S）
  • Select … for update （加锁类型：排它锁X）
  • insert
  • update
  • delete

视图在什么时候生成

RR的核心：就是一致性读（consistent read）

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成Read View的时机不同：

  • READ COMMITTED —— 每次读取数据前都生成一个ReadView，查询只承认在事务启动前就已经提交完成的数据，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。事务启动时的视图可以认为是静态的，不受其他事务更新的影响

  • REPEATABLE READ —— 在第一次读取数据时生成一个ReadView，查询只承认在语句启动前就已经提交完成的数据，这个视图是在每个 SQL 语句开始执行的时候创建的

注意：事务的启动时机概念理解
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。
如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令

在视图创建的同时，事务被分为： 已提交事务集合，未提交事务集合，未开始事务集合

一致性视图（read view），其实由三部分组成：

  • m_ids：当前活跃的事务id列表，已经启动了但是未提交的事务XID集合。如：[2,3]

  • min_trx_id：生成read view时当前活跃事务中最小事务id （低水位）

  • max_trx_id：生成read view时，即将分配给下一个事务id值  （高水位）

可见性分析

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。

“活跃”指的就是，启动了但还没提交，数据版本的可见性规则，就是基于数据的 row trx_id 和这个一致性视图（视图数组和高水位，组成了当前事务的一致性视图）的对比结果得到的。

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下3种可能：

  1. 如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；

  2. 如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；

  3. 如果落在黄色部分，那就包括两种情况

  • a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务（是排除当前事务本身的）生成的，不可见；
  • b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

具体规则可总结如下：

  1. 版本未提交，不可见；（排除了当前事务本身）

  2. 版本已提交，但是是在视图创建后提交的，不可见；

  3. 版本已提交，而且是在视图创建前提交的，可见；

 推荐阅读： 数据库事务(结合MYSQL）， 数据库隔离级别及mysql的实现

索引

相关系统变量：optimizer_switch

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的,

  • InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。
  • 每一个索引在 InnoDB 里面对应一棵 B+ 树,
  • B+ 树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。
  • B+树的叶子节点是page （页），一个页里面可以存多个行
  • page （页）内部有个有序数组， 通过二分法去定位具体的行数据

索引类型分为主键索引和非主键索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

索引页，数据页

索引下推(index condition pushdown, ICP) ： 不符合最左前缀索引规则时，最大程度利用索引字段先判断，过滤不满足条件记录，减少回表次数

索引的操作

对索引字段做函数操作，可能破坏索引值的有序性，优化器就决定放弃走树搜索功能

  • 条件字段做函数操作（字段的值做函数操作还是会走树搜索的哦） f(col)  和 col = f (v)  的区别
  • 隐式类型转换
  • 隐式字符编码转换

索引搜索： 就是“找到第一个值，然后向左或向右遍历”，

  • order by desc就是要用最大的值来找第一个；
  • order by就是要用做小的值来找第一个；

索引的组织方式：

主键索引

二级索引

索引的作用

唯一索引和普通二级索引的区别，在对change buffer的使用上有不同，唯一索引无法使用change buffer来缓存对数据的更新，必须将内存与磁盘数据比较确定唯一性才行，而普通索引可以。

索引相关问题：

有这么一个表，表结构定义如下，既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？


CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

// 为了这两个查询模式，这两个索引（“ca”，“cb”）是否都是必须的？为什么呢？
select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

分析：

  • 主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 无序
  • 索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键（主键部分只有b），这个跟索引 c 的数据是一模一样的
  • 索引 cb 的组织是先按 c 排序，在按 b 排序，同时记录主键（主键部分只有a）

所以，结论是 ca 可以去掉，cb 需要保留。

自适应hash索引

Innodb不支持hash索引，但是存在自适应hash索引

相关变量： innodb_adaptive_hash_index， innodb_adaptive_hash_index_parts

innodb_adaptive_hash_index ：on  表示自适应hash索引开启

innodb_adaptive_hash_index_parts： 8 表示分区的数量

自增主键

为什么主键是递增的，但是不保证连续？

  1. 唯一键冲突是导致自增主键id不连续的第一种原因；
  2. 事务回滚也会产生类似的现象，这就是第二种原因；
  3. 针对批量插入数据，包含的语句类型是 insert … select、replace … select 和 load  [local]  data 语句， MySQL有一个批量申请自增id的策略（每次申请的数量是上次的两倍）， 这样可能导致最后申请的id没有用完的情况，但是申请了的id是不能回退的（回退对性能损耗大）

主从复制

原理

MySQL 集群的主从复制过程分 3 个阶段

  • 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。

  • 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到中继日志（relay log）中。

  • 回放 Binlog：回放 binlog，并更新存储数据。

复制延迟

结果：导致主从数据不一致了

主从复制方式

按照数据一致性从弱到强来划分，有以下复制方式，分别是：

  • 异步复制：事务线程完全不等待从库的复制成功响应。

  • 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行（通过参数： rpl_semi_sync_master_wait_for_slave_count = 1控制）。比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。

  •  组复制（MGR：MYSQL Group Replication）： 基于分布式一致性算法Paxos来实现

  • 同步复制：事务线程要等待所有从库的复制成功响应。

日志模块

MYSQL日志使用的是一种WAL ，全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘， InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。 redo log 是 InnoDB 引擎特有的日志

Server 层也有自己的日志，称为 binlog（归档日志），

这两种日志有以下三点不同。

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的

redo log 的写入拆成了两个步骤：prepare 和 commit，这就是两阶段提交

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

 推荐阅读：MYSQL日志模块

性能优化

索引的优化

  • 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引

  • 尽量使用短索引，如果可以，应该制定一个前缀长度

  • 对于经常在where子句使用的列，最好设置索引，这样会加快查找速度

  • 对于有多个列where或者order by子句的，应该建立复合索引

  • 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引

  • 尽量不要在列上进行运算（函数操作和表达式操作）

  • 尽量不要使用not in和<>操作

SQL语句的优化

  • 查询时，能不要*就不用*，尽量写全字段名

  • 大部分情况连接效率远大于子查询

  • 多使用explain和profile分析查询语句

  • 查看慢查询日志，找出执行时间长的sql语句优化

  • 多表连接时，尽量小表驱动大表，即小表 join 大表

  • 在千万级分页时使用limit

  • 对于经常使用的查询，可以开启缓存

表结构的优化

  • 表的字段尽可能用NOT NULL

  • 字段长度固定的表查询会更快

  • 把数据库的大表按时间或一些标志分成小表

  • 将表拆分（数据表拆分：主要就是垂直拆分和水平拆分。水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。）

锁的优化策略

  • 读写分离
  • 分段加锁
  • 减少锁持有的时间
  • 多个线程尽量以相同的顺序去获取资源
  • 不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。
本文含有隐藏内容，请 开通VIP 后查看

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布