MySQL 主要索引类型有如下几种:
1.主键索引2.唯一索引3.普通索引4.空间索引5.全文索引假设有如下一张表
CREATETABLE`t1`(`id`bigintunsignedNOTNULLAUTO_INCREMENT,`u1`intunsignedNOTNULLDEFAULT0,`u2`intunsignedNOTNULLDEFAULT0,`u3`varchar(20)NOTNULLDEFAULT,`u4`varchar(35)NOTNULLDEFAULT, PRIMARYKEY(`id`) )ENGINE=InnoDB聚簇索引
索引与数据存放在一起,找到索引的同时也找到了数据;聚簇索引具有唯一性,一张表只有一个聚簇索引。聚簇索引默认是主键,如果表中没有定义主键,InnoDB 会选择一个非空唯一索引代替。如果没有,InnoDB 会定义一个隐藏的_rowid列来作为聚簇索引。
非聚簇索引
索引与数据分开存放,索引结构的叶子节点指向了数据的对应行。
主键索引
查看创建的索引关键词PRIMARYKEY(`id`)唯一索引
索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一创建唯一索引mysql> alter table t1 add unique idx_u1(`u1`);查看创建的索引关键词UNIQUE KEY`idx_u1`(`u1`)单列索引
即索引创建在单个列上创建单列索引mysql> alter table t1 addindexidx_u1(`u1`);查看创建的索引关键词KEY`idx_u1`(`u1`)多列索引
创建多列索引时记得遵循最左匹配原则,把区分度较高的字段放前面创建多列索引mysql> alter table t1 addindexidx_u1_u2(`u1`,`u2`);查看创建的多列索引关键词KEY`idx_u1_u2`(`u1`,`u2`)前缀索引
即当字段的长度超过索引限制,可以为字段的部分前缀创建索引创建前缀索引,取字符串的前4个字符mysql> alter table t1 addindexidx_u3(`u3`(4));查看创建的索引关键词KEY`idx_u3`(`u3`(4))倒序索引
8.0 版本出的新功能,以往创建倒叙索引可以创建成功,但实际上仍然是顺序的5.7 创建倒序索引mysql> select u1 from t1 limit10; +----+| u1 |+----+| 12 || 23 || 12 || 34 |+----+10rowsinset (0.00sec)创建倒序索引mysql> alter table t1 add index idx_u3(u3 desc);实际查出来还是顺序mysql> select u1 from t1 limit10; +----+| u1 |+----+| 12 || 12 || 12 || 12 |+----+8.0 创建倒序索引mysql> select u1 from t1 limit5; +----+| u1 |+----+| 12 || 23 || 12 || 34 |+----+5rowsinset (0.00sec)添加索引mysql> alter table t1 add index idx_u1(u1 desc);查询倒序索引成功mysql> select u1 from t1 limit5; +----+| u1 |+----+| 74 || 74 || 74 || 74 |+----+5rowsinset (0.00sec)函数索引
创建函数索引mysql> alter table t1 addindexidx_abs_u2 ((ABS(`u2`)));查看创建的索引关键词KEY`idx_abs_u2`((abs(`u2`)))表达式索引
创建表达式索引mysql> alter table t1 addindexidx_u1u2 ((u1 + u2));查看创建的索引关键词KEY`idx_u1u2`(((`u1`+`u2`)))不可见索引
设置不可见(invisible),修改为可见(visible)mysql> ALTER TABLE t1 ALTER INDEX idx_u1 INVISIBLE;查看不可见索引关键词KEY `idx_u1` (`u1` DESC)/*!80000 INVISIBLE */解析查询时时全表扫描mysql> explain select * from t1 order by u1 desc limit3; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+ |1| SIMPLE | t1 |NULL| ALL |NULL|NULL|NULL|NULL|50184|100.00| Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+1row in set,1warning (0.01sec)执行SQL时即便force index也不可用,提示不存在mysql> explain select * from t1 force index(idx_u1) order by u1 desc limit3; ERROR1176(42000): Keyidx_u1doesnt exist in tablet1空间索引
空间索引是对空间数据类型的字段建立的索引,MYSQL使用SPATIAL关键字进行扩展,使其能够在空间数据类型的语法上创建空间索引。
建表CREATE TABLE`gis_position`(`id`intNOT NULL,`gis`geometry NOT NULL COMMENT空间位置信息,`geohash`varchar(20) GENERATED ALWAYS AS (st_geohash(`gis`,12)) VIRTUAL, primary key(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=空间位置信息;创建空间索引mysql> alter table gis_position add SPATIAL KEY`idx_gis`(`gis`) ;查看索引关键信息SPATIAL KEY`idx_gis`(`gis`)全文索引
创建全文索引mysql> alter table t1 add fulltextindexidx_fulltext_u4(`u4`) with parser ngram;查看索引关键信息FULLTEXT KEY`idx_fulltext_u4`(`u4`) /*!50100WITH PARSER`ngram`*/网站声明:文章内容来源于网络,本站不拥有所有权,请认真核实,谨慎使用,本站不承担相关法律责任。