Mysql笔记

索引
    索引是帮助数据库高效获取数据的数据结构，索引的本质是排好序的快速查找数据结构    

索引分类
    单值索引
        一个索引只包含单个列，一个表可以有多个单列索引
    唯一索引
        索引列的值必须唯一，但允许有空值
    复合索引
        一个索引包含多个列

查看索引：show index from table_name;

mysql索引结构
    BTree索引
    Hash索引
    full-text全文索引
    R-Tree索引

哪些情况下需要创建索引
    1.频繁作为查询条件的字段应该创建索引
    2.查询中与其他表关联的字段，外键关系创建索引
    3.查询中排序的字段，排序字段若通过索引访问将大大提高排序速度
    4.查询中统计或者分组字段

哪些情况下不要创建索引    
    1.表记录太少
    2.频繁更新的字段不适合创建索引
    3.where条件里用不到的字段不创建索引
    4.某个字段重复且分布平均，例如性别
    
SQL性能分析
    执行计划 explain
        表的读取顺序
        数据读取操作的操作类型
        哪些索引可以使用
        哪些索引被实际使用
        表之间的引用
        每张表有多少行被优化器查询
    explain  id
        反映表的加载顺序
        
        1.id相同，执行顺序从上到下
        2.id不同，如果是子查询(SUBQUERY)，id的序号会递增，id值越大优先级越高,越先被执行
        3.id有相同也有不同
    
    explain select_type
        反映查询的类型，主要是用于区别 普通查询、联合查询、子查询等
        
        SIMPLE   简单的select查询，查询中不包含子查询或者union
        
        PRIMARY  查询中如包含任何复杂的子部分，最外层查询则被标记为PRIMARY
        
        DERIVED     在from列表中包含的子查询被标记为DERIVED(衍生)，mysql会递归执行这些子查询，把结果放在临时表里
            
        SUBQUERY 在select或where中包含了子查询
        
        UNION      若第二个select出现在UNION之后，则被标记为UNION
        
        UNION RESULT    从UNION表获取结果的SELECT，2种UNION的合并
        
    explain table
            查询的表名

    explain type
        访问类型
        从最好到最差依次是：system>const>eq_ref>ref>range>index>All
        一般来说，得保证查询至少达到range级别，最好能达到ref
        
            All
                全表扫描        
            index 
                全索引扫描(Full Index Scan),index和All的区别为index类型只遍历索引树。
            range
                只检索给定范围的行，使用一个索引选择行。key列显示使用了哪个索引,一般就是在where语句中出现between,<,>,in
            ref 
                非唯一性索引扫描，可能会找到多个符合条件的行
            eq_ref
                唯一性索引扫描，对于每个索引键，表里只有一条记录与之匹配。常见于主键或唯一索引扫描
            const
                表示通过索引一次就找到了。例如使用主键查询
            system
                表里只有一行记录，可以忽略不计
    
    explain possible_keys
            显示可能应用在这张表中的索引，查询涉及到的字段若存在索引，则该索引将被列出，但不一定会被实际查询使用
            
    explain key
            实际使用到的索引，如果为null，则没有用到索引
    
    explain key_len
            表示索引中使用的字节数，在不损失精度的情况下，长度越短越好
            key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是通过表定义计算得出，不是通过表内检索得出
    
    explain ref
            显示索引的哪一列被使用了  库名.表名.索引名称  或 常量
    
    explain rows
            根据表统计信息以及索引选用情况，大致估算出找到所需的记录所要读取的行数
            
    explain Extra
            1.Using filesort 文件排序
                说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
                mysql中无法利用索引完成的排序操作称为“文件排序”
                
                出现这个最好优化SQL
            
            2.Using temporary
                表示MySQL新建了内部的临时表保存中间结果。常见于order by和分组查询group by
                group by最好按照创建索引的顺序
                
                出现这个需要立即优化SQL
                
            3.Using index
                表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错
                如果同时出现using where，表示索引被用来执行索引键的查找
                如果没有出现using where，表示索引被用来读取数据而不是执行查找
                
                覆盖索引(Covering Index):查询的字段和创建的复合索引字段一样，select数据列从索引中就可以获得，不必读取数据行
                
            4.Using where
                表示使用了where过滤
                
            5.Using join buffer
                表示使用了连接缓存
                
            6.impossible where
                where中条件返回false，例如：where name = 'a' and name = 'b'
                
            7.select tables optimized away
                在没有group by 子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM引擎优化count(*)操作
                不必等到执行阶段再计算，查询执行计算执行的阶段即完成优化            
                
            8.distinct
                优化distinct操作，在找到第一个匹配的元组后即停止查找同样值的动作
    
索引优化
    1.尽量使用全值匹配，where中的条件和索引的字段的个数和顺序完全匹配
    2.尽量使用覆盖索引，索引列和查询列完全一致，最好不要使用select *
    3.最佳左前缀法则，指的是查询从索引的最左列开始，不要跳过索引中的列
    4.存储引擎不能使用索引中范围条件右边的列
        索引：idx_name_age_height
        例如：select * from table_name where name = 'lily' and age > 25 and height = 180;
            "and age > 25"是一个范围查询，height不能被索引使用到    
      
    
怎么避免索引失效?
    1.不在索引列上做任何操作（计算，函数，类型转换）
    2.字段最好不要放空值，is null，is not null无法使用索引
    3.mysql在使用<>或!=时无法使用索引
    4.字符串不加单引号索引失效
    5.少用or，用它来连接时会索引失效
    6.使用like以%开头，mysql索引会失效
    
查询优化    
    1.不要使用select *，如果字段过多，可能会超出sort_buffer_size的容量
    超出之后，mysql会创建tmp文件进行合并排序，进行多次IO
    
    2.为排序使用索引
    order by 子句尽量使用Index方式排序，避免使用FileSort方式排序，尽可能在索引列上完成排序操作，遵循索引创建的最佳左前缀
    
    index_a_b_c
    如果where使用索引的最左前缀定义为常量，则order by能使用到索引
    例如：
        where a = 'XX' order by b,c
        where a = 'XX' and b = 'XX' order by c
        where a = 'XX' and b > const order by b,c
        
    尝试增大sort_buffer_size参数的设置
    尝试增大max_length_for_sort_data参数的设置
        
        
慢查询日志
    默认情况下，MySQL数据库没有开启慢查询日志
    如果不是调优需要，不建议启动该参数，因为开启后会带来一定的性能影响
        
    查看是否开启
        show VARIABLES LIKE '%slow_query_log%';
    
    开启慢查询日志
        set global slow_query_log = 1; mysql重启后失效
        如果要永久生效，就需要修改mysql配置文件
    
    修改阈值
        set global long_query_time = 3;
        需要重新连接或者新开一个会话才能看到修改后的值
        
    日志分析工具
        mysqldumpslow