知乎 上海做网站的公司,新手机发布,北京工程建设交易网,安徽建海建设工程有限公司网站要想使用索引提高数据表的访问速度#xff0c;首先要创建一个常引。创建索引的方式有三种#xff0c;具体如下。 创建表的时候可以直接创建索引#xff0c;这种方式最简单、方便#xff0c;其基本的语法格式如下所示:
CREATE TABLE 表名 (字段名 数据类型 [完整性约束条件… 要想使用索引提高数据表的访问速度首先要创建一个常引。创建索引的方式有三种具体如下。 创建表的时候可以直接创建索引这种方式最简单、方便其基本的语法格式如下所示:
CREATE TABLE 表名 (字段名 数据类型 [完整性约束条件],字段名 数据类型[完整性约束条件],...字段名 数据类型[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY[别名](字段名1[(长度)][ASC|DESC]);
关于上述语法的相关解释具体如下。 (1)UNIQUE:可选参数表示唯一索引。 (2)FULLTEXT:可选参数表示全文索引。 (3)SPATIAL:可选参数表示空间索引。 (4)INDEX和 KEY:用来表示字段的索引二者选一即可。 (5)别名:可选参数表示创建的索引的名称。 (6)字段名1:指定索引对应字段的名称。 (7)长度:可选参数用于表示索引的长度。 (8)ASC 和 DESC:可选参数其中ASC 表示升序排列DESC 表示降序排列。 为了帮助读者更好地了解如何在创建表的时候创建索引接下来通过具体的案例分别对 MySQL 中的6种索引类型进行讲解具体如下:
1)创建普通索引 例如在 t1 表中 id 字段上建立索引SQL 语句如下:
mysql create table t1(id INT,- name VARCHAR(20),- score FLOAT,- INDEX (id)- );
Query OK, 0 rows affected (0.03 sec) 上述 SQL语句执行后使用 SHOW.CREATE TABLE 语句查看表的结构结果如下所示:
mysql show create table t1\G
*************************** 1. row ***************************Table: t1
Create Table: CREATE TABLE t1 (id int DEFAULT NULL,name varchar(20) DEFAULT NULL,score float DEFAULT NULL,KEY id (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)从上述结果可以看出id字段上已经创建了一个名称为id 的索引。为了查看索引是否被使用可以使用 EXPLAIN 语句进行查看SQL代码如下:
EXPLAIN SELECT * FROM t1 WHERE id1 \G 执行结果如下所示:
mysql EXPLAIN SELECT * FROM t1 WHERE id1 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t1partitions: NULLtype: ref
possible_keys: idkey: idkey_len: 5ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.01 sec) 从上述执行结果可以看出possible_keys 和 key 的值都为 id说明id 索引已经存在并且已经开始被使用了。
2)创建唯一性索引 例如创建一个表名为 t2 的表在表中的id 字段上建立索引名为 unique_id 的唯一性索引并且按照升序排列SQL语句如下:
mysql create table t2(id INT NOT NULL,- name VARCHAR(20) NOT NULL,- score FLOAT,- UNIQUE INDEX unique_id(id ASC)- );
Query OK, 0 rows affected (0.02 sec) 上述 SQL 语句执行后使用 SHOW CREATE TABLE 语句查看表的结构结果如下所示:
mysql show create table t2\G
*************************** 1. row ***************************Table: t2
Create Table: CREATE TABLE t2 (id int NOT NULL,name varchar(20) NOT NULL,score float DEFAULT NULL,UNIQUE KEY unique_id (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.01 sec) 从上述结果可以看出id 字段上已经建立了一个名称为 unique_id 的唯一性索引。
3)创建全文索引 例如创建一个表名为 t3 的表在表中的 name 字段上建立索引名为 fulltextname 的全文索引SQL语句如下:
mysql create table t3(id INT NOT NULL,- name VARCHAR(20) NOT NULL,- score FLOAT,- FULLTEXT INDEX fulltext_name(name)- )ENGINEMyISAM;
Query OK, 0 rows affected (0.01 sec) 上述 SQL语句执行后使用 SHOW CREATE TABLE 语句查看表的结构结果如下所示:
mysql show create table t3\G
*************************** 1. row ***************************Table: t3
Create Table: CREATE TABLE t3 (id int NOT NULL,name varchar(20) NOT NULL,score float DEFAULT NULL,FULLTEXT KEY fulltext_name (name)
) ENGINEMyISAM DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec) 从上述结果可以看出name字段上已经建立了一个名为 fulltext_name 的全文索引.需要注意的是由于目前只有 MyISAM 存储引擎支持全文索引InnoDB 存储引擎还不支持全文索引因此在建立全文索引时一定要注意表存储引擎的类型对于经常需要索引的字符串、文字数据等信息可以考虑存储到 MyISAM 存储引擎的表中。
4)创建单列索引 例如创建一个表名为 t4 的表在表中的 name 字段上建立索引名为 single.name的单列索引SQL语句如下:
mysql create table t4(id INT NOT NULL,- name VARCHAR(20) NOT NULL,- score FLOAT,- INDEX single_name(name(20))- );
Query OK, 0 rows affected (0.01 sec) 上述 SQL语甸执行后使用SHOW CREATE TABLE 语句登有衣的结构结果如下所示:
mysql show create table t4\G
*************************** 1. row ***************************Table: t4
Create Table: CREATE TABLE t4 (id int NOT NULL,name varchar(20) NOT NULL,score float DEFAULT NULL,KEY single_name (name)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec) 从上述结果可以看出name字段上已经建立了一个名称为 single_name 的单列索引并且索引的长度为 20。
5)创建多列索引 例如创建一个表名为 t5 的表在表中的 id 和 name 字段上建立索引名为multi 的多列索引SQL语句如下:
mysql create table t5(id INT NOT NULL,- name VARCHAR(20) NOT NULL,- score FLOAT,- INDEX multi(id,name(20))- );
Query OK, 0 rows affected (0.02 sec) 上述 SQL语句执行后使用 SHOW CREATE TABLE 语句查看表的结构结果如下所示:
mysql show create table t5\G
*************************** 1. row ***************************Table: t5
Create Table: CREATE TABLE t5 (id int NOT NULL,name varchar(20) NOT NULL,score float DEFAULT NULL,KEY multi (id,name)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec) 从上述结果可以看出id 和 name 字段上已经建立了一个名为 multi 的多列索引需要注意的是在多列索引中只有査询条件中使用了这些字段中的第一个字段时多列索引才会被使用。为了验证这个说法是否正确将id字段作为查询条件通过 EXPLAD语句查看索引的使用情况SQL执行结果如下所示:
mysql EXPLAIN SELECT * FROM t5 WHERE id1 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t5partitions: NULLtype: ref
possible_keys: multikey: multikey_len: 4ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.01 sec) 从上述执行结果可以看出possible_keys 和 key 的值都为 multi说明 multi 索引已经存在并且已经开始被使用了。但是如果只使用 name 字段作为查询条件SQL 执行结果如下所示:
mysql EXPLAIN SELECT * FROM t5 WHERE nameMike \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t5partitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1filtered: 100.00Extra: Using where
1 row in set, 1 warning (0.01 sec) 从上述执行结果可以看出possible_keys 和 key 的值都为 NULL说明 multi 索引还没有被使用。
6)创建空间索引 例如创建一个表名为 t6 的表在空间类型为 GEOMETRY 的字段上创建空间索引SQL 语句如下:
mysql create table t6(id INT,- space GEOMETRY NOT NULL,- SPATIAL INDEX sp(space)- )ENGINEMyISAM;
Query OK, 0 rows affected, 1 warning (0.01 sec) 上述 SQL语句执行后使用 SHOW CREATE TABLE 语句查看表的结构结果如下所示:
mysql show create table t6\G
*************************** 1. row ***************************Table: t6
Create Table: CREATE TABLE t6 (id int DEFAULT NULL,space geometry NOT NULL,SPATIAL KEY sp (space)
) ENGINEMyISAM DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec) 从上述结果可以看出t6 表中的 space 字段上已经建立了一个名称为 sp 的空间索引。需要注意的是创建空间索引时所在字段的值不能为空值并且表的存储引擎为MyISAM。
