1.MySQL

1.1 索引使用注意事项

（1）索引失效情况

  1. 在索引上做运算、函数、对索引列进行隐式转换（索引列式字符串，但Sql产讯未使用引号）
  2. 在索引列使用左模糊查询，全模糊查询
  3. 联合索引未使用最左匹配原则

（2）不适用场景

  1. 表数据量少不适合加索引
  2. 更新频繁的表不适合加索引
  3. 区分度低的字段不适合加索引（如性别）

1.2 MySQL中的死锁

　　　死锁是指两个或两个以上的线程在程序执行过程中，争夺同一个共享资源导致线程互相等待的现象。

（1）死锁形成的条件

  • 互斥：共享资源只能被一个线程占用
  • 请求并等待：一个线程获得了某个共享资源还想去获取另外的共享资源
  • 不可抢占：一个线程占有某个共享资源，其余线程无法获取这个线程占有的共享资源
  • 循环等待：一个线程占有某个共享资源，想去获取另外一个线程占有的共享资源

（2）解决方案

　　由于互斥是锁的特性，无法改变，因此可以从请求并等待、不可抢占和循环等待三个条件下手解决死锁问题

  • 请求并等待：一次性申请所有共享资源
  • 不可抢占：一个线程想获取其他共享资源，先释放自己持有的共享资源
  • 循环等待：按序申请资源

1.3 MySQL优化

　　MySQL的优化可以从硬件和操作系统、架构设计、MySQL程序配置、Sql优化等方面进行优化

  1. 硬件：CPU、内存大小、磁盘读写速度、网络带宽
  2. 操作系统：应用文件句柄、操作系统的网络配置

前面这两个部分的优化一般由DBA或运维工程师完成

  • 架构设计：搭建MySQL主从集群、读写分离、分库分表、热点数据进缓存
  • MySQL程序配置：在my.cnf文件中修改配置，如缓冲区大小，最大连接数等
  • SQL优化分为三步
  1. 慢SQL定位排查
  2. 执行计划分析
  3. show profile 分析当前会话SQL语句资源消耗情况

SQL优化：

  • 最大化使用索引
  • 避免全表扫描
  • 尽可能返回少量且有效的数据
  • 查询优化：join、分页
  • 建表优化：where与order by字段使用索引

1.4 分库分表设计

分库分表方案

（1）水平分库分表

  • 水平分库：以字段为依据，按照一定策略（hash、range），将一个库中的数据拆分到多个库中
  • 水平分表：以字段为依据，按照一定策略（hash，range），将一个表中的数据拆分到多个表中

（2）垂直分库分表

  • 垂直分库：以表为依据，按业务归属不同，将不同的表拆分到不同的库中。
  • 垂直分表：以字段为依据，按字段的活跃度，将表中字段拆到不同的表（主表和扩展表）中。

常用分库分表中间件：

  • sharding-jdbc(当当)
  • Mycat
  • TDDL(淘宝)
  • Oceanus(58 同城数据库中间件)
  • vitess(谷歌开发的数据库中间件)
  • Atlas(Qihoo 360)

分库分表可能遇到的问题：

  • 事务问题：分布式事务
  • 跨节点Join问题：分两次查询实现
  • 跨节点的count，order by，group by以及聚合函数问题：分别在各个节点上得到结果在应用程序端进行合并
  • 数据迁移，容量规划，扩容
  • ID问题，数据库被切分后，不能再依赖数据库自身的主键生成机制，考虑使用分布式ID
  • 跨分片的排序分页问题

三种应用场景：

（1）只分库不分表

　　当数据库读写访问过高，可能会出现数据库连接不够用的情况。这时考虑分库，增加数据库实例获取更多数据库连接，从而提高系统的并发性能。

（2）只分表不分库

　　当单表存储数据量非常大时，并发量不高，数据库连接够用。但数据写入与查询的性能出现瓶颈，这时考虑分表。将数据拆分到多张表中减单标存储的数据量，进而提升读写的效率。

（3）既分库又分表

　　既满足数据库连接不够用，且单表的数据量过大，从而导致数据库读写速度变慢，需要既分库又分表。

1.5 INNODB与MYISAM的区别

  1. 数据存储方式：INNODB是数据和索引存放在同一个文件中，MYISAM数据和索引存放在不同文件
  2. 事务：INNNODB支持事务，MYISAM不支持事务
  3. 外键：INNODB支持外键，MYISAM不支持外键
  4. 锁：MYISAM支持表锁，INNODB支持表锁、行锁、临键锁、间隙锁

　　需要事务就使用INNODB，表中大部分操作使用MYISAM

1.6 为什么MySQL采用B+树做索引

  1. B+树是一种多叉平衡树，查询、插入、删除数据效率高
  2. B+树非叶子结点存储索引，叶子结点存储索引和数据，相对于其他数据结构如B树在数据量相同情况下，IO次数少
  3. B+树的叶子结点以双向链表构成，适合范围查询
  4. B+树叶子结点存储所有数据，无需向B树那样全表扫描

1.7 聚簇索引和非聚簇索引

　　　聚簇索引是以主键构建的索引，非聚簇索引是主键以外索引构成的索引，也称二级索引。

　　　聚簇索引不仅仅表示一种索引类型，它还表示一种数据的存储方式。它意味着每一张表中必须要有一个主键，如果没有主键，INNODB默认会选择或添加一个隐藏列作为主键来存储整张表的数据行。即一张表的数据对应的物理文件本身是按照B+树组织的一种索引结构，聚簇索引按照每张表的主键构建一棵B+树，叶子结点存储这张表的每一行数据记录。

　　使用UUID这种随机ID，在频繁插入数据的时候，会导致随机磁盘IO，导致性能下降。

重点：

主键索引跟非聚簇索引不在同一颗B+树；

回表：指在非聚簇索引的B+树上没有找到不带有索引的字段，所以去聚簇索引所在的B+树根据主键ID查数据记录

减少回表：非聚簇索引的叶子结点存有索引（非聚簇索引）列表和主键ID。如果非聚簇索引查询的字段是索引字段就不需要回表（叶子结点的索引列表存有索引字段）。

 

1.8 MySQL的三大日志

undo log（回滚日志）：innoDB存储引擎生成的日志，实现事务中的原子性，常用于事务回滚和MVCC（通过ReadView和undolog实现）。事务未更新前记录更新前的数据到undolog日志中，事务回滚使用undolog进行回滚。

redo log（重做日志）：innoDB存储引擎生成的日志，实现事务中的持久性，常用于掉电等故障恢复。

binlog（归档日志）：Server层生成的日志，主要用于数据备份和主从复制。

 1.8.1 redolog和binlog的区别

  • 适用对象不同
    • binlog是Server层实现的，所有存储引擎都可以使用
    • redolog是innoDB存储引擎实现的
  • 文件格式不同
    • binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED
    • redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新
  • 写入方式不同
    • 追加写，写满新建一个日志，不会覆盖以前日志，保存的全量日志。
    • 循环写，日志空间固定，全部写满从头开始，保存未被刷入磁盘的脏页日志。
  • 用途不同：binlog备份恢复，主从复制，redolog用于掉电等故障恢复

1.8.2 主从复制

　　MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成

这里借用小林coding的图：

 

MySQL 集群的主从复制过程梳理成 3 个阶段：

  • 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
  • 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
  • 回放 Binlog：回放 binlog，并更新存储引擎中的数据。

具体详细过程如下：

  • MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
  • 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
  • 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

1.9 锁

1.9.1 哪些锁

　　锁分为三种，分别是全局锁、表级锁、行级锁。

（1）全局锁

开启

flush tables with read lock

　　整个数据库处于只读状态，无法进行DML、DDL操作

释放

unlock tables

　　全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

全局锁的缺点？

　　如果数据库里有很多数据，备份就会花费很多的时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

解决办法：

　　如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

　　备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎

（2）表级锁

表级锁分为：

  • 表锁
  • 元数据锁
  • 意向锁
  • Auto-INC锁

元数据锁（MDL）

  • 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
  • 对一张表做结构变更操作的时候，加的是 MDL 写锁；

　　MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

MDL 不需要显示调用，那它是在什么时候释放的?

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

意向锁

  • 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
  • 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

　　意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

　　意向锁的目的是为了快速判断表里是否有记录被加锁，避免全表扫描查看是否存在独占锁。

AUTO-INC锁

　　AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是在执行完插入语句后就会立即释放。

存在的问题：

　　一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

　　一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

  • 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
  • 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
  • 当 innodb_autoinc_lock_mode = 1：
    • 普通 insert 语句，自增锁在申请之后就马上释放；
    • 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题。

（3）行级锁

 

 

参考链接

小林coding (xiaolincoding.com)

posted @ 2024-04-25 11:15  求知律己  阅读(6)  评论(0)  编辑  收藏  举报