关于数据库调优的总结（程序员必备常识）

前言：之前小编在工作之余在慕课网上学习数据库调优时所做的一些笔记，虽然实际工作中基本没用到过，但是还是想整理一下，贴出来给大家分享，要是有什么总结的不到位的地方还望多多批评。

---

关于数据库调优你要懂得的常识

数据库优化的目的： 

1、避免出现页面访问的错误 ,避免出现访问页面发生5XX错误,避免由于慢查询造成页面无法加载 避免由于阻塞造成数据无法提交。

2、增加数据库的稳定性，避免低效查询导致数据库问题，优化用户体验，流畅页面的访问速度，良好的网站功能体验。

在哪几个方面进行数据库优化？ 

硬件； 系统配置； 数据库表结构； sql及索引优化（最重要的）。

show tables（展示表）

如何发现有问题的sql？

使用mysql的慢查日志对有效率问题的sql进行监控；

查看是否开启了慢查询日志：

mysql> show variables like 'slowquerylog'; 

结果：
+----------------+-------+ | Variable_name | Value | +----------------+-------+ | slowquerylog | ON | +----------------+-------+ 1 row in set

 

查看日志的查询状态： show variables like '%log%';

将未使用索引的记录设置为on； set global logqueriesnotusingindexes=on;

把超过一定时间的查询也记录到慢查询日志中： show variables like 'longquerytime';

把慢查询日志设置为开启状态： set global slowquerylog=on;

查看数据可中有哪些表： show tables;

指定看哪个数据库： use 数据库名;

查看自己的查询记录在的日志位置： show variables like 'slow%';

sql慢查询日志所包含的内容： 执行sql的主机信息；sql的执行信息；sql执行时间；sql的内容。

mysql慢查日志的分析工具：mysqldumpslow mysqldumpslow -t 3 日志位置 |more（分析慢查日志的前三条数据）

mysql慢查询日志工具：pt-query-digest

如何通过慢查日志的结果来发现有问题的sql？ 

1、查询次数多且每次查询占用时间长的sql 通常为pt-query-digest分析的前几个查询 

2、IO大的sql 注意pt-query-digest分析中的Rows examine项 

3、未命中索引的sql 注意pt-query-digest分析中Rows examine和Rows Send的对比

 

通过expain查询sql的执行计划：

explain返回各列的含义：

table:显示这一行的数据时关于哪张表的

type：这是最重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg-范围查找、ref-连接的查询中、range、index和All-表扫描

possible_keys:显示可能应用在这张表上的索引。如果为null，没有可能的索引。

key：实际使用的索引。如果为null，则没有使用索引。

key_len:使用的索引的长度。在不损失精确度的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。

rows：mysql认为必须检查的用来返回请求数据的行数。

extra列需要注意的返回值： Using filesort：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。 Using temporary:看到这个的时候，查询就需要优化了。在这里，mysql需要临时创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by上。

max和count的优化

explain select max(create_time) from t_user;//查看查询时间时的索引命中情况，rows不能太高

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | selecttype | table | partitions | type | possiblekeys | key | keylen | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tuser | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100 | NULL | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set create index idxcreatetime on tuser(createtime);//创建索引，对于max的优化方式

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | id | selecttype | table | partitions | type | possiblekeys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------select count(create_by='admin' or null) as 'admin的数量',count(create_by='yangfan' or null) as 'yangfan的数量' from t_user;//关于count的优化方法+-------------+---------------+ | admin的数量 | yangfan的数量 | +-------------+---------------+ | 2 | 1 | +-------------+---------------+

子查询的优化

mysql> select t.id from t join t1 on t.id = t1.tid;
 +----+ | id | +----+ | 1 | | 1 | +----+ 2 rows in set

mysql> select t.id from t where t.id in (select t1.tid from t1);

+----+ | id | +----+ | 1 | +----+ 1 row in set 把子查询优化成join方式时，容易造成查出多条数据（1对多时），这时候需要进行去重select distinct t.id from t join t1 on t.id = t1.tid;

group by的优化方式 

group by可能会出现临时表（Using temporary），文件排序（Using filesort）等，影响效率。
可以通过关联的子查询，来避免产生临时表和文件排序，可以节省io explain select actor.firstname,actor.lastname,count(*) from filmactor inner join actor on actor.actorid = filmactor.actorid group by actor.actor_id\G 

limit优化的方式 limit 优化:

limit会使用Filesorts这样造成大量的IO问题;

例如:

select film_id , description from salika.film order by title limit 500,5;

分析:当limit 后面的数据越大,IO越高,性能越差;可以使用主键排序进行优化,避免过多的扫描;

优化:

select filmid , description from sakila.film where filmid >=500 and filmid <= 505 order by filmid limit 1,5;

优化步骤：1、使用有索引的列或者主键进行order by操作。 2、记录上次返回的主键，在下次查询时使用主键过滤。避免了数据量大时扫描过多的记录

索引优化

 如何选择合适的列建立索引？ 1、在where从句中，group by从句，order by从句，on从句中出现的列； 2、索引字段越小越好 3、离散度大的列放在联合索引的前面。 例如：index（staffid，customerid）与index（customerid,staffid） 由于customerid的离散度更大，所以应该使用index（customerid,staff_id)。

desc 表名；//查看一个表的结构 

select count（distinct customid）,count(distinct staffid) from 表名；//查看两列数值的离散度 
查找重复以及冗余索引的语句： SELECT a.TABLESCHEMA AS '数据名' ,a.TABLENAME AS '表名' ,a.INDEXNAME AS '索引1' ,b.INDEXNAME AS '索引2' ,a.COLUMNNAME AS '重复列名' FROM STATISTICS a JOIN STATISTICS b ON a.TABLESCHEMA = b.TABLESCHEMA AND a.TABLENAME = b.TABLENAME AND a.SEQININDEX = b.SEQININDEX AND a.COLUMNNAME = b.COLUMNNAME WHERE a.SEQININDEX = 1 AND a.INDEXNAME <> b.INDEX_NAME

索引的维护及优化--查找重复以及冗余索引：pt-duplicate-key-checker工具检查重复及冗余索引； 删除索引。mysql中目前通过慢查日志配合pt-index-usage工具进行索引使用情况的分析。

mysql表结构的优化规则：

1、可以存下我门数据最小的数据类型。

2、使用简单的数据类型。int要比varchar类型在mysql处理简单。

3、尽可能使用not null定义字段。

4、尽量少使用text类型，分用不可时最好考虑分表。

使用int来存储日期时间，利用FROMUNIXTIME()【转换成日期格式】,UNIXTIMESTAMP()【转换成int】两个函数来进行转换。 使用bigint来存储ip地址，利用INETATON(),INETNTOA()两个函数来进行转换。

表的垂直拆分 

把一个具有很多列的表拆分成多个表。

原则：1、把不常用的字段单独存放到一个表中。 2、把大字段独立存放到一个表中。 3、把经常一起使用的字段放到一起。

表的水平拆分 

表的水平拆分为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是一样的。

常用的水平拆分的方法为： 1、对id进行hash运算，如果要拆分成5个表则使用mod（id，5）取出0-4个值 2、针对不同的hashid把数据存到不同的表中。

面临的问题：1、跨分区表进行数据查询 2、统计及后台报表操作。

数据库系统优化：操作系统配置优化：

1. 网络方面，修改/etc/sysctl.conf文件，增加tcp支持的队列数，减少断开连接时，资源的回收：net.ipv4.tcpmaxsyn_backlog=65535;

2. 打开文件数的限制。修改/etc/security/limits.conf文件，增加一下内容以修改打开文件数量的限制:soft nofile 65535 hard nofile 65535

关闭iptables，selinux等防火墙软件。

系统配置优化

 https://tools.percona.com/wizard 会给推荐的参数配置