西乡做网站的公司,安卓手机优化大师官方下载,十大黄冈网站排行榜,莱芜都市网二手市场目录
前言
一、Mysql逻辑架构图
二、Mysql单实例常见故障
1、无法通过套接字连接到本地MySQL服务器
2、用户rootlocalhost访问被拒绝
3、远程连接数据库时连接很慢
4、无法打开以MYI结尾的索引文件
5、超出最大连接错误数量限制
6、连接过多
7、配置文件/etc/my.cnf权…目录
前言
一、Mysql逻辑架构图
二、Mysql单实例常见故障
1、无法通过套接字连接到本地MySQL服务器
2、用户rootlocalhost访问被拒绝
3、远程连接数据库时连接很慢
4、无法打开以MYI结尾的索引文件
5、超出最大连接错误数量限制
6、连接过多
7、配置文件/etc/my.cnf权限问题
8、innodb数据文件损坏
三、Mysql主从环境常见故障
1、从库的I/O线程停止
2、从库的 Slave_IO_Running 为 NO
3、从库的中继日志relay-bin损坏
四、查看Mysql相关性能
五、Mysql优化
1、Mysql硬件优化
2、Mysql主配置文件优化
2.1 Mysql全局参数优化
2.2 Mysql二进制日志的参数优化
2.3 Mysql关于innodb引擎的参数优化
2.4 Mysql关于myisam引擎的参数优化
3、数据库设计与规划架构上的优化)
4、数据查询优化
5、对查询进行缓存
5.1 启动查询缓存
5.2 查看查询缓存
5.3 使用查询缓存
6、分库分表优化
6.1 什么是分库分表
6.2 为什么要分库分表
6.3 分库分表的实施策略
6.4 分库分表存在的问题 前言
MySQL是目前企业最常见的数据库之一在日常维护管理的过程中会遇到很多故障。本文汇总了常见的故障可增长学习经验。而MySQL默认配置无法满足高性能要求所以本文还汇总了常见的性能优化配置
一、Mysql逻辑架构图 客户端和连接服务 客户端Client客户端是与MySQL数据库进行交互的应用程序或工具例如MySQL命令行客户端、MySQL Workbench等。客户端负责发送SQL查询和接收查询结果。连接服务Connection Service连接服务负责管理客户端与MySQL服务器之间的连接。它处理客户端的连接请求管理连接池并确保连接的安全性和可靠性。 核心服务功能 SQL接口SQL InterfaceSQL接口负责接收客户端发送的SQL查询并将其转换为MySQL内部能够理解的格式。查询解析器Query Parser查询解析器解析SQL查询语句构建查询执行计划并优化查询以提高执行效率。查询缓存Query Cache查询缓存可以缓存查询结果加快相同查询的执行速度。优化器Optimizer优化器分析查询执行计划的多个可能路径并选择最优的执行路径以提高查询性能。 存储引擎层 存储引擎层负责管理数据的存储和检索不同的存储引擎具有不同的特性和适用场景。常见的存储引擎包括 InnoDB支持事务处理和行级锁定适合于需要强大事务支持的应用。MyISAM不支持事务处理但具有较快的读取速度适合于读操作较多的应用。Memory将数据存储在内存中读写速度非常快但数据不持久化适合于临时数据存储。等等MySQL还支持其他存储引擎每种引擎都有自己的优缺点可以根据应用需求选择合适的存储引擎。 数据存储层 数据存储层是实际存储数据的地方包括表、索引、数据文件等。存储引擎通过数据存储层与底层存储交互读取和写入数据。
这些组件共同构成了MySQL的逻辑架构每个部分都扮演着重要的角色协同工作以提供高效、可靠的数据库服务 二、Mysql单实例常见故障
1、无法通过套接字连接到本地MySQL服务器
#报错信息
ERROR 2002 (HY000): Cant connect to local MySQL server through socket /data/mysql/mysql.sock (2)
#错误2002 (HY000)无法通过套接字连接到本地MySQL服务器问题分析
以上这种情况一般都是数据库未启动数据库端口被防火墙拦截导致数据库故障
解决方法
启动数据库防火墙开放数据库监听端口修复数据库
2、用户rootlocalhost访问被拒绝
#报错信息
ERROR 1045 (28000): Access denied for user rootlocalhost (using password: NO)
#错误1045(28000)用户rootlocalhost访问被拒绝(使用密码NO)
问题分析
密码不正确没有权限访问
解决方法
1密码不正确可修改root用户密码
①修改 my.cnf 主配置文件在[mysqld]下添加 skip-grant-tables重启数据库
[rootlocalhost ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables
#添加使登录mysql不使用授权表
[rootlocalhost ~]#systemctl restart mysqld.service ②无密码登录之后修改数据库用户中的 root 密码
update mysql.user set authentication_string password(123) where userroot;
#用于更新MySQL数据库系统表mysql.user中的用户root的密码。AUTHENTICATION_STRING是MySQL 5.7.0以后版本中用于存储密码的字段名 ③登录测试新密码 ④撤销添加的免密码认证设置重启数据库服务那么修改root密码成功之后可用新密码登录到数据库
[rootlocalhost ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables
#撤销添加的免密码认证设置
[rootlocalhost ~]#systemctl restart mysqld.service
2 没有权限访问可重新授权
[rootlocalhost ~]#grant all on *.* to rootmysql-server identified by 123456;
3、远程连接数据库时连接很慢
#报错信息
在使用远程连接数据库时偶尔会发生远程连接数据库很慢的问题
问题分析
如果 MySQL 主机查询 DNS 很慢有很多客户端同时连接时会导致连接很慢
由于开发机器是不能够连接外网的在进行 MySQL 连接时DNS 解析是不可能完成的 从而也就明白了为什么连接那么慢了
解决方法
修改 my.cnf 主配置文件在[mysqld]下添加 skip-name-resolve重启数据库可以解决
注意在数据库授权禁止使用主机名
4、无法打开以MYI结尾的索引文件
#报错信息
Cant open file: xxx_forums.MYI. (errno: 145)
#无法打开文件xxx_forums.MYI (errno: 145)
问题分析
服务器非正常关机如机房意外断电等情况数据库所在空间已满或一些其它未知的原因对数据库表造成了损坏可能是操作系统下直接将数据库文件拷贝移动会因为文件的属组问题而产生该错误
解决方法
可以使用下面的两种方式修复数据表
方法一修复数据表仅适合独立主机用户
使用 MySQL 自带的专门用户数据表检查和修复工具 myisamchk。一般情况下只有在命令行下面才能运行 myisamchk 命令。常用的修复命令为
myisamchk -r 数据文件目录/数据表名.MYI;
通过 phpMyAdmin 修复 phpMyAdmin 带有修复数据表的功能进入到某一个表中后点击“操作”在下方的“表维护”中点击“修复表”即可
注意以上两种修复方式在执行前一定要备份数据库
方法二修改文件的属组仅适合独立主机用户
复制数据库文件的过程中没有将数据库文件设置为 MySQL 运行的帐号可读写一般适用于 Linux 和 FreeBSD 用户
5、超出最大连接错误数量限制
#报错信息
ERROR 1129 (HY000): Host xxx.xxx.xxx.xxx is blocked because of many connection errors; unblock with mysqladmin flush-hosts
#错误1129 (HY000)主机xxx.xxx.xxx.xxx被阻塞因为许多连接错误可使用mysqladmin flush-hosts解除阻塞
问题分析
由于 mysql 数据库的参数max_connect_errors其默认值是 10当大量(max_connect_errors)的主机去连接 MySQL总连接请求超过了 10 次新的连接就再也无法连接上 MySQL 服务。同一个 ip 在短时间内产生太多中断的数据库连接而导致的阻塞 超过 mysql 数据库max_connection_errors 的最大值
解决方法
方法一使用 mysqladmin flush-hosts 命令清除缓存
mysqladmin -uroot -p -h 172.16.12.10 flush-hosts Enter password:
方法二修改mysql配置文件(max connect errors的值)
[rootlocalhost ~]#vim /etc/my.cnf
[mysqld]
max connect errors10000
#根据实际情况设定连接数值
[rootlocalhost ~]#systemctl restart mysqld.service
6、连接过多
#报错信息
Too many connections
#连接过多
问题分析
指在数据库中同时建立的连接数超出 Mysql 的最大连接数限制导致数据库系统无法正常工作
解决方法方法一在 my.cnf 配置文件里面增大连接数然后重启 MySQL 服务下次生效
[rootlocalhost ~]#vim /etc/my.cnf
[mysqld]
max_connections 10000
#根据实际情况设定最大连接数值
[rootlocalhost ~]#systemctl restart mysqld.service
方法二临时修改最大连接数重启后不生效
mysql set GLOBAL max_connections10000;
7、配置文件/etc/my.cnf权限问题
#报错信息
Warning: World-writable config file /etc/my.cnf is ignored ERROR! MySQL is running but PID file could not be found
#警告全局可写配置文件/etc/my.cnf被忽略MySQL正在运行但是找不到PID文件
问题分析
MySQL 的配置文件/etc/my.cnf 权限不对
解决方法
修改配置文件/etc/my.cnf 权限
chmod 644 /etc/my.cnf
8、innodb数据文件损坏
#报错信息
InnoDB: Error: page 14178 log sequence number 29455369832
InnoDB: is in the future! Current system log sequence number 29455369832
问题分析
innodb数据文件损坏
解决方法
修改 my.cnf 配置文件在[mysqld]下添加 innodb_force_recovery4启动数据库后备份数据文件然后去掉该参数利用备份文件恢复数据
[rootlocalhost ~]#vim /etc/my.cnf
[mysqld]
innodb_force_recovery4
[rootlocalhost ~]#systemctl restart mysqld.service
三、Mysql主从环境常见故障
1、从库的I/O线程停止
#报错信息
The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
问题分析
网络不通防火墙、核心防护selinux没有关闭 my.cnf配置有问题主库和从库的 server-id 值一样、二进制文件和中继日志选项不存在设置主从同步时密码、file文件名、pos偏移量不对
解决方法
优化网络关闭防火墙、核心防护selinux修改 my.cnf 配置文件将主库和从库的 server-id 值设置不一样、再添加二进制文件和中继日志选项重新设置主从同步注意密码、file文件名、pos偏移量正确
2、从库的 Slave_IO_Running 为 NO
#报错信息
从库的Slave_IO_Running为NO
问题分析
造成从库线程为 NO 的原因会有很多主要原因是主键冲突或者主库删除或更新数据从库内找不到记录数据被修改导致。通常状态码报错有 1007、1032、1062、1452 等
解决方法
方法一
mysql stop slave;
mysql set global sql_slave_skip_counter1;
mysql start slave;
方法二设置用户权限设置从库只读权限
mysql set global read_onlytrue;
3、从库的中继日志relay-bin损坏
#报错信息
Error initializing relay log position: I/O error reading the header from the binary log
分析问题
从库的中继日志 relay-bin 损坏
解决方法
手工修复重新找到同步的 binlog 和 pos 点然后重新同步即可。
mysqlcreate master to master_log_filemysql-bin.xxx,master_log_posxxx;
四、查看Mysql相关性能
1查看每个客户端IP过来的连接消耗了多少资源
mysql select * from sys.x$host_summary;#系统表 sys.x$host_summary 中选择所有的列数据
2查看某个数据文件上发生了多少IO请求
mysql select * from sys.x$io_global_by_file_by_bytes;#系统表 sys.x$io_global_by_file_by_bytes 中选择所有的列数据
3查看每个用户消耗了多少资源
mysql select * from sys.x$user_summary;#从系统表 sys.x$user_summary 中选择所有列的数据
4查看总共分配了多少内存
mysql select * from sys.x$memory_global_total;#从系统表 sys.x$memory_global_total 中选择所有列的数据
5数据库连接来自哪里以及这些连接对数据库的请求情况是怎样的
mysql select host, current_connections, statements from sys.x$host_summary;#从系统表 sys.x$host_summary 中选择 host主机、current_connections当前连接数和 statements语句数量这三个字段
#换句话说这条指令的目的是获取主机的相关信息包括当前的连接数以及执行的语句数量
6查看当前正在执行的SQL和执行show full processlist的效果相当
mysql select conn_id, user, current_statement, last_statement from sys.x$session;#从系统表 sys.x$session 中选择 conn_id连接ID、user用户、current_statement当前语句和 last_statement最后一次语句这四个字段
#换句话说这条指令的目的是获取当前会话的连接ID、用户信息以及当前正在执行的语句以及最后一次执行的语句
7数据库中哪些SQL被频繁执行
mysql select db,exec_count,query from sys.x$statement_analysis order by exec_count desc limit 10;
#从系统表 sys.x$statement_analysis 中选择 db数据库、exec_count执行次数和 query查询这三个字段并按照执行次数降序排列然后限制结果只显示前10行数据
#换句话说这条指令的目的是找出执行次数最多的前10个查询语句以及它们所属的数据库
8哪个文件产生了最多的IO读多还是写的多
mysql select * from sys.x$io_global_by_file_by_bytes limit 10;#从系统表 x$io_global_by_file_by_bytes 中选择所有的列并返回前10行的结果
#这种查询通常用于查看系统中 I/O 操作的相关信息例如文件级别的 I/O 统计数据以便进行性能分析和监控
9哪个表上的IO请求最多
mysql select * from sys.x$io_global_by_file_by_bytes where file like %ibd order by total desc limit 10;#从系统表 x$io_global_by_file_by_bytes 中选择所有的列其中文件名file包含 ibd 的记录并按照总字节数total进行降序排列然后返回前10行的结果
#这种查询通常用于查找在MySQL中 I/O 操作量最大的 InnoDB 数据文件以便进行性能分析和优化
10哪个表被访问的最多
先访问statement_analysis根据热门SQL排序找到相应的数据表
哪些语句延迟比较严重
查看statement_analysis中avg_latency的最高的SQL
mysql select * from sys.x$statement_analysis order by avg_latency desc limit 10;#从系统表 x$statement_analysis 中选择所有的列并按照 avg_latency 字段的值进行降序排列然后返回前10行的结果
11哪些SQL执行了全表扫描如果没有使用索引则考虑为大型表添加索引
mysql select * from sys.x$statements_with_full_table_scans;#从系统表 x$statements_with_full_table_scans 中选择所有的列并返回它们的取值。这个查询可能是用于查找在数据库中执行全表扫描的SQL语句以便进行性能分析和优化
12列出所有做过排序的规范化语句
mysql select * from sys.x$statements_with_sorting;#从系统表 x$statements_with_sorting 中选择所有的列并返回它们的取值。这个查询可能是针对某个数据库或者系统进行性能分析、优化或者监控时使用的
13哪些SQL语句使用了临时表又有哪些用到了磁盘临时表
查看statement_analysis中哪个SQL的tmp_tables 、tmp_disk_tables值大于0即可
mysql select db, query, tmp_tables, tmp_disk_tables from sys.x$statement_analysis where tmp_tables0 or tmp_disk_tables 0 order by (tmp_tablestmp_disk_tables) desc limit 20;#sys.x$statement_analysis: 这是一个系统视图它提供了当前正在运行的语句的分析信息包括语句的SQL文本、语句的执行计划、临时表的名称、临时表的行数等
#select db, query, tmp_tables, tmp_disk_tables: 这部分选择了db数据库名称、querySQL文本、tmp_tables内存中的临时表数量、tmp_disk_tables磁盘上的临时表数量这四个字段
#where tmp_tables0 or tmp_disk_tables 0: 这部分过滤了tmp_tables或tmp_disk_tables大于0的记录即只选择涉及到临时表的语句
#order by (tmp_tablestmp_disk_tables) desc: 这部分将结果按照tmp_tables和tmp_disk_tables的总和即临时表的总数量降序排序
#limit 20: 这部分限制结果集的大小为20行。只有临时表使用最多的20个语句的信息会被返回
14列出所有使用临时表的语句——访问最高的磁盘临时表然后访问内存临时表
mysqlselect * from sys.statements_with_temp_tables;#sys.statements_with_temp_tables是一个系统视图它提供了当前正在运行的语句中涉及到临时表的详细信息包括语句的SQL文本、语句的执行计划、临时表的名称、临时表的行数等
15哪个表占用了最多的buffer pool
mysql select * from sys.x$innodb_buffer_stats_by_table order by allocated desc limit 10;#sys.x$innodb_buffer_stats_by_table: 这是一个系统视图它提供了InnoDB缓冲池中每个表的统计信息
#order by allocated desc: 这部分将结果按照allocated字段即分配的空间降序排序。desc表示降序排序
#limit 10: 这部分限制结果集的大小为10行。只有分配最多空间的10个表的信息会被返回
16每个库database占用多少buffer pool
mysql select * from sys.x$innodb_buffer_stats_by_schema order by allocated desc limit 10;#sys.x$innodb_buffer_stats_by_schema: 这是一个系统视图它提供了InnoDB缓冲池中每个数据库模式的统计信息
#order by allocated desc: 这部分将结果按照allocated字段即分配的空间降序排序。desc表示降序排序
#limit 10: 这部分限制结果集的大小为10行。只有分配最多空间的10个数据库模式的信息会被返回
17获取指定数据库dbname中所有表的名称、注释、数据大小和行数
select table_name,table_comment,concat(truncate(data_length / 1024 / 1024/1024, 4),\GB\) AS data_size,table_rows from information_schema.tables where table_schemadbname#table_name: 这是表的名称。
#TABLE_COMMENT: 这是表的注释。在创建表时你可以为表添加注释。
#CONCAT(TRUNCATE(data_length / 1024 / 1024/1024, 4),GB) AS data_size: 这部分计算并格式化表的数据大小
data_length是表的数据大小以字节为单位
TRUNCATE(data_length / 1024 / 1024/1024, 4)将数据大小转换为GB并保留4位小数
CONCAT(...,GB)将结果格式化为GB
#table_rows: 这是表的行数
五、Mysql优化
1、Mysql硬件优化
说到服务器硬件最主要的无非 CPU、内存、磁盘、网络四大关键因素。
关于 CPU
CPU 对于 MySQL 应用推荐使用 S.M.P.架构的多路对称 CPU。例如可以使用两颗Intel Xeon 3.6GHz 的 CPU。现在比较推荐用 4U 的服务器来专门做数据库服务器不仅仅是针对于 MySQL
关于内存
物理内存对于一台使用 MySQL 的 Database Server 来说服务器内存建议不要小于2GB推荐使用 4GB 以上的物理内存。不过内存对于现在的服务器而言可以说是一个可以忽略的问题工作中遇到了高端服务器基本上内存都超过了 32G
关于磁盘
磁盘寻道能力磁盘 I/O。以目前市场上普遍高转速 SAS 硬盘(15000 转/秒)为例 这种硬盘理论上每秒寻道 15000 次这是物理特性决定的没有办法改变。 MySQL 每秒钟都在进行大量、复杂的查询操作对磁盘的读写量可想而知。所以通常认为磁盘 I/O 是制约 MySQL 性能的最大因素之一通常是使用 RAID-01 磁盘阵列注意不要尝试使用RAID-5MySQL 在 RAID-5 磁盘阵列上的效率并不高。如果不考虑硬件的投入成本也可以考虑固态SSD硬盘专门作为数据库服务器使用。数据库的读写性能肯定会提高很多
关于网络
标配的千兆网卡10G网卡bond0MySQL服务器尽可能和使用它的web服务器在同一局域网内尽量避免诸如防火墙策略等不必要的开销
2、Mysql主配置文件优化
通常默认的 my.cnf 配置文件无法发挥出 MySQL 最高的性能所以需要根据不同的硬件进行优化配置文件的优化也是重点。下面是物理内存为 32G 的数据库优化参数具体从全局、二进制日志、主从、innodb、myisam 几个方面优化仅供参考
2.1 Mysql全局参数优化
1default-time-zone8:00
默认 MySQL 使用的是系统时区修改为北京时间也就是所说的东八区
2interactive_timeout 120
服务器关闭交互式连接前等待活动的秒数
3wait_timeout 120
服务器关闭非交互连接之前等待活动的秒数
4open_files_limit 10240
MySQL 服务器打开文件句柄数限制
5group_concat_max_len 102400
MySQL 默认的拼接最大长度为 1024 个字节由于 1024 个字节会出现不够用的情况 根据实际情况进行修改
6usermysql
使用 mysql 用户运行
7character-set-serverutf8、init_connectSET NAMES utf8
设置字符集为 utf8
8back_log 600
在 MySQL 暂时停止响应新请求之前短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接则需要增大该参数的值该参数值指定到来的 TCP/IP 连接的监听队列的大小。默认值 50
9max_connections 5000
MySQL 允许最大的进程连接数如果经常出现 Too Many Connections 的错误提示 则需要增大此值
10max_connect_errors 6000
设置每个主机的连接请求异常中断的最大次数。当超过该次数MySQL 服务器将禁止 host 的连接请求直到 MySQL 服务器重启或通过flush hosts 命令清空此host 的相关信息
11table_cache 1024
数据表调整缓冲区大小。它设置表高速缓存的数目。每个连接进来都会至少打开一个表缓存。因此table_cache 的大小与 max_connections 的设置有关。例如对于 200 个并行运行的连接应该让表的缓存至少有 200×N。这里 N 是应用可以执行查询的一个连接中表的最大数量。 此外还需要为临时表和文件保留一些额外的文件描述符。 当 MySQL 访问一个表时 如果该表在缓存中已经被打开则可以直接访问缓存。如果还没有被缓存但是在 MySQL 表缓冲区中还有空间那么这个表就被打开并放入表缓冲区。如果表缓存满了则会按照一定的规则将当前未用的表释放或者临时扩大表缓存来存放使用表缓存的好处是可以更快速地访问表中的内容。执行 flushtables 会清空缓存的内容 一般来说可以通过 showstatus 命令查看数据库运行峰值时间的状态值 Open_tables 和 Opened_tables判断是否需要增加 table_cache 的值其中 open_tables 是当前打开的表的数量Opened_tables 则是已经打开的表的数量。若 open_tables 接近 table_cache 并且 Opened_tables 值在逐步增加 那就要考虑增加这个值的大小了。还有就是Table_locks_waited 比较高的时候也需要增加 table_cache
12table_open_cache 2048
指定表高速缓存的大小。每当MySQL 访问一个表时如果在表缓冲区中还有空间该表就被打开并放入其中这样可以更快地访问表内容。
13max_heap_table_size 256M
这个变量定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变即 set max_heap_table_size#。但是对于已经存在的内存表就没有什么用了除非这个表被重新创建(create table)、修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局 max_heap_table_size 的值
14external-locking false
使用 skip-external-lockingMySQL 选项以避免外部锁定。该选项默认开启
15max_allowed_packet 32M
设置在网络传输中一次消息传输量的最大值。系统默认值为 1MB最大值是 1GB必须设置 1024 的倍数
16sort_buffer_size 512M
Sort_Buffer_Size 是一个 connection 级参数在每个 connectionsession第一次需要使用这个 buffer 的时候一次性分配设置的内存。Sort_Buffer_Size 并不是越大越好由于是 connection 级的参数过大的设置高并发可能会耗尽系统内存资源
17join_buffer_size 8M
用于表间关联缓存的大小和 sort_buffer_size 一样该参数对应的分配内存也是每个连接独享
18thread_cache_size 300
服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中如果线程重新被请求那么请求将从缓存中读取如果缓存中是空的或者是新的请求那么这个线程将被重新创建如果有很多新的线程增加这个值可以改善系统性能。通过比较 Connections 和 Threads_created 状态的变量可以看到这个变量的作用。设置规则如下1GB 内存配置为 82GB 配置为 16 3GB 配置为 324GB 或更高内存可配置更大
19thread_concurrency 8
设置 thread_concurrency 值的正确与否,对 MySQL 的性能影响很大,在多个 CPU(或多核)的情况下 错误设置了 thread_concurrency 的值 会导致 MySQL 不能充分利用多CPU(或多核)出现同一时刻只能一个 CPU 在工作的情况。thread_concurrency 应设为CPU
核数的 2 倍。比如有一个双核的 CPU那么 thread_concurrency 的应该为 42 个双核的 cputhread_concurrency 的值应为 8
20query_cache_size 512M
使用 MySQL 的用户对于这个变量一定不会陌生。前几年的 MyISAM 引擎优化中 这个参数也是一个重要的优化参数。但随着发展这个参数也爆露出来一些问题。机器的内存越来越大人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加大后也引发了一系列问题。首先分析一下 query_cache_size 的工作原理一个 SELECT 查询在 DB 中工作后DB 会把该语句缓存下来。当同样的一个 SQL 再次来到 DB 里调用时DB 在该表没发生变化的情况下把结果从缓存中返回给 Client。这里有一个关建点就是 DB 在利用Query_cache 工作时要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时Query_cache 里的数据又怎么处理呢首先要把 Query_cache 和该表相关的语句全部设置为失效然后再写入更新。那么如果 Query_cache 非常大该表的查询结构又比较多查询语句失效也慢一个更新或是 Insert 就会很慢这样看到的就是 Update 或是Insert 怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统该参数不适合分配过大。而且在高并发写入量大的系统建议把该功能禁掉
21query_cache_limit 4M
指定单个查询能够使用的缓冲区大小缺省为 1M
22query_cache_min_res_unit 2k
默认是 4KB设置值大对大数据查询有好处但如果查询都是小数据查询就容易造成内存碎片和浪费查询缓存碎片率Qcache_free_blocks/Qcache_total_blocks*100%。 如果查询缓存碎片率超过 20%可以用 FLUSHQUERYCACHE 整理缓存碎片或者尝试减小 query_cache_min_res_unit 。 如果查询都是小数据量 那么查询缓存利用率 (query_cache_size–Qcache_free_memory)/query_cache_size*100%。查询缓存利用率在 25%以下说明 query_cache_size 设置的过大可适当减小。查询缓存利用率在 80%以上而且 Qcache_lowmem_prunes50 的话说明 query_cache_size 可能有点小要不就是碎片太多。查询缓存命中率(Qcache_hits–Qcache_inserts)/Qcache_hits*100%
23default-storage-engine innodb
默认引擎现在一般都是 innodb 引擎表居多
24thread_stack 192K
设置 MySQL 每个线程的堆栈大小默认值足够大可满足普通操作。可设置范围为 128K 至 4GB默认为 192KB
25transaction_isolation READ-COMMITTED
设定默认的事务隔离级别READCOMMITTEE 是读已提交
26tmp_table_size 256M
tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小MySQL 产生一个Thetabletbl_nameisfull 形 式 的 错误 如 果 执 行很 多 高 级 GROUPBY 查 询 增 加tmp_table_size 值。如果超过该值则会将临时表写入磁盘
27key_buffer_size 1024M
指定用于索引的缓冲区大小增加它可以得到更好的索引处理性能
28read_buffer_size 2M
MySQL 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区MySQL 会为它分配一段内存缓冲区。read_buffer_size 变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁并且认为频繁扫描进行得太慢可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size 一样该参数对应的分配内存也是每个连接独享
29read_rnd_buffer_size 256M
MySQL 的随机读查询操作缓冲区大小。当按任意顺序读取行时(例如按照排序顺序)将分配一个随机读缓存区。进行排序查询时MySQL 会首先扫描一遍该缓冲以避免磁盘搜索提高查询速度。如果需要排序大量数据可适当调高该值。但 MySQL 会为每个客户连接发放该缓冲空间所以应尽量适当设置该值以避免内存开销过大
30bulk_insert_buffer_size 64M
批量插入数据缓存大小可以有效提高插入效率默认为 8M
31skip-name-resolve
禁止域名解析包括主机名.所以授权的时候要使用 IP 地址
32ft_min_word_len 1
从 MySQL4.0 开始就支持全文索引功能但是 MySQL 默认的最小索引长度是 4。如果是英文默认值是比较合理的但是中文绝大部分词都是 2 个字符这就导致小于 4 个字
的词都不能被索引。MySQL 全文索引是专门为了解决模糊查询提供的可以对整篇文章预先按照词进行索引搜索效率高能够支持百万级的数据检索
2.2 Mysql二进制日志的参数优化
1log-binmysql-bin
打开 MySQL 二进制功能
2binlog_cache_size 4M
在事务过程中容纳二进制日志 SQL 语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin 选项)的前提下为每个客户端分配的内存 注意是每个 Client 都可以分配设置大小的 binlogcache 空间。可以通过 MySQL 的以下两个 状态变量来判 断当前的 binlog_cache_size 的状况 Binlog_cache_use 和Binlog_cache_disk_use
3max_binlog_cache_size 128M
表 示 binlog 能 够使 用的 最大 cache 内 存大 小。 执行 多语 句事 务的 时候 max_binlog_cache_size 如 果 不 够 大 的 话 系 统 可 能 会 报 出“Multi-statementtransactionrequiredmorethanmax_binlog_cache_sizebytesofstorage” 的错误
4max_binlog_size 1G
Binlog 日志最大值一般来说设置为 512M 或者 1G但不能超过 1G。该大小并不能非常严格控制 Binlog 大小尤其是当到达 Binlog 比较靠近尾部而又遇到一个较大事务的时候系统为了保证事务的完整性不可能做切换日志的动作只能将该事务的所有 SQL 都记录进入当前日志直到该事务结束。这一点和Oracle 的Redo 日志有点不一样因为Oracle 的 Redo 日志所记录的是数据文件的物理位置的变化而且里面同时记录了 Redo 和 Undo 相关的信息所以同一个事务是否在一个日志中对 Oracle 来说并不关键。而 MySQL 在Binlog 中所记录的是数据库逻辑变化信息MySQL 称之为 Event实际上就是带来数据库变化的 DML 之类的 Query 语句
5sync_binlog1
在 MySQL 中系统默认的设置是 sync_binlog0也就是不做任何强制性的磁盘刷新指
令这时候的性能是最好的但是风险也是最大的。因为一旦系统 Crash在 binlog_cache 中的所有 binlog 信息都会被丢失。而当设置为“1”的时候最安全但也是性能损耗最大的设置。因为当设置为 1 的时候即使系统 Crash也最多丢失 binlog_cache 中未完成的一个事务对实际数据没有任何实质性影响。从以往经验和相关测试来看对于高并发事务的系统来说“sync_binlog”设置为 0 和设置为 1 的系统写入性能差距可能高达 5 倍甚至更多
6binlog_formatmixed
默认使用 statement 模式基于 SQL 语句的复制另外一种是基于行的复制为提升效率可以将以上两种模式混合使用。一般的复制使用 STATEMENT 模式保存 binlog对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlogMySQL 会根据执行的SQL 语句选择日志保存方式
7expire_logs_days 7
二进制日志只留存最近 7 天不用人工手动删除
8log-slave-updates
这条参数只读主从架构适用当从库 log_slave_updates 参数没有开启时从库的 binlog 不会记录来源于主库的操作记录。只有开启 log_slave_updates从库 binlog 才会记录主库同步的操作日志
9slow_query_log
打开慢查询日志
10slow_query_log_fileslow.log
慢查询日志文件位置
11long_query_time 2
记录超过 2 秒的 SQL 查询
2.3 Mysql关于innodb引擎的参数优化
1innodb_additional_mem_pool_size 64M
这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小类似于 Oracle 的 library cache。这不是一个强制参数可以被突破
2innodb_buffer_pool_size 20480M
用于缓存索引和数据的内存大小这个选项的值越多越好 数据读写在内存中非常快 减少了对磁盘的读写。当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。 然而内存还有操作系统或数据库其他进程使用推荐设置 innodb-buffer-pool-size 为服务器总可用内存的 75%。 若设置不当 内存使用可能浪费或者使用过多。 对于繁忙的服务器 buffer pool 将划分为多个实例以提高系统并发性 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响当然影响较小
3innodb_data_file_path ibdata1:1024M:autoextend
用 来 指 定 innodb tablespace 文 件 如 果 我 们 不 在 my.cnf 文 件 中 指 定innodb_data_home_dir 和innodb_data_file_path 那么默认会在datadir 目录下创建ibdata1 作为 innodb tablespace
4innodb_file_io_threads 4
文件 IO 的线程数一般为 4但是在 Windows 下可以设置得较大
5innodb_thread_concurrency 8
服务器有几个 CPU 就设置为几建议用默认设置一般为 8
6innodb_write_io_threads 8
InnoDB 使用后台线程处理数据页上写 I/O输入输出请求的数量。一般设置为 CPU 核数比如 CPU 是 2 颗 8 核的可以设置为 8
7innodb_read_io_threads 8
InnoDB 使用后台线程处理数据页上读 I/O输入输出请求的数量。一般设置为 CPU 核数比如 CPU 是 2 颗 8 核的可以设置为 8
8innodb_flush_log_at_trx_commit 2
如果将此参数设置为 1将在每次提交事务后将日志写入磁盘。为提高性能可以设置为 0 或 2但要承担在发生故障时丢失数据的风险。设置为 0 表示事务日志写入日志文件 而日志文件每秒刷新到磁盘一次。设置为 2 表示事务日志将在提交时写入日志但日志文件每次刷新到磁盘一次
9innodb_log_buffer_size 16M
此参数确定日志文件所用的内存大小以 M 为单位。缓冲区更大能提高性能但意外
的故障将会丢失数据。MySQL 开发人员建议设置为 18M 之间
10innodb_log_file_size 256M
此参数确定数据日志文件的大小以 M 为单位较大的值可以提高性能但也会增加恢复故障数据库所需的时间
11innodb_log_files_in_group 3
为提高性能MySQL 可以以循环方式将日志文件写到多个文件
12innodb_file_per_table 1
独享表空间关闭
13innodb_max_dirty_pages_pct 90
Buffer_Pool 中 Dirty_Page 所占的数量 直接影响 InnoDB 的关闭时间。 参数innodb_max_dirty_pages_pct 可以直接控制了 Dirty_Page 在 Buffer_Pool 中所占的比率 而且幸运的是 innodb_max_dirty_pages_pct 是可以动态改变的。所以在关闭 InnoDB 之前先将 innodb_max_dirty_pages_pct 调小强制数据块 Flush 一段时间就能够大大缩短MySQL 关闭的时间
14innodb_lock_wait_timeout 120
InnoDB 有其内置的死锁检测机制能导致未完成的事务回滚。但是如果结合 InnoDB 使用 MyISAM 的 locktables 语句或第三方事务引擎,InnoDB 就无法识别死锁。为消除这种可能性可以将 innodb_lock_wait_timeout 设置为一个整数值设置 MySQL 在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)
15innodb_open_files 8192 innodb
打开文件句柄数
2.4 Mysql关于myisam引擎的参数优化
1myisam_sort_buffer_size 128M
MyISAM 表发生变化时重新排序所需的缓冲大小
2myisam_max_sort_file_size 10G
MySQL 重建索引时所允许的最大临时文件的大小(当 REPAIRALTERTABLE 或者
LOADDATAINFILE)。如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)
3myisam_repair_threads 1
如果一个表拥有超过一个索引MyISAM 可以通过并行排序使用超过一个线程去修复。这对于拥有多个 CPU 以及大量内存情况的用户是一个很好的选择
4myisam_recover
自动检查和修复没有适当关闭的 MyISAM 表
3、数据库设计与规划架构上的优化)
纵向拆解: 专机专用
例现在公司一台服务器同时负责 web、ftp、数据库等多个角色
R720 dell 内存 768G 纵向拆解后就是数据库服务器专机专用避免额外的服务可能导致的性能下降和不稳定性 解决方法
横向拆解: 主从同步、负载均衡、高可用性集群当单个 MySQL 数据库无法满足日益增加的需求时可以考虑在数据库这个逻辑层面增加多台服务器以达到稳定、高效的效果
4、数据查询优化
建表时表结构要合理每个表不宜过大在任何情况下均应使用最精确的类型。例如如果ID列用int是一个好主意 而用text类型则是个蠢办法TIME列酌情使用DATE或者DATETIME索引建立合适的索引加快对表中记录的查询查询时尽量减少逻辑运算与运算、或运算、大于小于某值的运算减少不当的查询语句不要查询应用中不需要的列比如说 select * from 等操作减小事务包的大小将多个小的查询适当合并成一个大的查询减少每次建立/关闭查询时的开销将某些过于复杂的查询拆解成多个小查询和上一条恰好相反建立和优化存储过程来代替大量的外部程序交互建立视图简化查询结果集、灵活查询可针对不同用户呈现不同结果集
5、对查询进行缓存
大多数LNMP应用都严重依赖于数据库查询查询的大致过程如下: PHP发出查询请求---数据库收到指令对查询语句进行分析---确定如何查询---从磁盘中加载信息---返回结果 如果反复查询就反复执行这些。MySQL 有一个特性称为查询缓存他可以将查询的结果保存在内存中在很多情况下这会极大地提高性能。不过问题是查询缓存在默认情况下是禁用的
5.1 启动查询缓存
[rootlocalhost ~]#vim /etc/my.cnf
[mysqld]
query_cache_size 32M #添加此字段参数
[rootlocalhost ~]#systemctl restart mysqld
5.2 查看查询缓存
[rootlocalhost ~]#mysql -u root -p1 #登录到数据库中
mysql show status like qcache%; 查看查询缓存 变量名说明Qcache_free_blocks缓存中相邻内存块的个数数目大说明可能有碎片。 如果数目比较大可以执行 flush query cache;
#对缓存中的碎片进行整理从而得到一个空闲块 Qcache_free_memory缓存中的空闲内存大小Qcache_hits每次查询在缓存中命中时就增大Qcache_inserts每次插入一个查询时就增大。即没有从缓存中找到数据Qcache_lowmem_prunes 因内存不足删除缓存次数缓存出现内存不足并且必须要进行清理,以便为更多查 询提供空间的次数。返个数字最好长时间来看如果返个数字在不断增长就表示可能碎片非常严重或者缓存内存很少 如果Qcache_free_blocks比较大说明碎片严重。 如果 free_memory 很小说明缓存不够用了 Qcache_not_cached没有进行缓存的查询的数量通常是这些查询未被缓存或其类型不允许被缓存Qcache_queries_in_cache在当前缓存的查询和响应的数量Qcache_total_blocks缓存中块的数量
5.3 使用查询缓存
修改配置文件添加query_cache_size 32M
[rootlocalhost ~]#cat /etc/my.cnf
[mysqld]
datadir/var/lib/mysql
socket/var/lib/mysql/mysql.sock
usermysql
#Disabling symbolic-links is recommended to prevent assorted security risk
symbolic-links0
query cache size 32m
#至少 4M 以存储数据结构可扩展。整体100G若此服务器只运行 MySQL 服务器70-80G给mysql
[rootlocalhost ~]#systemctl restart mysqld #重启服务 创建库和表并插入数据查询数据生成缓存最后查看缓存内容
mysql create database aa;
mysql use aa;
mysql create table test3 (id int, name varchar(255));
mysql insert into test3 values (1,aaaa), (2,aaaa);
mysql select * from test3;
mysql show status like qcache%; 再次查询
mysql select * from test3;
mysql show status like qcache%; 经过多次select命中的次数也会增加
mysql select * from test3;
mysql show status like qcache%; 6、分库分表优化 6.1 什么是分库分表 MySQL数据库的分库分表是一种数据库水平拆分的技术用于解决单一数据库在处理大量数据时性能瓶颈的问题。在分库分表中数据表根据一定的规则被分散存储在多个数据库实例或数据表中以提高数据库的扩展性和性能 分库指的是将一个数据库中的数据按照一定的规则分散存储到多个数据库实例中每个数据库实例负责存储部分数据。这样可以减轻单一数据库的压力提高数据库的读写效率。 分表指的是将一个大表按照一定的规则拆分成多个小表每个小表存储部分数据。这样可以减少单个数据表的数据量提高查询和更新的效率。 通过分库分表可以将数据分散存储在多个数据库实例和数据表中从而提高数据库的并发处理能力和横向扩展能力降低单一数据库的负载压力提高系统的性能和扩展性 6.2 为什么要分库分表 数据库中的数据量不一定是可控的在未进行分库分表的情况下随着时间和业务的发展库中的表会越来越多表中的数据量也会越来越大相应地数据操作增、删、改、查的开销也会越来越大另外一台服务器的资源CPU、磁盘、 内存、IO等是有限的最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈 分库分表是一种常见的数据库优化策略通常用于处理大量数据和高并发的情况。以下是一些分库分表的主要优点 提高性能将数据分散存储在多个数据库实例和表中可以减轻单个数据库实例或表的负担提高查询性能和并发处理能力。通过分库分表可以有效减少单个库表的数据量加快数据的检索速度 水平扩展通过分库分表可以方便地进行水平扩展即在需要时增加更多的数据库实例和表来应对增长的数据量和请求。这种扩展方式相比垂直扩展更加灵活和可控 提高可用性通过分布式存储数据可以提高系统的可用性和容错能力。即使某个数据库实例或表发生故障其他数据库实例或表仍然可以正常工作从而保证系统的稳定性 更好的维护性分库分表可以使数据更加模块化和分离便于管理和维护。例如可以针对不同的业务需求对不同的数据库实例或表进行备份、优化和维护减少对整个系统的影响 6.3 分库分表的实施策略 如果你的单机性能很低了那可以尝试分库。分库业务透明在物理实现上分成多个服务器不同的分库在不同服务器上。分区可以把表分到不同的硬盘上但不能分配到不同服务器上。一台机器的性能是有限制的用分库可以解决单台服务器性能不够或者成本过高问题 使用MySQL的分区表功能将单个表的数据按照一定的规则分成多个分区存储但当分区之后表还是很大处理不过来这时候可以用分库。orderid,userid,ordertime,..... userid%40用分库 1 userid%41用分库 2 userid%42, 用分库 3 userid%43用分库 4 上面这个就是一个简单的 分库路由根据 userid 选择分库即不同的服务器 6.4 分库分表存在的问题 事务问题 在执行分库分表之后由于数据存储到了不同的库上数据库事务管理出现了困难。如果依赖数据库本身的分布式事务 管理功能去执行事务将付出高昂的性能代价如果由应用程序去协助控制形成程序逻辑上的事务又会造成编程方面的负担 跨库跨表的join 问题 在执行了分库分表之后难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上这时表的关联操作 将受到限制我们无法 join 位于不同分库的表也无法 join 分表粒度不同的表结果原本一次查询能够完成的业务可能需要多次查询才能完成 额外的数据管理负担和数据运算压力 额外的数据管理负担最显而易见的就是数据的定位问题和数据的增、删、改、查的重复执行问题这些都可以通过应用程序解决但必然引起额外的逻辑运算例如对于一个记录用户成绩的用户数据表userTable业务要求查出成绩最好的 100 位在进行分表之前只需一个 order by 语句就可以搞定但是在进行分表之后将需要 n 个 order by 语句分别查出每一个分表的前 100 名用户数据然后再对这些数据进行合并计算才能得出结果
