Compréhension approfondie des principes et de la mise en œuvre des index MySQL, ainsi que de la récupération rapide des bases de données.

Recommandations d'apprentissage gratuites : tutoriel vidéo mysql

1. Le concept d'index

1. L'index est une liste triée, dans laquelle la valeur de l'index et l'adresse physique de la ligne contenant les données contenant la valeur sont stockées (similaire au C). langue La liste chaînée pointe vers l'adresse mémoire de l'enregistrement de données via un pointeur).

2. Après avoir utilisé l'index, vous n'avez pas besoin de parcourir la table entière pour localiser les données d'une certaine ligne, au lieu de cela, vous trouvez d'abord l'adresse physique correspondant à la ligne de données via la table d'index et. accédez ensuite aux données correspondantes, accélérant ainsi la vitesse de requête de la base de données.

3. L'index est comme la table des matières d'un livre. Vous pouvez trouver rapidement le contenu dont vous avez besoin en fonction des numéros de page dans la table des matières.

4. L'index est une méthode de tri des valeurs d'une ou plusieurs colonnes d'un tableau.

5. Le but de l'établissement d'un index est d'accélérer la recherche ou le tri des enregistrements dans le tableau.

2. Le rôle de l'index

1. Après avoir configuré un index approprié, la base de données peut considérablement accélérer la requête en utilisant diverses technologies de positionnement rapide. pour créer un index. La raison principale.
2. Lorsque la table est volumineuse ou que la requête implique plusieurs tables, l'utilisation d'index peut augmenter la vitesse de requête de milliers ou de dizaines de milliers de fois.
3. Cela peut réduire le coût d'E/S de la base de données, et l'index peut également réduire le coût de tri de la base de données.
4. En créant un index unique, le caractère unique de chaque ligne de données dans la table de données peut être garanti.
5. Peut accélérer la connexion entre les tables.
6. Lors de l'utilisation du regroupement et du tri, le temps de regroupement et de tri peut être considérablement réduit.

3. Effets secondaires de l'indexation

1. Les index nécessitent de l'espace disque supplémentaire.

• Pour le moteur MyISAM, le fichier d'index et le fichier de données sont séparés, et le fichier d'index est utilisé pour enregistrer l'adresse de l'enregistrement de données.
• Le fichier de données de table du moteur InnoDB lui-même est un fichier d'index.

2. L'insertion et la modification des données prennent plus de temps, car l'index changera également en conséquence.

4. Principes de création d'index

Les index peuvent augmenter la vitesse des requêtes dans les bases de données, mais ils ne conviennent pas pour créer des index dans toutes les circonstances. Étant donné que l'index lui-même consomme des ressources système, s'il existe un index, la base de données effectuera d'abord une requête d'index, puis localisera la ligne de données spécifique. Si l'index est mal utilisé, cela augmentera la charge de la base de données.

1. La clé primaire et la clé étrangère de la table doivent avoir des index. Étant donné que la clé primaire est unique et que la clé étrangère est associée à la clé primaire de la table principale, elle peut être rapidement localisée lors de l'interrogation.
2. Les tables contenant plus de 300 lignes d'enregistrements doivent avoir des index. S'il n'y a pas d'index, chaque requête doit parcourir la table, ce qui affectera sérieusement les performances de la base de données.
3. Les tables fréquemment connectées à d'autres tables doivent créer des index sur les champs de connexion.
4. Les champs avec un faible caractère unique ne conviennent pas à l'indexation.
5. Les champs mis à jour trop fréquemment ne conviennent pas à la création d'index.
6. Les champs qui apparaissent souvent dans les clauses Where, en particulier les champs des grandes tables, doivent être indexés.
7. Les index doivent être construits sur des champs hautement sélectifs.
8. Les index doivent être construits sur de petits champs. Ne créez pas d'index sur des champs de texte volumineux ou même des champs très longs.

5. Classification et création d'index

在做之前先用这张表做示范
create table member (id int(10),name varchar(10),Cardid varchar(10),phone int(11),address varchar(50),remark text);

(1) Indice ordinaire

Indice ordinaire : l'index le plus basique type, aucune restriction telle que l'unicité.

1. Créer un index directement

CREATE INDEX  索引名  ON  表名 （列名[(length)]）；

#(列名(length))：length是可选项，下同。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾。

2. Créer un index en modifiant la table

ALTER TABLE 表名 ADD INDEX 索引名 (列名);例：alter table member ADD INDEX phone_index (phone);select phone from member;

3. Lors de la création du tableau, précisez l'index

CREATE TABLE 表名 ( 字段1 数据类型,字段2 数据类型[,...],INDEX 索引名 (列名));例：create table test(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));show create table test;

(2), index unique

与普通索引类似，但区别是唯一索引列的每个值都唯一。
唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

1. indexer directement

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

2 Créer en modifiant le tableau

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);例：alter table member add unique name_index (cardid);

Afin de faciliter l'expérimentation. , j'ai d'abord supprimé l'index précédent

3、创建表的时候指定

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

（三）、主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。
一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。

• ALTER TABLE 表名 ADD PRIMARY KEY (列名);

（四）、组合索引（单列索引与多列索引）

组合索引:可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为 select语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名(列名1,列名2,列名3));select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

（五）、全文索引

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。
在 MySQL5.6 版本以前FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);例：select * from member;create fulltext index name_index on member (name);

• 修改表的方式创建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

• 创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));

#数据类型可以为 CHAR、VARCHAR 或者 TEXT

• 使用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');例：select * from member where match(remark) against('this is vip');

六、查看索引

show index from 表名;show index from 表名\G； 竖向显示表索引信息
show keys from 表名;show keys from 表名\G;

Table	表的名称
Non_unique	如果索引不能包括重复词，则为 0；如果可以，则为 1。
Key_name	索引的名称。
Seq_in_index	索引中的列序号，从 1 开始。
Column_name	列名称。
Collation	列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）
Cardinality	索引中唯一值数目的估计值。
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL。
Packed	指示关键字如何被压缩。如果没有被压缩，则为 NULL
Null	如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）
Comment	备注

七、删除索引

1、直接删除索引
DROP INDEX 索引名 ON 表名;2、修改表方式删除索引
ALTER TABLE 表名 DROP INDEX 索引名;3、删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

八、实例

案例：为某商场做一个会员卡的系统。这个系统有一个会员表，有下列字段:会员编号  INT
会员姓名  VARCHAR(10)会员身份证号码  VARCHAR(18)会员电话  INT(11)会员住址  VARCHAR (50)会员备注信息  TEXT 

create table member (id int(10),name varchar(10),cardid varchar(18),phone int(11),address varchar(50),remark text);alter table member add primary key (id);create index name_index on member (name); create unique index cardid_index on member (cardid);alter table member add fulltext remark_index (remark);会员编号，作为主键，使用 PRIMARY KEY
会员姓名,如果要建索引的话，那么就是普通的 INDEX
会员身份证号码,如果要建索引的话，那么可以选择 UNIQUE (唯一的，不允许重复)会员备注信息，如果需要建索引的话，可以选择 FULLTEXT，全文搜索。
不过 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

更多相关免费学习推荐：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!