MySQL索引有哪些作用
发布时间：2022-02-15 22:41:22 所属栏目：MySql教程 来源：互联网
导读：这篇文章给大家分享的是有关MySQL索引有哪些作用的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 一、索引简介 (1)索引的含义和特定 (2)索引的分类 (3)索引的设计原则 二、创建索引 (1)创建表的时候创建索引 (2)在已经存在的
|
这篇文章给大家分享的是有关MySQL索引有哪些作用的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 一、索引简介 (1)索引的含义和特定 (2)索引的分类 (3)索引的设计原则 二、创建索引 (1)创建表的时候创建索引 (2)在已经存在的表上创建索引 (3)删除索引 一、索引简介 索引用于快速找出在某列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始读完整个表，知道找出相关的行。表越大，查询数据所花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达某个位置去搜寻数据文件，而不必查看所有数据。 (1)索引的含义和特定 含义：索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。用于快速找出在某个或多个列中有一特定值的行。 索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。 MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAL存储引擎可以支持HASH和BTREE索引。 索引的优点： 1.通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。 2.可以大大加快数据的查询速度。(创建索引的最主要原因) 3.在实现数据的参考完整性方面，可以加速表和表之间的连接。 4.在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。 增加索引的缺点： 1.创建索引和维护索引耗费时间，随着数据量的增加所耗费的时间也会增加。 2.索引占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。 3.当对表中的数据进行增加、删除和修改时，索引也要动态维护，降低了数据的维护速度。 (2)索引的分类 1.普通索引和唯一索引(unique) 普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。 唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 主键索引是一种特殊的唯一索引，不允许有空值。 2.单列索引和组合索引 单列所以你即一个索引只包含单个列，一个表可以有多个单列索引。 组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。 使用组合索引时遵循最左前缀集合。 3.全文索引(fulltext) 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值，全文索引可以在char、varchar或text类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。 4.空间索引(spatial) 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别为geometry，point，linestring和polygon。MySQL使用spatial关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为not null，空间索引只能在存储引擎为MySQL的表中创建。 (3)索引的设计原则 索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要，设计索引时，应该考虑以下准则： 1.索引并非越多越好。 2.避免对经常更新的表进行过多的索引，并且索引的列尽可能少。 3.数据量小的表最好不要使用索引。 4.在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。 5.当唯一性是某种数据本身的特征时，指定唯一索引。 6.在频繁进行排序或分组(进行group by 或 order by操作)的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。 二、创建索引 语法格式： create table table_name [col_name date_type][unique|fulltext|spatial] [index|key] [index_name] (col_name [length]) [asc | desc] unique，fulltext和spatial为可选参数，分别表示唯一索引，全文索引和空间索引。 index与key为同义词，两者作用相同，用来指定创建索引。 col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择。 index_name指定索引的名称，为可选参数，若不指定，MySQL默认col_name为索引值。 length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度。 asc或desc指定升序或者降序的索引值存储。 (1)创建表的时候创建索引 ①创建普通索引 普通索引是 最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。 【例1】在book表中的year_publication字段上建立普通索引，SQL语句如下： mysql> create table book -> ( -> bookid int not null, -> bookname varchar(255) not null, -> authors varchar(255) not null, -> info varchar(255) null, -> comment varchar(255) null, -> year_publication year not null, -> index(year_publication) -> );Query OK, 0 rows affected (0.21 sec)mysql> show create table book G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, KEY `year_publication` (`year_publication`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from book where year_publication=1990 G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: book partitions: NULL type: ref possible_keys: year_publication key: year_publication key_len: 1 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec) explan语句输出结果的各行解释如下： select_type行指定所使用的select查询类型，这里值为simple，表示简单的select，不使用union或子查询。其他可能的取值有primary、union、subquery等。 table行指定数据库读取的数据表的名字，它们按被读取的先后顺序排列。 type行指定了本数据库表与其他数据库表之间的关联关系，可能的取值有system、const、eq_ref、ref、range、index和all。 possible_keys行.给出了MySQL在搜索数据记录时可选用的各个索引。 key行是MySQL实际选用的索引。 key_len行给出索引按字节计算的长度，key_len数值越小，表示越快。 ref行给出了关联关系中另一个数据表里数据列的名字。 rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。 extra行提供了与关联操作有关的信息。 可以看到，possible_key和key的值都为year_publication，查询时使用了索引。 ②创建唯一索引 创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。它与前面的普通索引类似，不同就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 【例2】创建一个表t1，在表中的id字段上使用unique关键字创建唯一索引。 mysql> create table t1 -> ( -> id int not null -> ,name char(30) not null, -> unique index uniqidx(id) -> );Query OK, 0 rows affected (0.27 sec)mysql> show create table t1 G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, UNIQUE KEY `uniqidx` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec) ③创建单列索引 单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。 【例3】创建一个表t2，在表中的name字段上创建单列索引。 mysql> create table t2 -> ( -> id int not null, -> name char(50) null, -> index singleidx(name(20)) -> );Query OK, 0 rows affected (0.06 sec)mysql> show create table t2 G*************************** 1. row *************************** Table: t2Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` char(50) DEFAULT NULL, KEY `singleidx` (`name`(20))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec) 由结果可以看到，id字段上已经成功建立了一个名为SingleIdx的单列索引，索引长度为20。 ④创建组合索引 组合索引就是在多个字段上创建一个索引。 【例4】创建表t3，在表中的id、name和age字段上建立组合索引，SQL语句如下: mysql> create table t3 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> index mulitiidx(id,name,age) -> );Query OK, 0 rows affected (0.07 sec)mysql> show create table t3 G*************************** 1. row *************************** Table: t3Create Table: CREATE TABLE `t3` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, `age` int(11) NOT NULL, `info` varchar(255) DEFAULT NULL, KEY `mulitiidx` (`id`,`name`,`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from t3 where id = 1 and name = 'joe' G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ref possible_keys: mulitiidx key: mulitiidx key_len: 124 ref: const,const rows: 1 filtered: 100.00 Extra: Using index condition1 row in set, 1 warning (0.06 sec) 组合索引起到几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵循从"最左前缀":利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。 例如，这里由id，name，age三个字段构成的索引，索引行中按id/nam/age的顺序粗放，索引可以搜索字段组合：(id，name，age)、(id、name)或者id。如果列不构成索引最左面的前缀，MySQL不能使用局部索引，如(age)或(name，age)组合则不能使用索引查询。查询id和name字段时，使用了multiidx的索引，如果查询(name，age)组合或者单独查询name和age字段，索引为null。 ⑤创建全文索引 fulltext全文索引可以用于全文搜索。只有MyISAM存储引擎支持fulltext索引，并且只为char、varchar和text列创建索引。索引总是对整列进行，不支持局部(前缀)索引。 【例5】创建表t4，在表中的info字段上建立全文索引，SQL语句如下： mysql> create table t4 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> fulltext index fulltxtidx(info) -> )engine=MyISAM;Query OK, 0 rows affected (0.08 sec)mysql> show create table t4 G*************************** 1. row *************************** Table: t4Create Table: CREATE TABLE `t4` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, `age` int(11) NOT NULL, `info` varchar(255) DEFAULT NULL, FULLTEXT KEY `fulltxtidx` (`info`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec) 全文索引非常适合于大型数据集，对于小的数据集，它的用处比较小。 6.创建空间索引 空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。 【例6】创建表t5，在空间类型为geometry的字段上创建空间索引，SQL语句如下： mysql> create table t5 -> ( g geometry not null,spatial index spatidx(g) ) ENGINE=MyISAM;Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> show create table t5 G*************************** 1. row *************************** Table: t5Create Table: CREATE TABLE `t5` ( `g` geometry NOT NULL, SPATIAL KEY `spatidx` (`g`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.05 sec) 可以看到，t5表的g字段上创建了名称为spatIdx的空间索引。创建时指定空间类型字段值的非空约束，并且表的存储引擎为MyISAM。 (2)在已经存在的表上创建索引 在已经存在的表中创建索引，可以使用alter table语句或者create index 语句。 1.使用alter table语句创建索引 基本语法： alter table table_name add [unique|fulltext|spatial] [index|key][index_name] (col_name[length],...) [asc |dec] 【例7】在book表中的bookname字段上建立名为BkNameIdx的普通索引。 添加索引之前，show index语句查看指定表中创建的索引： mysql> show index from book G*************************** 1. row *************************** Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.10 sec) 其中，各个主要参数的含义为; table表示创建索引的表。 Non_unique表示索引非唯一，1表示非唯一，0表示唯一。 Key_name表示索引的名称。 Seq_in_index表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序。 Column_name表示定义索引的列字段。 Sub_part表示索引的长度。 Null表示该字段是否能为空值。 Index_type表示索引类型。 可以看到book表中已经存在一个索引，即year_publication索引，该索引为非唯一索引，下面使用alter table 在bookname字段上添加索引，SQL语句如下： mysql> alter table book add index bknameidx( bookname(30) );Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book G*************************** 1. row *************************** Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 2. row *************************** Table: book Non_unique: 1 Key_name: bknameidx Seq_in_index: 1 Column_name: bookname Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL2 rows in set (0.05 sec) 可以看到表中有了两个索引，另一个为通过alter table语句添加的名称为bknameidx的索引，该索引为非唯一索引，长度为30。 【例8】在book表的bookid字段上建立名称为uniqididx的唯一索引，SQL语句如下： mysql> alter table book add unique index uniqididx(bookid);Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book G1...2...*************************** 3. row *************************** Table: book Non_unique: 1 Key_name: bknameidx Seq_in_index: 1 Column_name: bookname Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL3 rows in set (0.01 sec) 可以看到，Non_unique的属性值为0，表示名称为Uniqididx的索引为唯一索引，创建唯一索引成功。 【例9】在book表的comment字段上建立单列索引，SQL语句如下： mysql> alter table book add index bkcmtidx ( comment(50) );Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book G1...2...3...*************************** 4. row *************************** Table: book Non_unique: 1 Key_name: bkcmtidx Seq_in_index: 1 Column_name: comment Collation: A Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL4 rows in set (0.01 sec) 可以看到，语句执行之后再book表的comment字段上建立了名称为bkcmtidx的索引，长度为50，在查询时，只需要检索前50个字符。 【例10】在book表的authors和info字段上建立组合索引，SQL语句如下： mysql> alter table book add index bkauandinfoidx (authors(30),info(50) );Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book G1...2...3...4...*************************** 5. row *************************** Table: book Non_unique: 1 Key_name: bkauandinfoidx Seq_in_index: 1 Column_name: authors Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 6. row *************************** Table: book Non_unique: 1 Key_name: bkauandinfoidx Seq_in_index: 2 Column_name: info Collation: A Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL6 rows in set (0.06 sec) 可以看到名称为bkauandinfoidx的索引由两个字段组成，authors字段长度为30，在组合索引中的序号为1，该字段不允许空值null；info字段长度为50，在组合索引中的序号为2，该字段可以为空值null。 【例11】创建表t6，在t6表上使用alter table创建全文索引，SQL语句如下： mysql> create table t6 -> ( -> id int not null, -> info char(255) -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t6 add fulltext index infofiidx( info );Query OK, 0 rows affected (0.13 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from t6 G*************************** 1. row *************************** Table: t6 Non_unique: 1 Key_name: infofiidx Seq_in_index: 1 Column_name: info Collati （编辑：大连站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐