Mysql监控调优

提升性能

1、允许情况下，调大连接数

2、开启查询缓存（看命中率，用在变化不大的表内）

3、锁（查看是否存在死锁）

4、慢查询（将执行时间过长的语句写入日志内）

5、explain（分析表结构，type 为 ALL或extra 为 Using filesort、Impossible where、Using join buffer则一定要优化）

关系型数据库概念，有啥？

非关系型数据库概念，有啥？

要对表操作，是有权限控制的，我们自动会忽略这个权限问题

查询走硬盘的话，硬盘会影响性能。ssd>hdd

存储引擎：innodb

首先，让我们来看一下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_number >

然而当数据库接收到查询请求以后会先对数据进行解析，解析后查询顺序就变成了下面这样

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

sql语法的顺序：

1、from……where之间，是取表，取库，去磁盘里面把数据加载到内存里面，也就是磁盘的io会影响性能，读磁盘操作（整表全部放内存）

2、where，group by，having之后是条件，where实现的是筛选功能。这一步是在哪操作的？是在内存里面操作的（分组占用 cpu 特别高），这里对 cpu 的性能影响很大

　　假设 where 之后筛选出 1W 数据，在 group by 后剩下 3000 ，having 后剩 1000 数据

3、order by 也是很耗费 cpu 的，排序算法，肯定很耗费 cpu

可以看到整个逻辑：取数据，选数据，排数据（取选排）伴随着的性能问题：io，内存和 cpu

抢 cpu 是怎么造成的？

进程都在占用 cpu ，突然一个 A 进程抢 cpu 暴增，那么其他的也会抢。比如说 a 进程要 10 m内存，内存 1%，那么没干完，被其他的抢走了，就会再要内存，内存就变成 2%，就这么其他的抢占也会一点一点增起来。一个高，另外的也会高，互相神仙打架。死的最多的是 tomcat ，java进程

DDL （Data Definition Language 数据定义语言）

create table 创建表
alter table  修改表
drop table 删除表
truncate table 删除表中所有行（把自增id清零）
create index 创建索引
drop index  删除索引

DML （Data Manipulation Language 数据操作语言）

insert 将记录插入到数据库
update 修改数据库的记录
delete 删除数据库的记录

4、Mysql连接数

　　线程的连接数，比如 A 用户连一个， B用户连一个。

　　这里有个三次握手的概念，比如说客户端a问服务端b，我能打你么？b说，可以。那么a就去打b了，在他们没有说再见的状况下，b是一直等待被a打的，不会等其他的人来打

比如说我连上了 一个 mysql ，我不告诉 mysql 断开。是不会断开的，在我们一定的连接时间内（时间可配置）假设 30s，30s 内及时没有数据操作，这个数据库连接就是被占用的。影响：如果用户访问多，我们的连接数满了，其他的用户就连不上了。

那么我们引入连接池连接的方法。连接池，比如说最大连接数 100个，我设连接池 50 个，我设置的 50 为啥不设为 100 呢？为啥只设置 50？

　　这么想，比如我最大连接数 100 ，连接池如果也设置 100 ，其他人连接就肯定连不上了。连接池这个 50 是在哪设置？其实是在代码里面，是应用服务自己创建的连接池，是比如说我现在建立一个 50 个连接的连池，应用服务会一直跟 mysql 去沟通：嘿，我还要用这 50 个连接池呢 ，在超时之前，每隔一段时间，跟 mysql 絮叨一回，我还占着呢，你别让别人进来。mysql 接收到这消息之后，就把这50个连接就一直留给这个应用服务。剩下的 50 个连接闲置，谁想用，谁想连就连。

　　那么连接池这边，我去传给连接池 一个 SQL 语句，执行了，返回正确结果了。那么下一个 SQL 语句过来，就不用管连接池是否还在等待链接，因为只要返回正确结果了，代表这个操作完成了。就相当于一条队列，我给你 10 条 sql 语句，按照顺序执行就好了，而不是说给你 10 条 sql ，你建立 10 次连接，等 30s 断开，再执行第二条，依次执行到第 10 条，这样效率肯定低。但是如果我通过代码的连接池，我相当于是调用一个功能，我给你 10 条sql ，你给我返回回来结果，返回回来后，你就给我闲置。你就可以开始接下来的操作。

　　那么为啥这个连接池要设置 50 ？如果设置成 100 ，结果会怎样？第一个问题，假设数据库挂了，管理员怎么连？所以肯定不能设定为 100 。可以去找找这种连接池的代码，看一看，玩一玩

　　

　　MYSQL数据库默认最大连接数是100，然而对于流量稍微大一点的论坛或网站这个连接数是远远不够的，当并发数过大的时候会出现连接数不够用，使得很多线程在等待其他连接释放，会直接导致导致数据库连接超时或者响应时间过长，所以需要调整最大连接数。

# 重新设置数据库最大连接数
set global max_connections=1000;

# 查询数据库当前设置的最大连接数
show variables like '%max_connections%';
MariaDB [(none)]> show variables like '%max_connections%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| extra_max_connections | 1     |    # extra_port可以接受的最大连接数
| max_connections       | 151   |    # 最大连接数
+-----------------------+-------+
2 rows in set (0.00 sec)

# extra_port是之前5.6版本开始新增的，指定了可以使用的端口

# 服务器响应的最大连接数
show global status like 'Max_used_connections';
MariaDB [(none)]> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 4     |
+----------------------+-------+
1 row in set (0.00 sec)

# 服务器线程方面的
show status like 'Threads%';
MariaDB [(none)]> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |    # mysql管理的线程池中还有多少可以被复用的资源
| Threads_connected | 3     |    # 打开的连接数（当前创建的，包括没工作的那些）
| Threads_created   | 226   |    # 表示创建过的线程数（累计的）
| Threads_running   | 1     |    # 激活的连接数，这个数值一般远低于connected数值，
                                # 准确的来说，Threads_running是代表当前并发数
+-------------------+-------+
4 rows in set (0.00 sec)

　　1、连接数这里查询是 151 是不是改成最大的就好呢？

　　151的意思是，只允许151个用户连接，其他的超过的用户，要进行排队。那么设置成最大就好么？不是的！假设咱们盲目地扩大这个数值，假设咱们服务器最大能支撑起 1000 个用户进行操作，那么这个连接数我设置了假如说 5000 ，那么当有 5000 个用户进行操作，服务器是处理不过来的。那么咋办？就会崩掉，连 1000 个都处理不了了。这就叫得不偿失和人心不足蛇吞象。这样的话，我们的最大连接数，根据我们的业务量和请求量，服务器的资源，以及数据库内表的量级去评估，到底设置多大的连接数。

　　但是也不能过于小，像默认的 151 如果在允许的情况下，设置成 1000 ，是可以解决一些排队的问题的

　　2、查询/设置连接数

　　# 重新设置数据库最大连接数 set global max_connections=1000;

　　# 查询数据库当前设置的最大连接数 show variables like '%max_connections%';

　　什么时候去调大连接数呢？当前的线程大于连接数肯定就得调了

　　3、我怎么判断，最大连接数为多大？数据库建立以来，历史以来，最大的连接接入数：show global status like 'Max_used_connections';

　　如果这个数字远小于现有的数据库连接数，那就没有必要从这里入手，去调节连接数的设置了

5、查询缓存Query cache

查询缓存会缓存完整的SELECT查询结果，当查询命中缓存时MySQL会将结果立刻返回，直接跳过了解析、优化和执行阶段。

当然，Query Cache 也有一个致命的缺陷，那就是当某个表的数据有任何任何变化（更新插入删除），都会导致所有引用了该表的select语句在Query Cache 中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache 可能会得不偿失。

因此，应用程序不需要关心MySQL是通过缓存查询出的结果还是实际执行过SQL语句返回的结果，因为这两种结果是完全相同的。

从前面的数据库执行过程图中可以看到，执行一条SQL查询语句会先查询该语句是否存在于缓存中，需要注意的是当语句中的字符大小写或注释只要有一点点的不同，查询缓存就会被认为是不同的查询，导致无法命中查询缓存。另外，对于不确定的函数，如：now()、current_date()等这种查询都不会被缓存。

既然查询缓存的有可以改善性能的优点，自然也有自己的缺点，主要体现在当开启了查询缓存时对于读写操作都增加了额外的开销。相对于读，再查询开始前需要先检查缓存，而对于写，则是当写入数据后需要更新缓存。

　　配置查询缓存的方法： find / |grep my.cnf 或者 find / -name my.cnf

# 1.是否开启查询缓存，具体选项是off，on
query_cache_type = on
query_cache_type = 1　　## 最好用这个，否则容易出现 mysqld 服务起不来的问题
# 2.分配给查询缓存的总内存，一般建议不超过256M
query_cache_size = 200M 

# 3.这个选项限制了MySQL存储的最大结果。如果查询的结果比这个大，那么就不会被缓存。
query_cache_limit = 1M

# 查询qcache状态：

MariaDB [(none)]> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |  #该MySQL 是否支持Query Cache
| query_cache_limit            | 1048576 |  #缓存块大小，超过该大小不会被缓存
| query_cache_min_res_unit     | 4096    |  #每个qcache最小的缓存空间大小
| query_cache_size             | 1048576 |  #分配给查询缓存的总内存
| query_cache_strip_comments   | OFF     |  #用于控制QC中是否去掉SQL语句的注释部分。
| query_cache_type             | OFF     |  #是否开启（这里就是代表没开启查询缓存）
| query_cache_wlock_invalidate | OFF     |  #控制当有锁加在表上的时候，是否先让该表相关的缓存失效
+------------------------------+---------+
rows in set (0.00 sec)

把上边代码的前3行，粘贴到 mysql 的配置文件内，实测：粘贴到最后面（课堂上说是粘贴到 mysql 内，但是重启失败，应该是版本的问题）

这里要注意，query_cache_type 和 query_cache_size，其中任何一个参数设置为0都意味着关闭查询缓存功能。配置文件设置很坑爹：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_size = 200M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

query_cache_type = on
query_cache_limit = 1M

结果如下：（把三条全部粘贴到最下面，query_cache_size不生效）

看查询缓存的命中率

# 查询qcache当前使用情况：
SHOW STATUS LIKE 'Qcache%';
SHOW GLOBAL STATUS LIKE 'Qcache%';（同一个实例的其他库要用全局）
MariaDB [(none)]> show status like 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       | # 表示查询缓存中处以重现状态的内存块数（碎片数量）。如果
                                      # Qcache_free_blocks 的值较大，则意味着查询缓存中碎片比较多
                                      # 表明查询结果集较小，此时可以减小query_cache_min_res_unit
                                      # 使用flush query cache 会对缓存中的若干个碎片进行整理，从
                                      # 而得到一个比较大的空闲块。
| Qcache_free_memory      | 1031336 | # Query Cache 中目前剩余的内存大小
| Qcache_hits             | 0       | # 缓存命中次数
| Qcache_inserts          | 0       | # 多少次未命中然后插入
| Qcache_lowmem_prunes    | 0       | # 因为查询缓存已满而溢出，导致MySQL删除的查询结果个数。
                                      # 如果该值比较大，则表明查询缓存过小。
| Qcache_not_cached       | 0       | # 没有进入查询缓存的select个数
| Qcache_queries_in_cache | 0       | # 查询缓存中缓存中有多少条select语句的结果集
| Qcache_total_blocks     | 1       | # 查询缓存的总个数
+-------------------------+---------+
rows in set (0.00 sec)

Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts )；（这里的公式是错误的，以下面的为准）

我们用三条命令去查，看看命中率变化

show status like 'Qcache%';
select * from cource WHERE Id = 101;
show status like 'Qcache%';

说明命中了缓存。第一次执行一条 sql 语句，应该是 qcache_insert 加1，但是第二次执行 这条 sql 应该是 qcache_insert 不变，qcache_hits 加 1

要注意的是，这里缓存要命中，是要求两条语句一模一样，里面连空格都不能有，或者说改大小写！因为是通过全字符匹配，才能作为一个缓存去查。

符合缓存命中的几点，参考

1. 查询语句中加了SQL_NO_CACHE参数；
2. 查询语句中含有获得值的函数，包涵自定义函数，如：CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ等；
3. 对系统数据库的查询：mysql、information_schema 查询语句中使用SESSION级别变量或存储过程中的局部变量；
4. 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句 查询语句中类似SELECT …INTO 导出数据的语句；
5. 对临时表的查询操作； 存在警告信息的查询语句； 不涉及任何表或视图的查询语句； 某用户只有列级别权限的查询语句；
6. 事务隔离级别为：Serializable情况下，所有查询语句都不能缓存

#走缓存失败的
show status like 'com_select';

有哪几种原因会导致走缓存失败的？

1、查询缓存里面没有

2、数据量过大，超过我预计的查询缓存的大小

综上：命中率=命中率/（命中数+没有走缓存的）【这里存疑】

命中率 = Qcache_hits/（com_select+Qcache_hits）

下面的要自己了解下：（课上未详细讲）

• 存储引擎层-innodb buffer pool

buffer pool是innodb存储引擎带的一个缓存池，查询数据的时候，它首先会从内存中查询，如果内存中存在的话，直接返回，从而提高查询响应时间。

innodb buffer pool和qcache的区别是：qcacche缓存的是sql语句对应的结果集，buffer pool中缓存的是表中的数据。Buffer pool是设置的越大越好，一般设置为服务器物理内存的70%。

innodb_buffer_pool_size = 16M    # Innodb_buffer_pool的大小 

innodb_buffer_pool_dump_now:  默认为关闭OFF。如果开启该参数，停止MySQL服务时，InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘。

innodb_buffer_pool_load_at_startup:默认为关闭OFF。如果开启该参数，启动MySQL服务时，MySQL将本地热数据加载到InnoDB缓冲池中。

• 查看Innodb_buffer_pool状态

查询Innodb_buffer_pool状态：
SHOW VARIABLES LIKE '%innodb_buffer_pool%';

MariaDB [(none)]> SHOW VARIABLES LIKE '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            | ##停止mysq服务时是否自动保存热数据
| innodb_buffer_pool_dump_now         | OFF            | ##启动mysql服务时是否自动读取热数据
| innodb_buffer_pool_dump_pct         | 100            | #表示转储每个bp instance LRU上最热的page的百分比。
| innodb_buffer_pool_filename         | ib_buffer_pool | #热数据文件名称
| innodb_buffer_pool_instances        | 8              | # 表示InnoDB缓存池被划分到多少个区域
| innodb_buffer_pool_load_abort       | OFF            | # 立刻中断LOAD操作
| innodb_buffer_pool_load_at_startup  | OFF            | # 启动实例时读入转储文件中记录的Page
| innodb_buffer_pool_load_now         | OFF            | # 立即做一次转储文件读入
| innodb_buffer_pool_populate         | OFF            |
| innodb_buffer_pool_size             | 16777216       | ##设置的bp大小
+-------------------------------------+----------------+
10 rows in set (0.01 sec)

• 监控Innodb_buffer_pool使用情况

查询Innodb_buffer_pool当前使用情况：
SHOW STATUS LIKE '%Innodb_buffer_pool%';

MariaDB [(none)]> SHOW STATUS LIKE '%Innodb_buffer_pool%';
+-----------------------------------------+----------------------------------------+
| Variable_name                           | Value                                  |
+-----------------------------------------+----------------------------------------+
| Innodb_buffer_pool_bytes_data           | 4194304                                |
| Innodb_buffer_pool_bytes_dirty          | 0                                      |
| Innodb_buffer_pool_dump_status          | Dumping buffer pool(s) not yet started |
| Innodb_buffer_pool_load_status          | Loading buffer pool(s) not yet started |
| Innodb_buffer_pool_pages_data           | 256                                    |
| Innodb_buffer_pool_pages_dirty          | 0                                      |
| Innodb_buffer_pool_pages_flushed        | 6186                                   |
| Innodb_buffer_pool_pages_free           | 765                                    |
| Innodb_buffer_pool_pages_lru_flushed    | 0                                      |
| Innodb_buffer_pool_pages_made_not_young | 4584                                   |
| Innodb_buffer_pool_pages_made_young     | 0                                      |
| Innodb_buffer_pool_pages_misc           | 2                                      |
| Innodb_buffer_pool_pages_old            | 0                                      |
| Innodb_buffer_pool_pages_total          | 1023                                   |
| Innodb_buffer_pool_read_ahead           | 831                                    |
| Innodb_buffer_pool_read_ahead_evicted   | 0                                      |
| Innodb_buffer_pool_read_ahead_rnd       | 0                                      |
| Innodb_buffer_pool_read_requests        | 1758225                                |
| Innodb_buffer_pool_reads                | 8144                                   |
| Innodb_buffer_pool_wait_free            | 0                                      |
| Innodb_buffer_pool_write_requests       | 13403                                  |
+-----------------------------------------+----------------------------------------+
21 rows in set (0.00 sec)

主要关注的两个参数

  innodb_buffer_pool_read_requests 总共查询bp的次数
  innodb_buffer_pool_reads 从物理磁盘中获取到数据的次数

6、事务和锁

　　事务就是一组原子性的SQL查询，或者也可以说一个独立的工作单元。如果数据库引擎可以成功的对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中任意一条语句因为某种原因无法执行，那么其他的语句都不会执行。也就是说，事务内的语句要么全部都执行，要么全部执行失败。

　　我们在 lr 和 jmeter 内有事物的概念，比如说注册：里面要输账户名，密码，邮箱校验，点击注册按钮等。这我们写在一个事务内，在结果内就相当于是一个请求。这里的 sql 也是一样。

　　正常来讲，我们的一个 sql 就是 select XXX from XXX where XXX；但是我们要理解，每一个 sql 语句是一个“事务”，那么里面我们拆分出来，是有哪些东西呢？？　

START TRANSACTION;

select *fromxxx；

commit；

　　那么，很容易理解：

　　select aaa from AAA 和 select bbb from BBB 是两个事务

　　

　　那么，如果我把两个语句，写进一个事务内，会怎么样？（这问题先留着，往下看）

START TRANSACTION;

select aaa from AAA;

select bbb from BBB;

commit；

　　我们如果一个事务里面，有 insert/update 语句，如果不 commit ，是不会对数据库进行更改的，就相当于，我们在界面华工具内改数据，要点 “√”，是一样的。我们的数据库内默认是有 auto commit; 的，也就是事务进来是默认自动提交，这个是可以关闭的，可以自己尝试下

　　

那么这里就有问题了：

情况1：

这里有左右两个事务，逻辑如下，同时执行

结果会怎样？左边的事务要把 aa 改成 bb；右边的事务，把 aa 修改成 bb ，bb 改成 cc。那么假设这两个事务同时进行，因为并发这样是有可能的。

那么问题来了，假设左边的先运行，随之右边的也运行，左边是创建 aa 数据，右边的能不能将 aa 改成 bb？

答案是不能，因为左边的事务还是没提交的状态，根本就不存在 aa 这条数据，右边改啥？没东西改。这样应该是先执行左边的事务，再执行右边的事务

情况2：

问题：如果左边的事务t1先执行，但是还没提交。右边的事务t2能不能执行？？（问题存疑）

先引入一个概念：（需要注意的是，查询是不锁的，只有对数据有操作的才会去锁）

表级锁和行级锁，那么这俩各是啥呢？表级锁很容易理解，就是把整张表锁起来，行级锁就是对整行锁住。其他的事务操作不了

锁是为了什么？为的是要有一个准确的数据，比如：update user set a=1 和 update user set a=2，广义上并发执行，那么假设没有锁，我该以哪个为准呢？哪个算先后呢？这样的更新是不准的，所以要有锁，但是select 都是去查现有状态，以现有为准自然也就不需要去锁

我们 innodb默认用的是：行级锁。我们的 mysql 就是 innodb的存储引擎。那么上面的问题就有答案了：

t1 事务去操作 id 为1的这行数据，会先把这行数据给锁起来，何时释放呢？事务结束提交就会释放。也就是说先执行完 t1 的 sql1 和 sql2，才会执行 t2

也就是说在行级锁的情况下：左右两个事务操作的不是同一行，就可以并行执行

那么我们极端一点：这样 t1 没有指定行，就会把全表给锁住，那么 t2 也就不能执行了

再引入一个概念：悲观锁和乐观锁（需要去了解）

大体的意思是，乐观锁是我认为我这个事务一定会把你锁住，对其他的事物不造成影响

死锁：

我们都对 a 表去操作，情况如下

　　我们可以看一下，在行级锁的情况下，t1 和 t2 并发， t1 会把 id 为 1 的给锁住，t2 会把 id 为 2 的给锁住；那么都往下执行第二句，我的 t1 要去更新 id 为 2 的这一行，但是 id 为  2 这一行是被 t2 锁住的， t2 也面临同样的问题， id 为 1 的这一行被 t1 锁住了。这就尴尬了……这个时候还能操作么？肯定就两条都不能释放，两者互相依赖了，这样就会出现死锁！

　　这样的情况下，这两条语句都不能进行更新了

　　

接下来我们模仿一下死锁的问题：

我们这里不提交，看一下死锁

t1事务：

START TRANSACTION;
UPDATE score set grade = 95 where id = 5;
UPDATE score set grade = 87 where id = 6;

t2事务：

START TRANSACTION;
UPDATE score set grade = 78 where id = 6;
UPDATE score set grade = 85 where id = 5;

我们用以下命令看死锁状态：

我们可以看到，Lock wait

查看是否死锁：
show engine innodb status;（一般日志里有dblock、lock等字样）
SELECT * FROM information_schema.INNODB_TRX; （定位哪个线程导致死锁）

杀掉死锁进程：只能一个一个杀
show processlist;
KILL xxxx;

　　把进程用命令杀死

把锁死的 全部 kil 掉，再去执行我们的 update 的单个命令，发现是可以正常执行的

1. 多线程并发容易出现死锁；
2. 尽量避免全表更新；

• 表级锁和行级锁

行级锁又分共享锁和排他锁。

共享锁：共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。

SELECT `Id` FROM Score WHERE Id in (1,2);  LOCK IN SHARE MODE 结果集的数据都会加共享锁

排他锁：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。for update仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。

SELECT `Id` FROM Score WHERE Id=1 FOR UPDATE

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点

开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

• 死锁

我们在测试中经常会遇到数据库死锁的情况，那么什么是死锁呢？ 死锁是指两个或者多个事务在同一资源上互相占用，并且请求锁定对方占用的资源，从而导致恶性循环的现象。

事务1：

START TRANSACTION;
UPDATE score set grade = 95 where id = 5;
UPDATE score set grade = 87 where id = 6;
COMMIT;

事务2：
START TRANSACTION;
UPDATE score set grade = 78 where id = 6;
UPDATE score set grade = 85 where id = 5;
COMMIT;

当两个事务同时执行，每个事务都执行了第一条update语句，更新了一行数据的同时也会锁定该行数据，接下来每个事务会尝试去执行第二条update语句，在这时会发现要修改的这条数据已经被锁定了，然后两个事务都在互相等待对方释放锁，同时又都持有对方所需要的锁，在这样的情况下就陷入了死循环。也就是死锁了，对于出现死锁的情况下，只有通过外部因素来介入来解除死锁。

当然既然存在有死锁的情况，自然也就有一些解决问题的方法，数据库系统实现了各种死锁检测和死锁超时机制。比如InnoDB存储引擎，就能检测到死锁的循环依赖，并且立即会返回一个错误。

锁的行为和顺序是和存储引擎相关的，以同样的顺序去执行语句时，有些存储引擎会出现死锁，有些就不会。因此死锁的产生有双重原因，有些是因为真正的数据冲突，有些则是因为存储引擎的实现方式导致。

如何查看死锁呢？

  查看是否死锁：

show engine innodb status;（一般日志里有dblock、lock等字样）
SELECT * FROM information_schema.INNODB_TRX; （定位哪个线程导致死锁）

查看死锁进程并kil
show processlist;
KILL xxxx;

• 多线程并发才有可能死锁
• 避免交叉加锁
• 减少涉及的表，表联接会大大增加锁范围
• 避免全表更新，控制更新行数

二、Mysql慢查询

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。顾名思义，慢查询日志中记录的是执行时间较长的query，也就是我们常说的slowquery，通过设–log-slow-queries[=file_name]来打开该功能并设置记录位置和文件名。

慢查询日志采用的是简单的文本格式，可以通过各种文本编辑器查看其中的内容。其中记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。MySQL 还提供了专门用来分析满查询日志的工具程序mysqlslowdump，用来帮助数据库管理人员解决可能存在的性能问题。

　　

　　查询慢查询相关设置：

MariaDB [(none)]> show variables like 'slow_query%';
+---------------------+---------------+
| Variable_name       | Value         |
+---------------------+---------------+
| slow_query_log      | OFF           |
| slow_query_log_file | liml-slow.log |
+---------------------+---------------+
rows in set (0.00 sec)

MariaDB [(none)]> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
row in set (0.00 sec)

slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录，默认为10秒

　　

　　配置慢查询

Linux:
在mysql配置文件my.cnf中增加：
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/xiaowenshu-slow.log
long_query_time= 

Windows:
在my.ini的[mysqld]添加如下语句：
log-slow-queries = E:\web\mysql\log\mysqlslowquery.loglong_query_time =
(其他参数如上)

slow_query_log_file (指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log)
long_query_time(记录超过的时间，默认为10s)
slow_query_log 记录慢查询日志开关

　　

　　使用命令启动慢查询日志：（重启失效）

将 slow_query_log 全局变量设置为“ON”状态
mysql> set global slow_query_log='ON'; 

设置慢查询日志存放的位置
mysql> set global slow_query_log_file='/opt/slow.log';

查询超过1秒就记录
mysql> set global long_query_time=;

　　这里配置会有个问题 ：如果我们的慢查询日志文件，不赋予权限，是开启不了的 ，我们可以在命令行中试下：

set global slow_query_log=;

结果：

ERROR  (HY000): File '/var/lib/mysql/xiaowenshu-slow.log' not found (Errcode:  - Permission denied)

　　出现这个问题就说明这个文件对于：mysql 用户组和 mysql 用户没有执行权限，修改一下即可：chown -R mysql:mysql  /var/lib/mysql/xiaowenshu-slow.log，或者直接 chmod 777 /var/lib/mysql/xiaowenshu-slow.log  再重启一下 mysql 就大功告成

　

　　此时，我们可以用一个 sql 试一下，看看如果执行时间过长，会不会写入到日志内：（我们设置的时间为 2s，这里我们执行一个 5s 的sql）

SELECT SLEEP() 

　　日志内的结果：

use test;　　##这个说明操作的是哪个数据库
SET timestamp=;
SELECT SLEEP();

　　这个日志文件一般是不开的，因为会越来越大，分析才开。这个有啥用处？sql 的执行时间，用户是感知不了的，我们定义一个阈值，超过了，就写入日志内

• Mysqldumpslow命令

mysqldumpslow -s c -t  /database/mysql/slow-log
#这会输出记录次数最多的10条SQL语句，其中：

-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；

• explain

前面分析出了具体是哪一条SQL导致的查询速度过慢，那么我们该如何针对这些占用资源严重的SQL来进一步的分析呢？？答案就是使用explain

Explain :该命令是查看查询优化器如何决定执行查询的主要方法，这个功能有局限性，只是一个近似结果，有时它是一个很好的近似，有时可能相差甚远。但它的输出是可以获取的最准确信息，值得仔细学习。使用方法如下：

MariaDB [besttest]> select * from students where id in (801,802,803);
+-----+-----------+------+------+--------+--------------------+
| Id  | Name      | Sex  | age  | class  | Addr               |
+-----+-----------+------+------+--------+--------------------+
| 801 | 安大叔    | 男   |   20 | besttest| 北京市海淀区        |
| 802 | 小胖子    | 男   |   38 | besttest| 北京市昌平区        |
| 803 | 小楠楠    | 男   |   18 | besttest| 湖南省永州市        |
+-----+-----------+------+------+--------+--------------------+
3 rows in set (0.00 sec)

MariaDB [besttest]> explain select * from students where id in (801,802,803);
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1  | SIMPLE      | students | range | PRIMARY | PRIMARY | 4     | NULL | 3 | Using where |
+------+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

　　

explain 不需要有那条数据，比如说：EXPLAIN SELECT * FROM score WHERE Id=100; 可以理解为只从表结构进行分析

这是有值的情况：

无值的情况：

explain的列：

我们先完成一个建表

CREATE TABLE `t1` (`id`  int NOT NULL AUTO_INCREMENT ,`name`  varchar(255) NULL ,`age` INT(3) NULL,PRIMARY KEY (`id`));
CREATE TABLE `t2` (`id`  int NOT NULL AUTO_INCREMENT ,`name`  varchar(255) NULL ,`age` INT(3) NULL,PRIMARY KEY (`id`));
CREATE TABLE `t3` (`id`  int NOT NULL AUTO_INCREMENT ,`name`  varchar(255) NULL ,`age` INT(3) NULL,PRIMARY KEY (`id`));

然后我们执行一个 sql 语句：

explain select t2.* from t2 where id =
(select id from t1 where id = (select t3.id from t3 where t3.name=''));

explain select t2.* from (select t3.id from t3 where t3.name='')s1, t2 where s1.id=t2.id;

结果1：

结果2：

我们需要关注的是这个 type ，常见的访问类型为：ALL, index, range, ref, eq_ref, const, system, NULL，从左到右，性能从最差到最好

我们用几个例子来说明，这几个 type 的不同之处：

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

MariaDB [besttest]> explain select * from t1 where name='';
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

index：Full Index Scan，index与ALL区别为index类型只遍历索引树

MariaDB [besttest]> explain select id from t1;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 4       | NULL |    1 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。索引范围扫描是带有between或者where子句里带有<, >查询。当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。

MariaDB [besttest]> explain select * from t1 where id in (2,6);
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行

MariaDB [besttest]> explain select * from t1 where name = 'zhang';
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

eq_ref类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

MariaDB [besttest]> explain select t1.name from t1, t2 where t1.id=t2.id;
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra       |
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
|    1 | SIMPLE      | t1    | ALL    | PRIMARY       | NULL    | NULL    | NULL           |    1 |             |
|    1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | besttest.t1.id |    1 | Using index |
+------+-------------+-------+--------+---------------+---------+---------+----------------+------+-------------+
2 rows in set (0.00 sec)

const,system,当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

MariaDB [besttest]> explain select * from ( select * from t1 where id=1) b;
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

NULL，MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

MariaDB [besttest]> explain select * from t1 where id = (select min(id) from t2);
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|    1 | PRIMARY     | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
|    2 | SUBQUERY    | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No matching min/max row                             |
+------+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
2 rows in set (0.00 sec)

　　我们在优化过程中， type 为 ALL 的语句是一定要干掉的

possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

还需要关注 extra 字段，包含不适合在其他列中显示但十分重要的额外信息，内容为以下三个，一定要优化的：

• Using filesort：MySQL中无法通过索引，去进行排序，而是直接通过名字去排序
• Impossible where：这个值强调了where语句会导致没有符合条件的行。
• Using join buffer：该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

综上所述， type 为 ALL 和 extra 为 Using filesort、Impossible where、Using join buffer 是需要优化的

怎么用 explain 呢？我们在慢查询的记录里面，可以查询出那些耗时较长的 sql 语句，语句我们就可以用 explain 命令去分析出来，到是什么原因导致的，查询数据很慢。

这里，咱们可以整理出一个案例：就是说定位到 sql 的执行时间太长了，然后我们在慢查询里面把日志 dump 出来，之后去 explain 分析；发现这个 sql 语句是全表查询，或者说 mysql 里面没有索引排序，所以查询很慢，或者说索引用名字排序，没有用 id 去排序，导致这个查询的时间特别长。

案例：from …… 是会影响 io 的，where 后是会影响 cpu 。比如说我们发现执行的过程中，cpu 特别高，那怎么办？我们通过慢查询日志，发现有两条 sql 语句用时特别长，占用了大量的时间，而且我们用 expalin 去分析该语句，发现是 extra 的字段值为 Using filesort，初步判断是因为没有用索引，通过跟开发沟通，发现 orer by 确实是没有进项索引的排序，之后加上了索引，执行时间大大缩短，最终解决了这个问题。时间由 8s 变成了 1s。

调优的原则：

减少 io 的压力——减少数据量级，拆库分表

IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

减少 cpu 的压力——连接数、加索引、增大查询缓存命中率、不能有死锁等

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标。

比如说，很常见的按时间阶段去进行拆分的现象：比如说我要查询一个表单的 3 天内的数据，那么我们可以把 3 天的数据单独存一张表，更早的数据放进历史表内，这样能大大减少数据量级，从而提升 mysql 性能。

掌握一个命令：mysqldumpslow，在哪个下面呢？可以 find / -name mysqldumpslow 一下，一般来讲，yum 安装的是在 /usr/bin 下面，这个是要掌握的哦，分析慢查询日志的

基于这个目标我们来看下优化的基本原则

MySQL优化基本原则

1、选取最适用的字段类型，尽量避免浪费

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

2、使用连接（JOIN）来代替子查询(Sub-Queries)

虽然Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。　在MySQL5.6或者更新的版本或者是MariaDB可以忽略关于子查询方面的建议

3、使用联合(UNION)来代替手动创建的临时表

union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。

4、减少排序，为经常需要排序、分组和联合查询操作的字段建立索引

排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。 对于MySQL来说，减少排序有多种办法，比如：

• 通过利用索引来排序的方式进行优化
• 减少参与排序的记录条数
• 非必要不对数据进行排序

5、禁用外键

6、避免大sql

• 一个SQL只能在一个cpu上运行
• 高并发环境中，大SQL容易影响性能问题
• 可能一个大SQL把数据库搞死 拆分SQL

7、保持事务的短小精悍

• 即开即用，用完即关
• 无关操作踢出事务，减少资源占用
• 保持一致性的前提下，拆分事务

8、避免大批量更新

• 避开高峰
• 白天限制速度
• 加sleep

9、避免取过量数据，灵活使用limit

当系统需要进行分页操作的时候，尽量使用limit加上偏移量的方法来实现，同时配合order by的子句

10、避免在SQL 语句中进行数学运算、函数计算、逻辑判断等操作

尤其需要注意，不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11、避免OR

同一字段，推荐in 不同字段，推荐union

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10
union all
select id from t where num=

当然是用in 或者 not in的时候也需要谨慎一些

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 

12、优先优化高并发的 SQL，而不是执行频率低某些“大”SQL

13、尽可能对每一条运行在数据库中的SQL进行explain。

14、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

五、mysql索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

索引在设定的时候，会自动对数据进行排序，当一个表的数据超过800W+行以上的时候，索引的功能效果便不再明显，建立索引会占用磁盘空间的索引文件，一般会选择列值较短的，尽量使用列值唯一的。

索引的种类：

种类：B+树索引

分类：主键、唯一、普通索引

1、普通索引

最普通的索引，所有列都可以加

# 创建索引
CREATE INDEX indexName ON mytable(username(length));
# 修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)

# 创建表的时候直接指定
CREATE TABLE mytable(
  ID INT NOT NULL,
  username VARCHAR(16) NOT NULL,
  INDEX [indexName] (username(length))
);  

# 查看索引
show index from mytable;

2、主键索引

建表的时候加的主键

CREATE TABLE besttest (
    id int(10) AUTO_INCREMENT PRIMARY KEY NOT NULL,
    mpp_name VARCHAR(20) NOT NULL UNIQUE,
    mpp_age int(10) NOT NULL,
    addree VARCHAR(50) NOT NULL
) CHARSET utf8;

3、组合索引

create index index_name on table_name (col,col2);

4、唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

# 创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
# 修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
# 创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);  

那么我怎么样知道我当前的表是否添加了索引呢？

SHOW INDEX FROM table_name; \G

说明：1-24是最底层的数据的地址。上一级是存下面节点的起始位置以及终点位置。我们这里是 4 个做一个切片。

比如说，我们不建立索引，我们要找一个数字，那咋办？肯定是要去遍历，从 1 - 24 依次遍历，运气不好的话，我们最多要遍历到第 24 次，才得到这个数字。

但是我们利用这个索引的话，以上图所示，我们最多要遍历：最上层 6 + 下层 4 = 10 次。也就是最多要遍历 10 次

那么我们再加一层：

那么最多遍历多少？ 3+2+4 = 9 次

也就是说，层级越多，那么我们的遍历数是会越少的，那么我们 cpu 所花的时间也就越少

那有个 B 树+，是啥呢？也就是 1 记录 2 的地址，2 会记录 3 的地址，每个数据会记录下一个数据的地址。好处是什么？不用全体遍历，可以去了解下

query_cache_type = on