MySQL

MySQL高级

Posted by Beyonderwei on 2020-01-09
Words 8.1k and Reading Time 29 Minutes
Viewed Times

一、架构

​ MySQL采用分层结构设计，可在不同需求场景中利用不同的存储引擎，插件式的存储引擎架构将查询处理和其他的系统任务以及数据存储相分离。

1. 连接层

最上层是客户端和连接服务，包含本地的socket和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信，完成连接处理、授权认证等安全方案。连接层上引入了连接池，为通过安全认证的客户端提供线程，在该层可实现SSL安全连接，服务器还会对每个安全接入的客户端进行验证，赋予操作权限。

2. 服务层

完成大多数的核心服务功能，如SQL接口，完成缓存的查询，SQL的分析优化及内置函数的执行，跨存储引擎的功能实现也在该层，如过程、函数等。在这一层，服务器会解析查询，并创建相应的内部解析树，完成相应的优化，如查询表的顺序、是否利用索引、最后生成相应的执行操作，如果是select操作，服务器会查询内部缓存，如果缓存的空间足够大，在解决大量的读环境中能很好的提升性能。

3. 引擎层

存储引擎实现了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通讯，不同的存储引擎的特性不同，可根据需求选取，主要用到的是MyISAM和InnoDB。

4. 存储层

将数据存储在裸设备的文件系统之上，并完成与存储引擎的交互。

二、存储引擎

1. InnoDB

​ 默认的存储引擎，支持外键、事务，行锁（适合高并发），缓存索引和真实数据，对内存的要求高，内存的大小对性能有决定性影响，表的空间大，更关注于事务，安装时默认安装该存储引擎。

2. MyISAM

​ 不支持外键、事务，采用表锁，在操作一条记录时也会锁住整张表，不适合并发场景，只缓存索引，不缓存真实数据。表空间小，更关注性能。安装时默认安装该存储引擎。

3. Percona的XtraDB

​ Percona对MySQL进行了改进，使其在功能和性能上都有了很大的提升，使用XtraDB代替了InnoDB，在性能和并发上做的更好。阿里等公司使用该引擎，或进一步优化。

三、 索引

  • 索引时帮助MySQL高效获取数据的数据结构，排好序的快速查找数据结构。
  • 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构通过引用（指针）的方式引用数据，这样就可以在此数据结构的基础上实现高级算法，这种数据结构就是索引。
  • 索引也是一张表，该表保存了主键与索引字段，并指向实体表记录。
  • 主键本身就是索引。

索引结构

  • Btree索引 （常用）
  • Hash索引
  • full-text全文索引
  • R-Tree索引

① 数据结构

B树（多路搜索树）

​ 通常所说的索引

B+树

​ 聚集索引、次要索引、复合索引、前缀索引、唯一索引

② 优缺点

  • 优点：
    • 提高数据的检索效率，降低数据库的IO成本
    • 通过索引对数据进行排序，降低数据的排序成本，降低CPU的消耗
  • 缺点：
    • 索引也是一张表，会占用空间
    • 对表进行insert update delete时不仅要保存数据，还要维护添加了索引的字段，会调整因为更新所带来的键值变化后的索引信息。

③ 索引分类

单值索引：

​ 一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：

​ 索引列的值必须唯一，允许有空值

复合索引

​ 一个索引包含了多个列

④ 基本语法

创建

CREATE [UNIQUE] INDEX indexName ON tableName(columnName);

ALTER tableName ADD [UNIQUE] INDEX [indexName] ON(columnName);

  • 加UNIQUE表示唯一索引
  • 单值索引：一个字段 多值索引：多个字段
删除

DROP INDEX [indexName] ON tableName;

查看

② 注意点

  • 索引会影响到查找和ORDER BY 的排序
  • 一般不实际删除表的数据，而是通过标志位（is_deleted）的形式使之无效，以此来维护索引的结构，避免查到一半查不到了，这种情况导致的查询变慢可通过重建索引来解决。
  • 索引是提高效率的一个因素，需要根据点击数据不断的优化。
  • 经常更新删除的数据不适合添加索引
  • 一张表的索引最好不要超过5个

四、 索引与SQL

1. 创建索引

① 单值索引

create index idx_user_name on user(name);

索引命名：

  • 以idx 开头，接着是表名和创建索引的字段名
  • 各部分以下环线分开，这样通过创建了索引的字段来查询就会很快。

② 复合索引

create index idx_user_nameEmail on user(name,email);

索引命名：

  • 多个索引字段按小驼峰命名接在表名后

2. SQL性能下降

① 性能下降原因

​ 主要时执行时间长或等待时间长，原因如下：

  • 查询语句写的不好
  • 索引失效
  • 多表关联查询，关联的表太多（设计缺陷或万不得已）
  • 服务器调优和参数设置等问题

② Join查询

3. SQL的执行顺序

  • 手写的SQL顺序

  • MySQL内部的解析顺序

  • 执行的顺序

4. 常用SQL JOINS

  • 左连接(右连接同理)

  • 内连接

  • 左连接去重

  • 全连接(MySQL不支持)，通过union去重，连接左连接和右连接

五、 创建索引 OR 不创建

1. 需要创建索引的情况

  • 主键自动创建唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引，即作为外键的字段
  • 单值、复合索引的选择（高并发下倾向创建复合索引）
  • 查询中排序的字段（排序字段若通过索引去访问将大大提高排序速度）
  • 查询中统计或分组字段

2. 不需要创建索引

  • 频繁更新的字段不适合创建索引，（因为更新也要更新索引）
  • Where条件里用不到的字段不建索引
  • 表记录太少（300万以内不用建）
  • 重复且分布均匀的表字段不建索引，如果某个字段的重复内容过多（如性别、国籍等），为他建索引没有什么实际效果，反而增减了维护索引的成本。

3. 索引的选择性

​ 索引的选择性表示索引列中不同值得数目与表中记录数的比，例如，表中有100条记录，索引列有99个不同的值，那么这个选择性就是99/100=0.99。选择性越接近1，这个索引的效率就越高。

六、 性能分析

1. MySQL Query Optimizer(查询优化器)

  • MySQL中有专门负责优化SELECT语句的优化器模块，该模块主要通过计算分析系统中手机的统计信息，为客户端请求的Query提供他人为最优的执行计划（但是他认为最优的检索方式不见得是DBA人为最优的）
  • 当客户端向MySQL请求一条Ｑｕｅｒｙ时，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先对整条Query中的查询条件进行简化和转换，如去掉一些无用或者显而易见的条件、结构调整等，然后分析Query中的Hint信息（如果有）,看监视Hint信息是否可以完全确定Query的执行计划，如果没有Hint或Hint信息不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行谐响应的计算分析，然后得出最后的执行计划。

2. MySQL的常见瓶颈

  • CPU：CPU饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
  • IO：磁盘的IO瓶颈发生在装入数据远大于内存容量的时候
  • 服务器硬件的性能瓶颈：top，free，iostat，vmstat来查看系统的性能状态。

七、Explain（计划）

1. 简介

​ 使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析查询语句或是表结构的性能瓶颈。

2. 作用

  • 查看表的读取顺序（id，table字段）
  • 展现数据读取操作的操作类型（select_type）
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

3. 使用

​ Explain + SQL语句，得到分析结果。

① 包含信息

原则：小表驱动大表。逐步调优，实现先加载小的，后加载大的。

*id：

​ select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，NULL最后执行。

  • id相同，执行顺序由上至下（table字段）
  • id不同，如果是子查询，id的序列号会递增，id值越大，所对应的table字段的表越先执行。
  • id相同和不同：id值越大，优先级越高，越先执行，id相同的部分是一组，从上往下执行。

注：table中的derivedx表示衍生表，x代表由谁执行后衍生的。

select_type

  • SIMPLE

    简单的select查询，查询中不包含子查询或UNION

  • PRIMARY

    查询中若包含任何复杂的子部分时，最外层查询被标记为子查询（最后加载的那个）

  • SUBQUERY

    在SELECT或WHERE列表中包含了子查询

  • DERIVED

    在FROM列表中包含的子查询被标记为DERIVED（衍生表），MySQL会递归执行这些子查询，把结果放在临时表中。

  • UNION

    若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED

  • UNION RESULT

    从UNION表获取结果的SELECT

table

​ 关于哪一张表

*type

​ 访问类型排列，显示了查询使用了哪种类型，从好到差的排列顺序为：systerm>const>eq_ref>ref>range>index>ALL

注：ALL表示全表扫描，一般来说至少达到range级别，最好能达到ref。（达到百万级别的表才需要优化）

  • systerm：表只有一行记录（等于系统表），是const类型的特例，平时不会出现，可忽略不计
  • const：表示通过索引一次就找到了，const用来比较primary key或unique索引，因为只匹配一行数据，所以很快，如果将主键至于WHERE列表中，MySQL就将该查询转换为一个常量。
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。（只有一条符合匹配）
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值得行，然而它可能会找到多个符合条件得行，所以它应该属于查找和扫描得混合体。（有多条符合匹配）
  • range：只检索给定范围得行，使用一个索引来选择行，key列表显示了使用了哪些索引。一般就是在你得where语句中出现了between、<、>、in等得查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引得某一个点，而不用扫描全部索引。（如id大于多少、小于多少）
  • index：Full Index Scan ，index于All的区别为index只遍历索引树。这通常要比ALL快，因为索引文件通常要比数据文件小。也就是说，虽然All和index都是读全表，但是index是从索引中读取的，而all是从硬盘中读取的。
  • all：全表扫描
possible_keys

​ 显示可能用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但在该查询中不一定被使用到

*key

​ 表示实际使用到的索引，如果位NULL则表示没有使用索引

​ 查询中若使用到了覆盖索引，则该索引仅出现在key列表中。（覆盖索引： select的数据列只用从索引中取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取文件，即查询列要被所建的索引覆盖。如下图：）

  • 如果用到覆盖索引，select列表中只取出需要的列，不可select*

key_len

​ 表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精度的情况下，长度越短越好。（在建了索引的前提下，查询条件越精确，key_len的值越大）

​ key_len显示的值为索引字段的最大可能长度，并非实际使用长度，该值是根据表定义计算得到，不是通过表内检索出来的。

*ref

​ 显示索引的哪一列被使用了（索引的具体引用情况），如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。格式为（库名.表名.字段名 或 const）

*rows

​ 表示每张表有多少行被优化器查询过（加起来越少越好）。

Extra（重要）

​ 包含不适合在其他列显示，但十分重要的额外信息。

注意：要么不建索引，要么就按照索引的顺序去查，不然很容易产生文件内排序、临时表等问题。

  • Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引排序进行读取，MySQL无法利用索引完成的排序操作称为“文件排序”，需要尽快优化。MySQL自己在内部产生了一次排序，会导致性能下降。因此尽可能的使用原本创建好的索引和索引的顺序。

  • Using temporary：使用了临时表保存中间结果，MySQl在对中间结果排序时使用临时表，常见于排序order by 和分组查询 group by。（一定要赶紧优化，需要见表、删表、性能急速下降,如下图）

  • Using index：

    • 表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。

    • 如果同时出现using where ，表明索引被用来执行索引键值的查找

    • 如果没有同时出现using where，表明索引用来读取；数据而非执行查找动作。

  • Using where：表明使用了where过滤

  • Using join buffer：使用了连接缓存

  • impossible where：where子句的值总是false，不能用来获取任何元组

  • select tables optimized away（了解）：在没有GROUP BY 子句的情况下，基于索引优化MIN/MAX操作或者对于M有ISAM存储引擎优化COUNT（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

  • distinct（了解）：优化distinct操作，在找到第一匹配的元组胡即停止找同样值得动作。

八、索引优化

1. 索引分析

① 单表

​ 范围需求字段建了索引以后，其后的索引会失效，降低效率。

② 两表

​ 由左右连接的特性来决定，左连接则左表一定全要，从右表搜索，此时要在右表建立索引。

​ 注：左连接加右表，右连接加左表，如果DBA已经建好索引，可以通过改变左右连接来提高效率。

③ 三表

​ 依然按照两表中的原则，索引最好设置在需要经常查询的字段中。

④ 结论

  • 尽可能减少Join语句中的NestedLoop的循环总次数，永远用小表驱动大表。
  • 优先优化NestedLoop的内层循环
  • 保证Join语句中被驱动表上Join条件字段已经被索引
  • 当无法把凭证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。

2. 索引失效

① 最好使用全值匹配

​ 查询条件中直接用值匹配，如 name = “张三”

② 最佳左前缀法则（重要）

​ 如果索引中引入了多列，要遵守该原则，查询从索引的最左前列开始（如果顺序不同，没有跳过某个列，MySQL优化器会进行优化），并且不跳过索引中的列。

​ order by 如果不按照索引顺序，几乎都会产生文件排序。

​ group by 如果不按照索引顺序，几乎都会产生文件排序和临时表。group by 分组之前需要排序。

③ 不再索引上做任何操作

​ 在索引上做计算、函数、（自动或手动）类型转换，都会导致索引失效，转为全表扫描。

④ 范围条件右边不可用

​ 存储引擎不能使用范围条件右边的列。如 pos = 1 and age > 18 and name = “zhangsan” 此时，最后 name = “zhangsan” 就无法使用索引，但前面两个就能用到。

⑤ 使用覆盖索引

​ 尽量使用索引覆盖（只访问某些或全部索引列的查询），减少使用select *

⑥ 避免使用 != 和 <>

​ 8.0版本进行了优化，但是还是range级别，Extra为 useing index condition。但是还是尽量避免使用。

举例：where name != “zhangsan”

⑦ 避免使用is (not) null

​ name is null； 可能在高版本会用到索引，待测

⑧ like以通配符开头

​ 以通配符开头（’%abc‘）索引失效，导致全表扫描。

​ like 以百分号结尾，type 为 range，因此like “abc%” 后的也不能被用到索引。

解决like “%字符串%” 时，索引不被使用的解决办法

​ 通过覆盖索引来解决问题，可以从ALL提升到index

⑨ 字符串不加单引号（重要）

​ varchar类型的数据在进行匹配的时候，如果不加单引号，可能导致隐式的类型转换，如数字与字符串之间的转换，因此会导致索引失效。

⑩少用or

​ 如： name = “zhangsan” OR name = “lisi”, 会导致索引失效。8.0以上可能是range。

3. 一般性建议

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

九、 查询截取分析

0. 分析优化过程

  • 观察，至少跑一天，看看生产中慢SQL的情况
  • 开启慢查询日志，设置阈值，比如超过三秒的SQL语句，并抓取
  • explain + 慢SQL分析
  • show profile，查询SQL在MySQL服务器里面的执行细节和生命周期情况
  • DBA进行SQL数据库服务器的参数调优

1. 查询优化

① 永远小表驱动大表

​ 即小的数据集，驱动大的数据集，从而减少IO次数，

  • B表的数据集小于A表时，用in优于exists

  • A表的数据集小于B表时，用exists优于in

② in和exists

  • EXISTS

    SELECT ... FROM table WHERE EXISTS(subquery)

    将主拆线呢的数据，放到子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据结果是否得以保留。

  • 提示：

    • EXISTS(subquery)只返回RTUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或SELECT ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没区别
    • EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，可实际验证是否有效率问题
    • EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来代替，何种最优需要具体问题具体分析。

③ order by关键字优化

最佳左前缀原则依然有效
  • order by后所用的索引要按照创建的顺序，不能间断，否则会产生文件排序。跟前面的内容无关。
  • 默认是升序，如果同时使用降序和升序，即使按照索引顺序，也会导致文件排序。除非所有索引都降序，且按照索引顺序，就不会产生文件排序。
filesort排序算法
  • 双路排序：MySQL4.1之前使用的是该算法，取一批数据需要进行两次扫描。
  • 单路排序：4.1版本以后采用单路算法，一次读取，在buffer中排序，只用读一次，总体好于双路排序。
  • 结论：单路排序可能会因为sort_buffer的太小产生多次IO，还不如双路排序，因此需要进行相应的配置。
提升性能方法

  • 加order by 就尽量不要SELECT *，因为如果产生了filesort就会增加缓存，把sort_buffer_size给用满了，导致产生多次IO。

  • 有filesort可尝试提高sort_buffer_size，根据系统能力去提高，该参数针对每个进程。

  • 有filesort可尝试提高max_length_for_sort_data，太大，数据总量超过sort_buffer_size会导致高的磁盘IO和低的处理器利用率。

总结

  • order能使用最左前缀索引

  • 如果最左前缀为常量，order by也能使用索引

④ group by关键字优化

​ group by 的实质是先排序后分组，遵照索引建的最佳左前缀，当无法使用索引列，增大sort_buffer_size 和 max_length_for_sort_data的参数设置，

​ where高于having ，因此能写在where里的条件就不要到having中去限制。

2. 慢查询日志

① 简介

  • MySQl的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值得SQL，则会记录在慢查询日志中。
  • long_query_time得默认值是10，即10秒钟

② 使用

  • 默认MySQL数据库没有开启，需要我们手动得开启慢查询日志。非调优需要不建议开启，因为开启慢查询会多少对性能造成影响，慢查询日志支持将日志写入文件。

  • 命令：

    • SHOW VARIABLES LIKE '%slow_query_log%'; 查看是否开启及存储文件
    • set global show_query_log=1; 开启慢查询（只对当前数据库有效，重启后就会失效）
    • set global long_query_time=3; 设置阈值为3 （本库有效，重启无效，重开一个连接通过show才能查到）
    • SHOW global status like '%Slow_queries%'; 查询日志的条数

  • 永久开启（不建议）

    • 修改my.cnf在[mysqld]下增加或修改参数
    • slow_query_log=1
    • slow_query_log_file=/var/lib/mysql/xxx.log (缺省得文件名为主机+slow.log)
    • long_query_time = 3
    • log_output=FILE

  • 执行测试

    可通过select sleep(5); 等方式来执行语句，这样如果阈值低于5秒得话，该语句会被记录。

③ 分析工具

​ 在生产中，手工分析日志比较麻烦，因此MySQL提供了日志分析工具，mysqldumpslow。通过mysqldumpslow --help 查看如何使用，查询出相关数据

  • s：按照何种方式排序
  • c：访问次数
  • l：锁定时间
  • r：返回记录
  • t：以查询总时间方式从大到小排序
  • al：平均锁定时间
  • ar：平均返回记录
  • at：平均查询时间
  • g：后面搭配正则表达式，大小写不敏感。

3. 批量数据脚本

​ 以插入1000W条数据为例

  • set global log_bin_trust_function_creators=1; 开启二进制方法创建

    或在[mysqld] 下加上 log_bin_trust_function_creators=1

  • 创建函数，保证每条数据都不同

    • 随机产生字符串

  • 随机产生编号

  • 创建存储函数,一次插入批量数据，如几十万的数据(插入其他数据同理)

  • 调用

    • DELIMITER ; 将结束符改为分号
    • CALL 函数名来调用

4. Show Profile

简介：

​ 是mySQL提供的可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量，默认是关闭状态，并保存最近15次的运行结果。

命令：

  • show variables like 'profiling;' 查看是否开启

  • set profiling=on; 打开profile

  • show profiles; 查看执行过的SQL，包含执行时间和SQL语句、查询ID信息。

  • show profile cpu,block io for query 查询ID; 列出上面某个查询的详细过程信息。一条SQL的完整生命周期。

  • 结果中需要注意的点：

    • converting HEAP to MyISAM 查询结果太大，内存不够，需要往磁盘存储
    • Creating tmp table 创建了临时表（拷贝数据到临时表，用完再删）
    • Copying to tmp table on disk 把内存中临时表复制到磁盘，危险
    • locked

  • 除了cpu block io还有其他的开销信息，如下表

5. 全局查询日志

​ 只能在测试环境用，不能在生产环境用。

① 配置使用

在my.cnf设置如下

  • general_log=1 开启
  • general_log_file=/path/logfile 日志文件路径
  • log_output=FILE 输出格式

② 命令使用

  • set global general_log=1;
  • set global log_output='TABLE';
  • 然后所编写的SQL语句会记录到mysql数据库里的general_log表
  • select * from mysql.general_log; 查看该表的数据

八、 MySQL锁机制

1. 简介

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

​ 在数据库中，除传统的CPU、RAM、IO等资源的征用以外，数据也是一种很多用户共享的资源，如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是并发访问性能的一个重要的因素，锁对于数据库尤为的重要，也更复杂。

2. 分类

  • 读锁（共享锁）：针对同一份数据，多个读操作可同时进行，互不影响
  • 写锁（排他锁）：当前写操作未完成之前，会阻断其他的写锁和读锁。

3. 表锁

​ 偏向于MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度低。

MyISQM在执行select前会对所涉及的表加读锁，在执行增删改之前，会对涉及的表加写锁。

① 命令

  • show open tables; 查看表上加过的锁，0未锁
  • lock table 表名 read(write); 给表加读锁或者写锁
  • unlock tables; 解除库中所有的锁

注意点（针对MyISAM引擎）：

  • 加表的读锁以后，只能读该表，不能读其他表或写这个表，一定要解除了自己加的这个锁以后才能做其他操作。其他连接要进行写操作要被堵塞。
  • 对表加写锁后，该连接可读、改自己锁的表，同样在没有解锁前不能操作其他表。其他表对该表查询和写同样阻塞。

② 表锁分析

show status like 'table%'; 可以查看表锁的情况，

  • Table_locks_immediate：产生表锁的次数，表示可以立即获取锁的查询次数，每立即获取锁值加一
  • Table_locks_waited：出现表级锁定征用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加一），此值高说明存在着严重的表级锁争用情况。

注意： MyISAM的读写锁调度是写优先，因此不适合作为写为主的情况，写锁后其他线程不能做任何操作，大量更新会使查询很难得到锁，从而造成永远阻塞。

4. 行锁

​ 偏向于InnoDB引擎，开销大、加锁慢，会出现死锁，锁的粒度小，发生冲突的可能小，并发高。InnoDB支持事务，并采用了行锁。 （复习事务的隔离级别，和数据库事务。）

① 行锁变表锁

​ 重要提示： 索引失效会导致行锁变表锁，常出现索引失效就是自动类型转换，即varchar数据不加单引号。这时操作数据会导致索引失效，操作过程所住整个表。

② 间隙锁危害

​ 当我们用范围条件而不是相等条件去检索数据的时候，并请求共享和排他锁时，InnoDB会给符合条件的已有数据的索引加索，对于键值在条件范围内但不存在的记录，叫做间隙（GAP）。

​ 当锁定一个范围的键值以后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候，无法插入锁定范围内的任何数据

③ 优化分析

  • show status like 'innodb_row_lock'; 查看行锁的状态
  • innodb_row_lock_current_waits: 当前正在等待的锁的数量
  • innodb_row_lock_time： 从系统锁定到现在锁定的总时间长度
  • innodb_row_lock_time_max： 从系统锁定到现在锁定最长的一次所花时间
  • innodb_row_lock_time_avg： 从系统锁定到现在锁定的平均时长
  • innodb_row_lock_waits：系统启动后到现在总共等待的次数

④ 优化建议

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

九、 主从复制

① 复制过程

  • master将干煸记录到二进制日志（binary log）中，这些日志叫做二进制日志事件，binary log events。

  • slave将master的binary log events拷贝到它的中继日志（relay log）

  • slave重做中继日志中的事件，将改变应用到自己的数据库中，MySQL复制是异步且串行化的。

② 复制原则

  • 每个slave只能有一个master
  • 买个slave只能有一个唯一的服务器ID
  • 每个master可以有多个slave

③ 复制的最大问题

  • 延时

④ 一主一从

  • MySQL版本一致，且以后台服务运行
  • 主从配置在[mysqld]节点下，都是小写
  • 主机和从机都要关闭防火墙 service iptables stop 关闭linux防火墙
主机
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 主服务器唯一ID[必须]
server-id=1
# 启用二进制日志[必须]
log-bin="本地路径/mysqlbin"
# 启用错误日志[可选]  主从复制不成功可以记录下来
log-err="本地路径/mysqlerr"
# 根目录[可选]
basedir="自己本地路径"
# 临时目录[可选]
tmpdir="自己本地路径"
# 数据目录[可选]
datadir="本机的目录/Data"
# 主机读写都可以
read-only=0
# 设置不需要复制的数据库（如mysql库）[可选]
binlog-ignore-db=mysql
# 设置需要复制的数据库 [可选] 不写表示都复制
binlog-do-db=需要复制的数据库名

  • 在主机上给从机授权，登录后执行

    GRANT REPLICATION SLAVE ON *.* TO '用户名' @‘从机IP’ IDENTIFIED BY '密码';

  • flush privileges; 查询master的状态

  • show master status; 查看主机二进制文件、复制和不复制的数据库信息

从机
1
2
3
4
# 主服务器唯一ID[必须]
server-id=2
# 启用二进制日志[可选]
log-bin="本地路径/mysqlbin"
  • ChANGE MASTER TO MASTER_HOST='主机ip',
MASTER_USER='用户名',
MASTER_PASSWORD='密码',
MASTER_LOG_FILE='mysql.具体数字', MASTER_LOG_POS=具体值;

    MASTER_LOG_FILE和MASTER_LOG_POS的值是在主机中通过show master status 查询到的。

  • start slave; 重启服务，开启从服务器复制功能

  • show slave status\G 以下两个参数都是yes说明配置成功

    • Slave_IO_Running：Yes
    • Slave_SQL_Running：Yes

  • stop slave; 关闭复制，如果要重启，需要重新更改MASTER_LOG_FILE和MASTER_LOG_POS。

十、配置文件

  • log-bin 二进制日志，用于主从复制
  • log-error 错误日志，用于记录错误
  • log 查询日志，用于记录查询，当需要记录哪些SQL执行时间很慢时可以记录下来，用于优化
  • ls -lF|grep ^d 进入容器后，查询创建的库的位置
  • 数据文件
    • frm文件 存放的数据库表结构
    • myd文件 存放的表数据
    • myi文件 存放的表索引

本文为作者原创文章，未经作者允许不得转载。

...

...

00:00
00:00
FEATURED TAGS
MySQL
FRIENDS