本文共 7468 字,大约阅读时间需要 24 分钟。
SQL 性能下降的几种原因:
MySQL 的 SQL 语句执行顺序
总结
// 内连接两表共有内容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.idunionselect * 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 nullunionselect * 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 有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询。
即一个索引只包含单个列,一个表可以有多个单列索引。
索引的值必须是唯一的,但允许有空值。
即一个索引包含多个列
// 单独创建索引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;
四种方式来添加数据表的索引:
// 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为 NULLALTER 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);
BTree 索引
Hash 索引
full-text 全文索引
R-Tree 索引
CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候。
磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候。
top,free,iostat和vmstat来查看系统的性能状态
Explain 又称为 【查看执行计划】
使用 Explain 关键字可以模拟优化器执行的 SQL 查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。
explain + SQL 语句包含的信息:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|
查看表的读取顺序
查看数据读取操作的操作类型
查看哪些索引可以使用
查看哪些索引被实际使用
查看表之间的引用关系
查看每张表有多少行被优化器查询
select 查询的序列号,包含一组数字,表示查询中执行 select 字句或操作表的顺序
id 字段有三种情况:
id 如果相同,可以认为是一组,从上往下顺序执行; 在所有组中,id值越大,优先级越高,越先执行; 衍生 = DERIVED
select_type 常见的值:
id | select_type |
---|---|
1 | SIMPLE |
2 | PRIMARY |
3 | SUBQUERY |
4 | DERIVED |
5 | UNION |
6 | UNION RESULT |
查询的类型,主要是用于区别。
普通查询、联合查询、子查询等的复杂查询。table:显示这一行的数据是关于哪张表的。
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
显示可能应用到这张表中的索引,一个或多个。
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定要被查询实际使用
。 实际使用的索引,如果为 NULL,则没有使用索引
查询中若使用了覆盖索引,则该索引仅出现在 key 列表中
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。
key_len 显示的值为索引字段的最大可能长度,并非实际使用长度
,即 key_len 是根据表定义计算而得,不是通过表内检索出的。 显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
查询中与其他表关联的字段,外键关系建立索引
根据表统计信息及索引选择情况,大致估算出找到所需的记录所需要读取的行数
记录十分重要的额外信息
常见的值有以下几种:
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 操作,再找到第一匹配的元组后即停止找同样值得动作。如果索引包含了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。
面试问题:如何解决 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 的字段一致或小于其字段。**
转载地址:http://qpqwi.baihongyu.com/