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的差別

繼續閱讀

建立/删除索引

強制/忽略索引