MYSQL优化方法

一张思维导图带你了解MYSQL优化方法，内容有MYSQL优化（explain关键字、索引优化、查询截取分析）、MVVC、锁。

MYSQL优化方法

• MYSQL 优化
  • explain关键字
    • 能干嘛
      • 表的读取顺序
      • 数据读取操作的操作类型
      • 哪些索引可以使用
      • 哪些索引被实际使用
      • 表之间的引用
      • 每张表有多少行被优化器查询
    • 怎么玩
      • Explain + SQL语句
      • 执行计划包含的信息
    • 各字段解释
      • id
        • select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
        • 三种情况
          • id相同，执行顺序由上至下
          • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
      • select_type
        • 查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询
          • 1.SIMPLE
            • 简单的select查询，查询中不包含子查询或者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
        • 访问类型排列
        • 显示查询使用了何种类型，从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL
          • system
            • 表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
          • const
            • 单表查询：根据主键或唯一索引，查出一条唯一数据记录 多表联合查询：根据主键或唯一索引，查出一条唯一数据记录
          • eq_ref
            • 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或卫衣索引扫描 （联表唯一）
          • ref
            • 非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配的某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
              • 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快就将主键置于where列表中，Mysql就能将该查询转换为一个常量
          • range
            • 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引
          • index
            • full Index Scan ,index与ALl区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小，（也就是说虽然ALL和INdex都是读全表，但是index是从索引中读取的，而all是从硬盘中读的）
          • ALL
            • Full Table Scan,将遍历全表以找到匹配的行
          • 备注：(百万级 ALL要进行优化) 一般来说，得保证查询至少达到range级别，最好能达到ref
      • possible_keys
        • 显示可能引用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
      • key
        • 实际使用的索引，如果为NULL，则没有使用索引
        • 查询中若使用了覆盖索引（查询字段与所建索引字段个数刚好吻合），则该索引仅出现在key列表中
      • key_len
        • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
        • key_len显示的值为索引字段的最大可能长度，并非实际使用长度。即key_len是根据表定义计算而得，不是通过表内检索出的
      • ref
        • 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
      • rows
        • 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
      • extra
        • 包含不适合在其他列显示但是十分重要的额外信息
          • 1.Using filesort（文件内排序）需要优化
            • 说明mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MYSQL中无法利用索引完成的排序操作称为“文件排序”
          • 2.Using temporary(使用了临时表)需要优化
            • 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询 group by。
          • 3.Using index 非常好
            • 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错！ 如果同时出现using where ,表明索引被用来执行索引键值得查找； 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
            • 覆盖索引（Covering Index）
              • 直接从索引中获取数据，不需要回表
          • using where
            • 表示使用了where过滤
          • using join buffer
            • 使用了连接缓存
          • impossible where
            • where子句的值总是false，不能用来获取任何元组
          • select tables optimized away
            • 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段再进行计算 查询执行计划生成的阶段即完成优化
          • distinct
            • 优化distinct操作，在找到第一匹配的元组后即停止找同样值得动作
  • 索引优化
    • 索引分析
      • 单表
        • 范围查找右边索引字段失效，可以绕过范围字段，只在其他字段建索引
      • 两表
        • 左连接加右表索引，右联接加左索引
      • 三表
        • 尽可能减少join语句中的NestedLoop的循环次数：“永远用小的结果集驱动大的结果集”； 优先优化NestedLoop的内层循环； 保证Join语句中被驱动表Join条件字段已经被索引； 当无法保证驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置；
    • 索引失效（应该避免）
      • 最左匹配原则，中间不能断
      • 不要在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
      • 范围查询之后全失效（存储引擎不能使用索引中范围条件右边的列）
      • 尽量使用覆盖索引（只访问索引的查询）减少select *
      • 在使用不等于（！=或者 <>）的时候无法使用索引会导致全表扫描
      • is null,is not null 也无法使用索引
      • like以通配开头（%abc）mysql索引失效会变成全表扫描的操作
      • 字符串不加单引号索引失效
      • 少用or，用它来连接时会索引失效
      • varchar类型不能失去单引号
      • in关键字 在后面数据量大于30%时不走索引，走全表扫描
    • 一般性建议
      • 优化口诀
        • 全值匹配我最爱，最左前缀要遵守
          • 带头大哥不能死，中间兄弟不能断 索引列上少计算，范围之后全失效 like百分写最右，覆盖索引不写星
  • 查询截取分析
    • 查询优化
      • 永远小表驱动大表
        • 当B表的数据集必须小于A表的数据集时，用in优于exists
        • 当A表数据集小于B表数据集时，用exists优于in select ... from table where EXISTS (subquery) 该语法可以理解为，将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE来决定主查询的数据结果是否得以保留）
        • 1.EXISTS（subquery）只返回True或false，因此子查询中的select*也可以是SELECT 1或select，官方说法是实际执行时会忽略SELECT清单，因此没有区别
        • 2.EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
      • order by关键字优化
        • order by子句，尽量使用Index方式排序，避免使用FileSort方式排序
        • Order By 满足两种情况，会使用Index方式排序
          • Order by 语句使用索引最左前列
          • 使用Where子句与Order By 子句条件列组合满足索引最左前列
        • 如果不在索引列上，filesort有两种算法：双路排序和单路排序
          • 单路性能好，但是如果sort_buff容量不够，会造成多次读取，所以需要优化
        • 优化策略
          • 增大sort_buffer_size参数的设置
          • 增大max_length_for_sort_data参数的设置
      • group by关键字优化
        • 先排序后分组，按照最左匹配原则
        • 无法使用索引时，增大max_length_for_sort_data和sort_buffer_size参数的设置
        • where高于having，能写在where的条件就不要写在having中
      • 表空间优化
        • 如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，表的空间会出现很多碎片; 删除时留白, 插入时尝试使用留白空间 (当删除后并未将所有留空的都插入数据,既可以认为未被立即使用的留空就是碎片)
          • 优化方式
            • * 使用 OPTIMIZE TABLE tbl_name命令 定期优化表，这个要依据表的数据情况定时间
          • 注意事项
            • * 命令执行时会锁表，所以如果表数据量较大或碎片较多时，谨慎执行
    • 慢查询日志
      • 慢查询日志几率运行时间超过long_query_time值得sql，默认10s
      • mysql默认没有开启漫查询日志
      • 如果不是调优需求，一般不建议启动该参数
      • 查看和开启
        • 默认
          • SHOW VARIABLES LIKE '%slow_query_log%'
        • 开启
          • set global slow_query_log=1；支队当前数据库生效，重启后失效
        • 永久生效
          • 设置配置文件my.cnf show_query_log=1.并设置日志文件路径
        • 默认时间
          • show variables like 'long_query_time%';大于这个时间才被记录
        • 日志过滤
          • 子主题1
    • 批量数据脚本
    • Show Profile
      • 作用
        • 了解SQL执行的线程的状态及消耗的时间
      • 开启
        • 默认是关闭的，开启语句“set profiling = 1;”
      • 查询语句
        • SHOW PROFILES ;
          • SHOW PROFILE FOR QUERY #{id};
    • 全局查询日志
• MVVC
  • 作用范围
    • 读已提交
      • 在每次查询生成一个独立的ReadView
    • 可重复读
      • 第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView
  • 数据结构
    • 聚簇索引记录中有两个必要的隐藏列
      • trx_id
        • 用来存储每次对每条聚簇索引记录进行修改的时候的事务id
      • roll_pointer
        • 每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo log中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。（注意插入操作的undo日志没有这个属性，因为它没有老版本）
          • ####
  • 实现原理
    • 开启事务时
      • 创建readview，readview维护当前活动的事务id，即未提交的事务id，排序生成一个数组
    • 访问数据时
      • 访问数据，获取数据中的事务id（获取的是事务id最大的记录），对比readview
        • 如果在readview的左边（比readview都小），可以访问（在左边意味着该事务已经提交）
        • 如果在readview的右边（比readview都大）或者就在readview中，不可以访问，获取roll_pointer,取上一个版本重新对比（在右边意味着，该事务在readview生成之后出现，在readview中意味着该事务还未提交）
  • 综述
    • 读取数据时用过类似一种快照的方式将数据保存下来，这样读锁和写锁不冲突了，不同的事务session会看到自己特定版本的数据，版本链
• 锁
  • MyISAM
    • 表锁
      • MyISAM加表锁方法
        • MyISAM执行（SELECT）前会自动给涉及的表加读锁
        • MyISAM执行（UPDATE、DELETE、INSERT 等）前会自动给涉及的表加写锁
        • 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。
      • 分类
        • 表共享读锁
          • 不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
        • 表共享写锁
          • 会阻塞其他用户对同一表的读和写操作；
      • 优先级
        • 默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。
        • 可以设置改变读锁和写锁的优先级：
          • 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。 给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
      • 优缺点
        • 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  • InnoDB
    • 表锁
      • 分类
        • 意向共享锁（S）
          • 事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
        • 意向排他锁（X）
          • 事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
    • 行锁
      • 优缺点
        • 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
      • 分类
        • 共享锁（S）
          • 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
        • 排他锁（X
          • 允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
    • 锁模式兼容
      • #####