mysql 最佳_MySQL的最佳实践

最新推荐文章于 2022-11-27 18:06:14 发布

weixin_39776991

最新推荐文章于 2022-11-27 18:06:14 发布

阅读量121

点赞数

文章标签： mysql 最佳

本文链接：https://blog.csdn.net/weixin_39776991/article/details/113151626

版权

背景

最开始就是部署了一个普通的MySQL容器，什么特别的设置都没有做，相当于一个helloworld版本的MySQL部署吧。然后基于MySQL的java web系统部署好后Gemfield做了性能测试，遇到了如下几个问题(都是读相关的)：在数据量达到几百万条级别(每条200个字节量)的时候，分页list的性能已经急剧下降；

在数据量达到几百万条级别(每条200个字节量)的时候，JSON字段搜索的性能急剧下降；

在jmeter100个线程同时请求REST server的时候，每个request的响应时间急剧增长；

为了解决这些问题，Gemfield重新部署了一个独立的测试MySQL cluster。请参考专栏文章：Gemfield：基于K8s部署MySQL clusterzhuanlan.zhihu.com

MySql的存储引擎

MySql的数据实际存储在(默认)/var/lib/mysql目录下，然后每个database是一个子目录。比如，名为civilnet的database的各种数据就是在/var/lib/mysql/civilnet目录下。当你在database中创建一个table，MySQL将会对应的在/var/lib/mysql/${database}/下创建一个table定义文件：${table}.frm。比如在civilnet数据库中创建一个syszux table，那么表定义文件就是：/var/lib/mysql/civilnet/syszux.frm。

因为MySQL使用文件系统上的文件名来作为数据库名和表定义文件，因此，数据库中database name和table name是否大小写敏感取决于当下操作系统的文件系统上的文件名是否大小写敏感。在Windows上不敏感的，而Linux上是大小写敏感的。

查看MySQL的各种metadata

1，查看table的信息

mysql> show table status like 'syszux' \G

*************************** 1. row ***************************

Name: syszux

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 9876336

Avg_row_length: 199

Data_length: 1974468608

Max_data_length: 0

Index_length: 0

Data_free: 6291456

Auto_increment: NULL

Create_time: 2020-02-03 14:40:30

Update_time: 2020-02-03 16:40:23

Check_time: NULL

Collation: utf8mb4_general_ci

Checksum: NULL

Create_options:

Comment: ??????

1 row in set (0.00 sec)

使用show table status命令可以查看一个table的metadata信息；以这个输出为例，显示syszux表是一个InnoDB表(除非特别的理由，就应该用InnoDB)；其它的信息还有：Row_format：Dynamic表明一个row是变长的，其它值还有有Dynamic，Fixed，Compressed；

Rows：表中的记录数量，对于MyISAM类型的表来说，这个数字是准确的；对于InnoDB来说，这个是个估计值，准确值用select count(id) from syszux;来获取；

Avg_row_length：平均每条记录的字节数；

Data_length：整个table中的数据的字节数；

Max_data_length：这个表可以存储数据的最大字节数；

Index_length：index占用的磁盘空间；

Data_free：对于MyISAM table来说，就是已经分配但是还没有使用的空间；

Auto_increment：下一个AUTO_INCREMENT值；

Create_time：这个表第一次创建的时间；

Update_time：这个表最后一次更新的时间；

Check_time：这个表最后一次被使用CHECK TABLE或者myisamchk的时间；

Collation：默认字符集；

一些关于InnoDB的特点：InnoDB不支持FULLTEXT类型的索引；

InnoDB 中不保存表的具体行数；

对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，而在MyISAM中，可以和其他字段建立联合索引；

Innodb 支持事务处理与外键和行级锁；

从MySQL 4.1之后，InnoDB会将data和index分开文件存放；

InnoDB有大量的内部优化：数据的提前读取；adaptive hash index来做到快速查询；插入buffer为了快速插入；

select count(id) from syszux 很慢；

2，查看一个table的所有字段及其类型

比如查看syszux表的所有字段及其类型：

mysql> show full columns from syszux;

3，转换MySQL的Engine

有3种方法：

1，ALTER TABLE

mysql> ALTER TABLE syszux ENGINE = InnoDB;

优点：简单，支持所有Engine；缺点：慢，会锁住源表，磁盘IO能力会用尽；

2，Dump再import

3，CREATE和SELECT

mysql> create table new_syszux LIKE syszux;

mysql> alter table new_syszux ENGINE=InnoDB;

mysql> insert into new_syszux select * from syszux;

4，查看MySQL的版本号

主要有两种方法，第一种：

mysql> show variables like "%version%";

+-------------------------+------------------------------+

| Variable_name | Value |

+-------------------------+------------------------------+

| innodb_version | 5.7.28 |

| protocol_version | 10 |

| slave_type_conversions | |

| tls_version | TLSv1,TLSv1.1,TLSv1.2 |

| version | 5.7.28-log |

| version_comment | MySQL Community Server (GPL) |

| version_compile_machine | x86_64 |

| version_compile_os | Linux |

+-------------------------+------------------------------+

8 rows in set (0.00 sec)

第二种，用status命令，当然也可以看到更多的其它信息：

mysql> status;

--------------

mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper

Connection id: 30296

Current database: syszux

Current user: root@172.66.163.8

SSL: Cipher in use is DHE-RSA-AES256-SHA

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.28-log MySQL Community Server (GPL)

Protocol version: 10

Connection: gemfield-mysql-read via TCP/IP

Server characterset: latin1

Db characterset: latin1

Client characterset: latin1

Conn. characterset: latin1

TCP port: 3306

Uptime: 17 hours 45 min 42 sec

Threads: 4 Questions: 112268 Slow queries: 0 Opens: 314 Flush tables: 3 Open tables: 205 Queries per second avg: 1.755

--------------

有些还很重要，比如Threads数量。

MySQL的Benchmark

最有效的工具还是使用sysbench，首先安装sysbench，Debian/Ubuntu上的安装方式如下：

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | bash

apt -y install sysbench

然后下面是一些和MySQL没有太多关系的使用例子：

#测试CPU的性能

sysbench --test=cpu --cpu-max-prime=20000 run

#file io，测试磁盘、RAID、CEPH等

sysbench --test=fileio --file-total-size=150G prepare

sysbench --test=fileio --file-total-size=150G --file-test-mode=rndrw/ --init-rng=on --max-time=300 --max-requests=0 run

#注意mode有seqwr、seqrewr、seqrd、rndrd、rndwr、rndrw等，关乎顺序读写和随机读写

#清理工作

sysbench --test=fileio --file-total-size=150G cleanup

sysbench还可以测试memory、threads、mutex、seqwr等性能，Gemfield这里就不细说了。然后是和MySQL相关的性能测试：

#prepare

root@gemfield-mysql-client-5596f7ff6f-5l8c2:/# sysbench --db-driver=mysql --mysql-host=gemfield-mysql-0.gemfield-mysql --mysql-user=root --mysql-db=test --range_size=100 --table_size=10000 --tables=2 --threads=1 --events=0 --time=60 --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua prepare

sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...

Inserting 10000 records into 'sbtest1'

Creating a secondary index on 'sbtest1'...

Creating table 'sbtest2'...

Inserting 10000 records into 'sbtest2'

Creating a secondary index on 'sbtest2'...

prepare完成之后，开始真正run：

sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 1

Initializing random number generator from current time

Initializing worker threads...

Threads started!

SQL statistics:

queries performed:

write: 0

other: 67998

total: 543984

transactions: 33999 (566.59 per sec.)

queries: 543984 (9065.42 per sec.)

ignored errors: 0 (0.00 per sec.)

reconnects: 0 (0.00 per sec.)

General statistics:

total time: 60.0020s

total number of events: 33999

Latency (ms):

min: 1.36

avg: 1.76

max: 7.15

95th percentile: 2.00

sum: 59935.66

Threads fairness:

events (avg/stddev): 33999.0000/0.00

execution time (avg/stddev): 59.9357/0.00

MySQL Server的性能研究(Performance profiling)

1，profiles

mysql> set profiling = 1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from syszux limit 10000000,20;

......

mysql> show profiles;

+----------+------------+------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------------------------------------+

| 1 | 7.82387075 | select * from syszux limit 10000000,20 |

+----------+------------+------------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

mysql> show profile for query 1;

+----------------------+----------+

| Status | Duration |

+----------------------+----------+

| starting | 0.000115 |

| checking permissions | 0.000011 |

| Opening tables | 0.000064 |

| init | 0.000042 |

| System lock | 0.000025 |

| optimizing | 0.000007 |

| statistics | 0.000024 |

| preparing | 0.000016 |

| executing | 0.000006 |

| Sending data | 7.823272 |

| end | 0.000016 |

| query end | 0.000013 |

| closing tables | 0.000022 |

| freeing items | 0.000177 |

| cleaning up | 0.000062 |

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

2，status

mysql> flush status;

mysql> select * from syszux limit 10000000,2;

......

mysql> show status where Variable_name like 'Handler%' or Variable_name like 'Created%';

+----------------------------+----------+

| Variable_name | Value |

+----------------------------+----------+

| Created_tmp_disk_tables | 0 |

| Created_tmp_files | 0 |

| Created_tmp_tables | 0 |

| Handler_commit | 1 |

| Handler_delete | 0 |

| Handler_discover | 0 |

| Handler_external_lock | 2 |

| Handler_mrr_init | 0 |

| Handler_prepare | 0 |

| Handler_read_first | 1 |

| Handler_read_key | 1 |

| Handler_read_last | 0 |

| Handler_read_next | 0 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 10000002 |

| Handler_rollback | 0 |

| Handler_savepoint | 0 |

| Handler_savepoint_rollback | 0 |

| Handler_update | 0 |

| Handler_write | 0 |

+----------------------------+----------+

21 rows in set (0.00 sec)

MySQL的配置项

如果是在Debian、Ubuntu系统上，配置文件为/etc/mysql/my.cnf；如果你不是很确定，那么可以让mysqld告诉你：

root@gemfield-mysql-0:/etc/mysql# mysqld --verbose --help | grep -A 1 "Default options"

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

配置文件有了，那常见的配置项有哪些呢？有这么几类：

1，线程类：

mysql> show variables like 'thread%';

+-------------------+---------------------------+

| Variable_name | Value |

+-------------------+---------------------------+

| thread_cache_size | 9 |

| thread_handling | one-thread-per-connection |

| thread_stack | 262144 |

+-------------------+---------------------------+

2，最大连接数：

mysql> show variables like "max_connections";

+-----------------+-------+

| Variable_name | Value |

+-----------------+-------+

| max_connections | 151 |

+-----------------+-------+

3，innodb比较重要的：

mysql> show variables like "innodb_%size%";

+----------------------------------+------------+

| Variable_name | Value |

+----------------------------------+------------+

| innodb_buffer_pool_size | 134217728 |

| innodb_log_file_size | 50331648 |

+----------------------------------+------------+

4，query_cache相关的：

mysql> show variables like "query_cache%";

+------------------------------+---------+

| Variable_name | Value |

+------------------------------+---------+

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 1048576 |

| query_cache_type | OFF |

| query_cache_wlock_invalidate | OFF |

+------------------------------+---------+

这些参数Gemfield喜欢改的有以下这些(这些改动都和你的硬件密切相关，一定要理解参数的含义)：innodb_buffer_pool_instances 改为8；

innodb_buffer_pool_size 改为 8GB(越大越像内存数据库)；

innodb_read_io_threads 改为16；

innodb_write_io_threads 改为16；

thread_cache_size改为20；

适当的优化上面这些参数，可以提升数据库的读写性能。

与此对应的，springboot中一些相关的配置如下，优化这些参数可以提高数据库的并发量；某些多线程的情况下可以优化几倍：

+ spring.datasource.hikari.minimum-idle=20

+ spring.datasource.hikari.maximum-pool-size=100

+ #最大线程数

+ server.tomcat.max-threads=1000

+ #接受和处理的最大连接数

+ server.tomcat.max-connections=20000

+ #初始化时创建的线程数

+ server.tomcat.min-spare-threads=20

+ #可以放到处理队列中的请求数

+ server.tomcat.accept-count=700

MySQL的query性能调优

1，super-read-only

[mysqld]

super-read-only

添加super-read-only配置后，MySQL节点变为只读的，那么在读的性能上来说，可能会有可忽略不计的性能提升 :-(；

2，Data type

table中各字段的类型，最好选用小的、简洁的、符合CPU特性的类型，比如各种内置的int。这里Gemfield就不细述了。

3，创建索引

Indexes(也叫keys，索引)是用来快速查找记录的数据结构，对性能来说很关键，尤其是当数据量很大的时候。对于DB的query操作的性能来说，index可能是第二强有力的方法，第一是正确的sql语法组合。

Index是在storage engine层实现的，而不是在mysql server层。这就意味着index不是标准化的，而是根据engine的不同而不同。

MySQL当前支持这几种索引：

B-Tree indexes，这是默认值，就是说如果人们提到索引又没有说它的类型时，那就是说的B-Tree索引。B-Tree index支持查找full key value、a key range、a key prefix，支持值查询和ORDER BY查询。

Hash indexes，只有Memory storage engine支持显式的hash indexes。Innodb内部有个功能叫做“adaptive hash indexes”，会对访问频繁的key自动建立hash index，这个过程用户无法自定义去控制它。Hash索引只支持完整key的哈希查找，不能排序；不支持range query；不支持key的部分match。

Spatial(R-Tree)indexes、Full-text indexes，哎，这两个不想说了。

另外，index的添加方法里要注意这几个非常重要的概念：column index、compound index、covering index(就是所有要query的字段都是index里，会显著加快query速度)，细节可以参考这里：https://stackoverflow.com/questions/8213235/mysql-covering-vs-composite-vs-column-index

使用索引有以下注意事项：

1，保证column的独立性，而不是条件表达式的一部分：

#比如对gemfield_id做了索引

#正确

mysql> select * from syszux where gemfield_id = 7030;

#错误

mysql> select * from syszux where gemfield_id + 1 = 7031;

2，要索引的字段的内容不能太长，比如你在为BLOB、TEXT、长的VARCHAR建立索引，你必须定义prefix索引，因为MySQL不允许为full length建立索引。prefix的长度选择多少呢？一个诀窍就是，既要省空间，又要保证尽可能的有区分度；细节就不说了；

3，查看一个表上已经建的索引：

mysql> show index from syszux\G

*************************** 1. row ***************************

Table: syszux

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 9869964

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: syszux

Non_unique: 1

Key_name: syszux__index_taskId

Seq_in_index: 1

Column_name: analysisTaskId

Collation: A

Cardinality: 1261

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 3. row ***************************

Table: syszux

Non_unique: 1

Key_name: syszux_index_name

Seq_in_index: 1

Column_name: result_name

Collation: A

Cardinality: 5647

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

3 rows in set (0.01 sec)

比如，对syszux表某个json字段建立索引，那么检索的速度会迅猛提升；可以使用explain来看下加索引前后query的区别。

加索引前：

mysql> explain select * from syszux where `result_name` = 'Gemfield';

+----+-------------+----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

+----+-------------+----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

+----+-------------+----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

加索引后：

mysql> explain select * from syszux where `result_name` = 'Gemfield';

+----+-------------+----------------------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------+

+----+-------------+----------------------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------+

| 1 | SIMPLE | syszux | NULL | ref | syszux_index_name | syszux_index_name | 82 | const | 2 | 100.00 | NULL |

+----+-------------+----------------------+------------+------+---------------------------------+---------------------------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

4，sql语法的优化

1，limit关键字

最常见的是偏移量很大的情况下的查询，常见于分页list的情况。mysql的limit关键字为分页list提供了很方便的功能，语法为limit [offset], [rows]；当offset很大的时候，这种mysql语句的性能急剧下降：

mysql> SELECT * FROM syszux WHERE taskId = 1214040326713376768 limit 8000000,10;

......

10 rows in set (7.38 sec)

这个时候使用inner join：

mysql> SELECT * FROM syszux INNER JOIN (SELECT id FROM syszux WHERE taskId = 1214040326713376768 LIMIT 8000000,10) x using (id);

......

10 rows in set (3.04 sec)

作为对比，只查询id所消耗的时间：

mysql> SELECT id FROM syszux WHERE taskId = 1214040326713376768 limit 8000000,10;

+---------------------+

| id |

+---------------------+

10 rows in set (2.92 sec)

如果where条件使用更严格的条件，从而让limit的offset从0开始，那就是光速了。

5，table partition

略过了，如果一个表逻辑上好拆分的话可以试试。

weixin_39776991

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql 最佳_MySQL的最佳实践

背景最开始就是部署了一个普通的MySQL容器，什么特别的设置都没有做，相当于一个helloworld版本的MySQL部署吧。然后基于MySQL的java web系统部署好后Gemfield做了性能测试，遇到了如下几个问题(都是读相关的)：在数据量达到几百万条级别(每条200个字节量)的时候，分页list的性能已经急剧下降；在数据量达到几百万条级别(每条200个字节量)的时候，JSON字段搜索的性能...
复制链接

扫一扫