MySQL优化

Tell · me · why

已于 2023-12-08 09:09:09 修改

阅读量49

点赞数 1

文章标签： mysql 数据库

于 2023-12-07 21:06:06 首次发布

本文链接：https://blog.csdn.net/weixin_47867713/article/details/134865036

版权

慢查询配置

5.5版本
[mysqld]
slow-query-log=on
slow_query_log_file=/var/lib/mysql/slowquery.log
long_query_time=1
#log-queries-not-using-indexes=on //列出没有使用索引的查询语句

5.7版本
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow/slow.log
long_query_time=3

# mkdir /var/log/mysql-slow/
# chown mysql.mysql /var/log/mysql-slow/
# systemctl restart mysqld

查看慢查询日志
测试:BENCHMARK(count,expr)
mysql> SELECT BENCHMARK(500000000,2*3);

字符集设置

临时：
mysql> create database db1 CHARACTER SET = utf8;
mysql> create table t1(id int(10)) CHARACTER SET = utf8;
永久：

5.5/5.7版本设置：
[mysqld]
character_set_server = utf8

注意：8.0默认使用utf8不需要设置字符集

存储引擎

存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。
用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

查看引擎：
mysql> show engines;
mysql> SHOW VARIABLES LIKE ‘%storage_engine%’;
mysql> show create table t1;
mysql> show table status like ‘t1’;

关闭不必要的引擎：
不想用哪个，加上跳过哪个就可以,重启mysql，这样也可以优化数据库。
默认如果不想他们启动的话，修改配置文件
#vim /etc/my.cnf
[mysqld]
skip-mrg_myisam
skip-csv
skip-memory

临时指定引擎：
mysql> create table innodb1(id int)engine=innodb;

修改默认引擎：
/etc/my.cnf
[mysqld]
default-storage-engine=INNODB

修改已经存在的表的引擎：
mysql> alter table t2 engine=myisam;

MySQL常用存储引擎:
MyISAM存储引擎
由于该存储引擎不支持事务、也不支持外键，所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。
.myi index 存储索引
.myd data 存储数据
.frm 存储表结构

InnoDB存储引擎
由于该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，所以比MyISAM存储引擎占用更多的磁盘空间。
因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。
大型数据库用innodb
[root@www var]# pwd
/usr/local/mysql/var

[root@www var]# ls ib*
ibdata1 ib_logfile0 ib_logfile1
innodb类型的表的数据存在ibdata1里面，不像myisam生成３个文件， ib_logfile0 ib_logfile1存放日志

初始是10M,每次增加8M ，初始大小可以指定，要修改配置文件
#vim /etc/my.cnf
innodb_data_file_path=ibdata1:20M:autoextend：max:1000M
设定初始大小是20M，增幅也是8M 设定峰值是1000M,就是指定最大能增加到1000M

innodb_data_home_dir=/data
指定他的存储数据的位置，也就是ibdata1的位置

数据分开存储:
比放在一块硬盘里访问速度快
默认的数据都保存在var下面，可以人为的改变他们的存储位置
只适用于innodb类型，.frm文件必须得放在var下，不能指定放到别的地方
#mkdir data 最好是不同的硬盘上
#chown mysql data
mysql> create table tb1(name char(20)) data directory=‘/data’;
data directory=指定数据文件的位置

MEMORY
速度快，比myisam快30％，当数据库重启之后，数据就会丢失，因为他是存在内存里的.适合于需要快速的访问或临时表。
mysql> create table t20(id int,name char(10)) type=memory; 创建一个memory类型的表

客户端连接MySQL数据库速度慢的问题

修改my.cnf配置，关闭DNS的反向解析参数
[mysqld]
skip-name-resolve

本地客户端不用输入密码登录的配置

[client]
user = root
password = 123

调优思路整理

1.数据库设计与规划–以后再修该很麻烦，估计数据量，使用什么存储引擎
2.数据的应用–怎样取数据，sql语句的优化
3.mysql服务优化–内存的使用，磁盘的使用(运维)
4.操作系统的优化–内核、tcp连接数量（运维）
5.升级硬件设备（运维）

1.数据库设计与规划
采用什么样的引擎，如果网站使用myisam，如果事务使用innodb
mysql版本，是否需要升级，
版本升级原则：4.0->4.1->5.0->5.1->5.5
升级工具：mysql_upgrade ,如果是innodb类型的表，转换成myisam
a，数据转myisam引擎
alter table
b，复制数据文件，到最新数据库中
c , mysql_upgrade
d , 恢复原来的存储引擎
是否用cluster，是否需要分区，安装方式
操作系统的选择（win/linux/unix）

2.数据的应用
建立索引
在海量查询时少用格式转换，如to_char,to_number
多用exists，这样语句性能高很多，少用in
尽量避免使用is null，空值索引失效

3.磁盘io规划
raid技术：raid0
swap分区：最好使用raid0
磁盘分区：
一个库放到一个分区上或一个磁盘上

物理分区:
#mkdir /data
#chown mysql.mysql /data
mysql> create table t1(id int,name char(20)) data directory=‘/data/’;

4.操作系统的优化
网卡bonding技术
tcp连接数量限制
优化系统打开文件的最大限制
关闭操作系统不必要的服务

5.mysql服务优化
show status 看系统的资源
show variables 看变量，在my.cnf配置文件里定义的
show processlist 显示系统中正在运行的所有进程。

启用mysql慢查询：—分析sql语句，找到影响效率的 SQL

查看数据库启动后不会动态更改的值，比如缓冲区大小、字符集、数据文件名称等等：
# mysqladmin variables
mysql> show variables;
mysql> show variables like “%char%”;

查看数据库运行期间动态变化的信息，比如锁等待、当前连接数等等：
# mysqladmin extended-status
mysql> show status;
mysql> show status like ‘%slow%’;

# mysqladmin status
Uptime: 4906 Threads: 1 Questions: 15 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.3

# mysqladmin -i 2 status //-i 2 #两秒钟刷新一次
Questions 所有查询的数量
Opens 服务器已经打开的数据库表的数量
Open_tables 通过命令打开的表的数量
mysql> show open tables;

查看参数用法和当前使用的参数值：
5.7
# mysqld -v --help | grep -A 2 default-character

实际上绝大多数参数不是经常需要用户调整的
对查询进行缓存:
query_cache_size 使用多大内存来缓存查询语句

query_cache_limit 超过此大小的查询将不缓存

query_cache_wlock_invalidate 当有其他客户端正在对表进程写操作时，如果查询在query cache中，是返回cache结果还是等写操作完成再读表获取结果
query_cache_min_res_unit 缓存块儿的最小大小，如果块儿大但是查询的数据都是小数据，会导致内存碎片增多，浪费内存

设置举例：
#vim /etc/my.cnf
query_cache_size=10M
重启服务，查看# /usr/libexec/mysqld -v --help | grep query_cache_size

Qcache_free_blocks：说明缓存太大了。缓存中相邻内存的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_total_blocks: 缓存中块儿总数

缓存碎片率计算：
Qcache_free_blocks / Qcache_total_blocks * 100%
如果缓存碎片率超过20%，就要使用FLUSH QUERY CACHE整理缓存碎片或尝试减小块儿大小
Qcache_free_memory 缓存中的空闲内存
Qcache_hits 每次查询在缓存中命中时就增大
Qcache_inserts 每插入一个查询时就增大。命中次数除以插入次数就是命中率。
Qcache_lowmem_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间看；如果这个数字在不断增长就表示可能碎片非常严重，或者内存很少
Qcache_hits/Qcache_inserts 命中率命中率低，可能因为写操作太频繁
缓存利用率：
(query_cache_size - Qcache_free_memory)/query_cache_size * 100%
缓存利用率在25%以下说明query_cache_size设置过大
缓存利用率在80%以上且 Qcache_lowmem_prunes > 50说明query_cache_size设置过小或者碎片过多。

强制限制mysql资源设置:
max_connections = 200 客户端并发连接数
connect_timeout=
临时表空间大小：order by和group by时把数据放到临时表里。
tmp_table_size 占的是内存的大小，如果太小在排序时会出错
created_tmp_tables 创建临时表的数量
max_tmp_tables=32
tmpdir=/tmp 硬盘上临时表所在的位置

myisam引擎：
key_buffer_size
说明: 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）。

使用方法:
mysql5.1 以前只允许使用一个系统默认的 key_buffer
mysql5.1 以后提供了多个 key_buffer,可以将指定的表索引缓存入指定的key_buffer,这样可以更小的降低线程之间的竞争,相关语法如下:

预装表t1，t2的所有索引：

删除键高速缓冲,将其大小设置为零:
mysql> set global host_cache2.key_buffer_size=0;

不能删除默认key_buffer:
mysql> set global key_buffer_size=0;
ERROR 1438 (HY000): Cannot drop default keycache

永久生效：
cache index在一个表和key_buffer之间建立联系，但每次服务器重启时key_buffer中的数据将清空。如果想每次服务器重启是相应的索引能自动放到key_buffer中，
可以在配置文件中设置init-file选项来制定包含cache index语句的文件路径，然后在对应的文件中写入cache index语句。
#vim /etc/my.cnf //添加下面几行
key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql
# cat /path/to/data-directory/mysqld_init.sql
cache index test.t1 in hot_cache;
cache index test.t2 in cold_cache;
相关状态参数值：
mysql> show status like “Key_read”;
Key_read_requests 请求总数
Key_reads 代表命中磁盘的请求个数
(key_read_requests - key_read)/key_read_requests：命中率

table_open_cache（table_cache已经不能使用）表高速缓存：缓存表的数量
table_open_cache 打开表缓存总数，如果经常对一些表进行操作，希望将读过的表放入到cache中，减少对磁盘的读取次数。可以检查峰值时间的状态值Open_tables和Opened_tables.

open_tables 通过命令打开的表的数量
opened_tables 曾经打开过的表的数量，这个值累加，这个值再增加说明表的缓存要设置大些，用flush tables清空表缓存的时候，这个不会改变
表缓存没有命中的数量。如果该值很大，你可能需要增加table_cache的数值。典型地，你可能想要这个值每秒打开的表数量少于1或2。
如果你发现open_tables等于table_open_cache，并且opened_tables在不断增长，那么你就需要增加table_open_cache的值了。
在mysql默认安装情况下，table_cache的值在2G内存以下的机器中的值默认时256到512,对于有1G内存的机器，推荐值是128－256。

设置技巧:
可以通过检查 mysqld 的状态变量 Opened_tables 确定表缓存是否太小:
mysql> show status like “open_tables”; //如果值很大,即使你没有发出许多 FLUSH TABLES 语句,也应增加表缓存的大小。
±--------------±------+
| Variable_name | Value |
±--------------±------+
| Open_tables | 10 |
±--------------±------+

mysql> flush tables; //清空表缓存

innodb引擎：
innodb-buffer-pool-size //缓存 InnoDB 数据和索引的内存缓冲区的大小
# /usr/libexec/mysqld -v --help | grep “–innodb-buffer-pool-size” -A 2 //-A表示包含关键字的行和他后面的两行
–innodb-buffer-pool-size=#
The size of the memory buffer InnoDB uses to cache data
and indexes of its tables.

这个值设得越高,访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的 80%。尽管如此,还是不要把它设置得太大,因为对物理内存的竞争可能在操作系统上导致内存调度。

myisam的key_buffer_size只缓存索引键，而innodb-buffer-pool-size却是同时为数据块和索引块做缓存，这个特性和ORACLE是一样的。

创建表空间文件:
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
这个设置配置一个可扩展大小的尺寸为10MB的单独文件，名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。
如果你对最后的数据文件指定autoextend选项。如果数据文件耗尽了表空间中的自由空间，InnoDB就扩展数据文件。扩展的幅度是每次8MB。
要为一个自动扩展数据文件指定最大尺寸，请使用max属性。
下列配置允许ibdata1涨到极限的500MB：
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend:max:500M
InnoDB默认地在MySQL数据目录创建表空间文件。要明确指定一个位置，请使用innodb_data_home_dir选项。比如，要使用两个名为ibdata1和ibdata2的文件，但是要把他们创建到/ibdata，像如下一样配置InnoDB：
[mysqld]
innodb_data_home_dir = /ibdata //注意设置这个选项的时候，如果第一次起不来需要删除这次产生的表空间文件再次重启
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

为每个表生成一个表空间文件：
innodb_file_per_table=1
重启mysql

其他参数
skip-locking(已经用下面参数代替)
skip-external-locking=on
取消文件系统的外部锁，减少出错几率增强稳定性

skip-name-resolve
关闭mysql的dns反查功能。速度快很多!
选项就能禁用DNS解析，连接速度会快很多。不过，这样的话就不能在My中使用主机SQL的授权表名了而只能用ip格式。

wait_timeout=10 终止空闲时间超过10秒的链接，避免长连接

max_connect_errors=10 //10次连接失败就锁定，使用flush hosts 解锁，
或mysqladmin flush-hosts解锁

innodb_additional_mem_pool_size
InnoDB 用来存储数据目录信息和其它内部数据结构的内存池的大小。默认值是1MB。应用程序里的表越多,你需要在这里分配越多的内存。如果 InnoDB 用光了这个池内的内存,InnoDB 开始从操作系统分配内存,并且往 MySQL 错误日志写警告信息。没有必要给这个缓冲池分配非常大的空间,在应用相对稳定的情况下,这个缓冲池的大小也相对稳定。

innodb_lock_wait_timeout:
Mysql 可以自动的监测行锁导致的死锁并进行相应的处理,但是对于表锁导致的死锁不能自动的监测,
所以该参数主要被用来在出现类似情况的时候对锁定进行回滚。默认值是 50 秒,根据应用的需要进行调整。

innodb_support_xa:
通过该参数设置是否支持分布式事务,默认值是 ON 或者 1,表示支持分布式事务。如果确认应用中不需要使用分布式事务,则可以关闭这个参数,减少磁盘刷新的次数并获得更好的 InnoDB 性能。

分布式事务：
像这种情况一定需要分布式的事务，要不都提交，要么都回滚。在任何一个节点出问题都会造成严重的结果：1 xiaozhang的帐号被扣款，但是xiaoli没有收到钱；2 xiaozhang的帐号没有被扣款，但是xiaoli收到钱了。

innodb_doublewrite:
默认地,InnoDB 存储所有数据两次,第一次存储到 doublewrite 缓冲,然后存储到确实的数据文件。如果对性能的要求高于对数据完整性的要求,那么可以通过–skip-innodb-doublewrite 关闭这个设置。

innodb_log_file_size:
在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高,但是要注意到可能会增加恢复时间。