登录
首页 >  数据库 >  MySQL

不会吧,不会吧?MySQL 索引最佳实践你不看看

来源:SegmentFault

时间:2023-01-16 12:29:41 439浏览 收藏

小伙伴们有没有觉得学习数据库很有意思?有意思就对了!今天就给大家带来《不会吧,不会吧?MySQL 索引最佳实践你不看看》,以下内容将会涉及到MySQL、Java,若是在学习中对其中部分知识点有疑问,或许看了本文就能帮到你!

作者爱说话

忙忙碌碌的一周又过去了,这周最大的乐趣就是买了个小音箱,又可以下班的时候开始愉快的开始练琴了,程序员嘛,还是得培养点艺术细菌。哈哈

这周本来没想好写什么东西,刚好周五公司进行了内部技术分享,内容为《MySQL索引讲解 + 最佳实践》,摘出了几条重要,和较常见的和大家一起分享,当然还有很多没有涉及到,关于索引的知识点,就不具体展开讲解,本来想转载一篇文章与大家分享,但是卑微的我还没加上原作者微信,有兴趣的话可以公众号内回复“唯一索引和普通索引”。我将原文地址推荐给你。

现在你可以洗洗手,打开你的 Naviact 和王经理一起动手实践,知识吸收 + 100%

温馨提示:文末附建表语句。

索引前戏

王经理 ?:天苍苍,野茫茫,索引查询帮大忙。

小林小声 bb:经理好像个憨批。

王经理:林步动,你在说什么?

王经理的砖头

王经理的砖头

小林:没没没,我说经理,好才气!这个诗写的,啧啧啧,真是 ? 牛掰 glass

王经理:算你小子,今天有眼力见。那我考考你,你能概括下索引的优点和缺点嘛?

小林:(淦,又考我) 经理,《高性能 MySQL》一书中总结的 MySQL 的优点是

  • 减少查询需要扫描的数据量(加快了查询速度)
  • 减少服务器的排序操作和创建临时表的操作(加快了groupby和orderby等操作)
  • 将服务器的随机IO变为顺序IO(加快查询速度)

至于缺点嘛,就是索引也是数据,需要存储,因此会带来额外的存储空间占用。其次,在插入,更新和删除操作的同时,需要维护索引,因此会带来额外的时间开销

小美:步动,你今天好帅啊

小林:????

王经理:今天,步动说的很对阿,我再来总结下索引的缺点

  • 索引占用磁盘或者内存空间
  • 减慢了插入更新操作的速度

但是实际上,在一定数据范围内(索引没有超级多的情况下),建立索引带来的开销是远远小于它带来的好处的,但是我们仍然要防止索引的滥用。

王经理:林步动,你骄傲啥,快给我从桌子上下来!我们今天的重点不在这里,重点是讲解一下 MySQL 的索引具体在生产中的最佳实践方法。拿起小本本,打开 Naviact,动起来。

最佳实践

前置知识 :EXPLAIN


1,最左前缀法则

EXPLAIN SELECT * FROM `employee_information` WHERE age = 23 AND position = "dev"

WHERE 条件列的,从索引的最左前列开始,且不跳过索引中的列,一旦未以最从左前列开始,索引就失效。意思就是 是兄弟,就来砍我啊,不对。应该是 带头大哥不能死,中间兄弟不能断

由最左前缀原则引申出的索引冗余问题: 根据最左索引原则,我们不难得出,index(a,b)相当于创建了二个索引index(a)、index(a,b)。也可以类推,index(a,b,c)相当于创建了三个索引index(a)、index(a,b)、index(a,b,c)。

那么,不妨多问自己一个为什么?? 索引冗余会带来什么问题?一张表设置几个索引以内最佳? (可以参考阿里 Java 开发手册,或《高性能的 MySQL》一书)


2,不在索引列上做任何操作(计算、函数、(自动 or 手动)类型转换),会导致索引失效而转向全表扫描

`EXPLAIN SELECT * FROM 
employee_information` WHERE LEFT(name,3) = 'bud'
``

拿该条 SQL 举例说明,我需要一条条取出 name,进行函数操作,然后逐条与限定的条件(bud)进行比较,所以没办法用索引,只能进行全表扫描。

那么,会不会有这么一种情况。在你不经意的时候, MySQL 偷偷的在你的索引列上做了操作?导致我们的索引失效了。

就比如,隐式转换


3,存储引擎不能使用索引中范围条件右边的列(若中间索引列用到了范围(>、

`EXPLAIN SELECT * FROM 
employee_information` WHERE name = 'budongli' AND age > 23 AND position = "dev"
``

可以看到 type = range,实际上 age 用到了索引(可以观察 key_len),只是说这个索引表达的是范围,导致 position 用不上索引


4,尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 SELECT * 语句

EXPLAIN SELECT name FROM `employee_information` WHERE name = 'budongli'

我们在相应的 SELECT 操作中使用了覆盖索引,通俗一点讲就是查询的列被索引覆盖,使用到覆盖索引查询速度会非常快,SQl优化中理想的状态。

需要注意的是,当 Using where 和 Using index 同时出现的时候,此时 Using where 只是用来从索引中查找数据,此数据如果不是用来过滤,那么就是用来读取,以避免回表读取数据行。

如果我们只爱使用 SELECT * ,那么完蛋。

Extra 会告诉你,查询的列未被索引覆盖,进而通过 where 条件过滤获取所需数据,


5,MySQL 在使用不等于(!= 或者 )的时候无法使用索引导致全表扫描,is null 或者 is null not 也不会走索引

EXPLAIN SELECT * FROM `employee_information` WHERE name != 'budongli'

这条和 Shit 一样的 SQL ?,在我是实习生的时候也写过。

尽可能把类似这种判断落在业务层,数据库很累,不给他加压力。保护数据库,从你我做起。


6,like 以通配符开头(‘$abc’) MySQL 索引失效会变成全表扫描操作

就像查字典一样,比如要查拼音首字母是ch的字,按顺序翻完以ch开头的页就能有结果,在这个 SQL 语句中我们想去查 名字开头是“budon”,利用索引顺序查就ok了,很快就能查出来了。

EXPLAIN SELECT * FROM `employee_information` WHERE name like 'budon%'

但要是假设你想在字典里查拼音包含an的字,那就只能一页一页翻完整个字典才能知道哪些是需要的,此时就相当于无法走索引,只能走全表扫描。

EXPLAIN SELECT * FROM `employee_information` WHERE name like '%budon%'


7,少用 or 或者 in

`EXPLAIN SELECT * FROM 
employee_information` WHERE name = "budongli" or name = "xiaomei"
``

MySQL 的查询优化器是一个非常复杂的部件,它使用了非常多的优化策略来生成一个最优的执行计划: 在使用 or 或者 in 的时候, MySQL 不一定会使用索引,MySQL 内部优化器会根据每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布等七七八八,九九十十等多个因素去整体评估是否使用索引。有非常多的原因会导致 MySQL 选择错误的执行计划,比如统计信息不准确、不会考虑不受其控制的操作成本(用户自定义函数、存储过程)。

归根结底,MySQL 认为的最优跟我们想的不一样(我们希望执行时间尽可能短,但 MySQL 值选择它认为成本小的,但成本小并不意味着执行时间短)等等。

所以说,MySQL 内部优化器有时候也是个笨蛋,很有可能选错。当然如果你觉得走一个固定的索引,这个 SQL 语句会快的飞起,请别客气使用 FORCE INDEX 与优化器进行 Battle

问题时间到了

如果一张表的某个字段即可以建唯一索引,又可以建普通索引,假设你是公司的 DBA,从性能角度考虑来讲,你会如何选择?选择的依据是什么?

(建议小伙伴可以主动去思考思考,动手实践实践,当然公众号内也给你准备了较为详细的分享,你可以在公众号内回复 “唯一索引和普通索引” ,我将与你分享)

建表语句

-- ----------------------------  
-- Table structure for employee_information  
-- ----------------------------  
DROP TABLE IF EXISTS `employee_information`;  
CREATE TABLE `employee_information`  (  
  `id` int(11) NOT NULL,  
  `name` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  
  `age` int(11) NOT NULL,  
  `position` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,  
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
  PRIMARY KEY (`id`) USING BTREE,  
  INDEX `hybrid_index`(`name`, `age`, `position`) USING BTREE,  
  INDEX `age`(`age`) USING BTREE  
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;  
  
-- ----------------------------  
-- Records of employee_information  
-- ----------------------------  
INSERT INTO `employee_information` VALUES (1, 'budongli', 23, 'dev', '2020-06-12 22:21:24');  
INSERT INTO `employee_information` VALUES (2, 'xiaomei', 22, 'dev', '2020-05-06 22:22:10');  
INSERT INTO `employee_information` VALUES (3, 'jingliwang', 30, 'manager', '2018-06-01 22:22:37');  
  
SET FOREIGN_KEY_CHECKS = 1;  

今天关于《不会吧,不会吧?MySQL 索引最佳实践你不看看》的内容就介绍到这里了,是不是学起来一目了然!想要了解更多关于mysql的内容请关注golang学习网公众号!

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>
评论列表