Résumé des index MySQL – introduction détaillée aux types d'index MySQL et à leur création

Concernant les avantages des index MySQL, si MySQL qui est correctement conçu et utilise des index est une Lamborghini, alors MySQL qui n'est pas conçu et utilise des index est un tricycle humain. Pour les tables sans index, une seule requête de table peut contenir des centaines de milliers de données, ce qui constitue le goulot d'étranglement. Généralement, les grands sites Web peuvent générer des centaines de milliers, voire des millions de données en une seule journée, et les requêtes sans index deviendront très lentes. En prenant WordPress comme exemple, bon nombre de ses tables de données ajouteront des index aux champs fréquemment interrogés. Par exemple, la table wp_comments a un index BTREE conçu pour 5 champs.

Un test de comparaison simple

Prenons comme exemple simple les données que j'ai testées l'année dernière. Plus de 20 sources de données ont généré aléatoirement 2 millions de données. En moyenne, chaque source de données contient environ 100 000. répétitions. Deuxièmement, la structure de la table est relativement simple, contenant uniquement un identifiant à incrémentation automatique, un type char, un type texte et un type int. La table unique a une taille de 2 Go et utilise le moteur MyIASM. Démarrage du test sans ajouter d'index.

Exécutez l'instruction SQL suivante :

mysql> SELECT id,FROM_UNIXTIME(time) FROM article WHERE a.title='测试标题'

Le temps requis pour la requête est très effrayant. Si vous ajoutez des requêtes conjointes et d'autres contraintes, la base de données disparaîtra. fou. Consomme de la mémoire et affecte l'exécution des programmes frontaux. À ce stade, ajoutez un index BTREE au champ de titre :

mysql> ALTER TABLE article ADD INDEX index_article_title ON title(200);

Exécutez à nouveau l'instruction de requête ci-dessus, et le contraste est très évident :

Le concept de MySQL index

Les index sont des fichiers spéciaux (les index de la table de données InnoDB sont un composant de l'espace table. Ils contiennent des pointeurs de référence vers tous les enregistrements de la table de données). Pour le dire plus généralement, un index de base de données est comme la table des matières au début d'un livre, ce qui peut accélérer les requêtes dans la base de données. Dans l'instruction SQL ci-dessus, s'il n'y a pas d'index, la base de données parcourra les 200 éléments de données et sélectionnera celles qui remplissent les conditions avec l'index correspondant, la base de données recherchera directement les options qui remplissent les conditions dans l'index ; . Si nous modifions l'instruction SQL en "SELECT * FROM article WHERE id=2000000", voulez-vous que la base de données vous donne les résultats après avoir lu 2 millions de lignes de données en séquence, ou qu'elle les localise directement dans l'index ? La comparaison temporelle claire entre les deux images ci-dessus donne déjà la réponse (Remarque : généralement, les bases de données génèrent par défaut des index pour la clé primaire).

Les index sont divisés en index clusterisés et index clusterisés. Les index clusterisés sont classés en fonction de l'emplacement physique du stockage des données, mais les index clusterisés sont différents. La vitesse de récupération des lignes, tandis que l'index non clusterisé est rapide pour la récupération d'une seule ligne.

Types d'index MySQL

1. Index ordinaire

Il s'agit de l'index le plus basique. Par exemple, l'index créé pour le champ de titre ci-dessus est. un index ordinaire, l'index de type BTREE par défaut dans MyIASM, est également l'index que nous utilisons dans la plupart des cas.

01    –直接创建索引    
02    CREATE INDEX index_name ON table(column(length))    
03    –修改表结构的方式添加索引    
04    ALTER TABLE table_name ADD INDEX index_name ON (column(length))    
05    –创建表的时候同时创建索引    
06    CREATE TABLE `table` (    
07    `id` int(11) NOT NULL AUTO_INCREMENT ,    
08    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
09    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
10    `time` int(10) NULL DEFAULT NULL ,    
11    PRIMARY KEY (`id`),    
12    INDEX index_name (title(length))    
13    )    
14    –删除索引    
15    DROP INDEX index_name ON table

2. L'index unique

est similaire à un index normal, sauf que la valeur de la colonne d'index doit être unique, mais des valeurs nulles. ​​sont autorisés (notez qu'elle est différente de la clé primaire). S'il s'agit d'un index combiné, la combinaison des valeurs des colonnes doit être unique et la méthode de création est similaire à celle d'un index normal.

01    –创建唯一索引    
02    CREATE UNIQUE INDEX indexName ON table(column(length))    
03    –修改表结构    
04    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))    
05    –创建表的时候直接指定    
06    CREATE TABLE `table` (    
07    `id` int(11) NOT NULL AUTO_INCREMENT ,    
08    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
09    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
10    `time` int(10) NULL DEFAULT NULL ,    
11    PRIMARY KEY (`id`),    
12    UNIQUE indexName (title(length))    
13    );

3. Index en texte intégral (FULLTEXT)

MySQL prend en charge l'index en texte intégral et la recherche en texte intégral à partir de la version 3.23. .23, index FULLTEXT Disponible uniquement pour les tables MyISAM ; elles peuvent être créées à partir de colonnes CHAR, VARCHAR ou TEXT dans le cadre d'une instruction CREATE TABLE, ou ajoutées ultérieurement à l'aide de ALTER TABLE ou CREATE INDEX. ////Pour des ensembles de données plus volumineux, saisir vos données dans une table sans index FULLTEXT, puis créer l'index est plus rapide que de saisir les données dans un index FULLTEXT existant. Toutefois, n'oubliez pas que pour les tables de données de grande capacité, la génération d'index de texte intégral est un processus très long et gourmand en espace disque.

01    –创建表的适合添加全文索引    
02    CREATE TABLE `table` (    
03    `id` int(11) NOT NULL AUTO_INCREMENT ,    
04    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
05    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
06    `time` int(10) NULL DEFAULT NULL ,    
07    PRIMARY KEY (`id`),    
08    FULLTEXT (content)    
09    );    
10    –修改表结构添加全文索引    
11    ALTER TABLE article ADD FULLTEXT index_content(content)    
12    –直接创建索引    
13    CREATE FULLTEXT INDEX index_content ON article(content)

4. Index à une seule colonne, index à plusieurs colonnes

L'effet de requête de plusieurs index à une seule colonne est différent de celui d'un seul index multi-colonnes, car lors de l'exécution d'une requête, MySQL ne peut utiliser qu'un seul index et sélectionnera l'index le plus restrictif parmi plusieurs index.

5. Index composite (préfixe le plus à gauche)

Les instructions de requête SQL habituellement utilisées ont généralement plus de restrictions, donc afin d'extraire davantage l'efficacité de MySQL, il est nécessaire d'envisager d'établir un index composite . Par exemple, dans le tableau ci-dessus, un index combiné est créé pour le titre et l'heure : ALTER TABLE article ADD INDEX index_titme_time (title(50), time(10)). Établir un tel index combiné équivaut en fait à établir les deux ensembles d'index combinés suivants :

–titre, heure

–titre

Pourquoi n'y a-t-il pas d'index combiné comme l'heure ? Du drap de laine ? Ceci est le résultat du "préfixe le plus à gauche" de l'index composite MySQL. La compréhension simple est de commencer la combinaison uniquement à partir de celle la plus à gauche. Non seulement les requêtes contenant ces deux colonnes utiliseront l'index combiné, comme indiqué dans les SQL suivants :

1    –使用到上面的索引    
2    SELECT * FROM article WHREE title='测试' AND time=1234567890;    
3    SELECT * FROM article WHREE utitle='测试';    
4    –不使用上面的索引    
5    SELECT * FROM article WHREE time=1234567890;

Ce qui précède est un résumé des index mysql - une introduction détaillée aux types d'index mysql et à la création de contenu, s'il vous plaît faites attention au site Web PHP chinois (www.php.cn) pour plus de contenu connexe !