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.join函数的语法及用法(1)语法:'sep'.join(sep_object)参数说明sep:分割符,可为&l
- 前言相关一些检测工具挺多的,比如powertop、powerstat、s-tui等。但如何通过代码的方式来实时检测,是个麻烦的问题。通过许久
- 二维列表转一维列表from compiler.ast import flattena=[[1,2],[5,6]]print(flatten(
- 1.open使用open打开文件后一定要记得调用文件对象的close()方法。比如可以用try/finally语句来确保最后能关闭文件。fi
- 写给新手的话pycharm是什么,为什么让我指定interpreter记事本最开始写C语言代码的时候,人们使用vi,记事本等软件写代码,写完
- 1 简介本篇主要介绍使用pytorch实现基于CharRNN来进行文本分类与内容生成所需要的相关知识,并最终给出完整的实现代码。2 相关AP
- 获得某层tensor的输出维度代码如下所示:from keras import backend as K@wraps(Conv2D)def
- 九宫格是一种比较古老的设计,它最基本的表现其实就像是一个三行三列的表格。其实它最初是在window的c/s结构中用得比较多,比如我们经常看到
- 原来的题目设想为界面视觉效果的统一性,但是“统一”这个词似乎有点敏感,怕触动萌点无数,而我也无意去设定一个什么什么的统一性来侃侃而谈,极为专
- tensorflow利用anaconda在ubuntu下安装方法及jupyter notebook运行目录及远程访问配置Ubuntu下安装A
- 代码如下from bs4 import BeautifulSoup #网页解析,获取数据import sys #正则表达式,进行文字匹配im
- 首先来看看这个php字符串替换函数 strtr()的两种用法:strtr(string,from,to) 或者strtr(string,ar
- 枚举是常用的功能,看看Python的枚举.from enum import EnumMonth = Enum('Month'
- 图像的阈值处理一般使得图像的像素值更单一、图像更简单。阈值可以分为全局性质的阈值,也可以分为局部性质的阈值,可以是单阈值的也可以是多阈值的。
- 一、Tensorlow结构import tensorflow as tfimport numpy as np#创建数据x_data = np
- 插入代码块使用sum函数:numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]print(sum(number
- 在使用SQL*LOADER装载数据时,由于平面文件的多样化和数据格式问题总会遇到形形色色的一些小问题,下面是工作中累积、整理记录的遇到的一些
- 1.索引问题索引是数据库优化中最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数 的SQL性能问题。本章节将对MySQL中的索引
- 本文实例讲述了Python使用re模块正则提取字符串中括号内的内容操作。分享给大家供大家参考,具体如下:直接上代码吧:# -*- codin
- 记录了CentOS7 安装python3.7.0的详细过程,供大家参考,具体内容如下1.下载及解压python3.7的安装包可从官网下载上传