前言
观看笔记：https://www.bilibili.com/video/BV1fJ41127Rj?from=search&seid=13531213796559959972
本课程作为MySQL高级课程，主要讲解了：
- MySQL中的视图/存储过程/触发器/索引等对象的使用
- 常见的SQL语句优化的技巧
- 应用优化
- 数据库优化
- 数据库日志
等方面的知识，并通过综合案例，对课程中的知识进行一个整合应用。
本课程旨在通过MySQl高级部分内容，可以在满足现有业务需求基础上，对MySQL底层的体系结构，及底层的优化有一个深入的理解，对系统的整体性能进行提升。
今日内容
- 应用优化
- MySQL中查询缓存优化
- MySQL内存管理以及优化
- MySQL并发参数调整
- MySQL锁问题
- 常用SQL技巧
应用优化
前面章节，介绍了很多数据库的优化措施，但是在实际生产环境中，由于数据库 本身的性能 局限，就必须对前台的应用进行一些优化，来降低 数据库的 访问压力。
使用连接池
对于访问数据库来说，建立连接的代价是比较昂贵的，因为频繁的 创建关闭连接，是比较耗费资源的，有必要建立数据库连接池，以提高访问的性能。
减少对MySQL的访问
减少对数据进行重复检索
在编写应用代码时，需要能够理解 对 数据库的 访问逻辑。
能够 一次连接 就 获取得到结果的，就 不用两次连接，这样可以大大减少 对 数据库 无用的重复请求。
比如，需要获取书籍的id和name字段，则查询如下：
select id, name from tb_book;
之后，在业务逻辑中有需要获取得到书籍状态信息，则查询如下：
select id ,status from tb_book;
这样，就需要向数据库提交两次请求，数据库就要做两次查询操作。
其实完全可以用一条SQL语句得到想要的结果。
select id,name ,status from tb_book;
增加cache层
在应用中，可以在应用中增加 缓存 层 来达到减轻数据库负担的目的。
缓存层有很多种，也有很多实现方式，只要达到降低数据库的负担又能满足应用需求就可以。
因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储，或者使用框架（Mybatis，Hibernate）提供的一级缓存、二级缓存，或者使用redis数据库来缓存数据。
负载均衡
负载均衡是应用中使用非常普遍的一种优化方式。
负载均衡的机制就是利用某种均衡算法，将固定的负载量 分到 不同的 服务器上，一次来降低 单台服务器的 负载，达到优化的效果。
利用MySQL复制分流查询
通过MySQL的 主从复制，实现读写分离，使得增删改操作 走 主节点，查询操作 走 从结点，从而可以降低 单台服务器的读写压力。
采用分布式数据库架构
分布式数据库架构适合大数据量、负载高的情况，它有良好的 拓展性 和 高可用性。
通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率。
查询缓存 - 概述及流程
Mysql中查询缓存优化-概述
开启mysql的 查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果；
当数据被修改，之前的缓存就会失效，修改比较频繁的 表 不适合 做 查询缓存。
操作流程
- 客户端 发送一条查询SQL 给 服务器
- 服务器先会 检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段。
- 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
- MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
- 将结果缓存并返回给客户端；
查询缓存 - 配置参数
查询缓存配置
- 查看当前的MySQL数据库 是否 支持 查询缓存：
mysql -u root -p
SHOW VARIABLES LIKE 'have_query_cache';
| Variable_name | Value |
|---|---|
| have_query_cache | YES |
- 查看当前MySQL 是否 开启了 查询缓存：
SHOW VARIABLES LIKE 'query_cache_type';
| Variable_name | Value |
|---|---|
| query_cache_type | OFF(关闭) |
- 查看 查询缓存 的 占用大小（建议按照1024倍数进行增大）：
SHOW VARIABLES LIKE 'query_cache_size';
| Variable_name | Value |
|---|---|
| query_cache_size | 1048576（单位为字节，占用内存空间约为：1048576/1024/1024约为 1M的内存空间） |
- 查看查询缓存 的 状态变量
SHOW STATUS LIKE 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1038344 |
| Qcache_hits | 13 |
| Qcache_inserts | 6 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 16 |
| Qcache_queryies_in_cache | 1 |
| Qcache_total_blocks | 4 |
use demo_02; # 切换数据库也算是一次查询mysql底层当中
select count(*) from tb_item; # 耗费时间2.76 second
select count(*) from tb_item; # 耗费时间2.53 second
| Variable_name | Value |
|---|---|
| Qcache_free_blocks（可用的内存块的个数） | 1 |
| Qcache_free_memory（可用的内存空间） | 1038344 |
| Qcache_hits（查询缓存的命中次数） | 13 |
| Qcache_inserts（添加到缓存当中的次数） | 6 |
| Qcache_lowmen_prunes（如果内存空间不足，将内存中数据移出缓存空间次数） | 0 |
| Qcache_not_cached(当前查询没有进行缓存次数) | 19 |
| Qcache_queryies_in_cache | 1 |
| Qcache_total_blocks | 4 |
| 参数 | 含义 |
|---|---|
| Qcache_free_blocks | 查询缓存中的可用内存块数 |
| Qcache_free_memory | 查询缓存的可用内存量 |
| Qcache_hits | 查询缓存命中数 |
| Qcache_inserts | 添加到查询缓存的查询数 |
| Qcache_lowmen_prunes | 由于内存不足而从查询缓存中删除的查询数 |
| Qcache_not_cached | 非缓存查询的数量（由于query_cache_type设置而无法缓存或者未缓存） |
| Qcache_queries_in_cache | 查询缓存中注册的查询数 |
| Qcache_total_blocks | 查询缓存中的块总数 |
查询缓存 - 开启查询缓存
MySQL的查询缓存默认是关闭的，需要手动配置参数 query_cache_type，来开启查询缓存。
query_cache_type 该参数的可取值有三个：
| 值 | 定义 |
|---|---|
| OFF 或 0 | 查询缓存功能关闭 |
| ON 或 1 | 查询缓存功能打开，SELECT的结果符合缓存条件即会缓存；否则不予缓存；显式指定SQL_NO_CACHE，则不予缓存 |
| DEMAND 或 2 | 查询缓存功能按需进行，显式指定 SQL_CACHE 的SELECT语句才会缓存；其他均不予缓存 |
在 /usr/my.cnf配置中，增加以下配置：
#在开启mysql的查询缓存
query_cache_type=1
配置完毕之后，重启服务即可生效；
然后就可以在命令行执行SQL语句进行验证，执行一条比较耗时的SQL语句，然后再执行多次，查看后面几次的执行时间；
获取通过查看查询缓存的缓存命中数，来判定是否走了查询缓存。
vi /usr/my.cnf
query_cache_type:1
service mysql restart#重启mysql
mysql -u root -p
use demo_02;
select count(*) from tb_item;#耗时 2.58 second
select count(*) from tb_item;#耗时 0.00 second
select count(*) from tb_item;#耗时 0.00 second
select count(*) from tb_item;#耗时 0.00 second
show status 'Qcache'%;
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1038344 |
| Qcache_hits | 4 |
| Qcache_inserts | 1 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 2 |
| Qcache_queryies_in_cache | 1 |
| Qcache_total_blocks | 4 |
select count(*) from tb_item;#耗时 0.00 second
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1038344 |
| Qcache_hits | 5 |
| Qcache_inserts | 1 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 2 |
| Qcache_queryies_in_cache | 1 |
| Qcache_total_blocks | 4 |
查询缓存 - SELECT选项
可以在SELECT语句中指定两个与查询缓存相关的选项：
- SQL_CACHE：如果查询结果是可缓存的，并且query_cache_type 系统变量的值为ON或者DEMAND，则缓存查询结果；
- SQL_NO_CACHE: 服务器不使用查询缓存，它既不检查查询缓存，也不检查结果是否已缓存，也不缓存查询结果。
例子：
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id,name FROM customer;
select * from tb_item limit 2;
select title, sellerid from tb_item where id=1;
show status like 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1038344 |
| Qcache_hits | 5 |
| Qcache_inserts | 3 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 2 |
| Qcache_queryies_in_cache | 3 |
| Qcache_total_blocks | 8 |
select SQL_NO_CACHE title, sellerid from tb_item where id=2;
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1038344 |
| Qcache_hits | 6 |
| Qcache_inserts（添加到缓存的数量） | 3 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 2 |
| Qcache_queryies_in_cache | 3 |
| Qcache_total_blocks | 8 |
select SQL_NO_CACHE title, sellerid from tb_item where id=2;
show status like 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1038344 |
| Qcache_hits | 6 |
| Qcache_inserts（添加到缓存的数量） | 3 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 3 |
| Qcache_queryies_in_cache | 3 |
| Qcache_total_blocks | 8 |
select SQL_NO_CACHE title, sellerid from tb_item where id=2;
select SQL_NO_CACHE title, sellerid from tb_item where id=2;
show status like 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1035936 |
| Qcache_hits | 6 |
| Qcache_inserts（添加到缓存的数量） | 3 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 5 |
| Qcache_queryies_in_cache | 3 |
| Qcache_total_blocks | 8 |
select SQL_CACHE title, sellerid from tb_item where id=3;
select SQL_CACHE title, sellerid from tb_item where id=3;
show status like 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1038344 |
| Qcache_hits | 7 |
| Qcache_inserts（添加到缓存的数量） | 4 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 5 |
| Qcache_queryies_in_cache | 4 |
| Qcache_total_blocks | 10 |
select SQL_CACHE title, sellerid from tb_item where id=3;
select SQL_CACHE title, sellerid from tb_item where id=3;
show status like 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1038344 |
| Qcache_hits | 9 |
| Qcache_inserts（添加到缓存的数量） | 4 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 5 |
| Qcache_queryies_in_cache | 4 |
| Qcache_total_blocks | 10 |
查询缓存 - 失效场景
| 值 | 定义 |
|---|---|
| OFF 或 0 | 查询缓存功能关闭 |
| ON 或 1 | 查询缓存功能打开，SELECT的结果符合缓存条件即会缓存；否则不予缓存；显式指定SQL_NO_CACHE，则不予缓存 |
| DEMAND 或 2 | 查询缓存功能按需进行，显式指定 SQL_CACHE 的SELECT语句才会缓存；其他均不予缓存 |
开启mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果；当数据被修改，之前的缓存就会失效，修改比较频繁的表不适合做查询缓存。
- SQL语句不一致的情况，要想命中查询缓存，查询的SQL语句必须一致。
#SQL1：
select count(*) from tb_item;#3000000 耗时0.00 second
#SQL2：
Select count(*) from tb_item;#3000000 耗时2.79 second
show status like 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1033888 |
| Qcache_hits | 11 |
| Qcache_inserts | 5 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 5 |
| Qcache_queryies_in_cache | 5 |
| Qcache_total_blocks | 12 |
- 当查询语句中有一些不确定的时候，则不会缓存。如：now()、current_date()、curdate()、curtime()、rand()、uuid()、user()、database()；
select now(); # 耗时 0.01 second
select now(); # 耗时 0.01 second；时间是发生了变化的；即不会走缓存
show status like 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1033888 |
| Qcache_hits | 11 |
| Qcache_inserts | 5 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 7 |
| Qcache_queryies_in_cache | 5 |
| Qcache_total_blocks | 12 |
#SQL1：
select * from tb_item where updatetime < now() limit 1;
#SQL2：
select user();
#SQL3：
select databse();
- 不适用任何表查询语句。
select 'Hello';
show status like 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1033888 |
| Qcache_hits | 11 |
| Qcache_inserts | 5 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 8 |
| Qcache_queryies_in_cache | 5 |
| Qcache_total_blocks | 12 |
select 'A';
- 查询mysql、information_schema 或者 performance_schema 数据库中的表时，不会走查询缓存。
show databases;
#information_schema
#mysql
#performance_schema
select * from information_schema.engines;
show status like 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1033888 |
| Qcache_hits | 11 |
| Qcache_inserts | 5 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 9 |
| Qcache_queryies_in_cache | 5 |
| Qcache_total_blocks | 12 |
select * from information_schema.engines;
show status like 'Qcache%';
| Variable_name | Value |
|---|---|
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1033888 |
| Qcache_hits | 11 |
| Qcache_inserts | 5 |
| Qcache_lowmen_prunes | 0 |
| Qcache_not_cached(当前查询没有进行缓存) | 10 |
| Qcache_queryies_in_cache | 5 |
| Qcache_total_blocks | 12 |
-
在存储的函数，触发器或者时间的主体内执行的查询；
-
如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用MERGE映射到已更改表的查询。一个表可以被许多类型的语句，如被改变INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE 或者 DROP DATABASE。
select count(*) from tb_item;#耗时 0.00 second
update tb_item set title='test1' where id=5;
select count(*) from tb_item;#耗时 2.67 second；该SQL失效；但是失效的同时又去进行了缓存；当前查询时并未缓存
select count(*) from tb_item;#耗时 0.00 second；此时再去查询；即查询的缓存区内的内容
内存优化 - 优化原则
内存优化原则
- 将尽量多的内存分配给MySQL做缓存(提高mysql的访问效率)，但要给操作系统和其他程序预留足够内存；
- MyISAM存储引擎的数据文件读取依赖于操作系统自身的I/O缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做I/O缓存。
- 排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。
内存优化 - MyISAM内存优化
MyISAM的缓存机制：
MyISAM存储引擎使用key_buffer缓存索引块（没有缓存数据块），加速MyISAM索引的读写速度，对于MyISAM表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的I/O缓存。
key_buffer_size
key_buffer_size决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。
可以在mysql参数文件中设置key_buffer_size的值，对于一般MyISAM数据库，建议至少将1/4可用内存分配给key_buffer_size。
在/usr/my.cnf 中做如下配置：
key_buffer_size=512M
show variables like 'key_buffer_size';#mysql 命令行
| Variable_name | Value |
|---|---|
| key_buffer_size | 8388608(单位为字节；默认大小) |
read_buffer_size
如果需要经常顺序扫描MyISAM表，可以通过增大read_buffer_size的值来改善性能。
但是需要注意的是read_buffer_size是每个session独占的，如果默认值设置太大，就会造成内存浪费。
read_rnd_buffer_size
对于需要做排序的MyISAM表的查询，如带有 order by 子句的SQL，适当增加read_rnd_buffer_size的值，可以改善此类的SQL性能。
但是需要注意的是read_rnd_buffer_size 是每个session独占的，如果默认值设置太大，就会造成内存浪费。
内存优化 - InnoDB内存优化
innodb的缓存机制：
InnoDB用一块内存区做IO缓存池，该缓存池不仅用来缓存InnoDB的索引块，而且也用来缓存InnoDB的数据块。
innodb_buffer_pool_size
该变量决定了innodb存储引擎表数据和索引数据的最大缓存区大小。
在保证操作系统以及其他程序有足够内存可用的情况下，innodb_buffer_pool_size的值越大，缓存命中率越高，访问innodb表的需要的磁盘I/O就越少，性能也就越高。
show variables like 'innodb_buffer_pool_size';
| Variable_name | Value |
|---|---|
| innodb_buffer_pool_size | 134217728(单位为字节；默认大小128M；134217728/1024-->131072（换算成B），134217728/1024/1024-->128（换算成M）；) |
vi /usr/my.cnf
# innodb_buffer_pool_size=512M #修改内容
innodb_log_buffer_size
决定了innodb重做日志缓存的大小，对于避免产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。
vi /usr/my.cnf
# innodb_log_buffer_size=10M #修改内容
service mysql restart
并发参数调整
mysql并发参数调整
从实现上来说，MySQL Server是多线程结构，包括后台线程和客户服务线程。
多线程可以有效利用服务器资源，提高数据库的并发性能。
在mysql中，控制并发连接和线程的主要参数包括 max_connections、back_log、thread_cache_size、table_open_size；
max_connections（最大连接数）
采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是151。
如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已经达到允许最大值而失败，这时可以考虑增大max_connections的值。
MySQL最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。
在Linux平台下，性能好的服务器，支持500-1000个连接都不是难事，需要根据服务器性能进行评估设定。
back_log
back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。
如果mysql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。
5.6.6版本之前默认值为50，之后的版本默认为50+（max_connections/5），但最大不超过900。
如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log的值。
show variables like 'back_log';
| Variable_name | Value |
|---|---|
| back_log | 80(默认个数) |
table_open_cache
该参数用来控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个SQL执行线程至少要打开一个表缓存，该参数的值应该根据设置的最大连接数max_connections 以及每个连接执行关联查询中涉及的白鸥的最大数量来决定。
max_connections x N;
show variables like 'table_open_cache';
| Variable_name | Value |
|---|---|
| table_open_cache | 2000(默认大小) |
thread_cache_size（线程缓存大小）
为了加快连接数据库的速度，MySQL会缓存一定数量的客户服务线程（线程池）以备重用，通过参数 thread_cache_size 可控制MySQL缓存客户服务线程的数量（线程池的大小）。
show variables like 'thread_cache_size';
| Variable_name | Value |
|---|---|
| thread_cache_size | 9(默认大小9个线程) |
innodb_lock_wait_timeout（innodb行锁的等待时间）
该参数是用来设置InnoDB事务等待行锁的时间，默认值时候50ms。
可以根据需要进行动态设置。
对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起；
对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作。
show variables like 'innodb_lock_wait_timeout';
| Variable_name | Value |
|---|---|
| innodb_lock_wait_timeout | 50 |
锁 - 锁的概述及分类
锁概述
锁是计算机协调多个进程 或者 线程 并发访问某一资源的机制（避免争抢）。
在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。
如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
锁分类
从对数据操作的粒度分为：
- 表锁：操作时，会锁定整个表
- 行锁：操作时，会锁定当前操作行
从对数据操作的类型分：
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
- 写锁（排它锁）：当前操作没有完成之前，他会阻断其他写锁和读锁
锁 - MySQL对锁的支持
相对其他数据库而言，MySQL的锁机制比较简单。
其最显著的特点是不同的存储引擎支持不同的锁机制。
下标罗列出了各个存储引擎对锁的支持情况。
| 存储引擎 | 表级锁 | 行级锁 | 页面锁 |
|---|---|---|---|
| MyISAM | 支持 | 不支持 | 不支持 |
| InnoDB | 支持 | 支持 | 不支持 |
| MEMORY | 支持 | 不支持 | 不支持 |
| BDB | 支持 | 不支持 | 支持 |
MySQL这三种锁的特性可大致归纳如下：
| 锁类型 | 特定 |
|---|---|
| 表级锁 | 偏向MyISAM存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 |
| 行级锁 | 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 |
| 页面锁 | 开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。 |
从上述特点可见，很难笼统的说哪种锁更好，只能就具体应用的特点来说哪种锁更合适。
仅从锁的角度来说：
表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；
而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统；
锁 - MyISAM表锁 - 读锁
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
MyISAM存储引擎支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。
如何加表锁
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等），会自动给涉及的表加写锁，这个过程并不需要用户干预。
因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
显示加表锁语法：
#加读锁：
lock table table_name read;
#加写锁：
lock table table_name write;
读锁案例
准备环境
create database demo_03 default charset=utf8mb4;
use demo_03;
CREATE TABLE 'tb_book'(
'id' INT(11) AUTO_INCREMENT,
'name' VARCHAR(50) DEFAULT NULL,
'publish_name' DATE DEFAULT NULL,
'status' CHAR(1) DEFAULT NULL,
PRIMARY KEY ('id')
)ENGINE=myisam DEFAULT CHARSET=utf8;
# 插入tb_book 基础数据
CREATE TABLE 'tb_user'(
'id' INT(11) AUTO_INCREMENT,
'name' VARCHAR(50) DEFAULT NULL,
PRIMARY KEY('id')
)ENGINE=myisam DEFAULT CHARSET=utf8;
# 插入tb_user 基础数据
show tables;
lock table tb_book read;
select * from tb_books;
select * from tb_user;# Table 'tb_user' was not locked with LOCK TABLES 锁定tb_book表途中无法去操作其他表；除非当前tb_book表锁释放；
update tb_book set name ='solr' where id=2;#当前对tb_book表进行的锁是读锁，而不是写锁；所以当前无法进行更新；Table 'tb_book' was locked with a READ lock and can't be updated.
unlock tables;#只有当当前的tb_book表当中的读锁进行解锁，其他客户端连接mysql的该表时才能够进行写锁；否则将一直等待当前该tb_book的读锁释放；
如果对MyISAM表进行操作了读锁，其不会阻塞其他线程的读操作，但是会阻塞其他线程的写操作。
锁 - MyISAM表锁 - 写锁
写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。
#当前客户端
lock table tb_book write;
select * from tb_book;
update tb_book set name ='solr' where id=2;
insert into tb_book(id,name) values(null,'es');
#其他客户端
select * from tb_book;#其他客户端将一直处于等待状态，除非当前客户端的写锁释放（即当前客户端的业务操作完成），否则其他客户端一直处于等待状态。
#当前客户端
unlock tables;
#其他客户端
select * from tb_book;#当 当前客户端释放锁之后，该其他客户端即立刻能查询到数据。
锁 - MyISAM表锁 - 小结
锁模式的互相兼容性如表所示：
| 当前锁模式/请求锁模式 | None（理解为当前客户端获取得到的锁） | 读锁（其他客户端对于读操作是否可行） | 写锁（其他客户端对于写操作是否可行） |
|---|---|---|---|
| 读锁 | 是（当前客户端获取得到的读锁） | 是（其他客户端可以进行读操作） | 否（其他客户端无法进行写操作） |
| 写锁 | 是（当前客户端获取得到的写锁） | 否（其他客户端无法进行读操作） | 否（其他客户端无法进行写操作） |
由上表可见：
- 对MyISAM表的读操作，不会阻塞其他用户对同一张表的读请求，但是会阻塞对同一张表的写请求。
- 对MyISAM表的写操作，则会阻塞其他用户对同一张表的读和写操作。
简而言之，就是读锁会阻塞写，但是不会阻塞读；而写锁，则既会阻塞读，又会阻塞写。
此外，MyISAM的读写锁调度是写操作优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。
因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
锁 - MyISAM表锁 - 查看锁争用情况
show open tables;
Database: 数据库
Table: 数据表
In_use: 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。
Name_locked: 表名称是否被锁定。名称锁定用于取消表或者对表进行重命名等操作。
lock table tb_book write;
show open tables;#database: demo_03; table: tb_book ; In_use(正在使用):1；Name_locked:0
unlock tables;
show status like 'Table_locks%';
| Variable_name | Value |
|---|---|
| Table_lockes_immediate | 70 |
| Table_locks_waited | 0 |
Table_locks_immediate: 指的是能够立即获得表级锁的次数，每次立即获取锁，值加1；
Table_locks_waited: 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。
锁 - InnoDB行锁 - 介绍及背景知识
行锁介绍
InnoDB默认支持行锁，也支持表锁；
行锁特点：偏向InnoDB存储迎请，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
（由于锁定粒度最小，所以发生锁冲突的概率最低，二者存在关联）
InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁；
(事务和行级锁之间存在关联，支持事务的原因即在于InnoDB采用的是行级锁)
背景知识
事务及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元。
事务具有以下4个特性，简称为事务ACID属性。
| ACID属性 | 含义 |
|---|---|
| 原子性（Atomic） | 事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败 |
| 一致性（Consistent） | 在事务开始和完成时，数据都必须保持一致状态 |
| 隔离性（Isolation） | 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境下运行 |
| 持久性（Durable） | 事务完成之后，对于数据的修改是永久的 |
并发事务处理带来的问题
| 问题 | 含义 |
|---|---|
| 丢失更新（Lost Update） | 当两个或者多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖 |
| 脏读（Dirty Reads） | 当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据（一个事务读取到了另外一个事务还未提交的数据） |
| 不可重复度（Non_repeatable Reads） | 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前独处的数据不一致 |
| 幻读（Phantom Reads） | 一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。 |
事务隔离级别
为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大(性能越低)，因为事务隔离实质上就是使用事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。
数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable ，这四个界别可以逐个解决脏写、脏读、不可重复度、幻读这几类问题。
| 隔离级别 | 丢失更新 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|
| Read uncommitted | x | √ | √ | √ |
| Read committed | x | x | √ | √ |
| Repeatable read(默认) | x | x | x | √ |
| Serializable | x | x | x | x |
备注： √ 代表可能出现， x 代表不会出现。
MySQL的数据库的默认隔离级别是 Repeatable read，查看方式：
show variables like 'tx_isolation';
| Variable_name | Value |
|---|---|
| tx_isolation | REPEATABLE-READ |
锁 - InnoDB行锁 - 类型
InnoDB的行锁模式
InnoDB实现了以下两种类型的行锁：
-
共享锁（S）： 又称为读锁，
简称S锁，
共享锁就是多个事务 对于 同一数据 可以 共享一把锁，都能 访问到 数据，
但是 只能读 不能修改。 -
排它锁（X）：又称为写锁，
简称X锁，
排它锁就是不能与其他锁 并存。
如果一个事务 获取了 一个数据行的排它锁，
其他事务就不能再获取 该行的其他锁，
包括共享锁和排它锁，
但是 获取排它锁的事务 可以对数据 进行 读取 和 修改。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集 加 排它锁（X）；
对于普通SELECT语句，InnoDB不会加任何锁；
可以通过以下语句显式给记录集加共享锁或者排它锁：
#共享锁（S）
SELECT * FROM table_name WHERE ...... LOCK IN SHARE MODE
#排它锁（X）
SELECT * FROM table_name WHERE ....... FOR UPDATE
案例准备工作
create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine=innodb default charset=utf8;
#插入基础数据
#创建单列索引
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);
show tables;
行锁基本演示
| Session-1 | Session-2 |
|---|---|
| 关闭自动提交功能（set autocommit=0;） | 关闭自动提交功能（set autocommit=0;） |
| 可以正常的查询出全部的数据（select * from test_innodb_lock;） | 可以正常的查询出全部的数据（select * from test_innodb_lock;） |
| 查询id为3的数据；（select * from test_innodb_lock where id=3;） | 查询id为3的数据；（select * from test_innodb_lock where id=3;） |
| 更新id为3的数据，但是不提交（update test_innodb_lock set name='AI' where id=3;） | 更新id为3的数据，在Session-1的更新提交完成之前一直处于等待状态；（update test_innodb_lock set name='AII' where id=3;） |
| 通过commit，提交事务（commit;） | 接触阻塞，更新正常进行（update test_innodb_lock set name='AII' where id=3;） |
| 以上，操作的都是同一行的数据，接下来，演示不同行的数据； | |
| 更新id为3的数据，正常的获取到行锁，执行更新；(update test_innodb_lock set name='a1' where id =3;) | 由于与Session-1操作不是同一行，获取当前行锁，执行更新；（update test_innodb_lock set name ="C1" where id=5;） |
锁 - InnoDB行锁 - 行锁升级为表锁
如果不通过索引条件检索数据，那么innodb将对表中的所有记录加锁，实际效果跟表锁一样。
查看当前表的索引：show index from test_innodb_lock;
show index from test_innodb_lock\G;
| Session-1 | Session-2 |
|---|---|
| 关闭事务的自动提交（set autocommit=0;） | 关闭事务的自动提交（set autocommit=0;） |
| 执行更新语句（update test_innodb_lock set sex='2' where name=400; ） | 执行更新语句，但是出于阻塞状态（update test_innodb_lock set sex='2' where id=9;） |
| 提交事务（commit;） | 解除阻塞，执行更新成功（update test_innodb_lock set sex='2' where id=9;） |
| 执行提交操作（commit;） |
由于执行更新时，name字段本来为varchar类型，但是Session-1当中name作为数字类型来使用，所以存在类型转换，索引失效，最终行锁变为表锁；
desc test_innodb_lock;#查看表结构
索引失效：name字段本来的类型为varcahr类型；如果是varchar类型但是在使用的时候，没有给其name字段的取值加上单引号，那么这个时候索引就将失效；索引失效，行锁升级为表锁；
锁 - InnoDB行锁 - 间隙锁危害
当用 范围条件，而不是使用相等条件检索数据，并请求共享或者排它锁的时候，InnoDB会给符合条件的已有数据进行加锁；
对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的 间隙锁（Next-Key锁）；
ID < 10 ：
- 1,2,3,4,5,6,7,8,9
- 1,2,3,4,6,9(此时当中的5 和 7、8 则称之为间隙)
示例：
| Session-1 | Session-2 |
|---|---|
| 关闭事务自动提交（set autocommit=0;） | 关闭事务自动提交（set autocommit=0;） |
| 根据id范围更新数据（update test_innodb_lock set name='332423' where id < 4 ;） | 插入id为2的记录，处于阻塞状态（insert into test_innodb_lock values(2,'1001','1'); ） |
| 提交事务（commit;） | 解除阻塞，执行插入操作（insert into test_innodb_lock values(2,'1001','1'); ） |
| 提交事务（commit;） |
锁 - InnoDB行锁 - 争用情况查看
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行锁 - 总结
Innodb存储引擎 由于实现了 行级锁定 ，
虽然在 锁定机制 的实现方面带来了 性能损耗 可能比表锁会更高一些，
但是在 整体并发处理能力方面要远远 优于 MyISAM的表锁 的。
当 系统并发量较高 的时候，InnoDB的整体性能 和 MyISAM 相比就会有比较明显的优势。
但是，InnoDB的行级锁 同样也有其脆弱的一面，但使用不当的时候，可能会让InnoDB的整体性能不仅不能比MyISAM高，甚至可能会更差。
优化建议：
- 尽可能让所有 数据检索 都能通过 索引 来完成，避免 无索引 行锁 升级为表锁
- 合理设计索引，尽量 缩小锁的范围
- 尽可能减少索引条件，以及索引范围，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可能使用低级别事务隔离（前提：但是需要业务层面满足需求）
SQL技巧 - SQL执行顺序及正则表达式
SQL执行顺序
编写顺序
SELECT DISTINCT
<select list>
FROM
<left_table> <join_type>
JOIN
<right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT
<limit_params>
执行顺序
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT DISTINCT <select list>
ORDER BY <order_by_condition>
LIMIT <limit_params>
正则表达式使用
show tables;
select * from tb_book;
# 正则表达式 regexp
select * from tb_book where name regexp '^j';#查询name字段取值以 “j” 字符开头的记录行
select * from tb_book where name regexp 'S$';#查询name字段取值以 “S” 字符结尾的记录行
select * from tb_book where name regexp '[uvw]';#查询name字段取值包含 “u”、“v”、“w” 字符的记录行
| 符号 | 含义 |
|---|---|
| ^ | 在字符串开始处进行匹配 |
| $ | 在字符串末尾处进行匹配 |
| . | 匹配任意单个字符，包括换行符 |
| [...] | 匹配出括号内的任意字符 |
| [^...] | 匹配不出括号内的任意字符 |
| a* | 匹配零个或者多个a（包括空串） |
| a+ | 匹配一个或者多个a（不包括空串） |
| a? | 匹配零个或者一个a |
| a1|a2 | 匹配a1或者a2 |
| a(m) | 匹配m个a |
| a(m,) | 至少匹配m个a |
| a(m,n) | 匹配m个a 到n个a |
| a(,n) | 匹配0到n个a |
| (...) | 将模式元素组成单一元素 |
SQL技巧 - 数字函数与字符串函数
数字函数
| 函数名称 | 作用 |
|---|---|
| ABS | 求绝对值 |
| SQRT | 求二次方根 |
| MOD | 求余数 |
| CEIL和CEILING | 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整 |
| FLOOR | 向下取整，返回值转化为一个BIGINT |
| RAND | 生成一个0-1之间的随机数，传入整数参数是，用来产生重复序列 |
| ROUND | 对所传入参数进行四舍五入 |
| SIGN | 返回参数的符号 |
| POW和POWER | 两个函数的功能相同，都是所传参数的次方的结果值 |
| SIN | 求正弦值 |
| COS | 求余弦值 |
| ACOS | 求反余弦值，与函数COS互为反函数 |
| TAN | 求正切值 |
| ATAN | 求反正切值，与函数TAN互为反函数 |
| COT | 求余切值 |
字符串函数
| 函数名称 | 作用 |
|---|---|
| LENGTH | 计算字符串长度函数，返回字符串的字节长度 |
| CONCAT | 合并字符串函数，返回结果为连接参数产生的字符串，参数可以使用一个或者多个 |
| INSERT | 替换字符串函数 |
| LOWER | 将字符串中的字母转换为小写 |
| UPPER | 将字符串中的字母转换为大写 |
| LEFT | 从左侧截取字符串，返回字符串左边的若干个字符 |
| RIGTH | 从右侧截取字符串，返回字符串右边的若干个字符 |
| TRIM | 删除字符串左右两侧的空格 |
| REPLACE | 字符串替换函数，返回替换后的新字符串 |
| SUBSTRING | 截取字符串，返回从指定位置开始的指定长度的字符串 |
| REVERSE | 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串 |
SQL技巧 - 日期函数与聚合函数
日期函数
| 函数名称 | 作用 |
|---|---|
| CURDATE和CURRENT_DATE | 两个函数作用相同，返回当前系统的日期值 |
| CURTIME和CURRENT_TIME | 两个函数作用相同，返回当前系统的时间值 |
| NOW和SYSDATE | 两个函数作用相同，返回当前系统的日期和时间值 |
| MONTH | 获取指定日期中的月份 |
| MONTHNAME | 获取指定日期中的月份英文名称 |
| DAYNAE | 获取指定日期对应的星期几的英文名称 |
| DAYOFWEEK | 获取指定日期对应的一周的索引位置值 |
| WEEK | 获取指定日期是一年中的第几周，返回值的范围是否为0~52或者1~53 |
| DAYOFYEAR | 获取指定日期是一年中的第几天，返回值范围是1~366 |
| DAYOFMONTH | 获取指定日期是一个月中的第几天，返回值是1~31 |
| YEAR | 获取年份，返回值范围是1970~2069 |
| TIME_TO_SEC | 将时间参数转换为秒数 |
| SEC_TO_TIME | 将秒数转换为时间，与TIME_TO_SEC互为反函数 |
| DATE_ADD和ADDDATE | 两个函数功能相同，都是向日期添加指定的时间间隔 |
| DATE_SUB和SUBDATE | 两个函数功能相同，都是向日期减去指定的时间间隔 |
| ADDTIME | 时间加法运算，在原始时间上添加指定的时间 |
| SUBTIME | 时间减法运算，在原始时间上减去指定时间 |
| DATEDIFF | 获取两个日期之间间隔，返回参数1减去参数2的值 |
| DATE_FORMAT | 格式化指定的日期，根据参数返回指定格式的值 |
| WEEKDAY | 获取指定日期在一周内的对应的工作日索引 |
聚合函数
| 函数名称 | 作用 |
|---|---|
| MAX | 查询 指定列的最大值 |
| MIN | 查询 指定列的最小值 |
| COUNT | 统计查询结果的行数 |
| SUM | 求和，返回 指定列的总和 |
| AVG | 求平均值，返回 指定列数据的平均值 |
扫一扫
179
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
