Mysql索引优化

哪些情况需要创建索引？
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他关联的字段没外键关系建立索引
4.频繁更新的字段不适合创建索引-=每次更新不仅更新记录还会更新索引
5.where条件里用不到的字段不创建索引
6.单键/组合索引的选择问题，who?（高并发下倾向创建组合索引）
7.查询中排序的字段，排序字段若通过索引去访问将大大提升排序速度
8.查询中统计或者分组字段

哪些情况不要创建索引？
1.表记录太少
2.经常增删改的表-=why?提高了查询速度，同时却降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
数据重复且分布平均的表字段，因此应该只为最经常查询和经常排序的数据列建立索引。
3.注意：，如果数据列包含许多重复的内容，建立索引没有太大的效果
（索引的选择性指索引列中不同值的数目与表的记录数的比。越接近于1，索引效率越高）

性能分析：
Mysql查询优化器
Mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中的统计信息，为客户端请求的Query提供最优的执行计划
Mysql常见瓶颈：
1.cpu在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时
2.IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
3.服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态

Explain:
1.是什么（查看执行计划）
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈
explain select * from tbl_emp;
2.能干嘛
3.怎么用
1)explain + SQL语句
2）执行计划包含的信息===id select_type  table type possible_keys key key_len ref rows Extra
4.用法：
1）表的读取顺序
2）数据读取操作的操作类型
3）哪些索引可以使用
4）哪些索引可以被实际使用
5）表之间的引用
6）每张表有多少航被优化器查询
5.怎么玩：
1)explain + SQL语句
2）执行计划包含的信息===id select_type  table type possible_keys key key_len ref rows Extra
===================id====================================
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
**id相同，执行顺序由上至下**
**id不同，如果是子查询，id的序号递增，id值越大优先级越高，优先被执行 **
**id相同不同，同时存在(id相同，可以人为是一组，从上往下顺序执行，id值越大优先级越高，优先被执行)**
explain select t2.* from (
select t3.id from t3
where t3.other_column = '') s1,t2
where s1.id = t2.id;(derived)

===================select_type================================
1）id select_type
1 SIMPLE：简单的select查询，查询中不包含子查询或者UNION
2 primary：查询中若包含任何复杂的子部分，最外层查询被标记为primary
3 subquery：select或where列表中包含了子查询
4 derived：在from列表中包含的子查询被标记为(derived)衍生表，Mysql会递归执行这        些子查询，把结果放在临时表里。
5 union：若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT被标记为DERIVED
6 union result：从UNION表获取结果的SELECT
2）查询类型，主要用于区别：普通查询、联合查询、子查询等的复杂查询

===================type================================
与sql是否优化相关
ALL index range ref eq_ref const,system NULL
访问类型排列
显示查询使用率何种类型，从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL
system-=表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
const-=表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快将主键置于where列表中，Mysql就能将该查询转换为一个常量
> 上述只有一条记录，用处不大
eq_ref-=唯一性索引扫描，对每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref-=非唯一性索引扫描，返回匹配某个单独值的所有行
本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体
create index idx_col1_col2 on t1(col1,col2);
select count(distinct col1) from t1;
explain select * from t1 where col1 = 'ac';
range-=只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
index-=Full Index Scan,Index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但是index是从索引中读取的，而all是从硬盘中读的）
explain select id from t1;
ALL-=Full Tabel Scan,将遍历全表以找到匹配行
explain select * from t1 where column_without_index = '';
备注：一般来说，得保证查询至少达到range级别，最好达到ref

possible_keys-=显示可能应用在这张表中的索引，一个或多个；查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key-=1.实际使用的索引。如果为NULL，则没有使用索引 2.查询中若使用了覆盖索引，则该索引将出现在key列表中

覆盖索引
create index idx_col1_col2 on t2(col1,col2);
explain select col1,col2 from t1;查找的字段与创建的复合索引一致，则会使用覆盖索引
key_len-=表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损    失精确性的情况下，长度越短越好。 key_len显示的值为索引字段的最大可能长度，并非实际使用过长度，即key_len是根据表定义计算而得，不是通过表内检索出的
explain select * from t1 where col1 = 'ab'; key_len = 13
explain select * from t1 where col1 = 'ab' and col2 = 'ac'; key_len = 26
同样的查询效果精度越小越好

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

rows-=根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要的读取的行数
       表有多少行被优化器查询  ， 行数越少越好
id type key rows Extra
Extra-=
1)Using filesort -= 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。Mysql中无法利用索引完成的排序操作成为“文件排序”
九死一生  影响性能
explain select col1 from t1 where col1 = 'ac' order by col3\G
Extra:Using where;Using index;Using filesort
explain select col1 from t1 where col1 = 'ac' order by col2, col3\G
Extra:Using where;Using index
2） Using temporory  影响性能
使用了临时表保存中间结果，Mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by.(统计分析通常是速度慢的原因
explain select col1 from t1 where col1 in ('ac','ab','aa') group by col2\G
Extra:Using where;Using index;Using temporory;Using filesort
explain select col1 from t1 where col1 in ('ac','ab') group by col1 col2\G
Using where;Using index for group by
3)Using index 
表示相应的select操作中使用了覆盖索引（Convering Index），避免访问表的数据行，效率不错！ 如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
覆盖索引
> 理解一：select的数据列只用从索引中就能取得，不必读取数据行，Mysql可以利用索引返回select列表的字段，而不必根据索引在此读取数据文件，**换句话说查询列要被所建索引覆盖**
注意：使用覆盖索引，一定注意select列表中只取出所需要的列，不可select *

4）Using where:表使用了where过滤 
5)using join buffer：使用了连接缓存
6）impossible where:where子句的值总是false，不能来获取任何元组
7）select tables optimized away
8）distinct:优化distinct操作，找到第一匹配的元组停止找同样值的动作

索引优化：
索引失效(失效:
1.全值匹配：所有字段信息给出后查找
2.最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列（若跳过，那么只有前面部分字段产生索引）。
3.不能在索引列上做任何操作（计算、函数、（自动or手动）类型转换），否则会导致索引失效而转向全表扫描
explain select * from staffs where NAME = 'July';
explain select * from staffs where left(NAME,4) = 'July';(索引失效
4. 存储引擎不能使用索引中范围条件右边的列，范围之后全失效
EXPLAIN select * from staffs where NAME = 'July' AND age > 25 and pos = 'manager';
5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select * 
EXPLAIN select * from staffs where NAME = 'July' AND age = 25 and pos = 'manager';
EXPLAIN select name,age,pos from staffs where NAME = 'July' AND age = 25 and pos = 'manager'; (Using index)
6.mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
explain select * from staffs where NAME != 'July';
7.is null,is not null也无法使用索引
8.like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描的操作
like%放右边
select * from staffs where name like '%July%';
select * from staffs where name like 'July%';
问题：解决like '%字符%' 时索引不被使用的方法？ 
1）通过主键使用索引
Explain select id from tb1_user where name like '%aa%'; 
2）覆盖索引(name_age组合索引)
Explain select name,age from tb1_user where name like '%aa;
Explain select age from tb1_user where name like '%aa;
Explain select name from tb1_user where name like '%aa;
9.字符串（varchar）不加单引号索引失效
select * from staffs;
(explain) select * from staffs where name = '2000';
select * from staffs where name = 2000;
10.少用or，用它来连接时会索引失效
explain select * from staffs where name='July' or name='z3';索引失效

1.带头大哥不能死，2.中间兄弟不能断，==永远符合最佳左前缀原则 3.索引列上无计算{函数、（自动/手动）类型转换}4.like百分号放右边 5.范围之后全失效 6.字符串里有有引号