一、索引优化分析
性能下降sql慢、执行时间长、等待时间长
①查询语句有问题
②索引失效
③关联查询太多join
④服务器调优及各个参数设置
SQL执行顺序
①手写顺序
SELECT DISTINCT
<select_list>
FROM
<left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT <limit_number>
②机读顺序
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
常见join查询
索引简介
1、简介
索引是帮助MySQL高效获取数据的数据结构，是一种排好序的快速查找数据结构；一般来说索引也很大，不可能全部存储在内存中，因此往往以索引文件的形式存储在磁盘上，平常所说的索引，不特殊说明，都是指B树（多路搜索树）。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。下图就是一种可能的索引方式示例：
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
2、优势
①类似于书目索引，提高数据检索效率，降低数据库IO成本
②通过索引列对数据进行排序，降低数据排序成本，降低CPU消耗
3、劣势
①实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也要占空间的
②虽然索引提高了查询速度，却会降低更新速度。因为更新表时，不仅要保存数据，还要调整因更新而变化的索引信息
4、MySQL索引分类
①单值索引：一个索引只包含单个列
②唯一索引：索引列的值必须唯一UNIQUE，但允许有空值
③复合索引：一个索引包含多个列
5、MySQL索引结构
①BTree索引
②Hash索引
③full-text索引
④R-Tree索引
6、哪些情况需要创建索引
①主键自动创建唯一索引
②频繁作为查询条件的字段应该创建索引
③查询中与其它表关联的字段、外键关系建立索引
④频繁更新的字段不适合创建索引，因为每次除了要更新数据，还要更新索引
⑤where条件里用不到的字段不创建索引
⑥单值索引和组合索引，优选组合索引
⑦查询中排序的字段，排序字段若通过索引去访问，大大提高排序速度（要考虑索引字段和有order by需求的字段）
⑧查询中统计或分组的字段，group by的前提是分组
7、哪些情况不需要创建索引
①表记录太少
②经常增、删、改的表
③如果某个数据列包含许多重复的内容，建索引没什么效果
假如一个表有10万行记录，有一个字段S只有F和M两种值，且每个值的分布概率大约为50%，那么对这种表S字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近1，这个索引的效率就越高。
性能分析
1、作用：
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句
①表的读取顺序
②数据读取操作的类型
③哪些索引可以使用
④哪些索引被实际使用
⑤表之间的引用
⑥每张表有多少行被优化器查询
2、用法
EXPLAIN + SQL
3、执行计划包含的信息
①id
select查询的序列号，表示查询中执行select子句或操作表的顺序
三种情况：
a.id相同，执行顺序由上至下
b.id不同，如果是子查询，id的序号会递增，id值越大优先级越高
c.id既有相同，又有不同：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
②select_type
查询类型，主要用于区别普通查询、联合查询、子查询等复杂：
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
显示这一行的数据是关于哪张表的
④partitions
如果查询是基于分区表的话，会显示查询将访问的分区
⑤type
显示查询使用了何种类型，从最好到最差依次是：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，与all的区别是，只遍历索引树，通常比all快，因为索引文件比数据文件小。index是从索引中读取，all是从硬盘中读
all——Full Table Scan，将遍历全表以找到匹配的行
⑥possible_keys
显示可能应用到这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用
⑦key
实际使用的索引，如果为NULL，则没使用索引；查询中若使用了覆盖索引，该索引仅出现在key列表中
⑧key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精度的情况下，长度越短越好；key_len显示的值为索引字段的最大可能长度，并非实际使用长度，是根据表的定义计算得到，不是通过表内检索出的
⑨ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
⑩rows
根据表统计信息及索引选用情况，大致估算出找到所需记录需要读取的行数
⑪filtered
表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数
⑫Extra
额外的信息说明
Using filesort——说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”
Using temporary——使用了临时表保存中间结果，MySQL在对查询结果排序时，使用临时表。常见于排序order by和分组查询group by
Using index——表示相应的select操作使用了覆盖索引，避免了访问数据行（覆盖索引：select的数据列只用从索引中就能取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，也就是查询列要被所建的索引覆盖）
Using where——使用了where过滤
using join buffer——使用了连接缓存
impossible where——where sex= 'm' and sex= 'female'，where值为false
select tables optimized away
distinct
索引优化
e.g. ALTER TABLE exampletable ADD INDEX idx_exampletable_nameAgeAddress(name,age,address);
1.全值匹配
2.最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指查询从索引的最左列开始，并且不跳过索引中的列
①符合最佳左前缀，用上了三个索引：EXPLAIN SELECT * FROM exampletable WHERE NAME='xxx' AND age = 20 AND address='nanjing';
②一个索引都没用上：EXPLAIN SELECT * FROM exampletable WHERE age = 20 AND address='nanjing';
③只用上了第一个NAME索引：EXPLAIN SELECT * FROM exampletable WHERE NAME='xxx' AND address='nanjing';
④能用到NAME的索引：EXPLAIN SELECT * FROM exampletable WHERE NAME='xxx';
⑤能用到NAME和age的索引：EXPLAIN SELECT * FROM exampletable WHERE NAME='xxx' AND age = 22;
3.不要在索引列上做任何操作（计算、函数、自动或手动的类型转换），会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
①三个索引，第二个是范围条件，第二个索引只用到了排序，没用到检索。且第二个之后的索引都失效了：EXPLAIN SELECT * FROM exampletable WHERE NAME='xxx' AND age >25 AND address='nanjing';
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select*
6.mysql在使用不等于(!=或<>)时无法使用索引会导致全表扫描
①无法使用索引：EXPLAIN SELECT * FROM exampletable WHERE NAME != 'xxx'; （使用覆盖索引好像也能解决？）
7.is null，is not null也无法使用索引
①无法使用索引：EXPLAIN SELECT * FROM exampletableWHERE NAME is null; （使用覆盖索引好像也能解决？）
8.like以通配符开头('%abc...')mysql索引失效会变成全表扫描
%写在左侧或两侧都写会使索引失效，写在右边不会失效，但可能不满足业务要求，查不到数据，如果必须在两边都写%，那么可以使用覆盖索引来解决索引失效的问题。