数据库索引以及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