MySQL性能优化笔记

数据库优化概览

参考视频：慕课网-性能优化之MySQL优化

1.SQL以及索引

2.数据库表结构

3.系统配置

4.硬件

成本自上往下变高，效果自下往上变好

工具

• mysqldumpslow

• pt-query-digest

SQL语句优化

explain关键字

重要的

• type : 好=>差 const > eq_reg > ref > range > index > ALL
• extra:
  1. Using filesort: 需要优化
  2. Using temporary: 需要优化

MAX()和COUNT()的优化

1. 对max()查询，可以为表创建索引，例如
     select max(payment_date) from payment;
     可以对payment建立索引 create index idx_paydate on payment(payment_date);
     然后在进行查询
   

2. count()对多个关键字进行查询，比如在一条SQL中同时查出2006年和2007年电影的数量，语句：
   select count(release_year='2006' or null) as '2006年电影数量',
          count(release_year='2007' or null) as '2007年电影数量'
   from film;
   这里解释一下为什么要加'or null'：
   count()函数只有count(NULL)是才不计数，即count(NULL)=0；
   而'release_year='2006''是个比较运算符，结果为1或者0，不管是不是2006 count函数都会计数。
   当加上or null以后，当值不为2006，release_year='2006'的结果为0, '0 or null'结果为null，这样就排除了2006以外的结果
   

3. count(*) 查询的结果中，包含了该列值为null的结果
   

子查询的优化

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

例子：

select * from t where t.id in (select t1.tid from t1)

优化后：

select distinct t.id from t join t1 on t.id=t1.tid;

group by查询优化

例子：

优化前：

select actor.first_name,actor.last_name,count(*)
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.记录上次返回的主键，在下次查询时使用主键过滤

例子：

优化前：

select film_id,description from sakila.film order by film_id limit 50,5;

优化后：

select film_id,description from sakila.film where file_id >55 and film_id<=60 order by film_id limit 1,5;

使用这种方式有一个限制，就是主键一定要顺序排序和连续的，如果主键出现空缺可能会导致最终页面上显示的列表不足5条，解决办法是附加一列，保证这一列是自增的并增加索引就可以了

索引优化

1. 选择合适的索引列

   选择合适的索引列
   1.在where，group by，order by，on从句中出现的列
   2.索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好)
   3.离散度高得列放在联合索引前面
   select count(distinct customer_id), count(distinct staff_id) from payment;
   查看离散度 通过统计不同的列值来实现 count越大 离散程度越高 唯一值越多
   
   过多的索引不但影响写入，而且影响查询，索引越多，分析越慢
   如何找到重复和多余的索引，主键已经是索引了，所以primay key 的主键不用再设置unique唯一索引了
   冗余索引，是指多个索引的前缀列相同(或者说在联合索引中包含了主键的索引)，innodb会在每个索引后面自动加上主键信息
   
   冗余索引查询工具
   pt-duplicate-key-checker
   
   查找重复索引
   SELECT a.TABLE_SCHEMA AS '数据名'
   	,a.TABLE_NAME AS '表名'
   	,a.INDEX_NAME AS '索引1'
   	,b.INDEX_NAME AS '索引2'
   	,a.COLUMN_NAME AS '重复列名'
   FROM STATISTICS a 
   	JOIN STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
   						AND a.TABLE_NAME = b.TABLE_NAME
   						AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
   						AND a.COLUMN_NAME = b.COLUMN_NAME
   WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME
   

2. 删除不用索引

   mysql中只能通过分析慢查日志配置pt-index-usage工具进行索引使用情况的分析,看哪些索引不常用,从而删除;
   
   pt-index-usage -uroot -p' ' mysql-slow.log
   

数据库结构优化

1. 选择合适的数据类型

   1.使用可存下数据的最小的数据类型
   
   2.使用简单地数据类型，Int要比varchar类型在mysql处理上更简单
   
   3.尽可能使用not null定义字段，这是由innodb的特性决定的，因为非not null的数据可能需要一些额外的字段进行存储，这样就会增加一些IO。可以对非null的字段设置一个默认值
   
   4.尽量少用text，非用不可最好分表，将text字段存放到另一张表中，在需要的时候再使用联合查询，这样可提高查询主表的效率
   
   例子1、用Int存储日期时间
   from_unixtime()可将Int类型的时间戳转换为时间格式
   select from_unixtime(1392178320); 输出为 2014-02-12 12:12:00
   unix_timestamp()可将时间格式转换为Int类型
   select unix_timestamp('2014-02-12 12:12:00'); 输出为1392178320
   
   例子2
   存储IP地址——bigInt
   利用inet_aton(),inet_ntoa()转换
   select inet_aton('192.169.1.1'); 输出为3232301313
   select inet_ntoa(3232301313); 输出为192.169.1.1
   

2. 表的范式化和反范式化

   1.表的范式化
   表的范式化即数据库设计的规范化：数据表不存在非关键字段对任意关键字段的传递函数依赖，则符合第三范式。
   可以将一张数据表进行拆分，来满足第三范式的要求。
   设计表的时候符合范式化是为了：减少数据冗余、减少表的插入、更新、删除异常
   设计表的时候使用反范式化是为了：以空间换时间、增强代码的可编程性和可维护性
   不符合第三范式要求的表存在以下问题：
   1.数据冗余:（分类、分类描述）对于每一个商品都会进行记录
   2.数据插入异常
   3.数据更新异常
   4.数据删除异常
   
   
   
   2.表的反范式化
   符合第三范式基础上适当增加冗余,已达到查询优化的目的;
   

3. 表的垂直拆分(拆字段)

   表的垂直拆分的原则
   所谓垂直拆分，就是把原来一个有很多列的表拆分成多个表解决表的宽度问题，通常拆分原则如下：
   1、把不常用的字段单独存放到一个表中
   2、把大字段独立存放到一个表中
   3、把经常一起使用的字段放到一起
   当表的宽度过宽的时候，我们需要对表进行垂直拆分，具体的建议如下（原则上是人以群分，列以表分）：一表变多表，物理上不在一起，逻辑上是在一起的！
   

4. 表的水平拆分(拆数据)

   常用的水平拆分方法为：
   1.对 customer id 进行 hash 运算，如果要拆分成 5 个表则使用 mod(customer_id,5) 取出 0-4 个值
   2.针对不同的 hashID 把数据存到不同的表中。
   
   挑战：
   
   1.跨分区表进行数据查询
   2.统计及后台报表操作
   

数据库系统配置优化

1. 操作系统配置优化

   数据库是基于操作系统的
   
   1. 网络方面，修改/etc/sysctl.conf文件，增加tcp支持的队列数，减少断开连接时，资源的回收。
   #增加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
   
   2. 打开文件数的限制。修改/etc/security/limits.conf文件，增加一下内容以修改打开文件数量的限制。
   *soft nofile 65535
   *hard nofile 65535
   
   关闭iptables，selinux等防火墙软件。
   

2. MySQL配置文件

   Linux大多数情况下位于 /etc/my.cnf 或是/etc/mysql/my.cnf
   查找配置文件方法
   /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
   
   windows C:/windows/my.ini
   
   
   #重要，缓冲池的大小 推荐总内存量的75%，越大越好。
   innodb_buffer_pool_size
   
   #默认只有一个缓冲池，如果一个缓冲池中并发量过大，容易阻塞，此时可以分为多个缓冲池；
   innodb_buffer_pool_instances
   
   #log缓冲的大小，一般最常1s就会刷新一次，故不用太大；
   innodb_log_buffer_size
   
   #重要，对io效率影响较大。0:1s刷新一次到磁盘；1：每次提交都会刷新到磁盘；2：每次提交刷新到缓冲区，1s刷新到磁盘；建议为2，默认为1。
   innodb_flush_log_at_trx_commit
   
   #读写的io进程数量，默认为4
   innodb_read_io_threads
   innodb_write_io_threads
   
   #重要，控制每个表使用独立的表空间，默认为OFF，即所有表建立在一个共享的表空间中。
   innodb_file_per_table
   
   #mysql在什么情况下会刷新表的统计信息，一般为OFF。
   innodb_stats_on_metadata
   

服务器硬件优化

1.cpu选择:
mysql一个sql的执行只能用到单核的cpu,其次,在复制进程的时候也是只能用到单核的cpu,所以cpu并不是越多越好,mysql5.5是的服务器不要超过32核,偏向选择单核频率更快的cpu;

2.Disk IO 优化

常用的RAID 级别简介

    RAID0: 也称为条带,多个磁盘接成一个使用,io最好(但是磁盘坏了,数据没了,安全性差)

    RAID1: 也称镜像,要求至少两个磁盘,每组磁盘存储的数据相同;

    RAID1+0:  就是RAID1和RAID0的结合,同时具备两个级别的优缺点,推荐数据库使用这个级别;

3.数据库随机读写比率很高，考虑使用随机读写效率高的存储设备。