数据库/MYSQL

saligialin
1,396 阅读26分钟

数据库

范式

  • 第一范式： 关系模式R中，所有的属性均为简单属性(每个属性都是不可再分的)。 1NF是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。
  • 第二范式： R∈1NF，每个非主属性都完全依赖于R的主键。 2NF是指每个表必须有主关键字(Primary key),其他数据元素与主关键字一一对应。通常称这种关系为函数依赖(Functional dependence)关系，即表中其他数据元素都依赖于主关键字,或称该数据元素惟一地被主关键字所标识。
  • 第三范式： R∈2NF，每个非主属性都不传递依赖于R的主键。 3NF就是指表中的所有数据元素不但要能唯一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。也就是说，对于一个满足2nd NF 的数据结构来说，表中有可能存在某些数据元素依赖于其他非关键字数据元素的现象,必须消除。
  • 巴斯-科德范式： BCNF是指数据库表中如果不存在任何字段对任意候选关键字段的传递函数依赖。

DDL、DML、DCL

  • DDL（Data Definition Languages）：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括create、drop、alter等。
  • DML（Data Manipulation Language）：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括insert、delete、udpate和select等。
  • DCL（Data Control Language）：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括grant、revoke等。

锁

数据库锁一般可以分为两类：一个是悲观锁，另一个是乐观锁。 image.png

悲观锁

悲观锁对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。
悲观锁的实现，往往依靠数据库提供的锁机制。
在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁

乐观锁认为对统一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在每次更新的时候，会判断在此期间别人有没有去更新这个数据。

乐观锁，一种是基于数据版本（Version）记录机制实现。
数据版本即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。
此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

另外，还可以通过CAS(compare and swap)方式实现乐观锁，CAS操作包括三个操作数：

  • 需要读写的内存位置V
  • 需要比较的预期原值A
  • 拟写入的新值B 如果内存位置V的值与预期原值A相匹配，那么处理器会自动将该位置值更新为新值B。否则处理器不做任何操作。

对比

悲观锁：

  • 优点：
    • 能保证保证了数据获取和修改都是有序进行的，可以做到保证数据的安全性。
  • 缺点：
    • 由于需要加锁，而且可能面临锁冲突甚至死锁的问题。
    • 悲观并发控制增加了系统的额外开销，降低了系统的效率，同时也会降低了系统的并行性。

乐观锁：

  • 优点：
    • 避免了长事务中的数据库加锁开销，大大提升了大并发量下的系统整体性能表现。
  • 缺点：
    • ABA问题：如果变量V，初次读取时是A值，并且在准备赋值的时候，检查到它仍然是A值，但这并不能证明它没有被其他线程修改过。
    • 循环时间长，开销大：自旋 CAS（也就是不成功就一直循环执行直到成功），如果长时间不成功，会给CPU带来非常大的执行开销。
    • 只保证单个共享变量的原子操作：CAS只对单个共享变量有效，当操作涉及跨多个共享变量时，CAS操作无效。

适用场景：

  • 乐观锁：比较适合读取操作比较频繁的场景，如果出现大量的写入操作，数据发生冲突的可能性就会增大，为了保证数据的一致性，应用层需要不断的重新获取数据，这样会增加大量的查询操作，降低了系统的吞吐量。
  • 悲观锁：比较适合写入操作比较频繁的场景，如果出现大量的读取操作，每次读取的时候都会进行加锁，这样会增加大量的锁的开销，降低了系统的吞吐量。

多版本并发控制

多版本并发控制（MVCC）英文全称为Multi-Version Concurrency Control。
MVCC的思想就是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。
使用多版本并发控制可以解决如下问题：

  1. 读写之间阻塞的问题。
  2. 降低了死锁的概率。
  3. 解决了一致性读的问题。

事务

用户定义的一个数据库操作序列，要么全做要么全不做，是一个不可分割的单位以BEGIN TRANSACTION开始，COMMIT / ROLLBACK结束。

事务的特性：

  • 原子性(A)-Atomicity ：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
  • 一致性(C)-Consistency ：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
  • 隔离性(I)-Isolation ：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
  • 持续性(D)-Durability ：已被提交的事务对数据库的修改应该永久保存在数据库中。

事务引发的问题

  • 脏读(Dirty Read)：A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。
  • 不可重复读(Non Repeatable Read)：事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间 。而在事务A第一次读取某数据后，事务B执行更改该数据操作，之后事务A第二次读取该数据，发现之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，这种情况称为不可重复读。
  • 幻读(Phantom Read)：事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，事务B执行了增删数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，这种情况称为幻读。

隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

image.png PS：MySQL默认的隔离级别为 Repeatable read 。

MYSQL

逻辑架构

  1. 连接层： 最长层是一些客户端和连接服务，包括本地socket通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关安全方案。在该层引入线程池的概念，为通过认证安全接入的客户端提供线程，同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  2. 服务层： 第二层架构主要完成大多数的核心服务功能（如SQL接口），并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也是在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建响应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好地提升系统的性能。
  3. 引擎层： 存储引擎层，存储引擎真正的负责了MYSQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
  4. 存储层： 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

MYSQL线程池

线程池的原理类似于操作系统中的缓冲区的概念，它的流程如下：先启动若干数量的线程，并让这些线程都处于睡眠状态，当客户端有一个新请求时，就会唤醒线程池中的某一个睡眠线程，让它来处理客户端的这个请求，当处理完这个请求后，线程又处于睡眠状态。
使用线程池的优点：减少线程重复创建与开销的，提高性能；对系统起到保护作用。

数据库连接池

一个数据库连接对象均对应一个物理数据库连接，每次操作都打开一个物理连接，使用完都关闭连接，这样造成系统的性能低下。数据库连接池的解决方案是在应用程序启动时建立足够的数据库连接，并讲这些连接组成一个连接池，由应用程序动态地对池中的连接进行申请、使用和释放。对于多于连接池中连接数的并发请求，应该在请求队列中排队等待。并且应用程序可以根据池中连接的使用率，动态增加或减少池中的连接数。

存储引擎

image.png

  • MyISAM：
    • 不支持外键
    • 不支持事务
    • 使用表锁，即操作一条记录也会锁住整个表，不合格高并发开发
    • 只缓存索引，不缓存真实数据
    • 表空间小
    • 更关注性能
  • InnoDB： 是MySQL和MariaDB的数据库引擎之一，最初由MySQL AB发行。InnoDB由Innobase Oy公司所开发，2006年五月时由甲骨文公司并购。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能，类似于PostgreSQL。
    • 支持外键
    • 支持事务
    • 使用行锁，操作时只锁定某一行，不对其他行有影响。适合高并发操作。
    • 不仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响。
    • 表空间大
    • 更关注事务

优化分析

SQL手写顺序

    SELECT <select list>
    FROM <left_table> <join type>
    JOIN <right table> ON <join condition>
    WHERE <where condition>
    GROUP BY <group by list>
    HAVING <having condition>
    ORDER BY <order by condition>
    LIMIT <limit number>

性能下降原因：

  • SQL语句写的烂
  • 索引失效
  • 关联查询使用太多的JOIN
  • 服务器调优及各个参数设置不当

七种JOIN

image.png

  • 笛卡尔积：CROSS JOIN
    SELECT * FROM table_A a CROSS JOIN table_B b;
    SELECT * FROM table_A a, table_B b;

索引

MYSQL对索引的定义：Index是帮助MYSQL高效获取数据的数据结构。可以简单理解为排好序的快速查找数据结构。索引的目的在于提高查询效率。

索引的分类

  • 单值索引
  • 唯一索引
  • 复合索引

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。
按物理存储分类可分为：聚簇索引、二级索引（辅助索引） 。
按字段特性分类可分为：主键索引、普通索引、前缀索引。
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引） 。

索引语法

   --[UNIQUE/PRIMARY/INDEX/FULLTEXT]
   --创建：
   CREATE [UNIQUE] INDEX indexName ON table_name(columnName(length));
   ALTER table_name ADD [UNIQUE] INDEX [indexName] ON(columnName(length));
   --删除：
   DROP INDEX [indexName] ON table_name;
   --查看：
   SHOW INDEX FROM table_name\G

索引的优劣势

  • 优势：
    1. 提高数据的检索效率，降低数据的IO成本；
    2. 降低数据排序的成本，降低了CPU的消耗
  • 劣势：
    1. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。所以索引列也会占用空间。
    2. 索引虽然大大提高了查询速度，同时却会降低更新表的速度。
    3. 索引只是提高效率的一个因素，如果MYSQL有大数据量的表，就需要花时间研究建立最优秀的索引或进行优化查询。

索引的结构

  • BTree索引
  • Hash索引
  • full-text索引
  • R-Tree索引

使用索引的情景

  • 适合使用索引的情况：
    1. 主键会自动建立唯一索引。
    2. 频繁作为查询条件的字段应该建立索引。
    3. 查询中与其他表关联的字段，外键关系建立索引。
    4. 频繁更新的字段不适合创建索引。
    5. 高并发下倾向于组合索引。
    6. 查询中排序的字段。排序字段按照索引去访问将提高排序速度。
    7. 查询中统计或者分组字段。
  • 不适合使用索引的情况：
    1. 表中记录太少。
    2. 经常增删改的表。
    3. 某个数据列中的数据重复率太低（数据值/数据总量）不适合创建索引。

性能分析

MySQL Query Optimizer

MySQL查询优化器，是MySQL中专门负责优化SELECT语句的优化器模块。其主要功能是通过计算分析系统中收集到的统计信息，为客户端请求Query提供mysql认为最优的执行计划。
当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出去是SELECT语句后，转发给MySQL Query Optimizer时，查询优化器首先对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息，（如果有）则看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或者Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

explain

使用EXPLAIN关键词可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或表结构的性能瓶颈。
执行语句EXPLAIN SQL语句 image.png 上图中字段解释：

  • id：select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序。
    • id相同：执行顺序由上到下
    • id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
    • id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
  • select_type：表明查询的操作类型
    • SIMPLE：简单的select查询，查询中不包含子查询或UNION。
    • PRIMARY：查询中如果包含任何复杂的子成分，则最外层查询被标记为PRIMARY。
    • SUBQUERY：在SELECT或WHERE列表中包含了子查询。
    • DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生），MYSQL会递归执行这些子查询，把结果放在临时表里。
    • UNION：如果第二个SELECT出现在UNION之后，则标记为UNION；如果UNION包含在FROM子句的子查询中，外层SELECT将标记为：DERIVED。
    • UNION_RESULT：从UNION表获得结果的SELECT语句。
  • table：表明数据是来自哪张表。
  • partitions：表明分区。
  • type：显示查询使用了哪种访问类型。从优到差依次是：
    • system：表只有一行记录（等于系统表）
    • const：表示通过索引一次就可以找到了。const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。比如将primary key置于where列表中，MYSQL就能将该查询转换为一个常量。
    • eq_ref：唯一性索引扫描，对于每个索引，表中只有一条记录与之匹配。常见于主键扫描或唯一索引扫描。
    • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，但它可能会找到多个符合条件的行，所以他该属于查找和扫描的混合体。
    • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在where语句中出现了between、<、>、in等查询，这种范围扫描索引扫描比全表扫面更优，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
    • index：Full Index Scan。index与ALL的区别是index类型只便利索引树。也就是说all和indx虽然都是读全表，但是index是从索引中读取的，而all是从硬盘中读取的。
    • ALL：Full Table Scan。
  • possible_keys：显示可能应用在这张表中的索引，结果可能是一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被实际用到。
  • key：实际使用的索引。查询中若使用到了覆盖索引（查询的字段和数量和顺序和建立的索引的字段顺序和数量相同），则该索引仅出现在key列表中。
  • key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度。
  • ref：表示表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
  • rows：根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数。
  • filtered：符合某条件的记录数百分比。
  • Extra：包含不适合在其他列中显示但十分重要的额外信息。
    • Using filesort：表明MySQL会对数据使用外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序叫做filesort。
    • Using temporary：表明MySQL需要使用临时表来存储结果。常见于order by和group by语句。
    • Using index：表明select操作中使用了Covering Index（覆盖索引），避免访问了表的数据行。如果同时出现了using where，表明索引被用来执行索引键的查找；如果没有出现using where，表明索引用来读取数据而非执行查找动作。
    • Using where：表示MySQL服务器将在存储引擎检索行后再进行过滤。是所有带where字句的查询都会显示"Using where"。
    • Using join buffer：表明MySQL在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。
    • Impossible where：表明该where语句会导致没有符合条件的行。
    • Select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MAX/MIN操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
    • distinct：优化DISTINCT操作，再找到第一匹配的元组后即停止找同样值的动作。

索引优化

避免索引失效

  1. 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。也就是查询从索引的最左前列开始并且不跳过复合索引中间列。
  2. 不在索引列上做任何操作，如计算、函数、类型转换等，这样会导致索引失效而转向全表扫描。
  3. 存储引擎不能使用索引中范围条件右边的列 
    SELECT * FROM user WHERE name = 'username' AND age > 8 AND city = 'Beijing'
    如上SQL语句中，在age>8后的条件无法使用索引。
  4. 尽量使用覆盖索引，减少SELECT *的出现。
  5. MySQL在使用<>、 != 的时候无法使用索引从而转向全表扫描。is null、 is not null 也无法使用索引。
  6. like以通配符开头的条件筛选也会是MySQL的索引失效转向全表扫描。 如果必须使用like '%...%，要做到覆盖索引。
  7. 字符串需要加单引号，不然会导致索引失效。
  8. 尽可能地少使用OR，使用它会导致索引失效。 去使用全值匹配

查询截获分析

优化步骤：

  1. 慢查询的开启并捕获
  2. EXPLAIN+慢SQL语句分析
  3. show profile查询SQL在MySQL服务器里面执行细节和生命周期情况
  4. SQL数据库服务器的参数调优

查询优化

inorexists?->小表驱动大表

    for (int i=0; i<i.MAX; i++) {
        for(int j=0; j<j.MAX; j++){
            //codes
        }
    }
    //i.MAX<j.MAX是优于i.MAX>j.MAX的
  • 对于SELECT * FROM tableA WHERE id in (SELECT id FROM tableB)可以理解为： 
        for SELECT id FROM tableB
        for SELECT * FROM tableA WHERE tableA.class_id = tableB.id

当tableB的数据集必须小于tableA的数据集时，用in优于exists。

  • 对于SELECT * FROM tableA WHERE EXISTS (SELECT constValue FROM tableB WHERE tableB.id = tableA.id)可以理解为：
    for SELECT * FROM tableA
        for SELECT * FROM tableB WHERE tableB.id = tableA.id

当tableA的数据集小于tableB的数据集时，用exists优于in。

对于EXISTS(subQuery)，其子查询部分只返回true或者false，因次select后面比较自由。MySQL实际执行时会忽略select清单。

排序优化

使用ORDER BY时，MySQL会使用两种排序方法，一直是索引index排序，另一种是filesort。显然这两者方式，通过索引排序效率更高。
满足如下两种情况，MySQL会通过index排序：

  1. ORDER BY清单排列顺序和索引的顺序匹配（可在中间打断，但不可以颠倒位置，缺少中间列）。
  2. WHERE子句和ORDER BY子句条件列组合和索引的顺序匹配（~）。
    如果WHERE使用素引的最左前缀定义为常量，则order by能使用索引
#对于索引a_b_c(a,b,c)，以下order by子句均可使用索引排序
WHERE a = const ORDER BY b,c
WHERE a = const AND b = const ORDER BY c
WHERE a = const AND b > const ORDER BY b, c

对于某索引，建立顺序为col1、col2，ORDER BY col1 ASC, col2 DESC子句无法使用index排序。

当无法使用索引进行排序时，MySQL就会使用filesort方式进行排序。
filesoet有两种算法：双路排序和单路排序。

对于GROUP BY的优化与ORDER BY大同小异。

慢查询日志

默认条件下，MySQL数据库默认不开启慢查询日志。

set global slow_query_log=1

使用上述命令可以开启当前数据库的慢查询日志。当MySQL重新启动后，需要再次手动打开慢查询日志。

查看阙值：

    SHOW VARIABLES LIKE 'long_query_time%';

修改阙值：

    set global long_query_time = number;

修改后，需要重新连接或者打开一个会话才可以看到修改值。或者使用如下命令查看：

    show global variables like 'long_query_time%';

在日志文件中查看具体情况进行分析。

show profile

show profile是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可用于SQL的调优测量。
默认情况下，参数处于关闭状态，并保存最近15次的运行结果。
MySQL 5.0.37版本以上支持PROFILING调试功能。
开启功能：

    set profiling = on

查看分析：

    show prifile

诊断SQL：

    show profile X for query Query_ID

其中X可以是ALL BLOCK IO CONTEXT SWITCHES CPU IPC MEMORY PAGE FAULTS SOURCE SWAPS 诊断结果的status中如果出现如下几种，那么SQL代码极大可能出现了问题，需要进行优化。

  • converting HEAP to MyISAM：查询结果太大，内存不够用。
  • Creating tmp table：创建临时表：拷贝数据到临时表，用完再删除。
  • Copying to tmp table on disk：把内存中临时表复制到磁盘上！！！
  • locked：被锁住。

全局查询日志

全局查询日志用于保存所有的sql执行记录，该功能主要用于测试环境，不要再生产环境使用。 启动查看：

    set global general_log = 1 ;
    set global log_output = 'TABLE';
    # SQL
    select * from mysql.general_log；

MySQL锁

锁是计算机协调多个进程或线程并访问某一资源的机制。

根据对数据操作的粒度分为表锁和行锁。
根据对数据的操作类型分为读锁（共享锁）和写锁（排它锁）

手动增加表锁：

    lock table tableName read/write;

查看加表锁的表：

    show open tables;
    # In_use 为1的即为加锁的

解除表锁：

    unlock tables;

表锁

表锁偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
建表时要声明存储引擎为MyISAM。

  • 给表增加读锁后：
    对于这个session，可以读此表，但是不可以对此表进行增删改。也不可以对其他表进行操作。
    对于其他session，可以读此表，对此表进行增删改操作会发生阻塞，直到资源释放。
  • 给表增加写锁后： 对于这个session，可以读此表，也可以对此表进行增删改。但不可以对其他表进行操作。
    对于其他session，对此表进行任何操作会发生阻塞，直到资源释放。

读锁会阻塞写，但不会阻塞读。而写锁会把读写都堵塞。

行锁

行锁偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生所冲突的概率最低，并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是采用事务，二是采用了行级锁。

无索引会导致行锁升级为表锁。

如何锁定一行？

    # SQL + for update;
    SELECT * FROM tableName WHERE id=idNum FOR UPDATE;
    # util 'commit;'

分析行锁定：

    show status like 'innodb_row_lock%';

使用如上命令可以检查innodb_row_lock状态变量和分析系统上的行锁争夺情况。
结果5中状态量：

  • innodb_row_lock_avg ：每次等待所需平均时间
  • innodb_row_lock_waits ：系统启动后到现在总共等待的次数
  • innodb_row_lock_time ： 系统从启动到现在锁定的总时间长度
  • innodb_row_lock_current_waits ：当前正在锁定的数量
  • innodb_row_lock_max ：系统启动到现在等待最长的一次所花费的时间

行锁优化建议：

  • 尽可能让所有的数据检索都通过索引完成。
  • 合理设计索引，尽量缩小锁的范围。
  • 尽可能减少检索条件，避免间隙锁。
  • 尽量控制事务大小，减少锁定资源量和时间长度。
  • 尽可能降低级别事务隔离。

间隙锁：当使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做间隙（GAP）。
间隙锁的危害：当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

页锁

开锁和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

主从复制

将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

基本原理： MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。
MySQL复制是基于主服务器在二进制日志中跟踪所有对数据库的更改。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到日志的数据。
当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。

image.png

MySQL复制过程分为三步：

  1. master将改变记录到二进制文件（binary log）。这些记录过程叫做二进制日志事件，binary log events；
  2. slave将master的binary log events拷贝到它的中继日志（relay log）；
  3. slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制时异步的串行化的。

作用：

  1. 主数据库出现问题，可以切换到从数据库。
  2. 可以进行数据库层面的读写分离。
  3. 可以在从数据库上进行日常备份。
avatar
文章
阅读
粉丝