1. Introduction à l'index
(1) La signification et la spécificité de l'index
(2) Classification de l'index
(3) Principes de conception de l'index
2. Créer un index
(1) Créer un index lors de la création d'une table
(2) Créer un index sur une table existante
(3) Supprimer un index
(Recommandation d'apprentissage gratuite :Tutoriel vidéo mysql)
1. Introduction aux index
Les indices sont utilisés pour trouver rapidement des lignes avec une valeur spécifique dans une colonne. Sans utiliser d'index, MySQL doit lire la table entière en commençant par le premier enregistrement jusqu'à trouver les lignes pertinentes. Plus la table est grande, plus l'interrogation des données prend du temps. Si la colonne interrogée dans la table possède un index, MySQL peut accéder rapidement à un emplacement pour rechercher le fichier de données sans avoir à examiner toutes les données.
Signification : Un index est une structure de base de données distincte stockée sur le disque, qui contient des pointeurs de référence vers tous les enregistrements de la table de données. Utilisé pour rechercher rapidement des lignes avec une valeur spécifique dans une ou plusieurs colonnes.
Les index sont implémentés dans les moteurs de stockage, donc les index de chaque moteur de stockage ne sont pas nécessairement exactement les mêmes, et chaque moteur de stockage ne prend pas nécessairement en charge tous les types d'index. Définissez le nombre maximum d'index et la longueur maximale de l'index pour chaque table en fonction du moteur de stockage. Tous les moteurs de stockage prennent en charge au moins 16 index par table, avec une longueur totale d'index d'au moins 256 octets. La plupart des moteurs de stockage ont des limites plus élevées.
Il existe deux types d'index de stockage dans MySQL : BTREE
et HASH
, qui sont spécifiquement liés au moteur de stockage de la table ; les moteurs de stockage MyISAM et InnoDB ne prennent en charge que les index BTREE ; les moteurs peuvent prendre en charge les index HASH et BTREE.
Avantages de l'index :
1. En créant un index unique, l'unicité de chaque ligne de données dans la table de la base de données peut être garantie.
2. Peut accélérer considérablement la requête de données. (La principale raison de la création d'un index)
3. En termes d'intégrité référentielle des données, cela peut accélérer la connexion entre les tables et les tables.
4. Lors de l'utilisation de clauses de regroupement et de tri pour la requête de données, le temps de regroupement et de tri dans la requête peut également être considérablement réduit.
Inconvénients de l'ajout d'index :
1. La création et la maintenance d'index prennent du temps, et à mesure que la quantité de données augmente, le temps passé augmentera également.
2. Les index occupent de l'espace disque. En plus de l'espace de données occupé par la table de données, chaque index occupe également une certaine quantité d'espace physique. S'il y a un grand nombre d'index, le fichier d'index peut atteindre le fichier maximum. taille plus rapide que le fichier de données.
3. Lors de l'ajout, de la suppression et de la modification de données dans le tableau, l'index doit également être maintenu dynamiquement, ce qui réduit la vitesse de maintenance des données.
1. Index ordinaire et index unique (unique)
2. Index à colonne unique et index combiné
3. Index de texte intégral (fulltext)
4. Index spatial (spatial)
Une conception d'index déraisonnable ou l'absence d'index entraînera des obstacles aux performances de la base de données et des applications. Des index efficaces sont très importants pour obtenir de bonnes performances. Lors de la conception d'index, les directives suivantes doivent être prises en compte :
2. Créer un index
Format de syntaxe :
create table table_name [col_name date_type][unique|fulltext|spatial] [index|key] [index_name] (col_name [length]) [asc | desc]
①Créer un index normal
Un index normal est le type d'index le plus basique et n'a aucune restriction telle que unicité, sa fonction est uniquement d'accélérer l'accès aux données.
[Exemple 1] Créez un index commun sur le champ year_publication dans la table book. L'instruction SQL est la suivante :
mysql> create table book -> ( -> bookid int not null, -> bookname varchar(255) not null, -> authors varchar(255) not null, -> info varchar(255) null, -> comment varchar(255) null, -> year_publication year not null, -> index(year_publication) -> );Query OK, 0 rows affected (0.21 sec)mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, KEY `year_publication` (`year_publication`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from book where year_publication=1990 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: book partitions: NULL type: ref possible_keys: year_publication key: year_publication key_len: 1 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)
L'explication de chaque ligne du résultat de sortie de l'instruction explan. est la suivante :
mysql> create table t1 -> ( -> id int not null -> ,name char(30) not null, -> unique index uniqidx(id) -> );Query OK, 0 rows affected (0.27 sec)mysql> show create table t1 \G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, UNIQUE KEY `uniqidx` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)
mysql> create table t2 -> ( -> id int not null, -> name char(50) null, -> index singleidx(name(20)) -> );Query OK, 0 rows affected (0.06 sec)mysql> show create table t2 \G*************************** 1. row *************************** Table: t2Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` char(50) DEFAULT NULL, KEY `singleidx` (`name`(20))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec)
Un index combiné consiste à créer un index sur plusieurs champs.
mysql> create table t3 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> index mulitiidx(id,name,age) -> );Query OK, 0 rows affected (0.07 sec)mysql> show create table t3 \G*************************** 1. row *************************** Table: t3Create Table: CREATE TABLE `t3` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, `age` int(11) NOT NULL, `info` varchar(255) DEFAULT NULL, KEY `mulitiidx` (`id`,`name`,`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from t3 where id = 1 and name = 'joe' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ref possible_keys: mulitiidx key: mulitiidx key_len: 124 ref: const,const rows: 1 filtered: 100.00 Extra: Using index condition1 row in set, 1 warning (0.06 sec)
mysql> create table t4 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> fulltext index fulltxtidx(info) -> )engine=MyISAM;Query OK, 0 rows affected (0.08 sec)mysql> show create table t4 \G*************************** 1. row *************************** Table: t4Create Table: CREATE TABLE `t4` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, `age` int(11) NOT NULL, `info` varchar(255) DEFAULT NULL, FULLTEXT KEY `fulltxtidx` (`info`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)
mysql> create table t5 -> ( g geometry not null,spatial index spatidx(g) ) ENGINE=MyISAM;Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> show create table t5 \G*************************** 1. row *************************** Table: t5Create Table: CREATE TABLE `t5` ( `g` geometry NOT NULL, SPATIAL KEY `spatidx` (`g`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.05 sec)
1. Utilisez l'instruction alter table pour créer un index
Syntaxe de base :alter table table_name add [unique|fulltext|spatial] [index|key][index_name] (col_name[length],...) [asc |dec]
mysql> show index from book \G*************************** 1. row *************************** Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.10 sec)
其中,各个主要参数的含义为;
可以看到book表中已经存在一个索引,即year_publication索引,该索引为非唯一索引,下面使用alter table 在bookname字段上添加索引,SQL语句如下:
mysql> alter table book add index bknameidx( bookname(30) );Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G*************************** 1. row *************************** Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 2. row *************************** Table: book Non_unique: 1 Key_name: bknameidx Seq_in_index: 1 Column_name: bookname Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL2 rows in set (0.05 sec)
可以看到表中有了两个索引,另一个为通过alter table语句添加的名称为bknameidx的索引,该索引为非唯一索引,长度为30。
【例8】在book表的bookid字段上建立名称为uniqididx的唯一索引,SQL语句如下:
mysql> alter table book add unique index uniqididx(bookid);Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...*************************** 3. row *************************** Table: book Non_unique: 1 Key_name: bknameidx Seq_in_index: 1 Column_name: bookname Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL3 rows in set (0.01 sec)
可以看到,Non_unique的属性值为0,表示名称为Uniqididx的索引为唯一索引,创建唯一索引成功。
【例9】在book表的comment字段上建立单列索引,SQL语句如下:
mysql> alter table book add index bkcmtidx ( comment(50) );Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...3...*************************** 4. row *************************** Table: book Non_unique: 1 Key_name: bkcmtidx Seq_in_index: 1 Column_name: comment Collation: A Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL4 rows in set (0.01 sec)
可以看到,语句执行之后再book表的comment字段上建立了名称为bkcmtidx的索引,长度为50,在查询时,只需要检索前50个字符。
【例10】在book表的authors和info字段上建立组合索引,SQL语句如下:
mysql> alter table book add index bkauandinfoidx (authors(30),info(50) );Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...3...4...*************************** 5. row *************************** Table: book Non_unique: 1 Key_name: bkauandinfoidx Seq_in_index: 1 Column_name: authors Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 6. row *************************** Table: book Non_unique: 1 Key_name: bkauandinfoidx Seq_in_index: 2 Column_name: info Collation: A Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL6 rows in set (0.06 sec)
可以看到名称为bkauandinfoidx的索引由两个字段组成,authors字段长度为30,在组合索引中的序号为1,该字段不允许空值null;info字段长度为50,在组合索引中的序号为2,该字段可以为空值null。
【例11】创建表t6,在t6表上使用alter table创建全文索引,SQL语句如下:
mysql> create table t6 -> ( -> id int not null, -> info char(255) -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t6 add fulltext index infofiidx( info );Query OK, 0 rows affected (0.13 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from t6 \G*************************** 1. row *************************** Table: t6 Non_unique: 1 Key_name: infofiidx Seq_in_index: 1 Column_name: info Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: FULLTEXT Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.05 sec)
可以看到,t6表中已经创建了名称为infoftidx的索引,该索引在info字段上创建,类型为fulltext,允许空值。
【例12】创建表t7,t7的空间类型字段g上创建名称为spatidx的空间索引,SQL语句如下:
mysql> create table t7(g geometry not null)ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t7 add spatial index spatidx(g);Query OK, 0 rows affected, 1 warning (0.06 sec)Records: 0 Duplicates: 0 Warnings: 1mysql> show index from t7 \G*************************** 1. row *************************** Table: t7 Non_unique: 1 Key_name: spatidx Seq_in_index: 1 Column_name: g Collation: A Cardinality: NULL Sub_part: 32 Packed: NULL Null: Index_type: SPATIAL Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.01 sec)
可以看到,t7表的g字段上创建了名为spatidx的空间索引。
2.使用create index 创建索引
create index 语句可以在已经存在的表上添加索引,MySQL中create index被映射到一个alter table语句上,基本语法为:
create [unique|fulltext|spatial] index index_nameon table_name (col_name[length],...) [asc|desc]
可以看到create index语句和alter index语句的语法基本一样,只是关键字不同,使用相同的表book,假设该表中没有任何索引值,创建book表语句如下:
create table book(bookid int not null,bookname varchar(255) not null,authors varchar(255) not null,info varchar(255) null,comment varchar(255) null,year_publication year not null);
【例13】在book表的bookname字段上建立名为BkNameIdx的普通索引,SQL语句如下:
mysql> create index BkNameOdx on book(bookname);Query OK, 0 rows affected (0.10 sec)Records: 0 Duplicates: 0 Warnings: 0
【例14】在book表的bookid字段上建立名为UniqidIdx的唯一索引,SQL语句如下:
mysql> create unique index UniqiiIdx on book(bookid);Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0
【例15】在book表的comment字段上建立单列索引,SQL语句如下:
mysql> create index BkcmtIdx on book(bookid);Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0
【例16】在book表的authors和info字段上建立组合索引,SQL语句如下:
mysql> create index BkAuAndInfoIdx on book (authors(20),info(50));Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0
【例17】删除表t6,重新建立表t6,在t6表中使用create index 语句,在char类型的info字段上创建全文索引。
mysql> drop table t6;Query OK, 0 rows affected (0.02 sec)mysql> create table t6 -> ( -> id int not null, -> info char(255) -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create fulltext index infoftidx on t6(info);Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0
【例18】删除表t7,重新创建表t7,在t7表中使用create index语句,在空间数据类型字段g上创建名称为spatIdx的空间索引。
mysql> drop table t7;Query OK, 0 rows affected (0.06 sec)mysql> create table t7 (g geometry not null )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create spatial index spatIdx on t7 (g);Query OK, 0 rows affected, 1 warning (0.07 sec)Records: 0 Duplicates: 0 Warnings: 1
MySQL中删除索引使用alter table或者drop index 语句,两者可实现相同的功能,drop index 语句在内部被映射到一个alter table语句中。
1.使用alter table删除索引
alter table 删除索引的基本语法格式:
alter table table_name drop index index_name
【例1】删除book表中的名称为UniqidIdx的唯一索引。
mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, UNIQUE KEY `UniqiIdx` (`bookid`), KEY `BkNameOdx` (`bookname`), KEY `BkcmtIdx` (`bookid`), KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)mysql> alter table book drop index UniqiIdx;Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, KEY `BkNameOdx` (`bookname`), KEY `BkcmtIdx` (`bookid`), KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
可以看到,book表中已经没有名称为UniqidIdx的唯一索引,删除索引成功。
注意:添加auto_increment约束字段的唯一索引不能被删除。
2.使用drop index 语句删除索引
drop index语句删除索引的基本语法格式:
drop index inde _name on table_name
【例2】删除book表中名称为BkAuAndInfoIdx的组合索引,SQL语句如下:
mysql> drop index BkAuAndInfoIdx on book;Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, KEY `BkNameOdx` (`bookname`), KEY `BkcmtIdx` (`bookid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
可以看到,book表中已经没有名称为BkAuAndInfoIdx的组合索引,删除索引成功。
注意:删除表中的列时,如果要删除的列为索引的组成部分,则该部分也会从索引中删除。如果组成索引的所有列都被删除,那么整个索引将被删除。
相关免费学习推荐:mysql数据库(视频)
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!