- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章MySQL为数据表建立索引的原则详解由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
面试题:
索引是帮助我们实现快速查询数据库的数据结构。 在很多时候,表中存放的记录非常的多,需要用到好多的数据页来存放这些记录,在很多页中查找记录可以分为2个步骤:
1、定位到记录所在的页 。
2、从所在页内查找相应的记录 。
在没有索引的情况下,无论是根据主键列还是其他列的值进行查找,由于我们不能快速的定位到记录所在的页,所以只能从第一页沿着双向链表一直往下找,因为需要遍历所有的数据页,这种方式超级消耗性能。因此人们期望一种能高效完成搜索的方法,因此就出现了索引,索引就是一种数据结构.
索引可以让服务器快速的定位到表的指定位置 (索引大大减少了服务器需要需要扫描的数据量)。最常见的B-Tree索引,按照顺序存储数据,所以mysql可以来做order by和group by操作(索引可以帮助服务器避免排序和临时表)。因为数据是有序的,所以B-Tree也就会将相关的列值都存储在一起(索引可以将随机IO变为顺序IO)。因为索引中存储了实际的列值,所以某些查询值使用索引就能完成全部查询。因此,总结下来的索引的优点为:
1、索引大大减少了服务器需要需要扫描的数据量; 。
2、索引可以帮助服务器避免排序和临时表; 。
3、索引可以将随机IO变为顺序IO,
1、空间上的代价:
建立索引需要占用物理空间 。
InnoDB存储引擎默认使用的B+树索引,每建立一个索引,都要为它建立一颗B+树,每一颗B+树的每一个节点都是一个数据页,一个数据页默认会占用16KB的存储空间,而一颗很大的B+树由许多数据页组成,这将占用很大的一片存储空间.
2、时间上的代价:
因为每次对表记录进行增删改,需要进行动态维护索引,导致增删改时间变长 。
每当对表中的数据进行增删改查操作时,都需要修改各个B+树索引,B+树中的每层节点都按照索引列的值从小到大的顺序组成了双向链表,无论是叶子节点中的记录还是非叶子节点中的记录都按照索引列的值从小到大的顺序排成了单向链表。而增删改查操作可能会对节点和记录的排序造成破坏,所以存储引擎需要额外的时间进行页面分裂、页面回收等操作,以维护节点和记录的顺序.
在执行查询语句前,首先要生成一个执行计划,一般情况下,一条查询语句在执行过程中最多使用一个二级索引,因此,在生成执行计划时需要计算使用不同索引执行查询时所需的成本,最后选取成本最小的那个索引执行查询,如果建立了太多的索引,可能会导致成本分析耗时太多,从而影响查询语句的执行性能.
在创建和使用索引是应该注意下列事项:
建立一张数据库表:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
create
table
single_table(
id
int
not
auto_increment,
key1
varchar
(100),
key2
int
,
key3
varchar
(100),
key_part1
varchar
(100),
key_part2
varchar
(100),
key_part3
varchar
(100),
common_field
varchar
(100),
primary
key
(id), # 聚簇索引
key
idx_key1(key1), # 二级索引
unique
key
uk_key2(key2), # 二级索引,而且该索引是唯一二级索引
key
idx_key3(key3), # 二级索引
key
idx_key_part(key_part1,key_part2,key_part3) # 二级索引,也是联合索引
)Engine=InnoDB CHARSET=utf8;
|
我们只为出现在where子句中的列、order by或group by子句中的列、连接子句中的连接列创建索引。仅出现在查询列表中的列就没有必要建立索引了.
比如下面的查询语句:
1
|
select
common_field,key_part3
from
single_table
where
key1=
'a'
;
|
我们只需要为出现在where子句中的key1建立索引就可以了,而查询列表中的common_field、key_part3这两个列就没有必要建立索引了.
在定义表结构时,要显式的指定列的类型。以整数类型为例,有tingint、mediumint、int、bigint这几种,他们占用的存储空间的大小依次递增,他们能表示的整数范围当然也是依次递增。如果想要对某个整数类型的列建立索引,在表示的整数范围允许的情况下,尽量让索引列使用较小的类型,比如使用int就不要用bigint,因为数据类型越小,索引占用的存储空间就越少,在一个数据页内就可以存放更多的记录,磁盘IO带来的性能损耗也就越小,读写效率也就越高.
这个建议对表的主键来说更加适用,因为不仅聚簇索引会存储主键值,所有的二级索引的节点都会存储一份记录的主键值,如果主键使用更小的数据类型,也就意味着能节省更多的存储空间.
一个字符串其实是由若干个字符组成的,假如字符串很长,那么存储这个字符串就会占用很多的存储空间。在为这个字符串所在的列建立索引时,就需要在对应的B+树中,把列的完整字符串存储起来,字符串越长,在索引中占用的存储空间越大.
索引列的字符串前缀其实也是排好序的,所以索引的设计人员设计了一个方案,即只将字符串的前几个字符存放到索引中,也就是说二级索引的记录中只保留字符串的前几个字符.
比如可以这样修改idx_key1索引,让索引中只保留字符串的前10个字符:
1
2
|
alert
table
single_table
drop
index
idx_key1;
alert
table
single_table
add
index
idx_key1(key1(10));
|
然后再执行下面的查询语句:
1
|
select
*
from
single_table
where
key1=
'abcdefghijklmn'
;
|
由于在idx_key1的二级索引记录中只保留字符串的前10个字符,所以我们只能定位到前缀为‘abcdefghij'的二级索引记录,在扫描这些二级索引记录时再判断它们是否满足key1='abcdefghijklmn'条件,当列中存储的字符串包含的字符较多时,这种为列前缀建立索引的方式可以明显减少索引大小.
不过,在只对前缀建立索引的情况下,下面这个查询语句就不能使用索引来完成排序需求了:
1
|
select
*
from
single_table
order
by
key1 limit 10;
|
因为二级索引idx_key1中不包含完整的key1列信息,所以在仅使用idx_key1索引执行查询时,无法对key1列前10个字符相同但其余字符不同的记录进行排序,也就是说,只为列前缀建立索引的方式无法支持使用索引进行排序的需求.
为了彻底告别回表操作带来的性能损耗,建议最好在查询列表中只包含索引列,比如这个查询语句:
1
|
select
key1,id
from
single_table
where
key1>
'a'
and
key1<
'c'
;
|
由于我们只查询key1列和id列的值,所以在使用idx_key1索引来扫描('a','c')区间中的二级索引时,可以直接从获取到的二级索引记录中读出key1列和id列的值,而不需要再通过id值到聚簇索引中执行回表操作了,这样就省去了回表操作带来的性能损耗.
我们把这种索引中已经包含了所有需要读取的列的查询方式称为覆盖索引。如果索引的叶子节点中已经包含要查询的数据,那么还有必要再回表查询呢?如果一个索引包含所有需要查询的字段的值,就称为覆盖索引.
排序操作也优先使用覆盖索引进行查询,比如下面这个查询语句:
1
|
select
key1
from
single_table
order
by
key1;
|
虽然这个查询语句中没有limit子句,但是由于可以采用覆盖索引,所以查询优化器会直接使用idx_key1索引进行排序,而不需要执行回表操作.
当然,如果业务需要查询索引列以外的列,还是要以保证业务需求为重,如无必要,最好把业务中需要的列放在查询列表中,而不是以简单的*替代.
在下面这两个查询语句中,搜索条件中的语义是一样的:
1
2
|
select
*
from
single_table
where
key2*2<4;
select
*
from
single_table
where
key2<4/2;
|
在第一个查询语句的搜索条件中,key2列并不是以单独列名的形式出现的,而是以key2*2这样的形式表现的,MySQL并不会尝试简化key2*2<4表达式,而是直接认为这个搜索条件不能形成合适的扫描区间来减少需要扫描的记录数量,所以该查询语句只能以全表扫描的方式来执行.
在第二个查询语句的搜索条件中,key2列并是以单独列名的形式出现的,MySQL可以分析出key2<2,这样可以减少需要扫描的记录数量。所以MySQL可能使用uk_key2来执行查询.
所以,如果想让某个查询使用索引来执行,请让索引列以列名的形式单独出现在搜索条件中.
我们知道,对于一个使用InnoDB存储引擎的表来说,在没有显式创建索引时,表中的数据实际上存储在聚簇索引的叶子节点中,而且B+树的每一层数据页以及页面中的记录都是按照主键值从小到大的顺序排序的,如果新插入记录的主键值是依次递增的话,每插满一个数据页就换到下一个数据页继续插入,如果新插入记录的主键值忽大忽小,就比较麻烦了.
假设某个数据页存储的聚簇索引记录已经满了,它存储的主键值在1~100之间,此时如果再插入一提哦啊主键值为9的记录,因为这个数据页已经满了,新纪录应该插入到哪里呢?
我们需要把当前页面分裂成两个页面,把本页中的一些记录移动到新创建的一些页中,页分裂意味着性能损耗,所以如果想尽量避免这种无谓的性能损耗,最好让插入记录的主键值依次递增。即让主键拥有auto_increment属性,MySQl会自动为新插入的记录生成递增的主键值.
针对single_table表,可以单独针对key_part1列建立一个idx_key_part1索引:
1
|
alert
table
single_table
and
index
idx_key_part1(key_part1);
|
而此时我们已经有了一个针对key_part1、key_part2、key_part3列建立的联合索引idx_key_par。idx_key_part索引的二级索引记录本身就是按照key_part1列的值排序的,此时再单独为key_part1列建立一个索引其实是没必要的,我们可以把整个新建的idx_key_part1索引看作一个冗余索引,该冗余索引是没有必要的.
有时,我们可能会对同一个列建立多个索引,比如下面两个添加索引的语句:
1
2
|
alert
table
single_table
add
unique
key
uk_id(id);
alert
table
single_table
add
index
idx_id(id);
|
我们针对id列又建立了一个唯一二级索引uk_id,还建立了一个普通二级索引idx_id,可是id列本身就是single_table表的主键,InnoDB自动为该列建立了聚簇索引,此时uk_id和idx_id就是重复的,这种重复索引应该避免.
本篇文章就到这里了,希望能够给你带来帮助,也希望您能够多多关注我的更多内容! 。
原文链接:https://hengheng.blog.csdn.net/article/details/123164586 。
最后此篇关于MySQL为数据表建立索引的原则详解的文章就讲到这里了,如果你想了解更多关于MySQL为数据表建立索引的原则详解的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
我正在尝试使用 Excel 中的间接函数来构建公式以在另一张纸上返回值。 在工作表 A 单元格 D3 的值为 B 我想使用值 B 从名为 App Summary 的工作表中的单元格 B6 返回一个值。
我目前正在使用 LumiSoft 的 SIP 堆栈,并且能够在我的 FreePBX 盒子上成功注册分机并调用另一个软电话。我现在需要做的就是通过调用流式传输 WAV 文件(或 RAW,或任何可行的文件
这个问题已经有答案了: How can I fix 'android.os.NetworkOnMainThreadException'? (65 个回答) 已关闭 8 年前。 我有一个安卓 Activ
我正在使用 ws npm 在服务器端,websocket 在客户端。 从 node-js 运行此代码时它工作正常,但从浏览器运行它会出现以下错误: failed: Error in connectio
当我将鼠标悬停在想要淡入和淡出的内容上多次时,它就会不断重复。即使我停止悬停它。我怎样才能阻止这个? $(".featured").hover(function(){ $(this).find
我需要建立一个 mysql 连接并取回一些数据。我可以使用此代码在 Java 中执行此操作 try{ String username;
不能制造愚蠢。具有下一个文件夹结构: /flint/double-conversion/src /燧石/愚蠢/愚蠢/ 其中/flint/folly 包含自述文件和许可证。作为in the readme
我想在编译主单元之前在程序集中嵌入本地引用。但书面目标不起作用。 WithMetadataValue( 'CopyLocal', 'true' )->Met
我不是软件专家,但我确实需要一些建议。 我正在编写一个 C 程序(在下面剪切/粘贴)以通过 LAN(以太网)建立从我的 Mac Pro 到位于它旁边的基于 Windows XP 的测试仪器的 TCP
我正在构建一个应用程序,我的手机经常将数据发送到我的服务器。由于我将使用我的移动数据,我想知道建立(和拆除?)到我的服务器的 TCP 连接需要多少数据。 最佳答案 TCP 三向握手 Device 1
我有一个带有登录表单的网站。当加载登录表单页面时,我创建一个新的 PDO 对象以查看连接是否正常工作。如果成功打开连接,查看者将看到一个登录表单。如果不成功,他们会收到一条消息,说明服务器已关闭。 然
构建我的Electron应用程序后,它将显示产品名称undefined。如何设置其他名称呢? 当前是这样的: 最佳答案 请尝试此操作。引用此链接 https://www.electronjs.org/
我有一个项目在哪里使用这个 jar 。 据我所知...发生 war 之后,文件夹WEB-INF/lib必须具有: mail-1.4.1.jar activation-1.1.jar mysql-con
代码: %{ #include #include #include #include "gener.h" #include "sym_tab.h" #include "scope.h" #inc
我需要将侧边栏小部件集成到我的高流量页面(称为SiteA)中。该小部件应包含我的其他页面之一(称为 SiteB)的最新文章。 在我看来,我有两种可能的解决方案。 SiteA 上的 cUrl 调用从 S
我正在尝试建立 Cortana 技能,以便能够使用 Surface 相机拍照。怎么做?目前我的技能是能够使用bot框架和使用nodejs来回答问题。代码看起来像 bot.dialog('ScanCar
这个问题在这里已经有了答案: Resolving javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorExce
当我与网络服务器建立 https 连接时出现 SSLProtocolException。我只在 Android 2.3 Gingebread 中有这个异常(exception);相同的代码在所有其他
我想做的是指定几个端口,然后检查它们是否已建立连接。我找到了以下脚本并运行了,但它只列出了 3 个端口,我不明白为什么。我验证了相关端口的事件规则(以及下面输出中未列出的许多其他端口)。 Set ob
使用 MySQL 我试图使用已经上传到数据库中的数据建立一对多关系。举个例子,假设我在一个表中有一个名字列表,我想将它们连接到一个他们去过的地方的列表。显然 1 个人可以去很多不同的地方,但我在设置时
我是一名优秀的程序员,十分优秀!