mysql索引实战

Mysql索引：

说起数据库索引想必小伙伴们都会听过这个东西，甚至还是能自己建立索引并且优化的。那么要是您不知道索引，那么这篇文章会告诉您什么是索引及优化帮您丰富技术栈，如果您知道索引，那么这篇文章可以帮您复习索引的基础知识同时也会给您介绍索引的本质，如果您知道索引并且也知道优化，那么这篇文章就相当于在您百忙之中的复习，同时也希望您的批评指正。

• 索引是什么：

Mysql官方对索引的定义为：索引（index）是帮助Mysql高效获取数据的数据结构。可以得到索引的本质：是一种数据结构；
索引的目的在于提高查询效率，可以类比字典的目录，通过目录去查生字要比直接翻整本字典要快的多吧。
可以简单理解为 “排好序的快速查找数据结构”
在数据之外，数据库系统还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构 就是 索引。下图就是一种可能的索引方式实例：

为了加快Col2的查找，可以维护一个右侧的二叉查找树，每个节点分别包含索引键值和一个指向对应数据物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速检索出符合规则的数据。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘中。
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引。当然，除了B+树这种索引之外还有哈希索引（hash index）等。

• 优势：

提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

• 劣势：

实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
虽然索引大大提高了查询速度，但是会降低更新表的速度，如对表进行insert,update,delete。因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素，如果你的Mysql有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

• Mysql索引的分类：

  • 单值索引 ：一个索引只包含一个列，一个表可以有多个单值索引，建议不超过5个单值索引。
  • 唯一索引：索引列的值必须唯一，可以有空值。
  • 复合索引：一个索引包含多个列

• Mysql索引结构：

  • BTree

  • Hash

  • full-text

  • R-Tree
    
    这个就是BTree的结构。查找过程：
    如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址吧磁盘块3由磁盘加载到内存中，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计3次IO。

    hash索引的查找效率更高，但是为什么没有成为主流的索引结构呢：

    • 由于仅仅能满足 “=” IN 这样的查询，不能满足范围查询，
    • 不能排序查询，
    • 不能利用部分索引键查询。
    • 不能避免表扫描，
    • 遇到大量hash值相等的情况，查询效率低下

• 哪些情况需要建立索引：

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 单键/组合索引的选择问题，（在高并发下倾向建立组合索引）
  • 查询中排序的字段，排序的字段如果通过索引去访问将大大提高排序速度
  • 查询中统计或者分组的字段

• 哪些情况不要建索引：

  • 频繁更新的字段不适合创建索引
  • where条件里用不到的字段不要建索引
  • 表记录太少
  • 数据重复 且分步平均的表的字段，建立没有太大的实际效果

性能分析：

• Explain(执行计划):

使用Explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的，分析你的查询语句或是表结构的性能瓶颈
能做：
- 表的读取顺序：
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引实际被使用
- 表之间的引用
- 每张表有多少行被优化器查询
语法：
- explain+sql语句
内容：

• 各字段解释：
  • id:
    select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序（select执行的优先级），有三种情况：
    • id相同，执行顺序由上至下
    • id不同，查询，id的序如果是子号会递增，id值越大优先级越高，越 先被执行
    • id相同不同，同时存在
  • select_type:

    • simple(简单查询),
      简单的select查询，者union查询中不包含子查询或

    • primary(主查询)
      查询中若包含任何复杂的子部分，最外层查询则被标记为

    • subquery(子查询)
      在select或where列表中包含了子查询

    • derived(衍生)
      在from列表中包含的子查询被标记为derived(衍生)
      mysql会递归执行这些子查询，把结果放在临时表里

    • union
      若第二个select出现在union之后，则被标记为union;
      若union包含在from子句的子查询中，外层select将被标记为
      derivde

    • union result
      从union表获取结果的select

    • type(访问类型):

      • system
        表只有一行记录(等于系统表)，这是const类型的特例，平时不会
        出现，可以忽略不计
      • const
        表示通过索引一次就找到了，const用于比较primary key或者
        unique索引。因为只匹配一行数据，所以很快。
        如将主键置于where列表中，mysql就能将该查询转换为一个常
        量
        例：通过索引查询
      • eq_ref
        唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
        常见于主键或唯一索引扫描
        例：链接表时的1对1
      • ref
        非唯一性索引扫描，返回匹配某个单独值的所有行
        本质上也是一种索引访问，他返回所有匹配某个单独值的行，然
        而它可能会找到多个符合条件的行，所以他应该属于查找和扫描
        的混合体
        例：链接表时的1对多
      • range
        只检索给定范围的行，使用一个索引来选择行。key列显示使用
        了哪个索引
        一般就是在你where语句中出现了between，<，>，in等查询
        这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索
        引的某一点，结束于另一点，不用全部扫描。
      • index
        Full Index Scan,index于all区别为index类型只遍历索引树。这通
        常比all快，因为索引文件通常比数据文件小。
        （也就是说虽然all和index都是读全表，但是index是从索引中读
        取的，而all是从磁盘中读取的）
        例：没有where条件的查询索引
        explain select id from landmanage_massif type是index explain select * from landmanage_massif type是all
      • all
        全表扫描
      • null
        显示的是访问类型，其中从最好到最差：
        system>const>eq_ref>ref>range>index>all
        一般来说，得保证查询至少达到range级别，最好能达到ref

    • possible_keys
      显示可能应用在这张表上的索引，一个或多个。
      查询涉及到的字段上若存在索引则该索引将被列出，
      但不一定被查询实际使用

    • key:
      实际使用的索引 ，如果为null则没有使用索引
      查询中若使用到了覆盖索引，则该索引只出现在key列表中

    • key_len:
      表示索引中使用的字节数， 可通过该列计算查询中使用的索引的长
      度。在不损失精确性的情况下，长度越短越好
      key_len显示的值为索引字段的最大可能长度，并非实际使用长度，
      即key_len是根据表定义计算而得，不是通过表内检索出的
      

    • ref：
      显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

    • rows:
      根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

    • extra: 包含不适合在其他列中显示但十分重要的额外信息

      • using filesort
        说明mysql会对数据使用一个外部的索引排序，而不是按照表内
        的索引顺序进行读取。
        mysql中无法利用索引完成的排序操作称为“文件排序”
      • using temporary
        使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by
      • using index
        表示相应的select操作中使用了覆盖索引（covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where 表明索引用来读取数据 而非执行查找动作
      • using where
        表明使用了where过滤
      • using join buffer
        使用了链接缓存
      • impossiable where
        where子句的值总是false,不能用来获取任何元组
      • sleect table optimized away
        在没有group by 子句的情况下，基于索引优化min/max 操作或者
        对于myisam存储引擎优化count(*)操作，不必等到执行阶段再进
        行计算，查询执行计划生成的阶段即完成优化。
      • distinct
        优化distinct操作，在找到第一匹配的元组后即停止找同样值的操作

Mysql索引优化：

1.索引失效：

• 全值匹配我最爱
  建立的复合索引，在where条件中全部用到，且条件都是等于
• 最佳左前缀法则
  如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
• 在索引列上做任何操作（计算，函数，（自动or手动）类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
  范围之后全失效，范围内个用来排序不是用来检索数据
• 尽量使用覆盖索引（只访问索引的查询（查询列和索引列一致））
• 减少select *
• mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
• is null , is not null 也无法使用索引
• like以通配符开头(’%abc…’)索引失效，会变成全表扫描
  通配符加后面，索引不失效
  解决以通配符开头的sql索引失效的办法 就是使用覆盖索引 即，建立的复合索引，查询的时候要覆盖上。
• 字符串不加单引号索引失效
  10)少用or，用它来连接时会索引失效
• 用or时 如果是复合索引，只用其中一个 索引失效 变全表扫描，如果是单值索引，不失效，变range

2.order by关键字优化

• order by 子句 尽量使用index方式排序，避免使用filesort方式排序
  mysql支持二种方式的排序，filesort和index，index效率高。
  它指mysql扫描索引本身完成排序。filesort方式效率较低。
  使用order by满足两种 排序情况会使用index方式排序：
  • order by 子句使用索引最左前列。
  • 使用where子句与order by子句条件列 组合满足索引最左前列
• 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀。
• 如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单
  路排序
  双路排序：
  mysql4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
  从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段
  单路排序：
  从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，他的效率更快一些，避免了第二次读取数据。并且把随机io变成了顺序io,但是他会使用更多的空间，因为他把每一行都保存在内存中了.

group by 关键字优化

• 基本和 order by相同，
• group by实质是先排序后分组，遵照索引建的最佳左前缀。
• 当无法使用索引列，增大max_length_for_sort_data参数的设置+sort_buffer_size参数的设置。
• where高于having，能写在where限定的条件就不要去having中限定了

慢查询日志：

mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阙值的语句，
具体指运行时间超过 long_query_time 值的sql，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的sql
由它来查看哪些sql超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢sql，希望能收集超过5秒的sql，结合之前的explain进行全面分析

• 查看是否开启
  show variables like '%slow_query_log%'
开启： set global slow_query_log = 1;
• 查看当前多少秒算慢
  show variables like 'long_query_time' 单位秒
• 设置慢的阙值时间
  set global long_query_time = 3;
• 查看当前有几条慢sql
  show global status like '%Slow_queries%'

show profiles:

• 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。
  可以用于sql的调优的测量。

• 默认情况下，参数处于关闭状态，并保存最近15次的运行结果

• 分析步骤

  • 查看当前版本是否支持
    show variables like 'profiling'
  • 开启功能
    set profiling = 1;
  • 运行sql
  • 查看结果
    show profiles
    
  • 诊断sql
    show profile cpu,block io for query sql的数字号码
    
    其中还可以查询：
    1.all : 显示所有开销信息
    2.block io ： 显示块io相关开销
    3.context switches : 上下文切换相关开销
    4.cpu : 显示cpu相关开销信息
    5.ipc： 显示发送和接收相关开销信息
    6.memory ： 显示内存相关开销信息
    7.page faults： 显示页面错误相关开销信息
    8.source : 显示和source_function,source_file,source_line相关的
    开销信息
    9.swaps ： 显示交换次数相关开销的信息

• 日常开发需要注意的结论
  status中如果出现下列问题语句，说明sql糟糕
  converting HEAP to MyISAM ：查询结果太大，内存不够用了 往磁盘上搬了
  creating tmp table 创建临时表
  Copying to tmp table on disk : 把内存中临时表复制到磁盘，危险！
  locked

题后话，由于作者水平有限，文章中难免会有歧义的地方，欢迎大家批评指正，作者定会及时改正