MySql高级

MySql高级

性能下降SQL执行时间长

• 查询语句写的不好
• 索引失效
  • 单值
  • 复合
• 关联查询太多join
• 服务器调优及各个参数设置（缓冲，线程池）

七种JOIN理论

索引

简介

官方对索引的定义是：索引是帮助mysql高级获取数据得到数据结构---->索引的本质是数据结构

简单理解为“排好序的快速查找数据结构”

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这样的数据结构上实现高级查找算法。这种数据结构就是索引（B+Tree）l

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上（.myi）

我们平常说的索引，如果没有特别指明，都是B树（多路搜查树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然除了B+树这种索引外，还有哈希索引（hash index）

优势和劣势

• 优势
  • 类似大学图书馆建立书目索引，提高数据检索的效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
• 劣势
  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是要占用看空间的
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如每次进行insert，update和delete，因为会更新表时，mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整应为更新带来的键值变化后的索引信息
  • 索引只是提高效率的一个因素，如果你的mysql有大数据量的表，就需要花时间研究建立最优秀的索引或者优化查询

索引分类

• 单值索引：即一个索引只包含一个单列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 即一个索引包含多个列

基本语法

• 创建索引

  • CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
  • ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));

• 删除索引：

  • DROP INDEX [indexName] ON mytable

• 查看索引：

  • SHOW INDEX FROM table_name\G

那些情况应该创建索引

• 主键自动创建唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• 频繁更新的字段不适合建立索引
• where条件中用不到的字段不创建索引
• 查询中统计或者分组的字段

那些情况不适合建立索引

• 表记录太少
• 经常增删改的表
• 数据重复且分布均匀平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的数据，那么为他建立索引就没有太大的实际效果。

性能分析

MySql Query Optimizer

常见瓶颈

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

EXPLAIN

• 使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的mysql语句的，分许你的查询语句或是表结构的性能瓶颈
• 使用
  • explain + sql语句
• 能干什么
  • 表的读取顺序
  • 数据读取操作的操作类型
  • 那些索引可以使用
  • 那些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询
• 执行计划包含的信息
  • id |select_type|table|type|psssible_keys|key|key_len|ref|rows|extra
    • id
      • select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
      • 三种情况
        • id相同，执行顺序由上至下
        • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
        • id相同，又有不相同；如果id有相同，可以认为是一组，从上往下执行；所有组中，id值越大，优先级越高，越先执行。（DERIVED =衍生）
    • select_type
      • 类别
        • SIMPLE：简单查询，不包含子查询或者UNION
        • PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
        • SUBQUERD：select或者where列表中包含子查询
        • DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生）mysql会递归执行这些子查询，把结构放在临时表中
        • UNION：若第二个select出现在union之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层select被标记为DERIVED
        • UNION RESULT：从UNION表获取的结果
      • table
        • 显示这一行的数据是哪一张表的
      • type （一般来说保证查询至少达到range级别，最好能到达ref级别）
        • 从最好到最差依次是：system>const>eq_ref>ref>range>index>all
          • system：表只有一行数据（等于系统表），只是const类型的特例，平时不会出现
          • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转化为一个常量
          • eq_ref：唯一索引扫描，对于每一个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描
          • ref：非唯一索引扫描，返回匹配某一个单独值的所有行，本质上也是一种索引访问，他返回所有匹配某个单独的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
          • range：只检索给定范围的行，使用一个索引来选择行，key列显示使用哪个索引，一般就是在你的where语句中出现between,<,>,in等的查询。这种范围扫描索引扫描比全表扫描要好，应为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
          • index：full index scan，index和all的区别为index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件小（也就是说虽然all和index都是读全表），但是index是从索引中读取的，而all是从硬盘读取的
          • all：全表扫描
        • possible_keys
          • 显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
        • key
          • 实际使用的索引，如果为null，则没有使用索引
          • 查询中若使用覆盖索引，则该索引仅出现key列表中
        • key_len
          • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精度的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索的
        • ref
          • 显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
        • rows
          • 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
        • extra
          • 包含不适合在其他列中显示但十分重要的额外信息
            • Using filesort ：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”
            • Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于排序order和分组查询 group by
            • Using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出using where，表明索引被用来执行索引键值的查找，如果没有同时出现using where 表明索引用来读取数据而非执行查找动作。
              • 覆盖索引理解：就是select的数据列只用从索引中就能获取，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询要被所见的索引覆盖。
            • Using join buffer：使用了连接缓存
            • impossible where ：where子句总是false，不能用来获取任何元组
            • select tables optimized away：在没有GROUP子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
            • distinct：优化distinct操作，在找到第一匹配的元组后停止找同样值得动作

优化案例

索引单表优化案例

建立单表

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL ,
`category` INT(10) UNSIGNED NOT NULL ,
`views` INT(10)UNSIGNED NOT NULL ,
`comments` INT(10) UNSIGNED NOT NULL ,
`title` VARCHAR(255) NOT NULL ,
`content` TEXT NOT NULL
);

INSERT INTO `article`(`author_id`,`category`,`views`,`comments`,`title`,`content`) VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,1,1,3,'3','3');

表查询

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ds9Aslk4-1618049408049)(C:\Users\ASUS\AppData\Roaming\Typora\typora-user-images\image-20210409181308652.png)]

问题：查询category=1且comments大于1的情况下，views最多的article_id

一般的sql查询语句：

对该语句性能分析得：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

type=ALL，可知该查询为全盘扫描查询，性能最差。而且Extra里还出现了Using filesort也是最坏得情况。所以优化是必须得。

优化

新建索引：

CREATE INDEX idx_article_ccv ON article(category,comments,views);

查看索引：

再次执行查询命令，并对其进行性能分析可得

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

由type=range可知，我们已经对查询语句进行了性能得优化，但是在extra中仍然存在using filesort。这是不可取得

原因：

按照BTree索引的工作原理，先排序category，再排序comments，如果遇到相同的comments则再排序views。

当comments字段再联合查询中处于中间位置时，因为comments>1条件是一个范围值（range）

mysql无法利用索引对后面的views部分进行检索。

出现得原因：出现得范围索引会导致后面得索引失效

处理：

删除之前的索引，重新构建索引时需要绕开comments的范围。

删除索引：

DROP INDEX idx_article_ccv ON article;

新建索引：

CREATE INDEX idx_article_cv ON article;

对新索引进行性能分析可知：

type变成了ref，using filesort也消失了，结果非常理想。

索引两表优化案例

建表

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(`id`)
);

CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(`bookid`)
);

INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));

INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));

问题：两表进行join查询时，索引建立在左表好，还是右表好。

尝试一：将索引建立在左表。

CREATE INDEX idx_class_card ON class(card);

性能分析得：

尝试二：将索引建立在右表。

CREATE INDEX idx_book_card ON book(card);

性能分析得：

由性能system>const>eq_ref>ref>range>index>all可知，将索引加载右表较好。

原因：

这是由于左连接的特性决定的，LEFT JOIN条件用于确定如何从右表搜索行，左边一定有，所以右边是我们的关键点，一定要建立索引。

相同的右连接，左边是我们的关键点，一定要建立索引。

索引三表优化案例

在上面两表的基础上，在加一个phone表。

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(`phoneid`)
);

INSERT INTO phone(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES (FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES (FLOOR(1+(RAND()*20)));

问题：下列三表查询时，索引如何添加?

SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card= phone.card;

结论:将索引添加到后两张表中。

性能分析得：


由上图可知，后两次查询type均为ref，非常理想。

结论：

• 尽量减少join语句中的NestedLoop的循环次数，“永远用下结果集驱动大的结果集”。
• 优先优化NestedLoop的内层循环。
• 保证Join语句中被驱动表上Join条件字段已经被索引。
• 当无法保证被驱动表的Join条件字段被索引且内存资源充足的情况下，不要吝啬JoinBuffer的设置。

索引优化

避免索引失效

建表

CREATE TABLE  staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)CHARSET utf8 COMMENT '员工记录表';

INSERT INTO staffs(`name`,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,age,pos,add_time) VALUES('july',23,'dev',NOW());
INSERT INTO staffs(`name`,age,pos,add_time) VALUES('2000',23,'dev',NOW());

添加复合索引：

CREATE INDEX idx_staffs_nap ON staffs(`name`,age,pos);

避免索引失效：

• 全值匹配
• 最佳左前缀法则
  • 如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引的列。
  • 带头大哥不能死，中间兄弟不能断
• 不在索引上做任何操作（计算，函数，（自动or手动）类型转换），会导致索引失效而转向全表扫描。
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致），减少select*）
• mysql在使用不等于(!=或者<>)的时候无法使用索引，会导致全表扫描
• is null，is not null也无法使用索引
• like以通配符开头（"%abc…"）mysql索引会失效变成全表扫描的操作
  • 如果业务需要，则使用覆盖索引来解决（即查询列被包含于索引列，这样）
• 字符串不带单引号索引失效,还可能导致行锁变为表锁
• 少用or，用它来连接时会索引失效

优化口诀：

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

查询截取分析

查询优化

• 永远小表驱动大表,类似嵌套循环Nested Loop

  • select * from A where id in(select id from B)
    

  • 等价于

  • for select id from B
    for select * from A where A.id=B.id
    

  • 当B表的数据集小于A表数据集时,用in优于exists;

  • select * from A where exists(select 1 from B where B.id=A.id)
    

  • 等价于

  • for select * from A
    for select * from B where B.id=A.id
    

  • 可以理解为:将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否的以保存

  • EXISTS(subquery)只返回true或者false,因此子查询中的select * 也可以是select 1 或者select ‘x’,官方说法是实际执行中会忽略select清单,因此没有区别

  • EXISTS子查询的实际执行过程可能经过了优化为不是我们理解上的对比

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

• Order By关键字优化

  • order by子句,尽量使用index方式排序,避免使用filesort方式排序
    • 会使用index方式排序的两种排序
      • order by 语句使用索引最左前列
      • 使用where子句与order by子句条件列组合满足索引最左前列
  • 尽可能在索引上完成排序,遵照索引建的最佳左前缀
  • 如果不在索引列上,filesort有两种算法:双路排序和单路排序
    • 双路排序
      • mysql4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和order列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取相应的数据输出
      • 从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段
    • 单路排序
      • mysql4.1之后,从磁盘读取查询需要的所有列,按照order by在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据.并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中
      • 问题
        • 在sort_buffer中,单路排序比双路排序占用很多空间,因为单路排序是把所有字段都取出来,所以有可能取出的数据总大小超过了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据量,进行排序(创建tmp文件,多路合并),拍完再取sort_buffer容量大小,从而导致多次IO
      • 优化策略
        • 增大sort_buffer_size参数的设置
        • 增大max_length_for_sort_data设置
        • 使用order by时不要使用select *
          • 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用改进后的算法—单路排序,否则就用老算法—多路排序
          • 两种算法的数据可能超过sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次IO,但是用单路排序算法的风险大一些,所以应当提高sort_buffer_size

• Group By关键字优化

  • group by实质是先排序后分组,遵照索引建的最佳左前缀

  • 当无法使用索引列时,增大sort_buffer_size参数设置和max_length_for_sort_data参数设置

  • where高于having,能再where限定的条件就不要去having中限定了

慢查询日志

是什么

• MySQL的慢查询日志时MySQL提供的一种日志记录,他用来记录再在MySQL中响应时间超过阙值的语句,具体值运行时间超过long_query_time的sql,则会被记录到日志中

• 默认没有开启MySQL的慢查询日志,需要手动设置

• show variables like "%slow_query_log%";   查看慢查询日志是否开启
  

批量数据脚本

创建函数时,假如报错: This function has none of DETERMINISTIC…

原因:由于开启慢查询日志,因为开启了bin-log,我们就必须给我们的函数指定一个参数.

show variables like "log_bin_trust_function_creators";

set global log_bin_trust_function_creators=1;

要想永久设置,需要在
window:my.ini[mysqld]加上log_bin_trust_function_creators=1
linux: my.conf[mysqld]加上log_bin_trust_function_creators=1

创建函数(示例)

DELIMITER $$				#防止函数内语句因为;结尾而执行,先将结束符改为$$
CREATE FUNCTION random（）
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=FLOOR(100+RAND()*10);
RETURN i; 
END$$

创建存储过程(示例)

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) VALUES(`start`+i);
UNTIL i=max_num;
END REPEAT
COMMIT;
END$$

#######运行存储过程#########
DELIMITER ;					#将结束符重新改为;
CALL insert_dept(10,100);	

show Profile

是什么

是MySQL提供的可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优测量

默认情况下,参数处于关闭状态,并保存最近15次的运行结果

show variables like "profiling";		#查看状态
set profiling=on						#开启

show profiles							#查询最近15次运行的资源消耗情况

show profile cpu,block io for query 4;

参数类型

• ALL:显示所有的开销信息
• BLOCK IO:显示块IO相关的开销
• CONTEXT SWITCHES:上下文切换相关开销
• CPU:显示CPU相关开销信息
• IPC:显示发送和接收相关开销信息
• MEMORY:显示内存相关开销信息
• PAGE FAULTS:显示页面错误相关开销信息
• SOURCE:显示和Source_function,source_file,source_line相关的开销信息
• SWAPS:显示交换次数相关开销的信息

状态信息中出现的问题

• converting HEAP to MyISAM:查询的结果太大,内存都不够用了,往磁盘上搬
• create tmp table:创建临时表
  • 拷贝数据到临时表
  • 用完删除
• copy to tmp table on disk:把内存中临时表复制到磁盘中**(这是非常危险的情况!!!**)
• locked

全局查询日志

永远不要在生产环境中开启这个日志

开启全局查询日志

set global general_log=1;

set global log_output='TABLE';

此后你所编写的sql语句将会记录到mysql库中的general_log表中,用下面命令查看

select *from mysql.general_log;

MySQL锁机制

概述

定义

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

在数据库中,除传统的计算机资源(如CPU,RAM,IO)的争用以外,数据也是一种供许多用户共享的资源.如何保证数据并发访问的一致性,有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素.从这个角度上来讲,锁对于数据库而言显得尤其重要,也更加复杂.

分类

• 从对数据操作的类型来分(read/write)

  • 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会相互影响
  • 写锁(排他锁):当前写操作没有完成前,它会阻断其他写锁和读锁

• 从对数据操作的粒度分

  • 表锁
  • 行锁

  三锁

  • 表锁

    • 偏写,偏向MyISAM存储引擎,开销小,加锁快;无死锁,锁定粒度大,发生锁冲突的概率最高,并发率最低

    • 查看表上加过的锁

      • show open tables;
        

    • 手动增加锁

      • lock table 表名 read(write),表名2 read(write),其它;
        

    • 解锁

      • unlock tables;
        

    • 结论

      • MyISAM在执行查询语句(select)前,会自动给涉及的所有表加锁,在执行增删改查操作前,会自动给涉及的表加写锁
      • 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写操作,只有当读锁释放后,才能执行其他进程的写操作
      • 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作

    • 如何分析表锁定

      • show status like "table%"
        

      • Table_locks_immediate:产生表级锁定的次数,表示可以立即获得所得查询次数,每立即获取锁,值加一

      • Table_locks_waited:出现表级锁定争用而发生得等待次数(不能立即获得锁得次数,每等待一次,值加一),此值高则说明存在着较严重得表级争用情况

    • MyISAM的读写锁调度是写优先,这也是MyISAM不适合做写为主表的引擎,因为写锁后,其他的线程不能做任何操作,大量的更新会使查询很难得到锁,从而操作永久堵塞.

  • 行锁

    • 偏写,偏向InnoDB存储引擎,开销大,加锁慢;会出现死锁;锁的粒度小,发生锁冲突的概率最低,并发度也最高

    • 并发事务带来的问题

      • 更新丢失

      • 脏读:事务A读取事务B已修改但尚未提交的数据,还在这个数据基础上做了操作,此时如果B事务回滚,A读取的数据无效,不符合一致性要求

      • 不可重复读:事务A读取到事务B已经提交的修改数据,不符合隔离性

      • 幻读:事务A读取到事务B提交的新增数据,不符合隔离性

        • 脏读是事务B中修改了数据
        • 幻读是事务B中新增了数据

      • 事务的隔离级别

        • 读数据一致性及允许的并发副作用	读数据一致性	脏读	不可重复读	幻读
          未提交读(read uncommited)	最低级别	是	是	是
          已提交读(read commited)	语句级	否	是	是
          可重复读(repeatable read)	事务级	否	否	否
          可序列化读(serializable)	最高级别,事务级	否	否	否

        • 查看当前数据库事务隔离级别命令

          • show variables like "tx_isolation";
            

      • 结论:

        • innoDB存储引擎由于实现了行级锁定,虽然在锁定机制方面锁带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的.当系统的发性较高的时候,innoDB的整体和MyISAM相比就会有比较明显的优势了.

    • 如何分析行锁定

      • 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:系统启动到现在总共等待的次数.

    • 间隙锁

      • 当我们使用范围条件而不是相等条件检索数据,并请求共享或排他锁时,innoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围但并不存在的记录,称为"间隙(GAP)";
      • innoDB也会给这个"间隙"加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)
      • 危害
        • 因为query执行过程中通过范围查找的话,他会锁定整个范围内所有的索引键值,即使这个键值不存在.
        • 间隙锁有一个致命的弱点,就是当锁定一个范围键值之后,即使某些不存在的键值也会被无辜锁定,而造成锁定的时候无法插入锁定键值范围内的任何数据.在某些场景下这可能会对性能造成很大的危害.

    • 问题:如何锁定一行

      • begin;
        
        select * from table where condition for update;
        
        commit;
        

  • 优化建议

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

  • 页锁

    • 开销和加锁时间介于表锁和行锁之间,会出现死锁,锁定的粒度介于表锁和行锁之间.

  主从复制

  复制的原理

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

  过程:

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

  复制的基本原则

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

存在的键值也会被无辜锁定,而造成锁定的时候无法插入锁定键值范围内的任何数据.在某些场景下这可能会对性能造成很大的危害.

- 问题:如何锁定一行

  - ```sql
    begin;
    
    select * from table where condition for update;
    
    commit;
    ```

• 优化建议
  • 尽可能让所有数据检索都通过索引来完成,避免无索引导致行锁升级为表锁
  • 合理设计索引,尽量缩小锁的范围
  • 尽可能缩小检索条件,避免间隙锁
  • 尽量控制事务大小,减少锁定资源量和时间长度
  • 尽可能低级别事务隔离
• 页锁
  • 开销和加锁时间介于表锁和行锁之间,会出现死锁,锁定的粒度介于表锁和行锁之间.

主从复制

复制的原理

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

过程:

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

复制的基本原则

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