mysql（二）

优化篇

八、SQL优化

8.1 优化SQL语句的一般步骤

8.1.1 通过show status命令了解各种SQL的执行频率

         

show status like 'Com_%';              --提供服务器状态信息，默认为session级（当前连接）show session status like 'Com_%';      show global status like 'Com_%';

上图表示每个语句执行的次数，通过比较管线Com_select，Com_insert，Com_update，Com_delete。

对于事务型的应用，还应关注Com_commit，和Com_rollback，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

8.1.2 定位执行效率较低的SQL语句

         通过慢查询日志定位那些执行效率较低的SQL语句（慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候信息慢查询体质不能定位问题）。

         show processlist命令查看当前MySQL正则进行的线程，包括线程的状态，是否锁表等。

8.1.3 通过EXPLAIN分析低效SQL的执行计划

          

explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id = b.customer_id and email = '[e-mail]' \G

select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询），PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）

table：输出结果集的表

type：表示MySQL在表中找到所需行的方式

           ALL：全表扫描，MYSQL遍历全表来找到匹配的行

           index：索引全扫描，遍历整个索引来查询匹配的行

           range：索引范围扫描，常见于<、≤、＞、≥、between

           ref：使用非唯一索引或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。ref还经常出现在join操作中

           eq_ref：类似于ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配（即多表连接中使用primary key或者unique index作为关联条件）

          const/system：单表中最多只有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当做常量处理。（eg 根据主键或者唯一索引进行的查询为const，查询只有一条记录的表为system）

          NULL：MySQL不用访问表或者索引，直接就能得到结果

possible_keys：查询时可能使用的索引

key：实际使用的索引

key_len：使用到的索引字段的长度

rows：扫描行的食量

Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

8.1.4 通过show profile分析SQL

可以根据profiling功能查看一条SQL到底是在哪里损失了性能，通过profile能够更清楚的了解SQL执行的过程。

查看当前MySQL是否支持profile

select @@have_profiling;

 默认profiling是关闭的，可以通过set在session级开启profiling

select @@profiling;

MyISAM表由表元数据的缓存（例如行数，count(*)值），那么对一个MyISAM表的count(*)是不需要消耗太多资源的，而对于InnoDB来说，没有这种元数据缓存，count(*)执行的很慢，  

eg 在InnoDB引擎的payment表和MyISAM存储引擎的payment表上分别执行count(*)查询

show profiles;

show profile for query 1;

注：sending data状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。由于Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

-- 查询INFORMATION_SHCEMA.PROFILING表并按照时间做DESC排序SET @query_id := 1;SELECT     STATE,     SUM(DURATION) AS Total_R,     ROUND(          100 * SUM(DURATION) / (               SELECT                    SUM(DURATION)               FROM                    INFORMATION_SCHEMA.PROFILING               WHERE                    QUERY_ID = @query_id          ),          2     ) AS Pct_R,     COUNT(*) AS Calls,     SUM(DURATION) / COUNT(*) AS "R/Call"FROM     INFORMATION_SCHEMA.PROFILINGWHERE     QUERY_ID = @query_idGROUP BY     STATEORDER BY     Total_R DESC;

在获取最消耗时间的线程状态后，可以进一步选择all、cpu、block io、context switch、page faults等名校类型来查看MySQL使用什么资源上耗费了过高的时间

查看cpu的耗费时间show profile cpu for query 1;

如上图，在sending data状态下，时间主要小号在cpu上

eg2 创建一个同样表结构的myisam表，数据量也完全一致

 create table payment_myisam like payment;# create table like 只会完整复制原表的建表语句，但不会复制数据alter table payment_myisam engine=myisam;insert into pyment_myisam select * from payment;select count(*) from payment_myisam;show profiles \G;show profile for query 19;

InnoDB引擎的表在Count(*)时经历了sending data状态，存在访问数据的过程，而MyISAM引擎的表在executing之后直接就结束查询，完全不需要访问数据。

8.2 索引优化

      MySQL目前提供以下4种引擎

      B-Tree索引：最常见的索引类型，大部分引擎支持B树索引。

      HASH索引：只有MEMORY引擎支持，使用场景简单。

      R-TREE索引：空间索引是MyISAM的一个特殊索引，主要用于地理空间数据类型

      FULL-TEXT（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本开始提供对全文索引的支持。

总结：

一、优化SQL语句的一般步骤

       1、通过show status命令了解各种SQL的执行频率

       2、定位执行效率比较低的SQL语句（慢查询日志和show processlist）

       3、通过EXPLAIN分析低效SQL的执行计划

explain select 语句

       4、通过show profile分析SQL（可以看到执行过程中线程的每个状态和消耗的时间）

show profilesshow profile for query n

       5、通过trace分析优化器如何选择执行计划

       6、确定问题并采取相应的优化措施

二、优化索引

      1、匹配全值

      2、匹配最左前缀

      3、匹配列前缀(like 'AFRATICA%')

      4、查看索引使用情况，handler_read_key代表一个索引值读的次数，如果很低代表索引不经常使用

show status like 'Handler_read%'

三、定期分析和检查表

analyze table #本语句用于分析和存储表的关键字分布

有一个字段是cardinality。这个字段的意思是：基数，集的势。（可以翻译为“散列程度”），它表示某个索引对应的列包含多少个不同的值——如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。它是索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

check table

检查一个表或多个表（视图）是否有错

optimize table

对于含有可变长度行的表，可以将表中的碎片进行合并，并可以消除由于删除和更新造成的空间浪费

四、其他常用优化

4-1 大批量插入数据

      1、MyISAM：

ALTER TABLE TBL_NAME DSIABLE KEYS; #关闭非唯一索引的更新loading the dataALTER TABLE TBL_NAME ENABLE KEYS;

      2、InnoDB：导入的数据按主键的顺序排列   

                           关闭唯一性校验

SET UNIQUE_CHECKS  #

                          关闭自动提交方式

SET AUTOCOMMIT=0

4-2 优化INSERT

      1、使用多个值表的insert语句，所见客户端和数据库之间的连接

insert into test values(1,2),(1,3),(1,4)。。。。

      2、使用文本文件时，用load data infile

4-3 优化order by

      尽量减少额外的排序，通过索引直接返回有序数据。where和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序