MySQL优化

  之前看了一段时间关于MySQL优化方面的资料，虽然不是专业的DBA,但这些足以在日常工作中能够快速解决sql优化的问题。不过，发现无论什么时候，都缺少不了数据结构的出现。

1.慢查询日志

           每个公司都会有自己的日志监控系统，可以通过监控系统速度定位慢查询的日志。如图：

 

   当然，一些公司可能没有那么多的资源，可能会没有。另一中方式我也会告诉大家。

     查询数据库慢查询日志是否开启

       show VARIABLES LIKE '%slow%';

        设置开关

        set GLOBAL slow_query_log=ON;

 

#设置自己慢查询的时间(根据自己业务去定)

SHOW VARIABLES LIKE 'long_query_time';
#所有的查询都会记录下来

 SET long_query_time=0; 

查询存放慢查询日志的地方

 

    下载pt-query-digest

浏览器输入:https://www.percona.com/get/pt-query-digest   保存脚本pt-query-digest.txt，上传到服务器上。

1.下载安装工具 #wget percona.com/get/pt-query-digest

2.授予用户执行权限 #chmod u+x pt-query-digest

3.移动位置，注意pt-query-digest安装位置不一定在/目录下，也可能在/root/目录下，根据实际情况调整

mv /pt-query-digest /usr/bin/ 或 mv /root/pt-query-digest /usr/bin/

打开slow.log

通过pt-query-digest 执行日志，找出慢查询sql

Rank Query ID 部分就是慢查询的sql记录，下面会有更详细的。

2.分析慢查询

         大家熟悉的explain,先要直到执行计划的列

           

列	备注
table	对应的列
type	连接类型（system\const、eq_ref、ref、range、index、all）
possible_keys	可能使用的索引
key_len	实际使用的索引
rows	预计扫描行数
Extra	解析查询的额外信息（usring index、usring  where、useing temporary、useing filesort）

 

           type 列值：

         

值	备注
system\const	效率会非常高
eq_ref	用到了索引，效率也很高
ref	用到索引，效率比较高
range	性能次只，也用到索引
index	需要优化
all	需要优化

 

   3.索引结构

      B-Tree 索引结构  

 

   B-Tree索引 聚簇索引是一种存储数据结构，因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。InnoDB通过主键聚集数据，如果没有定义主键，InnDB会选择一个唯一的非空索引代替值。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

                                                                                         主键索引下面挂载的是主键的值  （聚簇索引）

                聚簇索引的优点：

                           可以把相关数据保存在一起，数据访问性块，覆盖索引查询只需要扫描该索引树。

                                               

                                                                                             非主键索引下面挂载的是主键（非聚簇索引）

  MyISAM存储引擎数据存储方式是非聚簇索引

                                                                           索引下挂载的是数据的行号（非聚簇索引）

  总结：

1.  匹配最左前缀    最左前缀要遵守
2.  全职匹配       最前和中间不能断
3.  匹配列前缀   索引列上少计算，范围之后全失效
4.  匹配范围值    like百分写最右，覆盖索引不写*
5.  精确匹配某列范围匹配另外一列   不等空值还有or,索引失效要少用

BTree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引
• 不能跳过所索引中的列
•  如果查询中某一个列的范围查询，则其右边所有的列都无法使用索引优化查找。