索引优化分析
- 性能下降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查询的结果。
- simple
- 查询的类型,主要是用于区别普通查询,联合查询,子查询等复杂查询。
- 有哪些?
-
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:找到第一个匹配的元组后就收工。
- using firesort
-
从最好到最差 system>const>eq_ref>ref>i>range>index>all
-
一般来说,保证查询至少达到range级别,最好能达到ref
性能分析
一、索引分析
- 单表索引
- 两张表
- 左链接加到右表
- 右链接加到左表
- 三张表
- 加到后面两张表
- 结论
- 小结果集驱动大结果集
- 优先优化内部循环
- join buffer的设置
二、避免索引失效
- 全值索引最好
- 最左匹配法则
- 不在索引列上做任何操作(计算,函数,类型转换,会导致索引失效而转向全表扫描)
- 存储引擎不能使用索引范围条件右边的列(范围之后全失效)
- 尽量使用覆盖索引。不要select *
- mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描
- is null,is not null也无法使用索引(索引失效)(%加右边)
- 解决办法:覆盖索引。
- like 以通配符开通(%aaa)索引也会失效,变成全表扫描
- 字符串不加单引号索引失效
- 少用or,用它来连接时会索引失效