nosqlmysql高级篇

mysql索引优化

本文字数: 1.6k 阅读时长: 5 分钟
阅读次数次

索引优化分析

性能下降sql慢：（执行时间长，等待时间长）

  • 1.select语句写的烂，各种连接加子查询

  • 2.索引失效，建的索引没用，创建单索引和复合索引

    1
    create index idx_user_name (idx_tablename_indexname) on user(name)

    查询用的多的字段建立联合建立复合索引

    1
    create index idx_user_nameEmail on user(name,index)

  • 3.关联查询太多join（设计缺陷或不得已的要求）

  • 4.服务器调优及各个参数设置（缓冲、线程数等）不恰当

explain

使用explain可以模拟优化器执行sql查询语句的步骤，从而知道mysql是如何处理你的sql语句的，分析你的查询语句或是表结构的性能瓶颈。

1
2
3
4
5
6
7
8
9
10
select * from rms.users;
explain select * from rms.users;
explain + sql横版
explain + sql\G竖版
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE  | users | NULL    | ALL  | NULL       | NULL | NULL  | NULL | 272 | 100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

explain相关字段解读

id

  • 1.id相同表示执行顺序是由上至下
  • 2.id不同（如子查询）id的序号会递增，id值越大优先级越高，越先被执行。

select type

  • 1.simple 普通查询（查询中不包含子查询和union）
  • 2.primary 查询中包含子部分（最外层叫primary）
  • 3.subquery 在select或where列表中包含了子查询
  • 4.derived在from列表中包含的子查询被标记为Derived(衍生)，mysql会递归执行这些子查询，把结果放在临时表里面
  • 5.union 如果第二个select出现在union之后，则被标记为union;如果union包含在from子句的子查询中，外层select将被标记为：derived
  • 6.union result 从union表获取结果的select

type

显示查询用了何种类型

  • 1.有这几种ALL – index – range – ref – eq_ref – const,system – NULL

  • 2.从最好到最差：
    system – const – eq_ref – ref – range – index – ALL
    百万数据有all必须优化

  • 3.一般来说得保证查询至少达到range级别，最好能达到ref级别

  • 4.介绍级别类型

    • 1.system:表只有一行记录（等于系统表）这是const类型的特例，平时不会出现，这个可以忽略。
    • 2.const表示通过索引一次就找到了，const用于比较primary key 或者unique索引，因为只匹配一行数据
      所以很快，如果将主键置于where列表中，mysql就能将该查询转换为一个常量。
    • 3.eq_ref 唯一索引扫描，对每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
    • 4.ref 非唯一性索引扫描，返回匹配某个单独值的所有行，可能找到多个符合条件的行，所以他应该属于查找和扫描的混合体
    • 5.range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等查询，这种范围查询比全表扫描要好。
    • 6.index full index scan index与all的区别是index类型只遍历索引树，这通常比all快，因为索引文件比数据文件小。all从硬盘上读取数据。
    • 7.all 全表扫描

possible_keys and key

  • 1.判断是否索引失效，或多个索引到底使用哪一个？
    分别表示可能使用到的索引和实际用到的索引。
  • 2.key如果为null，说明没建索引或者索引失效
  • 3.查询中若使用了覆盖索引，则该索引仅出现在key列表中
    覆盖索引解决like问题，覆盖索引就是：查询的字段和联合索引顺序字段都一一一致。
  • 4.key_len表示索引中使用的字节数，可通过该列计算查询中使用索引的长度。key_len显示
    的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得到的。

ref

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

rows

大致需要读取的行数

extra 其他的额外的（重要的是前三个）

包含不适合在其余6个列中显示但是十分重要的信息

  • 1.using filesort
    说明mysql对数据进行了一个外部索引排序而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成的排序称为文件类排序。
    联合索引可能部分查询能用，（由于使用排序未按照建立联合索引的顺序）但排序不能用，这时候也会出现filesort
    当出现这个的时候，很危险。建议尽快优化可以通过新建联合索引进行优化。

  • 2.using temporary
    很糟糕的建立了临时表保存中间结果，常见于排序order by和分组查询group by。拖慢sql的元凶。十死无生
    一定要优化

  • 3.using index
    表明相应的select操作使用了覆盖索引（covering index）避免了表的数据行，效率不错！
    如果同时出现了using where，表明索引被用来执行索引键值的查找
    如果没有出现using where，表明索引被用来读取数据而非执行查找动作

  • 4.using where
    表明使用了where进行过滤

  • 5.using join buffer
    表明使用了连接缓存 join特别多了， 配置文件里的缓冲join可以调大一点

  • 6.impossible where
    where子句的值时false不可达到。

  • 7.select tables optimized away
    在没有groupby子句的情况下，基于索引优化MIN/MAX操作或者对于myISAM存储引擎优化count(*)
    操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

  • 8.distinct
    优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

覆盖索引（covering index）

当select时，部分满足联合索引或者满足联合索引，这时mysql可以利用索引文件返回select的列而不必取读取数据文件
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select，因为将所有字段一起做索引会导致索引文件过大，查询性能下降。

重建索引

分享到