侯体宗的博客
  • 首页
  • Hyperf版
  • beego仿版
  • 人生(杂谈)
  • 技术
  • 关于我
  • 更多分类
    • 文件下载
    • 文字修仙
    • 群聊
    • 九宫格抽奖
    • 拼图
    • 消消乐
    • 相册

mysql中联合索引和多个单列索引的创建原则、索引命中场景示例

数据库  /  管理员 发布于 5个月前   379

下面以mysql8.0.28版本为例测试mysql中联合索引和多个单列索引的创建原则及使用中索引命中场景


创建索引原则

使用唯一索引

这个不用说性价比最好的,索引的基数越大,索引的效果越好。

使用短索引

尽量选择区分度高的列去建立索引
如果对字符串列进行索引,尽可能的指定一个前缀长度,
例如一个char(200)的列,前面10个或者20个字符进行索引能够进可能的减少索引的空间,也能使查询更快,较小的索引涉及的磁盘IO也较小,较短的值比较起来更快。

最左索引

创建一个N列索引的时候,实际上创建的是一个mysql可利用的n个索引,多列索引可以起到几个索引的作用,可以利用索引中最左边的列集来匹配行,这样的列集称为最左索引。

非必要不要添加索引

尽量去根据需求修改索引,而不是去新增索引
每个额外的索引都会占用额外的空间,降低写操作性能,在修改表的内容的时候,表的索引也会更新,索引越多,所花时间越长,索引太多,也会使mysql选择不到索要使用的最好索引,只保持所需要的索引有利于查询优化。

对InnoDB存储引擎的表

默认记录会按照一定的顺序保存,
如果有明确定义的主键,则按照主键顺序保存,
如果没有主键,但是有唯一索引,会按照唯一索引的顺序保存,
如果即没有主键,也没有唯一索引,那么表中会自动生成一个内部列(按照主键和内部列进行访问是最快的),
inndb表的普通索引都会保存主键的键值,所以主键要尽可能的选择较短的数据类型,可以有效的减少索引的磁盘占用,提高索引的缓存效果。


联合索引


1.创建联合索引示例测试表

CREATE TABLE `multi_sy_test` (
   `id` int NOT NULL AUTO_INCREMENT,
   `a` varchar(255) NOT NULL,
   `b` varchar(255) NOT NULL,
   `c` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='联合索引测试表';

2.添加联合索引

ALTER TABLE `multi_sy_test` ADD index index_a_b_c(`a`, `b`, `c`);

3.sql命中场景

SELECT * FROM `multi_sy_test` WHERE a='xxx' and b='xxx' and c='xxx'; 命中
SELECT * FROM `multi_sy_test` WHERE a='xxx' and b='xxx'  命中
SELECT * FROM `multi_sy_test` WHERE a='xxx' and c='xxx'  命中
SELECT * FROM `multi_sy_test` WHERE c='xxx' and b='xxx' and a='xxx';  命中
SELECT * FROM `multi_sy_test` WHERE a='xxx'; 命中
---
SELECT * FROM `multi_sy_test` WHERE a='xxx' or b='xxx' or c='xxx'; 未命中
SELECT * FROM `multi_sy_test` WHERE  b='xxx' and c='xxx';未命中
SELECT * FROM `multi_sy_test` WHERE  b='xxx'; 未命中
SELECT * FROM `multi_sy_test` WHERE  c='xxx'; 未命中

ps:

创建联合索引时一定要注意创建的列顺序,对索引中的所有列或者前几列执行搜索时,多列索引非常有用


多个单列索引

1.创建多个单列索引示例测试表

CREATE TABLE `single_sy_test` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `a` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `b` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `c` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='多个单列索引测试表';

2.创建多个单独的索引

ALTER TABLE `single_sy_test` ADD index index_a(a);
ALTER TABLE `single_sy_test` ADD index index_b(b);
ALTER TABLE `single_sy_test` ADD index index_c(c);

3.多个单列索引命中场景

SELECT * FROM `single_sy_test` WHERE a='xxx';命中index_a
SELECT * FROM `single_sy_test` WHERE b='xxx';命中index_b
SELECT * FROM `single_sy_test` WHERE c='xxx';命中index_c
SELECT * FROM `single_sy_test` WHERE a='xxx' and b='xxx' and c='xxx';只命中index_a 
SELECT * FROM `single_sy_test` WHERE a='xxx' and b='xxx'; 只命中index_a
SELECT * FROM `single_sy_test` WHERE b='xxx' and a='xxx'; 只命中index_a
SELECT * FROM `single_sy_test` WHERE b='xxx' and c='xxx'; 只命中index_b
SELECT * FROM `single_sy_test` WHERE c='xxx' and b='xxx'; 只命中index_b
SELECT * FROM `single_sy_test` WHERE a='xxx' or b='xxx'; 未命中

ps:

多个单列索引都有效的时候mysql的优化策略选取了它认为最高效的一个index,而不会都使用


最后

创建有效的索引将会大幅度的提高了查询性能,比如

索引大大减少了服务器需要扫描的数据量;

索引可以帮助服务器避免排序和临时表;

索引可以将随机I/O 变为顺序I/O;

等等...

当然,如果创建无效的索引那就反之,并且是加倍的,希望文章能帮助你


  • 上一条:
    在go语言中使用GORM或SQL驱动程序读取时间戳时报错:Scan error on column index ...
    下一条:
    Laravel 9.35版本发布
  • 昵称:

    邮箱:

    0条评论 (评论内容有缓存机制,请悉知!)
    最新最热
    • 分类目录
    • 人生(杂谈)
    • 技术
    • linux
    • Java
    • php
    • 框架(架构)
    • 前端
    • ThinkPHP
    • 数据库
    • 微信(小程序)
    • Laravel
    • Redis
    • Docker
    • Go
    • swoole
    • Windows
    • Python
    • 苹果(mac/ios)
    • 相关文章
    • mysql5.7中实现分区表及分区where in查询示例及分区分表对比浅析(0个评论)
    • mysql中sql_mode的各模式浅析(0个评论)
    • 预处理之SQL参数化查询是如何防止SQL注入的浅析(0个评论)
    • 使用Navicat把mysql5.7版本的数据库导入至mysql8.1版本中流程步骤(0个评论)
    • 在mysql中设置表字段中COLLATE、CHARSET详解(0个评论)
    • 近期文章
    • 在go语言中使用GoPDF包把html生成PDF文件示例(0个评论)
    • 在go语言中创建和解析(读取)符号链接示例(0个评论)
    • ubuntu 22.04系统中报错:Python 3.6 is no longer supported by the Python core team...解决方式(0个评论)
    • Laravel 10.4版本发布(0个评论)
    • mysql5.7中实现分区表及分区where in查询示例及分区分表对比浅析(0个评论)
    • nginx + vue配置实现同域名下不同路径访问不同项目(0个评论)
    • 在laravel框架中的5个HTTP客户端技巧分享(0个评论)
    • 在go语言中使用FFmpeg库实现PCM音频文件编码为mp3格式文件流程步骤(0个评论)
    • gopacket免安装Pcap实现驱动层流量抓包流程步骤(0个评论)
    • 在laravel项目中实现密码强度验证功能推荐扩展包:password-strength(0个评论)
    • 近期评论
    • 博主 在

      2023年国务院办公厅春节放假通知:1月21日起休7天中评论 @ xiaoB 你只管努力,剩下的叫给天意;天若有情天亦老,..
    • xiaoB 在

      2023年国务院办公厅春节放假通知:1月21日起休7天中评论 会不会春节放假后又阳一次?..
    • BUG4 在

      你翻墙过吗?国内使用vpn翻墙可能会被网警抓,你需了解的事中评论 不是吧?..
    • 博主 在

      go语言+beego框架中获取get,post请求的所有参数中评论 @ t1  直接在router.go文件中配就ok..
    • Jade 在

      如何在MySQL查询中获得当月记录中评论 Dear zongscan.com team, We can skyroc..
    • 2017-06
    • 2017-08
    • 2017-09
    • 2017-10
    • 2017-11
    • 2018-01
    • 2018-05
    • 2018-10
    • 2018-11
    • 2020-02
    • 2020-03
    • 2020-04
    • 2020-05
    • 2020-06
    • 2020-07
    • 2020-08
    • 2020-09
    • 2021-02
    • 2021-04
    • 2021-07
    • 2021-08
    • 2021-11
    • 2021-12
    • 2022-02
    • 2022-03
    • 2022-05
    • 2022-06
    • 2022-07
    • 2022-08
    • 2022-09
    • 2022-10
    • 2022-11
    • 2022-12
    • 2023-01
    • 2023-03
    Top

    Copyright·© 2019 侯体宗版权所有· 粤ICP备20027696号 PHP交流群

    侯体宗的博客