文档章节

MySQL创建索引

秋风醉了
 秋风醉了
发布于 2014/08/30 20:52
字数 1271
阅读 115
收藏 0

MySQL创建索引

学习mysql,官方文档是最全面的最权威的。

以下英文资料引用自官方文档:

How MySQL Uses Indexes

Indexes are used to find rows with specific(具体) column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. If a table has 1,000 rows, this is at least 100 times faster than reading sequentially.

Most MySQL indexes (PRIMARY KEY,UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that(特殊的是) indexes on spatial(空间) data types use R-trees(R树), and that MEMORY tables also support hash indexes.


创建主键列(PRIMARY KEY)

CREATE TABLE people_1 (
id int not null AUTO_INCREMENT,
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m','f') not null,
PRIMARY KEY (id)
)

查看该表索引

mysql> describe people_1;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| last_name  | varchar(50)   | NO   |     | NULL    |                |
| first_name | varchar(50)   | NO   |     | NULL    |                |
| dob        | date          | NO   |     | NULL    |                |
| gender     | enum('m','f') | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
5 rows in set

mysql> show index from people_1;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people_1 |          0 | PRIMARY  |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

mysql> show keys from people_1;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people_1 |          0 | PRIMARY  |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

现在在first_name,last_name上创建索引,然后查看索引

mysql> create index name_index on people_1(first_name,last_name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from people_1;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people_1 |          0 | PRIMARY    |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| people_1 |          1 | name_index |            1 | first_name  | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| people_1 |          1 | name_index |            2 | last_name   | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set

可以看到表中有三个索引。。


创建表的外键列(FOREIGN KEY)

mysql> CREATE TABLE parent(
id int not null,
primary key (id)
);

CREATE TABLE child(
id int not null,
parent_id int not null,
primary key (id),
foreign key (parent_id) references parent(id)
);
Query OK, 0 rows affected

Query OK, 0 rows affected

mysql>

表child中持有表parent的主键

查看child表的索引

mysql> show index from child;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| child |          0 | PRIMARY   |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| child |          1 | parent_id |            1 | parent_id   | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set


创建唯一索引(UNIQUE INDEX || UNIQUE KEY)

mysql> CREATE TABLE people_2 (
id int not null,
name varchar(50) not null,
dob date not null,
gender enum('m','f') not null,
primary key(id)
);
Query OK, 0 rows affected

mysql> describe people_2;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(11)       | NO   | PRI | NULL    |       |
| name   | varchar(50)   | NO   |     | NULL    |       |
| dob    | date          | NO   |     | NULL    |       |
| gender | enum('m','f') | NO   |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
4 rows in set

mysql> show index from people_2;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people_2 |          0 | PRIMARY  |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

创建表的唯一索引,索引的名字叫name_unique,在name列上创建唯一索引

mysql> create unique index name_unique on people_2 (name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from people_2;
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people_2 |          0 | PRIMARY     |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| people_2 |          0 | name_unique |            1 | name        | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set


创建普通索引(INDEX || KEY)

mysql> CREATE TABLE people_3 (
id int not null,
name varchar(50) not null,
primary key(id),
index (name)
);
Query OK, 0 rows affected

mysql> describe people_3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set

查看索引

mysql> show index from people_3;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people_3 |          0 | PRIMARY  |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| people_3 |          1 | name     |            1 | name        | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set

Non_unique = 1 表示可以包含重复值。


创建全文索引(FULLTEXT)

These are used for full-text searches. Only the InnoDB and MyISAM storage engines support FULLTEXT indexes and only for CHAR, VARCHAR, and TEXT columns. 

mysql> CREATE TABLE people_4 (
id int not null,
name varchar(50) not null,
descriptioin varchar(255) not null,
primary key(id)
);
Query OK, 0 rows affected

mysql> describe people_4;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | NULL    |       |
| name         | varchar(50)  | NO   |     | NULL    |       |
| descriptioin | varchar(255) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set

mysql> show index from people_4;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people_4 |          0 | PRIMARY  |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

在description列上创建表的全文索引,然后查看索引

mysql> create fulltext index desc_index on people_4 (descriptioin);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 1

mysql> show index from people_4;
+----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people_4 |          0 | PRIMARY    |            1 | id           | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| people_4 |          1 | desc_index |            1 | descriptioin | NULL      |           0 | NULL     | NULL   |      | FULLTEXT   |         |               |
+----------+------------+------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set

从上面可以看到,目前为止,索引的存储类型有

Index_type
  • BTREE     

  • FULLTEXT   


创建索引时指定前缀

mysql> CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
Query OK, 0 rows affected

mysql> describe test;
+----------+------+------+-----+---------+-------+
| Field    | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| blob_col | blob | YES  | MUL | NULL    |       |
+----------+------+------+-----+---------+-------+
1 row in set

查看索引

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          1 | blob_col |            1 | blob_col    | A         |           0 |       10 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set

With col_name(N) syntax in an index specification, you can create an index that uses only the first N characters of a string column. Indexing only a prefix of column values in this way can make the index file much smaller. When you index a BLOB or TEXT column, you must specify a prefix length for the index. 

注意:Prefix limits are measured in bytes, while the prefix length in CREATE TABLE statements is interpreted as number of characters. Take this into account when specifying a prefix length for a column that uses a multibyte character set.

==========================END=========================

© 著作权归作者所有

秋风醉了
粉丝 250
博文 536
码字总数 408434
作品 0
朝阳
程序员
私信 提问
SQL基础-DDL数据定义语言

SQL数据定义语言-DDL语句 DDL语句部分使我们有能力创建或删除表格。我们也可以定义索引(键),规定表之间的链接,以及施加表间的约束。 语句 释义 CREATE DATABASE 创建新数据库 ALTER DATA...

ZHAO_JH
2018/08/10
0
0
MySQL Index详解

①MySQL Index 一、SHOW INDEX会返回以下字段 1、Table 表的名称。 2、 Non_unique 如果索引不能包括重复词,则为0,如果可以则为1。 3、 Key_name 索引的名称 4、 Seqinindex 索引中的列序列号...

雾妄
2016/12/22
10
0
MySQL专题9之MySQL索引、MySQL临时表、MySQL复制表

1、MySQL索引 - MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 - 打个比方,如果合理的设计并使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索...

极客微信条
2017/11/19
0
0
MYSQL索引分析和优化设计方案

一、什么是索引? 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时 MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求...

有些服务器
2015/11/02
0
0
MySQL索引类型一览 让MySQL高效运行起来

本文介绍了七种MySQL索引类型。在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引,可以令MySQL的查询和运行更加高效。 索引是快速搜索的关键。MySQL索引的建立对于MySQL...

5W1H-
2013/06/13
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Java的战争

本文来自微信公众号: 金捷幡(ID:jin-jiefan) ,作者:金捷幡,封面:拉里·埃里森(东方IC) 2019年5月,彻底撕破脸的特朗普掀起对华为的战争,谷歌被迫吊销了华为的Android授权。开源软...

Java领航员
35分钟前
1
0
超详细的LM3414MRX/NOPB规格参数介绍就在这里

超详细的LM3414MRX/NOPB规格参数介绍就在这里 描述 LM3414和LM3414MRX/NOPB是具有1-A 60 W(1)共阳极功能的恒流降压LED驱动器。它们适用于驱动单串3-W HBLED,效率高达96%。它们可接受4.5...

不能吃肉的仙女
42分钟前
1
0
Eclipse国内镜像源配置方法

Table of Contents 我们在国内从官网下载Eclipse以及插件非常慢,那么,有没有方法变快呢? 有,那就是使用国内的公开镜像源替换官方源。 1 下载Eclipse 首先,我们看一个链接地址: http:/...

求神
今天
5
0
java 请求服务

一.第一种基于java web http 请求服务,必须用tomcat 容器启动才能对外提供服务 在maven 工程中pox 文件导入jar <dependency> <groupId>org.apache.httpcomponents</groupId> <artifactId>h......

zaolonglei
今天
5
0
HDFS工作机制——自开发分布式数据采集系统

需求描述: 在业务系统的服务器上,业务程序会不断生成业务日志(比如网站的页面访问日志) 业务日志是用log4j生成的,会不断地切出日志文件,需要定期(比如每小时)从业务服务器上的日志目...

须臾之余
今天
15
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部