数据库访问故障处理

Access denied for user ‘root‘@’xxx.xxx.xxx.xxx’ (using password: YES)

  1. 密码不正确

  2. 未对IP进行授权,%不包含localhost

    1
    2
    3
    mysql -uroot -p

    grant all privileges on *.* to 账户名@'%' identified by '账户密码';

  3. 网络是否畅通

ERROR 1153 (08S01) at line 38: Got a packet bigger than ‘max_allowed_packet’ bytes

  • 问题原因:Mysql接收数据包问题

  • 解决方法:

    1. ```sh

      修改配置文件

      vi /etc/my.cnf

      在最后添加一行：

      max_allowed_packet=16M
      1
      2
      3
      4
      5

      2. ```sql
         # mysql命令行执行命令
         # 单位是字节,这里设置位置16M
         SET PERSIST max_allowed_packet=16*1024*1024

Sql优化

Sql改写优化

  • 使用outer join代替not in
  • 使用CTE代替子查询 (版本要求:Mysql8.0)
  • 拆分复杂的大SQL为多个简单的小SQL

分页查询优化案例

方案1

  • 未优化前 

    1
    2
    SELECT customer_id,title,content FROM 'product_comment'
    WHERE audit_status =1 AND product_id = 199726 LIMIT 0,5

  • 优化版的 

    1
    2
    3
    4
    5
    6
    7
    SELECT t.customer_id,t.title,t.content FROM 
    (
        SELECT 'comment_id'
        FROM product_comment
        WHERE product_id=199727 AND audit_status=l LIMIT 0,15 
    ) a JOIN product_comment t 
        ON a.comment_id = t.comment_id:

分区间统计

1
2
3
4
5
6
7
8
SELECT COUNT(CASE WHEN IFNULL(total_money,0) >= 1000 THEN a.customer_id END) AS '大于1000'
,COUNT(CASE WHEN IFNULL(total_money,0) >= 800 THEN a.customer_id END) AS '800-1000'
,COUNT(CASE WHEN IFNULL(total_money,0) >= 500 THEN a.customer_id END) AS '500-800'
,COUNT(CASE WHEN IFNULL(total_money,0) < 500 THEN a.customer_id END) AS '小于500'
FROM mc_userdb.customer_login a
LEFT JOIN
(SELECT customer_id,SUM(order_money) AS total_money FROM mc_orderdb.order_master GROUP BY customer_id) b
ON a.customer_id=b.customer_id

SQL执行计划

  • 如何查看sql的执行计划
1
2
3
4
5
6
EXPLAIN
[SQL语句]

# 例如:
EXPLAIN
SELECT * FROM 'user_info' WHERE user_id=2

执行计划返回的结果解析

id列

  • id列中的数据为一组数字,表示执行SELECT语句的顺序
  • id值相同时,执行顺序由上至下
  • id值越大优先级越高,越先被执行

select_type

值 含义
SIMPLE 不包含子查询或是UNION操作的查询
PRIMARY 查询中如果包含任何子查询,那么最外层的查询则标记为PRIMARY
SUBQUERY SELECT列表中的子查询
DEPENDENT SUBQUERY 依赖外部结果的子查询
UNION RESULT UNION产生的结果集
DERIVED 出现在FROM字句中的子查询
UNION UNION操作的第二个或是之后的查询的值为union
DERIVED 出现在FROM字句中的子查询

table

  • 输出数据行所在的表的名称
  • <unionM,N>由ID为M,N查询union产生的结果集
  • /由id为N的查询产生的结果

type

值(性能由高到低) 含义
system 这是const联接类型的一个特例,当查询的表只有一行时使用
const 表中有且只有一个匹配的行时使用,如对主键或是唯一索引的查询,这是效率最高的联接方式
eq_ref 唯一索引或主键查找,对于每个索引建,表中只有一条记录与之匹配
ref 非唯一索引查找,返回匹配某个单独值的所有行
ref_or_null 类似ref类型的查询,但是附加了对NULL值列的查询
index_merge 该联接类型表示使用了索引合并优化方法
range 索引范围扫描,常见于between,>,<这里的查询条件
index FULL index Scan全索引扫描,同ALL的区别是,遍历的是索引树
ALL FULL TABLE Scan 全表扫描,这是效率最差的联接方式

extra

值 含义
Distinct 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作
Not exists 使用not exists来优化查询
Using filesort 使用额外操作进行排序,通常会出现在order by或group by查询中
Using index 使用了覆盖索引进行查询
Using temporary mysql需要使用临时表来处理查询,常见于排序,子查询和分组查询
Using where 需要在mysql服务器层使用where条件来过滤数据
select tables optimized away 直接通过索引来获得数据,不用访问表

possible_keys

  • 指出mysql能使用那些索引来优化查询
  • 查询列所涉及到的列上的索引都会被列出但不一定被使用

key

  • 查询优化器优化查询实际所使用的索引
  • 如果没有可用的索引,则显示为Null
  • 如果查询使用了覆盖索引,则该索引仅出现在key列中

其他

值 含义
possible_keys 1. 指出mysql能使用那些索引来优化查询 2. 查询列所涉及到的列上的索引都会被列出但不一定被使用
ref 表示那些列或常量被用查找索引列上的值
key_len 1. 表示索引字段的最大可能长度 2. key_len的长度由字段定义计算而来,并非数据的实际长度
partitions 1. 对于分区表,显示查询的分区Id 2. 对于非分区表,显示为NULL
rows 表示mysql通过索引统计信息,估算的所需读取的行数 rows值的大小是个统计抽样结果,并不十分准确
filtered 表示返回结果的行数占需读取行数的百分比 值越大越好 依赖统计信息的

执行计划的限制

  • 无法展示存储过程,触发器,udf对查询的影响
  • 无法使用explain对存储过程进行分析
  • 早期版本的mysql只支持select语句进行分析

mysql捕获有问题的sql (慢查询日志)

  1. 启动mysql慢查日志
1
2
3
4
5
6
7
8
# 设置日志存储位置
set global slow_query_log_file = /sql_log/show_log.log;
# 记录未使用索引的sql日志 
set global log_queries_not_using_indexes = on;
# 抓取执行超过多少时间的sql(秒)
set global long_query_time = 0.001;
# 启动慢查询日志
set global low_query_log = on;
  1. 分析慢查询日志
1
2
mysqldumpslow [日志位置/日志名称]
# mysqldumpslow slow-mysql.log

阻塞

  • 由于不同锁之间的兼容关系,造成一个事务需要等待另一个事务释放其所占用的资源现象
  • 阻塞查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
SELECT p2.`host`                                                    Blockedhost, 
       p2.`user`                                                    BlockedUser, 
       r.trx_id                                                     BlockedTrxId 
       , 
       r.trx_mysql_thread_id 
       BlockedThreadId, 
       Timestampdiff(second, r.trx_wait_started, CURRENT_TIMESTAMP) WaitTime, 
       r.trx_query                                                  BlockedQuery 
       , 
       l.lock_table 
       BlockedTable, 
       m.`lock_mode` 
       BlockedLockMode, 
       m.`lock_type` 
       BlockedLockType, 
       m.`lock_index` 
       BlockedLockIndex, 
       m.`lock_space` 
       BlockedLockSpace, 
       m.lock_page 
       BlockedLockPage, 
       m.lock_rec 
       BlockedLockRec, 
       m.lock_data 
       BlockedLockData, 
       p.`host` 
       blocking_host, 
       p.`user` 
       blocking_user, 
       b.trx_id 
       BlockingTrxid, 
       b.trx_mysql_thread_id 
       BlockingThreadId, 
       b.trx_query 
       BlockingQuery, 
       l.`lock_mode` 
       BlockingLockMode, 
       l.`lock_type` 
       BlockingLockType, 
       l.`lock_index` 
       BlockingLockIndex, 
       l.`lock_space` 
       BlockingLockSpace, 
       l.lock_page 
       BlockingLockPage, 
       l.lock_rec 
       BlockingLockRec, 
       l.lock_data 
       BlockingLockData, 
       IF (p.command = 'Sleep', Concat(p.time, ' seconds'), 0)      idel_in_trx 
FROM   information_schema.innodb_lock_waits w 
       INNER JOIN information_schema.innodb_trx b 
               ON b.trx_id = w.blocking_trx_id 
       INNER JOIN information_schema.innodb_trx r 
               ON r.trx_id = w.requesting_trx_id 
       INNER JOIN information_schema.innodb_locks l 
               ON w.blocking_lock_id = l.lock_id 
                  AND l.`lock_trx_id` = b.`trx_id` 
       INNER JOIN information_schema.innodb_locks m 
               ON m.`lock_id` = w.`requested_lock_id` 
                  AND m.`lock_trx_id` = r.`trx_id` 
       INNER JOIN information_schema. processlist p 
               ON p.id = b.trx_mysql_thread_id 
       INNER JOIN information_schema. processlist p2 
               ON p2.id = r.trx_mysql_thread_id 
ORDER  BY waittime DESC;
  • 阻塞查询语义版
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SELECT    
p2.`HOST` AS '被阻塞方host',
p2.`USER` AS '被阻塞方用户',
r.trx_id AS '被阻塞方事务id',    
        r.trx_mysql_thread_id AS '被阻塞方线程号',    
        TIMESTAMPDIFF(    
            SECOND,    
            r.trx_wait_started,    
            CURRENT_TIMESTAMP    
        ) AS '等待时间',    
        r.trx_query AS '被阻塞的查询',    
        l.lock_table AS '阻塞方锁住的表',  
        m.`lock_mode` AS '被阻塞方的锁模式',
        m.`lock_type` AS '被阻塞方的锁类型(表锁还是行锁)',
        m.`lock_index` AS '被阻塞方锁住的索引',
        m.`lock_space` AS '被阻塞方锁对象的space_id',
        m.lock_page AS '被阻塞方事务锁定页的数量',
        m.lock_rec AS '被阻塞方事务锁定行的数量',
        m.lock_data AS '被阻塞方事务锁定记录的主键值', 
        p.`HOST` AS '阻塞方主机', 
        p.`USER` '阻塞方用户',
        b.trx_id AS '阻塞方事务id',    
        b.trx_mysql_thread_id AS '阻塞方线程号',
        b.trx_query AS '阻塞方查询',
        l.`lock_mode` AS '阻塞方的锁模式',
        l.`lock_type` AS '阻塞方的锁类型(表锁还是行锁)',
        l.`lock_index` AS '阻塞方锁住的索引',
        l.`lock_space` AS '阻塞方锁对象的space_id',
        l.lock_page AS '阻塞方事务锁定页的数量',
        l.lock_rec AS '阻塞方事务锁定行的数量',
        l.lock_data AS '阻塞方事务锁定记录的主键值',         
       IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) AS '阻塞方事务空闲的时间'             
    FROM    
        information_schema.INNODB_LOCK_WAITS w    
    INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id    
    INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id    
    INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id  AND l.`lock_trx_id`=b.`trx_id`
      INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
    INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id   
 INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id 
    ORDER BY    
        '等待时间' DESC;
  • 如果只有语句出现[Err] 3167 - The ‘INFORMATION_SCHEMA.GLOBAL_STATUS’ feature is disabled; see the documentation for ‘show_compatibility_56’
1
2
3
4
5
6
7
set global show_compatibility_56=on; 
show variables like '%show_compatibility_56%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| show_compatibility_56 | ON    |
+-----------------------+-------+

死锁

  • 并行执行的多个事务相互占有了对方所需要的资源
  • 方法1:
1
2
3
4
5
6
7
8
# 1.查询是否锁表
show OPEN TABLES where In_use > 0;

# 2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
show processlist

# 3.杀死进程id（就是上面命令的id列）
kill id
  • 方法2:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 1.查看下在锁的事务 
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

# 2.杀死进程id（就是上面命令的trx_mysql_thread_id列）
kill 线程ID

# 例子：

# 查出死锁进程：SHOW PROCESSLIST
# 杀掉进程          KILL 420821;

#其它关于查看死锁的命令：

# 1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

# 2：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

# 3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

  • 方法3:

    1
    2
    # 将死锁写入到错误日志中
    set global innodb-print_all-deadlocks=on;

死锁监控

  • 使用pt工具
1
pt-deadlock-logger u=[用户名],p=[密码],h=127.0.0.1 --create-dest-table --dest u=[用户名],p=[密码],h=127.0.0.1,D=crn,t=deadlock