尚硅谷MySQL高级笔记——后篇

最新推荐文章于 2023-05-23 23:43:54 发布
最新推荐文章于 2023-05-23 23:43:54 发布
阅读量599 收藏 6
点赞数
文章标签： 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhangliyuan4286/article/details/118311122
版权

索引优化

索引失效
最左前缀原则

建表，并且在name，age，pos列上建立复合索引。

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());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)

每次多带一个条件进行查询

explain select * from staffs where name = 'z3';
explain select * from staffs where name = 'z3' and age = 25;
explain select * from staffs where name = 'z3' and age = 25 and pos = 'dev';

查询结果如下

可以发现三次查询都用了索引，并且索引的长度在不断增长。

继续使用三列，但是将三列的位置调换

explain select * from staffs where age = 25 and name = 'z3' and pos = 'dev';

可以发现，即使我们调换位置，仍然用到了索引，证明全值匹配时MySQL会自动帮我们优化。

我们使用name和pos列查询

explain select * from staffs where name = 'z3' and pos = 'dev';

虽然用到了索引，但是len为74，证明只有name的检索用到了索引。

索引列上计算、函数、(手动or自动)类型转换
explain select * from staffs where left(name,3) = 'z3';

发现没有用到索引。

自动类型转换

执行如下两条SQL

explain select * from staffs where name = '2000';
explain select * from staffs where name = 2000;

执行结果

MySQL会自动的帮我们进行类型的转换，但是索引失效。

范围条件后边的列失效

我们将三个字段中的age字段变成范围

explain select * from staffs where name = 'z3' and age > 23 and pos = 'dev';

对比发现后面的范围查找变成了range，并且key_len变成了78，表示后面的pos字段没有用到索引。

使用不等于(!=或<>)
explain select * from staffs where name <> 'z3';

可以发现进行了全盘扫描，实际没有用到索引。

like通配符开头
explain select * from staffs where name like '%z3%';
explain select * from staffs where name like 'z3%';

可以发现以%开头的like查询并没有用到索引。

如果我们非要使用%开头的like查询，我们可以使用覆盖索引。

or
explain select * from staffs where name = 'z3' or name = 'z5';

尽量使用覆盖索引
explain select name,age,pos from staffs where name = 'z3' and age = 23 and pos = 'dev';
explain select * from staffs where name = 'z3' and age = 23 and pos = 'dev';os = 'dev';

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *。

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

查询截取分析

查询优化
小表驱动大表

EXISTS

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

提示

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

ORDER BY 优化

  • ORDER BY子句，尽量使用index方法排序，避免使用filesort方法排序。

创建tblA表，在age和birth字段上建立复合索引。

使用order by进行查询

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

查询结果如下

可以发现，order by 同样支持最左前缀原则，同时带头大哥为常量时，后面排序同样可以使用索引

文件排序（FileSort）分为两种：

  • 双路排序(又叫回表排序模式)：先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
  • 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；

举个例子，下面有一段sql：

select * from user where name = "自由的辣条" order by age;

双路排序过程:
MySQL 4.1 之前使用的双路排序，通过两次扫描磁盘得到数据。读取主键id 和 order by 列并对其进行排序，扫描排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

  1. 从索引 name 找到第一个满足 name = ‘自由的辣条’ 的主键id
  2. 根据主键 id 取出整行，把排序字段 age 和主键 id 这两个字段放到 sort buffer(排序缓存) 中
  3. 从索引 name 取下一个满足 name = ‘自由的辣条’ 记录的主键 id
  4. 重复 3、4 直到不满足 name = ‘自由的辣条’
  5. 对 sort_buffer 中的字段 age 和主键 id 按照字段 age进行排序
  6. 遍历排序好的 id 和字段 age ，按照 id 的值回到原表中取出 所有字段的值返回给客户端

单路排序过程：

  1. 从索引name找到第一个满足 name = ‘自由的辣条’ 条件的主键 id
  2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer(排序缓存)中
  3. 从索引name找到下一个满足 name = ‘自由的辣条’ 条件的主键 id
  4. 重复步骤 2、3 直到不满足 name = ‘自由的辣条’
  5. 对 sort_buffer 中的数据按照字段 age 进行排序
  6. 返回结果给客户端

从磁盘中读取查询需要的所有列，按照 order by 列在 sort_buffer(排序缓存) 缓冲区对他们进行排序，然后扫描排序后的列表输出。因为单路排序效率更快，避免了二次读取数据，把随机IO变成了顺序IO，但是会使用更多的空间。

对比：

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

选型：

至于mysql优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于max_length_for_sort_data变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。

max_length_for_sort_data指某个表的所有列长度总和

注意：
如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增 大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

order by关键字优化：

  1. ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。
  2. MySQL支持二种方式的排序，FileSort和Index，Index效率较高，FileSort方式效率较低。
  3. 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

慢查询日志

  • MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
  • long_query_time的默认值是10，意思是运行10秒以上的语句。
  • 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
  • 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

查看是否开启慢查询日志以及文件存放的位置。

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | OFF                          |
| slow_query_log_file | /var/lib/mysql/192-slow.log |
+---------------------+-----------------------------+
2 rows in set (0.01 sec)

开启慢查询日志

mysql> set global slow_query_log = 1;

查看当前阈值（默认10s）

mysql> show VARIABLES like 'long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 10.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

将阈值修改为3s

mysql> set global long_query_time=3;

查看当前系统有多慢sql

mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

去对应文件查看是那条sql慢

[root@localhost mysql]# cat 192-slow.log 
/usr/sbin/mysqld, Version: 5.7.34 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2021-06-19T06:10:16.412412Z
# User@Host: root[root] @ localhost []  Id:    17
# Query_time: 4.010066  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1624083016;
select sleep(4)

日志分析工具mysqldumpslow

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

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

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

批量插入数据脚本

  1. 创建SQL

    create database bigData;
use bigData;

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 ""
)ENGINE=INNODB DEFAULT CHARSET=utf8;

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
)ENGINE=INNODB DEFAULT CHARSET=utf8;

  2. 设置参数log_bin_trust_function_creators

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

  3. 创建函数，生成随机字符串

    delimiter $$ # 两个 $$ 表示结束
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
    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 $$

  4. 随机生成部门编号

    delimiter $$
create function rand_num() returns int(5)
begin
    declare i int default 0;
    set i=floor(100+rand()*10);
    return i;
end $$

  5. 创建存储过程

    delimiter $$
create procedure insert_emp(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 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 $$

  6. 插入数据

    mysql> DELIMITER ;
mysql> CALL insert_dept(100, 10);
Query OK, 0 rows affected (0.01 sec)

用show prefile进行sql分析

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

- 查看是否开启
show variables like 'profiling';
- 开启
set profiling = on;

查看结果，show profiles;

mysql> show profiles;
+----------+------------+----------------------------------------------------------------+
| Query_ID | Duration   | Query                                                          |
+----------+------------+----------------------------------------------------------------+
|        1 | 0.00127000 | show variables like 'profiling'                                |
|        2 | 0.00024250 | SELECT DATABASE()                                              |
|        3 | 0.00024875 | show databases                                                 |
|        4 | 0.00018525 | show tables                                                    |
|        5 | 0.00024750 | show tables                                                    |
|        6 | 0.00168025 | show variables like 'profiling'                                |
|        7 | 0.00438350 | select * from tbl_emp te join tbl_dept td on te.deptId = td.id |
|        8 | 0.00046700 | select * from tbl_emp te join tbl_dept td on te.deptId = td.id |
|        9 | 0.00043650 | select * from tbl_emp te join tbl_dept td on te.deptId = td.id |
|       10 | 0.00027800 | select * from tbl_emp te join tbl_dept td on te.deptId = td.id |
|       11 | 0.00012400 | SELECT DATABASE()                                              |
|       12 | 0.00022425 | show databases                                                 |
|       13 | 0.00012100 | show tables                                                    |
|       14 | 0.48516150 | select * from emp group by id % 10 limit 150000                |
|       15 | 0.49136075 | select * from emp group by id % 20 order by 5                  |
+----------+------------+----------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)

诊断SQL

mysql> show profile cpu,block io for query 15;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000065 | 0.000059 |   0.000000 |            0 |             0 |
| checking permissions | 0.000008 | 0.000007 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000013 | 0.000013 |   0.000000 |            0 |             0 |
| init                 | 0.000027 | 0.000028 |   0.000000 |            0 |             0 |
| System lock          | 0.000009 | 0.000008 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000004 |   0.000000 |            0 |             0 |
| statistics           | 0.000017 | 0.000017 |   0.000000 |            0 |             0 |
| preparing            | 0.000012 | 0.000012 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000034 | 0.000035 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000005 | 0.000004 |   0.000000 |            0 |             0 |
| executing            | 0.000002 | 0.000002 |   0.000000 |            0 |             0 |
| Sending data         | 0.491050 | 0.490653 |   0.000000 |            0 |             0 |
| Creating sort index  | 0.000052 | 0.000045 |   0.000000 |            0 |             0 |
| end                  | 0.000005 | 0.000004 |   0.000000 |            0 |             0 |
| query end            | 0.000008 | 0.000008 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000005 | 0.000006 |   0.000000 |            0 |             0 |
| query end            | 0.000004 | 0.000003 |   0.000000 |            0 |             0 |
| closing tables       | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
| freeing items        | 0.000024 | 0.000023 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000012 | 0.000013 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)

参数备注

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

日常开发需要注意的结论

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

MySQL锁机制

数据库锁理论概述

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的分类

从对数据操作的类型（读\写）分

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

从对数据操作的粒度分

  • 表锁
  • 行锁
MyISAM读写锁

表锁（偏读）

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

建表SQL（使用MyISAM引擎）

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) 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), 其他;
- 给mylock表加读锁
lock table mylock read;

查看表上加过的锁

show open tables;

in_use是1表示已经上锁。

释放锁

unlock tables;

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

会话1会话2
获得mylocak的读锁连接终端
当前会话可以查询表记录其他会话也可以查询该表的记录
当前会话不能查询其他没有锁定的表其他会话可以查询或更新未锁定的表
当前会话中插入或者更新锁定的表都会提示错误其他会话插入或更新锁定表会一直等待获得锁[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7mWkZDa9-1624881540513)(C:\Users\49572\AppData\Roaming\Typora\typora-user-images\image-20210625104439348.png)]
释放锁会话2获得锁，更新操作完成

加写锁——为mylock表加write锁（写阻塞读例子）

会话1会话2
获得mylock的写锁定等待会话1开启写锁后，会话2再连接终端
当前会话对锁定表的查询、更新、插入操作都可以执行其他会话的对锁定表的查询被阻塞，需要等待锁被释放
释放锁会话2获得锁，查询返回

案例结论

MyISAM在执行查询语句(SELECT)前，会自动给涉及到的所有表加读锁，在执行增删改操作前，会自动给涉及到的表加写锁。

MySQL的表级锁有两种模式：

  1. 表共享读锁(Table Read Lock)
  2. 表独占写锁(Table Write Lock)
锁类型可否兼容读锁写锁
读锁是是否
写锁是否否

结合上表，所以对MyISAM表进行操作，会有以下情况：

  1. 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

  2. 对MyISAM表的写操作〈加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

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

InnoDB行锁

行锁偏向InnoDB引擎，开销大，加锁慢；会出现死锁；所得颗粒度小，发生锁冲突的概览低，并发度也最高。

InnoDB与MyISAM最大不同的两点：1.支持事务。2.采用行级锁

查看当前数据库的事务隔离级别show variables like 'tx_isolation'

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

新建SQL（使用InnoDB引擎）

CREATE TABLE test_innodb_lock (a INT(11),b VARCHAR(16))ENGINE=INNODB;

INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4, '4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');

CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
会话1会话2
取消自动提交set autocommit = 0;取消自动提交set autocommit = 0;
更新但不提交会话2被阻塞，只能等待
提交更新接触阻塞，更新正常进行
更新但不提交会话2更新其他行的记录，并没有发生阻塞
索引失效行锁变表锁

a列为int类型，b列为varchar类型，两个会话都是不自动提交事务

会话1会话2
更新a字段，但是b字段不加引号会话2更新其他字段，依然阻塞

证明由行锁变为表锁。

间隙锁的危害

数据

mysql> select * from test_innodb_lock;
+------+------+
| a    | b    |
+------+------+
|    1 | b2   |
|    3 | 3    |
|    4 | 4003 |
|    5 | 5000 |
|    6 | 6000 |
|    7 | 7000 |
|    8 | 8000 |
|    9 | 9000 |
|    1 | b2   |
+------+------+
9 rows in set (0.00 sec)

a列并不连续

左侧执行sqlupdate test_innodb_lock set b = '600.01' where a > 1 and a < 6;不提交事务

右侧执行sqlinsert into test_innodb_lock values(2,'200');

右侧被阻塞

左侧提交，右侧插入成功。

间隙锁

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

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

锁定一行

for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。

总结

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

但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

如何分析行锁定

mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 223670 |
| Innodb_row_lock_time_avg      | 27958  |
| Innodb_row_lock_time_max      | 51460  |
| Innodb_row_lock_waits         | 8      |
+-------------------------------+--------+
5 rows in set (0.00 sec)

对各个状态量的说明如下:

  • Innodb_row_lock_current_waits：当前正在等待锁定的数量；
  • Innodb_row_lock_time：从系统启动到现在锁定总时间长度；
  • Innodb_row_lock_time_avg：每次等待所花平均时间；
  • Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
  • Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

优化建议

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能较少检索条件，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可能低级别事务隔离
liyuan.zhang
关注
  • 0
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
python标准库学习（后篇）
02-26
执行已编译的代码execfile("hello.py")defEXECFILE(filename,locals=None,globals=None):execcompile(open(filename).read(),filename,"exec")inlocals,globalsEXECFILE("hello.py")...
软件工程课程资料-后篇
06-18
在本压缩包中，我们聚焦于“软件工程”的后篇，涵盖了从软件实现到软件维护，以及面向对象方法的相关知识。这些内容对于理解和实践软件开发过程至关重要，是软件工程课程的核心部分。 首先，"第7章 软件实现.ppt"会...
MySQL高级--个人笔记
知行
12-22 1662
视频课程：尚硅谷MySQL数据库高级，mysql优化，数据库优化_哔哩哔哩_bilibili Mysql 逻辑架构简介 1. 整体架构图 和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引 擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。 各层介绍： 1.1 连接层 最上层是一些客户端和连接服务，包含本地 sock 通信和大多
第 1 章 MySQL 的架构介绍
Oneby的博客
08-09 4800
第 1 章 MySQL 的架构介绍 1、MySQL 简介 什么是 MySQL？ MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。 MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 Mysql是开源的，所以你不需要支付额外的费用。 Mysl支持大型的数据库，可以处理拥有上千万条记录的大型数据库。 MySQL使用标准的SQL数据语言形式。 Mysql可以允许于多个系统上，并且支持多
【学习笔记】MySQL数据库高级版 - 索引优化、慢查询、锁机制等
zuzhiang的博客
08-25 699
本文是尚硅谷周阳（阳哥）老师的MySQL高级篇视频的学习笔记。由于视频比较老，所以在高版本的MySQL中索引的地方做了优化，和视频的内容不完全一样，不过大体一致。从第四节锁机制开始的部分还没有整理。 一、MySQL介绍 常用存储引擎的对比： MyISAM存储引擎：不支持主外键，不支持事务，表锁，只缓存索引，不缓存真实数据，表空间小，关注点是性能。 InnoDB存储引擎：支持主外键和事务，行锁，适合高并发的操作，不仅缓存索引还缓存真实数据，对内存要求较高，内存大小对性能有决定性影响，表空间大，关注点是事务
《深入搜索引擎》勘误新增两条 感谢尚家兴同学的指正
pennyliang的专栏
09-04 1836
<br />第二章           <br />           P62 第3段第4行<br />           译文：在这种情况下，需要使用6个算术编码步骤。这看上去很多，实际上很少。<br />           较好的翻译应该是：这看上去很多，但这种情况发生的概率很小。<br />           in this case, six arithmetic encoding stpes would be required. This may seem extreme,but actu
走向ASP.NET架构设计—第四章—业务层分层架构（后篇）
02-27
前言：在上一篇文章中，我们讨论了组织业务逻辑的模式：TransactionScript和ActiveRecord，DomainModel。在本篇中开始讲述AnemicModel。 今天的内容比较简单，也是本章的一个收尾！ AnemicDomainModel： 这种模式...
尚硅谷SpringBoot核心技术篇---笔记整理2-Spring Boot入门（自用）
qq_39779921的博客
09-08 651
为什么学Spring Boot 因为j2EE配置太多，开发效率低，复杂的部署流程，第三方技术集成难度大。 所以在Spring4.0以后开发出一套框架->Spring Boot. Spring Boot简化了Spring应用开发，它规定约定大于配置，just run 就能创建一个独立的、产品级别的应用。 阴阳怪气话：哎呀，Spring Boot这么好，我们别学Spring了直接开搞Spring Boot吧。别忘了Spring Boot是继承了Spring的优良传统的[狗头] ...
尚硅谷-宋红康-MySQL高级性能篇
ctflq的博客
01-30 1296
mysql高级性能优化
尚硅谷MySQL高级教程笔记Xmind版
07-04
尚硅谷MySQL高级教程笔记Xmind版（部分备注缺失），非pdf，非mmap，Xmind版本为XMind 8 Update 4
尚硅谷MySQL高级_笔记（2018年10月5日19:08:17）-附件资源
03-02
尚硅谷MySQL高级_笔记（2018年10月5日19:08:17）-附件资源
shell在指定目录下批量执行sql脚本的实例
09-15
今天小编就为大家分享一篇shell在指定目录下批量执行sql脚本的实例，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧
Mysql尚硅谷视频学习笔记(自己课堂上记的笔记)
11-11
初级尚硅谷mysql得课堂笔记，关注即可下载。自己记的，DQL\DML\DDL\等内容循序渐进，条理清晰，可之间笔记去学，也可配合尚硅谷的mysql视频去学习。
MySQL基础和高级，md手册
12-30
跟随B站视频一边学习，一边写的，比较详细，可以用做学习Mysql的参考
尚硅谷MySQL高级笔记——前篇
zhangliyuan4286的博客
06-14 934
致谢 感谢尚硅谷周阳老师❤️❤️ b站视频地址 卸载之前的版本 查看已安装的MySQLrpm -qa|grep -i mysql 逐个卸载即可yum remove mysql-community-server-5.6.36-2.el7.x86_64 MySQL Linux版的安装 下载MySQL安装包wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm 安装MySQL源yum -y localinstal
mysql优化之：show Profiles详解
hnjsjsac的博客
07-13 5268
前言 前几篇文章我们讲了什么是 MySQL 索引，explain分析SQL语句是否用到索引，以及索引的优化等一系列的文章，今天我们来讲讲Show profiles，看看SQL耗时到底出现在哪个环节。 Show profiles 是什么 是MySQL提供可以用来分析当前会话中SQL语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果 开启 profiles 查看是否开启 show variables like "%profiling%"; 开启
【MySQL高级篇笔记 (上) 】
最新发布
蛋饼吧的博客
05-23 714
举例： 数据库a ， 表b。1、如果表b采用InnoDB，data\a中会产生1个或者2个文件：如果采用模式的，数据信息和索引信息都存储在 ibdata1 中如果采用存储模式，data\a中还会产生 b.ibd 文件（此外：① MySQL5.7 中会在data/a的目录下生成。比如：字符集、比较规则。而MySQL。② MySQL8.0中。如果表b采用MyISAM，data\a中会产生MySQL5.7 中： b.frm ：描述表结构文件，字段长度等。
批处理执行mysql脚本
07-10
工作中总会遇到大量的脚本执行，很多工具都不支持一次性执行多个sql脚本，没办法只有自己写了一个
Python标准库深入学习：execfile与内置函数解析
"这篇文档是关于Python标准库的后篇学习，主要讲解了如何执行已编译的代码以及显式地访问`__builtin__`模块中的函数，并提到了`execfile`函数的使用方法。" 在Python编程中，标准库是Python语言内置的一系列模块，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值