MySQL数据库优化

时间：2022-09-19 12:49:17

MySQL数据库优化

数据库优化的目的：
避免出现页面访问错误
· 由于数据库连接tomeout产生页面5xx错误（服务器内部错误，由web、中间件、数据库等引起）
· 由于慢查询造成页面无法加载（web及数据库的慢速查询到时页面无法加载，避免慢速查询和事物阻塞）
· 由于阻塞造成数据无法提交（服务器内部锁的原因，在大并发更新某一个字段时产生阻塞，轻则影响服务器性能，数据库中有锁超时，阻塞超过一定的时间，事物就会被回滚，影响到业务，及收入）
增加数据库的稳定性
• 很多数据库问题都是由于低效的查询引起的
优化用户体验
• 流畅页面的访问速度
• 良好的网站功能体验

优化方面：

3 系统配置（linux对mysql打开文件数的限制等）
4 硬件（提高文件io的速度，大内存，但是成本最高）
io的优化并不能减少阻塞  ，但sql索引没有优化好，则会产生大量的慢查询或阻塞，这是由于mysql的内部锁机制造成，所以硬件再好效果也不大
mysql对内核是有限制的

1、SQL及索引优化（提升性能最大，效率最高）

结构良好的SQL（选择最优的SQL）、
有效索引（索引越多不但会造成写操作的效率下降、而且也会造成读操作效率下降）

2、数据库表结构（满足范式、考虑到查询语句的写法）

（根据数据库设计范式，设计出简洁明了的表结构、减少数据的冗余；
a、在设计表结构时候要想到怎么样对这个表数据进行查询
b、怎么样设计表结构才是有益于SQL写法的
SQL及索引的优化也是日常工作中所涉及到的最多的一种优化方式
3、系统配置
大多数情况下我们的mysql都是跑在linux上的，系统本身也是会有些限制：
a、tcp/ip连接数的限制
b、打开文件数限制（重点）
mysql都是基于文件的，每查询一个表时，需要打开一些文件，
一旦打开的文件数超过上限，文件就会无法打开、就会平分IO操作
c、安全性限制
4、 硬件优化
内存：越大越好、mysql查询修改都是load在内存中进行的。
CPU：并不是越多，性能提升就越好的、mysql会对cpu核数进行限制、甚至有些查询只会用到单核
硬盘：会影响IO，可以考虑换用SSD，固态硬盘等等
所以这种IO设备对数据库肯定是有良好的影响的，但是这只是表面、并不能解决mysql内部锁的问题
lock锁是保证数据完整性的一种机制, 虽然IO很快并不能解决阻塞，所以说硬件优化、其实是成本最高，效果最不明显的

如果没有良好的SQL及有效的索引，数据库查询造成大量的慢查询、大量的阻塞，随之并发量就会上去、并发量一上去loading
就会高，会造成应用缓慢

查看mysql是否开启慢查日志
show variables like 'slow_query_log';是否开启满查询日志
show variables like '%log%';  查询设置
show variables like '%slow%';   查询慢查询日志所在位置
tail -50 /mydata/mysql/data/localhost-slow.log; 查看慢查询日志

set global slow_query_log_file = '/home/mysql/sql_log/mysql-slow.log'   指定慢查询日志的位置
set global log_queries_not_using_indexes=on;    是否要把没有索引的sql记录到慢查询日志中
show variables like 'long_query_time';查看设置的超时时间
set global long_query_time=0.01;   把超过多少秒的sql记录到慢查询日志中
set global slow_query_log=on; 开启慢查日志

在优化过程中主要是优化表索引以及查询所使用的方式，所以需要把set global log_queries_not_using_indexes变量设置为on，以备后面来进行优化
慢查询日志所包含的内容

# Time: 2016-10-28T15:59:22.416695Z 解释:慢查询执行的时间点
# User@Host: root[root] @ localhost []  
解释:执行SQL的主机信息 哪个用户在哪个主机执行
# Query_time: 0.003073  Lock_time: 0.001320 Rows_sent: 2  Rows_examined: 2  
解释:SQL执行的信息，Query_time指查询所有时间，Lock_time指锁定时间，Rows_sent发送的行数，Rows_examined扫描的行数
SET timestamp=1477670362;   解释:以时间戳的形式记录了此SQL执行的时间
select * from store limit 10;   解释:SQL的具体内容

用mysql官方提供的日志分析工具查看TOP3慢日志
mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log | more

慢查询分析工具2：pt-query-digest
输出到文件：pt-query-digest show-log > slow_log.report
输出到数据库表 pt-query-digest show.log -review \
h=127.0.0.1,D=test,p=root.P=3306,u=root,t=query_review \
--create-reviewtable \
--review-history t=hostname_show
pt-query-digest慢查询日志的输出有三部分
第一部分:
显示了日志的时间范围，以及总的SQL数量和不同的SQL数量
第二部分:
一些表的统计信息
Response time:响应时间占比
Calls : sql执行次数
第三部分:具体的SQL是什么

解决: 

1.查询次数多且每次查询占用时间长的SQL
  通常为pt-query-digest分析的前几个查询
2.IO大的SQL
  注意pt-query-digest分析中的Rows examine项
3.未命中索引的SQL
  注意pt-query-digest分析中的Rows examine和Row send 的对比

explain分析和返回各列的含义

table :显示这一行的数据是哪张表的
type:这是重要的列， 显示连接使用了何种类型。从最好到最差的连接类型为
     const  常数查找 唯一索引或主键查找
     eq_reg范围查找 唯一索引主键范围查找等
    ref   连接查找 一个表是基于某一个表的查找
    range  基于索引的范围查找
     index  基于索引的扫描
     ALL    全表扫描
possible_keys:显示可能应用在这张表中的索引。如果为空，没有可能的索引
key:实际使用的索引。如果为NULL，则没有使用索引
key_len:使用索引的长度。在不损失精确性的情况下，长度越短越好
ref:显示索引的哪一列被使用了，如果可能的话，是一个常数
rows:MySQL认为必须检查的用来请求数据的的行数
Extra:扩展列 需要注意的返回值 

如果extra列的返回值为Using filesort或Using temporary则查询需要优化,一般都出现在ORDER BY 或是GROUP BY上.

子查询优化

通常情况下，需要把子查询优化为join查询，但是优化时要注意关联键是否有一对多关系，要注意重复数据。

group by查询

如果涉及到表联接的过程中有group by这样的语句,可以先通过group by做为子查询，统计出结果后，再与其它表进行关联查询。
group by可能会出现临时表（Using temporary），文件排序（Using filesort）等，影响效率。
可以通过关联的子查询，来避免产生临时表和文件排序，可以节省io<br>
改写前
select actor.first_name,actor.last_name,count(*)
注意：在数据库表中针对索引的查询往往比较快，所以，我们的查询或对于SQL语句优化要想到怎么才能利用上索引才好！
经验丰富了才能更好的判断哪一种方式比较好，关键是思想要灵活要变通要学习，要有判断力要能把握住机会！
from sakila.film_actor
inner join sakila.actor using(actor_id)
group by film_actor.actor_id;
改写后
select actor.first_name,actor.last_name,c.cnt
from sakila.actor inner join(
select actor_id,count(*) as cnt from sakila.film_actor group by
actor_id
)as c using(actor_id);
limit优化
limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用Filesorts这样会造成大量的io问题1.使用有索引的列或主键进行order by操作2.记录上次返回的主键，在下次查询时使用主键过滤使用这种方式有一个限制，就是主键一定要顺序排序和连续的，如果主键出现空缺可能会导致最终页面上显示的列表不足5条，解决办法是附加一列，保证这一列是自增的并增加索引就可以了
选择合适的索引列
1.在where，group by，order by，on从句中出现的列2.索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )3.离散度大得列放在联合索引前面离散度查询，唯一值越多离散度越好：select count(distinct customer_id), count(distinct staff_id) from payment;
使用pt-duplicate-key-checker工具检查重复及冗余索引
冗余索引：多个索引的前缀列相同，或者是在联合索引中包含了主键索引
pt-duplicate-key-checker \
-uroot \
-p '' \
-h 127.0.0.1
通过慢查询日志配合pt-index-usage来删除不用索引：pt-index-usage -uroot -p '' mysql-slow.log

数据库表结构设计，
1.选择合适的数据类型,重点在于合适二字
1.使用可以存下你的数据的最小的数据类型
2.使用简单的数据类型，int要比varchar类型在mysql处理上简单。
3.尽可能的使用not null定义字段
4.尽量少用text类型，非用不可时最好考虑分表

.使用int来存储日起时间，利用from_unixtime()，unix_timestamp()两个函数来进行转换
from_unixtime() 将int转换为时间格式  unix_timestamp()时间格式转换为int。.使用bigint来存储IP地址，利用inet_aton(),inet_ntoa()两个函数转换。

2.大数据字段可以考虑分表,或做附加表。
利用Mysql内置函数对日期或者ip地址等进行转换.存储最小数据类型到数据库中.范式和反范式化的操作,
数据库结构优化－表的范式化和反范式化
范式化是指数据库设计的规范，目前说道范式化一般是指第三设计范式，也就是要求数据库表中不存在非关键字段对任意候选关键字段的传递函数依赖
则符合第三范式

商品名称    价格   重量   有效期   分类    
－－－－－－－－－－－－－－－－－－－－－－－－－－－－
可乐        3     250ml  201406  饮料    酸性饮料

存在以下传递函数依赖关系
商品名称->分类->分类描述
也就是说存在非关键字段 分类描述对关键字段商品名称的传递函数的依赖

不符合第三范式存在下面问题：
1.数据冗余
2.数据插入异常
3.数据更新异常
4.数据删除异常
不符合第三范式的设计  需要对表拆分来解决

反范式化： 增加冗余信息，少关联表，以空间换时间

表的垂直拆分
(解决表宽度问题，将部分列提取出一个新表)
所谓垂直拆分，就是把原来一个有很多列的表拆分成多个表解决表的宽度问题，通常拆分原则如下：1、把不常用的字段单独存放到一个表中2、把大字段独立存放到一个表中3、把经常一起使用的字段放到一起
当表的宽度过宽的时候，我们需要对表进行垂直拆分，具体的建议如下（原则上是人以群分，列以表分）：
一表变多表，物理上不在一起，逻辑上是在一起的！
水平拆分
(解决表数据量的问题,前后台分开查询，通过汇总表来解决后台用户的查询统计一般时效要求不高).
为了解决单表数据量过大的问题，每个水平拆分表的结构完全一致
方法
1.对id进行hash运算，可以取mod
2.针对不同的hashId把数据放到不同的表中
水平拆分之后的挑战
1.跨分区进行数据查询
2.统计及后台报表操作
前后台使用的表进行分开，前台要求查询效率，所以可以说会用拆分之后的表，后台在统计数据时可以使用汇总表。

操作系统配置优化

 数据库是基于操作系统的，目前大多数的MySQL都是安装在linux系统之上，
所以对于操作系统的一些参数配置也影响到MySQL的性能，下面就列出一些常用到
的系统配置。

网络方面的配置，要修改/etc/sysctl.conf文件

#增加tcp支持的队列书
net.ipv4.tcp_max_syn_backlog=65535
#减少断开连接时，资源的回收
net.ipv4.tcp_max_tw_buckets=8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=10  //timewait

打开文件数的限制，可以使用ulimit -a 查看目录的各位限制，可以修改
/etc/security/limits.conf文件，增加以下内容以修改打开文件数量的限制
*soft nofile 65535
*hard nofile 65535
除此之外最好在MySQL服务器上关闭iptables,selinux等防火墙软件。

MySQL配置优化


innodb_buffer_pool_size
非常重要的参数，用于配置innodb的缓冲池如果数据库中只有innodb表，
则推荐配置量为总内存的75%

下面是检测语句

select engine,round(sum(data_ength + index_length)/1024/1024,1) as 'Total MB',
from information_schema.tables where table_schema not in ('information_schema',
'performance_schema') group by engine;

innodb_buffer_pool_size >= Total MB


innodb_buffer_pool_instances

MySQL5.5中新增参数，可以控制缓冲池的个数，默认情况下只有一个

innodb_log_buffer_size
innodb log缓冲的大小，由于日志最长每秒钟就会刷新所以一般不用太大

innodb_flush_log_at_trx_commit
关键参数，对innodb的IO效率影响比较大，默认值为1，可以取0，1，2三个值，
一般建议设为2，但如果数据安全性要求比较高则使用默认值1.

innodb_read_io_threads
innodb_write_io_threads
以上两个参数决定了innodb读写IO进程数，默认4

innodb_file_per_table
关键参数，控制innodb中每一个表使用独立的表空间，默认为OFF，也就是所有表
都会建立在共享表空间。

innodb_stats_on_metadata
决定了MySQL在什么情况下会刷新innodb表的统计信息

mysql第三方配置工具:
https://tools.percona.com/wizard

硬件优化：

CPU： 单核频率高，不要超过32核。
IO： raid0 多个磁盘连接成一个硬盘使用，IO最好
raid1 单个备份
raid5 奇偶校验恢复
raid1+0 1和0的结合。

磁盘阵列： 1. 常用于高可用 2. 某些存储阵列顺序读写效率高，但是随机读写不如人意。


标签：

相关文章