1、MySQL第一章，索引优化
一、数据库性能下降的原因
两方面原因：执行时间长、等待时间长
1）、select语句写的烂
2）、索引失效（索引就是预先检索和排序）（单值、复合 ）
3）、关联查询，太多join（设计原因）
4）、服务器调优、各个参数的设置（缓冲、线程数等）
二、SQL执行顺序
FROM ON JOIN WHERE GROUP BY HAVING SELECT DISTINCT ORDER BY LIMITE
三、索引分类
1）、单值索引--单个列
2）、唯一索引--索引列的值必须唯一，但允许有空
3）、复合索引--一个索引包含多个列
四、基本语法
创建 create 【unique】 index [indexName] on [tableName] (columnName)
创建 alter table [tableName] add 【unique】index [indexName] (columnName)
删除 drop index [indexName] on [tableName]
查看 show index from [tableName]
（索引建的多了：增删改会变慢）
五、索引结构
BTree Hash Full-Text全文索引 R-Tree
六、适合创建索引的情况
1）、主键自动创建唯一索引
2）、频繁作为查询条件的字段
3）、查询中与其他表关联的字段，外键关系创建索引
4）、频繁更新的字段不适合创建索引
5）、where条件中用不到的字段不需要创建索引
6）、和单值索引相比最好创建组合索引
7）、查询中排序字段，排序字段若通过索引去访问将大大提高排序速度
8）、查询中统计和分组的字段
七、不适合创建索引的情况
1）、表记录太少
2）、经常增删改的表的字段
3）、字段中数据差异率和重复率不高、没必要创建索引（例如民族、国际、性别）
八、explain
1）、表的读取顺序
2）、数据读取操作的操作类型
3）、那些索引可以使用
4）、那些索引被实际使用
5）、表之间的引用
6）、每张表有多少行被优化器查询
id type key是关键的衡量指标
8.1、explain之id
表示执行select子句或操作表的顺序
1）、id相同：执行顺序由上而下
2）、id不同：id值越大优先级越高，越先被执行。
3）、id相同和不同同时存在：id相同的可以认为是一组的由上至下执行，id不同的越大优先级越高，越先执行
8.2、explain之select_type
表示查询类型：用于区分普通查询、联合查询、子查询等
1）、simple：不包含任何子查询或union
2）、primary：查询中若包含任何复杂的子部分，最外层查询则被标记为primary（同subquery同时存在）
3）、subquery
4）、derived衍生：（临时表？）
5）、union
6）、union result：（union合并的结果集）
8.3、explain之table
表示查询中所使用到的表
8.4、explain之type
查询类型排序：
从最好到最差的顺序依次是：system>const>eq_ref>ref>range>index>all
一般来说达到range和ref级别就好
1）、system:表只有一行记录（等于系统表），const类型的特例，平时不会出现，可以忽略不计
2）、const：表示用过索引依次就可找到，用于比较primary key或unique索引。应为只匹配一行数据，所以查询很快。
3）、eq_ref：唯一索引扫描，对于每个索引建，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
4）、ref
5）、range
6）、index full index scan，index与all的区别是index只遍历索引树，通常比all快。
7）、all
8.5、explain只possible_keys和key介绍
possible_key显示可能应用到这张表中的索引（一个或多个）---查询字段上存在索引，则该索引被列出（不一定被使用）
key显示实际使用的索引，如果为NULL,则没有使用任何索引---查询中若出现覆盖索引，则该索引出现在key列表中
（覆盖索引：select的字段和索引中所使用的字段一致）
8.6、key_len
表示索引中使用的字节数（使用的索引的长度），在不损失精确性的情况下，长度越短越好
8.7、ref
显示索引的那一列被使用了，如果可能的话，最好是一个常数，那些列或常量被用于查找索引列上的值
8.8、rows
根据表统计信息和索引选用情况，大致估算出找到所需记录所需读取的行数。
8.9、extra
十分重要的额外信息
（1）、using filesort：mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取（文件排序）---自己重新排序
（最好不要出现using filesort）
（2）、using temporary：新建了内部的临时表保存临时结果，常见于order by和group by
（十死一生，千万不要出现）
（3）、using index：使用索引默认的排序
表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错
如果同时出现using where，表明索引被用来索引键值的查找
如果没有同时出现using where，表明索引被用来读取数据而非执行查找动作
（这种情况非常好，不用再排序了）
（4）、using where：使用了where过滤（去表里边检索，而非索引里边---查询字段里包含索引列中不存在的字段）
（5）、using join buffer：使用了连接缓存
（6）、impossible where：表示where后的查询条件为FALSE不能获取任何值（如：name="a" and name="b"）
九、索引案例：
range后的索引会失效，解决：将range的字段从索引中删去
（1）单表优化
（2）两表优化
left join right join 将索引建立在副表上（比如：A left join B就应该把索引建立在B上）
注意：
Join语句优化：
（1）、尽量减少join语句中的循环总次数：-----永远用小的结果集去驱动大的结果集
（2）、有子查询，优先优化内层循环
（3）、保证join语句中被驱动表上的join条件字段已经被索引。
十、索引的优化 以及 如何避免索引失效
索引失效情况
10.1、最佳左前缀法则
如果索引了多列，要遵循最左前缀法则。指的是查询从最左前列开始并且不能跳过索引中的列：
如：建立索引的顺序是NAME AGE POS
where 后 是 AGE POS 丢失了NAME索引会失效，即左边的列不能丢失，且顺序不能变
口诀（带头大哥不能死）
如果where后是 NAME POS，索引索然存在，但只是部分使用，即只有NAME会使用到索引，AGE的索引丢失
口诀（中间兄弟不能丢）
10.2、不要在索引列上做任何操作
（操作如：计算、函数、自动或手动的类型转换等，会导致索引失效而转向全表扫描）
口诀：索引列上少操作
10.3、范围之后全失效
（即 范围类型的索引列后边的索引列字段 全部失效）
口诀：范围之后全失效
（NAME AGE都用到了索引，但是POS的索引失效）
10.4、尽量使用覆盖索引（索引列和查询列一致），减少 select * 使用
10.5、MySQL在使用不等于（！=或<>）的时候无法使用索引，会导致全表扫描
10.6、IS NULL或IS NOT NULL也无法使用索引
10.7、like以通配符开头（‘%abc’）MySQL索引失效，会变成全表扫描
口诀：like%加右边，两边%用覆盖
（like查询如果在右边写%,会变成范围range查询----但这个range和普通的range不一样，这个range后边的索引字段不会失效）
注：
如何解决like ‘%ABC%’导致索引失效？
用覆盖索引代替 * 可以防止索引失效
10.8、字符串不加单引号索引失效
10.9、少用or,用它来连接时会导致索引失效
索引练习：
只要where里有 顺序变没问题
十一、面试题分析
1、查询顺序变化，索引不会失效
2、范围之后全失效
3、c3用于了排序，断了所以C4的索引没有用到
4、C1、C2用到了索引，但跳过了C3，所以C4的排序会造成 查找到记录后，重新排序的额外损失
