mysql索引优化熊大
注意我们是以innodb为例
创建表sql如下:
CREATE TABLE `user` (`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',`age` int(10) NOT NULL COMMENT '年龄',`name` varchar(100) NOT NULL COMMENT '姓名',`card_no` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '身份证号',PRIMARY KEY (`id`) USING BTREE,INDEX `ids_card_no`(`card_no`) USING BTREE
) ENGINE = InnoDB ;**INSERT INTO `user` VALUES (1, 18, '1101011990d3d75410');
INSERT INTO `user` VALUES (2, 99, '1101011990d3d73677');
我有两个sql如下:
select * from user where id=1;
select * from user where card_no='1101011990d3d73677';
看下他们分别是怎么执行的:
第一个sql 因为是根据主键查询所以一次性就能把数据拿出来。
第二个sql根据普通索引查询它会分成两步:
1:先找到所在的主键2。
2:根据主键找到对应的数据也就造成了所谓的回表。
为什么是这样执行的请看上一篇文章[常见索引原理](https://blog.csdn.net/aiailingfei/article/details/112401875)
注意:
`EXPLAIN SELECT * FROM `user` WHERE card_no='1101011990d3d73677';`
发现扫描的行数是一 因为执行语句是在执行器执行 而回表操作是在存储层操作
如果把第二个sql改成:
SELECT id FROM `user` WHERE card_no='1101011990d3d73677';`
那么上面这个sql是不需要回表的因为索引上已经有值可以直接返回。那么这种情况我们成为索引覆盖
简单来说:
索引覆盖就是查询的字段在索引上存在才能不走回表.如果查询的字段过多其实就没必要了。维护索引也是需要代价的。
当然如果有一个频繁请求会根据身份证来查询年龄那么还是建议搞一个联合索引来提升性能(这里只讨论sql 因为根据身份证可以算出年龄)。
最左匹配:
在面试的时候经常会出现这样一个问题:
- 为什么like a% 可以用到索引 而%a是用不到索引的?
- 我有一个联合索引(a,b) 查询的时候条件用a可以使用索引b为什么用不到索引?
B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
假设我有name 索引如下 后面跟的是主键:
由图中可以更加清晰明了的看出a%这种可以用到索引而%a使用不到索引的.
假设假设我有name 索引如下 后面跟的是age变成一组联合索引道理也是一致的.
那么问题又来了 还是针对上面的联合索引(name,age)
select * from user where name like zhang% and age=10;
如果mysql 是5.6以上版本他会根据根据索引上的值先自行判断以便减少回表次数 那么这个过程就是索引下推.
索引下推针对是联合索引 如果实业业务中根据多个条件查询而且频繁那么就需要考虑是否创建一个联合索引减少回表次数
最后讨论一个分页查询
还是上面的表假如有3000万的数据 1000条一分页取第五页的数据
select * from user limit 5,5000;
执行过程:
mysql会先查出5000条的数据然后根据你的分页在取出最后一页的数据。也就是说它会把前面你不需要的4000条数据也查出来。其实也还好
那么如果我们查询第25000页的数据呢?这个是不是就挺吓人的因为它会扫描2500万的数据
不知道是否有兄弟跟我一样遇到过这个问题。
那么我们换种写法
select * from user where id>4000 limit 1000
那么这种写法可能会好点.如果通过explain来看他们扫描的行数差不多一致.但是效率为什么要比上面高不少呢? 首先id是自增的另外我们的数据是存到数据页中它可以按照顺序取数据。
当然这也是血的教训的来的。
在上面的总结中参考
<mysql实战45讲>
当然如果有一些不对的地方需要指正或者共同学习请加下微信前路慢慢你我共同进步.
