大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
下面讲讲关于MySQL索引类型分类有哪些,文字的奥妙在于贴近主题相关。所以,闲话就不谈了,我们直接看下文吧,相信看完MySQL索引类型分类有哪些这篇文章你一定会有所受益。
创新互联建站主要从事网页设计、PC网站建设(电脑版网站建设)、wap网站建设(手机版网站建设)、响应式网站、程序开发、网站优化、微网站、微信平台小程序开发等,凭借多年来在互联网的打拼,我们在互联网网站建设行业积累了丰富的成都网站制作、成都网站建设、网站设计、网络营销经验,集策划、开发、设计、营销、管理等多方位专业化运作于一体。
普通索引:是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值。
唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
主键索引:是一种特殊的唯一索引,不允许有空值。
单列索引:即一个索引只包含单个列,一个表可以有多个单列索引;
组合索引:指在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合。
全文索引类型为FULLTEXT,在定义索引的列上支持值得全文查找,允许在这些索引列
中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL 5.7.xx之前只有MyISAM存储引擎支持全文索引。
空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有4中,分别是:
geometry、point、linstring和polygon 。MySQL使用SPATIAL关键字进行扩展,使得能够用于创建空间索引的列,必须将其声明为NOT NULL,同样,在MySQL 5.7.xx之前,空间索引只能在存储引擎为MyISAM的表中创建。
- 创建索引并非是越多越好,一个表中如果有大量的索引,不仅占用磁盘空间,而且会影响
- insert、delete、update等语句的性能。因为当表中的数据更改时,索引也会进行调整和更新;
- 数据量小得表最好不要创建索引,由于数据较少,查询花费的时间可能比遍历索引的时间还要长;
- 避免对经常更新的数据创建索引。而对经常用于查询的字段应该创建索引;
- 在条件表达式中经常用到的不同值较多的列创建索引;
- 当唯一性是某种数据本身的特征时,我们创建唯一性索引;
- 在频繁进行排序或分组的列上建立索引,如果排序的列有多个,可以创建组合索引;
mysql> create table book -> ( -> bookid int, -> bookname varchar(255), -> authors varchar(255), -> info varchar(255), -> comment varchar(255), -> year_publication year, -> index(year_publication) -> ); mysql> show create table book\G *************************** 1. row *************************** Table: book Create Table: CREATE TABLE `book` ( `bookid` int(11) DEFAULT NULL, `bookname` varchar(255) DEFAULT NULL, `authors` varchar(255) DEFAULT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) DEFAULT NULL, KEY `year_publication` (`year_publication`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
使用explain判断索引是否正在被使用,如下:
mysql> explain select * from book where year_publication=1999\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: book partitions: NULL type: ref possible_keys: year_publication key: year_publication key_len: 2 ref: const rows: 1 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec)
唯一索引主要原因是减少查询索引列操作的执行时间。尤其是对比比较庞大的数据表。与普通索引类似,不同点在于:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
mysql> create table t1( -> id int not null, -> name char(30), -> unique index Uniqidx(id) -> ); mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `name` char(30) DEFAULT NULL, UNIQUE KEY `Uniqidx` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
单列索引:是在数据表中的某一字段上创建的索引,一个表中可以创建多个单列索引。
mysql> create table t2 -> ( -> id int not null, -> name char(50) null, -> index singleidx(name) -> ); mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` char(50) DEFAULT NULL, KEY `singleidx` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
组合索引:是在多个字段上创建一个索引。遵循最左前缀原则。最左前缀 索引最左边的列来匹配行。
mysql> create table t3 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> index multiidx(id,name,age) -> ); mysql> show create table t3\G *************************** 1. row *************************** Table: t3 Create 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 `multiidx` (`id`,`name`,`age`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
注:组合索引可以起几个索引的作用,但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀:利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
全文索引:FULLTEXT,可以用于全文搜索,支持为CHAR\VARCHAR和TEXT 列。索引总是对整个列进行,不支持局部索引,适合大型数据的表创建。
mysql> create table test8 ( id int not null, title varchar(255), body varchar(255),fulltext index(body) ) ENGINE=MyISAM; mysql> show create table t4\G *************************** 1. row *************************** Table: t4 Create 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 `fullidx` (`info`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> insert into test8 values(2,'数据库','WORDS OF WISDOM: Like they say in Asia, nobody should use a fork. Tradition evven dictates to “chop” all your forks and “stick” to the origiinal. '); Query OK, 1 row affected (0.00 sec) mysql> insert into test8 values(1,'数据库','In MySQL 8.0.17, we made an observation in the well-known TPC-H benchmark for one particular query'); Query OK, 1 row affected (0.00 sec) mysql> explain select * from test8 where match(body) against('MySQL')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test8 partitions: NULL type: fulltext possible_keys: body key: body key_len: 0 ref: const rows: 1 filtered: 100.00 Extra: Using where; Ft_hints: sorted 1 row in set, 1 warning (0.00 sec)
查看到的数据如下:
注:在上面创建全文索引的方式中,可以实现英文的全文索引,每个单词以空格分隔来匹配,若想实现中文的全文索引,那么需要在创建表的同时,加上“with parser ngram”来带上中文解析器。
中文的全文索引创建如下:
mysql> create table test10 -> ( -> id int not null, -> title varchar(50), -> info text, -> fulltext index(title,info) with parser ngram); mysql> insert into test10 values(1,'测试','主办方介绍,本次大展将更加突出互动性强,大众参与度高的特点,让观展者不仅享受视 觉盛宴,更能体验到冰雪运动的快乐。'); Query OK, 1 row affected (0.00 sec) mysql> insert into test10 values(2,'测试2','中国摄影家协会分 党组书记、驻会副主席郑更生,中国摄影家协会副主席线云强,中国 摄影家协会顾问王玉文'); Query OK, 1 row affected (0.00 sec) mysql> explain select * from test10 where match(title,info) against('中国')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test10 partitions: NULL type: fulltext possible_keys: title key: title key_len: 0 ref: const rows: 1 filtered: 100.00 Extra: Using where; Ft_hints: sorted 1 row in set, 1 warning (0.00 sec)
上述查到的结果如下:
空间索引:必须在MyISAM类型的表中创建,且空间类型的字段必须为非空。
mysql> create table t5 -> ( -> g geometry not null, -> spatial index spaidx(g) -> )engine=myisam; mysql> show create table t5\G *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `g` geometry NOT NULL, SPATIAL KEY `spaidx` (`g`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
mysql> alter table book add unique index uniqidx(bookid);
mysql> alter table book add index bkidex(comment(50));
mysql> alter table t6 add fulltext index infofulidx(info);
mysql> alter table book add index abc(authors(20),info);
mysql> alter table t5 add spatial index spatidx(g);
mysql> CREATE TABLE book1 ( 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 ); Query OK, 0 rows affected (0.02 sec)
mysql> create index bknameidex on book1(bookname);
mysql> create index bkcmtidex on book1(comment(50));
mysql> create index bkauthandinfoidex on book1(authors,info);
mysql> create fulltext index fullidx on book(info);
mysql> create unique index uniqidx on book1(bookid);
mysql> create table t7 (g geometry not null); mysql> create spatial index spaidx on t7(g);
删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,那么整个索引将被删除。
mysql> show index from book\G
mysql> alter table book drop index uniqidx;
注:添加AUTO_INCREMENT 的约束字段的唯一索引不能删除
mysql> drop index spaidx on t7;
对于以上MySQL索引类型分类有哪些相关内容,大家还有什么不明白的地方吗?或者想要了解更多相关,可以继续关注我们的行业资讯板块。