一起学mysql 02.mysql 索引

最新推荐文章于 2024-07-11 20:24:07 发布

隔壁小翔

最新推荐文章于 2024-07-11 20:24:07 发布

阅读量89

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/tangwei0928/article/details/97040744

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

mysql 索引

创建索引

创建表时创建索引

创建普通索引

mysql> create table index1(id INT, name VARCHAR(20), sex BOOLEAN, INDEX index1_id (id));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table index1 \G
*************************** 1. row ***************************
       Table: index1
Create Table: CREATE TABLE `index1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `index1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> explain select * from index1 where id=1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index1
   partitions: NULL
         type: ref
possible_keys: index1_id
          key: index1_id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

创建唯一性索引

mysql> create table index2(id INT UNIQUE, name VARCHAR(20), UNIQUE INDEX index2_id (id ASC));
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show create table index2 \G
*************************** 1. row ***************************
       Table: index2
Create Table: CREATE TABLE `index2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `index2_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建全文索引

mysql> create table index3(id INT, info VARCHAR(20), FULLTEXT INDEX index3_info (info));
Query OK, 0 rows affected (0.40 sec)

mysql> show create table index3 \G
*************************** 1. row ***************************
       Table: index3
Create Table: CREATE TABLE `index3` (
  `id` int(11) DEFAULT NULL,
  `info` varchar(20) DEFAULT NULL,
  FULLTEXT KEY `index3_info` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建单列索引

mysql> create table index4(id INT, subject VARCHAR(20), INDEX index4_st (subject(10)));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table index4 \G
*************************** 1. row ***************************
       Table: index4
Create Table: CREATE TABLE `index4` (
  `id` int(11) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  KEY `index4_st` (`subject`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建多列索引

mysql> create table index5(id INT, name VARCHAR(20), sex CHAR(4),INDEX index5_ns (name,sex));
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from index5 where name='tom' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index5
   partitions: NULL
         type: ref
possible_keys: index5_ns
          key: index5_ns
      key_len: 63
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

创建空间索引

mysql> create table index6(id INT, space GEOMETRY NOT NULL, SPATIAL INDEX index6_sp (space));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table index6 \G
*************************** 1. row ***************************
       Table: index6
Create Table: CREATE TABLE `index6` (
  `id` int(11) DEFAULT NULL,
  `space` geometry NOT NULL,
  SPATIAL KEY `index6_sp` (`space`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

在已经存在的表上创建索引

创建普通索引

mysql> create table example0 (id INT, name VARCHAR(20), sex BOOLEAN);
Query OK, 0 rows affected (0.00 sec)

mysql> create index index7_id on example0(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table example0 \G
*************************** 1. row ***************************
       Table: example0
Create Table: CREATE TABLE `example0` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `index7_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建唯一性索引

mysql> desc example2;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| stu_id    | int(11) | NO   | PRI | NULL    |       |
| course_id | int(11) | NO   | PRI | NULL    |       |
| grade     | float   | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table example2 \G
*************************** 1. row ***************************
       Table: example2
Create Table: CREATE TABLE `example2` (
  `stu_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `grade` float DEFAULT NULL,
  PRIMARY KEY (`stu_id`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> create  unique  index index8_id on example2(course_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table example2 \G
*************************** 1. row ***************************
       Table: example2
Create Table: CREATE TABLE `example2` (
  `stu_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `grade` float DEFAULT NULL,
  PRIMARY KEY (`stu_id`,`course_id`),
  UNIQUE KEY `index8_id` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建全文索引

mysql> create  fulltext  index index9_info on index9(info);

创建单列索引

mysql> create index index10_addr on index10(address(4));

创建多列索引

mysql> create index index11_na on index11(name,address);

创建空间索引

mysql> create spatial index index12_line on index12(line);

用alter table语句来创建索引

创建普通索引

mysql> alter table example0 add index index13_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table example0 \G
*************************** 1. row ***************************
       Table: example0
Create Table: CREATE TABLE `example0` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `index7_id` (`id`),
  KEY `index13_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

创建唯一性索引

mysql> alter table index14 add index index14_id(course_id);

创建全文索引

mysql> alter table index15 add  fulltext index index15_id(info);

创建单列索引

mysql> alter table index16 add index index16_addr(address(4));

创建多列索引

mysql> alter table index17 add index index17_na(name,address);

创建空间索引

mysql> alter table index18 add  spatial index index18_line(line);

删除索引

mysql> show create table index1 \G
*************************** 1. row ***************************
       Table: index1
Create Table: CREATE TABLE `index1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  KEY `index1_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> drop index index1_id on index1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table index1 \G
*************************** 1. row ***************************
       Table: index1
Create Table: CREATE TABLE `index1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

隔壁小翔

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一起学mysql 02.mysql 索引

mysql 索引创建索引创建表时创建索引创建普通索引mysql> create table index1(id INT, name VARCHAR(20), sex BOOLEAN, INDEX index1_id (id));Query OK, 0 rows affected (0.00 sec)mysql> show create table index1 \G**...
复制链接

扫一扫