MySQL创建索引

最新推荐文章于 2025-09-11 20:53:34 发布
转载 最新推荐文章于 2025-09-11 20:53:34 发布 · 121 阅读 · 0 ·
CC 4.0 BY-SA版权
原文链接：https://my.oschina.net/xinxingegeya/blog/308361
文章标签：

#数据库 #python

2019独角兽企业重金招聘Python工程师标准>>> hot3.png

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=========================

转载于:https://my.oschina.net/xinxingegeya/blog/308361

mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
mysql 创建索引 终止_创建索引及撤销
weixin_39531229的博客
01-28 1805
selectcolumn1,column2,.fromtable_namewith(index(index_name))wherecondition例如：select * from studentinfo with (Name_Index))运行结果如下：(说明：对于查询表中的所有记录信息，使用索引是毫无意义的。)使用order by关键字同样可以对查找结果进行排序。select * from s...
关于mysql中的key和index
Skyman的部落格
05-22 2278
1. 官方文档：http://dev.mysql.com/doc/refman/5.5/en/create-table.html KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a col
Mysql 创建索引
热门推荐
艾利克斯冰的博客
02-23 1万+
数据库建立索引常用的规则如下： 1、表的主键、外键必须有索引；2、数据量超过300的表应该有索引； 3、经常与其他表进行连接的表，在连接字段上应该建立索引； 4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 5、索引应该建在选择性高的字段上； 6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替： A、正确选择复合索引中的主列字段，一般是选择性较好的字段； B、复合索引的几个字段是否经常同...
MYSQL 创建索引
xiaochuan_bsj的博客
12-29 2837
MYSQL中创建索引
MySql 创建索引
李白
07-30 1270
先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦。...
mysql 创建索引语句
m0_67390788的博客
08-30 1万+
删除索引的语法： DROP INDEX [ indexName ] ON mytable;1.PRIMARYKEY（主键索引）4.FULLTEXT(全文索引)2.UNIQUE(唯一索引)3.INDEX(普通索引)5.多列索引(组合索引)
mysql 创建索引规范
qq_34253002的博客
04-26 2095
字段选取规范 命名规范
MySQL创建索引失败
weixin_43573186的博客
03-27 1864
MySQL创建索引失败 报错信息： 原因：将数据库表中某个字段设为索引时，提示当前字段的长度过长，为3072个字节； 经过查询发现： 在mysql5.6中索引列的最大长度为767个字节 而对于utf-8编码，一个字符占3个字节，所以对于varchar类型字段，我们如果需要创建索引，类型大小就必须设置为小等于varchar(255)长度； ...
MySQL创建索引与索引失效场景
我叫晨曦啊的博客
09-16 9560
MySQL创建索引及其索引失效场景
MYSQL创建索引全过程
03-28
代码复制粘贴 改下里面的参数 就可以用 而且参数 也有说明 简单易懂
MySQL创建索引，查看以及删除
04-02
一、创建索引 在MySQL中，索引主要分为以下几种类型： 1. 主键索引（Primary Key Index）：每个表只能有一个主键，不允许有重复值，且不能为NULL。 2. 唯一索引（Unique Index）：与主键类似，唯一索引也确保列中...
MySQL 创建索引(Create Index)的方法和语法结构及例子
09-11
MySQL 创建索引(Create Index)的方法和语法结构及例子 MySQL 创建索引是指在 MySQL 数据库中创建一个索引，以提高查询效率。索引是一种数据结构，它能够快速地定位特定的数据，使查询变得更加高效。下面将详细介绍 ...
Redis哨兵
2502_92141759的博客
09-10 682
哨兵的结构如图：：Sentinel 会不断检查您的master和slave是否按预期工作：如果master故障，Sentinel会将一个slave提升为master。当故障实例恢复后也以新的master为主：Sentinel充当Redis客户端的服务发现来源，当集群发生故障转移时，会将最新信息推送给Redis的客户端。
SAP HANA Scale-out 03：Performance Guide
SAP攻城狮
09-10 1026
HEX EngineThe SAP HANA Execution Engine (HEX)是一个查询执行引擎，从长远来看将取代其他SAP HANA引擎，如连接引擎和OLAP引擎.所有功能都整合在HEX中,从而消除不同引擎之间的物化过程。HEX引擎通过在准备阶段创建适当的SQL计划来连接SQL层和存储层。HEX引擎中的数据是流式处理的，因此可以快速获取第一批结果行，而无需等待所有结果行都被处理。HEX引擎中的操作符是管道化的，改进了并行化并可以减少内存消耗。
为什么要显示调用析构函数
最新发布
python_girl9475的博客
09-11 68
如果类内存在手动申请的资源、数据库连接、文件句柄、网络连接，则无法释放，会造成资源泄露。
计算机毕设 java 高校饭堂点餐系统 基于微信小程序 + SSM 的高校餐饮服务平台 Java+MySQL 的点餐与运营系统
毕设源码伍学长
09-10 861
在高校饭堂就餐需求增长的背景下，传统点餐依赖窗口排队、效率低，存在高峰期拥堵、菜品信息不透明等问题，难以满足师生便捷就餐需求。采用 Java 技术、SSM 框架、Uni-weixin 技术及 MySQL 数据库，开发高校饭堂点餐系统（微信小程序端 + 服务端），整合菜品展示、在线点餐、订单跟踪等功能，支持管理员、商家、用户三类角色协同，打造专业化餐饮服务平台。该系统既能帮助管理员监管平台内容，商家高效管理菜品与订单，又能让用户便捷查询菜品、完成点餐，提升高校饭堂服务效率与用户体验。
PostgreSQL打印实时查询语句（监控PostgreSQL监控）（未成功）
Dontla的博客
09-08 614
定期执行以下 SQL 语句（可通过脚本或。
知识图谱——图数据库与项目构建
renjt01的博客
09-10 316
知识图谱——图数据库与项目构建 一、图数据库 1.Neo4j 2.neo4j使用 3.创建节点 4.创建关系 5.删除所有节点和关系 6.效果展示 二、知识问答项目构建 1、前端构建 2、大模型进行问题分类和命名实体识别 3、python操作neo4j数据库 4、后端构建
MySQL 创建索引
08-30
下面是MySQL创建索引的一些常见方法和注意事项： 一、创建索引的方法： 1. 在创建表时，可以在某些列上使用关键字"INDEX"、"PRIMARY KEY"或"UNIQUE"来创建索引。 2. 在已创建的表上使用"CREATE INDEX"语句来添加...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值