Skip to content

Latest commit

 

History

History
219 lines (176 loc) · 25.6 KB

数据库-MySQL.md

File metadata and controls

219 lines (176 loc) · 25.6 KB
Raw

MySQL

引擎对比

  1. InnoDB支持事务
  2. InnoDB支持外键
  3. InnoDB有行级锁，MyISAM是表级锁

MyISAM相对简单所以在效率上要优于InnoDB。如果系统插入和查询操作多，不需要事务外键。选择MyISAM
如果需要频繁的更新、删除操作，或者需要事务、外键、行级锁的时候。选择InnoDB。

数据库性能优化

  1. 优化SQL语句和索引，在where/group by/order by中用到的字段建立索引，索引字段越小越好，复合索引建立的顺序
  2. 加缓存，Memcached, Redis
  3. 主从复制，读写分离
  4. 垂直拆分，其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统
  5. 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

SQL优化：

优化步骤：

  1. 根据慢日志定位慢查询SQL
  2. 用explain分析SQL（type和extra字段分析）
  3. 修改SQL或加索引（如下）
  • 对经常查询的列建立索引，但索引建多了当数据改变时修改索引会增大开销
  • 使用精确列名查询而不是*，特别是当数据量大的时候
  • 减少子查询，使用Join替代
  • 不用NOT IN，因为会使用全表扫描而不是索引；不用IS NULL，NOT IS NULL，因为会使索引、索引统计和值更加复杂，并且需要额外一个字节的存储空间。

问：max(xxx)如何用索引优化？ 答：在xxx列上建立索引，因为索引是B+树顺序排列的，在下次查询的时候就会使用索引来查询到最大的值是哪个

问：有个表特别大，字段是姓名、年龄、班级，如果调用select * from table where name = xxx and age = xxx该如何通过建立索引的方式优化查询速度？
答：由于mysql查询每次只能使用一个索引，如果在name、age两列上创建联合索引的话将带来更高的效率。如果我们创建了(name, age)的联合索引，那么其实相当于创建了(name, age)、(name)2个索引。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。其次还要考虑该列的数据离散程度，如果有很多不同的值的话建议放在左边，name的离散程度也大于age。

问：优化大数据量的分页查询？

  1. 索引优化：确保用于排序和筛选的字段已经建立了索引
  2. 延迟关联：先获取主键，然后再根据主键获取完整的行数据。这样可以减少数据库在排序时需要处理的数据量。通过将 select * 转变为 select id，把符合条件的 id 筛选出来后，最后通过嵌套查询的方式按顺序取出 id 对应的行
  3. 业务限制：查询页数上限
  4. 使用其他数据结构如ES、分表
-- 优化前
select *
from people
order by create_time desc
limit 5000000, 10;

-- 优化后
select a.*
from people a
inner join(
    select id
    from people
    order by create_time desc
    limit 5000000, 10
) b ON a.id = b.id;

最左前缀匹配原则

定义：在检索数据时从联合索引的最左边开始匹配。一直向右匹配直到遇到范围查询（>/</between/like）就停止后面的匹配。比如查询a = 3, b = 4 and c > 5 and d = 6，如果建立(a,b,c,d)索引，d是用不到索引的，如果建立(a,b,d,c)索引，则都可以用到，此外a,b,d的顺序可以任意调整

联合索引原理：联合索引是将第一个字段作为非叶子节点形成B+树结构的，在查询索引的时候先根据该字段一步步查询到具体的叶子节点，叶子节点上还会存在第二个字段甚至第三个字段的已排序结果。所以对于(a,b,c,d)这个联合索引会存在(a)，(a,b)，(a,b,c)，(a,b,c,d)四个索引

事务隔离级别

  1. 原子性（Atomicity）：事务作为一个整体被执行 ，要么全部执行，要么全部不执行；
  2. 一致性（Consistency）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作；
  3. 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行，然后你可以扯到隔离级别；
  4. 持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存。

不同事务隔离级别的问题：

  1. 读未提交（Read Uncommitted）：在这个级别下，一个事务可以读取到另一个事务尚未提交的数据变更，即脏读（Dirty Read）现象可能发生。这种隔离级别允许最大的并行处理能力，但并发事务间的数据一致性最弱。
  2. 读已提交（Read Committed）：在这种级别下，一个事务只能看到其他事务已经提交的数据，解决了脏读的问题。但是，在同一个事务内，前后两次相同的查询可能会返回不同的结果，这被称为不可重复读（Non-repeatable Read）。
  3. 可重复读（Repeatable Read）：这是MySQL InnoDB存储引擎默认的事务隔离级别。在这个级别下，事务在整个生命周期内可以看到第一次执行查询时的快照数据，即使其他事务在此期间提交了对这些数据的修改，也不会影响本事务内查询的结果，因此消除了不可重复读的问题。但是，它不能完全避免幻读（Phantom Read），即在同一事务内，前后两次相同的范围查询可能因为其他事务插入新的行而返回不同数量的结果。

为什么可重复读不能完全避免幻读：

  1. 幻读的定义： 幻读是指在一个事务内，同一个查询语句多次执行时，由于其他事务提交了新的数据插入或删除操作，导致前后两次查询结果集不一致的情况。具体表现为：即使事务A在开始时已经获取了一个数据范围的快照，当它再次扫描这个范围时，发现出现了之前未读取到的新行，这些新行如同“幻影”一般突然出现。
  2. 可重复读与幻读的关系： 在可重复读隔离级别下，对于已存在的记录，MVCC确保事务能够看到第一次查询时的数据状态，因此不会发生不可重复读。但是，当有新的行被插入到符合事务查询条件的范围内时，MVCC本身并不能阻止这种现象的发生，因为新插入的行对于当前事务是可见的（取决于插入事务的提交时间点和当前事务的Read View）。
  3. 为了减轻幻读的影响，InnoDB在执行某些范围查询时会采用间隙锁（Gap Locks）或者Next-Key Locks来锁定索引区间，防止其他事务在这个范围内插入新的记录，但并非所有的范围查询都会自动加上这样的锁，而且这仅限于使用基于索引的操作，非索引字段的范围查询无法通过间隙锁完全避免幻读。

锁表、锁行

  1. InnoDB 支持表锁和行锁，使用索引作为检索条件修改数据时采用行锁，否则采用表锁
  2. InnoDB 自动给修改操作加锁，给查询操作不自动加锁
  3. 行锁相对于表锁来说，优势在于高并发场景下表现更突出，毕竟锁的粒度小
  4. 当表的大部分数据需要被修改，或者是多表复杂关联查询时，建议使用表锁优于行锁

悲观锁乐观锁、如何写对应的SQL

悲观锁：在悲观锁机制下，事务认为在它执行期间数据一定会被其他事务修改，因此在读取数据时就立即对其加锁，阻止其他事务对同一数据进行操作，直到当前事务结束并释放锁。SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE 乐观锁：乐观锁并不在读取数据时立即加锁，而是在更新数据时检查自上次读取以来数据是否已被其他事务修改过。这通常通过在表中添加一个版本号字段或者时间戳字段来实现。

锁的种类

按功能分类：

  1. 共享锁：Shared Locks，简称S锁。在事务要读取一条记录时，需要先获取该记录的S锁。
  2. 独占锁：排他锁，英文名：Exclusive Locks，简称X锁。在事务要改动一条记录时，需要先获取该记录的X锁。

按粒度分类：

  1. 表锁是对一整张表加锁，虽然可分为读锁和写锁，但毕竟是锁住整张表，会导致并发能力下降，一般是做ddl处理时使用。另外表级别还有AUTO-INC锁:在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把AUTO-INC锁释放掉。这样一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的
  2. 行锁，也称为记录锁，顾名思义就是在记录上加的锁。锁住数据行，这种加锁方法比较复杂，但是由于只锁住有限的数据，对于其它数据不加限制，所以并发能力强，MySQL一般都是用行锁来处理并发事务。下面是行锁的类型：
  • Record Locks：记录锁，当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁；
  • Gap Locks：是为了防止插入幻影记录而提出的，不允许其他事务往这条记录前面的间隙插入新记录。在REPEATABLE READ隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用MVCC方案解决，也可以采用加锁方案解决，但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁
  • Next-Key Locks：记录锁和一个gap锁的合体。锁住某条记录，又想阻止其他事务在该记录前面的间隙插入新记录
  • Insert Intention Locks：插入意向锁。一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了所谓的gap锁，如果有的话，插入操作需要等待，直到拥有gap锁的那个事务提交
  • 隐式锁：当事务需要加锁的时，如果这个锁不可能发生冲突，InnoDB会跳过加锁环节，这种机制称为隐式锁。隐式锁是InnoDB实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能

MVCC

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种用于数据库管理系统中的事务处理机制，它在不使用加锁或者只对数据进行有限锁定的情况下，为并发事务提供了一种相对无冲突的访问方式。MVCC有效地避免了脏读、不可重复读等问题，并极大地提高了数据库系统的并发性能

原理：

  1. 隐式字段：每行记录除了实际的数据列外，还包含额外的系统信息，例如DB_TRX_ID（最近修改该行事务ID）、DB_ROLL_PTR（回滚指针指向undo日志），以及DB_ROW_ID（行ID）等。这些隐藏字段用于跟踪数据的不同版本。
  2. 事务版本号：每个事务都有一个唯一的事务ID，在事务开始时分配。这个ID用来区分不同事务的操作时间点。
  3. Undo日志：当事务对某一行进行修改时，旧版本的数据会被存放在Undo日志中，形成历史版本链。这样当其他事务需要读取旧版本数据时，可以从Undo日志中获取。
  4. Read View：MVCC通过Read View来决定事务可见的行版本。每个读操作都会创建或复用一个Read View，根据Read View的规则判断当前事务能否看到某个版本的数据：如果被读取行的事务ID小于Read View的最低事务ID，则可见；如果大于等于Read View的最高事务ID且未提交，则不可见；否则，检查该行是否在Read View创建之后被其他已提交事务修改过。
  5. 非锁定读：在MVCC中，存在两种类型的读操作：快照读（Snapshot Read）和当前读（Current Read）。快照读就是基于MVCC实现的，它不会阻塞其他事务，并总是返回某一特定时间点的数据视图。而当前读则会获取最新的数据并加锁以确保一致性。

索引

原理

聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，所以一个表中只能拥有一个聚集索引。叶子结点即存储了真实的数据行，不再有另外单独的数据页
非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，所以一个表中可以拥有多个非聚集索引。叶子结点包含索引字段值及指向数据页数据行的逻辑指针

索引原理

为什么使用B+树来构建索引？

  1. b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素；
  2. b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定
  3. 对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历
  4. 红黑树最多只有两个子节点，所以高度会非常高，导致遍历查询的次数会多
  5. 红黑树在数组中存储的方式，导致逻辑上很近的父节点与子节点可能在物理上很远，导致无法使用磁盘预读的局部性原理，需要很多次IO才能找到磁盘上的数据。B+树一个节点中可以存储很多个索引的key，且将大小设置为一个页，一次磁盘IO就能读取很多个key
  6. 叶子节点之间还加上了下个叶子节点的指针，遍历索引也会很快。

B+树的高度如何计算？

  1. 假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数 * 单个叶子节点记录行数
  2. 假设一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
  3. 非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(int类型的话，一个int就是32位，4字节)，而指针大小是固定的在InnoDB源码中设置为6字节，假设n指主键个数即key的个数，n8 + (n + 1) * 6 = 16K=161024B , 算出n约为 1170,意味着根节点会有1170个key与1171个指针
  4. 一棵高度为2的B+树，能存放1171* 16 = 18736条这样的数据记录。同理一棵高度为3的B+树，能存放1171 * 1171 * 16 = 21939856，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。

优化

如何选择合适的列建立索引？

  1. WHERE / GROUP BY / ORDER BY / ON 的列
  2. 离散度大（不同的数据多）的列使用索引才有查询效率提升
  3. 索引字段越小越好，因为数据库按页存储的，如果每次查询IO读取的页越少查询效率越高

count1/count*/count字段 的区别

  1. count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
  2. count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
  3. count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

count(*),自动会优化指定到那一个字段。所以没必要去count(1)，用count(*)，sql会帮你完成优化的 因此：count(1)和count(*)基本没有差别！

explain详解

  1. id：select 查询序列号。id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行。
  2. select_type：查询数据的操作类型，其值如下：
  • simple：简单查询，不包含子查询或 union
  • primary:包含复杂的子查询，最外层查询标记为该值
  • subquery：在 select 或 where 包含子查询，被标记为该值
  • derived：在 from 列表中包含的子查询被标记为该值，MySQL 会递归执行这些子查询，把结果放在临时表
  • union：若第二个 select 出现在 union 之后，则被标记为该值。若 union 包含在 from 的子查询中，外层 select 被标记为 derived
  • union result：从 union 表获取结果的 select
  1. table：显示该行数据是关于哪张表
  2. partitions：匹配的分区
  3. type：表的连接类型，其值，性能由高到底排列如下：
  • system：表只有一行记录，相当于系统表
  • const：通过索引一次就找到，只匹配一行数据
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引扫描
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列
  • range：只检索给定范围的行，使用一个索引来选择行。一般使用between、>、<情况
  • index：只遍历索引树
  • ALL：全表扫描，性能最差 注：前5种情况都是理想情况的索引使用情况。通常优化至少到range级别，最好能优化到 ref
  1. possible_keys：显示 MySQL 理论上使用的索引，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。如果该值为 NULL，说明没有使用索引，可以建立索引提高性能
  2. key：显示 MySQL 实际使用的索引。如果为 NULL，则没有使用索引查询
  3. key_len：表示索引中使用的字节数，通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好 显示的是索引字段的最大长度，并非实际使用长度
  4. ref：显示该表的索引字段关联了哪张表的哪个字段
  5. rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好
  6. filtered：返回结果的行数占读取行数的百分比，值越大越好
  7. extra：包含不合适在其他列中显示但十分重要的额外信息，常见的值如下：
  • using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。出现该值，应该优化 SQL
  • using temporary：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。出现该值，应该优化 SQL
  • using index：表示相应的 select 操作使用了覆盖索引，避免了访问表的数据行，效率不错
  • using where：where 子句用于限制哪一行
  • using join buffer：使用连接缓存
  • distinct：发现第一个匹配后，停止为当前的行组合搜索更多的行

分区分库分表

分区：把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的，通过将不同数据按一定规则放到不同的区块中提升表的查询效率。

水平分表：为了解决单标数据量过大（数据量达到千万级别）问题。所以将固定的ID hash之后mod，取若0~N个值，然后将数据划分到不同表中，需要在写入与查询的时候进行ID的路由与统计

垂直分表：为了解决表的宽度问题，同时还能分别优化每张单表的处理能力。所以将表结构根据数据的活跃度拆分成多个表，把不常用的字段单独放到一个表、把大字段单独放到一个表、把经常使用的字段放到一个表

分库：面对高并发的读写访问，当数据库无法承载写操作压力时，不管如何扩展slave服务器，此时都没有意义了。因此数据库进行拆分，从而提高数据库写入能力，这就是分库。

问题：

  1. 事务问题。在执行分库之后，由于数据存储到了不同的库上，数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。
  2. 跨库跨表的join问题。在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。
  3. 额外的数据管理负担和数据运算压力。额外的数据管理负担，最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序解决，但必然引起额外的逻辑运算。

MySQL数据库主从延迟同步方案

什么事主从延迟：为了完成主从复制，从库需要通过 I/O 线程获取主库中 dump 线程读取的 binlog 内容并写入到自己的中继日志 relay log 中，从库的 SQL 线程再读取中继日志，重做中继日志中的日志，相当于再执行一遍 SQL，更新自己的数据库，以达到数据的一致性。主从延迟，就是同一个事务，从库执行完成的时间与主库执行完成的时间之差

  1. 「异步复制」：MySQL 默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给客户端，并不关心从库是否已经接收并处理。这样就会有一个问题，一旦主库宕机，此时主库上已经提交的事务可能因为网络原因并没有传到从库上，如果此时执行故障转移，强行将从提升为主，可能导致新主上的数据不完整。
  2. 「全同步复制」：指当主库执行完一个事务，并且所有的从库都执行了该事务，主库才提交事务并返回结果给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
  3. 「半同步复制」：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库接收到并写到 Relay Log 文件即可，主库不需要等待所有从库给主库返回 ACK。主库收到这个 ACK 以后，才能给客户端返回 “事务完成” 的确认。这样会出现一个问题，就是实际上主库已经将该事务 commit 到了存储引擎层，应用已经可以看到数据发生了变化，只是在等待返回而已。如果此时主库宕机，可能从库还没写入 Relay Log，就会发生主从库数据不一致。为了解决上述问题，MySQL 5.7 引入了增强半同步复制。针对上面这个图，“Waiting Slave dump” 被调整到了 “Storage Commit” 之前，即主库写数据到 binlog 后，就开始等待从库的应答 ACK，直到至少一个从库写入 Relay Log 后，并将数据落盘，然后返回给主库 ACK，通知主库可以执行 commit 操作，然后主库再将事务提交到事务引擎层，应用此时才可以看到数据发生了变化。
  4. 一主多从：如果从库承担了大量查询请求，那么从库上的查询操作将耗费大量的 CPU 资源，从而影响了同步速度，造成主从延迟。那么我们可以多接几个从库，让这些从库来共同分担读的压力。
  5. 强制走主库：如果某些操作对数据的实时性要求比较苛刻，需要反映实时最新的数据，比如说涉及金钱的金融类系统、在线实时系统、又或者是写入之后马上又读的业务，这时我们就得放弃读写分离，让此类的读请求也走主库，这就不存延迟问题了。
  6. 并行复制：MySQL在5.6版本中使用并行复制来解决主从延迟问题。所谓并行复制，指的就是从库开启多个SQL线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。MySQL 5.7对并行复制做了进一步改进，开始支持真正的并行复制功能。MySQL5.7的并行复制是基于组提交的并行复制，官方称为enhanced multi-threaded slave（简称MTS），延迟问题已经得到了极大的改善。其核心思想是：一个组提交的事务都是可以并行回放（配合binary log group commit）；slave机器的relay log中 last_committed相同的事务（sequence_num不同）可以并发执行。

强制走主库之后，如何在处理大数据量的时候保证实时写查：

  1. 表结构与索引优化：确保表结构设计合理，尽量避免数据冗余和更新异常。根据查询条件创建合适的索引，特别是对于高频查询字段，可以显著提高读取速度。
  2. 分区表（Partitioning）：对于非常大的表，可以考虑使用分区表功能，将大表物理分割成多个小表，根据时间、范围或其他业务逻辑进行划分，这样不仅能提高查询效率，还可以通过并行处理提高写入速度。
  3. 分库分表：当单表数据量过大时，采用水平拆分或垂直拆分策略将数据分布到多个数据库或表中，进一步提升读写性能。
  4. 查询优化：避免全表扫描，尽可能使用覆盖索引。编写高效的SQL语句，减少不必要的计算和排序操作。
  5. 缓存机制：在数据库层面，可以考虑使用内存表或者缓存技术（如Redis）来临时存储最近写入的数据，以便快速响应实时查询请求。
  6. 实时分析型数据库：对于大数据量且实时性要求极高的场景，可以考虑引入实时分析型数据库系统，例如ClickHouse、Druid等，它们设计之初就为实时写入和查询提供了高效的支持。

欢迎光临我的博客，发现更多技术资源~