Mysql 索引该如何设计与优化
作者:0xBoo 发布时间:2024-01-21 19:22:19
目录
什么是索引?
最左前缀匹配原则
如何计算 key_len
索引优化
创建索引规范
什么是索引?
数据库索引是一种数据结构,它以额外的写入和存储空间为代价来提高数据库表上数据检索操作的速度。通俗来说,索引类似于书的目录,根据其中记录的页码可以快速找到所需的内容。—— *
常见索引有哪些?
普通索引:最基本的索引,没有任何限制
唯一索引:与”普通索引“类似,不同的就是:索引列的值必须是唯一,但允许有空值
主键索引:它是一种特殊的索引,不允许有空值
全文索引:仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时占空间
组合索引:为了提高多条件查询效率,可建立组合索引,遵循"最左前缀匹配原则"
这里以相对复杂的组合为例,介绍如何优化。
最左前缀匹配原则
首先我们要知道什么是最左前缀匹配原则。
最左前缀匹配原则是指在使用 B+Tree 联合索引进行数据检索时,MySQL 优化器会读取谓词(过滤条件)并按照联合索引字段创建顺序一直向右匹配直到遇到范围查询或非等值查询后停止匹配,此字段之后的索引列不会被使用,这时计算 key_len
可以分析出联合索引实际使用了哪些索引列。
如何计算 key_len
通过 key_len
计算也帮助我们了解索引的最左前缀匹配原则。
key_len
表示得到结果集所使用的选择索引的长度[字节数],不包括 order by
,也就是说如果 order by
也使用了索引则 key_len
不计算在内。
在计算 key_len
之前,先来温习一下基本数据类型(以UTF8 编码为例):
类型 | 所占空间 | 不允许为NULL额外占用 |
---|---|---|
char | 一个字符三个字节 | 一个字节 |
varchar | 一个字符三个字节 | 一个字节 |
int | 四个字节 | 一个字节 |
tinyint | 一个字节 | 一个字节 |
测试数据表如下:
CREATE TABLE `test_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NOT NULL,
`b` int(11) DEFAULT NOT NULL,
`c` int(11) DEFAULT NOT NULL,
PRIMARY KEY (`id`),
KEY `test_table_a_b_c_index` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
命中索引:
mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 12 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
可以看到 key_len = 12
,这是如何计算的呢?
因为字符集是 UTF8,一个字段占用四个字节,三个字段就是 4 * 3 = 12 字节。
是否允许为 NULL,如果允许为 NULL,则需要用额外的字节来标记该字段,不同的数据类型所需的字节大小不同。
mysql> ALTER TABLE `test_table` CHANGE `a` `a` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `c` `c` INT(11) NULL;
mysql> ALTER TABLE `test_table` CHANGE `b` `b` INT(11) NULL;
mysql> explain select * from test_table where a = 1 and b = 2 and c = 3;
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | test_table | NULL | ref | test_table_a_b_c_index | test_table_a_b_c_index | 15 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+------------+------------+------+------------------------+------------------------+---------+-------------------+------+----------+-------------+
可以看到,当字段允许为空时,这时的key_len
变成了15 = 4 3 + 1 3(INT 类型为空时,额外占用一个字节)。
索引优化
有了这些基础知识之后,再来根据实际的SQL 判断索性性能好坏。
还是以上面那张数据表为例,为 a、b、c 三个字段创建联合索引。
SQL 语句 | 是否索引 |
---|---|
explain select * from test_table where a = 1 and b = 2 and c = 3; | Extra:Using index key_len: 15 |
explain select * from test_table where a = 1 and b = 2 and c = 3 order by c; | Extra:Using index key_len: 15 |
explain select * from test_table where b = 2 and c = 3; | Extra:Using where; Using index key_len: 15 |
explain select * from test_table where a = 1 order by c; | Extra:Using where; Using index; Using filesort key_len: 5 |
explain select * from test_table order by a, b, c; | Extra:Using index key_len: 15 |
explain select * from test_table order by a, b, c desc; | Extra:Using index; Using filesort key_len:15 |
explain select * from test_table where a in (1,2) and b in (1,2,3) and c = 1; | Extra:Using where; Using index key_len: 15 |
通常在查看执行计划时, Extra 列为 Using index 则表示优化器使用了覆盖索引。
SQL1 可以使用覆盖索引,性能好
SQL2 可以使用覆盖索引,同时避免排序,性能好
SQL3 可以使用覆盖索引,但是需要根据 where 字句进行过滤
SQL4 可以使用部分索引 a,但无法避免排序,性能差
SQL5 可以完全使用覆盖索引,同时可以避免排序,性能好
SQL6 可以使用覆盖索引,但无法避免排序,(这是因为 MySQL InnoDB 创建索引时默认asc升序,索引无法自动倒序排序)
SQL7 可以使用覆盖索引,但是需要根据 where 子句进行过滤(非定值查询)
创建索引规范
考虑到索引维护的成本,单张表的索引数量不超过 5 个,单个索引中的字段数不超过 5 个
不在低基数列上建⽴索引,例如“性别”。 在低基数列上创建的索引查询相比全表扫描不一定有性能优势,特别是当存在回表成本时。
合理创建联合索引,(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
合理使用覆盖索引减少IO,避免排序。
来源:https://segmentfault.com/a/1190000039355574
猜你喜欢
- 目录1 摘要2 概述2.1 什么是并行计算?2.2 为什么要并行计算?2.3 谁都在使用并行计算?科学界和工程界:工业界和商业界:全球应用:
- 本文讲解如何设置SQL Server数据库全文索引服务。在Microsoft SQL Server 7.0 中提供了全文索引服务(Full-
- Part 1: 简介在PyTorch中,torch.cat()是一个被广泛使用的函数。它可以让我们在某个维度上把多个张量组合在一起。对于那些
- 工欲善其事,必先利其器,开发工具这个东西觉得折腾下还是有好处的。但常常感觉专门抽出时间搞这个浪费时间,更常见的现象是已经明显感觉到当前的开发
- 本文帮你六步改善SQL Server安全规划全攻略。一、什么是SQL注入式攻击所谓SQL注入式攻击,就是攻击者把SQL命令插入到Web表单的
- 学习内容1.软件安装及服务器设置。2.(选做,但是强烈建议) 使用图形界面软件 Navicat for SQL3.数据库基础知识数据库定义关
- 大部分语言,例如c语言,交换两个变量的值需要使用中间变量。例如交换a,b伪代码:tmp = aa = bb = tmppython里面可以实
- 我就废话不多说了,大家还是直接看代码吧~import pandas as pdimport numpy as npcolumns = [[&
- 首先,将FileToZip.class文件放到ThinkPHP/Extend/Library/ORG/Util/文件夹中,FileToZip
- 今日大致浏览了一下《High Performance Web Sites》。本书的中文版是《高性能网站建设指南》。本书另有对其中个别问题深入
- PDO::setAttributePDO::setAttribute — 设置属性(PHP 5 >= 5.1.0, PECL pdo
- 数据表中有一列数据,如图所示:现在需要将该列数据分成三列。SQL 代码如下所示:第一种select max(case when F1%3=1
- 先让我们看一个例子,了解什么是模式化窗口。以下是QQ秀商城在非登录时提示登录的一种状态。当我在非登录状态,通过保存形象的方式买一件衣服时,弹
- 目录项目地址安装导入使用1 创建连接2 执行sql语句3 select 方法4 insert_into 方法5 merge_in
- 由于ACCESS本身没有提供在窗体中添加一个命令按钮实现打开通用对话框的控件,所以大家必须通过编写相关的宏才能实现此功能,但是编写出的宏限制
- 楔子Python 有一个第三方模块叫 psutil,专门用来获取操作系统以及硬件相关的信息,比如:CPU、磁盘、网络、内存等等。下面来看一下
- 本文实例为大家分享了PHP变量传值赋值和引用赋值变量销毁的具体代码,供大家参考,具体内容如下<?php $a = 100
- 引言列表、字典:可变序列,可以执行增删改排序等字典:无序的一、字典的创建#使用{}创建scores = {'张三':100
- 环境变量配置首先需要将anaconda的路径配置进环境变量中,我是用户变量和系统变量都配置了。我的anaconda安装在D:\Anacond
- 我们知道,任何数据库系统都无法避免崩溃的状况,即使你使用了Clustered,双机热备……仍然无