mysql配置文件提高查询速率_MySQL数据库优化

最新推荐文章于 2023-03-17 13:14:07 发布

邱广涛

最新推荐文章于 2023-03-17 13:14:07 发布

阅读量574

点赞数

文章标签： mysql配置文件提高查询速率

本文链接：https://blog.csdn.net/weixin_29890207/article/details/114352625

版权

数据库优化的目的

优化用户体验，提高响应速度，使得应用具有一个良好的用户体验。

数据库优化的几个方面

这几个方面优化的效果是逐渐降低的，并且优化的成本也是逐渐增加的。

SQL及索引

表结构

MySQL配置

系统配置和硬件

1.SQL及索引

SQL语句的优化步骤：

开启MySQL慢查日志(slow_query_log)。

分析MySQL慢查日志，通过使用一些MySQL慢查日志分析工具(mysqldumpslow、pt-query-digest)。

优先查找出一些查询次数多且执行时间长的SQL语句。

explain查询SQL语句的执行计划，针对执行计划进行优化。

具体的SQL语句优化：

网查。

索引优化：

索引字段长度越小越好。因为数据库中数据存储单位是“页”，索引字段长度越小一次IO读取的索引会更多，越容易命中对应的值。(大意就是不要选择text列上面建立一个索引，而是选择简单的数据类型比如int。)

联合索引顺序，离散度大的列放到联合索引的前面。(离散度就是差异程度，列上不同的值越多，离散程度就越大。)

使用pt-duplicate-key-checker工具检查重复及冗余索引，删除重复及冗余的索引。

2.表结构

表的范式化，一般是指第三范式(3NF)。

反范式化，针对业务，表做适当的冗余。

使用可以存下你的数据的最小数据类型。

尽可能使用简单的数据类型。(int类型要比varchar类型mysql处理起来更简单)

尽可能使用not null定义字段，使用一个默认值来替代空值，除了需要存储为null的情况。(null 并不是真正意义上的空值，null 也会占用空间。null 列一般需要一个额外的字节作为判断是否为 null 的标志位，占用更多的存储空间。使得索引、索引统计和值比较更加复杂，每个索引记录需要一个额外的字节作为判断是否为 null 的标志位。)

尽量少用text等大类型，非用不可时考虑分表。

表的垂直拆分，解决表的宽度过宽的问题。(把不常用的字段拆分出一张表；把大字段拆分出一张表；把经常使用的字段拆分出一张表。)

表的水平拆分，解决表的数据量过大的问题。(一般对主键做hash运算来分散到几张表中。)

3.MySQL配置

MySQL可以通过启动时指定配置参数和使用配置文件两种方法进行配置，在大多数情况下配置文件位于/etc/mysql/my.cnf。

一些常见参数：

参数

作用

max_connections

Mysql最大连接数。参数对性能影响不大，一般设置的大一些避免”Too many connections”的错误。

innodb_buffer_pool_size

Innodb缓冲池总大小。建议内存的80%，建议大于表数据加索引的和。

innodb_buffer_pool_instances

Innodb缓冲池的个数。

innodb_log_buffer_size

Innodb日志缓冲大小，由于日志最长每秒就会刷新，所以一般不用太大。

innodb_flush_log_at_trx_commit

0：事务提交时，不将重做日志缓冲写入磁盘，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。因此如果 MySQL 发生宕机，那么就有可能丢失一部分事务。

1：事务提交时，会将重做日志缓冲写入磁盘，并且立即刷新(fsync())。注意，因为操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。

2：事务提交时，会将重做日志缓冲写入磁盘，但是不会立即进行刷新操作，因此只是写到了操作系统的缓冲区。此时若操作系统发生宕机而没有即使的同步，也可能会丢失一部分数据。

一般建议为2，安全性要求较高则使用默认值1。

innodb_read_io_threads

Innodb读的IO进程数。

innodb_write_io_threads

Innodb写的IO进程数。

innodb_file_per_table

Innodb的表使用独立的表空间，默认为OFF，也就是所有表都会建立在共享表空间中。建议为ON。

innodb_stats_on_metadata

使用 SHOW INDEX、SHOW TABLE STATUS、SHOW [FULL] TABLES时，会自动更新统计信息，或者对应的从 INFORMATION_SCHEMA.TABLES和INFORMATION_SCHEMA.STATISTICS 表中查询时，刷新innodb表的统计信息。默认为ON，建议设置为OFF。

4.系统配置和硬件

数据库是基于操作系统的，目前大多数MySQL都是安装在Linux系统之上，所以操作系统的一些参数配置也会影响到MySQL的性能。

网查参考：

一、内核相关(/etc/sysctl.conf)

#每一个端口最大的监听队列的长度

net.core.somaxconn = 65535

#当网络接受速率大于内核处理速率时，允许发送到队列中的包数目

net.core.netdev_max_backlog = 65535

#保持未连接的包最大数量，tcp队列的大小

net.ipv4.tcp_max_syn_backlog = 65535

#减少tcp的TIME_WAIT，加快资源回收

net.ipv4.tcp_max_tw_buckets = 8000