含泪整理MySQL索引

最新推荐文章于 2022-08-04 15:40:37 发布

失忆老幺

最新推荐文章于 2022-08-04 15:40:37 发布

阅读量110

点赞数 1

分类专栏：数据库文章标签：索引 mysql

本文链接：https://blog.csdn.net/qq_42216791/article/details/111387502

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

索引优化分析

性能下降SQL慢
- 查询语句写的差
- 索引失效
  - 单值索引
  - 多值索引
- 关联查询太多join(设计缺陷或不得已的需求)
- 服务器调优以及各个参数设置（缓冲、线程数等）
执行时间长
等待时间长
常见通用的JOIN查询
- 共有的INNER JOIN
- 左表的共有 LEFT JOIN
- 右表的共有 RIGHT JOIN
- 左边独有 where b.key is null
- 全链接 full outer join
- ab都没用a.key is null or b.key is null

索引简介

是什么
- 索引：是帮助MySQL搞笑获取数据的数据结构，可以得到索引的本质。索引是一种数据结构。
- 搞笑查询，类似新华字段。
- 排好序的快速查找数据结构。（order by …）
  - 排好序。
  - 查询快。
  - 数据库系统还维护者满足特定查找算法的数据结构。B+TREE索引（此处不展开讨论）
优势
- 提高索引的检索效率，降低数据库的IO成本。
- 通过数据进行排序，降低数据的排序成包，降低了cpu的消耗。
劣势
- 实际上索引也是一张表，该表保存了主键于索引字段，并指向实体表的数据，占空间。
- 索引提高了查询，但是降低了更新。
- 提高效率的一个因素，如果有大数据量的表，花时间建立最优秀的索引，或者优化查询。
MySQL索引分类
- 单值索引
  - 一个索引只包含单个列，一个表可以有多个单列索引。
- 唯一索引
  - 索引列的值必须唯一，但允许有空值。
- 复合索引
  - 一个索引包含索格列
- 基本语法
- 哪些需要创建索引
  - 主键索引
  - 频繁查询
  - 外键管理
  - 组合索引
  - 排序字段
  - 统计或分组
- 哪些情况不需要创建索引
  - 表记录少
  - 300百万就差不多开始优化
  - 经常增删改的不创建索引
  - 重复的值
MySQL索引结构
- BTree索引
- Hash索引
- full-text全文索引
- R-TREE索引

性能分析

一、MySQL常见的性能瓶颈

cpu饱和
io内存
硬件瓶颈，top,free,iostat,vmstat
Explain的使用
- 是什么
  - 模拟优化器执行的SQL查询语句，从而知道MySQL是如何处理SQL语句的，分析查询语句或者表结构。
- 能干什么
  - Explain + sql
  - 表的读取顺序
  - 数据读取操作的操作类型
  - 哪些索引可以被使用
  - 哪些哪些索引实际被使用
  - 表之间的引用
  - 每张表有多少行辈优化器查询

二、Explain每个字段的详细描述

id
- id不同，如果是子查询，id的需要会递增，id值越大优先级越高，先被执行。
- id相同，执行顺序从上往下
- id相同与不同，同时存在。
  - 数字大的优先级高。(derived衍生)
select_type
- 有哪些？
  - simple
    - 简单的select查询，查询中不包含子查询或者union
  - primary
    - 查询中若包含任何复杂的子部分，最外层查询则被标记为primary
  - subquery
    - 在select 或者where 列表中包含了子查询
  - derived（衍生)
    - 在from列表中包含的子查询被标记为derived，MySQL会递归执行这些子查询，把结果放在临时表里。
  - union
    - 若第二个select出现在union之后，则被标记为union。若union包含在from子句的子查询中，外城select被标记为derived.
  - union result
    - 两个union查询的结果。
- 查询的类型，主要是用于区别普通查询，联合查询，子查询等复杂查询。
type
- 类型有：all,index,arnge,ref,eq_ref,const,system,null
system
- 表只有一行记录，平时不会出现，忽略不计
const
- 一次索引，用于比较primary 或者union 索引。因为只匹配一行记录。例如将主键置于where列表，MySQL就能将该查询转换成一个常量
eq_ref
- 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引
ref
- 非唯一性索引扫描，返回匹配某个单独的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行。可能会找到多个符合条件的行，所以属于查找和扫描的混合体。
range
- 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就算在where语句中出现的between,<>,in,等的查询。这种范围扫描比全表扫描要好，因为它只需要开始结束于索引的两个点，不用全表索引。
index
- index与all区别为Index只遍历索引树。
all
- 全表扫描
possible_keys
- 可能用到的索引。一个，多个。
- 不一定被实际查询引用。
key
- 实际使用到的索引。
- 查询中若使用到了全文索引
- 覆盖索引：查询的字段和索引字段一一对应。（查询列要被所建的索引覆盖）
key_len
- 索引使用的字节数。索引字段的最大可能的长度，不是实际。(越小越好)
ref
- 显示索引的那一列被使用。最好是常量
rows
- 大致读到的行数。
Extra
- using firesort
  - mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为文件排序。（排序未命中索引）
- sql+\G
- using temporary
  - 新建了内部的临时表，常见于order by 和分组查询group by
- using index(还可以哦)
  - 表示使用了覆盖索引，避免了访问表的数据行。如果同时出现了using where,表明索引被用来执行索引键值的查找。如果没用同时现象using where ，表民缩影用来读取数据而非执行查询动作。
- using where
- using join buffer：使用了连接缓存
- impossible where：不能获取到任何元素
- select tables optimized away
  - 在没用group by 子句的情况瞎，基于索引优化min/max操作或者对用MyIAAM存储引擎优化count()操作，不必等到执行阶段再进行计算。查询执行计划生成阶段即完成优化。
- distinct：找到第一个匹配的元组后就收工。
从最好到最差 system>const>eq_ref>ref>i>range>index>all
一般来说，保证查询至少达到range级别，最好能达到ref