1、性能下降、SQL慢、执行时间长、等待时间长的原因
- 查询语句写的差
- 索引失效
- 单值索引：新建索引的语句只实施在一列上
- 复合索引：在多个列上建立索引
- 关联查询太多join(设计缺陷或不得已的需求)
- 服务器调优及各个参数设置(缓冲\线程数等)
2、索引简介
（1）索引是什么
MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构，所以索引的本质是：数据结构，可以理解为排好序的快速查找数据结构
索引的目的在于提高查询效率，可以类比字典，
如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。
如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？
是不是觉得如果没有索引，这个事情根本无法完成？
在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式指向、引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
- 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
- 为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录
- 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上
（2）索引的优势与劣势
优势：
- 类似图书馆对书本的检索，能够提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势：
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息（改数据还得改索引）
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间
的（索引也占空间）
（3）MySQL索引分类
a. 单值索引
即一个索引只包含单个列，一个表可以由多个单列索引
b. 唯一索引
索引列的值必须唯一，但允许有空值
c. 复合索引
即一个索引包含多个列
d. 基本语法
（4）MySQL索引结构
我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引 。其中聚集索引，次要索引，覆盖索引， 复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等
a. BTree索引
MySQL使用的是Btree索引
初始化介绍：
一颗b树，黑框内的称之为一个磁盘块，每个磁盘块包含几个数据项（蓝色所示）和指针（黄色所示）
如磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3， P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。 真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。 非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如 17、35 并不真实存在于数据表中
查找过程：
如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短（相比磁盘的 IO）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指 针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO
真实的情况是，3 层的 b+树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的， 如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。
b. B+Tree索引
（1）B+跟B树不同B+树的非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得B+树每个非叶子节点所能保存的关键字大大增加；
（2）B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样；
（3）B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低
c. full-text全文索引
全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果
d. Hash索引
Hash索引只有Memory，NDB两种引擎支持，Memory引擎默认支持Hash索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储
NoSql采用此中索引结构
（5）索引创建时机
a. 创建索引的情况
-
主键自动建立唯一索引
-
频繁作为查询条件的字段应该创建索引
-
查询中与其他表关联的字段，外键关系应该建立索引
-
单键/组合索引的选择问题， 组合索引性价比更高
-
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
-
查询中统计或者分组字段
b. 不适合创建索引的情况
-
数据重复且分布平均的表字段，不应该为其建立索引（如“性别”字段只有男和女两种值，不应该建立索引，应该只为最经常查询和最经常排序的数据列建立索引）
-
表记录太少
-
经常增删改的表或者字段
-
Where 条件里用不到的字段不创建索引
-
过滤性不好的不适合建索引
3、性能分析
（1）MySQL Query Optimizer
（2）MySQL常见瓶颈
a. CPU
CPU饱和的时候，一般发生在数据装入内存或从磁盘中读取数据的时候
b. IO
实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO
查询执行效率低，扫描过多数据行
c. 服务器
服务器的性能瓶颈，可以使用top、free、iostat和vmstat来查看系统的性能状态
（3）EXPLAIN 的使用
概念
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈
用法： Explain+SQL 语句
Explain 执行后返回的信息：
Explain的作用
表的读取顺序（id）
哪些索引可以使用（select_type）
数据读取操作的操作类型（possible_key）
哪些索引被实际使用(key)
表之间的引用
每张表有多少行被优化器查询（rows）
a. id
select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序
①id 相同，执行顺序由上至下
注意此图中是t1、t3、t2
②id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
③有相同也有不同
id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中， id 值越大，优先级越高，越先执行
衍生 = DERIVED（临时表）
关注点：id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好
b. select_type
select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
| select_type 属性 | 含义 |
|---|---|
| SIMPLE | 简单的 select 查询，查询中不包含子查询或者 UNION（简单的单表查询） |
| PRIMARY | 查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary |
| DERIVED | 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询， 把结果放在临时表里 |
| SUBQUERY | 在SELECT或WHERE列表中包含了子查询 |
| DEPEDENT SUBQUERY | 在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层 都是 where 后面的条件，subquery 查询出来的是单个值，dependentsubquery 是一组值 |
| UNCACHEABLE SUBQUREY | 使用了@@来引用系统变量的子查询，当使用了@@来引用系统变量的时候，不会使用缓存 |
| UNION | 若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为：DERIVED。 |
| UNION RESULT | 从 UNION 表获取结果的 SELECT |
c. table
这个数据是基于哪张表的
d. type
type是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏一次是：
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
一般来说，得保证查询至少达到 range 级别，最好能达到 ref
ALL表示全表扫描，所以结果值最差
上面的比较关系比较难记，工作中常用的如下：
system>const>eq_ref>ref>range>index>ALL
（1）system
表只有一行记录（等于系统表） ，这是 const 类型的特列，平时不会出现，这个也可以忽略不计，实际系统中几乎不可能出现
（2）const
表示通过索引一次就找到了，const 用于比较 primarykey 或者 unique 索引。因为只匹配一行数据，所以很快
因为where中查询条件为t1.id=1，所以该查询类型为const
又因为这样查询出来的记录只有一条，所以上级查询为system
（4）eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
（5）ref
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
- 没用索引前：
- 建立索引后：
（6）range
只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现 了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
（7）index
出现index是sql使用了索引但是没有通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组
（8）all
FullTableScan，将遍历全表以找到匹配的行
e. possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
如果该值为NULL，则表示未使用到索引
f. key
实际使用的索引。如果为NULL，则没有使用索引
g. key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分
如何计算：
①先看索引上字段的类型+长度比如 int=4; varchar(20)=20;char(20)=20
②如果是 varchar 或者 char 这种字符串字段，视字符集要乘不同的值，比如 utf-8 要乘 3，GBK 要乘 2
③varchar 这种动态字符串要加 2 个字节
④允许为空的字段要加 1 个字节
第一组：key_len=age 的字节长度+name 的字节长度=4+1 +(20*3+2)=5+62=67
第二组：key_len=age 的字节长度=4+1=5
h. ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
i. row
rows 列显示 MySQL 认为它执行查询时必须检查的行数，越少越好！
j. Extra
其他的额外重要的信息
-
Usingfilesort：说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”
-
Using temporary：使用了临时表来保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序 order by或者分组 group by
- Using index： 代表表示相应的 select 操作中使用了覆盖索引(CoveringIndex)，避免访问了表的数据行，效率不错！ 如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。
-
Using where：表明使用了 where 过滤
-
**Using joinbuffer：**使用了连接缓存（select * from emp,dept where emp.id=dept.id)
-
impossible where：where 子句的值总是 false，不能用来获取任何元组
-
select tables optimized away：在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操
作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。myisam已经保存了记录的总数，直接返回结果，不需要再进行select查询，而innodb还需要全表扫描
（4）热身案例
4、索引失效
（1）全值匹配
建立索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
进行查询，分别使用1、2、3个索引，查看情况
结论：需要查询的字段和建立的索引越匹配，精度越高，全值匹配查询的字段按照顺序在索引中都可以匹配到
（2）最佳左前缀法则
查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效！
原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用
（3）不要在索引列上做任何计算
不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换） ，会导致索引失效而转向全表扫描。
（1）在查询列上使用了函数
（2）在查询列上做了转换
虽然都能查询出结果，因为Mysql自动做了隐式类型转换，但是字符串不加单引号索引失效
（4）索引列上不能有范围查询
解决方案：将可能做范围查询的字段的索引顺序放在最后
（5）尽量使用覆盖索引
即查询列和索引列一直，不要写 select * !
（6）使用不等于(!= 或者<>)的时候
mysql 在使用不等于(!= 或者<>)时，有时会无法使用索引会导致全表扫描
（7）字段的is not null 和 is null
isnotnull 用不到索引，isnull 可以用到索引
（8）like的前后模糊匹配
前缀不能出现模糊匹配
如何解决%在前缀的时候导致的索引失效？
使用覆盖索引，即建的索引和查询的字段尽量一致
（9）减少使用or
使用 union all 或者 union 来替代：
（10）字符串不加单引号索引失效
底层进行转换使索引失效，使用了函数造成索引失效
例如：
select * from staffs where name=‘2000’
select * from staffs where name=2000
mysql都能查出来，mysql引擎会自动做类型转换，但是索引会失效
（11）结论
全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写*；
不等空值还有 OR，索引影响要注意；
VAR 引号不可丢，SQL 优化有诀窍。
6464
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
