MYSQL的索引（mysql优化之索引篇）

MYSQL的索引（mysql优化之索引篇）

mysql性能的优化

索引优化
select语句优化,值得注意的是join语句越多,性能越不好，酌情写语句
服务器参数的设计

select语句

select语句输入命令之后，要通过mysql优化器自动做出最优顺序进行。而不是按照输入顺
序。一般情况下,第一步是from tabname，先确定需要查询的表,再根据条件一步一步删选,
筛选完按照输出字段要求进行输出字段的筛选,然后再进行排序分组等,最后把处理之后的数
据展示出来

join

复习mysql中的join语句
join,连接的意思,所谓join语句,也就是连表查询的语句
内连接 ：inner join on
外连接 ：左外连接：left join on
        右外连接：right join on
全连接  ：full outer join （但是mysql不支持,可以利用左连接和右连接加去重来实现）

差集:
WHERE A.KEY IS NULL
WHERE B.KEY IS NULL

索引理解和概念

索引其实是一种排好序的快速查找的数据结构

目的就是提高mysql查询效率以及排序效率

数据库服务器,也就是数据库系统,不仅保存着数据,而且也维护着满足特定查找算法的数据结
构，这些数据结构是以某种方式引用着数据，这个就是索引

总的来说,索引就是在数据库中优化数据的检索和排序的

索引的优势和劣势

优势

提高检索的效率,降低服务器的IO成本
降低排序成本,降低cpu的消耗

    总的来说，就是节省硬件,IO以及CPU

劣势

索引也是表,保存索引是需要空间的（索引保存了数据表主键和索引对应的字段,并且只想数据）

降低了数据库的写操作，因为写入数据的时候,索引也是需要修改的

总结

索引不是一气呵成,在大容量数据表中,是不断地增删改查,不断的性能分析做出此时此刻的最
优索引

索引的分类

单列索引：一个索引只有一个列
唯一索引：列值必须唯一,但可以为空
复合索引：一个索引包含多个列

索引的语法

CREATE INDEX INDEX_NAME ON TABLE_NAME (column(length);
ALTER TABLE_NAME ADD INDEX INDEX_NAME ON (column(length));

show index from table_name;
drop index from table_name;

建立索引和不建立索引的情况

需要建立索引

1，主键自动建立唯一索引
2，频繁作为查询条件的字段应该创建索引
3，查询中和其他表关联的字段,外键关系创建索引
4，频繁更新的字段不适合建索引,因为更新记录的同时还会更新索引，降低效率
5，where条件里用不到的字段不创建索引
6，单键/组合缩影的选择问题,再高并发下亲想创建组合索引
7，查询中的排序字段，建立索引
8，查询中需要统计的字段或者需要分组的字段

不需要建立索引

1，表记录太少的表，也就是说数据量少的表
2，经常增删改的表
3，数据重复,且平均分布的字段,建立索引一般没用
4，如果某个数据列包含许多重复的内容,那么给它建立索引则意义不大,没有实际效果

MySQL性能分析

1，mysql query optimizer(),select语句优化器，通过计算分析
系统中的统计信息，为客户端提供query的最优执行计划。一般情况下
默认即可

2，cpu
   IO
   服务器硬件性能

3，explain 模拟优化器执行sql查询语句,从而了解mysql是如何处
处理sql语句,分析sql语句和表结构的性能，从而优化

1和2中一般默认即可,索引主要是从explain分析中优化数据库。

EXPLAIN

explain+sql语句即可。

执行计划包含信息：
id，select_type,table,type,possible_keys,key,key_len,ref,row,extra

1,id
    查询序列号,包含一组数字，是查询中执行select子句或表的操作顺序
    如果id相同，按照table的顺序，从上而下操作
    如果id不同，id越大，表的优先级越大
    如果有相同有不同,先操作id大的表，然后按照顺序操作id一致的表
        如果是子查询，会出现衍生表，derived,然后操作的表按照derived id来观察执行顺序

2，select_type
    simple              简单的select查询，不包含子查询或者union
    pirmary             查询中包含了任何复杂的子部分，最外层查询的标记
    subquery            select或者where中包含了子查询
    derived             在from列表中包含包含的子查询被标记为derived,mysql会递
                        这些归这些子查询，把结果放在临时表中
    union               若第二个select出现在union之后，则标记为union；
                        若union包含在from子句的子查询中，外层select将被标记为derived
    union result        包含union结果的数据表

3，table
    执行的表名
4，type
    访问类型
    system>const>eq_ref>ref>range>index>all   执行效果排名    
    system          查询中一行记录一行表
    const           primary和unique索引，只匹配一行数据
    eq_ref          唯一性索引扫描，对于每个索，表中只有一个记录与之匹配，常见主键和唯一索引
    ref             非唯一索引扫描，返回某个单独值的所有行
    range           给定的范围查询
    index           索引中全部扫描
    all             全表扫描
5，possible_keys
    有可能用到的索引，真实情况下不一定用到
6，key
    真是情况下用到的索引
7，key_len   
    表示索引中使用的字节数,可以通过该列计算查询时使用的索引长度,不损失精确情况下，越短越好
    注意它显示的是最大可能长度，并不是实际使用长度，是表定义计算而来，而不是检索得到
8，ref
    显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引上的列值
9，rows
    扫描的行数
10，extra
    额外的重要信息
    using filesort  说明是一个外部的索引排序，索引没有参加排序。也叫文件排序，出现是不好的
    using temporary 使用了临时表，出现是不好的
    using index select的操作中使用了覆盖缩影，避免访问了表的数据行。
    using where
    …………

索引多表优化

join语句优化

    尽可能减少join中的NestedLoop的循环总次数，也就是永远用小表结果集驱动达标结果集
    优先优化NestedLoop的内层循环
    保证join语句中被驱动表表上join条件已经被索引

高效索引原理

全值匹配

最佳左前缀法则   索引从最左端开始并且不跳过索引中的列

不再索引上做任何操作，例如计算，函数，自动手动的类型转换

尽量使用覆盖索引，减少selecte * 

mysql在使用不等于的条件时，索引会失效

is null,is not null 无法使用索引

like 用通配符开头，索引失效。优化原则，建立覆盖索引

字符串不加引号索引失效-也就是进行了自动类型转换

or连接的时候索引会失效

索引一般的建议

对于单键索引，尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好

在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的



尽量将索引全值匹配，遵守最左前缀原则
含有索引的列值少用mysql自带函数进行计算
like的百分号写右边的情况下不中断
不等，空值，or尽量少用

in/exists

多表查询中的子查询优化策略，永远是小表驱动大表
例如:select * from A where id in (select id from B)
等价于：
       for select id from B
       for select * from A where A.id=B.id
以上方法适用于A表数据集大于B表数据集

    select * from A where exists (select 1 from B where B.id=A.id)
等价于：
       for select * from A
       for select * from B where B.id=A.id
以上方法使用于A的数据集小于B的数据集

in的语法理解: 主查询的条件，要满足子查询查出的数据

exists的语法理解:将主查询的数据，放到子查询中做验证，根据验证结果true/false来决定主查询数据是否保留          

ORDER BY语句优化

order by排序优化
    mysql中有两种排序方式：
    1，通过有序索引扫描出有序数据，效率高。using index
    2，通过对数据查询完毕后进行排序，效率低。using filesort

排序优化的目标，就是避免filesort排序，尽量使用using index排序

using index优化（利用索引排序）：
    1，遵守最左前缀原则，中间不能断。
    2，要么同升，要么同降。
    3，同一个索引，可以where 和order by组合使用，但是中间不能断。并且where为常量
using filesort(优化)：
    通过合适的索引，可以避免filesort排序，但有些情况下，必须要使用filesort，所以用一下方式优化。
        1，两次扫描算法，先取出排序列和行指针信息，第二次根据第一次的序列再取出数据。内存开销少，I/O操作多
        2，一次性取出满足条件的行和所有的字段，然后再排序区进行排列，排列结束之后输出。
    mysql选择哪种排序，是比较系统变量max_length_for_sort_data的大小和query取出的字段大小来选择使用那种排序。所以优化的方法就是尝试设置这些参数取出最优值。

group by语句优化

group by 后面的字段如果利用索引，那么和order by机理一致
如果利用group by但是要避免order by排序的消耗，则可以在最后加上order by null