Umfassendes Verständnis der MySQL-Indexprinzipien und -Implementierung sowie schnelles Abrufen von Datenbanken

Kostenlose Lernempfehlung: MySQL-Video-Tutorial

1. Das Konzept des Index

1. Ein Index ist eine sortierte Liste, in der der Wert des Index und sein Inhalt angegeben sind Die physische Adresse der Zeile, in der sich die Daten dieses Werts befinden, wird gespeichert (ähnlich der verknüpften Liste in der C-Sprache, die über einen Zeiger auf die Speicheradresse des Datensatzes zeigt).

2. Nach der Verwendung des Index müssen Sie nicht die gesamte Tabelle durchsuchen, um die Daten einer bestimmten Zeile zu finden. Stattdessen finden Sie zunächst die der Datenzeile entsprechende physische Adresse und greifen dann auf die entsprechende zu Daten, wodurch die Abfragegeschwindigkeit der Datenbank beschleunigt wird.

3. Der Index ist wie das Inhaltsverzeichnis eines Buches. Anhand der Seitenzahlen im Inhaltsverzeichnis können Sie schnell den benötigten Inhalt finden.

4. Index ist eine Methode zum Sortieren der Werte einer oder mehrerer Spalten in einer Tabelle.

5. Der Zweck der Erstellung eines Index besteht darin, die Suche oder Sortierung von Datensätzen in der Tabelle zu beschleunigen.

2. Die Rolle des Index

1. Nach dem Einrichten eines geeigneten Index kann die Datenbank verschiedene schnelle Positionierungstechnologien verwenden, um die Abfrage erheblich zu beschleunigen.
2. Wenn die Tabelle groß ist oder die Abfrage mehrere Tabellen umfasst, kann die Verwendung von Indizes die Abfragegeschwindigkeit um das Tausend- oder Zehntausendfache erhöhen.
3. Es kann die E/A-Kosten der Datenbank reduzieren, und der Index kann auch die Sortierkosten der Datenbank reduzieren.
4. Durch die Erstellung eines eindeutigen Index kann die Eindeutigkeit jeder Datenzeile in der Datentabelle garantiert werden.
5. Kann die Verbindung zwischen Tabellen beschleunigen.
6. Durch die Verwendung von Gruppierung und Sortierung kann die Zeit für das Gruppieren und Sortieren erheblich verkürzt werden.

3. Nebenwirkungen der Indizierung

1.

• Für die MyISAM-Engine werden die Indexdatei und die Datendatei getrennt, und die Indexdatei wird zum Speichern der Adresse des Datensatzes verwendet.
• Die Tabellendatendatei der InnoDB-Engine selbst ist eine Indexdatei.

2. Das Einfügen und Ändern von Daten nimmt mehr Zeit in Anspruch, da sich auch der Index entsprechend ändert.

4. Prinzipien der Indexerstellung

Indizes können die Geschwindigkeit von Datenbankabfragen erhöhen, sie sind jedoch nicht unter allen Umständen für die Erstellung von Indizes geeignet. Da der Index selbst Systemressourcen verbraucht, führt die Datenbank bei einem Index zunächst eine Indexabfrage durch und sucht dann nach der spezifischen Datenzeile. Bei unsachgemäßer Verwendung des Index erhöht sich die Belastung der Datenbank.

1. Der Primärschlüssel und der Fremdschlüssel der Tabelle müssen Indizes haben. Da der Primärschlüssel eindeutig ist und der Fremdschlüssel mit dem Primärschlüssel der Haupttabelle verknüpft ist, kann er bei der Abfrage schnell gefunden werden.
2. Tabellen mit mehr als 300 Datensatzzeilen sollten über Indizes verfügen. Wenn kein Index vorhanden ist, muss jede Abfrage die Tabelle durchlaufen, was die Leistung der Datenbank erheblich beeinträchtigt.
3. Tabellen, die häufig mit anderen Tabellen verbunden sind, sollten Indizes für die Verbindungsfelder erstellen.
4. Felder mit schlechter Eindeutigkeit sind nicht für die Indizierung geeignet.
5. Felder, die zu häufig aktualisiert werden, sind nicht für die Erstellung von Indizes geeignet.
6. Felder, die häufig in WHERE-Klauseln vorkommen, insbesondere Felder in großen Tabellen, sollten indiziert werden.
7. Indizes sollten auf hochselektiven Feldern aufgebaut sein.
8. Indizes sollten auf kleinen Feldern erstellt werden. Erstellen Sie keine Indizes auf großen Textfeldern oder sogar auf sehr langen Feldern.

5. Klassifizierung und Erstellung von Indizes

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

(1) Gewöhnlicher Index

Gewöhnlicher Index: Der grundlegendste Indextyp, ohne Einschränkungen wie Eindeutigkeit.索 1.

1. Erstellen Sie direkt einen eindeutigen Index

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教程(视频)

Das obige ist der detaillierte Inhalt vonUmfassendes Verständnis der MySQL-Indexprinzipien und -Implementierung sowie schnelles Abrufen von Datenbanken. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!