MySQL优化,索引优化（学习笔记）

写SQL一次就能写成最好不常见，一蹴而就并非易事，都是需要经过优化才能让其更好，
本文通过学习网上视频资源归纳总结而来

SQL性能下降的原因

• 查询语句写得不好
• 索引失效（单值或复合）
• 关联查询太多join
• 服务器调优及各个参数设置

具体性能下降分析

首先我们在分析之前先了解以下基本的理论基础，才便于我们能顺利查找出错误；

• Mysql SQL语句执行顺序：
  FROM -> ON -> <join_type> JOIN <right_table> -> WHERE<where_condition> -> GROUP BY<group_by_list> -> HAVING<having_condition> SELECT -> DISTINCT <select_list> -> ORDER BY <order_by_condition> -> LIMIT <limit_number>
• Join联合查询7类

1. 左满联接查询：SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.key = B.key;
2. 右满联接查询：SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.key = B.key;
3. 内联接查询：SELECT <select_list> FROM TableA A （INNER）JOIN TableB B ON A.key = B.key;
4. 左独联接查询（去除公共部分只要左表）：SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.key = B.key WHERE B.key IS NULL;
5. 右独联接查询（去除公共部分只要右表）：SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.key = B.key WHERE A.key IS NULL;
6. 全联接查询 ：SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.key = B.key;
7. 全独联接查询（去除公共部分左右表都要）：SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.key = B.key WHERE A.key IS NULL OR B.key IS NULL

• 索引是帮助MySQL高效获取数据的数据结构，会影响SQL语句执行顺序WHERE后面的SELECT查询和ORDER BY排序

优势：提高数据检索效率，减低数据库的IO成本；降低数据排序成本和CPU的消耗
劣势：占用空间，降低增删改操作的速度，需要花时间研究建立最优秀的索引

Mysql Query Optimizer（Mysql自动优化）

当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQLQuery Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的 Hint信息（如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint 或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析,然后再得出最后的执行计划。

MySQL常见瓶颈（需要硬件优化）

CPU: 在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量时
通过 top，free，iostat，vmstat来查看系统的性能状态

Explain（重点）

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，分析查询语句或是表结构的性能瓶颈。使用就是用EXPLAIN + SQL语句即可

它的作用如下：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

执行计划包含的信息：

• id

查询的序列号，包含一组数字，表示查询中执行SELECT子句或操作表的顺序;

1. id相同顺序执行 由上至下
2. id如果是子查询，id越大越先执行
3. 有相同有不同，越大越先执行，否则顺序执行

• select_type

查询类型

1. SIMPLE:简单查询，查询中不包含子查询或者UNION
2. PRIMARY：查询中若包含任何复杂的子查询，最外层查询则会被标记为PRIMARY
3. SUBQUERY：在SELECT或WHERE列表中包含子查询
4. DERIVED ：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
5. UNION：若第二个Select出现在UNION之后，则被标记UNION；若UNION包含FROM子句中的子查询中，外层SELECT将被标记为：DERIVED
6. UNION_RESULT:从UNION表中获取结果SELECT

• table

显示这一行的数据是哪张表的

• type

从最好到最差依次为（常用），一般得保证查询至少到range级别， 最好能达到ref。：
system > const > eq_ref > ref > range > index > ALL

1. system：表只有一行记录
2. const：一般是主键索引查询或唯一索引查询，例如主键置于where列表中，
3. eq_ref:唯一性索引查询，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
4. ref：非唯一性索引扫描，返回匹配某个单独得所有行
5. range：只检索给定范围的行，使用一个索引来选择行
6. index: Full Index Scan,index和ALL 的区别为index只遍历索引树。
7. ALL：遍历全表找到匹配的行

• possibale_keys

显示可能应用到的索引，一个或多个

• key

实际用的索引，如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中

• key_len

表示索引中的使用的字节数，显示为索引字段的最大可能长度，并非实际使用长度

• ref

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

• rows

根据表统计的信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数(越少越好)

• Extra

1. Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。出现该值需要优化
2. Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用了临时表。常见于排序order by和分组查询group by。出现该值需要优化
3. Using index: 表示select操作中使用了覆盖索引，避免了访问表的数据行，效率不错。如果同时出现了using where，表示索引被用来执行索引键值的查找；如果没有出现using where ，表示索引用来读取数据而非执行查找动作。
4. Using where： 使用了where过滤

慢查询日志

具体指运行时间超过long_query_time值得SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，运行10秒以上的SQL语句

• 默认没有开启，需要手动开启，一般没有调优需求，建议不要开启
• 开启命令：set global slow_query_log=1;，如果要永久有效则需要修改MySQL配置文件，添加slow_query_log和slow_query_log_file，重启MySQL
• 利用以下命令快捷查看慢查询日志中的记录

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

索引优化（重点）

根据Explain如果出现type是ALL或者extra为using filesort或者using temporary则需要尝试优化

前言

单表

• 尝试建立过滤中字段的复合索引
• 如果同样还是出现以上两种情况则尝试把sql中的‘>’,‘<’尽量改成‘=’
• 尝试建立过滤字段中是常量值的字段的联合索引

双表

• 左联接索引建在右表中，右联接索引建在左表

三表

• 索引应该建立在常用查询字段

Jonin语句的优化：永远用小结果集驱动大的结果集，即用小表去查大表；优先优化NestedLoop的内层循环；保证Join语句中被驱动表上Join条件字段已经被索引

一般性建议

• 全值匹配我最爱:sql查询字段条件是按照所建索引字段顺序一致
• 最佳左前缀法则：如果索引多列，查询从索引的最左前列开始并不跳过索引中的列
• 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
• mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
• is null ,is not null 也无法使用索引
• like以通配符开头(’%abc…’ )mysql索引失效会变成全表扫描的操作
• 字符串不加单引号索引失效
  分
• 少用or,用它来连接时会索引失效