简介:
索引是对数据库表中一列或多列的值进行排序的一种表结构,使用索引可以提高数据库中特定数据的查询速度。索引也是一个单独的、存储在磁盘上的数据库结构,包含着对数据表里所有记录的引用指针,使用索引用于快速找出在某个或多个列中有一特定值得行。
分类:
普通索引、唯一索引、单列索引、组合索引、全文索引、空间索引
设计索引的原则:
索引的数量要适当,不是越多越好;数据量小的表最好不要使用索引,达不到索引优化的效果;根据对数据表的特定需求来设定不同的索引
1、创建普通索引
MySQL中的基本索引类型,允许定义索引的列中插入重复值和空值。
MariaDB [vincen]> 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) #创建字段为year_publication普通索引
-> );
Query OK, 0 rows affected (0.01 sec)
2、创建唯一索引
索引列的值必须唯一,但允许有空值
MariaDB [vincen]> CREATE TABLE a1
-> (
-> id INT NOT NULL,
-> name CHAR(30) NOT NULL,
-> UNIQUE INDEX UniqIdx(id) #创建字段为id的唯一索引
-> );
Query OK, 0 rows affected (0.01 sec)
3、创建单列索引
也就是一个索引,只包含单个列。一个表里允许有多个单列索引
MariaDB [vincen]> CREATE TABLE a2
-> (
-> id INT NOT NULL,
-> name CHAR(50) NULL,
-> INDEX SingleIdx(name(20)) #创建字段为name的单列索引
-> );
4、创建组合索引
在表的多个字段组合上创建的索引。使用组合索引时遵循最左前缀集合
MariaDB [vincen]> CREATE TABLE a3
-> (
-> id INT NOT NULL,
-> name CHAR(30) NOT NULL,
-> age INT NOT NULL,
-> info VARCHAR(255),
-> INDEX MultiIdx(id, name) #创建字段为id和name的组合索引
-> );
5、创建全文索引
全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。可以在CHAR,VARCHAR,TEXT类型的列上创建。要注意的是,在MySQL中只有MyISAM存储引擎支持全文索引。但MySQL默认的存储引擎是InnoDB,所以创建全文索引的时候要把存储引擎改为MyISAM.
MariaDB [vincen]> CREATE TABLE a4
-> (
-> id INT NOT NULL,
-> name CHAR(30) NOT NULL,
-> age INT NOT NULL,
-> info VARCHAR(255),
-> FULLTEXT INDEX FullTxtIdx(info) #创建字段为info的全文索引
-> )
-> ENGINE=MyISAM; #将这个数据表的存储引擎改为MyISAM
Query OK, 0 rows affected (0.02 sec)
6、创建空间索引
空间索引是对空间数据类型的字段建立的索引。(空间数据类型有GEOMETRY,POINT,LINESTRING,POLYGON)。其中,创建空间索引的列,必须即将其声明为NOT NULL,而且空间索引只能在MyISAM存储引擎中创建。
MariaDB [vincen]> CREATE TABLE a5
-> ( g GEOMETRY NOT NULL,
-> SPATIAL INDEX spatIdx(g) #创建字段为g的空间索引
-> )
-> ENGINE=MyISAM; #修改这个表的存储引擎为MyISAM