MySQL은 인덱싱에 의존할 수 있지만 아르바이트에만 의존할 수 있습니다....

mysql tutorial 컬럼에서는 관련 인덱스를 소개합니다.

1. 인덱스 데이터 구조

이 질문은 인터뷰에서 분명히 나올 질문입니다. b-tree와 같은 다른 인덱스를 선택하는 대신? 해시시?

아래에서 언급하는 디스크 IO는 하드디스크의 데이터를 메모리로 로딩하는 동작을 의미합니다.

• 해시 인덱스 는 범위 쿼리를 지원하지 않습니다. 해시는 값에 해당하는 키이므로, 범위 쿼리를 할 방법이 없습니다

• 이진 트리의 경우 왼쪽 하위 트리가 루트 노드보다 작고 오른쪽 하위 트리보다 작은 것이 특징입니다. 루트 노드의 값에 문제가 있으면 트리로 퇴화될 수 있습니다. 연결된 목록, 즉 트리가 분기되지 않고 트리가 그대로 유지됩니다. 왼쪽 또는 오른쪽으로 이동하므로 반으로 검색할 수 없으며 범위 쿼리 수를 줄이는 경우는 지원되지 않습니다. 범위 쿼리를 사용하면 매번 루트에서 탐색해야 합니다. 트리가 높을수록 IO 작업이 더 자주 발생합니다.

• 이진 트리가 균형을 이룬다면 , 왼쪽과 오른쪽 자식 노드의 차이가 최대 1 수준이므로 이진 트리가 연결 목록으로 변질되는 단점이 없지만 범위 검색과 이진 트리를 지원하지 않습니다. 문제는 동일합니다

• B-tree. 트리는 이진 트리에 비해 매우 짧고 굵으며, 다중 포크 트리이며 각 노드는 해당 행 데이터를 저장합니다. 만약 데이터 행의 열 수가 계속 증가하면 이 페이지에 저장되는 노드 수가 줄어들게 됩니다. 차지하는 공간이 계속 증가하기 때문에 트리의 키도 점점 커져 IO 작업 수가 늘어납니다. 동시에 범위 찾기는 지원되지 않습니다. 같은 크기의 공간에 많은 노드 데이터를 저장할 수 있으면 더 좋을 것 같으니 다음과 같은 b+ 트리가 있습니다

• b+ 트리 전체 행이 아닌 리프 노드에 대한 인덱스 데이터만 저장됩니다. 그러나 리프 노드는 중복되고 리프가 아닌 노드도 이중 연결 목록으로 연결되어 있어 순차 검색이 용이합니다. b-트리에 비해 b+ 트리는 더 짧고 굵습니다. 디스크 IO 수가 적습니다.

2. mysql의 인덱스 유형

• 클러스터형 인덱스 및 비클러스터형 인덱스

우리는 간단히 다음과 같이 이해할 수 있습니다. 클러스터형 인덱스는 기본 키 인덱스이고 비클러스터형 인덱스는 일반 인덱스입니다. 근본적인 차이점은 클러스터형 인덱스의 리프 노드가 전체 데이터 행을 저장한다는 것입니다.

innodb는 기본 키를 통해 클러스터링 인덱스를 구현합니다. 기본 키가 없으면 비어 있지 않은 고유 인덱스를 선택하여 클러스터링을 구현합니다. 인덱스

비클러스터형 인덱스 인덱스 값과 기본 키 값을 저장합니다.

• 일반 인덱스 테이블에는 여러 개의 일반 인덱스가 있을 수 있으며, 인덱스는 모든 필드에 설정할 수 있습니다. 보통 생성되는 인덱스는 대부분 일반 인덱스입니다

• 공동 인덱스여러 필드를 결합하여 생성되는 인덱스

• 고유 인덱스비즈니스에서 유일한 필드는 고유 인덱스 구축에 적합합니다. 테이블에는 여러 개의 고유 인덱스가 있을 수 있습니다

• 기본 키 인덱스 고유 인덱스와 마찬가지로 기본 키 인덱스도 고유하다는 점은 테이블에 기본 키 인덱스가 하나만 있을 수 있다는 것입니다

3. Indexed sql 소개

기본키 인덱스 생성

ALTER TABLE test add  PRIMARY  KEY (id)复制代码

고유 인덱스 생성

ALTER TABLE test add UNIQUE idx_id_card(id_card)复制代码

일반 인덱스 생성

ALTER TABLE test add INDEX idx_name(name)复制代码

공동 인덱스 생성

ALTER TABLE test add INDEX idx_age_name(age,name)复制代码

인덱스 이름 수정: 먼저 삭제 후 추가

색인 삭제(2가지 방법)

ALTER TABLE test DROP INDEX idx_id_cardDROP INDEX idx_id_card on test --删除主键索引DROP PRIMARY key on test  ALTER TABLE test DROP  PRIMARY key复制代码

테이블에서 색인 보기

SHOW INDEX FROM test复制代码

색인 분석

EXPLAIN SELECT * from test WHERE name = "xhJaver"复制代码

我们先给name字段添加一个索引,索引名字叫做idx_name

ALTER TABLE test add INDEX idx_name(name)复制代码

查看test表中的索引

SHOW INDEX FROM test复制代码

其中的属性

• table: 表名

• Non_unique: 能重复的话为1，不能重复的话为0，我们主键的那里是0，而name那里是1，因为name可以重复，而主键不能重复

• Key_name: 索引名称

• Seq_in_index:索引中列的顺序

• Column_name:列名称

• Collation:列以什么方式存储的，A升序，null无序

• Cardinality:数目越大，则使用该索引的可能性越大

• Sub_part:如果列只是部分的编入索引，则被编入索引的字符数目，如果整列被编入索引，则为null

• Packed：关键字是否被压缩，null表示没有被压缩

• Null:如果该列含有null,则为yes,如果没有null,则为no

• Index_type:索引数据结构

• Comment:多种评注

四、回表查询

select * from test where  name = "xhJaver"复制代码

假如说我们name字段建立了索引，然后当我们运行这一句sql语句的时候，因为建立的是普通索引，所以我们的b+树的叶子节点存储的数据是id，我们会找到name是xhJaver的这条记录的id，再根据这个id,去主键索引的那棵b+树去查询，查询到叶子节点时即查询出这条记录，可见这个过程中，我们从一棵树跑到了另一棵树继续查，这样就叫做“回表查询”，那有没有办法只查一棵树就可以查询出结果呢？

五、覆盖索引

办法当然是有的啦，那就是覆盖索引，我们注意到，刚才这个sql语句时查询出来了所有元素，假如说我们这样写的话

select address from test where  name = "xhJaver"复制代码

假如说我们建立的索引是（name,address）那么这个时候（name,address）这棵b+树的叶子节点存储的数据就包括address了，此时就不需要再根据name = "xhJaver"的id去第二棵树查了，这样就避免了回表查询

六、最左匹配原则

假如说现在我们写一个这样的sql语句

select *  from test where  name = "xhJaver" and age =23  and address="京东"复制代码

并且我们建立的索引是（name,address,age）这样是会用到（name,address,age）索引的，可是如果要这样写的话

select *  from test where  name = "xhJaver" and age >23  and address="京东"复制代码

这样只会用到（name，age）这两个索引，从左边开始匹配，如果要是遇到范围查询的话，则不继续往右匹配索引

七、explain分析索引语句

我们用explain语句解析一下下面这条sql语句

EXPLAIN SELECT * from test WHERE name = "xhJaver"复制代码

它的属性有

id: 执行的顺序

• id相同时，顺序从上到下执行
• id不同时，id大的先执行

select_type:  查询的类型

• primary: 最外层的查询被标记为primary
• simple:  简单查询，没有关联其他表，就一张表
• subquery: 在where或者select中的子查询
• derived: 衍生虚拟表  例如from(子查询) t,这个子查询的结果就被放在虚拟表t中

table:  关于哪张表的

partitions:  分区相关（还没搞懂呜呜呜）

type:访问类型

性能由好至坏依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说，好的sql查询至少达到range级别，最好能达到ref

• system: 테이블에 데이터 행이 하나만 있습니다.

• const: 상수 쿼리는 일반적으로 기본 키가 상수와 같은지 비교하는 데 사용되며 인덱스 쿼리로 찾을 수 있습니다. 한 번

• eq_ref: 고유 인덱스, 각 인덱스는 기본 키 인덱스와 같은 데이터 조각에 해당합니다.

• ref: 고유하지 않은 인덱스, 각 인덱스는 여러 데이터 행에 해당할 수 있습니다. 일반 인덱스로

• range: 범위 쿼리, Used>, 52498c3a525be269f0ffb5e2e7707304,3730409ab0102a19484a1261b4c3727d, like "%xxx" 索引会失效

但是用覆盖索引就可以解决 like左模糊查询走不到索引的情况 如果只select索引字段，或者select索引字段和主键，也会走索引的。

更多相关免费学习推荐：mysql教程(视频)

위 내용은 MySQL은 인덱싱에 의존할 수 있지만 아르바이트에만 의존할 수 있습니다....의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!