MySQL性能优化

Alan

8 年前

通过Profile查看SQL各个阶段所消耗的时间进而采取有针对性的优化

set profiling=1; #仅在当前session有效
select count(*) from sakila.film; #使用测试库执行一条语句
show profiles; #可显示查询所用的总时间以及Query_ID
show profile for query 1; #query后接的数字1即为对应的Query_ID
show profile cpu for query 1; #查看查询中各阶段的CPU占用情况

在上图中我们看到显示了一个warning，那这是什么原因导致的呢，执行

得到的结果如下
[php]
| Warning | 1287 | 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead |

因此在MySQL5.6以后实际上官方推荐使用的是Performance Schema，下面是使用Performance Schema方法

#对数据库进行如下的更新操作
use performance_schema;
update setup_instruments set enabled='Yes', timed='yes' where name like 'stage%';
update setup_consumers set enabled='Yes' where name like 'events%';

此时再执行同样的语句

select count(*) from sakila.film;
#查询消耗时间的语句
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.<code>THREAD_ID</code>=b.<code>THREAD_ID</code>
JOIN events_stages_history_long c ON c.<code>THREAD_ID</code>=b.<code>THREAD_ID</code> AND c.<code>EVENT_ID</code> BETWEEN a.EVENT_ID AND a.END_EVENT_ID
ORDER BY a.THREAD_ID,c.EVENT_ID;

常用的优化方法
1.大表数据的修改采取分批处理
比如以下脚本：

DELIMITER $$
use <code>dbname</code> $$
DROP PROCEDURE IF EXISTS <code>p_delete_rows</code>$$
CREATE DFINER=<code>root</code>@<code>127.0.0,1</code> PROCEDURE <code>p_delete_rows</code>()
BEGIN
	DECLARE v_rows INT;
	SET v_rows = 1;
	WHILE v_rows&gt;0;
	DO
		DELETE FROM tableName WHERE id&gt;=90000 AND id&lt;=190000 LIMIT 5000;
		SELECT ROW_COUNT() INTO v_rows;
		SELECT SLEEP(5);
	END WHILE;
END$$
DELIMITER ;

2.如何修改大表的表结构

pt-online-schema-change
--alter="MODIFY columnName VARCHAR(150) NOT NULL DEFAULT''" #任意其它修改均可
--user=userName --password=passwd D=dbname, t=tableName
--charset=uf8 --execute

3.针对大表优化not in和<>查询

#原语句
SELECT customer_id,first_name,last_name,email
FROM customer
WHERE customer_id
NOT IN(SELECT customer_id FROM payment)
#优化后的语句
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

4.使用汇总表优化查询

#常见的显示商品的评论数功能
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())
)