- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章高效利用mysql索引指南由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
前言 。
mysql 相信大部分人都用过,索引肯定也是用过的,但是你知道如何创建恰当的索引吗?在数据量小的时候,不合适的索引对性能并不会有太大的影响,但是当数据逐渐增大时,性能便会急剧的下降.
本篇是对 mysql 索引的一个归纳总结,如果有错误的地方,记得评论指出哦.
索引基础 。
我们都有都知道查字典的步骤,是先在索引页中找到这个字的页码,然后再到对应的页码中查看这个字的信息。mysql 的索引方法也是和这个类似的,先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。假如有下面的 sql 语句:
1
|
select
*
from
student
where
code=
'2333'
|
加入 code 列上建立有索引,mysql 将使用该索引找到值为'2333'的数据行,然后读取数据行的所有数据返回.
索引类型 。
B-Tree 索引 。
(不是 B 减树,就是 B 树),绝大多数的索引类型都是 B-Tree 的(或者是 B-Tree 的变体),通常我们使用的也是这类索引。Mysql 中 MyISAM 存储引擎使用的是 B-tree,InnoDB 使用的是 B+Tree,B 树和 B+树的区别自行百度.
树结构的索引能够加快访问数据的速度,存储引擎不再需要全表扫描来获取所需的数据,取而代之的是从树的根节点来进行二分搜索,总所周知二分搜索的速度是相当快的,因此我们能够利用索引来极大的提高查询速度。B-Tree 支持以下几种类型的查询:
假设再 student 表中仅有:name,age,weight 这样一个多列索引,下面的查询都能利用到此索引 。
和索引列中的所有列进行匹配。比如查询name='abc' and age=12,这里用到了第一列和第二列 。
只是用索引的开头部分,比如查询name='ggg'只使用索引的第一列,查询name='ggg' and age=12是用索引的第1、二列.
也可以只匹配某一列的开头部分,比如查询name lik 'g%',查询 name 以 g 开头的记录。这里用到了第一列 。
可用于匹配范围值,比如查询name > 'abc' and name < 'bcd' 。
用于匹配多列,比如查询name='abc' and age > 12.
总的来看,可以发现 B-Tree 索引适用于根据最左前缀的查找,也就是查询字段字段顺序要和索引字段顺序一样,且以第一个索引字段开头。比如查询name,name and age,name and age and weight都能使用索引,但是查询age,age and name不能使用索引.
哈希索引 。
hash 索引基于 hash 表实现,只有精确匹配索引所有列才会生效。MySQL 中只有 Memory 引擎显示支持哈希索引,同时也是其默认索引.
InnoDB 无法创建 hash 索引,但是它有一个功能叫自适应hash索引,当某些索引值使用非常频繁时,引擎会在内存中基于 B-Tree 索引之上再创建一个 hash 索引,这样就让 B-Tree 索引也有了一点 hash 索引的优点。这个功能是一个完全自动的、内部的行为,也就是无法手动控制或配置.
高性能索引策略 。
下面是一些常见的索引策略.
独立的列 。
这个很简单,如果查询中的列不是独立,便无法使用索引,比如:
1
|
select
*
from
student
where
age+1=12
|
即使 age 列有索引,上面的查询语句也是无法利用索引的.
前缀索引和索引选择性 。
如果需要索引很长的字符串列,直接创建索引,会让索引占用更多的空间且速度较慢。一个优化策略是模拟 hash 索引:给列计算一个 hash 值,并在 hash 值列建立索引.
另外一个办法就是建立前缀索引。只索引这个字段开始的部分字符,这样可以极大的解决空间占用,索引建立速度也会快很多。但是这样也有如下弊端:
这里的关键是确定索引多少个字符合适。既要避免长度过大,还要有足够的索引选择性。有以下两种办法来帮助确定索引字符数:
索引字段前缀数据分布均匀。也就是以索引字符开头的字符串数目分布均匀,比如索引 name 字段的前 3 个字符,下面的结果是比较合理(只取排名前 8 的):
。
数目 | 索引前三个字符 |
---|---|
500 | abc |
465 | asd |
455 | acd |
431 | zaf |
430 | aaa |
420 | vvv |
411 | asv |
512 |
。
如果每一列的数据都比较大,说明区分度还不高需要增大索引字符数,直到这个前缀的选择性接近完整列的索引性,也就是前面的数据要尽可能的小.
计算完整列的选择性,并使前缀的选择性接近于完整列的选择性。下面语句用户计算完整列选择性:
1
2
|
-- 不同字符串的数目/总的数目就是完整列选择性
select
count
(
distinct
name
)/
count
(*)
from
person;
|
下面语句计算索引前 3 个字段选择性:
1
2
|
-- 前3个字符不同的字符串数据/总的数据
select
count
(distincy
left
(city,3))/
count
(*)
from
person
|
不断增大索引字符数目,直到选择性接近完整列选择性且继续增大数据选择性提升幅度不大的时候.
创建方法 。
1
2
|
-- 假设最佳长度为4
alter
table
person
add
key
(
name
(4));
|
多列索引 。
不少人有这样的误解,如果一个查询用有多个字段 ‘and'查询,那么给每个字段都建立索引不就能最大化提高效率了?事实并不是如此,mysql 只会选择其中一个字段来进行索引查找。这种情况下应该建立多列索引(又叫联合索引),就能利用多个索引字段了,注意索引列顺序要和查询的顺序一致.
在 5.0 及以上版本中引入了“索引合并”的策略。一定程度上也可以使用多个单列索引,比如下面的查询:
1
2
3
|
-- mysql会分别使用name和age索引查出数据然后合并
-- 如果使and则查出数据后再对比取交集
select * from person where name = "bob" or age=12
|
但是不推荐这么做,and 或 or条件过多会耗费大量的 CPU 和内存在算法的缓存、排序和合并操作上.
选择合适的索引列顺序 。
在一个多列 B-Tree 索引中,索引列的顺序意味着索引首先是按照最左列进行排序,然后是第二列…索引一个良好的多列索引应该是将选择性最高的索引放在最前面,然后依次降低,这样才能更好的利于索引。选择性计算方发见:前缀索引 小节.
聚族索引 。
聚族索引不是一种单独的索引类型,而是一种数据存储方法,具体的细节依赖其实现方式.
InnoDB 的聚族索引实际是在同一个结构中保存索引值和数据行。因为不能同时将数据行放在两个不同的地方,所以一个表只能有一个聚族索引。InnoDB 的聚族索引列为“主键列”.
如果没有定义主键,InnoDB 会选择一个唯一的非空索引代替。如果这样的索引也没有,InnoDB 会隐式定义一个主键来作为聚族索引.
聚族索引的主要优点是:可以把相关数据保存在一起,减少磁盘 IO,提高查询效率。但是也有缺点:
覆盖索引 。
简单来说就是一个索引覆盖了需要查询的列字段,这样就不需要再到聚族索引中利用主键进行二次查找,在一个二级索引中就能取到所需的数据.
InnoDB 的索引会在叶子节点中保存索引值,因此如果要查询的字段全部包含在某个索引中,且这个索引被使用了,那么就能极大的提高查询速度。比如如下查询语句:
1
2
3
4
|
-- name有索引的情况下,直接从索引的叶子节点中取name值返回,无需二次查找
select
name
from
person
where
name
=
'abc'
-- 如果存在`name,age`聚合索引,也会直接返回数据,无需二次查找
select
name
,age
from
person
where
name
=
'abc'
and
age=12
|
使用索引进行排序 。
mysql 的排序操作也是可以利用索引的,只有当索引的列顺序和ORDER BY的顺序完全一致,并且所有列的排序方法(正序或者倒序)也一样时,才能够使用索引来进行排序。注意:排序的字段可以比对应的索引字段少,但是顺序必须一致。如下
1
2
3
4
5
6
7
|
-- 假设有:(name,age,sex)联合索引
-- 可使用索引排序
select
...
order
by
name
desc
,age
desc
select
...
order
by
name
desc
,age
desc
,sex
desc
-- 不可使用排序
select
...
order
by
name
desc
,sex
desc
select
...
order
by
name
desc
,age
asc
|
结束 。
本篇基于 mysql 5.5 的版本,更新的版本可能会有不一样的策略.
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我.
原文链接:https://www.tapme.top/blog/detail/20190422/ 。
最后此篇关于高效利用mysql索引指南的文章就讲到这里了,如果你想了解更多关于高效利用mysql索引指南的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
在后台开启了“URL Rewrite”,看起来一切正常,可是点击某一栏目的时候却怎么都进不去,显示的依然是论坛的首页。看了一下论坛目录下,原来这下面并没有自己的.htaccess文件,所以默认使用的
本文实例为大家分享了.net发送邮件的实现代码,供大家参考,具体内容如下 关键代码: 需要引用命名空间: using System.Net.Mail; using System.Net;
今天的一个小测试是老师让用.NET用控件来制作一个拉菜单要求如下: 将鼠标移到父菜单上弹出3个子菜单,而且每个子菜单都有超链接。 以下是我自己做的代码: 复制代
我有以下内容 static const unsigned int chromosome = 6; double bestFitness[chromosomes]; for(int i = 0; i
关于附图,我需要一个计算算法来将 A 轴向下移动 n 英寸,将 B 轴从左向右移动 m 英寸,以便组件圆 D 遵循抛物线的曲线;圆 D 并不总是 10 英寸,可以更小。我不是数学专业的,所以这对我来说
我正在尝试利用我的格式字符串错误,它存在于这个程序中: #include #include #include #include #include void foo(char* tmp, ch
用Matplotlib和Seaborn这类Python库可以画出很好看的图,但是这些图只是静态的,难以动态且美观地呈现数值变化。要是在你下次的演示、视频、社交媒体Po文里能用短视频呈现数据变化,是不
1、进程介绍 进程:正在执行的程序,由程序、数据和进程控制块组成,是正在执行的程序,程序的一次执行过程,是资源调度的基本单位。 程序:没有执行的代码,是一个静态的。 2、线程
1、前言 在开发过程中,有时会遇到需要控制任务并发执行数量的需求。 例如一个爬虫程序,可以通过限制其并发任务数量来降低请求频率,从而避免由于请求过于频繁被封禁问题的发生。 接下来
Opera 管理着一个漏洞赏金计划,研究人员可以在该计划中报告 Opera 软件中的漏洞并获得奖励。 这篇文章就是我发现的一个漏洞——网页可能会从用户那里检索本地文件的屏幕截图。 考虑到 O
C++ 文件查找 在C++中我们要如何查找文件呢?我们需要一个结构体和几个大家可能不太熟悉的函数。这些函数和结构体在的头文件中,结构体为struct _finddata_t ,函数为_findfi
1、前言 本文利用 fsockopen() 函数,编写一个功能简单的端口扫描器。 2、关键技术 本实例的端口号是固定的,通过对数组的遍历,利用 fsockopen() 函数连接,如果连接成功,
最近在将一些项目的rest api迁移到.net core中,最开始是用的Nginx做反向代理,将已经完成切换的部分切入系统,如下图所示: 由于迁移过程中也在进行代码重构,需要经常比较频繁的测
前言 最近学习了python,感觉挺多地方能用到它的。打包 测试 上传 爬电影....而且代码量是真少。人生苦短,我用python。而今天写的这个是因为下载电影时总会发现除了视频还会有这两个文件,
1、Monkey测试简介 Monkey测试是Android平台自动化测试的一种手段,通过Monkey程序模拟用户触摸屏幕、滑动Trackball、按键等操作来对设备上的程序进行压力测试,检测程序
一直想写一套生成静态页面的文章系统 但面对生成静态后的一些复杂数据库交互问题。又望而却步! 于是就想 有没有 在不耽误数据交互的情况下,而又能降低服务器负
Qt 利用大量第 3 方库进行图像编码、压缩、加密、音频和视频编解码器支持等。 从历史上看,当我想使用它们时,我总是必须将它们作为附加依赖项包含在内。我一直想知道是否有一种方法可以简单地重用 Qt 已
我想知道是否可以使用属性将功能“混合”到类/方法/属性中。 就像是: [TrackChanges] public Foo { get; set; } 如果可能的话,有谁会如何实现? 最佳答
有些站点位于共享主机(Windows 2003 Server)上,因此我无法访问服务器配置。 我到处都读到关于杠杆浏览器缓存的信息,特别是静态文件(jpg,css,js等)的信息,但是...在我的情况
我想在我的项目中使用 Julia 的主要原因之一是它的速度,尤其是在计算积分方面。 我想在某个区间 [a,b] 上积分一维函数 f(x)。一般来说,Julia 的 quadgk 函数将是一个快速而准确
我是一名优秀的程序员,十分优秀!