【记录】数据库索引

数据库索引以及explain工具

mysql索引的认识以及使用

• 明白索引的类型有多少种，分别都有什么功能

  ①普通索引index :加速查找

  ②唯一索引
  唯一索引：unique：索引列的值必须唯一，但允许有空值

  ​ 主键索引：primary key ：是一种特殊的唯一索引，不允许有空值

  ③联合索引

  ​ 当查找条件为两个以上是可以使用联合索引

  ​ -primary key(id,name):联合主键索引
  -unique(id,name):联合唯一索引
  -index(id,name):联合普通索引
  ④全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。

• 明白最左前缀原则（重点），这个关系到查询语句是否能够正常走索引，不过通常是单索引的情况下，就不需要这个原则，只有在组合索引的情况才会用到

  以最左边的为起点任何连续的索引都能匹配上

  将最常用（频率）做限制条件的列放在最左边，依次递减

  mysql会一直向右匹配直到遇到范围查询(>,<,between,like)就停止匹配

  例如有组合索引顺序（a,b,c）：相当于建立了索引：（a），（a，b），（a，b，c）

  可以不用单独建立a索引了

  以下情况下会用到索引：

  a

  a，b 组合

  a，b，c 组合

  select * from users where a=1 and b=2 能用上a、b
  select * from users where b=2 and a=1 能用上a、b
  select * from users where a=2 and c=1 能用上 a
  select * from users where b=2 and c=1 不能

• 学会使用explain命令来分析查询语句，了解查询语句是否使用了索引，如果索引失效失效的原因是什么

  explain示例：

  explain select * from loginList where userName = ‘lucy’

  结果说明：

  **①select_type:**表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)等。

  **②table:**输出结果集的表

  **③type:**表示表的连接类型,性能由好到差的连接类型为

  system(表中仅有一行,即常量表)

  const(单表中最多有一个匹配行,例如 primary key 或者 unique index)

  eq_ref(对于前面的每一行,在此表中只查询一条记录,简单来说,就是多表连接中使用 primary key 或者 unique index)

  ref(与 eq_ref 类似,区别在于不是使用 primary key 或者 unique index,而是使用普通的索引)

  ref_or_null(与 ref 类似,区别在于条件中包含对 NULL 的查询)

  index_merge(索引合并优化)

  unique_subquery(in的后面是一个查询主键字段的子查询)

  index_subquery (与 unique_subquery 类似,区别在于 in 的后面是查询非唯一索引字段的子查询)

  range (单表中的范围查询)

  index (对于前面的每一行,都通过查询索引来得到数据)

  all (对于前面的每一行,都通过全表扫描来得到数据)

  **④possible_keys:**表示查询时,可能使用的索引。

  **⑤key:**表示实际使用的索引。

  **⑥key_len:**索引字段的长度。

  **⑦rows:**扫描行的数量。

  **⑧Extra:**执行情况的说明和描述。

  不使用索引的原因：

  • 如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引
  • 用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到
  • 如果不是索引列的第一部分
  • 如果 like 是以%开始，%在左边索引就会不起作用
  • 如果列类型是字符串,那么一定记得在 where 条件中把字符常量值用引号引起来,否则的话即便这个列上有索引,MySQL 也不会用到的,因为,MySQL 默认把输入的常量值进行转换以后才进行检索

  查看索引情况：

  • show status like ‘Handler_read%’;

    Handler_read_key：表示一个行被索引读取的情况，很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用

    Handler_read_rnd_next：表示文件读取下一行的请求数

• https://www.cnblogs.com/bypp/p/7755307.html

• https://www.cnblogs.com/tufujie/p/9413852.html