文章目录
- SQL 性能下降
- Join 查询
- 七种 Join 查询
- 索引简介
- 什么是索引?
- 索引的优势
- 索引的劣势
- mysql 索引分类
- mysql 索引结构
- 哪些情况需要创建索引
- 哪些情况不要创建索引
- 性能分析
- MySql Query Optimizer
- MySQL 常见瓶颈
- Explain
- 如何使用 Explain
- Explain 的作用
- Explain 的 id 字段作用
- Explain 的 select_type 和 table 字段作用
- Explain 的 type 字段作用
- Explain 的 possible_keys 和 key 字段作用
- Explain 的 key_len 字段作用
- Explain 的 ref 字段作用
- Explain 的 rows 字段作用
- Explain 的 Extra 字段作用
- 索引优化
- 索引分析
- 索引失效
- 一般性建议
SQL 性能下降
SQL 性能下降的几种原因:
- SQL 写的烂
- 使用了索引,但是索引失效了
- 关联查询太多 join(设计缺陷或不得已的需求)
- 服务器调优及各个参数设置的问题(比如缓冲区、线程数大小设置问题等)
Join 查询
MySQL 的 SQL 语句执行顺序
总结
七种 Join 查询
// 内连接两表共有内容
select * from emp a inner join dept b on a.deptId=b.id
// 左连接左表全有 && 两表共有内容
select * from emp a left join dept b on a.deptId=b.id
// 右连接右表全有 && 两表共有内容
select * from emp a right join dept b on a.deptId=b.id
// 左连接 左表独有内容
select * from emp a left join dept b on a.deptId=b.id where b.id is null
// 右连接 右表独有内容
select * from emp a right join dept b on a.deptId=b.id where a.deptId is null
// 全连接 两表全部内容【union 去除重复数据】
select * from emp a left join dept b on a.deptId=b.id
union
select * from emp a right join dept b on a.deptId=b.id
// 全连接 两表独有内容
select * from emp a left join dept b on a.deptId=b.id where b.id is null
union
select * from emp a right join dept b on a.deptId=b.id where a.deptId is null
索引简介
官方定义: 索引 Index 是帮助 mysql 高效获取数据的数据结构。所以我们可以得出一个结论:**索引的本质就是数据结构。**
什么是索引?
排好序的快速查找数据结构
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储到磁盘上。
我们平常所说的索引,如果没有特别指明,都是指 B 树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都是使用 B+ 树索引,统称索引。除了B+树这种类型的索引之外,还有哈希索引(hash,index)等。
索引的优势
类似于大学图书馆建立书目索引,提高数据检索的效率,降低了数据库的 IO 成本
通过索引列对数据进行排序,降低了数据排序的成本,降低了CPU的使用消耗
索引的劣势
实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 insert、update、delete。因为更新表时,MySQL 不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素,如果你的 MySQL 有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询。
mysql 索引分类
- 单值索引
即一个索引只包含单个列,一个表可以有多个单列索引。
- 唯一索引
索引的值必须是唯一的,但允许有空值。
- 复合索引
即一个索引包含多个列
- 基本语法
// 单独创建索引
create [unique] index indexName ON mytable(columnName(length));
// 创建表的同时创建索引
alter mytable add [unique] index [indexName] ON (columnName(lenght));
// 删除索引
drop index [indexName] ON mytable;
// 查看索引
show index from table_name;
四种方式来添加数据表的索引:
// 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为 NULL
ALTER table table_name ADD PRIMARY KEY(column_list);
// 该语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER table table_name ADD UNIQUE index_name(column_list);
// 添加普通索引,索引值可出现多次。
ALTER table table_name ADD INDEX index_name(column_list);
// 该语句指定了索引为 FULLTEXT,用于全文索引
ALTER table table_name ADD FULLTEXT index_name(column_list);
mysql 索引结构
BTree 索引
Hash 索引
full-text 全文索引
R-Tree 索引
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段,外键关系建立索引
- 单键/组合索引选择问题(高并发下倾向于创建组合索引)
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度。
- 查询中统计或者分组字段。
哪些情况不要创建索引
- 频繁更新的字段不适合创建索引
- Where 条件里用不到的字段不创建索引
- 表字段太少
- 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据建立索引。如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
性能分析
MySql Query Optimizer
- MySQL 中有专门负责优化 SELECT 语句的优化器模块,主要功能:通过计算分析系统中收集到统计信息,为客户端请求的 Query 提供它认为最优的执行计划,这部分最耗时间。
- 当客户端向 MySQL 请求一条 Query,命令解析器模块完成请求分类,区别出是 SELECT 并转发给 MySQL Query Optimizer 时,MySQL Query Optimizer 首先会对整条 Query 进行优化,处理掉一些常量表达式的预算,直接换算成常量值。并对 Query 中的查询条件进行简化和转换,如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息(如果有),看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据 Query 进行写相应的计算分析,然后再得出最后的执行计划。
MySQL 常见瓶颈
- CPU:
CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候。
- IO:
磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候。
- 服务器硬件的性能瓶颈:
top,free,iostat和vmstat来查看系统的性能状态
Explain
Explain 又称为 【查看执行计划】
使用 Explain 关键字可以模拟优化器执行的 SQL 查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。
如何使用 Explain
explain + SQL 语句
包含的信息:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
Explain 的作用
查看表的读取顺序
查看数据读取操作的操作类型
查看哪些索引可以使用
查看哪些索引被实际使用
查看表之间的引用关系
查看每张表有多少行被优化器查询
Explain 的 id 字段作用
select 查询的序列号,包含一组数字,表示查询中执行 select 字句或操作表的顺序
id 字段有三种情况:
- id 相同,执行顺序由上至下
- id 不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
- id相同不同,同时存在
id 如果相同,可以认为是一组,从上往下顺序执行;
在所有组中,id值越大,优先级越高,越先执行;
衍生 = DERIVED
Explain 的 select_type 和 table 字段作用
select_type 常见的值:
id | select_type |
1 | SIMPLE |
2 | PRIMARY |
3 | SUBQUERY |
4 | DERIVED |
5 | UNION |
6 | UNION RESULT |
查询的类型,主要是用于区别。
普通查询、联合查询、子查询等的复杂查询。
- SIMPLE :简单的 select 查询,查询中不包含子查询或者 union
- PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为 primary
- SUBQUERY:在 select 或 where 列表中包含了子查询
- DERIVED:在 from 列表中包含的子查询被标记为 DERIVED衍生 ,MySQL 会递归执行这些子查询,把结果放在临时表里。
- UNION:若第二个 select 出现在 union 之后,则被标记为 union;若 union 包含在 from 字句得子查询中,外层 select 将被标记为:DERIVED
- UNION RESULT:从 union 表获取结果得 select
table:显示这一行的数据是关于哪张表的。
Explain 的 type 字段作用
type 表示访问类型排列,type 显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
system>conft>eq_ref>ref>range>index>ALL
一般来说,得保证查询至少达到 range 级别,最好能达到 ref。
type 常见的值:
ALL | index | range | ref | eq_ref | const,system | NULL |
显示查询使用了何种类型,从最好到最差依次是:
system>const>en_ref>ref>range>index>ALL
- system:表只有一行记录(等于系统表),这是 const 类型得特列,平时不会出现,这个也可以忽略不计
- const:表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据,所以很快;如将主键置于 where 列表中, MySQL 就能将该查询转换为一个常量。
- ep_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,他可能会找到多个符合条件的行,所以他应该属于查找和扫描得混合体。
- range:只检查给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between,<,>,in 等的查询,这种范围扫描索引比全表扫描要快,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。
- index:Full Index Scan,index 与 ALL 区别在于 index 类型只遍历索引树。这通常比 ALL 快,因为索引文件通常比数据文件小。(也就是说虽然 all 和 Index 都是读全表,但 index 是从索引中读取的,而 all 是从硬盘中读的)
- all:Full Table Scan,将遍历全表以找到匹配的行。
Explain 的 possible_keys 和 key 字段作用
- possible_keys
显示可能应用到这张表中的索引,一个或多个。
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定要被查询实际使用
。
- key
实际使用的索引,如果为 NULL,则没有使用索引查询中若使用了覆盖索引,则该索引仅出现在 key 列表中
Explain 的 key_len 字段作用
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。
key_len 显示的值为索引字段的最大可能长度,并非实际使用长度
,即 key_len 是根据表定义计算而得,不是通过表内检索出的。
Explain 的 ref 字段作用
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
查询中与其他表关联的字段,外键关系建立索引
Explain 的 rows 字段作用
根据表统计信息及索引选择情况,大致估算出找到所需的记录所需要读取的行数
Explain 的 Extra 字段作用
记录十分重要的额外信息
常见的值有以下几种:
-
Using filesort
:说明 Mysql 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。 -
Using temporary
:使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。 -
Using index
:表示相应的 select 操作中使用了覆盖索引 (Covering Index),避免访问了表的数据行,效率不错!如果同时出现了 using where,表明索引被用来执行索引键值的查找;如果没有同时出现 using where,表明索引用来读取数据而非执行查找动作。覆盖索引(Covering Index)
覆盖索引:就是 select 的数据列只用从索引中就能取得,不必读取数据行,MySQL 可以利用索引返回 select 列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所创建的索引覆盖。
注意:如果要使用覆盖索引,一定要注意 select 列表中只取出需要的列,不可 select * ,因为如果将所有字段一起做索引会导致索引文件过大,查询性能降低。
-
Using where
:表示该条 SQL 使用了 where 过滤。 -
using join buffer
:表示该条 SQL 使用了连接缓存。 -
impossible where
:表示该条 SQL 语句的 where 字句的值总是 false,不能用来获取任何元组。 -
select tables optimized away
:在没有 group by 的情况下,基于索引优化 min/max 操作或者对于 MyISAM 存储引擎优化 count( * )操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。 -
distinct
:优化 distinct 操作,再找到第一匹配的元组后即停止找同样值得动作。
索引优化
索引分析
- 单表分析:常用来查询得字段建立索引优化良好。
- 两表分析:左连接建索引建议建到右表中,右表则建到左表中,相反的来互相建立索引即可优化。
- 三表分析:同两表一致,也是相反的给另外两表建立索引优化。
索引失效
- 全值匹配
- 最佳左前缀法则
如果索引包含了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。
- 不在索引列上做任何操作(计算、函数、自动或手动类型转换),会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少 select *
- mysql 在使用不等于 (!= 或者 <>) 的时候无法使用索引会导致全表扫描
- is null,is not null 也无法使用索引
- like 通配符以 % 开头(’%abc…’) mysql 索引失效会变成全表扫描操作。
- 字符串不加单引号索引失效。
- 少用 or,用它来来连接时会索引失效。
面试问题:如何解决 like ‘%abc%’ 时索引不失效的方法?
我们都知道 like 查询时要保证索引不失效的前提是 %加在字符串右边,列如 : ‘abc%’ 这样写索引就不会失效,但现在的条件是我们要全模糊查询,例如 ‘%abc%’ ,这样写索引 100% 会失效,如何解决这种问题呢?
我们可以添加覆盖索引来优化,比如你的查询条件是 name,我们在该字段上添加一个索引,列如:
select * from table where name like ‘%abc%’;
经过 explain 分析以上sql并没有用到索引,我们可以这样改这条 sql
select name from table where name like ‘%abc%’;
再次经过 explain 分析以上 sql ,终于发现确实用到了索引,这就保证了即使用了 like 全模糊查询,又解决了索引失效的问题。**这里要注意的事情:覆盖索引优化的前提是 select 查询的字段正好和你的索引字段数量一致的前提下,简单来说:覆盖索引字段和你select 的字段一致或小于其字段。**
一般性建议
- 对于单键索引,尽量选择针对当前 query 过滤性更好的索引。
- 在选择组合索引的时候,当前 Query 中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
- 在选择组合索引的时候,尽量选择可以能够包含当前 query 中的 where 字句中更多字段的索引。
- 尽可能通过分析统计信息和调整 query 的写法来达到选择合适索引的目的。