MySQL之索引及索引优化

最新推荐文章于 2022-01-24 13:27:13 发布
最新推荐文章于 2022-01-24 13:27:13 发布
阅读量90 收藏
点赞数
分类专栏： MySQL 文章标签： 索引 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48465257/article/details/113885842
版权

文章目录

什么是索引

  • 索引（index）是帮助MySQL高效获取数据的数据结构。
  • 索引的目的在于提高查询效率，类似于字典。
  • 索引是排好序的快速查找数据结构。
  • 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以 某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
  • 平常所说的索引，如果没有特别指明，都是B树（多路索引树，并不一定是二叉树）结构组织的索引。

优势

  • 提高数据检索的效率，降低数据库的IO成本。
  • 通过索引列队数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是要占空间的。
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新条件了索引列的字段，都会调整因为更新所带来的键值变化会的索引信息。

索引分类

  • 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
  • 唯一索引：索引列的值必须唯一，但允许有空值。
  • 复合索引：一个索引包含多个列。

需要索引情况

  • 主键自动建立唯一索引。
  • 频繁作为查询条件的字段应该创建索引。
  • 查询中与其他表关联的字段，外键关系建立索引。
  • 频繁更新的字段不适合创建索引。
  • where条件里用不到的字段不创建索引。
  • 在高并发下倾向创建组合索引。
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
  • 查询中统计或者分组字段。
  • 一个索引的选择性越接近于1，这个索引的效率就越高。

不需要索引情况

  • 表记录太少（300万左右）。
  • 经常增删改的表。
  • 数据重复且分布平均的表字段，应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含许多重复的内容，为他建立索引就没有太大的实际效果。

索引单表优化

建立数据表

create table `article`(
`id` int primary key auto_increment,
`author_id` int,
`category_id` int,
`views` int,
`comments` int,
`title` varchar(100),
`content` text);

插入数据

insert into `article` (`author_id`, `category_id`, `views`, `comments`, `title`, `content`) 
values (1, 1, 1, 1, "1", "1")
insert into `article` (`author_id`, `category_id`, `views`, `comments`, `title`, `content`) 
values (2, 2, 2, 2, "2", "2")
insert into `article` (`author_id`, `category_id`, `views`, `comments`, `title`, `content`) 
values (3, 1, 1, 3, "3", "3")

没索引查询

explain 
select `id`, `author_id` from `article` 
where `category_id` = 1 and `comments` > 1 
order by `views` desc 
limit 1;

性能分析
在这里插入图片描述

优化

尝试一

建立新索引

create index `idx_article_ccv` on `article` (`category_id`, `comments`, `views`);

性能分析
在这里插入图片描述

尝试二

删除原先索引

drop index `idx_article_ccv` on `article`

建立新索引

create index `idx_article_cv` on `article` (`category_id`, `views`)

性能分析
在这里插入图片描述

索引两表优化

建立数据表

create table `class`(
`id` int primary key auto_increment,
`card` int)

create table `book`(
`book_id` int primary key auto_increment,
`card` int)

插入数据

insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))
insert into `class` (`card`) values (floor(1+(rand()*20)))

insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))
insert into `book` (`card`) values (floor(1+(rand()*20)))

没索引查询

explain 
select * from `class` left join `book` on `class`.`card` = `book`.`card`

性能分析
在这里插入图片描述

优化

尝试一

左连接，索引加在右表（优）

create index `idx_book_card` on `book` (`card`)

性能分析
在这里插入图片描述

尝试二

左连接，索引加在左表
删除原先索引

drop index `idx_book_card` on `book`

建立新索引

create index `idx_class_card` on `class` (`card`)

性能分析
在这里插入图片描述

索引三表优化

建立数据表

create table `phone`(
`phone_id` int primary key auto_increment,
`card` int)

插入数据

insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))
insert into `phone` (`card`) values (floor(1+(rand()*20)))

没索引查询

删除原先索引

drop index `idx_class_card` on `class`

explain 
select * from `class` left join `book` on `class`.`card` = `book`.`card` 
left join `phone` on `book`.`card` = `phone`.`card`

性能分析
在这里插入图片描述

优化

尝试一

建立新索引

create index `idx_book_card` on `book` (`card`)
create index `idx_phone_card` on `phone` (`card`)

性能分析
在这里插入图片描述

索引优化

建立数据表

create table `staffs`(
`id` int primary key auto_increment,
`name` varchar(30),
`age` int,
`pos` varchar(20),
`add_time` timestamp)

插入数据

insert into `staffs` (`name`, `age`, `pos`, `add_time`) values ("不知火舞", 21, "法师", now())
insert into `staffs` (`name`, `age`, `pos`, `add_time`) values ("猪八戒", 21, "坦克", now())
insert into `staffs` (`name`, `age`, `pos`, `add_time`) values ("白起", 21, "坦克", now())

建立索引

create index `idx_staffs_name_pos_age` on `staffs` (`name`, `age`, `pos`)

全值匹配

最左前缀法则：查询从索引的最左前列开始，并且不跳过索引中的列。

explain select * from `staffs` where `name` = "不知火舞"

在这里插入图片描述

explain select * from `staffs` where `name` = "不知火舞" and age = 21

在这里插入图片描述

explain select * from `staffs` where `name` = "不知火舞" and age = 21 and pos = "法师"

在这里插入图片描述

explain select * from `staffs` where age = 21 and pos = "法师"

在这里插入图片描述

explain select * from `staffs` where pos = "法师"

在这里插入图片描述

不在索引列做任何操作

不使用计算、函数、（自动或手动）类型转换

explain select * from `staffs` where `name` = "不知火舞"

在这里插入图片描述

explain select * from `staffs` where left(`name`,4) = "不知火舞"

在这里插入图片描述

存储引擎不能使用索引中范围条件右边的列

explain select * from `staffs` where `name` = "不知火舞" and age = 21 and pos = "法师"

在这里插入图片描述

explain select * from `staffs` where `name` = "不知火舞" and age > 11 and pos = "法师"

在这里插入图片描述
pos索引没有用到

使用覆盖索引

索引列和查询列一致

explain select * from `staffs` where `name` = "不知火舞" and age = 21 and pos = "法师"

在这里插入图片描述

explain select `name`, `age`, `pos` from `staffs` where `name` = "不知火舞" and age = 21 and pos = "法师"

在这里插入图片描述

不使用不等于

explain select * from `staffs` where `name` = "不知火舞"

在这里插入图片描述

explain select * from `staffs` where `name` != "不知火舞"

在这里插入图片描述

不使用 is null 或 is not null

explain select * from `staffs` where `name` is null

在这里插入图片描述

explain select * from `staffs` where `name` is not null

在这里插入图片描述

谨慎使用like

explain select * from `staffs` where `name` = "不知火舞"

在这里插入图片描述

explain select * from `staffs` where `name` like "%不知火舞%"

在这里插入图片描述

explain select * from `staffs` where `name` like "%不知火舞"

在这里插入图片描述

explain select * from `staffs` where `name` like "不知火舞%"

在这里插入图片描述
使用覆盖索引解决

explain select `name`, `pos`, `age` from `staffs` where `name` like "%不知火舞%"

在这里插入图片描述

字符串加引号

insert into `staffs` (`name`, `age`, `pos`, `add_time`) values ("100", 25, "不知道", now())

explain select * from `staffs` where `name` = "100"

在这里插入图片描述

explain select * from `staffs` where `name` = 100

在这里插入图片描述

少用or

explain select * from `staffs` where `name` = "白起" or "猪八戒"

在这里插入图片描述

查询优化

小表驱动大表

select * from `A` where `id` in (select `id` from `B`)

当B表的数据集必须小于A表的数据集时，用in优于exists。

select * from `A` where exists (select 1 from `B` where `B`.`id` = `A`.`id`)

当A表的数据集必须小于B表的数据集时，用exists优于in。

  • exists
    select * from table where exists (subquery)
    将主查询的数据，放到子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据结果是否得以保留

order by 关键字优化

建立数据表

create table `person`(
`id` int primary key auto_increment,
`age` int, 
`birth` timestamp)

插入数据

insert into `person` (`age`, `birth`) values (22, now())
insert into `person` (`age`, `birth`) values (23, now())
insert into `person` (`age`, `birth`) values (24, now())

建立索引

create index `idx_person_age_birth` on `person` (`age`, `birth`)

尽量使用index方式排序

explain select * from `person` where `age` > 20 order by `age`

在这里插入图片描述

explain select * from `person` where `age` > 20 order by `age`, `birth`

在这里插入图片描述

explain select * from `person` where `age` > 20 order by `birth`

在这里插入图片描述

explain select * from `person` where `age` > 20 order by `birth`, `age`

在这里插入图片描述

explain select * from `person` order by `age` asc, `birth` desc

在这里插入图片描述

  • order by语句使用索引最左前列。
  • 使用where子句与order by子句条件列组合满足索引最左前列。
独守丶轩
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Python之MySQL优化(上)
baidu_39394442的博客
10-09 407
1.索引优化 1.1 索引的分类 分类角度 索引名称 数据结构 B+树，Hash索引，B-Tree 等 存储层面 聚簇索引，非聚簇索引 逻辑层面 主键索引，普通索引，组合索引，唯一索引，空间索引 1.2 回表 假设我们执行一条查询语句 select * from person where ID = 6,因为直接使用的是主键ID查询，所以就会用主键索引，由于主键索引直接关联了整行所有数据，所以，引擎只要执行一次就能查询出结果。 如果执行的sql语句是非主键索引 select * f
索引：“其实我是一种数据结构”
weixin_44988779的博客
09-07 1001
“人家真的 不是目录” 索引的自白引言索引概述索引的创建索引的删除索引的查询explain查询有无索引show keys查询何种索引索引的原理聚簇索引辅助索引联合索引索引存在的问题 引言 大家好，我是索引，在MySQL中你可以使用CREATE INDEX indexName ON table_name (column_name)或alter table table_name add index(column_name)来创造我！在这里我主要澄清一下，我不是目录，你可以认为我是一种排序的数据结构，实在不行你就
索引失效原因总结
热门推荐
Colin Lau Oracle
02-28 5万+
今天一个同事突然问我索引为什么失效。说实在的，失效的原因有多种： 但是如果是同样的sql如果在之前能够使用到索引，那么现在使用不到索引，以下几种主要情况: 1. 随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致CBO计算走索引花费大于走全表） 2. 统计信息失效      需要重新搜集统计信息 3. 索引本身失效      需要重建索引 下面是一些不会使用到索
索引失效
04-06 85
在此仅讨论是否使用INDEX问题, 不涉及执行时间长短问题 IS NULL 或IS NOT NULL操作 （判断字段是否为空）...
聊聊索引失效？失效的原因是什么？
小林coding
01-24 6485
大家好，我是小林。 在工作中，如果我们想提高一条语句查询速度，通常都会想对字段建立索引。 但是索引并不是万能的。建立了索引，并不意味着任何查询语句都能走索引扫描。 稍不注意，可能你写的查询语句是会导致索引失效，从而走了全表扫描，虽然查询的结果没问题，但是查询的性能大大降低。 今天就来跟大家盘一盘，常见的 6 种会发生索引失效的场景。 不仅会用实验案例给大家说明，也会清楚每个索引失效的原因。 发车！ 索引存储结构长什么样？ 我们先来看看索引存储结构长什么样？因为只有知道索引的存储结构，才能更好的理解索引失效
Mysql索引步骤及优化
12-14
MySQL索引是数据库管理系统中用于加速数据检索的重要工具。它的工作原理类似于书籍的目录，能够快速定位到数据表中的特定记录。索引分为聚簇索引和非聚簇索引两类。聚簇索引根据数据的物理存储位置进行排序，而非聚...
MySQL索引原理及慢查询优化
12-14
总的来说，优化MySQL的慢查询需要结合索引原理、查询优化技巧、数据库设计和运维实践。通过对业务需求的理解，我们可以制定合适的索引策略，改进SQL语句，从而提升数据库的整体性能，满足高并发、大数据量的业务需求...
Mysql数据库之索引优化
09-10
总结来说，MySQL索引优化涉及了对索引类型的选择、索引字段的选择、索引策略的制定以及对索引的持续监控和调整。通过对索引的有效管理和优化，可以显著提高数据库的查询性能，使系统的整体性能得到提升。对于希望...
mysql性能优化之索引优化
09-10
MySQL性能优化中的索引优化是提升数据库查询效率的关键技术。索引是一种特殊的数据结构，它允许数据库快速定位和访问表中的特定数据行，类似于书籍的目录，帮助读者迅速找到所需内容。MySQL中，默认的索引类型是B+树...
索引失效和注意事项
不远远方
08-24 1万+
https://blog.csdn.net/hehexiaoxia/article/details/54312130 索引失效的情况 如果是同样的sql如果在之前能够使用到索引，那么现在使用不到索引，以下几种主要情况: 随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致CBO计算走索引花费大于走全表） 统计信息失效 需要重新搜集统计信息...
索引失效的情况
动哒APP—运动健康科技提供商
07-20 140
索引失效的情况，网上列出的有如下情况： 没有查询条件，或者查询条件没有建立索引 在查询条件上没有使用引导列 查询的数量是大表的大部分，应该是30％以上。 索引本身失效 查询条件使用函数在索引列上（见12） 对小表查询 提示不使用索引 统计数据不真实 CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。 10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(
索引失效的几种情况
qq_41452455的博客
04-20 236
常见的有如下几种情况 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL COMMENT '名字', `sex` tinyint(5) DEFAULT '0' COMMENT '性别', `address` varchar(255) DEFAULT NULL COMMENT '住址', `card_id` varchar(18) DEFAULT NULL C
mysql group 索引失效_介绍mysql索引失效的情况
weixin_42410477的博客
02-17 1271
mysql视频教程栏目索引失效的情况。索引对于MySQL而言，是非常重要的篇章。索引知识点也巨多，要想掌握透彻，需要逐个知识点一一击破，今天来先来聊聊哪些情况下会导致索引失效。图片总结版相关免费学习推荐：mysql视频教程全值匹配(索引最佳)explain select * from user where name = 'zhangsan' and age = 20 and pos = 'cxy'...
使用mysql数据库语句_使用MySQL数据库 SQL语句
weixin_33758343的博客
01-19 211
1.查看当前服务器数据库中有哪些库？SHOW DATABASES; ###查看有哪些库2.查看当前使用的库有哪些表？USE +要查询的库名SHOW TABLES; ###查询库中有哪些表3.查看标的结构？USE +要使用的库名DESCRIBE +表名 ###查看表结构4.创建新的库？CREATE DATABASE +表名 ###创建库5.创建新的表CREATE TABLE...
MySQL索引优化深度解析
本文主要探讨了MySQL如何使用索引以及索引优化的策略，涵盖了索引的分类、MyISAM和InnoDB存储引擎的索引结构、索引的优缺点以及设计原则。 在MySQL中，索引对于提高查询速度至关重要。它们可以显著提升全值匹配和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值