重新认识Database-MySQL②--索引使用

sql语句 用户表 采用5.7版本

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `create_date` datetime DEFAULT NULL COMMENT '创建时间',
  `email` varchar(45) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户表'

一、type：连接类型 最关键的一列 效率（const>eq_ref>ref>range>index>all）

1. const:查询索引字段，并且表中最多只有一行匹配（好像只有主键查询只匹配一行才会是const，有些情况唯一索引匹配一行会是ref）
2. eq_ref 主键或者唯一索引
3. ref 非唯一索引（主键也是唯一索引）
4. range 索引的范围查询
5. index (type=index extra = using index 代表索引覆盖，即不需要回表)
6. all 全表扫描（通常没有建索引的列）

二、key_len

索引的长度，在不损失精度的情况下越短越好

临时关闭缓存

set global query_cache_size=0

set global query_cache_type=0

执行sql如下

-- 关闭临时查询缓存
set global query_cache_size=0;
set global query_cache_type=0;
-- 耗时0.016
explain select * from user limit 10000,10;
-- 基本上没有耗时
explain select * from user where id > 10000 limit 10;

1. 分页优化

• 主键递增且连续

第一条明显没有利用到主键聚集索引。

第二条分页利用主键索引

• 其他情况下的处理

explain select * from user u inner join (select id from user limit 10000,10) r on r.id = u.id;

该条语句主要利用了id进行关联，没有进行回表查询数据，只查询10条数据，利用主键聚集索引故而速度加快查询。

根据执行结果可知，直接limit 10000,10有明显的耗时0.016，主要是当前表的数据少。

语句的执行计划如下：

关于count查询

explain select count(*) from user;
explain select count(1) from user;
explain select count(id) from user;

结论：执行计划都执行了index索引，故而网上很多说count(1)>count(*)其实是不正确的。至少MySQL5.7版本不是如此。 因此，选择其实就数据库标准执行的方式select count(*) 即可。