SQL/MySQL--语句大全--索引--创建/删除/查看--命令大全/语法大全

曾经终败给现在 2022-08-28 11:47 204阅读 0赞

原文网址：[SQL/MySQL--语句大全--索引--创建/删除/查看--命令大全/语法大全\_IT利刃出鞘的博客-CSDN博客][SQL_MySQL--_--_--_--_IT_-CSDN]

# 简介 #

本文介绍索引相关的命令。包括：创建索引、删除索引、查看索引、强制索引、索引长度等。

# 创建/删除 索引 #

官网网址：[MySQL :: MySQL 8.0 Reference Manual :: 13.1 Data Definition Statements][MySQL _ MySQL 8.0 Reference Manual _ 13.1 Data Definition Statements]

<table style="width:484.5pt;"> 
 <tbody> 
 <tr> 
 <td> 索引类型 </td> 
 <td style="width:282pt;"> 创建索引的命令 </td> 
 <td style="width:173.25pt;"> 删除索引的命令 </td> 
 </tr> 
 <tr> 
 <td> 普通索引 </td> 
 <td style="width:282pt;"> （1）直接创建索引： CREATE INDEX index_name ON `table_name` (column_name(length)) &nbsp; （2）修改表结构的方式添加索引 ALTER TABLE `table_name` ADD INDEX index_name&nbsp; (column_name(length)) （3）创建表的时候同时创建索引 CREATE TABLE `table_name` ( &nbsp;&nbsp;&nbsp;&nbsp;`id` int(11) NOT NULL AUTO_INCREMENT , &nbsp;&nbsp;&nbsp;&nbsp;`title` char(255) CHARACTER NOT NULL , &nbsp;&nbsp;&nbsp;&nbsp;PRIMARY KEY (`id`), &nbsp;&nbsp;&nbsp;&nbsp;INDEX index_name (title(length)) ); </td> 
 <td style="width:173.25pt;"> DROP INDEX index_name ON `table_name`; 或：ALTER TABLE `table_name` DROP INDEX index_name </td> 
 </tr> 
 <tr> 
 <td> 唯一索引 </td> 
 <td style="width:282pt;"> （1）创建唯一索引 CREATE UNIQUE INDEX indexName ON table(column(length)) （2）修改表结构 ALTER TABLE `table_name` ADD UNIQUE INDEX indexName (column(length)) （3）创建表的时候直接指定 CREATE TABLE `table_name` ( &nbsp;&nbsp;&nbsp;&nbsp;`id` int(11) NOT NULL AUTO_INCREMENT , &nbsp;&nbsp;&nbsp;&nbsp;`title` char(255) CHARACTER NOT NULL , &nbsp;&nbsp;&nbsp;&nbsp;UNIQUE INDEX indexName (title(length)) ); </td> 
 <td style="width:173.25pt;"> DROP INDEX index_name ON `table_name`; 或:ALTER TABLE table_name DROP INDEX index_name </td> 
 </tr> 
 <tr> 
 <td> 主键索引 </td> 
 <td style="width:282pt;"> 不能用CREATE PRIMARY INDEX这种方式直接创建。 （1）修改表结构的方式添加索引 ALTER TABLE `table_name` ADD CONSTRAINT FK_ID PRIMARY KEY(字段名)&nbsp; （2）建表的时候同时创建主键索引： CREATE TABLE `table_name` ( &nbsp;&nbsp;&nbsp;&nbsp;`id` int(11) NOT NULL AUTO_INCREMENT , &nbsp;&nbsp;&nbsp;&nbsp;`title` char(255) NOT NULL , &nbsp;&nbsp;&nbsp;&nbsp;PRIMARY KEY (`id`) ); </td> 
 <td style="width:173.25pt;"> DROP INDEX `PRIMARY` ON `table_name`; 或：ALTER TABLE `table_name`&nbsp;DROP PRIMARY KEY 对主键来说，删除时index_name必须是`PRIMARY`，这是保留的。 添加&nbsp; AUTO_INCREMENT&nbsp; 约束字段的唯一索引不能被删除。 </td> 
 </tr> 
 <tr> 
 <td> 外键索引 </td> 
 <td style="width:282pt;"> （1）创建表的时候直接指定 CREATE TABLE `t_class`( &nbsp;&nbsp;&nbsp;&nbsp;id int unsigned auto_increment not null, &nbsp;&nbsp;&nbsp;&nbsp;name varchar(10), &nbsp; &nbsp; primary key(id) ); CREATE TABLE `t_student`( &nbsp;&nbsp;&nbsp;&nbsp;id int unsigned auto_increment not null&nbsp;primary key, &nbsp; &nbsp; sid int unsigned default null, &nbsp; &nbsp; CONSTRAINT fk_student_class FOREIGN KEY (sid) REFERENCES class (id) ); （2）修改表结构 ALTER TABLE tb1 ADD CONSTRAINT&nbsp;FK_ID FOREIGN foreign KEY (`gid`) REFERENCES&nbsp;tb2(`id`); </td> 
 <td style="width:173.25pt;"> DROP INDEX index_name ON `table_name` 或：ALTER TABLE `table_name`&nbsp;DROP FOREIGN KEY 外键名 </td> 
 </tr> 
 <tr> 
 <td> 全文索引 </td> 
 <td style="width:282pt;"> CREATE TABLE `table` ( &nbsp;&nbsp;&nbsp;&nbsp;`id` int(11) NOT NULL AUTO_INCREMENT , &nbsp;&nbsp;&nbsp;&nbsp;`title` char(255) CHARACTER NOT NULL , &nbsp;&nbsp;&nbsp;&nbsp;`content` text CHARACTER NULL , &nbsp;&nbsp;&nbsp;&nbsp;PRIMARY KEY (`id`), &nbsp;&nbsp;&nbsp;&nbsp;FULLTEXT (content) ); </td> 
 <td style="width:173.25pt;"> </td> 
 </tr> 
 <tr> 
 <td> 联合索引 </td> 
 <td style="width:282pt;"> ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); </td> 
 <td style="width:173.25pt;"> </td> 
 </tr> 
 </tbody> 
</table>

一条语句创建多条索引：

ALTER TABLE \`t\_user\` ADD INDEX idx\_user\_name (\`user\_name\`), ADD INDEX idx\_phone (\`phone\`);

# 查看索引 #

## 查看索引 ##

**命令：SHOW INDEX FROM table\_name**

官网网址：[https://dev.mysql.com/doc/refman/5.7/en/show-index.html][https_dev.mysql.com_doc_refman_5.7_en_show-index.html]

示例：

CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id 自增,主键',
      `name` varchar(12) NOT NULL DEFAULT '' COMMENT '用户姓名',
      `score` int(3) NOT NULL COMMENT '分数',
      `class` varchar(12) NOT NULL COMMENT '班级',
      `school_id` int(11) NOT NULL COMMENT '学校id',
      PRIMARY KEY (`id`),
    ) ENGINE=InnoDB AUTO_INCREMENT=192904 DEFAULT CHARSET=utf8;

CREATE INDEX name_Index ON `user`(`name`(5));
    CREATE INDEX school_Index ON `user`(`school_id`);
    CREATE INDEX class_score_Index ON `user`(`class`, `score`);

执行结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaXlpbmcwY2FuZ2xhbmc_size_16_color_FFFFFF_t_70][]

<table> 
 <tbody> 
 <tr> 
 <td> 项 </td> 
 <td> 含义 </td> 
 </tr> 
 <tr> 
 <td> table </td> 
 <td> 表的名称 </td> 
 </tr> 
 <tr> 
 <td> Non_unique </td> 
 <td> 如果该列索引中不包括重复的值则为0否则为1 </td> 
 </tr> 
 <tr> 
 <td> Key_name </td> 
 <td> 索引名称,如果是主键的话则为PRIMARY </td> 
 </tr> 
 <tr> 
 <td> Seq_in_index </td> 
 <td> 索引中序列的序列号,从1开始。如果是组合索引那么按照字段在建立索引时的顺序排列如('c1','c2','c3')那么分别为1,2,3 </td> 
 </tr> 
 <tr> 
 <td> Column_name </td> 
 <td> 列的名称 </td> 
 </tr> 
 <tr> 
 <td> Collation </td> 
 <td> 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分序） </td> 
 </tr> 
 <tr> 
 <td> Cardinality </td> 
 <td> 索引中唯一值的数目的估计值,通过运行ANALYZETABLEormyisamchk-a来更新，数根据被存储为整数的统计数据来计数,所以对于小表该值没必要太过于精确,而对于大数据量的表来说,改值越大当进行联合时，MySQL使用该索引的机会就越大。 </td> 
 </tr> 
 <tr> 
 <td> Sub_part </td> 
 <td> 索引的长度,如果是部分被编入索引则该值表示索引的长度,如果是整列被编入索引则为null,例如name_Index和school_Index两个索引,比较一下上面两个索引创建时候的区别 </td> 
 </tr> 
 <tr> 
 <td> Packed </td> 
 <td> 指示关键字如何被压缩。如果没有被压缩，则为NULL </td> 
 </tr> 
 <tr> 
 <td> Null </td> 
 <td> 如果该列的值有NULL,则是YES否则为NO.. </td> 
 </tr> 
 <tr> 
 <td> Index_type </td> 
 <td> 所用索引方法（BTREE,FULLTEXT,HASH,RTREE） </td> 
 </tr> 
 <tr> 
 <td> Commnet </td> 
 <td> 关于在其列中没有描述的索引的信息 </td> 
 </tr> 
 <tr> 
 <td> Index_comment </td> 
 <td> 为索引创建时提供了一个注释属性的索引的任何评论 </td> 
 </tr> 
 </tbody> 
</table>

## 查看索引状态 ##

**命令：SHOW STATUS LIKE 'Handler\_read%';**

示例：

handler\_read\_key:越高越好，表示使用索引查询到的次数  
handler\_read\_rnd\_next:这个值越高，说明查询低效

# 强制/忽略 索引 #

**命令：USE/FORCE/IGNORE INDEX(index\_name)**

作用：希望使用/强制使用/不使用 某索引

FORCE INDEX 和 USE INDEX 的区别

*  USE INDEX 给MYSQL优化器一种选择的可能，具体的MYSQL优化器再进行优化选择
 *  FORCE INDEX 是强制MYSQL优化器使用某个索引

**示例1：use index**

如在test1中查询。test1 有两个字段：主键id，索引 name。

默认情况：使用id的索引（加粗部分）

指定希望使用的索引：最终使用了指定的索引（加粗部分）

**示例2：force index**

**示例3：ignore index**

默认情况：使用索引

不使用索引：发现没使用索引

# 索引长度 #

**索引长度的含义**

创建索引的命令有两种写法（指定或者不指定长度）：

*  CREATE INDEX index\_name ON table\_name (column\_name)
 *  CREATE INDEX index\_name ON table\_name (column\_name(length))

如果确实需要在单个很大的列上创建索引，或者需要在多个很大的列上创建联合索引，而又超过了索引的长度限制，**解决办法是在建索引时限制索引prefix的大小**（也就是上边命令中的length）。

例如：

create index yarn_app_result_i4 on yarn_app_result (flow_exec_id(100), another_column(50));

这样，在创建索引时就会限制使用的每个列的最大长度。如上的例子中，在创建联合索引时，最多使用列flow\_exec\_id中前100个字符创建索引，最多使用another\_column中前50个字符创建索引。这样子，就可以避免索引长度过大的问题。

**为什么要指定长度？**

1.  如果要创建索引的列很长，在创建索引时以及根据索引查询时，都会浪费很多时间在计算和存储上。限制其长度可以提高性能
2.  数据库对索引的长度有限制。

**长度限制**

<table> 
 <tbody> 
 <tr> 
 <td>项</td> 
 <td>单列索引</td> 
 <td>联合索引</td> 
 </tr> 
 <tr> 
 <td>长度限制</td> 
 <td> MySQL5.6：默认不能超过767bytes MySQL5.7：默认不超过3072bytes </td> 
 <td>默认不超过3072bytes</td> 
 </tr> 
 <tr> 
 <td>长度限制来由</td> 
 <td> 767字节：256×3-1。这个3是字符最大占用空间（utf8）。 3072字节：5.5以后，开始支持4个字节的uutf8，255×4&gt;767。默认情况下：innodb_large_prefix=OFF，最大字节数为767。可通过以下方法设置为3072：innodb_large_prefix=ON、innodb_file_format=barracuda、innodb_file_per_table=ON&nbsp;，且Innodb表的存储格式为&nbsp;DYNAMIC&nbsp;或 COMPRESSED </td> 
 <td> &nbsp; &nbsp; &nbsp; &nbsp; InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录（否则就退化链表了）。所以一个记录最多不能超过8k。 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，pk和某个二级索引都达到这个限制）。 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是(1024*3)。&nbsp; </td> 
 </tr> 
 </tbody> 
</table>

命令示例

CREATE TABLE \`tb\` (  
\`a\` varchar(255) DEFAULT NULL,  
\`b\` varchar(255) DEFAULT NULL,  
\`c\` varchar(255) DEFAULT NULL,  
\`d\` varchar(255) DEFAULT NULL,  
\`e\` varchar(255) DEFAULT NULL,  
KEY \`idx\_a\` (\`a\`,\`b\`,\`c\`,\`d\`,\`e\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

报错： ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

原因：创建的联合索引idx\_a的长度为：255\*3\*5= 3825 bytes ，大于最大值 3072 bytes

# **index和key的区别** #

**官方解释**：

KEY通常是INDEX的同义词。当在列定义中给出时，键属性PRIMARY KEY也可以被指定为KEY。这是为了与其他数据库系统兼容而实现的。

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

**如果语句中只有key，没有其他辅助，则表示普通索引。**

示例如下：

mysql> create table t(a int,key idx_a(a))engine =innodb;
    Query OK, 0 rows affected (0.24 sec)
    
    mysql> desc t;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | a     | int(11) | YES  | MUL | NULL    |       |
    +-------+---------+------+-----+---------+-------+
    1 row in set (0.00 sec)

若语句中结合其他，如：primary，则结果如下：

mysql> create table t1(a int, primary key idx_a(a))engine =innodb;
    Query OK, 0 rows affected (0.44 sec)
    
    mysql> desc t1;
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | a     | int(11) | NO   | PRI | NULL    |       |
    +-------+---------+------+-----+---------+-------+

**key**：

key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等。  
primary key 有两个作用：一是约束作用（constraint），规范一个存储主键和唯一性，同时在此key上建立了一个index；  
unique key 有两个作用：一是约束作用（constraint），规范数据的唯一性，同时也在这个key上建立了一个index；  
foreign key也有两个作用：一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index

可见，mysql的key是同时具有constraint和index的意义，这点和其他数据库表现的可能有区别。（至少在[Oracle][]上建立外键，不会自动建立index），因此创建key也有如下几种方式：  
　　（1）在字段级以key方式建立， 如 create table t (id int not null primary key);  
　　（2）在表级以constraint方式建立，如create table t(id int, CONSTRAINT pk\_t\_id PRIMARY key (id));  
　　（3）在表级以key方式建立，如create table t(id int, primary key (id));  
其它key创建类似，但不管那种方式，既建立了constraint，又建立了index，只不过index使用的就是这个constraint或key。

**index**：

index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；  
因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。  
如，create table t(id int, index inx\_tx\_id (id));

[SQL_MySQL--_--_--_--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/120520401
[MySQL _ MySQL 8.0 Reference Manual _ 13.1 Data Definition Statements]: https://dev.mysql.com/doc/refman/8.0/en/sql-data-definition-statements.html
[https_dev.mysql.com_doc_refman_5.7_en_show-index.html]: https://dev.mysql.com/doc/refman/5.7/en/show-index.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2ZlaXlpbmcwY2FuZ2xhbmc_size_16_color_FFFFFF_t_70]: /images/20220828/0a3d6b937a794187a3ba4205fec70278.png
[Oracle]: http://lib.csdn.net/base/oracle