mysql索引原理和优化(Mysql 索引该如何设计与优化)
mysql索引原理和优化
Mysql 索引该如何设计与优化目录
- 什么是索引?
- 最左前缀匹配原则
- 如何计算 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,避免排序。
以上就是Mysql 索引该如何设计与优化的详细内容,更多关于MySQL 索引设计与优化的资料请关注开心学习网其它相关文章!
原文链接:https://segmentfault.com/a/1190000039355574
- mysql对大表千万级如何优化(MySQL 大表的count优化实现)
- 图片如何存放在mysql中(将图片保存到mysql数据库并展示在前端页面的实现代码)
- mysql创建表的基本步骤(mysql中操作表常用的sql总结)
- centos7.5安装mysql教程(CentOS8下MySQL 8.0安装部署的方法)
- 安装mysql中要注意什么问题(安装Mysql时可能会遇到的一些疑难杂症)
- mysql单独导出表(mysql实现从导出数据的sql文件中只导入指定的一个表)
- idea安装mybatis(IDEA使用mybatis-generator及配上mysql8.0.3版本遇到的bug)
- centos7离线安装mysql5.7(CentOS7.5 安装MySql的教程)
- mysql记录binlog的方式(MySQL使用binlog日志做数据恢复的实现)
- myeclipse连接mysql数据库的方法(教你用eclipse连接mysql数据库)
- mysql8.0.15安装详细教程(Mysql8.0.22解压版安装教程小白专用)
- mysql有哪些索引和聚簇索引(MySQL 独立索引和联合索引的选择)
- mysql索引的弊端(MySQL 全文索引的原理与缺陷)
- python mysql配置(详解python校验SQL脚本命名规则)
- mysql数据库延时监控(Mysql sql慢查询监控脚本代码实例)
- mysql的uuid说明(MySQL GTID全面总结)
- 焕然一新 成都轨道集团官方网站改版上线(成都轨道集团官方网站改版上线)
- 成都轨道交通19号线二期全线电通(成都轨道交通19号线二期全线电通)
- 19号线二期全线电通 轨道交通项目最新进展来了(19号线二期全线电通)
- 涉及3条地铁线路 成都这4座轨道交通站点有新名字了(涉及3条地铁线路)
- 来了 成都轨道交通5条线路刷新 进度条(成都轨道交通5条线路刷新)
- 一部手机两套系统 OPPO Find X3的正确打开方式你知道吗(一部手机两套系统)
热门推荐
- css水平或者垂直居中的写法(深入理解CSS行高line-height与文本垂直居中的原理)
- css圆角阴影代码(CSS实现曲面阴影效果的简单实例推荐)
- css图片水平旋转动画(css实现图片横向排列滚动效果)
- ASP.NET使用JavaScriptSerializer实现序列化与反序列化
- docker中国加速镜像怎么设置(Docker 安装及配置镜像加速的实现)
- nginx与tomcat长连接(Windows下使用Nginx+Tomcat做负载均衡的完整步骤)
- springboot与docker(详解SpringBoot项目docker环境运行时无限重启问题)
- 存储过程异常处理
- Html5中的<section>标签
- 怎么实现显示用户浏览过的历史记录
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9