MySQL索引优化

1.sql性能问题
    a.分析sql执行计划：explain，可以模拟sql优化器执行sql语句，从而让开发人员知道自己编写的sql状况
    b.Mysql查询优化其会干扰我们的优化
    解析过程：
            from .. on .. join .. where .. group by .. having .. select distinct/dɪ'stɪŋkt/ .. order by .. limit ..
    
    查询执行计划：explain + sql语句
    参数说明：
        id:编号；id值越大越优先，id值相同，从上往下顺序执行。
        select_type:查询类型
        table:表
        type:类型
        possible_keys:预测用到的索引
        key:实际使用的索引
        key_len:实际使用索引的长度
        ref:表之间的引用
        rows:通过索引查询到的数据量
        Extra:额外的信息

2.selct_type
    a.PRIMARY 包含子查询sql中的 主查询（最外层）
    b.SUBQUERY 包含子查询sql中的 子查询（非最外层）
    c.simple：简单查询（不包含子查询，union）
    d.union
    e.union result：告知开发人员，那些表之间存在union查询
    f.derived:衍生查询(使用到了临时查询)  
       
3.type：索引类型、类型(7种)
    system>const>eq_ref>ref>range>index>all 性能高>性能低；要对type进行优化的前提：要有索引
    其中：system，const只是理想情况；实际能到达ref>range
    system：只有一条数据的系统表；或衍生表只有一条数据的主查询
    const:仅仅能查到一条数据的sql，用于Primary key 或 unique索引（类型与索引类型有关）
    eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有一个，不能多、不能0没查到） 
    ref:非唯一性索引，对于每个索引键的查询，返回匹配的所有的行（0，多）
    range:检索指定范围的行，where后面是一个范围查询(between,in(有时会索引失效，从而转为无索引all),>,<,>=,<=)        
    index:查询全部索引中的数据（查询的单列有索引，只需要扫描索引表，不需要所有表中的数据）
    all:查询全部表中的数据（需要表中的所有数据）

4.possible_keys:可能使用到的索引，是一种预测，不准。

5.key:实际使用到的索引

6.key_len:索引字节数，经常判断复合索引是否被完全使用；在mysql中utf8一个字符占3字节
    如果索引字段可以为null，则会使用一个字节用于标识；如果索引字段为可变长度（varchar），用2字节标识。
    
7.ref：注意与type中的ref值区分
    作用：指明当前表所参照的字段，如果是常量就是conset，前提是字段要有索引。
    
8.rows：被索引优化查询的数据个数  （通过索引查询到的数据个数）

9.Extra：
    排序：先查询  结果集
    a.using filesort：性能消耗大；需要额外一次排序（查找）
        单索引：如果排序和查询的是统一字段，则不会出现using filesort；反之亦然。  避免：where那些字段就order by那些字段；
        复合索引：不能跨列（最佳左前缀）  避免：where和order by 按照复合索引的顺序使用，不要跨列或无序使用（where和order by拼起来，去掉where失效的列）
            create index a1_a2_a3_index on tb(a1,a2,a3);
            explain select * from tb where a1='' order by a3;--using filesort 垮了a2列
            explain select * from tb where a2='' order by a3;--usring filesort 跨了a1
            explain select * from tb where a1='' order by a2;--ok的
    b.using temporary:性能损耗大，用到了临时表，一般出现在group by语句中。已经有了表了，但不适合，必须在来一张表。
        避免：查询那些列，就根据那些列group by。
    c.using index：性能提升；索引覆盖（覆盖索引）。原因：不读取原文件，只从索引中获取数据（不需要回表查询）
        1)、只要使用到的列，全部都在索引中，就是索引覆盖using index（多个列就看复合索引）
        2)、如果用到了索引覆盖，会对possible_keys和key造成影响；如果没有where，则索引只出现在key中，如果有where，则索引出现在key和possible_keys中。
    d.using where：需要会原表查询
    e.impossible where:where子句永远为false
    
11.单表优化

12.多表优化：小表驱动大表（...on t.cid = c.tid t表的数据少 ），左连接给左表加索引，右连接给右表加索引。where条件必须加索引。
    对于多层循环来说：一般建议将次数少的循环放外层，次数多的放内层（编程语言中，这样做程序效率越高，程序优化原则。）
    总结：
        a.小表驱动大表（小表放左边）
        b.索引建立在经常查询的字段上

13.using join buffer:mysql引擎使用了连接缓存。

14.索引失效的一些原则：可以通过key_len检查索引是否失效。
    a.复合索引，不要跨列或无序使用（最佳左前缀）；左边失效，右边全失效。单独索引没有。
        索引（a,b,c,d）:
            b失效：c、d都失效，a有效。
            c失效：d失效，a、b有效。
    b.复合索引，尽量使用全索引匹配（索引字段尽量都用上）
    c.不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效
        ... where t.id * 3 = '' 对于索引id来说进行了计算，索引失效
    d.复合索引不能使用不等于(!= <>) 或is null(is not null),否则自身以及右侧索引全部失效。 
    e.尽量是用using index索引覆盖
    f.like尽量以'常量'开头，不要以'%'开头，否则索引失效。不用*索引覆盖可以补救一下
    g.尽量不要使用类型转换（显示、隐式），否则索引失效
        ... where name = 123 ;//程序底层将 123转换为'123',即类型转换索引失效
    h.尽量不要使用or，否则索引失效。左侧的索引都将失效    
    
15.sql优化，是一种概率层面的优化，至于是否实际使用了我们的优化，需要通过explain进行推测。原因在于：服务层sql优化器。  

16.一些其它的优化方法：exists、in(子查询)
    a.如果主查询的数据集大，则使用in。
    b.如果子查询的数据集大，则使用exists
    
17.order by:
    a.using filesort两种算法:双路排序、单路排序(根据IO的次数)。
    b.mysql4.1前是使用双路排序（扫描两次磁盘）：
        第一次：从磁盘读取排序字段，对排序字段进行排序（在buffer缓冲中排序）；IO较消耗性能
        第二次：扫描其它字段
    c.MYsql4.1之后默认使用单路排序：只读取一次（去不字段），在buffer中进行排序。但单路排序会有一定弊端（不一定真的是“单路|一次IO”，有可能多次IO）；原因：如果数据量特别大，则无法将所有数据一次性读取完毕，因此会进行“分片读取｜多次读取”。注意：单路排序比双路排序占用更多的buffer。
    d.单路排序使用时，如果数据量大，可以考虑调大buffer的容量大小：set max_length_for_sort_data = 1024 （单位时byte）
    e.如果max_length_for_sort_data值太小，MySQL会自动从单路➡双路（太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）
    f.总结：
        1.选择使用单路，双路;调整buffer的容量大小
        2.避免*的使用：使用*会计算*是什么，也很难使用索引覆盖
        3.复合索引不要跨列使用、无序是使用
        4.保证全部的排序字段排序的一致性（都是升序、或降序）
        
18.SQL排查 - 慢查询日志：MySQL提供的一种日志记录，用于记录MySQL中响应超过阀值的sql语句（long_query_time,默认10秒）
    a.慢查询日志默认是关闭的：建议，开发调优时打开，上线最终关闭。
    b.检查是否开启慢查询日志：show variables like '%slow_query_log%';
    c.开启：
        临时开启：set global slow_query_log = 1;#在内存中开启，关闭服务|重启服务后失效
        永久开启：
            在配置文件(/etc/my.cnf,windows中my.ini中)中追加配置：
                [mysqld]
                slow_query_log=1
                slow_query_log_file=/.../file.log
    d.查询慢查询阀值：show variables like '%long_query_time%';
        临时设置阀值：set golbal long_query_time = 5;#修改完毕，需要重新登录（不需要重启服务）
        永久设置阀值：
            在配置文件(/etc/my.cnf,windows中my.ini中)中追加配置：
                [mysqld]
                long_query_time=5        
    e.查询超过阀值的sql：show global status like '%slow_queries%';
    f.查看日志文件定位具体的sql
    g.通过mysqldumpslow工具查看慢sql

19.分析海量数据：
    a.profiles
        show profiles;#默认关闭
        show variables like '%profiling%';
        set profiling = on;
        show profiles : 会记录所有profiling打开之后的全部sql查询语句所花费的时间。缺点：不够精确。
    b.精确分析：sql诊断
        show profile all for query queryId(show profiles查询到的sqlId)
    c.全局查询日：记录开启之后的全部sql语句:mysql.general_log表中。（全局的记录操作在开发中使用，生产环境一定要关闭，很消耗资源）        
        show variables like '%general_log$%';
        set global general_log = 1;
        set global log_output ='table';#这只将sql记录在表中
        set global general_log_file = '/path';
        select * from mysql.general_log;