MySQL 인덱스 원리 및 구현, 신속한 데이터베이스 검색에 대한 심층적인 이해

무료 학습 추천: mysql 동영상 튜토리얼

1. 인덱스의 개념

1. 인덱스는 정렬된 목록입니다. 이 값의 데이터가 위치한 행의 물리적 주소가 저장됩니다(포인터를 통해 데이터 레코드의 메모리 주소를 가리키는 C 언어의 연결 리스트와 유사).

2. 인덱스를 사용한 후, 특정 행의 데이터를 찾기 위해 전체 테이블을 스캔할 필요는 없습니다. 대신, 먼저 인덱스 테이블을 통해 해당 데이터 행에 해당하는 물리적 주소를 찾은 다음 해당하는 주소에 액세스합니다. 데이터를 전송하므로 데이터베이스의 쿼리 속도가 빨라집니다.

3. 색인은 책의 목차와 같습니다. 목차에 있는 페이지 번호를 기준으로 필요한 내용을 빠르게 찾을 수 있습니다.

4. 인덱스는 테이블에 있는 하나 또는 여러 열의 값을 정렬하는 방법입니다.

5. 인덱스를 설정하는 목적은 테이블의 레코드 검색 또는 정렬 속도를 높이는 것입니다.

2. 인덱스의 역할

1. 데이터베이스는 적절한 인덱스를 설정한 후 다양한 빠른 위치 지정 기술을 사용하여 쿼리 속도를 크게 높일 수 있습니다.
2. 테이블이 크거나 쿼리에 여러 테이블이 포함된 경우 인덱스를 사용하면 쿼리 속도가 수천 배 또는 수만 배 향상될 수 있습니다.
3. 데이터베이스의 I/O 비용을 줄일 수 있고, 인덱스를 사용하면 데이터베이스의 정렬 비용도 줄일 수 있습니다.
4. 고유 인덱스를 생성하면 데이터 테이블의 각 데이터 행에 대한 고유성이 보장됩니다.
5. 테이블 간의 연결 속도를 높일 수 있습니다.
6. 그룹화 및 정렬을 사용하면 그룹화 및 정렬하는 시간을 크게 줄일 수 있습니다.

3. 인덱싱의 부작용

1. 인덱스에는 추가 디스크 공간이 필요합니다.

• MyISAM 엔진의 경우 인덱스 파일과 데이터 파일이 분리되어 있으며, 인덱스 파일은 데이터 레코드의 주소를 저장하는 데 사용됩니다.
• InnoDB 엔진 자체의 테이블 데이터 파일은 인덱스 파일입니다.

2. 인덱스도 그에 따라 변경되므로 데이터를 삽입하고 수정하는 데 더 많은 시간이 걸립니다.

4. 인덱스 생성 원리

인덱스는 데이터베이스 쿼리 속도를 높일 수 있지만 모든 상황에서 인덱스를 생성하는 데 적합하지는 않습니다. 인덱스 자체가 시스템 자원을 소모하기 때문에 인덱스가 있는 경우 데이터베이스는 먼저 인덱스 쿼리를 수행한 후 특정 데이터 행을 찾게 된다. 인덱스를 잘못 사용하면 데이터베이스에 부담이 커진다.

1. 테이블의 기본 키와 외래 키에는 인덱스가 있어야 합니다. 기본 키는 고유하고 외래 키는 기본 테이블의 기본 키와 연결되어 있으므로 쿼리 중에 빠르게 찾을 수 있습니다.
2. 300행 이상의 레코드가 있는 테이블에는 인덱스가 있어야 합니다. 인덱스가 없으면 각 쿼리는 테이블을 순회해야 하며 이는 데이터베이스 성능에 심각한 영향을 미칩니다.
3. 다른 테이블과 자주 연결되는 테이블은 연결 필드에 인덱스를 생성해야 합니다.
4. 고유성이 낮은 필드는 인덱싱에 적합하지 않습니다.
5. 너무 자주 업데이트되는 필드는 인덱스 생성에 적합하지 않습니다.
6. where 절에 자주 나타나는 필드, 특히 큰 테이블의 필드는 색인을 생성해야 합니다.
7. 인덱스는 매우 선택적인 필드에 구축되어야 합니다.
8. 인덱스는 작은 필드에 구축해야 합니다. 큰 텍스트 필드나 매우 긴 필드에 인덱스를 구축하지 마세요.

5. 인덱스의 분류 및 생성

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

(1) 일반 인덱스

일반 인덱스: 고유성 등의 제한이 없는 가장 기본적인 인덱스 유형입니다.索1. 인덱스 직접 생성

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

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

E

2, 테이블을 수정하여

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

E 생성

3. 테이블 생성 시 인덱스 R

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;

E

(2)를 지정하고, 인덱스만 R

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

E로 지정합니다. 1. 고유 인덱스를 직접 생성

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

2. 테이블을 수정하여 생성

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

실험을 원활하게 하기 위해 먼저 이전 인덱스를 삭제했습니다

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

위 내용은 MySQL 인덱스 원리 및 구현, 신속한 데이터베이스 검색에 대한 심층적인 이해의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!