mysqlRealTimeSQLAcquisition - juedaiyuer/researchNote GitHub Wiki

##如何实时获取有性能问题的SQL##

information_schema数据库--->PROCESSLIST表

SELECT id,`user`,`host`,DB,command,`time`,`state`,info
FROM information_schema.PROCESSLIST
WHERE TIME>=60

##SQL的解析预处理及生成执行计划##

MySQL服务器处理查询请求的整个过程

  1. 客户端发送SQL请求给服务器
  2. 服务器检查是否可以在查询缓存中命中该SQL
  3. 服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划
  4. 根据执行计划,调用存储引擎API来查询数据
  5. 将结果返回给客户端

###查询缓存对SQL性能的影响###

  • 优先检查这个查询是否命中查询缓存中的数据

  • 通过一个对大小写敏感的哈希查找实现的,Hash查找只能进行全值匹配

  • 从查询缓存中直接返回结果并不容易

  • 对于一个读写频繁的系统使用查询缓存很可能会降低查询处理的效率,不建议使用查询缓存

    query_cache_type 设置查询缓存是否可用 ON OFF DEMAND 在查询语句中使用SQL_CACHE和SQL_NO_CACHE来控制是否需要缓存 query_cache_size 设置查询缓存的内存大小(n*1024) query_cache_limit 设置查询缓存可用存储的最大值 查询语句+SQL_NO_CACHE可以提高效率 #默认关闭,也是建议 query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据 query_cache_min_res_unit 设置查询缓存分配的内存块最小单位

###MySQL依照执行计划和存储引擎进行交互###

子过程:解析SQL,预处理,优化SQL执行计划

语法解析

通过关键字对MySQL语句进行解析,并生成一颗对应的解析树

MySQL解析器将使用MySQL语法规则验证和解析查询,包括检查语法是否使用了正确的关键字,关键字的顺序是否正确等

预处理

根据MySQL规则进一步检查解析树是否合法

检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义等

语法检查全部通过了,查询优化器就可以生成查询计划了

会造成MySQL生成错误的执行计划的原因

  1. 统计信息不准确
  2. 执行计划中的成本估算不等同于实际的执行计划的成本
  3. MySQL优化器所认为的最优可能与你所认为的最优不一样,基于其成本模型选择最优的执行计划
  4. MySQL从不考虑其它并发的查询,这可能会影响当前的查询速度
  5. MySQL有时候也会基于一些固定的规则来生成执行计划
  6. MySQL不会考虑不受其控制的成本,存储过程,用户自定义的函数

MySQL服务器层并不知道哪些页面在内存中
哪些页面在磁盘上
哪些需要顺序读取
哪些要页面随机读

MySQL优化器可优化的SQL类型

  1. 重新定义表的关联顺序,优化器会根据统计信息来决定表的关联顺序
  2. 将外连接转化成内连接(where条件和库表结构等)
  3. 使用等价交换规则
  4. 优化count(),min(),max()
  5. 将一个表达式转化为常数表达式
  6. 子查询优化(子查询转换为关联查询)
  7. 提前终止查询
  8. 对in()条件进行优化

优化count(),min(),max()
select tables optimized away
优化器已经从执行计划中移除了该表,并以一个常数取而代之

mysql> show create table film\G;

###如何确定查询处理各个阶段所消耗的时间###

减少查询所消耗的时间,加快查询的响应速度

####profile#####

profile

#启动profile,session级别
set profiling=1;

#执行查询

#查看每一个查询所消耗的总时间的信息
show profiles;

#查询的每一个阶段所消耗的时间
show profile for query N;

实际演示

mysql> set profiling=1;
mysql> select count(*) from film;

mysql> show profiles;
+----------+------------+---------------------------+
| Query_ID | Duration   | Query                     |
+----------+------------+---------------------------+
|        1 | 0.00147775 | select count(*) from film |
+----------+------------+---------------------------+ 

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000314 |
| checking permissions | 0.000037 |
| Opening tables       | 0.000146 |
| init                 | 0.000074 |
| System lock          | 0.000038 | 
| optimizing           | 0.000618 | 
| executing            | 0.000064 |
| end                  | 0.000045 |
| query end            | 0.000029 |
| closing tables       | 0.000022 |
| freeing items        | 0.000081 |
| cleaning up          | 0.000012 |
+----------------------+----------+
12 rows in set, 1 warning (0.01 sec) 

#查看cpu
mysql> show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000314 | 0.000214 |   0.000064 |
| checking permissions | 0.000037 | 0.000024 |   0.000007 |
| Opening tables       | 0.000146 | 0.000113 |   0.000033 |
| init                 | 0.000074 | 0.000057 |   0.000017 | 
| System lock          | 0.000038 | 0.000028 |   0.000009 |
| optimizing           | 0.000618 | 0.000477 |   0.000141 |
| executing            | 0.000064 | 0.001112 |   0.000000 | 
| end                  | 0.000045 | 0.000000 |   0.000000 |
| query end            | 0.000029 | 0.000000 |   0.000000 |
| closing tables       | 0.000022 | 0.000000 |   0.000000 |
| freeing items        | 0.000081 | 0.000000 |   0.000000 |
| cleaning up          | 0.000012 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
12 rows in set, 1 warning (0.00 sec)

mysql> show warnings;

考虑到profile可能被移除,所以开始记录下一个工具

####performance_schema#####

5.5版本引入

启动监控

UPDATE `setup_instruments`
SET enabled=`YES`,TIMED=`YES` WHERE NAME LIKE `stage%`;

UPDATE setup_consumers
SET enabled=`YES` WHERE NAME LIKE 'events%';

查询语句

SELECT a.THREAD_ID,SQL_TEXT,c.EVENT_NAME,(c.TIMER_END-c.TIMER_START)/1000000000 AS 'DURATION(ms)'
FROM events_statements_history_long a
JOIN threads b ON a.`THREAD_ID`=b.'THREAD_ID'
JOIN events_stages_history_long c ON c.`THREAD_ID`=b.`THREAD_ID`
AND c.`EVENT_ID` BETWEEN a.EVENT_ID AND a.END_EVENT_ID
WHERE b.`PROCESSLIST_ID`=CONNECTTION_ID()
AND a.EVENT_NAME='statement/sql/select'
ORDER BY a.THREAD_ID,c.EVENT_ID

##特定SQL的查询优化##

大表的数据修改最好要分批处理

  • 1000万行记录的表中删除/更新100万行记录

  • 一次只删除/更新5000行记录

  • 暂停几秒,为主从同步提供一些时间

    DELIMITER $$ USE imooc$$ DROP PROCEDURE IF EXISTS p_delete_rows$$ CREATE DEFINER=root@127.0.0.1 PROCEDURE p_delete_rows() BEGIN DECLARE v_rows INT; SET v_rows=1; WHILE v_rows>0 DO DELETE FORM sbtest1 WHERE id>=90000 AND id<=190000 LIMIT 5000; 根据需要修改改行即可 SELECT ROW_COUNT() INTO v_rows; SELECT SLEEP(5); END WHILE; END$$ DELIMITER ;

如何修改大表的表结构

对表中的列的字段类型进行修改,改变字段的宽度时还是会锁表,无法解决主从数据库延迟

修改大表结构

pt-online-schema-change
--alter="MODIFY c VARCHAR(150) NOT NULL DEFAULT"
--user=root --password=yourpassword D=imooc,t=sbtest
--charset=utf8 --excute

如何优化not in和< >查询

SELECT customer_id,first_name,last_name,email
FROM customer
WHERE customer_id
NOT IN(SELECT customer_id FROM payment)


#改写后的SQL

SELECT a.customer_id,a.first_name,a.last_name,a.email
FROM customer a
LEFT JOIN payment b ON a.customer_id=b.customer_id
WHERE b.customer_id IS NULL

使用汇总表优化查询

SELECT COUNT(*) FROM product_comment WHERE product_id=999;

汇总表就是提前以要统计的数据进行汇总并记录到表中以备后续的查询使用

#建立汇总表
CREATE TABLE product_comment_cnt(product_id INT,cnt INT);

#显示每个商品的评论数
SELECT SUM(cnt) FROM(
SELECT cnt FROM product_comment_cnt WHERE product_id=999
UNION ALL
SELECT COUNT(*) FROM product_comment WHERE product_id=999
AND timestr>DATE(NOW())
) a