mysql索引优化（一）

性能下降SQL慢 执行时间长 等待时间长 可能原因：

查询数据过多

关联了太多的表，太多join 

没有利用到索引：索引针对 列 建索引。但并不可能每一列都建索引 索引并非越多越好。当数据更新了，索引会进行调整。也会很消耗性能。 且 mysql 并不会把所有索引都用上，只会根据其算法挑一个索引用。所以建的准很重要。

服务器调优及各个参数设置（缓冲、线程数等）

索引：MySQL官方对索引的定义为，索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

你可以简单理解为“排好序的快速查找数据结构”。 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

优势：类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

mysql索引分类：

主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)
当表的行数远大于索引列的数目时可以使用复合索引

基本语法：创建 ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length)) 

                    删除：DROP INDEX [indexName] ON mytable; 

                  查看：SHOW INDEX FROM table_name\G

需要建立索引的情况：主键自动建立唯一索引。频繁作为查询条件的字段应该创建索引(where 后面的语句)。查询中与其它表关联的字段，外键关系建立索引。单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)，查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。查询中统计或者分组字段。

哪些情况不要创建索引：表记录太少，

                                       经常增删改的表：Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

                                        Where条件里用不到的字段不创建索引

                                        数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

mysql常见的瓶颈：

cpu:SQL中对大量数据进行比较、关联、排序、分组

io:实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO。查询执行效率低，扫描过多数据行。

锁：不适宜的锁的设置，导致线程阻塞，性能下降。死锁，线程之间交叉调用资源，导致死锁，程序卡住。

或者服务器硬件性能不行。

优化原理：

可以通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

通过explain出的数据进行分析得出如下信息：

表的读取顺序，哪些索引可以使用，数据读取操作的操作类型，哪些索引被实际使用，表之间的引用，每张表有多少行被优化器查询

名次段解释：

id:

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上至下,id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在.id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type:

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

1.SIMPLE 简单的 select 查询,查询中不包含子查询或者UNION

2.PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为Primary

3.DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。

4.SUBQUERY 在SELECT或WHERE列表中包含了子查询 

  5.DEPENDENT SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询基于外层

6.UNCACHEABLE SUBQUREY 无法被缓存的子查询

7.union 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

8.union result 从UNION表获取结果的SELECT

type：
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是： 

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL 

system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
 

system:表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

const:表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref:非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，
它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体.

range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

index:Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

all:Full Table Scan，将遍历全表以找到匹配的行

index_merge:在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中

ref_or_null:对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

index_subquery:利用索引来关联子查询，不再全表扫描。

unique_subquery :该联接类型类似于index_subquery。 子查询中的唯一索引

possible_keys:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key:实际使用的索引。如果为NULL，则没有使用索引.查询中若使用了覆盖索引，则该索引和查询的select字段重叠

key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 

key_len字段能够帮你检查是否充分的利用上了索引

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows：rows列显示MySQL认为它执行查询时必须检查的行数。越少越好

exra:包含不适合在其他列中显示但十分重要的额外信息

Using filesort :说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”

给ename加上排序（0.00s）即在索引中排序

Using temporary ：使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
 create index idx_deptno_ename on emp(deptno,ename) 后解决

USING index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

覆盖索引：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。①一个索引 ②包含了(或覆盖了)[select子句]与查询条件[Where子句]中 ③所有需要的字段就叫做覆盖索引。

例如：

select id , name from t_xxx where age=18;
         有一个组合索引  idx_id_name_age_xxx 包含了(覆盖了)，id,name,age三个字段。查询时直接将建立了索引的列读取出来了，而不需要去查找所在行的其他数据。所以很高效。
         (个人认为：在数据量较大，固定字段查询情况多时可以使用这种方法。)
注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
Using where：过滤where

using join buffer：使用了连接缓存

出现在当两个连接时驱动表(被连接的表,left join 左边的表。inner join 中数据少的表) 没有索引的情况下。给驱动表建立索引可解决此问题。且 type 将改变成 ref

impossible where：where子句的值总是false

索引失效案例：

1.全值匹配最好

2.最佳左前缀法则，如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

（带头大哥不能死，不能跳过中间列）

 and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在，会自动优化。
经过试验结论  建立了 idx_nameAge 索引  id 为主键
    1.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。
    既 select 后的字段 有索引，where 后的字段也有索引，则无关执行顺序。
    2.除开上述条件 才满足最左前缀法则。

3.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select 

6.mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

使用 != 和 <> 的字段索引失效( != 针对数值类型。 <> 针对字符类型
前提 where and 后的字段在混合索引中的位置比比当前字段靠后  where age != 10 and name='xxx'  ,这种情况下，mysql自动优化，将 name='xxx' 放在 age ！=10 之前，name 依然能使用索引。只是 age 的索引失效)

7.is not null 也无法使用索引,但是is null是可以使用索引的

8.like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

 like ‘%abc%’  type 类型会变成 all
like ‘abc%’ type 类型为 range ，算是范围，可以使用索引

如果真的要查%xx%就用覆盖索引，其中主键也会用到index。

9.字符串不加单引号索引失效

10.少用or,用它来连接时会索引失效

一般性建议:对于单键索引，尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)

在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

优化总结口诀：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上无计算，范围之后全失效；

like百分加右边，覆盖索引不写星*

不等空值还有or，索引失效要少用；

字符串里有引号，SQL高级也不难