MySQL——数据库和表的根本操作（六）索引（1）创建索引①创建表的时候创建 ...

王國慶  论坛元老 | 2024-9-2 12:39:37 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1030|帖子 1030|积分 3094

        要想使用索引提高数据表的访问速率，起首要创建一个常引。创建索引的方式有三种，具体如下。
                创建表的时候可以直接创建索引，这种方式最简朴、方便，其根本的语法格式如下所示:
  1. CREATE TABLE 表名 (字段名 数据类型 [完整性约束条件],
  2.                   字段名 数据类型[完整性约束条件],
  3.                   ...
  4.                       字段名 数据类型
  5.                       [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
  6.                           [别名](字段名1[(长度)][ASC|DESC]
  7.                   );   
复制代码
关于上述语法的相关解释具体如下。
   
(1)UNIQUE:可选参数，表现唯一索引。
(2)FULLTEXT:可选参数，表现全文索引。
(3)SPATIAL:可选参数，表现空间索引。
(4)INDEX和 KEY:用来表现字段的索引，二者选一即可。
(5)别名:可选参数，表现创建的索引的名称。
(6)字段名1:指定索引对应字段的名称。
(7)长度:可选参数，用于表现索引的长度。
(8)ASC 和 DESC:可选参数，此中，ASC 表现升序排列，DESC 表现降序排列。

          为了帮助读者更好地了解怎样在创建表的时候创建索引，接下来，通过具体的案例分别对 MySQL 中的6种索引类型举行解说，具体如下:
1)创建平常索引

        例如，在 t1 表中 id 字段上创建索引，SQL 语句如下:
  1. mysql> create table t1(id INT,
  2.     -> name VARCHAR(20),
  3.     -> score FLOAT,
  4.     -> INDEX (id)
  5.     -> );
  6. Query OK, 0 rows affected (0.03 sec)
复制代码
        上述 SQL语句执行后，使用 SHOW.CREATE TABLE 语句查看表的结构，效果如下所示:
  1. mysql> show create table t1\G
  2. *************************** 1. row ***************************
  3.        Table: t1
  4. Create Table: CREATE TABLE `t1` (
  5.   `id` int DEFAULT NULL,
  6.   `name` varchar(20) DEFAULT NULL,
  7.   `score` float DEFAULT NULL,
  8.   KEY `id` (`id`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  10. 1 row in set (0.00 sec)
复制代码
        从上述效果可以看出，id字段上已经创建了一个名称为id 的索引。为了查看索引是否被使用，可以使用 EXPLAIN 语句举行查看，SQL代码如下:
  1. EXPLAIN SELECT * FROM t1 WHERE id=1 \G
复制代码
        执行效果如下所示:
  1. mysql> EXPLAIN SELECT * FROM t1 WHERE id=1 \G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t1
  6.    partitions: NULL
  7.          type: ref
  8. possible_keys: id
  9.           key: id
  10.       key_len: 5
  11.           ref: const
  12.          rows: 1
  13.      filtered: 100.00
  14.         Extra: NULL
  15. 1 row in set, 1 warning (0.01 sec)
复制代码
        从上述执行效果可以看出，possible_keys 和 key 的值都为 id，说明id 索引已经存在而且已经开始被使用了。
2)创建唯一性索引

        例如，创建一个表名为 t2 的表，在表中的id 字段上创建索引名为 unique_id 的唯一性索引，而且按照升序排列，SQL语句如下:
  1. mysql> create table t2(id INT NOT NULL,
  2.     -> name VARCHAR(20) NOT NULL,
  3.     -> score FLOAT,
  4.     -> UNIQUE INDEX unique_id(id ASC)
  5.     -> );
  6. Query OK, 0 rows affected (0.02 sec)
复制代码
        上述 SQL 语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，效果如下所示:
  1. mysql> show create table t2\G
  2. *************************** 1. row ***************************
  3.        Table: t2
  4. Create Table: CREATE TABLE `t2` (
  5.   `id` int NOT NULL,
  6.   `name` varchar(20) NOT NULL,
  7.   `score` float DEFAULT NULL,
  8.   UNIQUE KEY `unique_id` (`id`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  10. 1 row in set (0.01 sec)
复制代码
        从上述效果可以看出，id 字段上已经创建了一个名称为 unique_id 的唯一性索引。
3)创建全文索引

        例如，创建一个表名为 t3 的表，在表中的 name 字段上创建索引名为 fulltextname 的全文索引，SQL语句如下:
  1. mysql> create table t3(id INT NOT NULL,
  2.     -> name VARCHAR(20) NOT NULL,
  3.     -> score FLOAT,
  4.     -> FULLTEXT INDEX fulltext_name(name)
  5.     -> )ENGINE=MyISAM;
  6. Query OK, 0 rows affected (0.01 sec)
复制代码
        上述 SQL语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，效果如下所示:
  1. mysql> show create table t3\G
  2. *************************** 1. row ***************************
  3.        Table: t3
  4. Create Table: CREATE TABLE `t3` (
  5.   `id` int NOT NULL,
  6.   `name` varchar(20) NOT NULL,
  7.   `score` float DEFAULT NULL,
  8.   FULLTEXT KEY `fulltext_name` (`name`)
  9. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  10. 1 row in set (0.00 sec)
复制代码
        从上述效果可以看出，name字段上已经创建了一个名为 fulltext_name 的全文索引.需要留意的是，由于目前只有 MyISAM 存储引擎支持全文索引，InnoDB 存储引擎还不支持全文索引，因此，在创建全文索引时，肯定要留意表存储引擎的类型，对于常常需要索引的字符串、笔墨数据等信息，可以思量存储到 MyISAM 存储引擎的表中。
4)创建单列索引

        例如，创建一个表名为 t4 的表，在表中的 name 字段上创建索引名为 single.name的单列索引，SQL语句如下:
  1. mysql> create table t4(id INT NOT NULL,
  2.     -> name VARCHAR(20) NOT NULL,
  3.     -> score FLOAT,
  4.     -> INDEX single_name(name(20))
  5.     -> );
  6. Query OK, 0 rows affected (0.01 sec)
复制代码
        上述 SQL语甸执行后，使用SHOW CREATE TABLE 语句登有衣的结构，效果如下所示:
  1. mysql> show create table t4\G
  2. *************************** 1. row ***************************
  3.        Table: t4
  4. Create Table: CREATE TABLE `t4` (
  5.   `id` int NOT NULL,
  6.   `name` varchar(20) NOT NULL,
  7.   `score` float DEFAULT NULL,
  8.   KEY `single_name` (`name`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  10. 1 row in set (0.00 sec)
复制代码
        从上述效果可以看出，name字段上已经创建了一个名称为 single_name 的单列索引，而且索引的长度为 20。
5)创建多列索引

        例如，创建一个表名为 t5 的表，在表中的 id 和 name 字段上创建索引名为multi 的多列索引，SQL语句如下:
  1. mysql> create table t5(id INT NOT NULL,
  2.     -> name VARCHAR(20) NOT NULL,
  3.     -> score FLOAT,
  4.     -> INDEX multi(id,name(20))
  5.     -> );
  6. Query OK, 0 rows affected (0.02 sec)
复制代码
        上述 SQL语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，效果如下所示:
  1. mysql> show create table t5\G
  2. *************************** 1. row ***************************
  3.        Table: t5
  4. Create Table: CREATE TABLE `t5` (
  5.   `id` int NOT NULL,
  6.   `name` varchar(20) NOT NULL,
  7.   `score` float DEFAULT NULL,
  8.   KEY `multi` (`id`,`name`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  10. 1 row in set (0.00 sec)
复制代码
        从上述效果可以看出，id 和 name 字段上已经创建了一个名为 multi 的多列索引需要留意的是，在多列索引中，只有査询条件中使用了这些字段中的第一个字段时，多列索引才会被使用。为了验证这个说法是否正确，将id字段作为查询条件，通过 EXPLAD语句查看索引的使用环境，SQL执行效果如下所示:
  1. mysql> EXPLAIN SELECT * FROM t5 WHERE id=1 \G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t5
  6.    partitions: NULL
  7.          type: ref
  8. possible_keys: multi
  9.           key: multi
  10.       key_len: 4
  11.           ref: const
  12.          rows: 1
  13.      filtered: 100.00
  14.         Extra: NULL
  15. 1 row in set, 1 warning (0.01 sec)
复制代码
        从上述执行效果可以看出，possible_keys 和 key 的值都为 multi，说明 multi 索引已经存在，而且已经开始被使用了。但是，如果只使用 name 字段作为查询条件，SQL 执行效果如下所示:
  1. mysql> EXPLAIN SELECT * FROM t5 WHERE name='Mike' \G
  2. *************************** 1. row ***************************
  3.            id: 1
  4.   select_type: SIMPLE
  5.         table: t5
  6.    partitions: NULL
  7.          type: ALL
  8. possible_keys: NULL
  9.           key: NULL
  10.       key_len: NULL
  11.           ref: NULL
  12.          rows: 1
  13.      filtered: 100.00
  14.         Extra: Using where
  15. 1 row in set, 1 warning (0.01 sec)
复制代码
        从上述执行效果可以看出，possible_keys 和 key 的值都为 NULL，说明 multi 索引还没有被使用。
6)创建空间索引

        例如，创建一个表名为 t6 的表，在空间类型为 GEOMETRY 的字段上创建空间索引，SQL 语句如下:
  1. mysql> create table t6(id INT,
  2.     -> space GEOMETRY NOT NULL,
  3.     -> SPATIAL INDEX sp(space)
  4.     -> )ENGINE=MyISAM;
  5. Query OK, 0 rows affected, 1 warning (0.01 sec)
复制代码
        上述 SQL语句执行后，使用 SHOW CREATE TABLE 语句查看表的结构，效果如下所示:
  1. mysql> show create table t6\G
  2. *************************** 1. row ***************************
  3.        Table: t6
  4. Create Table: CREATE TABLE `t6` (
  5.   `id` int DEFAULT NULL,
  6.   `space` geometry NOT NULL,
  7.   SPATIAL KEY `sp` (`space`)
  8. ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  9. 1 row in set (0.00 sec)
复制代码
        从上述效果可以看出，t6 表中的 space 字段上已经创建了一个名称为 sp 的空间索引。需要留意的是，创建空间索引时，地点字段的值不能为空值，而且表的存储引擎为MyISAM。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

王國慶

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表