mysql 主键索引,组合索引,单列索引使用场景

mysql 主键索引,组合索引,单列索引使用场景

CREATE TABLE button (
id bigint(20) NOT NULL AUTO_INCREMENT, --主键索引
button_name varchar(45) NOT NULL COMMENT ‘功能名称’,
app_id bigint(20) NOT NULL,
permission_id bigint(20) DEFAULT NULL, – permission_id 和 app_id 联合索引。
api_id bigint(20) NOT NULL, --api_id单独索引
PRIMARY KEY (id),
KEY index_app_permission_lianhe (permission_id,app_id) USING BTREE,
KEY index_api_id_dange (api_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
主键索引,单独索引,组合索引使用场景及优化
表button 有3个索引,分别是:id主键,联合索引(permission_id,app_id),api_id(单列索引)
button_name 无索引
查询where条件中:
主键索引:
1、主键索引与联合索引同时存在,使用主键索引
2、主键索引与单个索引同时存在,使用主键索引
结论:只要主键索引在,使用主键索引。
联合索引 :
1、联合索引与单列索引列 同时存在,使用单列索引
2、联合索引中列顺序颠倒无影响。
3、联合索引实行最左侧原则(最左原则在我另一篇博客有提到)。
4、如果查询条件中只有app_id,但是select 条件中有 permission_id,则也使用联合索引。
5、select id,app_id from button where app_id=1001; 使用联合索引
6、explain select id,app_id,button_name from button where app_id=1001;不使用联合索引

结论:索引优先级:主键索引,单列索引,组合索引
联合索引中遵从最左侧列原则。
当查询条件和返回结果中仅仅包含联合索引中索引项,也使用联合索引。如第4条。
当查询条件中出现联合索引中非最左侧索引列,返回结果中含义联合索引中的列或者主键则也使用联合索引。

展开阅读全文

MYSQL查询用到的主键id索引问题

03-28
CREATE TABLE `mytest` ( `id` int(10) NOT NULL AUTO_INCREMENT, `a` int(50) DEFAULT NULL, `b` int(50) DEFAULT NULL, `c` int(50) DEFAULT NULL, `index` int(50) DEFAULT NULL, `remark1` varchar(255) DEFAULT NULL, `remark2` varchar(255) DEFAULT NULL, `remark3` varchar(255) DEFAULT NULL, `remark4` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10059857 DEFAULT CHARSET=utf8 COMMENT='测试1000万数据'; 本人菜鸟,希望大佬们多多指教 这是本人测试建的表,插入了1000万数据,主要是为了测试一下mysql千万数据分页查询时的速度,查询8000000往后100条数据 建表因为主键id唯一自增,所以默认为主键索引 ![查看索引](https://img-ask.csdn.net/upload/201903/28/1553762081_28446.png) SELECT * from mytest limit 8000000,100 > OK > 时间: 8.106s 此查未使用索引,耗时比较久 SELECT * from mytest where id>(SELECT id from mytest LIMIT 8000000,1) limit 100 > OK > 时间: 5.87s 此处似乎用到了索引,速度有所提升? 这是explain结果 ![图片说明](https://img-ask.csdn.net/upload/201903/28/1553763277_276647.png) 此处是外面查询用到了索引,子查询未用到索引吗?试了多次发现都是差不多这个速度,感觉还是挺慢的 然后 按理说主键id已经是自增索引,查询速度应该很快才对吧,这时候我又对主键id这个字段单独创建了一个普通索引 CREATE index id_index on mytest(id) > OK > 时间: 26.275s ![查询索引](https://img-ask.csdn.net/upload/201903/28/1553762745_778699.png) 查看索引发现和主键索引应该没啥区别,可是,,, 发现查询居然变快了不少??? SELECT * from mytest limit 8000000,100 > OK > 时间: 9.242s 这里未用到索引 SELECT * from mytest where id>(SELECT id from mytest LIMIT 8000000,1) limit 100 > OK > 时间: 1.853s 这是explain结果 ![图片说明](https://img-ask.csdn.net/upload/201903/28/1553763018_643436.png) **这里应该是用到了索引,可是为什么会出现这种情况呢,主键id也是索引,查询就比较慢,单独对id创建了一个普通索引查询速度居然变快了许多,想了半天没明白为啥,求前辈们指教** 此外查询时我也尝试连表查询 SELECT * from mytest a join (SELECT id from mytest limit 8000000,100) b on a.id=b.id; 发现和SELECT * from mytest where id>(SELECT id from mytest LIMIT 8000000,1) limit 100;这个查询耗时几乎一致 附一下表数据 ![图片说明](https://img-ask.csdn.net/upload/201903/28/1553764984_854206.png)
©️2020 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值