Fork me on GitHub

MySQL高级知识点总结

MySQL系统架构介绍

MySQL安装

修改mysql字符集编码

1
2
3
4
5
6
7
8
9
10
[clinet]
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci
[mysql]
default-character-set=utf8

MySQL文件

  • 二进制日志log-bin: 用于主从复制
  • 错误日志log-error: 默认关闭的,记录严重的错误和警告信息、每次启动和关闭的详细信息。
  • 查询日志log: 默认关闭,记录查询的sql语句,如果开启会降低mysql的整体性能,因为记录日志也会消耗系统资源。
  • 数据文件:
    1. 主目录: Windows:D:/MySQL_HOME/data; Linux: 默认为 /var/lib/mysql
    2. frm文件: 存放表结构
    3. myd文件: 存放表数据
    4. myi文件: 存放表索引
  • 配置
    1. windows: my.ini文件
    2. linux: my.cnf文件(默认位置: /usr/my.cnf)
      扫描顺序: /etc/my.cnf,/etc/mysql/my.cnf,/usr/local/mysql/etc/my.cnf,~/.my.cnf

MySQL总体概览

Mysql和其他数据库相比,MySQl有点与众不同,他的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种可以根据业务需求和实际需求需要选择合适的存储引擎。
MySQL总体架构图
各层作用

MySQL存储引擎

  • 查看命令: show engines , show variables like "%storage_engine%"
  • 主要有InnoDB和MYISAM
    InnoDB和MYISAM对比

MySQL索引优化分析

性能下降SQL慢,执行时间长,等待时间长原因

  • 查询语句写的烂
  • 索引失效
    1. 单值—create index idxName on tableName(feild)
    2. 复合—create index idxName on tableName(feild1,feild2)
  • 关联查询太多join
  • 服务器调优及各个参数的设置(缓冲、线程数等)

常用的通用join查询

  • SQL执行顺序
    手写:
    手写
    机读:
    机读
    总结:
    总结

  • 7种join理论图

    1. 内连接: select * from tableA A inner join tableB B on A.key=B.key
      内连接
    2. 左连接: select * from tableA A left join tableB B on A.key=B.key
      左连接
    3. 左外连接: select * from tableA A left join tableB B on A.key=B.key where B.key is NULL
      左外连接
    4. 右连接: select * from tableA A right join tableB B on A.key=B.key
      右连接
    5. 右外连接: : select * from tableA A right join tableB B on A.key=B.key where A.key is NULL
      右外连接
    6. 全连接: Oracle: select * from tableA A full outer join tableB B on A.key=B.key
      MySQL: select * from tableA A left join tableB B on A.key=B.key union select * from tableA A right join tableB B on A.key=B.key
      全连接
    7. 两表的独立数据: Oracle: select * from tableA A full outer join tableB B on A.key=B.key where A.key is NULL or B.key is NULL
      MySQL: select * from tableA A left join tableB B on A.key=B.key where b.key is NULL union select * from tableA A right join tableB B on A.key=B.key where a.key is NULL
      两表的独立数据

索引简介

索引是什么?

官方定义为索引是数据结构,目的在于提高查询效率,可以类比字典,简单理解为排好序的快速查找数据结构
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
我们平常说的索引,一般都是B树(多路查找搜索树,并不一定是二叉树)结构组织的索引。其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都使用B+树索引,统称索引。除了B+树这种索引,还有哈希索引。
B树索引

优势

  1. 提高数据检索的效率,降低数据库的IO成本
  2. 通过索引对数据进行排序,降低数据排序的成本,降低CPU的消耗

劣势

索引也是一张表,保存了主键与索引字段,并指向实体表的数据,所以索引列也占空间。虽然索引提高可产讯速度,同时却会降低更新表的速度。因为更新表时,MySQL不仅要保存数据,还要更新索引信息。
索引只是提高效率的一个因素,如果MySQL有大量数据,就需要花时间研究建立最优秀的索引,或优化查询语句。

MySQL索引分类

  • 单值索引:一个索引只包含单个列,一个表可以有多个单列索引
  • 唯一索引:索引列的值必须唯一,但允许有空值
  • 复合索引:一个索引包含多个列

基本语法:

  • 创建: create [unique] index indexName on tableName(columnName);
  • 删除: drop index indexName on tableName;
  • 查看: show index from tableName;
  • 使用alter命令(四中方式添加数据库索引):
    1. alter table tableName add primary key(column_list) ;// 添加一个主键,索引值必须唯一,且不能为NULL
    2. alter table tableName add unique indexName (column_list); //创建唯一索引
    3. alter table tableName add index indexName (column_list); //添加普通索引
    4. alter table tableName add fulltext indexName (column_list); //创建全文索引

MySQL索引结构

包括四中:BTree索引、Hash索引、full-text索引、R-Tree索引。
BTree的检索原理:
BTree的检索原理
真是的数据存在于叶子节点,非叶子节点不存储真实的数据,只存储指引搜索方向的数据项。
如查询29,总共需要三次IO,效率非常高。3层的B+树可以表示上百万的数据,只需要查找3次,性能的提高将是巨大的。

哪些情况需要创建索引?

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 外键关系建立索引
  4. 频繁更新的字段不适合建立索引
  5. where条件里用不到的字段不创建索引
  6. 单值/复合索引的选择,who?(高并发建议复合索引)
  7. 查询中排序的字段,排序字段若通过索引访问将大大提高排序速度
  8. 查询中统计或者分组的字段

哪些情况不要建立索引

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布平均的字段
    建立索引的选择性?索引的选择性是指索引列中不同值的树木与表中记录数的比。比值越大,建立的索引效率越大。

性能分析

MySQL Query Optimizer

MySQL中有专门负责优化SELECT语句的优化器模块,主要功能:通过计算分析系统中的收集到的统计信息,为客户端请求的Query提供它认为最优的执行计划。
步骤:
1.客户端发送一条Query
2.命令解析器模块完成请求分类,区别出是SELECT并转发给MySQL Query Optimizer
3.MySQL Query Optimizer 对整条Query进行优化,处理掉常量表达式的预算,直接换算成常量值。
4.对Query中的查询条件进行简化和转化,如去掉无用或显而易见的条件、结构调整等。
5.分析Query中的hint信息(如果有),看显示Hint信息是否完全可以确定该Query的执行计划。
6.若没有hint或hint信息不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据query写相应的计算分析
7.得出最后传动侧执行计划

MySQL常见瓶颈

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

Explain

  • 是什么(查看执行计划)?
    使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何执行SQL语句的。分析查询语句或表结构是否存在性能瓶颈。
  • 能干嘛?
    • 表的读取顺序
    • 数据读取操作的操作类型
    • 哪些索引可以使用
    • 哪些索引被实际使用
    • 表之间的引用
    • 每张表有多少行被优化器查询
  • 怎么玩? explain+SQL语句
    执行计划包含的信息: id、type、key、rows、extar重要
    执行计划各字段
  • 各字段解释

    1. id
      select 查询的序列号,包含一组数字,表时执行查询select子句或操作表的顺序。
      三种情况:
      • id相同,执行顺序由上至下
        ID顺序1
      • id不同,如果是子查询,id的序号会递增,id越大优先级越高,越先被执行
        ID顺序2
      • id相同不同同时存在,id相同的认为十一组,由上至下执行,所有id中id越大越先执行
        ID顺序2
    2. select type
      有哪些?SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT,查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂语句

      • SIMPLE:简单的select查询,不包含子查询或union
      • PRIMARY:查询中若包含任何复杂的子部分,最外层被标记为primary
      • SUBQUERY:在select或where中包含了子查询
      • DERIVED:在from列表中包含的子查询被标记为DRIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中。
      • UNION:若第二个select出现在union之后,则被标记为UNION;若UNION包含在From子句的子查询中,外层select将被标记为drived
      • UNION RESULT:从UNION表中获取结果的select
    3. table
      显示这一行的数据是关于哪张表的

    4. type
      显示查询使用了何种访问类型,从最好到最差依次是: system、const、eq_ref、ref、range、index、all。一般来说至少达到range级别,最好能达到ref。

      • system:表只有一行记录(等于系统表),这是const类型的特例,可以忽略不计。
      • const: 表时通过一次索引就找到了,const用于比较primary key或者unique索引,因为只匹配一行数据,所以很快。如将主键至于where列表中,MySQL就能将该查询转换为一盒常量。
      • eq_ref: 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描。
      • ref:非唯一性索引扫描,返回匹配某个单独值的所有行
      • range: 只检索给定范围的行,使用一个索引来选择行一般是在where语句中出现了between、<、>、in等的查询,比全表扫描好。
      • index: full index scan index只遍历索引树,通常比all快,因为索引文件通常比数据文件小(index从索引文件中读取,而all从硬盘读取)
      • all: full table scan,遍历全表
    5. possible_keys
      显示可能应用在这张表中的索引,一个或多个。但不一定被查询实际使用。poss
    6. key
      实际使用的索引,如果为NULL,则没有使用索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中。
    7. key_len: 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即ken_len是根据表定义而得,不是通过表检索出来的。
    8. ref: 显示索引的哪一列被使用了,如果可能的话,是一个常数。那些列或常量被用于查找索引列上的值。
      ref
    9. rows: 根据表统计信息及索引选用情况,大致估算出找打所需的记录所需要读取的行数。数值越小越好
      rows
    10. extra: 包含不适合在其他列显示但十分重要的额外信息。using filesort(必须优化)、using temporary(必须优化)、using index(nice,效率不错)重要
      • Using filesort: 说明MySQL对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL无法利用索引文成的排序操作称作“文件排序”。
        Usingfilesort
      • Using temporary: 使用了临时表保存中间结果,MySQL在堆查询结果排序时使用临时表。常见于order by和分组查询group by。
        UsingTemporary
      • Using index: 表示使用了覆盖索引,避免了访问表的数据行,效率不错!
        UsingIndex
        覆盖索引: 就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说:查询列要被所建的索引覆盖
      • Using where: 表明使用了where过滤
      • Using join buffer: 使用了连接缓存
      • impossible where: where子句的值总是false,不能用来获取任何元组
      • select tables optimized away: 在没有groupby子句的情况下,对索引的优化
      • distinct: 优化distinct操作,在招待第一匹配的元组后即停止找同样值的动作。
  • 热身
    1
    2

索引优化

索引分析

  • 单表
    优化前:
    优化前
    优化后:
    优化后
    range类型查询字段后面的索引失效
  • 两表
    优化前:
    优化前
    优化后:
    优化后
  • 三表
    优化前:
    优化前
    建索引:
    建索引
    优化后:
    优化后
  • 总结:
    • 尽可能减少join语句中的NestedLoop的循环总次数,”永远用小结果集驱动大的结果集”
    • 优先优化NestedLoop的内层循环
    • 保证join语句中被驱动表的上join条件字段已经被索引
    • 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝啬JoinBuffer的设置

索引失效

  • 原因及解决方案加口诀
    原因及解决方案加口诀

全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难!

  • 总结:
    总结
  • 面试题:
    面试SQL:
    1
    2
    3
    4
    5
    6

总结:
定值、范围还是排序,一般OrderBy是给个范围
group by基本上都需要排序,会有临时表产生

一般性建议

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

查询截取分析

查询优化

永远小表驱动大表(类似嵌套循环)

  • 查询优化原则
    查询优化原则
  • exists
    exists
    例子:
    例子

order by关键字优化

  • order by 子句尽量使用Index方式排序,避免使用filesort
    1. 建表:
      建表
    2. 例子:
      例子1
      例子2
    3. MySQL支持FileSort和index两种排序方式,index效率高,filesort效率低。
    4. Order by会使用index排序的情况:order by语句使用索引最左前列;使用where子句与order by子句组合满足最左前列。
  • 尽可能在索引上完成排序,遵守最佳左前缀,若不在索引列上,filesort有两种排序算法:双路排序和单路排序。MySQL4.1之前使用双路排序。
    • 双路排序:扫描两次磁盘,最终得到数据。即从磁盘取排序字段,在buffer进行排序,在从磁盘读取其他字段。
    • 单路排序: 从磁盘读取查询需要的所有列,按照orderby列在buffer对它们进行排序,然后扫描排序后的列表进行输出,这样效率更快,避免了第二次读取数据。
    • 结论及问题:由于单路是后出的,总体而言好过双路,但单路也有问题。
      单路缺点
  • 优化策略: 增大sort_buffer_size和max_length_for_sort_data参数设置
    Why?
    优化策略
  • 总结:
    总结

    group by关键字优化

  • group by实质是先排序后进行分组,遵照索引建的最佳左前缀。
  • 当无法使用索引列时,增大sort_buffer_size和max_length_for_sort_data参数设置。
  • where高于having,能写在where限定的条件就不要去having限定了。

慢查询日志

是什么?

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中相应时间超过阙值的语句,具体指运行时间超过(大于,而非大于等于)long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的语句。收集到以后结合explain进行全面分析。

怎么玩?

默认情况下,MySQL数据库不开启慢查询日志,需要手动设置。

  • 查看是否开启及如何开启?
    查看:show variables like "%slow_query_log%"
    开启:set global slow_query_log=1 只对当前数据库生效,MySQL重启后则会失效,要永久生效,需在my.cnf文件中配置
  • 查看和设置阙值:
    查看:show variables like "%long_query_time%"
    开启:set global long_query_time=3
  • 查看有多少条慢查询记录
    show global status like "%slow_queryies%"
  • 慢查询日志永久配置
    慢查询日志永久配置

日志分析工具mysqldumpslow

  • 查看mysqldumpslow的帮助信息

    • s:表示按照何种方式进行排序
      • c:访问次数
      • l:锁定时间
      • r:返回记录
      • t:查询时间
      • al:平均锁定时间
      • ar:平均返回记录数
      • at:平均查询时间(默认)
    • t:返回前面的多少条数据
    • g:后边搭配一个正则匹配模式,大小写不敏感
  • 工作常用参考
    工作常用参考

批量数据脚本

利用存储过程脚本往数据库插入1000w条数据。

  1. 建表

    • 创建部门表:

      1
      2
      3
      4
      5
      6
      CREATE TABLE dept(
      id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
      deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
      dname VARCHAR(20) NOT NULL DEFAULT "",
      loc VARCHAR(13) NOT NULL DEFAULT ""
      )
    • 创建员工表:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      CREATE TABLE emp(
      id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
      empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*编号*/
      ename VARCHAR(20) NOT NULL DEFAULT "",/*名字*/
      job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
      mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
      hiredate DATE NOT NULL,/*入职时间*/
      sal DECIMAL(7,2) NOT NULL,/*薪水*/
      comm DECIMAL(7,2) NOT NULL,/*红利*/
      deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
      )
  2. 设置参数
    因为开启了慢查询日志和bin_log,创建函数会拨错,所以要设置一个参数: set global log_bin_trust_function_creators=1

  3. 创建函数,保证每条数据都不同

    • 随机生成字符串函数

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      DELIMITER $$
      CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
      BEGIN
      DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
      DECLARE return_str VARCHAR(255) DEFAULT '';
      DECLARE i INT DEFAULT 0;
      WHILE i < n DO
      SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
      SET i = i + 1;
      END WHILE;
      RETURN return_str;
      END $$
    • 随机生成数函数:

      1
      2
      3
      4
      5
      6
      7
      DELIMITER $$
      CREATE FUNCTION rand_num() RETURNS INT(5)
      BEGIN
      DECLARE i INT DEFAULT 0;
      SET i = FLOOR(100+RAND()*10);
      RETURN i;
      END $$
  4. 创建存储过程

    • 插部门数据存储过程:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      DELIMITER $$
      CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
      BEGIN
      DECLARE i INT DEFAULT 0;
      SET autocommit = 0;
      REPEAT
      SET i = i + 1;
      INSERT INTO dept(deptno,dname,loc) VALUES ((START+1),rand_string(10),rand_string(8));
      UNTIL i = max_num
      END REPEAT;
      COMMIT;
      END $$
    • 插员工数据存储过程:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      DELIMITER $$
      CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
      BEGIN
      DECLARE i INT DEFAULT 0;
      # set autocommit=0 把autocommit设置成0
      SET autocommit = 0;
      REPEAT
      SET i = i + 1;
      INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
      UNTIL i= max_num
      END REPEAT;
      COMMIT;
      END $$
  5. 调用存储过程

    • 调用部门存储过程:
      CALL insert_dept(100,10);
    • 调用部门存储过程:
      CALL insert_emp(100001,500000)

Show Profile

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

  1. 是否支持: show variables like 'profiling'
  2. 开启,默认是关闭的: set profiling = on
  3. 运行SQL

    1
    2
    select * from emp group by id%10 limit 150000;
    select * from emp group by id%20 order by 5;
  4. 查看结果,show profiles;

  5. 诊断SQL: show profile cpu , block io [其他参数...] for query [Query_ID]
    参数:
    参数
  6. 日常开发需要注意的结论(出现以下的情况时必须优化)
  • converting HEAP to MyISAM: 查询结果太大,内存不够用往磁盘上搬了。
  • creating tmp table: 拷贝数据到临时表,用完再删除。
  • copying to tmp table on disk: 把内存中临时表复制到磁盘,很危险了。
  • locked

全局查询日志

永远不要在生产环境开启这个功能

  • 永久配置(在my.cnf文件)

    1
    2
    3
    4
    5
    6
    # 开启
    general_log=1
    # 记录日志文件的路径
    general_log_file=/path/logfile
    # 输出格式
    log_output=FILE
  • 临时配置

    1
    2
    set global general_log=1;
    set global log_output="TABLE"

将会记录到mysql库中的general_log表中,可以用下面命令查看:
select * from mysql.general_log

MySQL锁机制

概述

  • 定义: 锁是计算计协调多个进程或线程并发访问某一资源的机制。
  • 分类:

    1. 从对数据操作的类型分为读写锁

      • 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而互不影响。
      • 写锁(排它锁): 当前写操作没有完成,它会阻断其他写锁和读锁
    2. 从对数据操作的粒度分为表/行锁

三锁

表锁(偏读)

  • 特点: 偏向MyISAM引擎,开销小,加锁快;无死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
  • 加解锁: lock table tableName read(write) unlock tables
  • 表锁分析:

    • 查看哪些表被锁了: show open tables;
    • 通过检查table_locks_watied(产生表级锁定的次数)和table_locks_immediate(出现表级锁定争用而发生等待的次数)状态变量来分析系统上的表锁定: show status like 'table%';
  • 结论:读锁会阻塞写,但是不会阻塞读,而写锁则会把读和写都阻塞。MyISAM的读写锁调度是写优先

行锁(偏写)

  • 特点: 偏向InnoDB引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生死锁冲突定位概率最低,并发度最高。与MyISAM最大不同有两点:一是支持事物,二是采用了行级锁。
  • 支持事物
    • 事务及ACID属性
      ![mark](http://oqwn6kueb.bkt.clouddn.com/blog/170924/4mfJ9Bk11A.jpg?imageslim)
    • 并发带来的问题:
      • 丢失更新:当多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他十五的存在,就会发生丢失更新问题—最后的更新覆盖了有其他事务所做的更新。
      • 脏读: 事务A读取到了事务B已修改但尚未提交的数据。
      • 不可重复读: 事务A读取到了事务B已经提交的修改数据,不符合隔离性。
      • 幻读: 事务A读取到了事务B提交的新增数据,不符合隔离性。脏读和幻读区别: 脏读是事务B里面修改了数据,幻读是事务B新增了数据
    • 事务隔离级别
      事务隔离级别
  • 索引失效会导致行锁变表锁
  • 间隙锁: 当用范围条件检索数据,并请求共享或排它锁时,InnoDB会给符合条件而得已有数据记录的索引项加锁,对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。
  • 如何锁定一行
    select XXX ... for update 锁定某一行后,其他的操作被阻塞,直到锁定行的会话提交commit。
  • 分析行锁定: show status like 'innodb_row_lock%'
    • innodb_row_lock_current_waits: 当前正在等待锁定的数量
    • innodb_row_lock_time: 从系统启动到现在锁定总时间长度
    • innodb_row_lock_time_avg: 每次等待所花平均时间
    • innodb_row_lock_time_max: 从系统启动到现在等待时间最长的一次所花的时间
    • innodb_row_lock_waits: 系统启动到现在总共等待的次数

页锁(了解即可)

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

主从复制

复制的基本原理

slave会从master读取binlog来进行数据同步

复制过程分三步:
1.master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件,binary log events。
2.slave将master的binary log evnets 拷贝到它的中继日志(relay log)。
3.slave重做中继日志中的事件,将改变应用到自己的数据库中。MySQL复制是异步的且串行化的。

复制的基本原则

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

复制的最大问题

延时

一主一从常见配置

配置主机(my.cnf文件)

  • 主服务器唯一ID(必须): sever-id=1
  • 启用二进制日志(必须): log-bin=
  • 启用错误日志(可选) log-err=
  • 根目录(可选): basedir=
  • 临时目录(可选): tmpdir=
  • 数据目录(可选): datadir=
  • read-only=0(主机读写都可以)
  • 设置不要复制的数据库(可选): binlog-ignore-db=
  • 设置需要复制的数据库(可选): binlog-do-db=

配置从机

  • 从服务器唯一ID(必须): server-id=2
  • 启用二进制日志: log-bin=

主机从机都关闭防火墙

1
2
service iptables stop
chkconfig iptables off

在主机上建立账户并授权slave

  • 授权: grant replication slave on *.* to 'slaveuser'@'192.168.170.252' identified by '123456' flush privileges
  • 查看master的状态: show master status,记录下file和position的值

在从机上配置需要复制的主机

  • 配置 server-id=2
    重启mysql后执行

    1
    2
    3
    4
    5
    change master to master_host='192.168.170.251',
    master_user='slaveuser',
    master_password='123456',
    master_log_file='mysqlbin.具体数字',
    master_log_pos=具体值;
  • 启动从服务器复制功能: start slave

  • 查看状态: show slave status\G
    两个参数都是yes,说明配置成功。slave_io_running=yes,slave_sql_running

主机建库,建表,插数据。从机复制

停止从服务器复制功能

stop slave

-------------本文结束感谢您的阅读-------------
坚持原创技术分享,您的支持将鼓励我继续创作!