数据库优化大作战：15招高效MySQL性能调优策略大揭秘

##

数据库优化大作战：15招高效MySQL性能调优策略大揭秘

本文翻译自lefred 的Quickie: 15 tips for MySQL Performance Tuning

需要原文件的可以私我或者自行下载

1、不要使用MyISAM

永远不要使用

CREATE TABLE... ENGINE=MyISAM


推荐了两个参数设置

SQL> set persist default_storage_engine="InnoDB";
SQL> set persist_only disabled_storage_engines="MyISAM";


在MySQL 8.x中默认是使用InnoDB，更能保障数据安全.

为什么总是使用InnoDB

ACID支持
更多的锁类型
比MyISAM更快

给了一张对比图

图片1

2、并行导入数据

这里提到了使用mysql shell导出与导入时添加参数threads，导出和导入都可以使用

util.dumpInstance()
util.dumpSchemas()
util.dumpTables()

JS > util.dumpInstance("/opt/dump/", {threads: 32})
JS > util.loadDump("/opt/dump", {threads: 32})


这个在我的公众号上也有，有兴趣的可以翻翻。

还提到了如何更快的导入数据，禁用binlog以及调整innodb刷脏参数

SQL > ALTER INSTANCE DISABLE INNODB REDO_LOG;
SQL > set global innodb_extend_and_initialize=OFF;
SQL > set global innodb_max_dirty_pages_pct=10;
SQL > set global innodb_max_dirty_pages_pct_lwm=10;


测试数据

2802 chunks (194.70M rows, 64.75 GB) for 1 tables in 1 schemas
were loaded in 4 min 51 sec (avg throughput 222.51 MB/s)


3、数据迁移不落地

也是使用mysql shell,这个在我的公众号上也有案例

util.copyInstance()
util.copySchemas()
util.copyTables()

JS > util.copyInstance('mysql://admin@10.0.1.105',{"compatibility": ["force_innodb", "skip_invalid_accounts"],threads: 32})


4、总是使用主键

主要是讲InnoDB GIPK参数，其实业务表定义主键是最好的。

SQL> SET PERSIST sql_generate_invisible_primary_key=1;


5、索引优化

一些查询SQL，记录下就可以了

5.1、未使用过的索引

SQL> select database_name, table_name, t1.index_name,
format_bytes(stat_value * @@innodb_page_size) size
from sys.schema_unused_indexes t2
join mysql.innodb_index_stats t1
on object_schema=database_name
and object_name=table_name and t2.index_name=t1.index_name
where stat_name='size' order by stat_value desc;


5.2、重复(冗余)索引

SQL> select t2.*, format_bytes(stat_value * @@innodb_page_size) size
from mysql.innodb_index_stats t1
join sys.schema_redundant_indexes t2
on table_schema=database_name and t2.table_name=t1.table_name
and t2.redundant_index_name=t1.index_name
where stat_name='size' order by stat_value descG


5.3、缺少索引的表

可以理解为全表扫描full_table_scans

SQL> select * from sys.schema_tables_with_full_table_scans;
+-----------------------------+-------------+-------------------+-----------+
| object_schema | object_name | rows_full_scanned | latency |
+-----------------------------+-------------+-------------------+-----------+
| students | Customers | 12210858800 | 41.28 min |
+-----------------------------+-------------+-------------------+-----------+

SQL > select * from sys.statements_with_full_table_scans where db='students' and query like '%customers%'G
*************************** 1. row ***************************
query: SELECT * FROM `Customers` WHERE `age` > ?
db: students
exec_count: 140
total_latency: 17.97s
no_index_used_count: 137
no_good_index_used_count: 0
no_index_used_pct: 100
...
first_seen: 23-01-27 14:34:12.66877
last_seen: 2023-02-23 17:44:47.738911


6、索引的可见性

这个也是新特性，可以看爱可生大佬的文章，关键字提示

alter table large_tbl alter index strange_idx invisible;


7、并行创建索引优化

主要是三个参数的优化,innodb_ddl_buffer_size被所有threads共享，两者推荐同时增加

innodb_ddl_threads = 4
innodb_ddl_buffer_size = 1048576
innodb_parallel_read_threads = 4


给了一个查CPU的SQL语句

MySQL > select count from information_schema.INNODB_METRICS where name = 'cpu_n';
+-------+
| count |
+-------+
| 16 |
+-------+


设置参数为

MySQL > SET innodb_ddl_threads = 8;
MySQL > SET innodb_parallel_read_threads = 8;
MySQL > SET innodb_ddl_buffer_size = 1048576000;


8、限制查询时间

如果SQL查询不确定时间，那么可以使用hint

select /*+ max_execution_time(5000) */ sleep(10);
+-----------+
| sleep(10) |
+-----------+
| 1 |
+-----------+
1 row in set (5.0006 sec)


这个单位是ms

9、找到最需要优化的SQL语句

这个衡量的维度比较多，这里是根据执行时间来判断的，在我的巡检脚本中有更详细更多维度的。

SELECT schema_name, format_pico_time(total_latency) tot_lat,
exec_count, format_pico_time(total_latency/exec_count) latency_per_call,
query_sample_text
FROM sys.x$statements_with_runtimes_in_95th_percentile AS t1
JOIN performance_schema.events_statements_summary_by_digest AS t2
ON t2.digest=t1.digest
WHERE schema_name NOT in ('performance_schema', 'sys')
ORDER BY (total_latency/exec_count) desc LIMIT 1G


10、自动参数优化

这里就是想buffer pool和redo的参数设置由innodb_dedicated_server控制。怎么说呢？请结合实际情况考虑吧

11、Start Warm

不好翻译，但是意思明确

The secret of good performance is to always run a production server with a warm Buffer Pool.
If you need to restart MySQL for any reason (maintenance, updgrade, crash), it’s recommended to dump the content of the InnoDB Bu􀆬er Pool to disk and load it at startup:

innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1


12、禁用自适应hash索引

一般不会出现只做查询并且数据全部缓存在buffer pool，所以禁用AHI是推荐的，这个基本共识了

set persist innodb_adaptive_hash_index = 0;


13、尽可能使用内存

涉及到的参数,，这个查官网吧，内容太多

show global status like 'innodb_buffer_pool_read%s';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| Innodb_buffer_pool_read_requests | 365290 |
| Innodb_buffer_pool_reads | 1594 |
+----------------------------------+--------+


13.1、检查比例

SELECT CONCAT(FORMAT(B.num * 100.0 / A.num,2),'%') DiskReadRatio
FROM (
SELECT variable_value num FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_read_requests') A,
(SELECT variable_value num FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads') B;
+---------------+
| DiskReadRatio |
+---------------+
| 0.44% |
+---------------+


13.2、检查更多信息

如果比例大于10%，则检查如下信息

SQL> SELECT TABLE_NAME,INDEX_NAME, COUNT(*) AS Pages,
ROUND(SUM(IF(COMPRESSED_SIZE = 0, 16384, COMPRESSED_SIZE))/1024/1024)
AS 'Total Data (MB)'
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE table_name not like '`mysql`.%'
GROUP BY TABLE_NAME,INDEX_NAME order by 4 desc,3 desc;


14、选择正确的内存allocator

结论，tcmalloc最推荐，其次是jemalloc

安装与设置

yum -y install gperftools-libs

systemctl daemon-reload
ystemctl restart mysqld


15、skip_name_resolve

没啥说的

SQL> set persist_only skip_name_resolve=1;


16、总结

一些适用性比较高的性能优化推荐，值得学习并结合实践使用。