MySQL용 고성능 인덱스를 생성하는 방법

1 인덱스 기본

1.1 인덱스 함수

MySQL에서는 데이터를 검색할 때 먼저 인덱스에서 해당 값을 찾은 다음, 일치하는 인덱스 레코드를 기반으로 해당 데이터 행을 찾으면 됩니다. 다음 쿼리 문을 실행하세요:

SELECT	* FROM  USER  WHERE uid = 5;

uid에 구축된 인덱스가 있는 경우 MySQL은 인덱스를 사용하여 먼저 uid 5가 있는 행을 찾습니다. 이는 MySQL이 먼저 인덱스의 값으로 검색한 다음 모든 데이터를 반환한다는 의미입니다. 이 값을 포함하는 행입니다.

1.2 MySQL 인덱스의 공통 데이터 구조

MySQL 인덱스는 서버가 아닌 스토리지 엔진 수준에서 구현됩니다. 따라서 통합된 인덱싱 표준이 없습니다. 서로 다른 스토리지 엔진의 인덱스는 다르게 작동합니다.

1.2.1 B-Tree

대부분의 MySQL 엔진은 이러한 종류의 인덱스 B-트리를 지원합니다. 여러 스토리지 엔진이 동일한 유형의 인덱스를 지원하더라도 기본 구현은 다를 수 있습니다. 예를 들어 InnoDB는 B+Tree를 사용합니다.

스토리지 엔진은 다양한 성능과 장점을 지닌 다양한 방식으로 B-Tree를 구현합니다. 예를 들어 MyISAM은 접두사 압축 기술을 사용하여 인덱스를 더 작게 만들고, InnoDB는 원래 데이터 형식에 따라 데이터를 저장하는 반면, MyISAM 인덱스는 데이터의 물리적 위치에 따라 인덱스된 행을 참조합니다. 요소.

B-Tree의 모든 값은 순차적으로 저장되며, 각 리프 페이지에서 루트까지의 거리는 동일합니다. 다음 그림은 InnoDB 인덱스의 작동 방식을 대략적으로 보여줍니다. MyISAM에서 사용하는 구조는 다릅니다. 그러나 기본 구현은 비슷합니다.

예제 다이어그램 설명:

각 노드는 하나의 디스크 블록을 차지합니다. 한 노드에는 두 개의 오름차순 키워드가 있고 하위 트리의 루트 노드에 대한 세 개의 포인터가 하위 노드가 있는 디스크 블록을 저장합니다. 의 주소입니다. 두 개의 키워드로 나누어진 세 개의 범위 필드는 세 개의 포인터가 가리키는 하위 트리의 데이터의 범위 필드에 해당합니다. 루트 노드를 예로 들면 키워드는 16과 34이고, P1 포인터가 가리키는 하위 트리의 데이터 범위는 16 미만이고, P2 포인터가 가리키는 하위 트리의 데이터 범위는 16~34이며, 데이터는 P3 포인터가 가리키는 하위 트리의 범위가 34보다 큽니다. 키워드 검색 과정:

• 루트 노드를 기준으로 디스크 블록 1을 찾아 메모리로 읽어옵니다. [디스크 I/O 작업 1차]

• 비교 키워드 28 간격(16,34)에서 디스크 블록 1의 포인터 P2를 찾습니다.

• P2 포인터를 기준으로 디스크 블록 3을 찾아 메모리로 읽어옵니다. [디스크 I/O 작업 2차]

• 비교 키워드 28 간격(25,31)에서 디스크 블록 3의 포인터 P2를 찾습니다.

• P2 포인터를 기준으로 디스크 블록 8을 찾아 메모리로 읽어옵니다. [디스크 I/O 작업 3번째]

• 디스크 블록 8의 키워드 목록에서 키워드 28을 찾았습니다.

단점:

• 각 노드에는 키가 있고 데이터도 포함되어 있으며, 각 페이지의 저장 공간이 제한되어 있으므로 데이터가 상대적으로 크면 각 노드에 저장되는 키 수가 작아집니다. ;

• 저장된 데이터의 양이 많으면 깊이가 커지고 쿼리 중 디스크 IO 횟수가 늘어나 쿼리 성능에 영향을 미칩니다.

1.2.2 B+트리 인덱스

B+ 트리는 B-트리의 변형입니다. B-트리와의 차이점: B+ 트리는 리프 노드에만 데이터를 저장하고, 리프가 아닌 노드에는 키 값과 포인터만 저장합니다.

B+ 트리에는 두 개의 포인터가 있는데, 하나는 루트 리프 노드를 가리키고 다른 하나는 가장 작은 키워드가 있는 리프 노드를 가리키며, 모든 리프 노드(즉, 데이터 노드) 사이에는 체인 링 구조가 있으므로 B+ can 트리는 두 가지 검색 작업을 수행합니다. 하나는 구성 요소에 대한 범위 검색이고 다른 하나는 루트 노드에서 시작하는 무작위 검색입니다.

B* 트리는 B+ 번호와 유사하지만, 차이점은 B* 번호도 리프가 아닌 노드 사이에 체인 링 구조를 갖는다는 것입니다.

1.2.3 해시 인덱스

해시 인덱스는 해시 테이블을 기반으로 구현되며 인덱스의 모든 열을 정확하게 일치하는 쿼리만 유효합니다. 각 데이터 행에 대해 스토리지 엔진은 모든 인덱스 열에 대한 해시 코드를 계산합니다. 해시 코드는 더 작은 값이며 다른 키 값을 가진 행에 대해 계산된 해시 코드도 다릅니다. 해시 인덱스는 인덱스의 모든 해시 코드와 해시 테이블의 각 데이터 행에 대한 포인터를 저장합니다.

MySQL에서는 Memory의 기본 인덱스 유형만 해시 인덱스를 사용하며, 메모리는 B-Tree 인덱스도 지원합니다. 동시에 메모리 엔진은 고유하지 않은 해시 인덱스를 지원합니다. 여러 열의 해시 값이 동일한 경우 인덱스는 연결된 목록의 동일한 해시 항목에 여러 포인터를 저장합니다. 해시맵과 유사합니다.

장점:
인덱스 자체는 해당 해시 값만 저장하면 되므로 인덱스 구조가 매우 컴팩트하고 해시 검색 속도가 매우 빠릅니다.
단점:

• 해시 저장소를 사용하는 경우 모든 데이터 파일을 메모리에 추가해야 하며 이로 인해 더 많은 메모리 공간이 소모됩니다.

• 해시 인덱스 데이터가 순서대로 저장되지 않으므로 사용할 수 없습니다.

  정렬용

• 모든 쿼리가 등가 쿼리라면 해싱은 정말 빠르지만, 기업이나 실제 작업 환경에서는 등가 쿼리보다 범위 내에서 검색해야 할 데이터가 더 많아 해싱이 적합하지 않습니다.

• 해시 충돌이 많으면 인덱스 유지 관리 비용도 매우 높을 것입니다. 이는 HashMap이 이후 단계에서 레드-블랙 트리를 추가하여 해시 충돌을 해결하는 문제이기도 합니다.

2 고성능 인덱스; 전략

2.1 클러스터형 인덱스와 비클러스터형 인덱스

클러스터형 인덱스

는 별도의 인덱스 유형이 아니라 데이터 저장 방식입니다. InnoDB 스토리지 엔진에서 클러스터형 인덱스는 실제로 키 값과 데이터 행을 같은 구조. 테이블에 클러스터형 인덱스가 있는 경우 해당 데이터 행은 실제로 인덱스의 리프 페이지에 저장됩니다. 데이터 행은 동시에 여러 위치에 저장될 수 없기 때문에 테이블에는 하나의 클러스터형 인덱스만 있을 수 있습니다(인덱스 적용 범위는 여러 클러스터형 인덱스의 상황을 시뮬레이션할 수 있음).

클러스터형 인덱스의 장점:

인덱스와 데이터가 동일한 트리에 저장되므로 데이터 액세스가 더 빠릅니다. 포함 인덱스 스캔을 사용하는 쿼리는 기본 페이지 노드 키 값을 직접 사용할 수 있습니다.

단점:

클러스터형 데이터는 IO 집약적 애플리케이션의 성능을 극대화합니다. 데이터가 모두 메모리에 있는 경우 클러스터형 인덱스는 기본 키에 따라 삽입 순서에 따라 크게 달라지지 않습니다. 가장 빠른 방법은 업데이트된 각 행을 새로운 위치로 이동시키기 때문에 비용이 많이 듭니다. 클러스터형 인덱스 기반 테이블은 행을 이동해야 할 때 문제를 일으킬 수 있습니다. 페이지 분할 문제가 발생할 수 있습니다. 특히 행이 희박하거나 페이지 분할로 인해 데이터 저장이 중단되는 경우 클러스터형 인덱스로 인해 전체 테이블 검색 속도가 느려질 수 있습니다. 별도로 저장

2.2 접두사 인덱스

매우 긴 문자열을 인덱스해야 하는 경우가 있는데, 이로 인해 인덱스가 크고 느려지는 경우가 많습니다. 일반적으로 열 시작 부분에 문자열의 일부를 사용하면 인덱스 공간이 크게 절약됩니다. 이를 통해 인덱스 효율성은 향상되지만 인덱스 선택성은 감소합니다. 인덱스 선택성은 전체 데이터 테이블 레코드 수에 대한 고유 인덱스 값(카디널리티라고도 함)의 비율을 의미하며 범위는 T와 1 사이입니다. 인덱스의 선택성이 높을수록 쿼리 효율성도 높아집니다. 인덱스의 선택성이 높을수록 MySQL은 검색 시 더 많은 행을 필터링할 수 있기 때문입니다.

일반적으로 특정 열 접두사의 선택도는 쿼리 성능을 충족할 만큼 높습니다. 그러나 BLOB, TEXT, VARCHAR 유형의 열의 경우 MySQL에서는 전체 길이에 대한 인덱싱을 허용하지 않으므로 접두사 인덱스를 사용해야 합니다. 이 방법을 사용하는 비결은 높은 선택성을 보장할 만큼 길지만 너무 길지는 않은 접두사를 선택하는 것입니다.

예제

테이블 구조 및 데이터는 MySQL 공식 웹사이트 또는 GitHub에서 다운로드하세요.

도시 테이블 열

필드 이름

의미city_id도시 기본 키 IDcity도시 이름국가 _id국가 IDlast_update :작성 또는 마지막 업데이트 시간

--计算完整列的选择性
select count(distinct left(city,3))/count(*) as sel3,
    count(distinct left(city,4))/count(*) as sel4,
    count(distinct left(city,5))/count(*) as sel5,
    count(distinct left(city,6))/count(*) as sel6,
    count(distinct left(city,7))/count(*) as sel7,
    count(distinct left(city,8))/count(*) as sel8 
from citydemo;

可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了。由此最佳创建前缀索引长度为7。

2.3 回表

要理解回表需要先了解聚族索引和普通索引。聚族索引即建表时设置的主键索引，如果没有设置MySQL自动将第一个非空唯一值作为索引，如果还是没有InnoDB会创建一个隐藏的row-id作为索引（oracle数据库row-id显式展示，可以用于分页）；普通索引就是给普通列创建的索引。普通列索引在叶子节点中存储的并不是整行数据而是主键，当按普通索引查找时会先在B+树中查找该列的主键，然后根据主键所在的B+树中查找改行数据，这就是回表。

2.4 覆盖索引

覆盖索引在InnoDB中特别有用。MySQL中可以使用索引直接获取列的数据，如果索引的叶子节点中已经包含要查询的数据，那么就没必要再回表查询了，如果一个索引包含（覆盖）所有需要查询的字段的值，那么该索引就是覆盖索引。简单的说：不回表直接通过一次索引查找到列的数据就叫覆盖索引。

表信息

CREATE TABLE `t_user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

举例

--将uid设置成主键索引后通过下面的SQL查询 在explain的Extra列可以看到“Using index”
explain select uid from t_user where uid = 1;

覆盖索引在组合索引中用的比较多，举例

explain select age,uname from t_user where age = 10 ;

当不建立组合索引时，会进行回表查询

设置组合索引后再次查询

create index index_user on t_user(age,uname);

2.5 索引匹配方式

2.5.1 最左匹配

在使用组合索引中，比如设置(age,name)为组合索引，单独使用组合索引中最左列是可以匹配索引的，如果不使用最左列则不走索引。例如下面SQL

--走索引
explain select * from t_user where age=10 and uname='zhang';

下面的SQL不走索引

explain select * from t_user where  uname='zhang';

2.5.2 匹配列前缀

可以匹配某一列的值的开头部分，比如like 'abc%'。

2.5.3 匹配范围值

可以查找某一个范围的数据。

explain select * from t_user where age>18;

2.5.4 精确匹配某一列并范围匹配另外一列

可以查询第一列的全部和第二列的部分

explain select * from t_user where age=18 and uname like 'zhang%';

2.5.5 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引。

explain select age,uname,update_time from t_user 
            where age=18 and uname= 'zhang' and update_time='123';

3 索引优化最佳实践

1. 当使用索引列进行查询的时候尽量不要使用表达式，把计算放到业务层而不是数据库层。

--推荐
select uid,age,uname from t_user where uid=1;

--不推荐
select uid,age,uname from t_user where uid+9=10;

2. 尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询

3. 使用前缀索引参考2.2 前缀索引
4. 使用索引扫描排序mysql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢。
mysql可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务。
只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当orderby子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序操作，而无法利用索引排序。
举例表结构及数据MySQL官网或GItHub下载。

CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

rental表在rental_date,inventory_id,customer_id上有rental_date的索引。使用rental_date索引为下面的查询做排序

--该查询为索引的第一列提供了常量条件，而使用第二列进行排序，将两个列组合在一起，就形成了索引的最左前缀
explain select rental_id,staff_id from rental 
where rental_date='2005-05-25' order by inventory_id desc

--下面的查询不会利用索引
explain select rental_id,staff_id from rental 
where rental_date>'2005-05-25' order by rental_date,inventory_id

5. union all,in,or都能够使用索引，但是推荐使用in

explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2;
explain select * from actor where actor_id in (1,2);
explain select * from actor where actor_id = 1 or actor_id =2;

6. 范围列可以用到索引范围条件是：<、<=、>、>=、between。范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列。

7. 更新十分频繁，数据区分度不高的字段上不宜建立索引

• 更新会变更B+树，更新频繁的字段建议索引会大大降低数据库性能；

• 类似于性别这类区分不大的属性，建立索引是没有意义的，不能有效的过滤数据；

• 一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算；

8. 创建索引的列，不允许为null，可能会得到不符合预期的结果

9.当需要进行表连接的时候，最好不要超过三张表，如果需要join的字段，数据类型必须一致

10. 能使用limit的时候尽量使用limit

11. 单表索引建议控制在5个以内

12. 单索引字段数不允许超过5个（组合索引）

13. 创建索引的时候应该避免以下错误概念

• 索引越多越好

• 过早优化，在不了解系统的情况下进行优化

4 索引监控

show status like 'Handler_read%';

参数	说明
Handler_read_first	读取索引第一个条目的次数
Handler_read_key	通过index获取数据的次数
Handler_read_last	读取索引最后一个条目的次数
Handler_read_next	通过索引读取下一条数据的次数
Handler_read_prev	通过索引读取上一条数据的次数
Handler_read_rnd	从固定位置读取数据的次数
Handler_read_rnd_next	从数据节点读取下一条数据的次数

위 내용은 MySQL용 고성능 인덱스를 생성하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!