MySQL高级部分学习

一、索引

索引简介：

详解（B树）

结论：

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

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

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

优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或者优化查询。

mysql索引分类

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

基本语法：

创建：

create [unique] index indexname on mytable(columnname(length));

alter mytable add [unique] index [indexname] on (columnname(length))

如果是char，varchar类型，length可以小于字段实际长度；如果是blob和text类型，必须指定length。
删除：

drop index [indexname] on table_name;

查看：

show index from table_name

使用alter命令

MySQL索引结构

哪些情况需要创建索引：

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录，还会更新索引，加重IO负担
where条件里用不到的字段不创建索引
单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不需要创建索引

表记录太少
经常增删改的表

Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

性能分析:

MySQL Query Optimizer

MySQL常见瓶颈：

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

Explain（解释）：

是什么（查看执行计划）：

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

能干嘛：

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的应用
每张表有多少行被优化器查询

怎么玩：

　　Explain+SQL语句：

执行计划包含的信息：

id:

　　select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
　　id相同，执行顺序由上至下
　　id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
　　id相同不同，同时存在
　　衍生：DERIVED

select_type：

SIMPLE：
    简单的select查询，查询中不包含子查询或者UNION。
PRIMARY：
    查询中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
SUBQUERY：
    在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。
DERIVED：
    在FROM列表中包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里。
UNION：
    若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
UNION RESULT：
    从UNION表中获取结果的SELECT。

possible_keys：

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

key：

　　实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列表中。（覆盖索引：查询的字段与建立的复合索引的个数一一吻合）

key_len：

　　表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref：

　　显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。

table：

　　显示这一行的数据是关于哪些表的。　

ype：

　　type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>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：
    Full Table Scan，将遍历全表以找到匹配的行。
一般来说，得保证查询至少达到range级别，最好能达到ref。

rows：

　　根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。　　

Extra：

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

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。

Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

索引优化：

单表：

双表：

总结：

　　左连接建右表指定字段，右连接建左表指定字段

理由：

　　以左连接为例，左表的信息全都有，所以右表需要查找，所以建立右表index

三表：

　　尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。

意思是说：
    如果有"书籍类别"表跟"书籍表"，在一个商城中有50种类的书籍，但是书籍的总数可能有三千万，这就是用小结果集（类别）去连接大结果集（book）

View Code

　　优先优化NestedLoop的内层循环。
　　保证Join语句中被驱动表上Join条件字段已经被索引。
　　当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。

索引失效：

SQL文件：

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
  `age` int(0) NOT NULL COMMENT '年龄',
  `pos` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '职位',
  `add_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '入职时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '员工记录表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, 'zs', 22, 'dev', '2021-07-10 12:44:25');
INSERT INTO `emp` VALUES (2, 'ls', 25, 'test', '2021-07-10 12:44:47');
INSERT INTO `emp` VALUES (3, 'ww', 21, 'manager', '2021-07-10 12:44:47');
INSERT INTO `emp` VALUES (4, 'zl', 29, 'zzz', '2021-07-10 12:44:47');

SET FOREIGN_KEY_CHECKS = 1;

select * from emp;

create index idx_emp_nameAgePos on emp(name,age,pos);

1、非全值匹配会导致索引失效

2、最佳左前缀法则：

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（带头大哥不能死，中间兄弟不能断哈哈哈）

这里讲一下MYSQL底层对索引的一些优化：

　　《1》举个例子，我们上面已经建立好了索引"inx_emp_nameAgePos"了，让age在name前面出现会怎么样呢？索引会不会失效呢？

（不会失效），索引底层实际上是调整了索引的顺序，因为在ref中有两个变量“const”

　　《2》同理，上面的例子中如果pos在name前面出现，索引会不会失效呢？

（不会失效），因为在上个例子就已经说了在索引底层会对字段顺序做一个优化，那么name跟pos索引不是连续的，那么这个就只用到了name这一个索引

　　《3》举个例子，如果money跟name(我在第9步加的一个列，可以暂时忽略这个，等看到第九题再回来看这个)同时出现，现在name在money后面，那么MySQL会自动优化，因为name是索引"inx_emp_nameAgePos"的带头大哥，所以看下面的SQL是使用到了索引的，又不过他俩不是连续的（不在一个索引名，他俩不是连续的），所以只用到了一个索引列：

3、不在索引列上作任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

4、存储引擎不能使用索引中范围条件右边的列

5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

按需取数据，用多少取多少，尽量与索引一致
Extra中出现了using index很好！

6、mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

7、is null，is not null也无法使用索引

8、以%开头的like会导致索引失效

在平时的使用中肯定要用到%%，怎么解决%%失效的问题？利用覆盖索引解决两边%导致索引失效的问题：

9、字符串不加单引号索引失效

10、少用or，使用or可能会导致索引失效

总结来说：or两边的字段，如果是索引字段，这时候索引就不会失效。

如果给下面再添加一个查询“explain select * from emp where age = 22 or money = '800';”也会导致索引失效，因为age字段不是单独索引，或者说“不是索引的首列字段”

小总结：

优化总结口诀：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

二、查询截取分析

查询优化：

小表驱动大表：

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

优化原则：小表驱动大表，即小的数据集驱动大的数据集。
当B表的数据集必须小于A表的数据集时，用in优于exists
当A表的数据集必须小于B表的数据集时，用exists优于in
注意：A表与B表的ID字段应建立索引。

EXISTS

　　SELECT … FROM table WHERE EXISTS(subquery)
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
提示

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

ORDER BY关键字优化

SQL：

create table tab(
	id int primary key not null auto_increment,
	age int,
	birth timestamp not null
);

insert into tab(age,birth) values(22,now());
insert into tab(age,birth) values(23,now());
insert into tab(age,birth) values(24,now());

create index idx_tab_ageBirth on tab(age,birth);

select * from tab;

　　来区分一下Using filesort跟Using index，由上面的学习可以知道后者肯定比文件内再排序效率要高：

Test1：

explain select * from tab where age > 20 order by age;
explain select * from tab where age > 20 order by age,birth;
explain select * from tab where age > 20 order by birth;
explain select * from tab where age > 20 order by birth,age;

Test2：

explain select * from tab order by birth;
explain select * from tab where birth > '2021-03-05' order by birth;
explain select * from tab where birth > '2021-03-05' order by age;
explain select * from tab order by age ASC,birth DESC;

小总结：

MySQL支持两种方式的排序

FileSort和Index，Index效率高。FileSort方式效率较低。
Using Index，它指MySQL扫描索引本身完成排序。

ORDER BY满足两种情况，会使用Index方式排序：

ORDER BY语句使用索引最左前列
使用Where子句与ORDER BY子句条件列组合满足索引最左前列

慢查询日志：

1、SQL慢查询日志是什么：

　　MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
long_query_time的默认值是10，意思是运行10秒以上的语句。
由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前的explain进行全面分析

2、慢查询日志怎么使用？

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

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

　　开启慢日志查询设置： set global slow_query_log=1;

注意：使用当前设置开启慢查询日志，如果重启了mysql慢查询还是失效的，也就是不是永久的、

　　如何开启永久的慢查询日志功能呢？

#由于慢查询日志记录的信息比较多，会影响mysql的性能，所以生产环境不建议长期开启

vim /etc/my.cnf
在[mysqlid]增加以下两行配置
-----------------------------------
slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/zhixi-query.log
-----------------------------------

关于慢查询的参数slow_ query_ _log_ file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host _name-slow.log (如果没有指定参数slow_ query_ log_ file的话 )

3、什么样的日志会被记录到log文件中呢?　　

顾名思义，当然是那些执行较慢的SQL语句会被放在日志文件中，让我们程序员优化。

命令： SHOW VARIABLES LIKE 'long_query_time%';

设置慢的阈值时间： set global long_query_time=3;

可以使用命令修改，也可以在my.cnf参数里面修改。
假如运行时间正好等于long_ query_ time的情况，并不会被记录下来。也就是说，
在mysq|源码里是判断大于long_ query_time，而非大于等于。

4、案例：记录慢日志并分析

1.设置当前慢日志为3s
2.执行慢sql
3.查看/分析慢SQL

查询当前系统中有多少条慢查询记录：

show status like 'slow_queries';

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

查看mysqldumpslow的帮助信息：mysqldumpslow --help

s：是表示按照何种方式排序
c：访问次数
I：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感

得到返回记录集最多的十条记录：

mysqldumpslow -s r -t 10 /usr/local/mysql/data/zhixi-slow.log

得到访问次数最多的十条SQL；

mysqldumpslow -s c -t 10 /usr/local/mysql/data/zhixi-slow.log

得到按时间排序的十条SQL中包含“left join”的SQL：

mysqldumpslow -s t -t 10 -g "left join" /usr/local/mysql/data/zhixi-slow.log

批量创建数据【数据库脚本】：

往数据表中添加百万数据

1、建表

dept：

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `deptno` mediumint(0) NULL DEFAULT NULL,
  `dname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  `loc` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

emp：

DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '名字',
  `job` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '工作',
  `mgr` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '上级编号',
  `hiredate` date NULL DEFAULT NULL COMMENT '入职时间',
  `sal` decimal(7, 2) NULL DEFAULT NULL COMMENT '薪水',
  `comm` decimal(7, 2) NULL DEFAULT NULL COMMENT '奖金',
  `deptno` int(0) NULL DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;

2、设置参数

-- 设置参数：由于开启过慢查询日志，因为我们开启了bin-log, 我们就必须为我们的function指定一个参数。

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

说明：当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。如果设置为0（默认值），用户不得创建或修改存储函数

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

随机产生字符串：

-- 函数的作用是，传入一个参数，得到一个英文版的随机字符串　
DELIMITER $$
CREATE FUNCTION rand_string ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
    DECLARE
        chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    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 $$

随机产生部门编号：

-- 用户产生随机部门编号

DELIMITER $$
CREATE FUNCTION rand_num () RETURNS INT ( 5 ) BEGIN
    DECLARE
        i INT DEFAULT 0;
    
    SET i = FLOOR( 100+RAND ()* 10 );
    RETURN i;
    
END $$

删除已创建的函数：

-- 如果要删除函数
DROP FUNCTION rand_num ();

4、创建存储过程

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 $$

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 + i ),rand_string ( 10 ),rand_string ( 8 ));
        UNTIL i = max_num 
    END REPEAT;
    COMMIT;
END $$

5、调用存储过程

dept

-- 设置为分号结束SLQ
DELIMITER;
-- 一次性插入十个部门
CALL insert_dept(100, 10);

往emp表中插入50万条数据：

DELIMITER ;
CALL insert_emp(100001, 500000);

Show Profile

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

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

1、是否支持，看看当前的mysql版本是否支持

show variables like ‘profiling’;

2、开启功能，默认是关闭，使用前需要开启

set profiling = on;

3、执行SQL

这里需要注意的是执行SQL报错variable ‘sql_mode’ can’t be set to the value of ‘NO_AUTO_CREATE_USER’

原因是：8.0以上已经取消了NO_AUTO_CREATE_USER这个关键字，删掉sql语句中的这个关键字即可

set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

执行查询：

select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;

4、查看结果，show profiles;

诊断SQL，show profile cpu, block io for query [上一步前面的问题SQL数字号码];

参数备注：

三、MySQL锁机制

1、锁的概述

定义

　　锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源(如CPU、RAM、I/O等) 的争用以外，数据也是一种供许多用户共享的资源。

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

从这个角度来说锁对数据库而言显得尤其重要，也更加复杂。

锁的分类

从对数据操作的类型（读/写）分
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
从对数据操作的粒度分
- 表锁
- 行锁

MySQL 存储引擎

MyIsAM
- 表级锁
InnoDB
- 支持行级锁，默认采用行级锁
- 支持表级锁
MEMORY
- 表级锁
BDB
- 支持页面锁，默认采用页面锁
- 支持表级锁

2、MySQL中的三种锁之表锁：

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

表级锁案例分析：

建表：

create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b'); 
insert into mylock(name) values('c'); 
insert into mylock(name) values('d');
insert into mylock(name) values('e');

select * from mylock;

手动增加表锁：

lock table 表名字 read(write), 表名2 read(write), 其他;

查看表上加过的锁：

show open tables;

释放表锁：

unlock tables;

加读锁（我们为mylock表加read锁（读阻塞写例子））

Session1 Session2

加写锁（我们为mylock表加write锁（MyISAM存储引擎的写阻塞读例子））

Session1 Session2

小结：

案例结论

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

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

3、MySQL中的三种锁之行锁（偏写）：

特点

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

并发事务处理带来的问题　　

更新丢失（Lost Update）

　　当两个或多个事务选择同一-行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问
题----最后的更新覆盖了由其他事务所做的更新。
例如：

　　两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一一个程序员所做的更改。
如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

脏读（Dirty Reads）

　　一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态; 这时，另一个事务也来读取同一条记录，

如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。

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

不可重复读（Non-Repeatable Reads）

　　一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了!

这种现象就叫做“不可重复读”。

一句话:事务A读取到了事务B已经提交的修改数据，不符合隔离性

事务隔离级别

查看当前数据库的默认隔离级别：

SELECT @@transaction_isolation;

案例

SQL：

drop table  if exists `innodb_lock`;

CREATE TABLE innodb_lock(
	`id` int(0) NOT NULL AUTO_INCREMENT primary key,
	`name` varchar(16)
) ENGINE=INNODB;

INSERT INTO innodb_lock(`name`)
values('zs'),('ls'),('ww'),('zl');

如何锁定一行：

select * from 表名 where id=xx for update;

行锁分析

如何分析行锁定

　　通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

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：系统启动后到现在总共等待的次数。

对于这5个变量，比较重要的是

innodb_row_lock_time_avg（等待平均时长）
innodb_row_lock_waits（等待总次数）
innodb_row_lock_time（等待总时长）

这三项
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。
优化建议：

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

posted @ 2021-07-04 10:01 Java小白的搬砖路阅读(125) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Captain

人生没有白走的路，每走一步都算数。

MySQL高级部分学习

一、索引

索引简介：

性能分析:

索引优化：

索引失效：

小总结：

二、查询截取分析

查询优化：

慢查询日志：

日志分析工具mysqldumpslow

批量创建数据【数据库脚本】：

Show Profile

三、MySQL锁机制

1、锁的概述

2、MySQL中的三种锁之表锁：

3、MySQL中的三种锁之行锁（偏写）：

Captain

人生没有白走的路，每走一步都算数。

MySQL高级部分学习

一、索引

索引简介：

性能分析:

索引优化：

索引失效：

小总结：

二、 查询截取分析

查询优化：

慢查询日志：

日志分析工具mysqldumpslow

批量创建数据【数据库脚本】：

Show Profile

三、MySQL锁机制

1、锁的概述

2、MySQL中的三种锁之表锁：

3、MySQL中的三种锁之行锁（偏写）：

二、查询截取分析