MySQL数据库优化

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. 某些存储阵列顺序读写效率高，但是随机读写不如人意。