mysql数据库sql优化

SQL优化

 

 

背景

本文档描述了mysql数据库的sql优化，从整体上描述mysql数据库的sql执行过程，及整个流程上相关优化点，重点描述innoDB存储引擎的索引优化及锁机制，旨在为读者提供在mysql上做sql优化的思路，达到授人以渔的效果，希望能为读者带来收获。

 

基本定义

       性能：完成某项任务所需要的时间度量，即响应时间

       性能优化：就是降低任务执行的响应时间

工具

       无法测量就无法有效的优化

一、perconaToolkit中的pt-query-digest

二、New Relic

 

性能剖析

MYSQL的性能剖析分为执行性能分析与等待性能分析

一、剖析MYSQL查询

       1、剖析服务器负载：

              a、慢查询日志：5.1版本之后，慢查询精度升级到微秒级，而且可以输出所有的查询，且对性能的影响微乎其微。

              b、pt-query-digest：慢查询日志分析工具

       2、 剖析单挑查询性能：

              a、SHOW PROFILE：记录每个查询的耗时，及每个步骤的耗时。

              b、SHOW STATUS：统计服务器级别、回话级别的计数器，如：句柄计数器、临时文件计数器、表计数器等

              c、慢查询：

              d、Performance Schema

              e、explain

schema和数据类型优化

1、数据类型：

       a、更小的通常更好：占用更少的磁盘、内存、cpu缓存、cpu周期

       b、简单就好：简单的数据类型操作使用更少的cpu周期。如：整型比字符串操作的代价更低

       c、标志位类型选择：能满足需求的整型是最好的选择，因为整型很快，可以自增长；对于完全随机的字符串（MD5/UUID等）非常不适合做标志列，因为耗费很多空间，插入会随机写入索引的不同位置，会导致索引分裂、磁盘随机访问，select也会变得更慢，因为本来逻辑相邻的行被分散在磁盘和内存的不同位置，随机值会导致缓存缓存效果很差。

       d、缓存表和汇总表以及计数器表

慢查询基础

一、优化数据访问

       1、应用程序是否检索了太多的无用的行和列

              a、查询不需要的行，然后丢弃

              b、多表关联返回所有的列

              c、总是去除全部列：select *

       2、mysql服务器是否在分析大量超过需要的数据行

              a、看mysql查询是返回的行数是否与mysql服务器扫描的行数相差太大

              b、扫描的行数与访问类型：返回一行数据的访问类型不同成本也会不同，访问类型从慢到快（扫描的行数从多到少）为：全表扫描、范围扫描、唯一索引扫描、常数引用

              c、mysql在如下三种方式中使用where条件（由好到坏）：

                     1、在索引中使用where条件过滤掉不匹配的记录，在存储引擎层实现

                     2、使用索引覆盖扫描来返回记录，直接从索引中过滤掉不需要的记录并返回命中结果，在mysql服务器层实现

                     3、从数据表中返回数据，然后过滤掉不满足条件的记录，在mysql服务器层完成

       3、重构查询

              a、一个复杂查询拆分成多个简单查询

              b、切分查询

              c、分解关联查询：缓存效率更高、单条查询可以减少锁的竞争、更容易做数据库拆分、单条查询本身的效率更高、减少冗余记录的查询

MYSQL执行主流程

一、主流程图

      

二、优化点

       1、客户端与服务端通讯

              a、客户端与服务端采用半双工的通信协议，所以当客户端发送请求或者服务端返回结果后都不能被中止，所以为了安全起见，服务端接受客户端请求数据包有长度限制（可配置max_allowed_packet参数），对于返回的结果服务器没有长度限制，但是对于大数据的查询应都是用limit限制返回的结果集，否则很容易导致客户端程序内存溢出。

              b、连接状态：sleep、query、locked、analyzing andstatistics、coping to tmptable、sortingresult、sending data

       2、查询缓存

              a、开启缓存功能功能：query_cache_type = OFF |ON | DEMAND

              b、缓存中包含：查询结果、缓存管理维护相关的数据

              c、mysql使用一个映射表缓存查询结果，查询key大小写敏感

              d、查询缓存有一个个数据块组成，每个数据块变长，保存了该数据块类型、大小、存储的数据、指向前一个数据块的指针、指向下一个数据块的指针，所以查询缓存是一个双向链表结构

              e、数据块的类型：存储查询结果、存储查询与数据表的映射、存储查询文本等

              f、服务器启动的时候会初始化一块完整的内存，其中可用的空闲块为初始值减去维护元数据所需要的空间（约40KB）

              g、查询一开始返回结果的时候就缓存数据，而这个时候根本不知道缓存结果的精确大小，所以也无法准确的给查询结果分配空间。所以需要先从一个大的空间块中申请一个数据块（大于query_cache_min_res_unit的配置），当数据块缓存不下结果时会再申请一个新的数据块，而当结果小于数据块时，则释放该数据块多余的空间。

       3、查询优化器

              mysql处理的优化类型：

              a、重新定义关联表的顺序

              b、将外关联转化成内关联

              c、使用等价变换规则

              d、优化count()、min()、max()

              e、预估并转化成常数表达式

              f、覆盖索引扫描

              g、提前终止查询

              h、等值传播

              k、列表in()的比较

       4、排序优化

              1、首选使用索引生成排序结果，如果没有索引利用，mysql需要自己生成排序结果，如果排序的数据小于“排序缓冲区（max_length_for_sort_data）”，则直接在缓冲区排序，否则会将数据分块在缓冲区排序，将拍需要的结果存入磁盘，然后在将各个排序好的分块合并起来。

       5、查询执行引擎