mysql的索引使用的案例

最新推荐文章于 2024-05-30 09:50:34 发布
最新推荐文章于 2024-05-30 09:50:34 发布
阅读量1.1k 收藏 11
点赞数 5
分类专栏： # mysql数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/K_520_W/article/details/108432541
版权
  1. 单表索引优化案例
    1）表结构
           
    DROP TABLE IF EXISTS `article`;
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`author_id` INT (10) UNSIGNED NOT NULL COMMENT '作者ID',
`category_id` INT(10) UNSIGNED NOT NULL COMMENT '分类ID',
`views` INT(10) UNSIGNED NOT NULL COMMENT '被查看次数',
`comments` INT(10) UNSIGNED NOT NULL COMMENT '回帖的备注',
`title` VARBINARY(255) NOT NULL COMMENT '文章主题',
`content` TEXT NOT NULL COMMENT '文章内容'
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
 
SELECT * FROM article;

    2）需求
           查询category_id为1且comments大于1的情况下，views最多的article_d
    3）开始使用的sql
           SELECT id, author_id FROM article WHERE category_id = 1 and comments > 1 ORDER BY views DESC LIMIT 1;
            
    4）使用explain查看sql的执行计划
          
    可以发现type为ALL这个是全表扫描，而且在Extra中存在Using filesort这个也是最坏的情况，现在数据量小，后面一旦数据量上来，就会出现问题了，所以优化是必须的。
    5）开始创建索引进行sql优化
          a：根据where后面的条件创建组合索引
           
    CREATE INDEX idx_article_ccv on article(category_id,comments,views);
         b：查看索引
        
    6）再次执行sql计划看一下效果
        
    通过执行计划我们发现key中使用到了我们刚刚建立的索引了，并且type也变成了range,解决了全表扫描的问题，但是Extra
    中还是存在Using filesort问题，这个是为什么呢？
    7）问题分析
          因为上面我们建立的索引是三个字段category_id，comments，views。而条件1是category_id等于一个常量，那么我们使用
         上了索引，而我们的第二个字段comments却是一个范围的条件，范围的条件会导致索引的失效，从而导致mysql内部产生一个
        内部排序，最后导致后面的索引失效
    8）解决办法
        a：删除之前的索引
             
       b：上面的问题是因为comments的条件是一个范围，从而导致后面的索引都失效了，那么我们现在可以将comments这个字段
             的索引去掉，只留category_id和views两个字段的索引即可
            
       c：最后看一下效果
            
           可以发现不仅解决了Using filesort的问题，type也变成了ref级别，变得更快了。
  2. 双表索引优化案例
     1）表结构
            
    CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
`card` INT (10) UNSIGNED NOT NULL COMMENT '分类card'
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
`card` INT (10) UNSIGNED NOT NULL COMMENT '分类card'
);
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 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)));
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)));

     2）需求
           使用class表左连接book表关联查询
    3）sql语句
          SELECT * FROM class c left join book b on c.card = b.card
    4）执行计划效果
         
         我们发现出现了全表扫描的问题。
    5）尝试先为book表的card字段添加索引试试
         
         在看执行效果：
         
        我们发现book表以及使用了索引，但是class表还是全表扫描。但是比上面的两个表都是全表扫描要好很多了。
    6）我们再尝试将book的索引去掉，在class的card字段上加上索引试试。
         
         查看执行结果：
          
          可以发现type由ref变为了index，说明效果不如将索引加在book表的card字段上好，而且这个效果的rows两张表
          都是20，效果比上面加在book表时的一个20，一个1的效果也要差。
         这是为什么呢？
    7）分析，为什么左连接时，索引加在右边的表比加在左边的表效果好呢？
           这个是由左连接的特性决定的，left join 条件用于确定如何从右表搜索行，而左边一定都有。所以右边是我们的关键点，
           右表一定要建立索引。所以我们还是要将当前的class表索引删掉，将book表的索引加上
           
    8）所以目前我们将索引建立在book这个右表上的效果是
         
    9）结论：
          左连接时，索引建立在右表，右连接时索引建立在左表。
          注意：上面的例子为什么不两张表都加上索引呢？因为这个是左连接，左表的表时查询所有数据，加不加没有意义。
  3. 三表索引优化案例
     1）表结构是在上面两张表的基础上加上一张phone表
           
    CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

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)));
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)));

    2) 需求sql
         SELECT * FROM class c left join book b on c.card = b.card left join phone p on b.card = p.card;
    3）建立索引，在后面book表以及phone表的关联字段上建立索引
          
    4）执行sql计划，查看效果
           
           可以发现后两行的type都是ref，且rows都优化的很好。
    5）总结
          

爱上口袋的天空
关注
  • 5
    点赞
  • 踩
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql的索引实例_数据库索引实例
weixin_34802266的博客
02-03 996
索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(B 树)中，使 SQL Server 可以快速有效地查找与键值关联的行。表或视图可以包含以下类型的索引:* 聚集o 聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。o 只有...
MYSQL索引使用案例分析
weixin_39539399的博客
06-28 4857
目录   一、创建数据表 二、向数据表中插入字段 三、创建联合索引在a1,a2,a3,a4字段上 四、索引案例分析 1、字符串未使用引号，导致索引失效 2、符合索引最左原则使用不当，导致索引失效 3、范围查找导致索引失效 4、order by使用不当，导致索引失效 4、group by使用不当导致索引失效 5、通配符like的使用不当导致索引失效 6、MYSQL 中!=,&...
Mysql索引分类及其使用实例
qq_45752401的博客
07-19 2478
前面已经知道，Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。任何事物都是有两面性的，Hash索引也一样，虽然Hash索引效率高，但是Hash索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。......
【小白专用 已验证24.6.13】mysql 添加索引-结合实战项目
zgscwxd的博客
05-30 244
MYSQL增加索引
MySQL索引实战汇总
weixin_34214500的博客
09-06 224
MySQL索引对数据检索的性能至关重要，盲目的增加索引不仅不能带来性能的提升，反而会消耗更多的额外资源，本篇总结了一些MySQL索引实战经验。索引是用于快速查找记录的一种数据结构。索引就像是数据库中数据的目录，数据库在查询时，首先在索引中找到匹配的值，然后根据这个匹配值找到对应的数据行。名词解释聚簇索引聚簇索引的顺序就是数据的物理存储顺序，索引中数据域存储的就是实际的数据，一个表最多只能有一个聚簇...
MySQL索引一些常用案例
一个人的Code博客
03-18 341
索引案例分析 先创建一些假数据： create table test03( id int primary key not null auto_increment, c1 char(10), c2 char(10), c3 char(10), c4 char(10), c5 char(10) ); insert into test03(c1,c2,c3,c4,c5)values('a1','a2','a3','a4','a5'); insert into test03(c1,c2,c3,c4,c5)va
MySQL索引用法实例分析
09-10
总结，理解并正确使用MySQL索引及其相关的查询优化策略是提升数据库性能的关键。合理地创建复合索引、避免 `IN` 和 `OR` 引起的全表扫描，以及掌握 `UNION` 和 `UNION ALL` 的正确用法，都能有效地提高查询效率，...
mysql索引覆盖实例分析
01-19
本文实例讲述了mysql索引覆盖。分享给大家供大家参考，具体如下： 索引覆盖 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据。这种查询速度非常快,称为“索引覆盖”。 假...
mysql索引对排序的影响实例分析
09-09
在本实例分析中，我们将深入探讨MySQL索引如何影响排序速度，通过具体的操作步骤和测试来展示其效果。 首先，创建一个名为`t15`的测试表，包含`id`、`cat_id`、`price`和`name`四个字段，其中`id`是主键。接着，...
MySQL 索引及优化实战(转载自蒲苇 CSDN技术头条)
z875710239的博客
03-27 1282
索引概念和作用索引是一种使记录有序化的技术，它可以指定按某列/某几列预先排序，从而大大提高查询速度（类似于汉语词典中按照拼音或者笔画查找）。索引的主要作用是加快数据查找速度，提高数据库的性能。MySQL 索引类型从物理存储角度上，索引可以分为聚集索引和非聚集索引。1. 聚集索引（Clustered Index）聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引。2. 非聚集索引（Non-...
mysql索引实例-explain-type类型分析
qq_38331606的博客
08-09 856
知识概括 a. all 全表扫描 b. index 按照索引顺序的全表扫描 c. range 有范围的索引扫描 ,between，and以及'>','<'外，in和or均是索引范围扫描 d. ref 使用了非主键或者非唯一索引的普通索引(即索引可以重复情况下的) e....
mysql测试数据图表_mysql测试数据表
weixin_29191761的博客
01-28 467
1.截取至后盾人用于mysql数据测试请在navicat中执行一下命令生成测试数据表/*Navicat Premium Data TransferSource Server : 我的本地连接Source Server Type : MySQLSource Server Version : 50726Source Host : localhost:3306S...
主流数据库之索引及其例子
m0_67393619的博客
06-21 1597
目录文章目录前言索引概述概念在数据库中使用索引的优缺点:索引分类普通索引唯─性索引主键索引全文索引空间索引其他分类索引设置的基本原则创建索引使用CREATE INDEX语句建立索引创建表时创建索引修改表时创建索引创建索引注意事项索引性能分析查看profile是否开启的设置开启profile查看SQL执行的耗时详情删除索引利用drop index语句删除索引利用alter table语句删除索引1.索引，是由数据库表中一列或多列组合而成的一种特殊的数据结构，利用索引可以快速查询数据库表中的特定记录信息（索引类
mysql 索引实例_Mysql 索引案例学习
weixin_42497839的博客
01-19 244
理解索引最好的办法是结合示例，所以这里准备了一个索引的案例。假设要设计一个在线约会网站，用户信息表有很多列，包裹国家，地区，城市，性别，眼睛颜色，等等。完整必须支持上面这些特征的各种组合来搜索用户，还不行一些根据用户的最后在线时间，其他会员对用户的屏风等对用户进行排序并对结果进行限制。如何世界索引满足上面的负载需求呢？出人意料的是第一件需要考虑的事情是需要使用索引来排序，还是先检索数据再排序。使用...
以一个简单的例子说明MySQL的索引
m0_51321956的博客
03-04 161
比如每8栋楼排成一列，你只能看到每一列的第一栋楼的号码，现在你要去18号楼，你看到的第一列的楼栋号是1，第二列的楼栋号是9，你就知道18号楼不在第一列，然后看第三列的楼栋号是17，第四列的楼栋号是25，你就知道去第三列找第18号楼，这是顺序查找，可以通过二分查找加快查找速度。同理，你知道去17到20这个区找18号楼，于是你就走入17到20这个区，相当于加载17号到20号上的磁盘页到内存中，如果你找到18号后，又想找到19号，20号，就直接在这个区里顺序找，因为你就在这个区，相当于数据就在内存中。
Mysql学习笔记（全）
m0_67402914的博客
06-06 1354
目录MyISAM和InnoDB性能下降SQL慢的原因：Mysql执行顺序SQLJoin索引索引的优劣索引分类mysql索引结构那些情况建索引哪些情况不要建索引性能分析Extra索引优化索引口诀索引案例Order By 索引优化exsitesMysql慢查询日志命令函数操作批量插入数据show Profile分析sql全局查询日志Mysql锁锁命令表锁：读锁表锁：写锁行锁事务行锁测试间隙锁行锁总结对比MyISAMInnoDB主外键不支持支持事务不支持支持行表锁表锁，操作时即使操作一条记录也会锁住一整张表，不适
mysql使用索引实例教程_MySQL索引的详解及实例教程
weixin_36352023的博客
02-03 142
MySQL 索引MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。创建索引时，你需要确保该索引是应用在...
mysql多列索引用处_MySQL索引使用说明(单列索引和多列索引)
weixin_29927289的博客
02-18 146
1. 单列索引在性能优化过程中，选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列：在Where子句中出现的列，在join子句中出现的列。请看下面这个查询：这个查询与前面的查询略有不同，但仍属于简单查询。由于age是在Select部分被引用，MySQL不会用它来限制列选择操作。因此，对于这个查询来说，创建age列的索引没有什么必要。下面是一个更复杂的例子：与前面的例子一...
mysql使用索引实例教程_实例介绍MySQL索引的使用
weixin_39628186的博客
02-03 135
mysql教程栏目实例讲解MySQL索引的使用更多相关免费学习推荐：mysql教程(视频)MySQL索引的使用实例一. 慢查询日志二. 查询分析器——explain三. 索引的基本使用四. 复合索引五. 覆盖索引一. 慢查询日志//查看是否开启慢查询日志mysql> show variables like '%slow%';//临时开启慢查询日志mysql> set global sl...
mysql索引的实例
最新发布
07-04
MySQL中的索引是一种数据结构，用于提高数据库查询效率，它们类似于书籍的目录，可以帮助快速定位到所需的信息。让我们来看几个例子： 1. **单列索引（B-Tree Index）**：这是最常见的索引类型，适用于一个列或字段。例如，在`users`表中查找所有姓张的人，你可以为`last_name`列创建一个索引。SQL查询会更快地找到所有名字为“张”的记录。 ```sql CREATE INDEX idx_lastname ON users (last_name); ```

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值