Qu'est-ce qu'un index MySQL ? Introduction aux connaissances connexes sur l'index mysql

Le contenu de cet article porte sur qu'est-ce que l'index MySQL ? L'introduction de connaissances pertinentes sur l'index mysql a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer, j'espère que cela vous sera utile.

Qu'est-ce qu'un index

Un index est comme la table des matières d'un livre

. L'index est utilisé pour une recherche rapide. Pour trouver une ligne 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'à ce qu'elle trouve la ligne correspondante. Plus la table est grande, plus elle est grande. temps nécessaire pour interroger les données. Si la colonne interrogée dans la table a un index, MySQL peut rapidement accéder à un emplacement pour rechercher le fichier de données sans avoir à consulter toutes les données, ce qui permettra de gagner beaucoup de temps.

Avantages et inconvénients

Avantages

1 Accélère considérablement la requête

Tous les types de champs peuvent être indexés

Inconvénients

1. Il faut du temps pour créer et maintenir des index, plus il y a de données, plus les index occupent de l'espace de stockage et les données dans les données. La table aura également un paramètre en ligne maximum. Si nous avons un grand nombre d'index, le fichier d'index peut atteindre la valeur en ligne plus rapidement que le fichier de données

3. Lors de l'ajout, de la suppression ou de la modification de données dans le fichier. table, l'index a également besoin d'une maintenance dynamique qui réduit la vitesse de maintenance des données

Principes et scénarios d'utilisation

1. 🎜>2. Les tables fréquemment mises à jour doivent avoir le moins d'index possible

3. Construisez des index pour les champs fréquemment utilisés pour les requêtes

4. pour les champs avec de petites quantités de données. Interroger tout Le temps passé sur les données est plus court que celui de parcourir les données d'index, et l'index n'aura aucun effet d'optimisation

5. Essayez de ne pas utiliser d'index pour les champs avec peu de données. des valeurs différentes, comme le champ genre qui n'a que deux valeurs différentes pour les hommes et les femmes.

Classification des index

Remarque : l'indexation est implémentée dans le moteur de stockage, ce qui signifie que différents moteurs de stockage utiliseront différents index Stockage MyISAM et InnoDB moteurs : ne prennent en charge que les index BTREE, ce qui signifie que BTREE est utilisé par défaut et ne peut pas être remplacé

Moteurs de stockage MEMORY/HEAP : prend en charge les index HASH et BTREE

1. 🎜>

Un index ne contient qu'une seule colonne, mais il peut y avoir plusieurs index à une seule colonne dans une table

Indice ordinaire

Type d'index de base dans MySQL, non Quelles restrictions sont autorisées pour insérer des valeurs en double et des valeurs nulles dans les colonnes où l'index est défini, uniquement pour interroger les données plus rapidement.

1.2. Index unique

La valeur dans la colonne d'index doit être unique, mais les valeurs nulles sont autorisées

Indice de clé primaire

est un index unique spécial, aucune valeur nulle n'est autorisée

2 Index combiné

Un index créé sur une combinaison de plusieurs champs dans la table, uniquement dans. les conditions de requête L'index ne sera utilisé que lorsque le champ gauche de ces champs est utilisé. Lors de l'utilisation de l'index combiné, suivez la règle du meilleur préfixe gauche

3. 🎜>

Index de texte intégral, il ne peut être utilisé que sur le moteur MyISAM, et les index de texte intégral ne peuvent être utilisés que sur les champs de type CHAR, VARCHAR et TEXT. L'index de texte intégral signifie que dans une pile de texte, vous pouvez trouver la ligne d'enregistrement à laquelle appartient le champ grâce à un certain mot-clé, par exemple : "Vous êtes un grand méchant, une personne de second ordre..." Grâce à un gros méchant stylo, vous pourrez peut-être le retrouver. Trouver l'enregistrement

4. Index spatial

Un index spatial est un index construit sur des champs de types de données spatiales. . Il existe quatre types de données spatiales dans MySQL, GEOMETRY, POINT, LINESTRING, POLYGON. Lors de la création d'un index spatial, utilisez le mot-clé SPATIAL. Pré-requis : Le moteur est MyISAM La colonne utilisée pour créer un index spatial doit être déclarée comme NON NULL

Mode index

Principe d'utilisation : Si la différence de valeurs. est grand et principalement basé sur la recherche de valeurs égales (=, <=>, in), l'index de hachage est un choix plus efficace, il a une complexité de recherche O(1) si la différence de valeurs est relative ; médiocre et la recherche par plage est principalement, B-tree est un meilleur choix, il prend en charge la recherche par plage.

Index B-Tree

L'index B-Tree a les capacités de recherche de plage et de recherche de préfixe Pour un arbre B avec N nœuds, la complexité de récupération d'un enregistrement est O(LogN). . Équivalent à la recherche binaire.

Index de hachage

L'index de hachage ne peut effectuer qu'une recherche égale, mais quelle que soit la taille de la table de hachage, la complexité de la recherche est O(1).

Création et suppression d'index

Création

Créer lors de la création d'un tableau

Exemple :

Création d'une table existante

CREATE TABLE 表名[字段名 数据类型]  [UNIQUE|FULLTEXT|SPATIAL|...] [INDEX|KEY] [索引名字] (字段名[length]) 　　[ASC|DESC]

Exemple :

CREATE TABLE `NewTable` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR (255) NOT NULL,
    `name` VARCHAR (255) NOT NULL,
    `sex` TINYINT NOT NULL DEFAULT 0,
    `address` VARCHAR (255) NULL,
    PRIMARY KEY (`id`), # 主键索引
    INDEX `name` (`name`) USING BTREE, # 普通索引
    UNIQUE INDEX `username` (`username`) USING BTREE # 唯一索引
    INDEX `u_n_a` (`username`,    `name`,`address`) USING BTREE # 组合索引
);

Supprimer l'index

ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]

Exemple :

ALTER TABLE `test`
ADD PRIMARY KEY (`id`),  # 主键索引
ADD INDEX `name` (`name`) USING BTREE , # 普通索引
ADD UNIQUE INDEX `username` (`username`) USING BTREE , # 唯一索引
ADD INDEX `u_n_a` (`username`, `name`, `address`) USING BTREE ; # 组合索引

Mettre à jour l'index

ALTER TABLE 表名 DROP INDEX 索引名。

Supprimer d'abord puis construire

ALTER TABLE `test`
DROP PRIMARY KEY,
DROP INDEX `username`,
DROP INDEX `name`,
DROP INDEX `u_n_a`;

Échec de l'index

1. Le champ combiné ne suit pas la règle du meilleur préfixe gauche

ALTER TABLE `test`
DROP INDEX `username` ,
ADD UNIQUE INDEX `username1` (`username`) USING BTREE ,
DROP INDEX `name` ,
ADD INDEX `name2` (`name`) USING BTREE ,
DROP INDEX `u_n_a` ,
ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;

2. Requête floue, telle que '%test

.

# 索引生效
select * from `test` where `name` like "123";
# 索引生效
select * from `test` where `name` like "123%";
# 索引失效
select * from `test` where `name` like "%123";
# 索引失效
select * from `test` where `name` like "%123%";

3. 在索引列上做如下任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描

如 sex 字段上添加索引

# 索引失效
select * from `test` where `sex`*0.5  = 1

4. 范围索引(>,<,between and)后，无法命中组合索引右边的列

构建索引

ALTER TABLE `test`
ADD INDEX `s_n` (`sex`, `name`) USING BTREE ;

示例：

# 命中全部
select * from `test` where `sex` = 1 and `name` = 'a';
# 命中部分，sex命中，name失效
select * from `test` where `sex` > 1 and `name` = 'a';</p>
<p style="white-space: normal;">5. !=, is null, is not null 无法使用索引</p>
<p style="white-space: normal;">6. 字符串字段的值不加单引号（数字不报错，英文报错）索引失效</p>
<p>构建索引</p>
<pre class="brush:php;toolbar:false">ALTER TABLE `test`
ADD INDEX `name` (`name`) USING BTREE ;

示例

# 索引失效
select * from `test` where `name`  =  123;
# 索引生效
select * from `test` where `name`  =  '123';

7. or 条件导致索引失效

构建索引

ALTER TABLE `test`
ADD INDEX `sex` (`sex`) USING BTREE ;
ADD INDEX `n_u` (`name`, `username`) USING BTREE ;

示例：

# 索引不生效
select * from `test` where (`name` = 'aa' and `username` = 'aa') or `sex` > 1 
# 索引sex生效
select * from `test` where `sex` = 1 and (`id` = 2 or `name` = 'aa' )

附录

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询要从索引的最左前列开始并且不跳过索引中的列

如下构建索引

ALTER TABLE `test` ADD INDEX `u_a_n` (`username`, `address`, `name`) USING BTREE ;

如下查询情况

# 命中部分
select * from `test` where `username` = 'aaa';
# 命中部分
select * from `test` where `username` = 'aa' and `address` = 'aaa';
# 全命中
select * from `test` where `username` = 'aa' and `address` = 'aaa' and `name` = 'a';
# 不命中，第一条件字段不是username
select * from `test` where  `address` = 'aaa';

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!