• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

Mysql 学习笔记 2

互联网 diligentman 2小时前 1次浏览

目录

      • 22,关于 MySQL 索引
        • 1,MySQL 索引的种类
        • 2,什么时候使用索引
        • 3,如何创建索引
        • 4,如何设计索引
        • 5,索引的底层数据结构
          • 1,关于平衡二叉树
          • 2,关于 B 树
          • 3,关于 B+ 树
          • 4,关于 Hash 索引
      • 23,数据库中的存储结构
        • 1,页结构
        • 2,从数据页的角度看 B+ 树
        • 3,B+ 树如何检索记录
      • 24,数据库缓冲池
        • 1,缓冲池原理
        • 2,查看与修改缓冲池大小
        • 3,SQL 的查询成本
      • 25,慢查询
      • 26,MySQL 中的锁
        • 1,锁的划分
          • 1.1,按照锁的粒度划分
          • 1.2,从数据库管理的角度对锁进行划分
          • 1.3,从程序员的视角来看锁
        • 2,锁升级
        • 3,锁操作命令
        • 4,意向锁
        • 5,死锁
      • 27,MVCC 多版本并发控制
        • 1,MVCC 的作用
        • 2,MVCC 的实现原理
        • 3,什么是快照读
        • 4,什么是当前读
        • 5,记录的多个版本包括哪些内容
        • 6,Read View 原理
      • 28,定位数据库 SQL 性能问题
        • 1,数据库优化步骤
        • 2,慢查询
        • 3,分析慢查询日志
        • 4,EXPLAIN 命令
        • 5,SHOW PROFILE 命令
      • 29,MySQL 主从同步
        • 1,主从同步的作用
        • 2,主从同步的原理
        • 3,主从同步的数据一致性问题
        • 4,如何解决数据一致性问题
      • 30,InnoDB 存储引擎的表空间

22,关于 MySQL 索引

索引的本质目的是快速定位想要查找的数据。

1,MySQL 索引的种类

MySQL 中的索引分为:

  • 普通索引:没有任何约束,主要用于提高查询效率。
  • 唯一索引:在普通索引的基础上增加了数据唯一性的约束,一张数据表里可以有多个唯一索引。
  • 主键索引:在唯一索引的基础上增加了不为空的约束,也就是 NOT NULL+UNIQUE,一张表里最多只有一个主键索引。
  • 全文索引:使用的不多,MySQL 自带的全文索引只支持英文。
    • 通常可以采用专门的全文搜索引擎,比如 ElasticSearch。

单一索引与联合索引:

  • 单一索引:索引列为一列;
  • 联合索引:多个列组合在一起创建的索引。
    • 创建联合索引时,需要注意索引的顺序,因为联合索引 (x, y, z)(z, y, x) 在使用的时候效率可能会存在差别。
    • 联合索引存在最左匹配原则,也就是按照最左优先的方式进行索引的匹配。
      • 比如联合索引(x, y, z),如果查询条件是 WHERE x=1 AND y=2 AND z=3,就可以匹配上联合索引;
      • 如果查询条件是 WHERE y=2,就无法匹配上联合索引。
      • SQL条件语句中的字段顺序不重要,因为在逻辑查询优化阶段会自动进行查询重写。

2,什么时候使用索引

不需要创建索引的情况:

  • 表中的数据比较少的情况下,比如不到 1000 行;
  • 表中的数据重复度大,比如高于 10% 的时候。

3,如何创建索引

参照这里。

4,如何设计索引

  • 首先一张表的索引个数不宜过多,否则一条记录的增加和修改,会因为过多的索引造成额外的负担。
    • 针对这个情况,当你需要新建索引的时候,首先考虑在原有的索引片上增加索引,也就是采用复合索引的方式,而不是新建一个新的索引。
    • 另外,我们可以定期检查索引的使用情况,对于很少使用到的索引可以及时删除,从而减少索引数量。
  • 其次,在索引片中,也需要控制索引列的数量,通常情况下我们将 WHERE 里的条件列添加到索引中,而 SELECT 中的非条件列则不需要添加。除非 SELECT 中的非条件列数少,并且该字段会经常使用到。
  • 另外,单列索引和复合索引的长度也需要控制,在 MySQL InnoDB 中,系统默认单个索引长度最大为 767 bytes,如果单列索引长度超过了这个限制,就会取前缀索引,也就是取前 255 字符。这实际上也是告诉我们,字符列会占用较大的空间,在数据表设计的时候,尽量采用数值类型替代字符类型,尽量避免用字符类型做主键,同时针对字符字段最好只建前缀索引。

5,索引的底层数据结构

MySQL 的索引存储在磁盘(速度很慢)上,读取索引时与磁盘的交互越少(重点在于将树的高度降低),性能也就越高。

1,关于平衡二叉树

平衡二叉树不适合作为 MySQL 索引,主要是由于树的深度比较深,从而磁盘交互就比较多。常见的平衡二叉树有:

  • 平衡二叉搜索树:查询时间复杂度O(log2n),树的深度为 O(log2n)
  • 红黑树

Mysql 学习笔记 2

2,关于 B 树

B 树是平衡的多叉树,它的高度远小于平衡二叉树。B 树的一个节点可以存储 M 个子节点,M 成为 B 树的阶。在文件系统和数据库系统中的索引结构经常采用 B 树来实现。

Mysql 学习笔记 2

3,关于 B+ 树

B+ 树是B 树的改进版,通常用在数据库中。

B+ 树的优点:

  • B+ 树查询效率更稳定。因为 B+ 树每次只有访问到叶子节点才能找到对应的数据,而在 B 树中,非叶子节点也会存储数据,这样就会造成查询效率不稳定的情况,有时候访问到了非叶子节点就可以找到关键字,而有时需要访问到叶子节点才能找到关键字。
  • B+ 树的查询效率更高,这是因为通常 B+ 树比 B 树更矮胖(阶数更大,深度更低),查询所需要的磁盘 I/O 也会更少。同样的磁盘页大小,B+ 树可以存储更多的节点关键字。
  • 在查询范围上,B+ 树的效率也比 B 树高。这是因为所有关键字都出现在 B+ 树的叶子节点中,并通过有序链表进行了链接。而在 B 树中则需要通过中序遍历才能完成查询范围的查找,效率要低很多。

Mysql 学习笔记 2

4,关于 Hash 索引

Mysql 学习笔记 2

Hash 索引与B+ 树的区别:

  • Hash 索引不能进行范围查询,而 B+ 树可以。
    • 因为 Hash 索引指向的数据是无序的,而 B+ 树的叶子节点是个有序的链表。
  • Hash 索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用),而 B+ 树可以。
    • 对于联合索引来说,Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值,所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时,联合索引无法被利用。
  • Hash 索引不支持 ORDER BY 排序
    • 因为 Hash 索引指向的数据是无序的,因此无法起到排序优化的作用,而 B+ 树索引数据是有序的,可以起到对该字段 ORDER BY 排序优化的作用。
    • 同理,我们也无法用 Hash 索引进行模糊查询,而 B+ 树可以使用 LIKE 进行模糊查询。

23,数据库中的存储结构

记录是按照行来存储的,但是数据库的读取并不以行为单位,否则一次读取(也就是一次 I/O 操作)只能处理一行数据,效率会非常低。因此在数据库中,不论读一行,还是读多行,都是将这些行所在的页进行加载。也就是说,数据库管理存储空间的基本单位是页(Page)

MySQL 中表空间,段,区,页,行的关系:
Mysql 学习笔记 2

  • :在 InnoDB 存储引擎中,一个区会分配 64 个连续的页InnoDB 中的页大小默认是 16KB,所以一个区的大小是 64*16KB=1MB。
  • :段由一个或多个区组成,段中不要求区与区之间是相邻的
    • 不同类型的数据库对象以不同的段形式存在。
    • 当我们创建数据表、索引的时候,就会相应创建对应的段,比如创建一张表时会创建一个表段,创建一个索引时会创建一个索引段。
  • 表空间:是一个逻辑容器,一个表空间可以有一个或多个段,但一个段只能属于一个表空间。
    • 数据库由一个或多个表空间组成;
    • 表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。
    • InnoDB 中存在两种表空间的类型:共享表空间和独立表空间。
      • 共享表空间:意味着多张表共用一个表空间。
      • 独立表空间:意味着每张表有一个独立的表空间,也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。

查看 InnoDB 的表空间类型:

mysql > show variables like 'innodb_file_per_table';

Mysql 学习笔记 2
innodb_file_per_table=ON,这意味着每张表都会单独保存为一个.ibd 文件。

1,页结构

查看页的大小:

mysql> show variables like '%innodb_page_size%';

Mysql 学习笔记 2

页结构的示意图(包括 7 个部分):

Mysql 学习笔记 2
Mysql 学习笔记 2

页结构的变化:

Mysql 学习笔记 2

2,从数据页的角度看 B+ 树

一棵 B+ 树按照节点类型可以分成两部分:

  • 叶子节点,B+ 树最底层的节点,用于存储行记录。
  • 非叶子节点,用于存储索引键和页面指针,并不存储行记录本身。

Mysql 学习笔记 2

在一棵 B+ 树中,每个节点都是一个页,每次新建节点的时候,就会申请一个页空间。同一层上的节点之间,通过页的结构构成一个双向的链表(页文件头中的两个指针字段)。

  • 非叶子节点,包括了多个索引行,每个索引行里存储索引键和指向下一层页面的页面指针。
  • 叶子节点,存储了关键字和行记录,在节点内部(也就是页结构的内部)记录之间是一个单向的链表,但是对记录进行查找,则可以通过页目录采用二分查找的方式来进行。

3,B+ 树如何检索记录

B+ 树的索引查询行记录,首先是从 B+ 树的根开始,逐层检索,直到找到叶子节点,也就是找到对应的数据页为止,将数据页加载到内存中,页目录中的槽(slot)采用二分查找的方式先找到一个粗略的记录分组,然后再在分组中通过链表遍历的方式查找记录。

虽然我们想要查找的,只是一行记录,但是对于磁盘 I/O 来说却需要加载一页的信息,因为页是最小的存储单位。

24,数据库缓冲池

数据库缓冲池用于加快数据的读写性能。

1,缓冲池原理

对于读操作:

  • 缓冲池管理器会尽量将经常使用的数据保存起来;
  • 在数据库进行页面读操作的时候,首先会判断该页面是否在缓冲池中:
    • 如果存在就直接读取;
    • 如果不存在,就会通过内存或磁盘将页面存放到缓冲池中再进行读取。
      • 内存读取:如果数据在内存中,会直接从内存中读取,所需时间约 1ms
      • 磁盘读取:如果数据不在内存中,会从磁盘读取;
        • 随机读取:如果需要查找,时间基本约需 10ms
        • 顺序读取:如果一个磁盘的吞吐量是 40MB/S,那么对于一个 16KB 大小的页来说,一次可以顺序读取 2560(40MB/16KB)个页,相当于一个页的读取时间为 0.4ms

对于写操作:

  • 当我们对数据库中的记录进行修改的时候,首先会修改缓冲池中页里面的记录信息,然后数据库会以一定的频率刷新到磁盘上。
  • 注意并不是每次发生更新操作,都会立刻进行磁盘回写。缓冲池会采用一种叫做 checkpoint 的机制将数据回写到磁盘上,这样做的好处就是提升了数据库的整体性能。
    • 当缓冲池不够用时,需要释放掉一些不常用的页,就可以采用强行采用 checkpoint 的方式,将不常用的脏页回写到磁盘上,然后再从缓冲池中将这些页释放掉。
    • 脏页(dirty page)指的是缓冲池中被修改过的页,与磁盘上的数据页不一致。

2,查看与修改缓冲池大小

mysql > show variables like 'innodb_buffer_pool_size';

Mysql 学习笔记 2

可以看到 InnoDB 的缓冲池大小只有 8388608/1024/1024=8MB,我们可以修改缓冲池大小为 128MB,方法如下:

mysql > set global innodb_buffer_pool_size = 134217728;

在 InnoDB 存储引擎中,可以同时开启多个缓冲池,查看缓冲池的个数:

mysql > show variables like 'innodb_buffer_pool_instances';

Mysql 学习笔记 2
可以看到当前只有一个缓冲池。

说明:

  • 实际上 innodb_buffer_pool_instances 默认情况下为 8,为什么只显示只有一个呢?
  • 原因是,如果想要开启多个缓冲池,首先需要将 innodb_buffer_pool_size 参数设置为大于等于 1GB,这时 innodb_buffer_pool_instances 才会大于 1。

你可以在 MySQL 的配置文件中对 innodb_buffer_pool_size 进行设置,大于等于 1GB,然后再针对 innodb_buffer_pool_instances 参数进行修改。

3,SQL 的查询成本

可以在执行完一条 SQL 语句后,通过查看当前会话中的 last_query_cost 变量值来得到当前查询的成本。这个查询成本对应的是 SQL 语句所需要读取的页的数量

mysql> SHOW STATUS LIKE 'last_query_cost';

例 1:
Mysql 学习笔记 2
例 2:
Mysql 学习笔记 2

需要说明的是,如果 last_query_cost 的值比较大,并一定意味着 SQL 的查询时间较长;因为如果是顺序读取的方式将页面一次性加载到缓冲池中,这是页的读取速度是非常快。

25,慢查询

参考这里。

26,MySQL 中的锁

事务有 4 大隔离级别,这些隔离级别的实现都是通过锁来完成的。

加锁的目的是为了,在多线程同时操作一个数据的时候,保证数据的一致性

1,锁的划分

数据库中的锁有以下三种划分方式:

  • 按照锁的粒度划分
  • 从数据库管理的角度对锁进行划分
  • 程序员的视角来看锁
1.1,按照锁的粒度划分

可分为 5 种:

  • 行锁:按照行的粒度对数据进行锁定。锁定力度小,发生锁冲突概率低,可以实现的并发度高,但是对于锁的开销比较大,加锁会比较慢,容易出现死锁情况。InnoDB 三种行锁的方式:
    • 记录锁:针对单个行记录添加锁。
    • 间隙锁(Gap Locking):可以锁住一个范围(索引之间的空隙),但不包括记录本身。采用间隙锁的方式可以防止幻读情况的产生。
    • Next-Key 锁:可以锁住一个范围,同时锁定记录本身,相当于间隙锁 + 记录锁,可以解决幻读的问题。
  • 页锁:在页的粒度上进行锁定,锁定的数据资源比行锁要多,因为一个页中可以有多个行记录。当我们使用页锁的时候,会出现数据浪费的现象,但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间,会出现死锁。锁定粒度介于表锁和行锁之间,并发度一般。
  • 表锁:对数据表进行锁定,锁定粒度很大,同时发生锁冲突的概率也会较高,数据访问的并发度低。不过好处在于对锁的使用开销小,加锁会很快。
  • 区锁
  • 数据库锁

不同的数据库和存储引擎支持的锁粒度不同:

Mysql 学习笔记 2

1.2,从数据库管理的角度对锁进行划分

常见的有以下 2 种:

  • 共享锁:也叫读锁或 S 锁。共享锁锁定的资源可以被其他用户读取,但不能修改。在进行SELECT的时候,会将对象进行共享锁锁定,当数据读取完毕之后,就会释放共享锁,这样就可以保证数据在读取时不被修改。
  • 排它锁:也叫独占锁、写锁或 X 锁。排它锁锁定的数据只允许进行锁定操作的事务使用,其他事务无法对已锁定的数据进行查询或修改
1.3,从程序员的视角来看锁

可以将锁分成:

  • 乐观锁:乐观锁认为对同一数据的并发操作不会总发生,属于小概率事件,不用每次都对数据上锁,也就是不采用数据库自身的锁机制,而是通过程序来实现。在程序上,一般可以采用版本号机制或者时间戳机制实现。
    • 乐观锁的版本号机制:在表中设计一个版本字段 version,第一次读的时候,会获取 version 字段的取值。然后对数据进行更新或删除操作时,会执行 UPDATE ... SET version=version+1 WHERE version=version。此时如果已经有事务对这条数据进行了更改,修改就不会成功。
    • 乐观锁的时间戳机制:时间戳和版本号机制一样,也是在更新提交的时候,将当前数据的时间戳和更新之前取得的时间戳进行比较,如果两者一致则更新成功,否则就是版本冲突。
  • 悲观锁:对数据被其他事务的修改持保守态度,会通过数据库自身的锁机制来实现,从而保证数据操作的排它性。

Mysql 学习笔记 2

乐观锁与悲观锁的适用场景:

  • 乐观锁:适合读多写少的场景,它的优点在于不存在死锁问题。
  • 悲观锁:适合写多读少的场景,因为写的操作具有排它性。采用悲观锁的方式,可以在数据库层面阻止其他事务对该数据的操作权限,防止读 - 写写 - 写的冲突。

2,锁升级

每个层级的锁数量是有限制的,因为锁会占用内存空间,锁空间的大小是有限的。

当某个层级的锁数量超过了这个层级的阈值时,就会进行锁升级

锁升级就是用更大粒度的锁替代多个更小粒度的锁,比如 InnoDB 中行锁升级为表锁,这样做的好处是占用的锁空间降低了,但同时数据的并发度也下降了。

3,锁操作命令

共享锁操作:

# 给表加共享锁
LOCK TABLE tableName READ;

# 当对数据表加上共享锁的时候,该数据表就变成了只读模式,此时想要更新表中的数据,比如:
UPDATE tableName SET product_id = 10002 WHERE user_id = 912178;

# 系统会做出如下提示:
ERROR 1099 (HY000): Table 'product_comment' was locked with a READ lock and can't be updated

# 解除共享锁,不需要参数
UNLOCK TABLE;

# 给某一行加共享锁,可以像下面这样:
SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE user_id = 912178 LOCK IN SHARE MODE;

排它锁操作:

# 给表加排它锁
LOCK TABLE tableName WRITE;

# 这时只有获得排它锁的事务可以对 tableName 进行查询或修改,
# 其他事务如果想要在 tableName 表上查询数据,则需要等待。

# 在某个数据行上添加排它锁,则写成如下这样:
SELECT comment_id, product_id, comment_text, user_id FROM product_comment WHERE user_id = 912178 FOR UPDATE;

# 释放排它锁
UNLOCK TABLE;

另外,当我们对数据进行更新的时候,也就是 INSERTDELETE 或者 UPDATE 的时候,数据库也会自动使用排它锁,防止其他事务对该数据行进行操作

4,意向锁

当我们想要获取某个数据表的排它锁的时候,需要先看下这张数据表有没有上了排它锁。如果这个数据表中的某个数据行被上了行锁,我们就无法获取排它锁。这时需要对数据表中的行逐一排查,检查是否有行锁,如果没有,才可以获取这张数据表的排它锁。

这个过程有些麻烦,这里就需要用到意向锁

意向锁(Intent Lock),简单来说就是给更大一级别的空间示意里面是否已经上过锁。举个例子,你可以给整个房子设置一个标识,告诉它里面有人,即使你只是获取了房子中某一个房间的锁。这样其他人如果想要获取整个房子的控制权,只需要看这个房子的标识即可,不需要再对房子中的每个房间进行查找。

返回数据表的场景,如果我们给某一行数据加上了排它锁,数据库会自动给更大一级的空间,比如数据页或数据表加上意向锁,告诉其他人这个数据页或数据表已经有人上过排它锁了,这样当其他人想要获取数据表排它锁的时候,只需要了解是否有人已经获取了这个数据表的意向排他锁即可

如果事务想要获得数据表中某些记录的共享锁,就需要在数据表上添加意向共享锁。同理,事务想要获得数据表中某些记录的排他锁,就需要在数据表上添加意向排他锁。这时,意向锁会告诉其他事务已经有人锁定了表中的某些记录,不能对整个表进行全表扫描。

5,死锁

死锁就是多个事务在执行过程中,因为竞争某个相同的资源而造成阻塞的现象。

如何避免死锁:

  • 如果事务涉及多个表,操作比较复杂,那么可以尽量一次锁定所有的资源,而不是逐步来获取,这样可以减少死锁发生的概率;
  • 如果事务需要更新数据表中的大部分数据,数据表又比较大,这时可以采用锁升级的方式,比如将行级锁升级为表级锁,从而减少死锁产生的概率;
  • 不同事务并发读写多张数据表,可以约定访问表的顺序,采用相同的顺序降低死锁发生的概率。
  • 采用乐观锁的方式

27,MVCC 多版本并发控制

MVCC 就是采用乐观锁思想的一种实现。

1,MVCC 的作用

数据库有四种隔离方式,前三种都存在一定的问题,只有串行化不存在问题,但是串行化的并发性能最低。

Mysql 学习笔记 2

MVCC 的存在就是采用乐观锁的方式,即能解决各种读的问题,又不影响数据库的并发性能;它可以在大多数情况下替代行级锁,降低系统的开销

MVCC 可以解决以下问题:

  • 读写之间阻塞的问题,通过 MVCC 可以让读写互相不阻塞,即读不阻塞写,写不阻塞读,这样就可以提升事务并发处理能力。
  • 降低了死锁的概率。这是因为 MVCC 采用了乐观锁的方式,读取数据时并不需要加锁,对于写操作,也只锁定必要的行。
  • 解决一致性读的问题。一致性读也被称为快照读,当我们查询数据库在某个时间点的快照时,只能看到这个时间点之前事务提交更新的结果,而不能看到这个时间点之后事务提交的更新结果。

在可重复读的情况下,InnoDB 可以通过 Next-Key 锁 + MVCC 来解决幻读问题。

2,MVCC 的实现原理

MVCC 是通过数据行的多个版本管理来实现数据库的并发控制,简单来说就是保存数据的历史版本。这样我们就可以通过比较版本号决定数据是否显示出来,从而达到读取数据的时候不需要加锁也可以保证事务的隔离效果。

MVCC 的核心是 Undo Log + Read View

  • “MV” 是通过 Undo Log 来保存数据的历史版本,实现多版本的管理
  • “CC” 是通过 Read View 来实现管理,通过 Read View 原则来决定数据是否显示。同时针对不同的隔离级别,Read View 的生成策略不同,也就实现了不同的隔离级别。

MVCC 是一种机制,MySQL、Oracle、SQL Server 和 PostgreSQL 的实现方式均有不同。

3,什么是快照读

快照读读取的是快照数据。不加锁的 SELECT 或者说普通的 SELECT 都属于快照读,比如:

SELECT * FROM tableName WHERE ...

4,什么是当前读

当前读就是读取最新数据,而不是历史版本的数据。加锁的 SELECT,或者对数据进行增删改都会进行当前读,比如:

# 加锁的 select
SELECT * FROM player LOCK IN SHARE MODE;

# 相当于加锁的 select
SELECT * FROM player FOR UPDATE;

# Insert 操作
INSERT INTO player values ...

# Update 操作
UPDATE player SET ...

# Delete 操作
DELETE FROM player WHERE ...

5,记录的多个版本包括哪些内容

在 InnoDB 中,MVCC 是通过 Undo Log + Read View 进行数据读取,Undo Log 保存了历史快照,而 Read View 规则帮我们判断当前版本的数据是否可见。

一个记录的多个版本包括以下:

  • 事务版本号:每开启一个事务,会从数据库中获得一个事务 ID(事务版本号),这个事务 ID 是自增长的,通过 ID 大小,可以判断事务的时间顺序。
  • 行记录中的隐藏列:InnoDB 的叶子段存储了数据页,数据页中保存了行记录,行记录中有一些重要的隐藏字段:
    • b_row_id:隐藏的行 ID,用来生成默认聚集索引。如果我们创建数据表的时候没有指定聚集索引,这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚集索引的方式可以提升数据的查找效率。
    • db_trx_id:操作这个数据的事务 ID,也就是最后一个对该数据进行插入或更新的事务 ID。
    • db_roll_ptr:回滚指针,也就是指向这个记录的 Undo Log 信息。
  • Undo Log:InnoDB 将行记录快照保存在了 Undo Log 里,我们可以在回滚段中找到它们。

Mysql 学习笔记 2

6,Read View 原理

Mysql 学习笔记 2

28,定位数据库 SQL 性能问题

1,数据库优化步骤

Mysql 学习笔记 2

2,慢查询

查看慢查询是否已经开启:

mysql > show variables like '%slow_query_log';

Mysql 学习笔记 2

打开慢查询:

mysql > set global slow_query_log='ON';

查看慢查询日志是否开启,以及慢查询日志文件的位置:

mysql > show variables like '%slow_query_log%';

Mysql 学习笔记 2

查看慢查询的时间阈值:

mysql > show variables like '%long_query_time%';

Mysql 学习笔记 2

设置慢查询时间阈值:

mysql > set global long_query_time = 3;

Mysql 学习笔记 2

3,分析慢查询日志

使用 MySQL 自带的 mysqldumpslow 工具统计慢查询日志(这个工具是个 Perl 脚本,需要先安装好 Perl)。

mysqldumpslow 命令的具体参数如下:

  • -s:采用 order 排序的方式,排序方式可以有以下几种。分别是:
    • c(访问次数)
    • t(查询时间)
    • l(锁定时间)
    • r(返回记录)
    • ac(平均查询次数)
    • al(平均锁定时间)
    • ar(平均返回记录数)
    • at(平均查询时间),为默认排序方式。
  • -t:返回前 N 条数据 。
  • -g:后面可以是正则表达式,对大小写不敏感。

比如要按照查询时间排序,查看前两条 SQL 语句:

perl mysqldumpslow.pl -s t -t 2 "C:ProgramDataMySQLMySQL Server 8.0DataDESKTOP-4BK02RP-slow.log"

Mysql 学习笔记 2

4,EXPLAIN 命令

当定位到了查询慢的 SQL 之后,就可以使用 EXPLAIN 工具做针对性的分析:

EXPLAIN SELECT comment_id, product_id, comment_text, product_comment.user_id, user_name 
FROM product_comment 
JOIN user on product_comment.user_id = user.user_id 

结果如下:
Mysql 学习笔记 2

SQL 执行的顺序是根据 id 从大到小执行的,也就是 id 越大越先执行,当 id 相同时,从上到下执行。

数据表的访问类型所对应的 type 列是比较关键的信息,type 可能有以下取值:

Mysql 学习笔记 2

在这些情况里:

  • all 是最坏的情况,表示全表扫描。
  • indexall 差不多,只不过 index 对索引表进行全扫描,这样做的好处是不再需要对数据进行排序,但是开销依然很大。
    • 如果我们在 Extral 列中看到 Using index,说明采用了索引覆盖,也就是索引可以覆盖所需的 SELECT 字段,就不需要进行回表,这样就减少了数据查找的开销。
  • range 表示索引范围扫描。
  • index_merge 说明查询同时使用了两个或以上的索引,最后取了交集或者并集。
  • ref 表示非唯一索引,或者是唯一索引的非唯一性前缀。
  • eq_ref 表示使用主键或唯一索引时产生的访问方式,通常使用在多表联查中。
  • const 表示使用了主键或者唯一索引(所有的部分)与常量值进行比较。
    • 需要说明的是:const 类型和 eq_ref 都使用了主键或唯一索引,不过这两个类型有所区别,const 是与常量进行比较,查询效率会更快,而 eq_ref 通常用于多表联查中
  • system 一般用于 MyISAM 或 Memory 表,属于 const 类型的特例,当表只有一行时连接类型为 system。

除了 all 类型外,其他类型都可以使用到索引,但不同的连接方式的效率也会有所不同,效率从低到高依次为 all < index < range < index_merge < ref < eq_ref < const/system

我们在查看执行计划的时候,通常希望执行计划至少可以使用到 range 级别以上的连接方式,如果只使用到了 all 或者 index 连接方式,我们可以从 SQL 语句和索引设计的角度上进行改进。

5,SHOW PROFILE 命令

SHOW PROFILE 相比 EXPLAIN 能看到更进一步的执行解析,包括 SQL 都做了什么、所花费的时间等。

默认情况下,profiling 是关闭的:

mysql > show variables like 'profiling';

Mysql 学习笔记 2

开启命令如下:

mysql > set profiling = 'ON';

查看下当前会话都有哪些 profiles

mysql > show profiles;

Mysql 学习笔记 2

查看上一个查询的开销:

mysql > show profile;

# 也可以查看指定的 Query ID 的开销,比如:
# `show profile for query 2`
# 查询结果是一样的

Mysql 学习笔记 2

注意:SHOW PROFILE 命令将被弃用,我们可以从 information_schema 中的 profiling 数据表进行查看。

29,MySQL 主从同步

提升数据库高并发访问效率的三种方式:

  • 首先需要考虑的是优化 SQL 和索引,这种方式简单有效。
  • 其次是采用缓存的策略,比如使用 Redis。
  • 最后是对数据库采用主从架构,进行读写分离。

1,主从同步的作用

  • 达到读写分离的效果:
    • 将同一份数据被放到多个数据库中,其中一个数据库是 Master 主库,其余的多个数据库是 Slave 从库。
    • 当主库进行更新的时候,会自动将数据复制到从库中。
    • 当读取数据的时候,会从从库中进行读取,也就是采用读写分离的方式。
  • 达到数据备份的效果
  • 达到高可用性:当服务器出现故障或宕机的情况下,可以切换到从服务器上,保证服务的正常运行。

2,主从同步的原理

主从同步是基于 Binlog 进行数据同步的,它是二进制日志文件,存储的是一个个的事件(Event),这些事件分别对应着数据库的更新操作,比如 INSERT、UPDATE、DELETE 等。

需要注意的是,不是所有版本的 MySQL 都默认开启服务器的二进制日志,在进行主从同步的时候,我们需要先检查服务器是否已经开启了二进制日志。

在主从复制过程中,会基于 3 个线程来操作:

  • 一个主库线程:即 Binlog 转储线程,当从库线程连接的时候,主库可以将 Binlog 日志发送给从库。
  • 两个从库线程:
    • 一个是从库 I/O 线程:向主库发送请求更新 Binlog,并拷贝到本地形成中继日志(Relay log)。
    • 一个是从库 SQL 线程:读取从库中的中继日志,并执行日志中的事件,从而将从库中的数据与主库保持同步。

Mysql 学习笔记 2

3,主从同步的数据一致性问题

进行主从同步的内容是二进制日志,在进行网络传输的过程中就一定会存在延迟,这样就可能造成用户在从库上读取的数据不是最新的数据。

比如我们对一条记录进行更新,这个操作是在主库上完成的,而在很短的时间内又对同一个记录进行了读取,这时候从库还没有完成数据的更新,那么我们通过从库读到的数据就是一条旧的记录。

4,如何解决数据一致性问题

主从数据库之间数据复制有 3 种方式,按照数据一致性从弱到强来进行划分:

  • 异步复制:主库写成功后,及返回成功,而不保证从库中写成功。
    Mysql 学习笔记 2

  • 半同步复制:MySQL5.5 版本之后开始支持半同步复制的方式。

    • 原理是在客户端提交 COMMIT 之后不直接将结果返回给客户端,而是等待至少有一个从库接收到了 Binlog,并且写入到中继日志中,再返回给客户端。
    • 这样做的好处是提高了数据的一致性,当然相比于异步复制来说,至少多增加了一个网络连接的延迟,降低了主库写的效率。
    • 在 MySQL5.7 版本中增加了一个rpl_semi_sync_master_wait_for_slave_count 参数,我们可以对应答的从库数量进行设置,默认为 1,也就是说只要有 1 个从库进行了响应,就可以返回给客户端。
    • 如果将这个参数调大,可以提升数据一致性的强度,但也会增加主库等待从库响应的时间。
  • 组复制:简称 MGR(MySQL Group Replication),是 MySQL 在 5.7.17 版本中推出的一种新的数据复制技术,这种复制技术是基于 Paxos 协议的状态机复制。

    • MGR 将 MySQL 带入了数据强一致性的时代,是一个划时代的创新,其中一个重要的原因就是 MGR 是基于 Paxos 协议的。

30,InnoDB 存储引擎的表空间

InnoDB 存储引擎的文件格式是 .ibd 文件,数据会按照表空间进行存储,分为:

  • 共享表空间:多个数据表共用一个表空间,同时表空间也会自动分成多个文件存放到磁盘上。
    • 这样做的好处在于单个数据表的大小可以突破文件系统大小的限制,最大可以达到 64TB,也就是 InnoDB 存储引擎表空间的上限。
    • 不足是,多个数据表存放到一起,结构不清晰,不利于数据的找回,同时将所有数据和索引都存放到一个文件中,也会使得共享表空间的文件很大。
  • 独立表空间:每个数据表都有自己的物理文件,也就是 table_name.ibd 的文件,在这个文件中保存了数据表中的数据、索引、表的内部数据字典等信息。
    • 优势在于每张表都相互独立,不会影响到其他数据表,存储结构清晰,利于数据恢复,同时数据表还可以在不同的数据库之间进行迁移。

如果我们之前没有做过全量备份,也没有开启 Binlog,那么我们还可以通过.ibd 文件进行数据恢复,采用独立表空间的方式可以很方便地对数据库进行迁移和分析。

查看表空间的存储方式:

# ON 表示独立表空间,而 OFF 则表示共享表空间。
> show variables like 'innodb_file_per_table';

Mysql 学习笔记 2


程序员灯塔
转载请注明原文链接:Mysql 学习笔记 2
喜欢 (0)