MYSQL系列笔记（三）——优化专题

一、首先mysql得先开启慢查询日志。不会的可以网上查。
1.1 环境准备：
perl环境，因为mysql自带的mysqldumpslow优化工具以及pt-query-digest工具分析都是perl文件。
curl环境，因为mysql安装版没有带pt-query-digest工具，所以想要使用该工具得先安装，安装可以通过curl命令直接安装。就得先需要curl环境。
安装perl环境只需要下载完成之后配置环境变量即可，跟jdk差不多，就不再记录了。
curl环境安装下载之后也配置环境变量即可。
1.2 mysqldumpslow因为是mysql完整版自带的，只需要安装了perl环境，去mysql目录bin下面查看有没有mysqldumpslow这个文件名的pl文件。
使用方式：mysqldumpslow /path（慢查询日志的路径，一般在mysql目录data里面）。
可以使用mysqldumpslow --help 帮助指令查看可以使用那些指令

经常使用的参数：

-s，是order的顺序

al 平均锁定时间

ar 平均返回记录时间

at 平均查询时间（默认）

c 计数

l 锁定时间

r 返回记录

t 查询时间

-t，是top n的意思，即为返回前面多少条的数据

-g，后边可以写一个正则匹配模式，大小写不敏感的
主要功能是, 统计不同慢sql的出现次数(Count),执行最长时间(Time),累计总耗费时间(Time),等待锁的时间(Lock),发送给客户端的行总数(Rows),扫描的行总数(Rows)

1.3 pt-query-digest这个就需要安装之后才能使用，pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

shell>pt-query-digest --help
常用参数：
--create-review-table ：当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table：当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter ：  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit：限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是95%则按总响应时间占比从大到小排序，输出到总和达到95%位置截止。
--log=s ：指定输出的日志文件
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表
           中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review：将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。
           当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since：从该指定日期开始分析。
--until：截止时间，配合—since可以分析一段时间内的慢查询。

二、使用explain关键字来查询某条sql语句的执行计划，当我们要去执行一条sql语句的时候，mysql首先是先分析了这个sql，再去执行，这个关键字就是用来查询这个计划，也相当于查询这句sql的执行情况了。
举例：

EXPLAIN SELECT * FROM customer;

下面这张图片是这条sql语句执行的结果。
这些列的含义：
table：显示这一行的数据是关于那张表的。
type:这是比较重要的一列，显示连接使用了哪种类型，它更确切的说是一种数据库引擎查找表的一种方式，在《高性能mysql》一书中作者更是觉得称呼它为访问类型更贴切一些。从最好的到最差的：const、eq_reg、ref、range、index、ALL。后面一点会详情说明
撇开sql的具体应用环境以及其他因素，你应当尽量优化你的sql语句，使它的type尽量靠左，但实际运用中还是要综合考虑各个方面的。
possible_keys:显示可能运用在这张表的索引，如果为空，没有可能的索引。
key:实际使用的索引，如果为空，则是没有使用索引。
key_len:使用的索引的长度，在不损失精确性的情况下，长度越短越好。
ref:显示索引的那一列被使用了，如果可能的话，是一个常数。
rows:mysql认为必须检查的用来返回数据的行数。
Extra:扩展列。有两个值需要注意一下；1.Using filesort：（文件排序，在order by中比较常见）看到这个的时候，查询就需要优化了。MYSQL需要额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
Using temporary ：这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列进行order by，而不是group by上。

再来试试带了where条件并且条件是主键时候的查询：

EXPLAIN SELECT * FROM customer WHERE customer_id='574';

可以看出来以上两条sql语句的区别还是很大的。(如果条件不是主键列也可能得到之前的结果)，主键是带了索引的。
三、type详解：
all:
这便是所谓的“全表扫描”，如果是展示一个数据表中的全部数据项，倒是觉得也没什么，如果是在一个查找数据项的sql中出现了all类型，那通常意味着你的sql语句处于一种最原生的状态，有很大的优化空间。
为什么这么说呢？因为all是一种非常暴力和原始的查找方法，非常的耗时而且低效。用all去查找数据就好比这样的一个情形：S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止。所以，基本所有情况，我们都要避免这样类型的查找，除非你不得不这样做。
index：
这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。
range：
range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描。
ref：
出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给employee表中的name列添加一个普通的key（值允许重复）
const：
通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。
type总结：
有很多东西和细节，想要明白清楚，也是没有那么简单的，需要对操作系统以及数据库的底层查询和运行原理要有一个清楚的理解。同时type的几种类型几乎都是基于索引之上的，因此需要对索引有个深入的了解，而且explain的结果可以指导我们什么时候加索引，什么时候不加索引，从而让我们更好的使用索引。
四、常见SQL优化：
以下数据我是在mysql官方demo数据库sakila中执行的以sql_yong工具执行的。(下载在mysql官网的demo中)。
count()以及max()优化：


看到的出来，这是一个全表扫描的方式，这条sql语句IO消耗特别大，会拖慢服务器的效率。需要扫描1W多行返回数据。而且也没有索引。
那我们这种情况一般怎么优化呢？
我们可以在要查询的这一行创建索引:

再次执行查询结果如下：

通过explain，我们可以看出现在这条sql并不需要实际的查询得出数据，只需要通过索引就可以知道sql的执行结果。（缺点就是创建索引的时候也需要花费时间。）
count()举例：
在一条SQL中同时查出2006年和2007年电影的数量。
select count(release_year=‘2006’ OR NULL) AS ‘2006年电影数量’,count(release_year=‘2007’ OR NULL) AS ‘2007年电影数量’ FROM film;
值得注意的地方是 count() 和count(列名)是有区别的，count()是要统计值为null的列，count(列名)是不会统计该列值为null的列。
子查询的优化：
通常情况下，需要把子查询优化为join查询，但在优化时要注意关联表是否存在一对多的关系，这时可能会出现重复数据，我们可以使用去重关键字 distinct。
优化limit查询：
limit常用于分页处理，时常会伴随order by从句使用，所以可能会造成大量的IO问题。
优化方案1：使用有索引的列或主键进行order by操作。
优化方案2：记录上次返回的主键，在下次查询时使用主键过滤查询 where id>=50 and id<=60 limit 1,10;
但是方案2有个缺点，主键一定得是顺序排序的。
当我们主键不是顺序自增的时候，这时候我们可以用一个小技巧：加一个额外列index_id，设成自增。再给这一列加上索引，查询时候使用这一列即可。
五、索引的合理使用：
如何选择合适的列建立索引：
1 在where，group by、order by、on等从句中出现的列。
2 索引字段越小越好（每一页字段越大数据就越多IO就更多。）
3 离散度大的列放在联合索引的前面（离散度：简单来讲：唯一值更多的）
索引的维护及优化：
重复索引：是指相同的列以相同的顺序建立的同类型的索引。
冗余索引：是指多个索引的前缀列相同，或是在联合索引中包含了主键索引。
过多的索引不但会影响我们写入效率也会影响查询的效率。
怎么找出冗余索引和重复索引呢？
1.可以使用Shlomi Noach的common_schema中的一些试图来定位，common_schema是一系列可以安装到服务器上的常用的存储和试图。

2.可以使用Percona Toolkit中的pt_duplicate-key-checker,该工具通过分析表结构来找出冗余和重复的索引。
pt_duplicate-key-checker跟pt-query-digest使用差不多 这个工具使用需要三个参数pt_duplicate-key-checker\ -u root \ -p 密码\ -h 数据库地址
。会得出重复索引的结果还有优化建议。
删除不用了的索引：
可以通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析，具体可以百度这个工具的使用。 pt-index-usage\ -u root -p 123456 \ lizhi-slow.log
六、数据库结构优化
选择合适的数据类型：
1.使用可以存下你的数据的最小的数据类型。
2.使用简单的数据类型。Int要比varchar类型在mysql处理上简单。
3.尽可能的使用not null定义字段。
4.尽量少用text类型，非用不可的时候可以考虑分表。
PS:如果使用int来存储日期时间，要利用FROM_UNIXTIME()和UNIX_TIMESTAMP()这两个函数，第一个函数意思是将int类型的时间戳转换为时间格式，第二个是正常的日期时间格式数据转换为int进行存储。（在插入和查询的时候直接用函数将值包起来）
可以使用bigint来存储IP地址，利用INET_ATON(),INET_NTOA()，作用跟上面两个函数差不多也是转换的作用。
七、表的范式化和反范式化
范式化是指数据库设计的规范，一般遵循第三设计范式就可以了，也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖。
有点不好解释，大概就是一个表有字段是重复值很多的，修改一条可能会修改这个表大多数数据，优化方式就是拆分。将有些字段拆分到另一个表，再进行中间表多对多关联。
反范式化优化：是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以达到优化查询效率的目的，就是空间换取时间的操作。也不用觉得数据库设计不规范，一切设计都是建立在需求上面的。
下面这两个图是完全符合第三范式设计规范的，但是我们查询一个订单所需要的信息需要关联四个表，但是查询的字段却不是很多。


下面这两个图是我们反范式化优化之后的结果，虽然表的字段设计重复了，但是也只是新增的时候多增加几个字段，可我们在查询的时候只需要查询一个表就行了。


表的垂直拆分
垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。可以按以下原则进行：
1.把不常用的字段单独存放到一个表中。
2.把大字段单独放到一个表中。
3.把经常一起使用的字段放到一起。
表的水平拆分
表的水平拆分主要是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。
常用的水平拆分方法：
1.对id进行hash运算，如果要拆分成5个表则使用mod（id，5）取出0-4个值
2.针对不同的hashID把数据存到不同的表中。
随之而来的也有问题：
1.跨分区表进行数据查询
2.统计及后台报表操作
我们可以讲前后台的业务分开，前台查询使用拆分之后的表，后台统计就查询几个表汇总的表，虽然效率会比较低一点。
八、系统配置优化
数据库是基于操作系统的，大多数项目MYSQL都是安装在Linux系统上，所以对于操作系统的参数配置也会影响到MySQL的性能。
常见的系统配置：


MYSQL配置文件：
MYSQL可以通过启动时指定配置参数和使用配置文件两种方式进行配置，
如果存在多个位置存在配置文件，则后面加载顺序的会覆盖前面的。






九、服务器硬件优化。