关于mysql:MySQLmysql索引

1、索引是什么

1.1 索引简介

索引是表的目录，是数据库中专门用于帮忙用户疾速查问数据的一种数据结构。相似于字典中的目录，查找字典内容时能够依据目录查找到数据的寄存地位，以及疾速定位查问数据。对于索引，会保留在额定的文件中。

1.2 次要留神的中央：

(1). 索引能够进步查问速度，会减慢写入速度，索引的毛病时创立和保护索引须要消耗工夫。

(2). 索引并不是越多越好，索引诚然能够进步相应的 select 的效率，但同时也升高了 insert 及 update 的效率，因为 insert 或 update 是有可能会重建索引。

1.3 什么样的字段适宜创立索引

(1).表的主键、外键必须有索引；外键是惟一的，而且常常会用来查问。

(2).常常与其余表进行连贯的表，在连贯字段上应该建设索引。

(3). 数据量超过 300 的表应该有索引。

(4). 重要的 SQL 或调用频率高的 SQL，比方经常出现在 where 子句中的字段，order by,group by, distinct的字段都要增加索引。

(5).常常用到排序的列上，因为索引曾经排序。

(6).常常用在范畴内搜寻的列上创立索引，因为索引曾经排序了，其指定的范畴是间断的。

1.4 什么场景不适宜创立索引

1.对于那些在查问中很少应用或者参考的列不应该创立索引，这是因为，既然这些列很少应用到，因而有索引或者无索引，并不能进步查问速度。相同，因为减少了索引，反而升高了零碎的保护速度和增大了空间需要。

2.对于那 些只有很少数据值的列也不应该减少索引。因为原本后果汇合就是相当于全表查问了，所以没有必要。这是因为，因为这些列的取值很少，例如人事表的性别列，在查问的后果中，后果集的数据行占了表中数据行的很大比 例，即须要在表中搜寻的数据行的比例很大。减少索引，并不能显著放慢检索速度。

3.对于那些定义为 text, image 和 bit 数据类型的列不应该减少索引。这是因为，这些列的数据量要么相当大，要么取值很少。

4、当批改性能远远大于检索性能时，不应该创立索引。这是因为，批改性能和检索性能是互相矛盾的。当减少索引时，会进步检索性能，然而会升高批改性能。当缩小索引时，会进步批改性能，升高检索性能。因 此，当批改性能远远大于检索性能时，不应该创立索引。

5.不会呈现在 where 条件中的字段不该建设索引。

6.如果列均匀分布在 1 和 100 之间，却只是查问中 where key_part1 > 1 and key_part1 < 90 不应该减少索引

1.5 索引应用以及设计规范

1、越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和 CPU 缓存中都须要更少的空间，解决起来更快。简略的数据类型更好：整型数据比起字符，解决开销更小，因为字符串的比拟更简单

2、尽量避免 null：应该指定列为 not null，含有空值的列很难进行查问优化 ，因为它们使得索引、索引的统计信息以及比拟运算更加简单， 也可能导致复合索引有效

3、主键抉择策略

1) 每个表必须显示指定主键；

2) 主键尽量为一个字段，且为数字类型, 防止应用字符串；

3) 主键尽量放弃增长趋势，倡议应用 id 的生成器；

4)主键尽量杜绝联结索引

4、每个表的索引个数尽量少于 5 个，防止创立反复冗余索引；每个组合索引尽量避免超过 3 个字段，索引不是越多越好，审慎增加索引，综合思考数据分布和数据更新

5、重要的 SQL 或调用频率高的 SQL

1) update/select/delete 的 where 条件列字段都要增加索引；

2) order by , group by, distinct 的字段都要增加索引

6、避免出现 index merge（单索引 or 的查问）；正当利用 covering index

7、组合索引创立时，把区分度 (选择性) 高的字段放在后面；依据 SQL 的个性，调整组合索引的程序

8、对于 varchar 字段加索引，倡议应用前缀索引，从而减小索引大小

2、索引的创立和删除

2.1 创立数据表时创立索引的根本语法结构：

create table 表名(

字段名 1 数据类型 [约束条件...]

字段名 2 数据类型 [约束条件...]

...

[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] (被用作索引的字段名[(长度)] [ASC|DESC])

);

#[]中的内容可加可不加，看具体情况；|: 示意 '或者' 字段名就是表的列名

2.2 间接创立索引的根本语法结构:

create [`UNIQUE|FULLTEXT|SPATIAL]` `INDEX|KEY` `[索引名]` `on` ` 表名 (被用作索引的字段名[(长度)] [ASC|DESC`])

);

2.3 批改表构造的形式增加索引的根本语法:

alter table 表名 add 索引格局;

2.4 删除索引:

drop index 索引名 on 表名;

2.5 以上呈现的索引属性名的含意：

UNIQUE`：(unique)，可选参数，示意索引为惟一索引。`

FULLTEXT`：(fulltext)，可选参数，示意索引为全文索引。`

SPATIAL`：(spatial)，可选参数，示意索引为空间索引。`

INDEX | KEY`：(index` `|` `key)，必选参数，用于指定字段为索引的，用户在抉择时，只须要二选一即可。`

[索引名]：可选参数，其作用是给创立的索引取新名称。(起到方便使用的目标)

被选定的字段名：必选参数，被用作索引的对应的字段名称，该字段必须被事后定义。

长度：可选参数，其指索引的长度，必须是字符串类型才能够应用。(比方：电话号码)

[ASC | DESC]：(asc | desc)，可选参数，ASC 示意升序排列，DESC 示意降序排列。

3、索引类型

索引有很多种类型，能够为不同的场景提供更好的性能。在 MySQL 中，索引是在存储引擎层而不是服务层。所以，并没有对立的索引规范：不同的存储引擎的索引的工作形式并不一样，也不是所有的存储引擎都反对所有类型的索引。

3.1 一般索引

创立一般索引，不须要增加 [UNIQUE | FULLTEXT | SPATIAL] 等任何参数进行束缚。

• 一般索引 (由关键字 KEY 或 INDEX 定义的索引) 的惟一工作是放慢对数据的访问速度。
• 只为那些最经常出现在‘查问条件’(WHERE column = …) 或‘排序条件’(ORDER BYcolumn)中的数据列，来创立索引。
• 只有有可能，就应该抉择一个数据最参差、最紧凑的数据列 (如一个 int 整数类型的数据列) 来创立索引。

建表语句:

create table person(

id int`(11) not null auto_increment`,

name varchar(20) default null,

primary key (id),

key (name) using btree

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

增加索引:

create index name on person(name);

3.2 惟一索引（主键）

创立惟一索引时，应用 UNIQUE 参数对 INDEX | KEY 进行束缚。

• 与一般索引相似，不同的就是：索引列的值必须惟一，但容许有空值（留神和主键不同）。如果是组合索引，则列值的组合必须惟一，创立办法和一般索引相似。
• 如果能确定某个数据列将只蕴含彼此各不相同的值，在为这个数据列创立索引的时候就应该用关键字 UNIQUE 把它定义为一个惟一索引。这么做的益处：一是简化了 MySQL 对这个索引的管理工作，这个索引也因而而变得更有效率；二是 MySQL 会在有新记录插入数据表时，主动查看新记录的这个字段的值是否曾经在某个记录的这个字段里呈现过了；如果是，MySQL 将回绝插入那条新记录。也就是说，惟一索引能够保证数据记录的唯一性。
• 事实上，在许多场合，人们创立惟一索引的目标往往不是为了进步访问速度，而只是为了防止数据呈现反复。
• 主索引：在后面曾经重复屡次强调过！必须为主键字段创立一个索引，这个索引就是所谓的 ” 主索引 ”。
• 主索引 与 惟一索引的惟一区别是：前者在定义时应用的关键字是 PRIMARY 而不是 UNIQUE。

eg: 创立的表名为 address 的数据表，并在该表的 id 字段上建设名称为 address_id 的惟一索引，

建表语句:

create table address(

id int(11)  auto_increment primary key not null,

name varchar(50),

address varchar(200),

UNIQUE INDEX address_id (id ASC) # id 字段设为惟一索引，并赋予新名字 address_id , 且 ASC 升序排列

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

3.3 全文索引

全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创立全文索引须要应用 FULLTEXT 参数进行束缚。

• MySQL 从 3.23.23 版开始反对全文索引和全文检索，fulltext 索引仅可用于 MyISAM 表；他们能够从 CHAR、VARCHAR 或 TEXT 列中作为 CREATE TABLE 语句的一部分被创立，或是随后应用 ALTER TABLE 或 CREATE INDEX 被增加。
• 对于较大的数据集，将你的材料输出一个没有 FULLTEXT 索引的表中，而后创立索引，其速度比把材料输出现有 FULLTEXT 索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个十分耗费工夫十分耗费硬盘空间的做法。
• 文本字段上的一般索引只能放慢对呈现在字段内容最后面的字符串 (也就是字段内容结尾的字符) 进行检索操作。如果字段里寄存的是由几个、甚至是多个单词形成的较大段文字，一般索引就没什么作用了。这种检索往往以 LIKE%word% 的模式呈现，这对 MySQL 来说很简单，如果须要解决的数据量很大，响应工夫就会很长。
• 这类场合正是全文索引 (full-textindex) 能够大显神通的中央。在生成这种类型的索引时，MySQL 将把在文本中呈现的所有单词创立为一份清单，查问操作将依据这份清单去检索无关的数据记录。全文索引即能够随数据表一起创立，也能够等日后有必要时再应用命令增加。
• 有了全文索引，就能够用 SELECT 查问命令去检索那些蕴含着一个或多个给定单词的数据记录了。上面是这类查问命令的根本语法：SELECT * FROM table_name
• WHERE MATCH(column1, column2)
• AGAINST(‘word1’,‘word2’,‘word3’)
• 下面这条命令将把 column1 和 column2 字段里有 word1、word2 和 word3 的数据记录全副查问进去。

eg: 创立的表名为 cards 的数据表，并在该表的 name 字段上建设名称为 cards_number 的全文索引

建表语句:

create table cards(

id int(11) auto_increment primary key not null ,

name varchar(20) default null,

number bigint(11),

info varchar(50),

FULLTEXT KEY cards_number (name) # name 字段设为全文索引，并赋予新名字 cards_number

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

3.4 单列索引

创立单列索引，即在数据表的单个字段上创立索引。创立该类型索引不须要引入束缚参数，用户在建设时只须要指定单列字段名，即可创立单列索引。

• 多个单列索引与单个多列索引的查问成果不同，因为执行查问时，MySQL 只能应用一个索引，会从多个索引中抉择一个限度最为严格的索引。

eg: 创立的表名为 telephone 的数据表，并在该表的 tel 字段上建设名称为 tel_num 的单列索引

create table telephone(

id int(11) primary key auto_increment not null,

name varchar(10) not null,

tel varchar(50) not null,

index tel_num (tel(20)) # tel 字段设为单列索引，并赋予新名字 tel_num, 因为 tel 字段是字符串类型，因而能够设定 '索引长度 20'

);

3.5 多列索引

创立多列索引，即在数据表的多个字段上创立索引。与上述单列索引相似，创立该类型索引不须要引入束缚参数。

• 多个单列索引与单个多列索引的查问成果不同，因为执行查问时，MySQL 只能应用一个索引，会从多个索引中抉择一个限度最为严格的索引。

eg: 创立的表名为 information 的数据表，并在该表的 name 和 sex 字段上建设名称为 info 的多列索引

create table information(

inf_id int(11) auto_increment primary key not null,

name varchar(20) not null,

sex varchar(5) not null,

birthday varchar(50) not null,

index info (name,sex) # name,sex 字段设为多列索引，并赋予新名字 info

);

• 留神：在多列索引中，只有查问条件中应用了这些字段中的第一个字段(即下面示例中的 name 字段)，索引才会被应用。触发多列索引的条件是用户必须应用索引的第一字段，如果没有用到第一字段，则索引不起任何作用，用户想要优化查问速度，能够利用该类索引模式。

3.6 空间索引

创立空间索引，须要增加 SPATIAL 参数进行束缚。同样，必须阐明的是，只有 MyISAM 类型的表反对该类型‘空间索引’。而且，索引字段必须有非空束缚。

eg: 创立的表名为 list 的数据表，并在该表的 goods 字段上建设名称为 listinfo 的空间索引，这里 goods 字段有非空束缚，

create table list(

id int(11) primary key auto_increment not null,

goods geometry not null,

SPATIAL INDEX listinfo (goods) # 这里 goods 字段有非空束缚，符合条件 goods 字段设为空间索引，并赋予新名字 listinfo

)engine=MyISAM;

留神：goods 字段上曾经建设名称为 listinfo 的空间索引，其中 goods 字段必须不能为空，且数据类型是 GEOMETRY，该类型是空间数据类型。

空间类型不能用其余类型代替，否则在生成空间素引时会产生谬误且不能失常创立该类型索引。

外键索引：如果为某个外键字段定义了一个外键约束条件，MySQL 就会定义一个外部索引来帮忙本人以最有效率的形式去治理和应用外键约束条件。

另外：INDEX | KEY：(index | key)，必选参数，用于指定字段为索引的，用户在抉择时，只须要二选一即可。

区别：

1). key：是数据库的物理构造，它蕴含两层意义：一是束缚（偏重于束缚和标准数据库的构造完整性）、二是索引（辅助查问用的）。包含 primary key, unique key, foreign key 等。primary key 有两个作用，一是束缚作用（constraint），用来标准一个存储主键和唯一性，但同时也在此 key 上建设了一个 index；unique key 也有两个作用，一是束缚作用（constraint），标准数据的唯一性，但同时也在这个 key 上建设了一个 index；foreign key 也有两个作用，一是束缚作用（constraint），标准数据的援用完整性，但同时也在这个 key 上建设了一个 index；2). index：是数据库的物理构造，它蕴含一层意义：它只是索引（辅助查问用的），它创立时会在另外的表空间（mysql 中的 innodb 表空间）以一个相似目录的构造存储。因而，索引只是索引，它不会去束缚索引的字段的行为（那是 key 要做的事件）。

3.7 聚簇索引

1. 聚簇索引并不是一种独自索引，而是一种数据贮存形式。InnoDB 的聚簇索引实际上在同一构造中保留了 B -Tree 索引和数据行。

2. 当表有聚簇索引时，它的数据行实际上寄存在索引的叶子页中。“聚簇”示意数据行和相邻的键值紧凑的贮存在一起。

3. 对应 InnoDB 来说如果表没有定义主键，会抉择一个惟一的非空索引代替。如果没有这样的索引 InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只汇集在同一页面中的记录。

4. 聚簇索引的劣势：

（1）能够把相干数据保留在一起。（2）数据拜访更快。数据和索引保留在同一个 B-Tree。（3）应用笼罩索引扫描的查问能够间接应用页节点的主键值

5. 聚簇索引的毛病：

(1)聚簇索引最大的进步了 I / O 密集型利用的性能，但如果数据全副都放到内存中，则数据的程序就没有那么重要了，聚簇索引也就没什么劣势了。（2）插入速度重大依赖插入程序。依照主键插入的形式是 InnoDB 速度最快的形式，但如果不是依照主键程序加载数据，那么在加载后最好应用 OPTIMIZE TABLE 命令从新组织一 2 下表（3）更新聚簇索引列的代价很高。因为会强制 InnoDB 将每个被更新的行挪动到新的地位

6. 二级索引

主键索引的叶子节点存的是整行数据，在 InnoDB 里，主键索引也被称为聚簇索引非主键索引的叶子节点内容是主键的值。在 InnoDB 里。非主键索引也被称为二级索引。如：select* from order where user_id=3; user_id 是一般索引。则会先搜寻 user_id 的索引树，失去 id=5, 再到 id 索引树搜寻一次，这个过程就是“回表”。也就是说非主键索引须要查问 2 次

3.8 笼罩索引

1.mysql 能够应用索引间接来获取列的数据，这样就能够不再须要读取数据行。如果索引的叶子节点中曾经蕴含要查问的数据，那么还有什么必要再回表查问呢？如果一个索引蕴含（笼罩）所有要查问的字段的值，那么就称为“笼罩索引”

2. 笼罩索引能够进步查问的性能，不须要回表，益处是：

（1）索引条目通常小于数据行，如果只需读取索引，那么 mysql 就会缩小访问量（2）索引是依照列值顺序存储的，索引 I /O 密集型的范畴查问会比随机从磁盘读取每一行数据的 I /O 要少得多（3）一些存储引擎如 MyISAM 在内存只缓存索引，数据则依赖操作系统来缓存，因而要拜访数据须要一次零碎调用，这可能导致重大的性能问题，尤其是那些零碎调用占了数据拜访中最大开销的场景（4)InnoDB 的聚簇索引，笼罩索引对 InnoDB 表的特地有用。InnoDB 的二级索引在叶子节点保留了行的主键值，所以如果二级主键可能笼罩查问，则能够防止对主键索引的二次查问。

1. select id from order where user_id between 1 and 3 这时候只须要查 ID 的值，而 ID 曾经在 user_id 索引树上，因而能够间接提供查问后果，不须要回表。

select from order where user_id between 1 and 3 一旦用了 select ，就会有其余列须要读取，这时在读完 index 当前还须要去读 data 才会返回后果。

这两种解决形式性能差别十分大，特地是返回行数比拟多，并且读数据须要 I/O 的时候，可能会有几十上百倍的差别。因而倡议依据须要用 select *

4、索引原理

5.1 索引实现的数据结构

Mysql 对于不同的存储引擎，索引的实现实现形式是不同的。支流的存储引擎：MyISAM 和 InnoDB，两种存储引擎都应用 B +Tree（B-Tree 的变种）作为索引构造，然而在实现形式上，却有很大的不同。上面是两种 BTree 数据结构：

B-tree 构造：

B-Tree 无论是叶子结点还是非叶子结点，都含有 key 和一个指向数据的指针，只有找到某个节点后，就能够依据指针找到磁盘地址从而找到数据。

B+tree 构造:

B+Tree 所有叶子结点才有指向数据的指针。非叶子结点就是纯索引数据和主键。每个叶子结点都有指向下一个叶子结点的链接。

小结：非叶子结点寄存在内存中，也叫内结点，因而，在无限的内存中，B-Tree 中每个数据的指针会带来额定的内存占用，缩小了放入内存的非叶子结点数；B+Tree 则尽可能多地将非叶子结点放入内存中

5.2MySQL 中索引实现

因为 B +Tree 数据结构的劣势，目前 mysql 根本都采纳 B +Tree 形式实现索引，上面列出了两个最罕用的存储引擎的索引实现：

1、MyISAM：如下图，叶子结点的 data 域寄存的是数据的地址：

上图表中共三列数据，col1 为主键，示意 MyISAM 表的主索引示意图，在 MyISAM 中，主索引和辅助索引（除主键以外的其它索引）在结构上没有任何区别，只是主索引的 key 是惟一的，辅助索引的 key 能够反复。

2、InnoDB：比照 MyISAM，InnoDB 的主键索引与辅助索引存储形式是不同的：

主键索引：主键索引的叶子结点寄存的是 key 值和数据，叶子结点载入内存时，数据一起载入，找到叶子结点的 key，就找到了数据。

辅助索引：辅助索引的叶子结点寄存的是 key 值和对应的记录的主键值，应用辅助索引查问，首先检索辅助索引获取主键，而后用主键在主索引中检索获取记录。

小结：MyISAM 索引叶子节点寄存的是数据的地址，主键索引与辅助索引除了值得唯一性在结构上齐全一样。InnoDB 索引叶子节点寄存的内容因索引类型不同而不同，主键索引叶子节点寄存的是数据自身，辅助索引叶子节点上寄存的是主键值。
更多技术详情请查看云掣官网https://www.dtstack.com/dtsmart/