为什么要优化mysql

mysql 性能下降SQL慢、执行时间长、等待时间长
原因：
1.查询语句写的烂
2.索引失效
　　单值
　　复合
3.关联查询太多join(设计缺陷或不得已的需求)
4.服务器调优及各个参数设置

从几个方面优化

成本由低到高，效果却由高到低：SQL及索引 –> 数据库表结构 –> 系统配置 –> 硬件

常见的jion链接

常见的jion链接

1 A、B两表共有
select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
2 A、B两表共有+A的独有
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
3 A、B两表共有+B的独有
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
4 A的独有
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
5 B的独有
select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null; #B的独有
6 AB全有
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT * FROM tbl_emp A LEFT JOIN tbl_dept B ON A.deptId = B.id
UNION
SELECT * FROM tbl_emp A RIGHT JOIN tbl_dept B ON A.deptId = B.id
7 A的独有+B的独有
SELECT * FROM tbl_emp A LEFT JOIN tbl_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM tbl_emp A RIGHT JOIN tbl_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

1253382-20171018162601521-1841819198.png

如何优化mysql

1.观察，至少跑1天，看看生产的慢SQL情况
　　2.开启慢查询日志，设置阀值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
　　3.explain+慢SQL分析
　　4.show profile
　　5.运维经理 Or DBA, 进行SQL数据库服务器的参数调优。
总结
　　1.慢查询的开启并捕获
　　2.explain+慢SQL分析
　　3.show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况
　　4.SQL数据库服务器的参数调优。

索引简介

是什么

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构
　　可以得到索引的本质：索引是数据结构
　　可简单理解为“排好序的快速查找数据结构”

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

　　一般来说索引本身也很大，不可能全部存储在内存中，因此 索引往往以索引文件的形式存储在磁盘上
　　我们平常所说的索引，如果没有特别指明，都是B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树之外，还有哈希索引。

优劣势

优势：
　　类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
　　通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势：
　　实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
　　虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert,update和delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
　　索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间 研究建立最优秀的索引，或优化查询语句。

mysql索引结构

主要有BTree索引、Hash索引、full-text全文索引、R-Tree索引。下面主要分析BTree索引
　　备注：先说下，在MySQL文档里，实际上是把B+树索引写成了BTREE，
　　B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。
　　在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。

btree.png

初始化介绍

一颗b+树，浅蓝色的块称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)，如磁盘块1包含数据项17和35，包含指针P1, P2, P3, P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
　　真实的数据存在于叶子节点即3，5，9，19…
　　非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17，35并不真实存在于数据表中

查找过程

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短(相比于磁盘的IO)可以忽略不计。
　　通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO, 29在26和30之前，锁定磁盘块3的P2指针。
　　通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

使用索引时机

1.哪些情况需要创建索引
　　1).主键自动建立唯一索引
　　2).频繁作为查询查询条件的字段应该创建索引
　　3).查询中与其它表关联的字段，外键关系建立索引
　　4).频繁更新的字段不适合创建索引
　　5).where条件里用不到的字段不创建索引
　　6).单键/组合索引的选择问题(在高并发下倾向创建组合索引)
　　7).查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
　　8).查询中统计或者分组字段

2.哪些情况不要创建索引
　　1).表记录太少
　　2).经常增删改的表(因为不仅要保存数据，还要保存一下索引文件)
　　3).数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
　　注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

索引优化

Join语句的优化

二张表
　　性能优化：left join是由左边决定的，左边一定都有，所以右边是我们的关键点，建立索引要建右边边。当然如果索引在左边，可以用右连接。

1
2
select * from atable  
left join btable  on atable.aid=btable.bid;   // 最好在bid上建索引
结论：
　　尽可能减少Join语句中的NestedLoop的循环次数：“永远用小结果集驱动大的结果集”

避免索引失效

2.最佳左前缀法则：如果索引了多列，要尊守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
　　3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。
　　4.存储引擎不能使用索引中范围条件右边的列。
　　如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引，只有username和age会生效，phone的索引没有用到。
　　5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列致))，如select age from user减少select *
　　6.mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。
　　7.is null, is not null 也无法使用索引。
　　8.like 以通配符开头(‘%abc..’)mysql索引失效会变成全表扫描的操作。
　　所以最好用右边like 'abc%'。如果两边都要用，可以用select age from user where username like '%abc%',其中age是索引列
　　假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用
　　9.字符串不加单引号索引失效
　　10.少用or，用它来连接时会索引失效
　　11.尽量避免子查询，而用join

一般性建议

对于单键索引，尽量选择针对当前query过滤性更好的索引
　　在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
　　在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
　　尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
left/right join注意
1).on与 where的执行顺序
　　ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
　　所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件，减少Where的执行。如：
2).注意ON 子句和 WHERE 子句的不同
--------------------------------------------------------------------------------------
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |  2 |     22 |     0 |
+----+--------+----+--------+-------+
1 row in set (0.01 sec)
--------------------------------------------------------------------------------------
　　从上可知，第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN，然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

查询截取分析

查询优化

1.永远小表驱动大表

在java程序里，两个for循环，循环次数不管谁先谁后都是两者次数相乘。
　　但在mysql的语句中，一定要小表驱动大表，因为小表跟Mysql连接和释放数量少
如in与exists
select * from A where id in (select id form B)
等价于
for select id from B
for select * from A where A.id=B.id
结论：当B表的数据集必须小于A表的数据集时，用in优于exists。
select * from A where exists (select 1 from B where B.id=A.id) // 这里的1用任何常量都行
等价于
for select * from A
for select * from B where B.id=A.id
结论：当A表的数据集必须小于B表的数据集时，用in优于exists。
注意：A表与B表的ID字段应建立索引
　　exists通用语法select ... from table where exists (subquery)
　　该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果(true或false)来决定主查询的数据结果是否得以保留。
提示：
　　1).exists(subquery)只返回true或false， 因此子查询中select *也可以是select 1或select 'X', 官方说法是实际执行会忽略select 清单，因此没有区别。
　　2).exists 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验。
　　3).exists 子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析。

2.order by 关键字优化

1).order by子句，尽量使用index方式排序，避免使用FileSort方式排序
　　MySQL支持二种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。(用explain可以在extra字段里看到Using index/filesort)
　　Order By满足两种情况，会使用Index方式排序
　　　a.Order by语句使用索引最左前列
　　　b.使用where子句与Order by子句条件组合满足索引最左前列。
　　
　　2).尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
　　3).如果不在索引列上，fileSort有两种算法：mysql就要启动双路排序和单路排序
　　双路排序：MySQL4.1之前是使用双路排序，字面意思就是 两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。取一批数据，要对磁盘进行了两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1后，出现了改进算法，就是单路排序
　　单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
　　结论及引申出的问题：由于单路是后出的，总体而言好过双路，但是单路也有问题。

单路的问题
　　在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序(创建tmp文件，多路合并),排完再取sort_buffer容量大小，再排…..从而多次IO.
　　本来想省一次IO操作，反而导致了大量的I/O操作，反而得不偿失。

　　4).参数调优
　　增大sort_buffer_size参数的设置
　　增大 max_length_for_sort_data 参数的设置

参数设置，提高order by 的速度
　　1.order by 时select * 是一个大忌，最好只Query需要，这点非常重要。在这里影响的是：
　　　　a).当Query 的字段大小总和小于 max_length_for_sort_data ，而且排序字段不是text|blob类型时，会用改进后的算法 – 单路排序；否则用老算法–多路排序
　　　　b).两种算法算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O， 但是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size
　　2.尝试提高 sort_buffer_size
　　　　不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
　　3.尝试提高 max_length_for_sort_data
　　　　提高这个参数，会增加用改进算法的概率。但如果设的太高，数据总容量超出 sort_buffer_size 的概率增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

　　5).总结
　　MySql两种排序方式：文件排序(filesort)或扫描有序索引排序(index)
　　MySql能为排序与查询使用相同的索引
---------------------------------------------------------
Y a_b_c(a, b, c)
order by 能使用索引最左前缀
- order by a
- order by a, b
- order by a desc, c desc
如果where使用索引的最左前缀定义为常量，则order by 能使用索引
- where a = const order by b, c
- where a = const and b = const order by c
- where a = const order by b,c
- where a = const and b > const order by b, c
不能使用索引进行排序
- order by a asc, b desc, c desc  /* 排序不一致 */
- where g = const order by b, c   /* 丢失a索引  */
- where a = const order by c      /* 丢失b索引  */
- where a = const order by a, d   /* d不是索引的一部分  */
- where a in (..) order by b, c   /* 对于排序来说，多个相等条件也是范围查询(in 也是范围查询)！！  */
---------------------------------------------------------

3.group by 关键字优化

基本与 order by 优化一致
　　1).group by 实质是先排序后分组，遵照索引建的最佳左前缀
　　2).当无法使用索引列，增大 max_length_for_sort_data 参数的设置 + 增大sort_buffer_size参数的设置
　　3).where高于having，能写在where限定的条件就不要去having限定了。

慢查询日志

1.是什么

MySql的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中
　　long_query_time 的默认值为10，意思是运行10秒以上的语句。
　　由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒种，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。 　　

2.怎么用

1.说明
　　默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
　　当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

2.查看是否开启及如何开启
　　默认：show variables like '%slow_query_log%';
　　开启：set global show_query_log=1;，这个 只对当前数据库生效，如果MySQL重启后则会失效。如果要永久生效，必须修改配置文件my.cnf(其他系统变量也是如此)

3.开启慢查询后，什么样的SQL才会记录到慢查询日志里面呢？
　　这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，
　　命令：``show variables like ‘long_query_time%;’。可以使用命令修改，也可以在my.cnf参数里面修改。
　　假如运行时间正好等于 long_query_time 的情况，并不会被记录。也就是说，在mysql源码里是 判断>long_query_time，而非>=.

3.日志分析工具 mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow
查看 mysqldumpslow 的帮助信息：mysqldumpslow --help
　　s: 表示按何种方式排序
　　c: 访问次数
　　l: 锁定时间
　　r: 返回记录
　　t: 查询时间
　　al: 平均锁定时间
　　ar: 平均返回记录数
　　at: 平均查询时间
　　t: 返回前面多少条的数据
　　g: 后边搭配一个正则匹配模式，大小写不敏感的。


得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/show.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lig/mysql/show.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lig/mysql/show.log
另外建议在使用这些命令时结构 | 和more使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lig/mysql/show.log | more

4.show profile

这个是sql分析最强大的
　　默认情况下，参数处于关闭状态，并保存最近15次的运行结果

1.是什么
　　是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

2.分析步骤
　　1).是否支持，看看当前的mysql版本是否支持
　　　show variables like 'profiling%'
　　2).开启功能，默认是关闭，使用前需要开启
　　　set profiling=on
　　3).运行SQL
　　　select * from emp group by id%10 limit 1500000
　　　select * from emp group by id%20 order by 5
　　4).查看结果，show profiles

----------------------------------------------------------------------------------------------------------
mysql> SHOW PROFILES;
+----------+----------+-------------------------------------------------------+
| Query_ID | Duration | Query                                                 |
+----------+----------+-------------------------------------------------------+
|        1 | 2.000088 | select * from emp group by id%10 limit 1500000        |
|        2 | 1.000136 | select * from emp group by id%20 order by 5           |
----------------------------------------------------------------------------------------------------------
　　5).诊断SQL，show profile cpu, block io for query 2 后的数字是 show profiles 里的query_id
参数备注：
　　all: 显示所有的开销信息
　　block io: 显示块IO相关开销
　　context switches: 上下文切换相关开销
　　cpu: 显示CPU相关开销信息
　　ipc: 显示发送和接收相关开销信息
　　memory: 显示内存相关开销信息
　　page faults: 显示页面错误相关开销信息
　　source: 显示和source_function, source_file, souce_line相关的开销信息
　　swaps: 显示交换次数相关开销的信息

　　6).日常开发需要注意的结论
　　出现下一个情况，就很危险了。
　　　converting HEAP to MyISAM 查询结果太长，内存都不够用了往磁盘上搬了。
　　　Creating tmp table 创建临时表：copy数据到临时表，用完再删除
　　　Copying to tmp table on disk 把内存中临时表复制到磁盘，很危险！！！
　　　locaked

5.全局查询日志

永远不要在生产环境上打开，测试时可以
1、配置启用

1
2
3
4
5
6
7
8
9
在mysql的my.cnf中，设置如下：
# 开启
general_log=1
# 记录日志文件的路径
general_log_file=/path/logfile
# 输出格式
log_output=FILE
　　
2、编码启用
mysql> set global general_log=1;
mysql> set global log_output='TABLE';
# 此后，你所编写的sql语句，将会记录到mysql库里的general_log表。
# 可以用下面的命令查看
mysql> select * from mysql.general_log;

数据库表结构优化

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

待补充

1、mysql锁机制
2、explain分析
3、常见通用join查询分析