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

简介

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

创建/删除索引

官网：MySQL :: MySQL 8.0 Reference Manual :: 13.1 Data Definition Statements

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

一条语句创建多条索引：

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

示例

建表、建索引

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`);

查看索引

SHOW INDEX FROM user

执行结果

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

查看索引状态

命令

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的索引（加粗部分）

explain select id from test1;

+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |

+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

|    1 | SIMPLE      | test1 | index | NULL          | PRIMARY | 4       | NULL |   18 | Using index |

+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

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

explain select id from test1 use index(name);

+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |

+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

|    1 | SIMPLE      | test1 | index | NULL          | name_index | 6       | NULL |   18 | Using index |

+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

示例2：force index

explain select id from test1 force index(name);
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | index | NULL          | name_index | 6       | NULL |   18 | Using index |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

示例3：ignore index

默认情况：使用索引

explain select name from test1 ;
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | test1 | index | NULL          | name_index | 6       | NULL |   18 | Using index |
+------+-------------+-------+-------+---------------+------------+---------+------+------+-------------+

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

explain select name from test1 ignore index(name);
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL |   18 |       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+

索引长度

索引长度的含义

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

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个字符创建索引。这样子，就可以避免索引长度过大的问题。

为什么要指定长度？

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

长度限制

项

单列索引

联合索引

长度限制

MySQL5.6：默认不能超过767bytes

MySQL5.7：默认不超过3072bytes

默认不超过3072bytes

长度限制来由

767字节：256×3-1。这个3是字符最大占用空间（utf8）。

3072字节：5.5以后，开始支持4个字节的uutf8，255×4>767。默认情况下：innodb_large_prefix=OFF，最大字节数为767。可通过以下方法设置为3072：innodb_large_prefix=ON、innodb_file_format=barracuda、innodb_file_per_table=ON ，且Innodb表的存储格式为 DYNAMIC 或 COMPRESSED

InnoDB一个page的默认大小是16k。由于是Btree组织，要求叶子节点上一个page至少要包含两条记录（否则就退化链表了）。所以一个记录最多不能超过8k。

又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，pk和某个二级索引都达到这个限制）。

由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是(1024*3)。

命令示例

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表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；

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

简介

创建/删除索引

查看索引

查看索引

查看索引状态

强制/忽略索引

索引长度

index和key的区别

继续阅读

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述

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

简介

创建/删除 索引

查看索引

查看索引

查看索引状态

强制/忽略 索引

索引长度

index和key的区别

继续阅读

创建/删除索引

强制/忽略索引