小工具      在线工具  汉语词典  dos游戏  css  js  c++  java

MySQL优化系列-索引的使用、原理和设计优化

Java 额外说明

收录于:52天前

之前的文章一直在避免创建索引来优化数据库。不是不想讲,而是太重要了,必须要提。今天我们来研究数据库索引的设计和优化(以MySQL为例)。

文章结构:
(1)索引的概述和使用;
(2)索引的基本原理;
(3)索引分类;
(4)索引设计优化

本系列:demo下载
(一)MySQL优化笔记(一)–库与表基本操作以及数据增删改
(二)MySQL优化笔记(二)–查找优化(1)(非索引设计)
(三)MySQL优化笔记(二)–查找优化(2)(外连接、多表联合查询以及查询注意点)
(四) MySQL优化笔记(三)–索引的使用、原理和设计优化
(五) MySQL优化笔记(四)–表的设计与优化(单表、多表)
(六)MySQL优化笔记(五)–数据库存储引擎
文章目录:
(1)索引的概述和使用
概述
- 什么是索引
- 索引的优点
- 索引的缺点
- 为什么需要索引
索引的使用(语法)
- 创建索引:(三种方式)
- 删除索引
- 查看索引
####(2)索引的基本原理
整体性原理例子
针对存储性质讲解
索引的数据结构:B+tree
B+tree性质
B+tree有两种搜索方法
####(3)索引分类
普通索引
唯一索引
主键索引
全文索引:(FULLTEXT)
单列索引与多列索引(其实是相当于一个用法技巧)(重点)
- 单列索引
- 多列索引
- 聚集索引和非聚集索引
- 聚集索引
- 非聚集索引
- 关于聚集索引以及非聚集索引的几个问题
####(4)索引设计优化:
索引建立的几大原则
索引使用的注意点(大概有14点)
一、索引的概述和使用:
(1)概述:
1)什么是索引?
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。
更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。在没有索引的情况下,数据库会遍历全部数据后选择符合条件的;而有了相应的索引之后,数据库会直接在索引中查找符合条件的选项。

索引是特殊文件(InnoDB 数据表上的索引是表空间的组成部分),其中包含指向数据表中所有记录的引用指针。更笼统地说,数据库索引就像一本书前面的目录,可以加快数据库查询的速度。在没有索引的情况下,数据库会遍历所有数据,选择符合条件的选项;有了相应的索引,数据库就会直接在索引中查找符合条件的选项。

索引的性质分类:
索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。

2)索引的优点:
一】通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
二】可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
三】可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
四】在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
五】通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

3)索引的缺点:
一】创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
二】索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
三】当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

4)为什么需要索引:
数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性,访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似,即它们都包含一个数据段和一个指针,指针指向下一个节点(数据块)的内存地址,而且它们都不需要连续存储(即逻辑上相邻的数据块在物理上可以相隔很远)。

由于很多记录只能按一个字段进行排序,如果要查询一个未排序的字段,则需要使用线性查找,即需要访问N/2个数据块,其中N指的是所覆盖的所有字段一张桌子。数据块。如果该字段是非键字段(即不包含唯一值),则必须搜索整个表空间,即必须访问所有N个数据块。 (在某些情况下,索引可以避免排序操作。)

然而,对于排序字段,可以使用二分查找,因此只访问log2 N块数据。同样,对于已经排序的非关键字段,只要找到更大的值,就不需要再去表中的其他数据块中查找。这样,性能将会得到大幅提升。

(2)索引的使用(语法):本文代码实例的针对此数据库
一】创建索引:(三种方式)
第一种方式:

//第一种方式:
//在执行CREATE TABLE 时创建索引:(硬设一个id索引)
CREATE TABLE `black_list` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`black_user_id` BIGINT(20) NULL DEFAULT NULL,
	`user_id` BIGINT(20) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
    INDEX indexName (black_user_id(length))
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;


第二种方式:使用ALTER TABLE命令去增加索引:
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

//标准语句:
ALTER TABLE table_name ADD INDEX index_name (column_list)//添加普通索引,索引值可出现多次。 
ALTER TABLE table_name ADD UNIQUE (column_list)//这条语句创建的索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。 
ALTER TABLE table_name ADD PRIMARY KEY (column_list)//该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE table_name ADD FULLTEXT index_name(olumu_name);该语句指定了索引为FULLTEXT,用于全文索引。


//针对上述数据库,增加商品分类的索引
ALTER table commodity_list ADD INDEX classify_index  (Classify_Description)


其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可自己命名,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。

第三种方式:使用CREATE INDEX命令创建
CREATE INDEX可对表增加普通索引或UNIQUE索引。

//标准语句:
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
//针对上述数据库:
CREATE INDEX classify_index  ON commodity_list (Classify_Description)


table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。

二】删除索引:
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理,其格式如下:

DROP INDEX [indexName] ON [table_name];
alter table [table_name] drop index [index_name] ;
alter table [table_name] drop primary key ;
//针对上述数据库
drop index classify_index on commodity_list ;


其中,在前面的两条语句中,都删除了table_name中的索引index_name。而在最后一条语句中,只在删除PRIMARY KEY索引中使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除列,索引将会受到影响。对于多列索引,如果删除其中一列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

3]查看索引:

SHOW INDEX FROM [table_name];
show keys from [table_name];

在这里插入图片描述
Table:表的名称。

Non_unique:如果索引不能包含重复单词,则为 0。 1 如果可能的话。

Key_name:索引的名称。

Seq_in_index:索引中的列序号,从1开始。

Column_name:列名称。

排序规则:列在索引中的存储方式。在MySQL中,有值'A'(升序)或NULL(不排序)。

基数:索引中唯一值数量的估计。可以通过运行 ANALYZE TABLE 或 myisamchk -a 来更新它。基数根据存储为整数的统计信息进行计数,因此即使对于小型表,该值也不需要精确。基数越大,MySQL 在进行连接时使用该索引的机会就越大。

Sub_part:如果列仅部分索引,则索引的字符数。如果整个列都被索引,则为 NULL。

Packed:表示关键字如何打包。如果未压缩则为 NULL。

Null:如果该列包含 NULL,则它包含 YES。如果不是,则该列包含“否”。

Index_type:使用的索引方法(BTREE、FULLTEXT、HASH、RTREE)。

评论:评论

二、索引的基本原理:
举例解析基本原理:
整体性原理例子:
除了词典,生活中随处可见索引的例子,如火车站的车次表、图书的目录等。它们的原理都是一样的,通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。

SQL的应用场景会使用索引:
数据库也是一样,但显然要复杂许多,因为不仅面临着等值查询,还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?我们回想字典的例子,能不能把数据分成段,然后分段查询呢?最简单的如果1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段…这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。

针对存储性质讲解:此部分转载自此博主此博客
由于存储介质的特性,磁盘本身存取就比主存慢很多,再加上机械运动耗费,磁盘的存取速度往往是主存的几百分分之一,因此为了提高效率,要尽量减少磁盘I/O。为了达到这个目的,磁盘往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

由于顺序磁盘读取非常高效(无寻道时间,旋转时间极少),因此预读可以提高具有局部性的程序的 I/O 效率。

预读长度一般为页的整数倍。页是计算机管理的内存的逻辑块。硬件和操作系统通常将主存和磁盘存储区域划分为连续的相同大小的块。每个存储块称为页(在许多操作系统中,页大小通常为4k),主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时,就会触发缺页异常。这时,系统会向磁盘发出读信号,磁盘会找到数据的起始位置,向后读取一页或多页。加载到内存中,然后异常返回,程序继续运行。

索引数据结构:B+tree 这里部分转载自该博主的博客


B树:关于B树的相关,以后我会详细更新在此文档
B树中每个节点包含了键值和键值对于的数据对象存放地址指针,所以成功搜索一个对象可以不用到达树的叶节点。
成功搜索包括节点内搜索和沿某一路径的搜索,成功搜索时间取决于关键码所在的层次以及节点内关键码的数量。

在B树中查找给定关键字的方法是:首先取根节点,在根节点包含的关键字K1,...,kj中查找给定关键字(顺序查找或二分查找方法均可) be used) ,如果找到等于给定值的关键字,则搜索成功;否则,必须确定要查找的关键字在某个Ki或Ki+1之间,所以Pi指向的下一层索引节点块继续查找。 ,直到找到,否则当指针Pi为空时查找失败。

B+tree性质:
1.)n棵子tree的节点包含n个关键字,不用来保存数据而是保存数据的索引。
2.)所有的叶子结点中包含了全部关键字的信息,及指向含这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。
3.)所有的非终端结点可以看成是索引部分,结点中仅含其子树中的最大(或最小)关键字。
B+树非叶节点中存放的关键码并不指示数据对象的地址指针,非叶节点只是索引部分。所有的叶节点在同一层上,包含了全部关键码和相应数据对象的存放地址指针,且叶节点按关键码从小到大顺序链接。如果实际数据对象按加入的顺序存储而不是按关键码次数存储的话,叶节点的索引必须是稠密索引,若实际数据存储按关键码次序存放的话,叶节点索引时稀疏索引。

在B+树中,数据对象仅在叶节点上插入和删除。

B+树有两个头指针,一个是树的根节点,另一个是最小键的叶子节点。

B+tree有两种搜索方法:
1)一种是按叶节点自己拉起的链表顺序搜索。
2)一种是从根节点开始搜索,和B树类似,不过如果非叶节点的关键码等于给定值,搜索并不停止,而是继续沿右指针,一直查到叶节点上的关键码。所以无论搜索是否成功,都将走完树的所有层。
这两种处理索引的数据结构的不同之处:(B和B+树)
1)B树中同一键值不会出现多次,并且它有可能出现在叶结点,也有可能出现在非叶结点中。
而B+树的键一定会出现在叶结点中,并且有可能在非叶结点中也有可能重复出现,以维持B+树的平衡。

2)因为B树键位置不定,且在整个树结构中只出现一次
虽然可以节省存储空间,但使得在插入、删除操作复杂度明显增加。B+树相比来说是一种较好的折中。

3)B树的查询效率与键在树中的位置有关
最大时间复杂度与B+树相同(在叶结点的时候),最小时间复杂度为1(在根结点的时候)。而B+树的时候复杂度对某建成的树是固定的。

在这里插入图片描述
上图展示了一种可能的索引方式。左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的)。为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

三、索引分类:
一)普通索引:
基本的索引,它没有任何限制。

如何创建:

//标准语句:
ALTER TABLE table_name ADD INDEX index_name (column_list)
CREATE INDEX index_name ON table_name (column_list); 
//还有建表的时候创建亦可
CREATE TABLE table_name ( 
ID INT NOT NULL, 
column_listVARCHAR(16) NOT NULL,
INDEX [index_name ] 
(column_list(length)) 
);  


如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。

示例:如果长度为 10,则它是索引该字段的记录的前 10 个字符。

二)唯一索引:
与前面的普通索引类似,不同的就是:MySQL数据库索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

可以通过以下方式创建:

ALTER TABLE table_name ADD UNIQUE (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
//还有建表时创建
CREATE TABLE table_name (
 ID INT NOT NULL, 
 column_list VARCHAR(16) NOT NULL, 
 UNIQUE [index_name ]  
 (column_list(length)) 
 );  


三)主键索引:
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:

CREATE TABLE table_name ( 
ID INT NOT NULL,
 [column] VARCHAR(16) NOT NULL,
 PRIMARY KEY(ID)  
 );  


四)全文索引:(FULLTEXT)
定义:
全文检索是对大数据文本进行索引,在建立的索引中对要查找的单词进行进行搜索,定位哪些文本数据包括要搜索的单词。因此,全文检索的全部工作就是建立索引和在索引中搜索定位,所有的工作都是围绕这两个来进行的。

此索引关键:
建立全文索引中有两项非常重要,一个是如何对文本进行分词,一是建立索引的数据结构。分词的方法基本上是二元分词法、最大匹配法和统计方法。索引的数据结构基本上采用倒排索引的结构。分词的好坏关系到查询的准确程度和生成的索引的大小。

应用:
FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。

但请注意:对于较大的数据集,将数据输入到没有 FULLTEXT 索引的表中然后创建索引比将数据输入到现有的 FULLTEXT 索引要快。但请记住,对于大容量的数据表,生成全文索引是一个非常耗时且消耗硬盘空间的过程。因为! !在插入、修改、删除表的时候,还必须对索引进行一系列的处理。

创建方法:

//针对content做了全文索引:
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);


SQL使用全文索引的方法:首先必须是MyISAM的数据库引擎的数据表
如果是其他数据引擎,则全文索引不会生效。

SELECT * FROM article WHERE MATCH( content) AGAINST('想查询的字符串')


此外,MySQL自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索,我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。

注意:
目前,使用MySQL自带的全文索引时,如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外,如果查询的字符串包含停止词,那么该停止词将会被忽略。

如果可能的话,请先创建表并插入所有数据,然后再创建全文索引,而不是在创建表时直接创建全文索引,因为前者比后者全文索引效率更高。

五)单列索引与多列索引(其实是相当于一个用法技巧)
单列索引,就是平常的只索引一个一个的字段的方式

//例子为name列的头10个字符创建一个索引:
CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10))
);


多列索引(也叫组合索引):
相关概念(适用多列索引的原因):
MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。(在CHAR和VARCHAR列上,你也可以使用列的前缀作为一个索引的部分)。

多列索引可以被认为是一个排序数组,其中包含通过连接索引列值创建的值。

多个单列索引的查询效果与单个多列索引的查询效果不同,因为在执行查询时,MySQL只能使用一个索引,并且会选择限制性最强的索引(结果中记录数最少的) set)来自多个单列索引。

当你为WHERE子句索引中的第一列指定已知数量时,MySQL以这种方式使用多列索引,使得查询速度非常快,即使你没有为其他列指定值。

适用场景:
1.全字段匹配
2.匹配部分最左前缀
3.匹配第一列
4.匹配第一列范围查询(可用用like a%,但不能使用like %b)
5.精确匹配某一列和和范围匹配另外一列

例子:

//假设只使用单列索引名字
 ALTER TABLE people ADD INDEX name (name);
 //使用多列索引:
  ALTER TABLE people ADD INDEX height_name_age (height,name,age);
  //相当于创建了(height)单列索引,(height,name)组合索引以及(height,name,age)组合索引
/*
注意:
注:在mysql中执行查询时,只能使用一个索引,如果我们在name,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。
*/


注意:
在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

组合索引(多列索引)的原则:
原则:
最左前缀:顾名思义,就是最左优先
平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引(多列索引)。例如上面使用的例子就相当于创建了(height)单列索引,(height,name)组合索引以及(height,name,age)组合索引。

此外,补充一个概念对比,那就是聚集索引和非聚集索引:
1)聚集索引:相关概念说法取自此处
定义:
该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,其中数据按姓氏排列。由于聚集索引指定了表中数据的物理存储顺序,因此一张表只能包含一个聚集索引。但索引可以包含多个列(组合索引),就像按名字和姓氏组织的电话簿一样。

注意事项:
定义聚集索引键时使用的列越少越好。

使用的场景:
一)包含大量非重复值的列。

2) 使用以下运算符返回一系列值的查询:BETWEEN、>、>=、< 和 <=。

3) 连续访问的列。

4) 返回大结果集的查询。

e) 使用联接或 GROUP BY 子句的查询经常访问的列;一般来说,这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列建立索引可以防止 SQL Server 对数据进行排序,因为行已经排序。这提高了查询性能。

6)需要非常快速的单行查找(通常通过主键)的OLTP类型应用程序。

缺点:请看此博客
不适用于:

经常更改的列。这会导致整行被移动(因为SQL Server必须按物理顺序保留行中的数据值)。值得注意这一点,因为数据在大容量事务处理系统中是不稳定的。

宽键。聚集索引中的键值被所有非聚集索引用作查找键,因此存储在每个非聚集索引的叶条目中。

2)非聚集索引:
定义:
数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置。

非聚集索引中的项目按索引键值的顺序存储,而表中的信息按其他顺序存储(这可以由聚集索引指定)。对于非聚集索引,您可以为在表的非聚集索引中查找数据时常用的每一列创建一个非聚集索引。有些书包含多个索引。例如,园艺入门书籍可能包含植物流行名称索引和植物学名索引,因为这是读者查找信息的两种最常见方式。

两者的区别此处有个很清晰的例子:请点此处
选择使用的场景:

在这里插入图片描述
关于聚集索引以及非聚集索引的几个问题:
一)聚集索引的约束是唯一性,是否要求字段也是唯一的呢?
一般我们指定一个表的主键,如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,SQL会默认在此字段上创建一个聚集索引,而主键都是唯一的,所以理所当然的认为创建聚集索引的字段也需要唯一。

可以在您想要创建的任何字段上创建聚集索引。这是理论上的。实际中不能随意指定,否则在性能方面将是一场噩梦。

二)|主键就是聚集索引???
这样有时会对聚集索引的一种浪费。Innodb将通过主键聚集数据,如果没有定义主键,Innodb会选择第一个非空的唯一索引代替,如果没有非空唯一索引,Innodb会隐式定义一个6字节的rowid主键来作为聚集索引。innodb只聚集在同一个页面中的记录,包含相邻键值的页面可能会相距甚远。

由于每个表只能有一个聚集索引规则,这使得聚集索引更有价值。

使用聚集索引的最大优点是可以根据查询需求快速缩小查询范围,避免全表扫描。在实际应用中,由于ID号是自动生成的,我们并不知道每条记录的ID号,因此我们在实际应用中很难利用ID号进行查询。这就使得使用ID号的主键作为聚集索引造成了资源的浪费。其次,使用不同ID号的字段作为聚集索引,不符合“存在大量不同值时不应建立聚集索引”的规则;当然,这种情况仅适用于经常修改记录内容,尤其是索引项的用户。会有负面影响,但对查询速度没有影响。

三)是不是聚集索引就一定要比非聚集索引性能优呢???
如果想查询学分在60-90之间的学生的学分以及姓名,在学分上创建聚集索引是否是最优的呢?

答:不需要。由于只输出两列,所以我们可以对学分和学生姓名建立联合非聚集索引(即多列索引)。此时的索引形成覆盖索引,即索引中存储的内容就是最终输出的数据。这种索引在查询上比基于信用的聚集索引表现得更好。

四)在数据库中通过什么描述聚集索引与非聚集索引的?
索引是通过二叉树的形式进行描述的,我们可以这样区分聚集与非聚集索引的区别:聚集索引的叶节点就是最终的数据节点,而非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针。

五)在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢?
在有主键的表中插入数据行,由于有主键唯一性的约束,所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况:聚集索引由于索引叶节点就是数据页,所以如果想检查主键的唯一性,需要遍历所有数据节点才行,但非聚集索引不同,由于非聚集索引上已经包含了主键值,所以查找主键唯一性,只需要遍历所有的索引页就行,这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。

四、索引设计优化:
(1)索引建立的几大原则:
1) 最左前缀匹配原则
非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

2)=和in可以乱序
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。

3)尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)
表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

4)索引列不能参与计算,保持列“干净”
比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5)尽量的扩展索引,不要新建索引。
比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

6)定义有外键的数据列一定要建立索引。
7)对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
8)对于定义为text、image和bit的数据类型的列不要建立索引。
9)对于经常存取的列避免建立索引
(2)索引使用的注意点:
一、)一般说来,索引应建立在那些将用于JOIN,WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说,出现大量重复值是很有可能的情况。
二、)应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where num=10 or Name = 'fuzhu'


最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。
不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
***三、)***应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
***四、)***应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where num=10 or Name = 'fuzhu'


可以这样查询,充分利用索引:

select id from t where num = 10
union all
select id from t where Name = 'fuzhu'



***五、***)in 和 not in 也要慎用,否则会导致全表扫描。
而且负向查询(not , not in, not like, <>, != ,!>,!< ) 不会使用索引

select id from t where num in(1,2,3)


对于连续的数值,能用 between 就不要用 in 了:

select id from t where num between 1 and 3


很多时候用 exists 代替 in 是一个好的选择,当然exists也不跑索引。

select num from a where num in(select num from b)


正上面的,用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num)


***六、)***下面的模糊查询也将导致全表扫描:

select id from t where name like ‘%abc%’


一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引,而like “aaa%”可以使用索引。

为了提高效率,可以考虑全文搜索。

既然谈到模糊查询下使用索引,我们就顺便详细地讲讲吧。
1. like %keyword 索引失效,使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索引,不走全表扫描。例子在此处
2. like keyword% 索引有效。
3. like %keyword% 索引失效,也无法使用反向索引。

//可以拿我给出的数据库试一下嘛。然后用explain测试,就能测出有没有走索引了
select * from table where code like 'Classify_Description%'  
select * from table where code like '%Classify_Description%'  
select * from table where code like '%Classify_Description'  



***七、)***如果在 where 子句中使用参数,也会导致全表扫描。
因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

select id from t where num = @num

可以改为强制查询使用索引:

select id from t with(index(索引名)) where num = @num

应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where num/2 = 100
正上面的应改为:
select id from t where num = 100*2
八、)应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3) = ’abc’       //name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0    -–‘2005-11-30’    //生成的id

应改为:
select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

九、).不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
十、)在使用索引字段作为条件时,如果该索引是复合索引(多列索引),那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
十一、)索引并不是越多越好
索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

十二、)应尽可能的避免更新 clustered 索引数据列
因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

十三、)尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
十四、)MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。
因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

用到的部分例子数据库在里面
好了,MySQL优化系列(三)–索引的使用、原理和设计优化
这是积累的必经一步,我会继续出这个系列文章,分享经验给大家。欢迎在下面指出错误,共同学习!!你的点赞是对我最好的支持!!

更多内容,可以访问JackFrost的博客
————————————————
版权声明:本文为CSDN博主「Jack__Frost」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/Jack__Frost/article/details/72571540

. . .

相关推荐

额外说明

授权mac下的文件夹并添加权限

1.打开终端 2.执行命令 chmod -R 777 你要授权的文件路径 例如我的  

额外说明

由于找不到 iUtils.dll,代码执行无法继续。重新安装该程序可能会解决此问题。

目录 一、下载工具 二、修复办法 三、重启计算机 解决办法: 一、下载工具 工具下载地址:https://www.ewbang.com/community/service/details/1695878580460.html 二、修复办法 操作步骤:打开

额外说明

25岁之前农村青年人应该阅读的一篇文章

  25岁是一个男人心态抑或思考方式的转折点。对男人来讲很重要,也是一个里程碑的时刻。从这一刻后,你需要具备责任心与事业心。   25岁之前,你不需要具备责任心与事业心,因为这不是你应该考虑的问题,不是这个年龄段思考的问题。 25岁之前,你不需要跟周围的

额外说明

java面试题收集(06)

1、HashMap 和HashTable区别 (1)线程安全性不同 HashMap是线程不安全的,HashTable是线程安全的,HashTable中的方法有Synchronize修饰,所以是线程安全的。在多线程并发的情况下,可以使用HashTable保

额外说明

美工建模-PR视频剪辑自学教程

美工建模-PR视频剪辑自学教程 1 快速认识PR主界面 2 导入与基本剪辑 3 背景音乐的添加和修改 4 利用关键帧做一个高仿弹幕 5 让说话的速度快起来 6 给视频添加效果 7 鬼畜教学 8 片尾字幕 9 视频的导出 1 快速认识PR主界面 素材 编辑

额外说明

spring初始化bean时初始化操作

spring初始化bean时执行某些方法完成特定的初始化操作 在项目中经常会在容器启动时,完成特定的初始化操作,如资源文件的加载等。 一 实现的方式有三种: 1.使用@PostConstruct注解,该注解作用于void方法上 2.在配置文件中配置ini

额外说明

使用windows下的Eclipse或者IDEA远程连接Linux的Hadoop并运行wordcount

Windows使用Eclipse或IDEA连接Linux环境Hadoop运行wordcount   1 环境准备      linux 系统版本centos7 ,Hadoop版本2.7.6      (1)正确安装hadoop,具体安装步骤参考安装教程,

额外说明

HNU数据结构与算法分析-作业1-算法分析

  1. (简答题) 1.(教材3.4)(a)假设某一个算法的时间代价为 ,对于输入规模n,在某台计算机上实现并完成该算法的时间为t秒。现在另有一台计算机,运行速度为第一台的64倍,那么t秒内新机器上能完成的输入规模为多大? 2.(教材3.12) 写出下

额外说明

(一)Qt下实现多个海康工业相机内触发采集回调取流显示

系列文章目录 提示:这里是该系列文章的所有文章的目录 第一章:(一)Qt下实现多个海康工业相机内触发采集回调取流显示 第二章:(二)Qt下多线程实现多个海康工业相机内触发采集回调取流显示 文章目录 系列文章目录 前言 一、环境配置 二、图像显示流程 三、

额外说明

Linux中 ls -l 详解,图解各字段意义

ls -l //查看文件详细信息 点击图片放大。

ads via 小工具