索引优化原则

1.explain（sql执行计划） 使用与详解

id:是select的序列号，有几个select就有几个id，，并且id的顺序是按照select出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为null则最后执行。

select_type:表示对应行时简单查询还是复杂查询。

        simple：简单查询。查询不包含子查询和union；

        primary：复杂查询中最外层的select；

        subquery：包含在select中的子查询（不在from中）；

        derived：包含在from中的子查询。mysql会将结果放在一个临时表（派生表）里。

table:查询的表名，子查询会显示<derivenN>,N为以来的id=N的查询。

partitions:分区

type:表示关联类型或访问类型，即mysql决定如何查找表中的行，查找数据行记录的大改范围。范围从最优到最差为：system>const>eq_ref>ref>range>index>ALL(全表扫描)，一般来说要达到range，最好是ref。

        NULL表示mysql能够在优化阶段分解查询语句，在执行阶段不需要再访问表或索引。

        const，system：mysql能对查询的某部分进行优化并将其转化为一个常量（show warning结果）。用于primary key或 unique key的所有列和常数比较时，所有表最多有一个匹配行，读取一次，速度比较快。system是const的特例，表里只有一条元组匹配时为system。

        eq_ref：primary_key或unique_key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在const之外最好的关联类型，简单的sql查询不会出现这种type；

        ref：相比于eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

        range：范围扫描，通常出现在in（），between ，>，<，>=等操作中，使用一个索引来检索给定范围的行。

        index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点便利和扫描，速度较慢，一般使用覆盖索引，二级索引一般比较小，会比ALL快一些。

        ALL：全表扫描。

possible_keys:显示sql在执行中，可能会使用那些索引来查找。

key:在sql执行过程中，实际使用到的索引。

key_len:显示mysql在索引中使用的字节数。如果是联合索引，通过这个值可以算出具体使用了索引中的那些列。

        计算规则如下：

        字符串：char（n） n字节长度

                      varchar（n）utf-8 长度为3n+2字节

        数值：tinyint：1字节

                   smallint：2字节

                   int：4字节

                    bigint：8字节

        时间：date：3字节

                   timestamp：4字节

                   datatime：8字节

        null：1字节（允许为null时，需要1字节来记录是否为null）

ref:显示再key列记录的索引中，表查找值用到的列或常量，如counst（常量），字段名

rows:sql估计要读取并检测的行数，不是结果集的行数。

filtered:（rows*filtered/100  可以估算与其他表的关联行数）

Extra:额外信息

        using index：使用覆盖索引（mysql执行计划explain结果中的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引。）

        using where：使用where语句来处理结果，查询的列未被索引覆盖

        using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

        using temporary：创建一张临时表来处理查询。出现这种情况一般是要进行优化的，加索引。

        using filesort：用外部排序而不是索引排序，数据较小时从内存排序，否则在磁盘中排序，需加索引。

        select tables optimized away：使用某些聚合函数（max，min）来访问存在索引的某个字段。

2.show wornings；

warning：预警信息

note：mysql执行sql时内部优化后的内容。

 3.sql执行匹配索引规则

全值匹配；

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

不在索引列上做任何操作（如函数，计算，类型转换（自动或手动都不行）），否则会导致索引失效进而全表扫描。

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

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句。

mysql使用不等于（！=或<>）时会导致索引失效。

is null，is not null 也会使索引失效。

like通配符（%）开头会导致索引失效。

字符串类型要加单引号，否则会导致索引失效。

少用or或in，也可能会导致索引失效。

范围查询优化

（1）大范围拆分为小范围查找。

联合索引第一个字段用范围不会走索引。

强制走索引（force index（索引名））

4.trace工具