高考考试网
当前位置: 首页 高考资讯

mysql常用的索引有什么(MySQL的索引是什么怎么优化)

时间:2023-08-22 作者: 小编 阅读量: 4 栏目名: 高考资讯

MySQL提供了Explain,用于显示SQL执行的详细信息,可以进行索引的优化。P1指针表示小于17的磁盘块,P2是在17-35之间,P3指向大于35的磁盘块。接着发现28在26和30之间,通过P2指针的地址加载磁盘块3到内存,发生第二次I/O。用同样的方式找到磁盘块8,发生第三次I/O。真实的情况是,上面3层的BTree可以表示上百万的数据,上百万的数据只发生了三次I/O而不是上百万次I/O,时间提升是巨大的。

索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,所以大数据量建立索引是非常有必要的。MySQL提供了Explain,用于显示SQL执行的详细信息,可以进行索引的优化。

一、导致SQL执行慢的原因:

1.硬件问题。如网络速度慢,内存不足,I/O吞吐量小,磁盘空间满了等。

2.没有索引或者索引失效。(一般在互联网公司,DBA会在半夜把表锁了,重新建立一遍索引,因为当你删除某个数据的时候,索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引 )

3.数据过多(分库分表)

4.服务器调优及各个参数设置(调整my.cnf)

二、分析原因时,一定要找切入点:

1.先观察,开启慢查询日志,设置相应的阈值(比如超过3秒就是慢SQL),在生产环境跑上个一天过后,看看哪些SQL比较慢。

2.Explain和慢SQL分析。比如SQL语句写的烂,索引没有或失效,关联查询太多(有时候是设计缺陷或者不得以的需求)等等。

3.Show Profile是比Explain更近一步的执行细节,可以查询到执行每一个SQL都干了什么事,这些事分别花了多少秒。

4.找DBA或者运维对MySQL进行服务器的参数调优。

三、什么是索引?

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为:快速查找排好序的一种数据结构。Mysql索引主要有两种结构:B Tree索引和Hash索引。我们平常所说的索引,如果没有特别指明,一般都是指B树结构组织的索引(B Tree索引)。索引如图所示:

最外层浅蓝色磁盘块1里有数据17、35(深蓝色)和指针P1、P2、P3(黄色)。P1指针表示小于17的磁盘块,P2是在17-35之间,P3指向大于35的磁盘块。真实数据存在于子叶节点也就是最底下的一层3、5、9、10、13......非叶子节点不存储真实的数据,只存储指引搜索方向的数据项,如17、35。

查找过程:例如搜索28数据项,首先加载磁盘块1到内存中,发生一次I/O,用二分查找确定在P2指针。接着发现28在26和30之间,通过P2指针的地址加载磁盘块3到内存,发生第二次I/O。用同样的方式找到磁盘块8,发生第三次I/O。

真实的情况是,上面3层的B Tree可以表示上百万的数据,上百万的数据只发生了三次I/O而不是上百万次I/O,时间提升是巨大的。

四、Explain分析

前文铺垫完成,进入实操部分,先来插入测试需要的数据:

CREATE TABLE `user_info` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL DEFAULT '', `age` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `name_index` (`name`))ENGINE = InnoDB DEFAULT CHARSET = utf8;INSERT INTO user_info (name, age) VALUES ('xys', 20);INSERT INTO user_info (name, age) VALUES ('a', 21);INSERT INTO user_info (name, age) VALUES ('b', 23);INSERT INTO user_info (name, age) VALUES ('c', 50);INSERT INTO user_info (name, age) VALUES ('d', 15);INSERT INTO user_info (name, age) VALUES ('e', 20);INSERT INTO user_info (name, age) VALUES ('f', 21);INSERT INTO user_info (name, age) VALUES ('g', 23);INSERT INTO user_info (name, age) VALUES ('h', 50);INSERT INTO user_info (name, age) VALUES ('i', 15);CREATE TABLE `order_info` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `user_id` BIGINT(20) DEFAULT NULL, `product_name` VARCHAR(50) NOT NULL DEFAULT '', `productor` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`))ENGINE = InnoDB DEFAULT CHARSET = utf8;INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

初体验,执行Explain的效果:

索引使用情况在possible_keys、key和key_len三列,接下来我们先从左到右依次讲解。

1.id

--id相同,执行顺序由上而下explain select u.*,o.* from user_info u,order_info o where u.id=o.user_id;

--id不同,值越大越先被执行explain select * from user_info where id=(select user_id from order_info where product_name ='p8');

2.select_type

可以看id的执行实例,总共有以下几种类型:

3.table

table表示查询涉及的表或衍生的表:

explain select tt.* from (select u.* from user_info u,order_info o where u.id=o.user_id and u.id=1) tt

id为1的<derived2>的表示id为2的u和o表衍生出来的。

4.type

type 字段比较重要,它提供了判断查询是否高效的重要依据依据。 通过 type 字段,我们判断此次查询是 全表扫描 还是 索引扫描等。

type 常用的取值有:

通常来说, 不同的 type 类型的性能关系如下:

ALL < index < range ~ index_merge < ref < eq_ref < const < system

ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

5.possible_keys

它表示 mysql 在查询时, 可能使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 mysql 使用到. mysql 在查询时具体使用了哪些索引, 由 key 字段决定.

6.key

此字段是 mysql 在当前查询时所真正使用到的索引.比如请客吃饭,possible_keys是应到多少人,key是实到多少人.当我们没有建立索引时:

explain select o.* from order_info o where o.product_name= 'p1' and o.productor='whh';create index idx_name_productor on order_info(productor);drop index idx_name_productor on order_info;

建立复合索引后再查询:

7.key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用.

8.ref

这个表示显示索引的哪一列被使用了,如果可能的话,是一个常量。前文的type属性里也有ref.注意区别

9.rows

rows 也是一个重要的字段. mysql 查询优化器根据统计信息, 估算 sql 要查找到结果集需要扫描读取的数据行数.这个值非常直观显示 sql 的效率好坏, 原则上 rows 越少越好.可以对比key中的例子,一个没建立索引钱,rows是9,建立索引后,rows是4.

10.extra

explain 中的很多额外的信息会在 extra 字段显示, 常见的有以下几种内容:

五、优化案例

explain select u.*,o.* from user_info u LEFT JOIN order_info o on u.id=o.user_id;

执行结果,type有ALL,并且没有索引:

开始优化,在关联列创建索引,明显看到type列的ALL变成ref,并且用到了索引,rows也从扫描9行变成了1行:

这里面一般有个规律是:左链接索引加在右表上面,右链接索引加在左表上面。

六、是否需要创建索引?

索引虽然能非常高效的提高查询速度,同时却会降低更新表的速度。实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。

我是个普通的程序猿,水平有限,文章难免有错误,欢迎牺牲自己宝贵时间的读者,就本文内容直抒己见,我的目的仅仅是希望对读者有所帮助。

    推荐阅读
  • 新能源和锂材料价格持续升高(太魔幻电池片1.26元)

    值得一提的是,7月1日当晚,除了通威电池片宣布涨价,光伏行业还有两件大事发生。硅片新势力双良节能发布公告称,全资子公司双良硅材料(包头)有限公司与通威股份旗下四家子公司签订了为期4年的硅料长单,预计采购金额约为560亿元。而后通威股份发布公告,公司旗下四家子公司于近日与美科硅能源签订多晶硅长单销售合同,美科硅能源在2022年~2027年预计向公司合计采购25.61万吨多晶硅产品,预计销售总额约644.10亿元。

  • 街机经典游戏排行(游戏厅火爆一时的街机游戏王者之剑)

    《王者之剑》当年则发行了三部作品,每一部作品都曾经火爆一时。游戏中砍死敌人之后会出现血浆爆裂的效果,而且因为受到《野蛮人柯蓝》的影响,游戏中也有很多裸体镜头,虽然养眼,但看上去并不色情。最终BOSS是喷火龙。公主曾经答应过主角,只要能打死这条喷火龙,那王国所有的财产都是主角的了。打败喷火龙之后,男主离开了这里,寻找新的挑战。根据Gulestopalis这个名字推测,最终BOSS,很有可能就是初代《Gladiator》中的主角。

  • 电脑打不开qq空间显示页面(你修了我的电脑就上不了QQ空间了)

    前几天,打电话说无法上网,让我去上门看一下什么情况。一再强调自己不懂这个,没有修改过,都记在本子上,重新接上旧的路由器是没有问题,排除上网账号到期。直接把网线接到电脑上拨号,拨号错误代码691,只能让他重新打电话去修改密码。最后是由于自己记录的密码有误,重新修改密码之后,搞定!但是过了几天之后,忽然又来电话,说他上不了空间了!看来这是一个伤心的故事啊!

  • 三国演义里张飞谁演的(三国演义张飞扮演者李靖飞去世)

    据多方消息透露,11月24日晚23时左右,94版《三国演义》中张飞的扮演者李靖飞因病医治无效离世,终年65岁。公开资料显示,李靖飞1957年10月13日出生于河北省,河北省话剧院演员、戏剧家协会会员、影视家协会会员、国家一级演员。1994年参演电视剧《三国演义》饰演张飞一角,进入演艺圈。11月1日,94版《三国演义》关羽扮演者陆树铭因脑梗去世。这部留下无数经典的《三国演义》两位主要演员一个月内相继辞世,令人无比痛心。

  • 一些非官方的改版游戏是怎么做的(年度游戏的有力竞争者)

    在正式上手之前,我对《死亡循环》乃至Arkane一知半解,在通关之后,我却对其这家工作室的坚持与创作肃然起敬。有鉴于Arkane过往的履历,《死亡循环》势必也会在2021年度的各项游戏评选上有所斩获。想要解读《死亡循环》,不妨从三个关键词入手,沉浸式模拟、Arkane以及时间循环。《死亡循环》同理,它是沉浸式模拟游戏的延续,正如游戏发售时游戏导演DingaBakaba所说的,它身上流着Ar

  • 飘香藤有什么风水讲究(飘香藤风水讲究有什么)

    飘香藤的风水寓意,飘香藤是一种常绿藤本植物,以其独特而美丽的外观而闻名。但飘香藤是夹竹桃科植物,具有一定的毒性,在种植时要注意摆放忌讳。在风水里,飘香藤具有着兴旺爱情运势、减少病灾邪气等镇宅旺运的作用。飘香藤是夹竹桃科的一种灌木植物,虽然它的外表很漂亮,但是汁液里带有一定的毒性,很容易伤到人。所以在家中不宜养殖飘香藤。

  • 洗发水正确使用方法(如何正确使用洗发水)

    洗发水正确使用方法要点是让洗发水起泡,在手心中放入适量洗发水,然后开始打出泡沫吧。如果对于打泡沫不太擅长的话建议您可以使用起泡网,这样就可以简单打出丰富泡沫了。要领是用指腹像做按摩一样清洗头皮。冲洗时请用温水充分清洗,为了不让洗发水有残留,一定要充分冲洗干净。从头皮开始,以及洗发水容易残留的耳朵周围,发际线附近都要冲洗干净。冲洗水的温度与预洗水一样,在38度左右最合适。

  • 23岁就得了高血压是怎么得的呢(我已经看到有高中生患高血压了)

    当天,麻省总医院高血压科主任RandallZusman发声,呼吁人们关注高血压风险。RandallZusman表示,高血压看似是一种老年病,但是近年来高血压的发病逐渐呈现出年轻化的趋势,而且控制率却很低,仅仅只有16.8%。目前,RandallZusman团队正在研究超声肾脏去神经支配方法,这是一种通过破坏神经系统与肾脏的联系来降低血压的方法。

  • 在微信电脑版上怎么下载文件(有什么方法)

    跟着小编一起来看一看吧!在微信电脑版上怎么下载文件新增小程序面板,可以查看最近使用和已添加的小程序。聊天中可以引用更多类型的消息。聊天中可以拖拽消息,以转发到其他聊天或保存到电脑。可以设置聊天中的语音消息自动转为文字。

  • 职场遇到哪些人必须避而远(前行者中的4个招人恨角色)

    但是在皮埃尔骗保杀人后,陈亨礼的做法就让人看不过眼了:我们可以圆滑世故,可以逢迎上级,但不能草菅人命,毕竟做人做事还是要有底线的。因为没有直接的人命,所以列第4。于是投靠日本人,后被复兴社上海站特工刺杀。但鉴于他曾有过赴电刑不皱眉头的爷们时刻,将其列为第3。最怕上这种当,所以将其列为第2。正如裴如海,干尽了坏事,手上带了手铐,胸前绑了炸弹,被关在车中,从悬崖掉下去,车子爆炸了都还不死。