MySQL05-SQL分析和锁

MySQL05-SQL分析和锁

1.慢查询日志

  1. show variables like '%slow_query_log%'，查看慢查询日志是否开启和存储位置。
  2. set global slow_query_log=1，开启慢查询日志，只对当前有效，重启失效。
  3. 永久开启慢查询日志。
# 修改mysql.ini文件
slow_query_log=1
slow_query_log_file=D:\Program Files\mysql-8.0.25-winx64\data\MS-HPFVGTEKTAEA-slow.log
  1. show variables like '%long_query_time%'，查看SQL执行多长时间会被记录到慢查询日志中，默认10秒。
  2. set long_query_time=3，修改慢查询日志的时间。
  3. 慢查询日志记录。
# Time: 2021-10-23T01:21:39.171868Z
# User@Host: root[root] @ localhost [::1]  Id:     9
# Query_time: 4.009782 (查询执行的时间)  Lock_time: 0.000000 (等待的时间) Rows_sent: 1  Rows_examined: 1
use db_test; # 数据库
SET timestamp=1634952095;
select sleep(4); # 执行的SQL。
  1. show global status like 'Slow_queries';，查询记录的慢查询SQL的数量。
  2. 慢查询日志的分析，mysqldumpslow。
# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/cqm-slow.log

# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/cqm-slow.log

# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/cqm-slow.log

# 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/cqm-slow.log | more

2.SQL执行详细分析，profiling

  1. profiling，是MySQL提供的，用来分析SQL执行的资源消耗情况，可以会SQL执行的每一步进行准确分析。默认情况下，处于关闭状态，并保存最近15次的运行结果。
  2. show variables like 'profiling'，查看profiling是否开启。
  3. set profiling=1，开启profiling。
  4. show profiles的使用。
show profiles; # 查看SQL执行所用的时间。

# 通过id查看当前SQL执行的具体情况。
show profile cpu, block io for query 2[show profiles查询得到的query_id]; 
# 查询结果不要出现 creating tmp table，创建临时表。
# copy to tmp table on disk ，把内存临时表的数据拷贝到磁盘。

3.Msyql锁-查看锁状态常用的命令

  1. 锁的分类。
    1. 从对数据的操作上，分为读锁和写锁。
    2. 从对数据操作的粒度上，分为表锁和行锁。
  2. 读锁，也叫共享锁。对同一份数据，多个读操作可以同时进行从而不会互相影响。
  3. 写锁，也叫排它锁。当前写操作没有完成前，它会阻断其他写锁和读锁。
  4. show open tables，显示MySQL表加锁的情况。
  5. lock table mylock read,tb_student write;，给mylock表加读锁，给tb_student表加写锁。
  6. unlock tables，释放锁。
  7. show status like 'table%'，查看数据库锁的情况。
    1. Table_locks_immediate 136，产生表锁的次数。
    2. Table_locks_waited 0，表级锁征用，而产生等待的次数。
  8. show status like 'innodb_row_lock%'，查看系统的行锁情况
    1. Innodb_row_lock_current_waits=0，当前正在等待行锁的数量。
    2. Innodb_row_lock_time= 72050，从系统开启到现在总动锁定的时间。
    3. Innodb_row_lock_time_avg=18012，从系统开启到现在锁的平均等待时间
    4. Innodb_row_lock_time_max=50378，从系统开启到现在锁定的最大时间。
    5. Innodb_row_lock_waits=4，从系统开启总共等待锁的数量。

4.表锁

  1. 在session1中为mylock表加读锁。
    1. session1和session2都可以读到mylock的数据。
    2. session1不能读取其他表的数据，session2可以读到其他表的数据。
    3. session1可以给其他表加读写锁，加锁之后，就可以读锁这个表的数据了。
    4. session1往加读锁的mylock表写数据会报错，session2往加读锁的mylock表写数据会阻塞。
  2. 在session1中为mylock表加写锁。
    1. session1可以读写mylock中的数据，不能读写他表的数据。
    2. session2如果读写mylock中的数据会发生阻塞。

5.行锁

  1. 有两个事务t1和事务t2中。事务t1修改了id=1的数据，在t1中可以查询到修改后的状态；t2中不能查询到t1对id=1数据的修改，即使事务t1提交，t2查到的也是t2开启事务之前的状态；事务t1修改了id=1的数据，会对这一行的数据加行锁，所以在t2中修改id=1这条数据，会被阻塞，直到事务t1提交。
  2. 索引失效，行锁会升级为表锁。
  3. 间隙锁，Gap Lock。假设数据之前没有 where age = 15这条数据；事务t1更新数据，where age > 10 and age < 20；这时候会锁定 age > 10 and age < 20范围的数据，事务t2去插入age=15的这条数据会被阻塞。
  4. 在修改线上数据库的时候，先begin开启事务，在执行select...for update，锁定这一行的数据，保证不被其他事务访问，然后再去修改数据，最后commit提交事务。

6.SQL执行慢的原因

  1. SQL执行慢主要是由于执行时间长或者等待时间长引起的。
  2. 执行时间长的原因。
    1. 没有创建索引。
    2. 索引失效。创建了索引，但是没有走索引。
    3. join连接的表太多。
  3. 等待时间长的原因。IO阻塞、写操作的压力大，争抢锁。可以通过调优数据库参数来解决问题。

7.SQL的执行顺序

1 from <left_table>
2 on <join_conditin>
3 <join_type> join <right_table>
4 where <where_condition>
5 gruop by <group_by_list>
6 having <having_condition>
7 select 
8 distinct <select_liset>
9 order by <order_by_condition>
10 limit <limit_number>

8.redo log、undo log和bin log

  1. redo log用来保证事务的持久性。

    1. 事务中对数据库的操作，并不对立刻刷新到磁盘中，而是会记录到缓存中，即脏页。
    2. 同时随着事务操作的执行，就会生成redo log。等事务操作的脏页写入到磁盘之后，redo log的使命也就完成了。Mysql的Master Thread每秒会将redo log刷新到磁盘，或者在事务提交的时候会将redo log刷新到磁盘。
    3. InnoDB中的ib_logfile1和ib_logfile2用于存放redo log。

  2. undo log用来保证事务的回滚和多版本并发控制(MVCC)。

    1. undo log主要记录了数据的逻辑变化。一条INSERT语句，对应一条DELETE的undo log；每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。
    2. 事务提交后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

  3. bin log用于主从复制。从库利用主库上的bin log实现主从同步。

9.多版本并发控制MVCC

  1. MVCC全称Mutli Version Concurreny Control，多版本并发控制，也可称之为一致性非锁定读。
  2. MVCC 主要应用于Read Commited和Repeatable read两个事务隔离级别。
  3. MVCC的实现依赖于每行的隐藏字段，DATA_TRX_ID和DATA_ROLL_PTR。DATA_TRX_ID，标记了最新更新这条行记录的transaction id；DATA_ROLL_PTR，一个rollback指针，指向当前这一行数据的上一个版本。
  4. MVCC可以理解为当前事务只能读取到当前事务开始之前的数据。

10.查看MySQL性能

  1. show engine innodb status，查看Innodb存储引擎的状态。
=====================================
2022-06-27 18:42:37 0x88cc INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 32 seconds # 计算过去32秒的状态
-----------------
BACKGROUND THREAD # 备份线程
-----------------
srv_master_thread loops: 32 srv_active, 0 srv_shutdown, 519970 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES # 信号量
----------
OS WAIT ARRAY INFO: reservation count 46
OS WAIT ARRAY INFO: signal count 43
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS # 事务
------------
Trx id counter 4994
Purge done for trx's n:o < 4994 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 284446148354704, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284446148353928, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284446148353152, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284446148352376, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 284446148351600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O # IO线程。一个插入缓冲线程；一个日志线程；四个读线程；四个写线程。
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
904 OS file reads, 1205 OS file writes, 501 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX # 插入缓冲和自适应哈希
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG # 日志
---
Log sequence number          18818808
Log buffer assigned up to    18818808
Log buffer completed up to   18818808
Log written up to            18818808
Log flushed up to            18818808
Added dirty pages up to      18818808
Pages flushed up to          18818808
Last checkpoint at           18818808
284 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY # 缓冲池和内存
----------------------
Total large memory allocated 0
Dictionary memory allocated 423986
Buffer pool size   8191 # 一共8191个缓冲帧，每个缓冲帧16k。
Free buffers       7132 # 空闲的缓冲帧
Database pages     1054 # 已经使用的缓冲帧
Old database pages 369 # 脏页的数量
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 190, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 879, created 176, written 667
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1054, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS # 行的操作
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=6184, Main thread ID=7896 , state=sleeping
Number of rows inserted 13, updated 9, deleted 1, read 158
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 80, updated 376, deleted 40, read 6707
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
posted @ 2021-10-31 14:32  行稳致远方  阅读(22)  评论(0)    收藏  举报