Mysql索引以及explain的字段

当春遇上冬
338 阅读7分钟

索引

1. 什么是索引

  • MySQL官方对索引的定义为：索引是帮助mysql高效获取数据的数据结构。即可以简单理解为“排好序的快速查找数据结构”。
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储到磁盘上。
  • 我们平常说的索引，如果没有特别指明，都是B+树索引。

2. 索引的优势

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

3. 索引的劣势

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE都会调整因为更新性能所带来的键值变化后的索引信息。
  • 索引只是提高效率的一个因素，如果你的mysql有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

4. 索引的分类

  • 单值索引 即一个索引只包含单个列，一个表可以有多个单列索引
  • 唯一索引 索引列的值必须为唯一，但允许为空
  • 复合索引 一个索引可以包含多个列
  • 基本语法
    • 创建
    CREATE [UNIQUE] INDEX 索引名称 ON 表名(列名)
ALTER 表名 ADD [UNIQUE] INDEX [索引名称] ON (列名)
    • 删除 DROP INDEX [索引名称] ON 表名
    • 查看 SHOW INDEX FROM 表名\G

5. explain分析

  • id select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
    • id相同，执行顺序由上至下
    • id不同，id值越大优先级越高，越先被执行
    • id有相同的有不同的，按照上面两个情况综合执行
  • select_type
    • SIMPLE 简单的select查询，查询中不包含子查询或者UNION
    • PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
    • SUBQUERY 在select或where列表中包含了子查询
    • DERIVED 在from列表中包含的子查询被标记为derived,mysql会递归执行这些子查询，把结果放在临时表里
    • UNION 若第二个SELECT出现在UNION之后，则被标记为UNION,若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
    • UNION RESULT 从UNION表获取结果的SELECT
  • table 显示这一行数据是关于哪张表的
  • type 访问类型排列：从最好到最坏依次是 system>const>eq_ref>ref>range>index>ALL
    • ALL 全表扫描
    • index index与all区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。index是从索引中读的，all是从硬盘中读的
    • range 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引
    • ref 非唯一性索引扫描返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
    • eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    • const 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快将主键置于where列表中，mysql就能将该查询转换为一个常量
    • system 表只有一行记录，这是const类型的特例，平时不会出现
    • NULL
  • possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
  • key 实际使用的索引。如果是NULL,则没有使用索引。 查询中若使用了覆盖索引，则该索引仅出现在key列表中，例如select a,b from c ,此时c表中有a,b的复合索引，虽然查询语句中没有使用任何条件，但是mysql也会将复合索引放入key中
  • key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
  • ref 显示索引的哪一列被使用了，如果可能的话，是一个常数。那些列或者常量被用于查找索引列的值
  • rows 根据表统计信息以及索引选用情况，大致估算出找到所需的记录
  • extra 执行该sql之外的一些信息
    • using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作成为 文件排序
    • using temporary 使用了临时表保存中间结果，mysql在对查询结果排序是使用临时表。常见于排序和分组查询
    • using index 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。 如果同时出现using where,表明索引被用来执行索引值的查找 如果没有同时出现using where,表名索引用来读取数据而非执行查找动作

5. 适合创建索引的情况

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

6.不适合创建索引的情况

  • 表记录太少 mysql自带性能优化，300W以下的数据，没有太大必要建立索引
  • 经常增删改的表
  • 数据重复且分布平均的表字段

7.索引失效的原因

  • 全值匹配 如果创建了多个索引，最好在查询条件中全部用到
#假设我们创建index_NameAgeAddress，最理想的状态就是
select * from table where name = '' and age = xx and address = ''
  • 最佳左前缀法则 如果创建了多列索引，要遵从最左前缀法则。即查询从索引的最左前列开始并且不跳过索引中的列
#假设我们创建index_NameAgeAddress
#1. 全部用到索引的情况
select * from table  where name = '' and age = xx and address = ''
#2. 用到一个索引的情况
select * from table  where name = ''
select * from table  where name = '' and address = ''
#3.用到两个索引的情况
select * from table  where name = '' and age = xx
# 3.索引失效的情况
select * from table  where age = xx and address = ''
select * from table  where address = ''
  • 不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列
#只能使用到前面两个索引，第二个还是range的，范围之后的索引列起不到作用了
select * from table  where name = '' and age > xx and address = ''
  • 尽量使用覆盖索引（之访问索引的查询（索引列和查询列一致）），减少select *
  • mysql 在使用不等于（!= 或者 <>）的是无法使用索引会导致全表扫描
  • is null, is not null 也无法使用索引
  • like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作 如果两边都需要写%的话，只能使用覆盖索引解决此问题
#索引失效
select * from table  where name like '%cc';
select * from table  where name like '%cc%';
#索引有用 
select * from table  where name like 'cc%';
  • 字符串不加单引号索引失效
  • 少用or,用它来连接时会索引失效
avatar
文章
阅读
粉丝