이 글에서는 mysql에 대한 관련 지식을 소개하는데, 인덱스 푸시다운에 대한 관련 내용을 주로 소개하고 있습니다. 인덱스 조건 푸시다운은 Index Condition Pushdown, 줄여서 ICP라고도 합니다. 데이터 쿼리를 최적화하는 것이 모든 사람에게 도움이 되기를 바랍니다.
추천 학습: mysql 비디오 튜토리얼
SELECT 문 실행 프로세스
MySQL
데이터베이스는 Server
레이어와 Engine
으로 구성됩니다. > 레이어 구성: MySQL
数据库由 Server
层和 Engine
层组成:
-
Server
层: 有SQL
分析器、SQL
优化器、SQL
执行器,用于负责SQL
语句的具体执行过程。 -
Engine
层: 负责存储具体的数据,如最常使用的InnoDB
存储引擎,还有用于在内存中存储临时结果集的TempTable
引擎。
通过客户端/服务器通信协议与
MySQL
建立连接。-
查询缓存:
- 如果开启了
Query Cache
且在查询缓存过程中查询到完全相同的SQL
语句,则将查询结果直接返回给客户端; - 如果没有开启
Query Cache
或者没有查询到完全相同的SQL
语句则会由解析器进行语法语义解析,并生成解析树。
- 如果开启了
分析器生成新的解析树。
查询优化器生成执行计划。
-
查询执行引擎执行
SQL
语句,此时查询执行引擎会根据SQL
语句中表的存储引擎类型,以及对应的API
接口与底层存储引擎缓存或者物理文件的交互情况,得到查询结果,由MySQL Server
过滤后将查询结果缓存并返回给客户端。若开启了
Query Cache
,这时也会将SQL
语句和结果完整地保存到Query Cache
中,以后若有相同的SQL
语句执行则直接返回结果。
Tips
:MySQL 8.0
已去掉 query cache
(查询缓存模块)。
因为查询缓存的命中率会非常低。 查询缓存的失效非常频繁:只要有对一个表的更新,这个表上所有的查询缓存都会被清空。
什么是索引下推?
索引下推(Index Condition Pushdown
): 简称 ICP
,通过把索引过滤条件下推到存储引擎,来减少 MySQL
存储引擎访问基表的次数 和 MySQL
服务层访问存储引擎的次数。
索引下推 VS 覆盖索引: 其实都是 减少回表的次数,只不过方式不同
覆盖索引: 当索引中包含所需要的字段(
SELECT XXX
),则不再回表去查询字段。索引下推: 对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表的行数。
要了解 ICP
是如何工作的,先从一个查询 SQL
开始:
举个栗子:查询名字 la
开头、年龄为 18
的记录
SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
有这些记录:
不开启 ICP
时索引扫描是如何进行的:
- 通过索引元组,定位读取对应数据行。(实际上:就是回表)
- 对
WHERE
中字段做判断,过滤掉不满足条件的行。
使用 ICP
,索引扫描如下进行:
- 获取索引元组。
- 对
WHERE
中字段做判断,在索引列中进行过滤。 - 对满足条件的索引,进行回表查询整行。
- 对
WHERE
-
서버
레이어:SQL
분석기,SQL
최적화 있음 실행자,SQL
실행자는SQL
문의 특정 실행 프로세스를 담당합니다.
-
-
🎜엔진
레이어: 가장 일반적으로 사용되는InnoDB
스토리지 엔진과 같은 특정 데이터를 저장하고 임시 결과를 메모리 세트에 저장하는 역할을 담당합니다.TempTable
엔진.🎜
- 🎜고객별 클라이언트/서버 통신 프로토콜을 사용하여
MySQL
과 연결을 설정합니다. 🎜🎜 - 🎜🎜쿼리 캐시:🎜
-
쿼리 캐시
가 활성화되어 있고 쿼리 중에 정확히 동일한SQL
가 쿼리되는 경우 캐시 프로세스 코드> 문을 실행하면 쿼리 결과가 클라이언트에 직접 반환됩니다. 🎜 -
쿼리 캐시
가 활성화되지 않거나 동일한SQL
문이 활성화되지 않은 경우; 프로세서는 구문 및 의미 구문 분석을 수행하고 구문 분석 트리를 생성합니다. 🎜🎜🎜 - 🎜파서는 새로운 구문 분석 트리를 생성합니다. 🎜🎜
- 🎜쿼리 최적화 프로그램은 실행 계획을 생성합니다. 🎜🎜
- 🎜쿼리 실행 엔진은
SQL
문을 실행합니다. 이때 쿼리 실행 엔진은SQL
에 있는 테이블의 스토리지 엔진 유형을 기반으로 합니다. 명령문 및 해당API
인터페이스는 기본 스토리지 엔진 캐시 또는 실제 파일과 상호 작용하여MySQL Server
로 필터링한 후 쿼리 결과를 캐시하여 반환합니다. 클라이언트. 🎜🎜
🎜🎜🎜🎜Query Cache
가 켜져 있으면 이때SQL
문과 결과가 완전히Query Cache
에 저장됩니다. 동일한SQL
문이 실행되면 결과가 직접 반환됩니다. 🎜팁
:MySQL 8.0
에서는쿼리 캐시
(쿼리 캐시 모듈)가 제거되었습니다. 🎜🎜쿼리 캐시의 적중률이 매우 낮기 때문입니다. 쿼리 캐시 무효화는 매우 자주 발생합니다. 테이블이 업데이트될 때마다 해당 테이블의 모든 쿼리 캐시가 지워집니다. 🎜
🎜인덱스 푸시다운이란 무엇인가요? 🎜🎜🎜인덱스 조건 푸시다운(인덱스 조건 푸시다운
):ICP
라고도 하며, 인덱스 필터링 조건을 스토리지 엔진. 스토리지 엔진이 기본 테이블에 액세스하는 횟수와MySQL
서비스 계층이 스토리지 엔진에 액세스하는 횟수입니다. 🎜🎜🎜인덱스 푸시다운 VS 포함 인덱스: 실제로 둘 다 🎜테이블 반환 수를 줄이지만 방식은 다릅니다 🎜- 🎜🎜포함 인덱스: 색인 생성에 필수 필드(
SELECT XXX
)가 포함되어 있으면 필드를 쿼리하기 위해 테이블로 돌아갈 필요가 없습니다. 🎜🎜 - 🎜🎜인덱스 푸시다운: 인덱스에 포함된 필드를 먼저 판단하고 🎜조건을 충족하지 않는 레코드를 직접 필터링하여 반환되는 행 수를 줄입니다. 테이블. 🎜🎜🎜🎜🎜
ICP
작동 방식을 이해하려면SQL
쿼리로 시작하세요.🎜🎜예:la 레코드라는 이름을 쿼리하세요.
및 연령18
🎜-- 表创建 CREATE TABLE IF NOT EXISTS `user` ( `id` VARCHAR(64) NOT NULL COMMENT '主键 id', `name` VARCHAR(50) NOT NULL COMMENT '名字', `age` TINYINT NOT NULL COMMENT '年龄', `address` VARCHAR(100) NOT NULL COMMENT '地址', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '用户表'; -- 创建索引 CREATE INDEX idx_name_age ON user (name, age); -- 新增数据 INSERT INTO user (id, name, age, address) VALUES (1, 'tt', 14, 'linhai'); INSERT INTO user (id, name, age, address) VALUES (2, 'lala', 18, 'linhai'); INSERT INTO user (id, name, age, address) VALUES (3, 'laxi', 30, 'linhai'); INSERT INTO user (id, name, age, address) VALUES (4, 'lawa', 40, 'linhai'); -- 查询语句 SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
🎜🎜에는 다음 기록이 있습니다: 🎜🎜🎜🎜🎜
ICP
가 켜져 있지 않을 때 색인 스캔이 수행되는 방법:🎜- 찾고 및 인덱스 튜플을 통해 해당 데이터 행을 읽습니다. (실제로는 테이블만 반환) 🎜
-
WHERE
의 필드를 판단하고 조건에 맞지 않는 행을 필터링합니다. 🎜🎜🎜🎜🎜🎜
ICP 사용 code>에서 인덱스 스캔은 다음과 같이 진행됩니다:🎜<ul> <li>인덱스 튜플을 가져옵니다. 🎜</li> <li> <code>WHERE
의 필드를 판단하고 인덱스 열에서 필터링합니다. 🎜 - 조건을 충족하는 인덱스의 경우 전체 행을 테이블에 다시 쿼리합니다. 🎜
-
WHERE
의 필드를 판단하고 조건을 충족하지 않는 행을 필터링합니다. 🎜🎜🎜🎜🎜动手实验:
实验:使用
MySQL
版本8.0.16
-- 表创建 CREATE TABLE IF NOT EXISTS `user` ( `id` VARCHAR(64) NOT NULL COMMENT '主键 id', `name` VARCHAR(50) NOT NULL COMMENT '名字', `age` TINYINT NOT NULL COMMENT '年龄', `address` VARCHAR(100) NOT NULL COMMENT '地址', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT '用户表'; -- 创建索引 CREATE INDEX idx_name_age ON user (name, age); -- 新增数据 INSERT INTO user (id, name, age, address) VALUES (1, 'tt', 14, 'linhai'); INSERT INTO user (id, name, age, address) VALUES (2, 'lala', 18, 'linhai'); INSERT INTO user (id, name, age, address) VALUES (3, 'laxi', 30, 'linhai'); INSERT INTO user (id, name, age, address) VALUES (4, 'lawa', 40, 'linhai'); -- 查询语句 SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
新增数据如下:
- 关闭
ICP
,再调用EXPLAIN
查看语句:
-- 将 ICP 关闭 SET optimizer_switch = 'index_condition_pushdown=off'; -- 查看确认 show variables like 'optimizer_switch'; -- 用 EXPLAIN 查看 EXPLAIN SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
- 开启
ICP
,再调用EXPLAIN
查看语句:
-- 将 ICP 打开 SET optimizer_switch = 'index_condition_pushdown=on'; -- 查看确认 show variables like 'optimizer_switch'; -- 用 EXPLAIN 查看 EXPLAIN SELECT * FROM user WHERE name LIKE 'la%' AND age = 18;
由上实验可知,区别是否开启
ICP
:Exira
字段中的Using index condition
更进一步,来看下
ICP
带来的性能提升:通过访问数据文件的次数
-- 1. 清空 status 状态 flush status; -- 2. 查询 SELECT * FROM user WHERE name LIKE 'la%' AND age = 18; -- 3. 查看 handler 状态 show status like '%handler%';
对比开启
ICP
和 关闭ICP
: 关注Handler_read_next
的值-- 开启 ICP flush status; SELECT * FROM user WHERE name LIKE 'la%' AND age = 18; show status like '%handler%'; +----------------------------|-------+ | Variable_name | Value | +----------------------------|-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 1 | <---重点 | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------|-------+ 18 rows in set (0.00 sec) -- 关闭 ICP flush status; SELECT * FROM user WHERE name LIKE 'la%' AND age = 18; show status like '%handler%'; +----------------------------|-------+ | Variable_name | Value | +----------------------------|-------+ | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_external_lock | 2 | | Handler_mrr_init | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 3 | <---重点 | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------|-------+ 18 rows in set (0.00 sec)
由上实验可知:
- 开启
ICP
:Handler_read_next
等于 1,回表查 1 次。 - 关闭
ICP
:Handler_read_next
等于 3,回表查 3 次。
这实验跟上面的栗子就对应上了。
索引下推限制
根据官网可知,索引下推 受以下条件限制:
当需要访问整个表行时,
ICP
用于range
、ref
、eq_ref
和ref_or_null
ICP
可以用于InnoDB
和MyISAM
表,包括分区表InnoDB
和MyISAM
表。对于
InnoDB
表,ICP
仅用于二级索引。ICP
的目标是减少全行读取次数,从而减少I/O
操作。对于InnoDB
聚集索引,完整的记录已经读入InnoDB
缓冲区。在这种情况下使用ICP
不会减少I/O
。在虚拟生成列上创建的二级索引不支持
ICP
。InnoDB
支持虚拟生成列的二级索引。引用子查询的条件不能下推。
引用存储功能的条件不能被按下。存储引擎不能调用存储的函数。
触发条件不能下推。
不能将条件下推到包含对系统变量的引用的派生表。(
MySQL 8.0.30
及更高版本)。
小结下:
-
ICP
仅适用于 二级索引。 -
ICP
目标是 减少回表查询。 -
ICP
对联合索引的部分列模糊查询非常有效。
拓展:虚拟列
CREATE TABLE UserLogin ( userId BIGINT, loginInfo JSON, cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"), PRIMARY KEY(userId), UNIQUE KEY idx_cellphone(cellphone) );
列
cellphone
:就是一个虚拟列,它是由后面的函数表达式计算而成,本身这个列不占用任何的存储空间,而索引idx_cellphone
实质是一个函数索引。好处: 在写
SQL
时可以直接使用这个虚拟列,而不用写冗长的函数。举个栗子: 查询手机号
-- 不用虚拟列 SELECT * FROM UserLogin WHERE loginInfo->>"$.cellphone" = '13988888888' -- 使用虚拟列 SELECT * FROM UserLogin WHERE cellphone = '13988888888'
推荐学习:mysql视频教程
- 关闭
- 🎜🎜포함 인덱스: 색인 생성에 필수 필드(
-
- 🎜고객별 클라이언트/서버 통신 프로토콜을 사용하여
위 내용은 한 기사로 MySQL 인덱스 푸시다운 이해하기의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

mysqldiffersfromothersqldialectsinsyntaxforlimit, 자동 점유, 문자열 comparison, 하위 쿼리 및 퍼포먼스 앤 알리 분석 .1) mysqluse Slimit, whilesqlSerVerusestOpandoracleSrownum.2) MySql'Sauto_incrementContrastSwithPostgresql'serialandoracle '

MySQL 파티셔닝은 성능을 향상시키고 유지 보수를 단순화합니다. 1) 큰 테이블을 특정 기준 (예 : 날짜 범위)으로 작은 조각으로 나누고, 2) 데이터를 독립적 인 파일로 물리적으로 나눌 수 있습니다.

MySQL에서 권한을 부여하고 취소하는 방법은 무엇입니까? 1. 보조금 명세서를 사용하여 grantallprivilegesondatabase_name.to'username'@'host '와 같은 부여 권한; 2. Revoke 문을 사용하여 Revokeallprivilegesondatabase_name.from'username'@'host '와 같은 권한을 취소하여 허가 변경의 적시에 의사 소통을 보장하십시오.

InnoDB는 거래 지원 및 높은 동시성이 필요한 응용 프로그램에 적합한 반면, MyISAM은 더 많은 읽기와 덜 쓰는 응용 프로그램에 적합합니다. 1. INNODB는 전자 상거래 및 은행 시스템에 적합한 거래 및 은행 수준의 자물쇠를 지원합니다. 2. Myisam은 블로깅 및 컨텐츠 관리 시스템에 적합한 빠른 읽기 및 색인을 제공합니다.

MySQL에는 Innerjoin, Leftjoin, RightJoin 및 FullouterJoin의 네 가지 주요 조인 유형이 있습니다. 1. 결합 조건을 충족하는 두 테이블의 모든 행을 반환합니다. 2. Leftjoin 오른쪽 테이블에 일치하는 행이 없더라도 왼쪽 테이블의 모든 행을 반환합니다. 3. RightJoin은 LeftJoin과 상반되며 오른쪽 테이블의 모든 행을 반환합니다. 4. FULLOUTERNOIN은 조건을 충족 시키거나 충족하지 않는 두 테이블의 모든 행을 반환합니다.

mysqloffersvariousStorageEngines, 각각의 everitedforentUsecases : 1) innodbisidealforapplicationsneedingAcidCoInceandHighConcurrency, 지원 트랜잭션 및 foreignKeys.2) myIsAmisbestforread-heverworkloads, memoryengineis

MySQL의 일반적인 보안 취약점에는 SQL 주입, 약한 암호, 부적절한 권한 구성 및 업데이트되지 않은 소프트웨어가 포함됩니다. 1. 전처리 명령문을 사용하여 SQL 주입을 방지 할 수 있습니다. 2. 강력한 비밀번호 전략을 사용하여 약한 암호는 피할 수 있습니다. 3. 정기적 인 검토 및 사용자 권한 조정을 통해 부적절한 권한 구성을 해결할 수 있습니다. 4. Unupdated 소프트웨어는 MySQL 버전을 정기적으로 확인하고 업데이트하여 패치 할 수 있습니다.

느린 쿼리 로그를 활성화하고 임계 값을 설정하여 MySQL에서 느린 쿼리를 식별 할 수 있습니다. 1. 느린 쿼리 로그를 활성화하고 임계 값을 설정하십시오. 2. 느린 쿼리 로그 파일을보고 분석하고 심층 분석을 위해 MySQLDumpSlow 또는 PT-Query 소수성과 같은 도구를 사용하십시오. 3. 인덱스 최적화, 쿼리 재 작성 및 select*의 사용을 피함으로써 느린 쿼리 최적화를 달성 할 수 있습니다.


핫 AI 도구

Undresser.AI Undress
사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool
무료로 이미지를 벗다

Clothoff.io
AI 옷 제거제

Video Face Swap
완전히 무료인 AI 얼굴 교환 도구를 사용하여 모든 비디오의 얼굴을 쉽게 바꾸세요!

인기 기사

뜨거운 도구

스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경

Atom Editor Mac 버전 다운로드
가장 인기 있는 오픈 소스 편집기

드림위버 CS6
시각적 웹 개발 도구

WebStorm Mac 버전
유용한 JavaScript 개발 도구

DVWA
DVWA(Damn Vulnerable Web App)는 매우 취약한 PHP/MySQL 웹 애플리케이션입니다. 주요 목표는 보안 전문가가 법적 환경에서 자신의 기술과 도구를 테스트하고, 웹 개발자가 웹 응용 프로그램 보안 프로세스를 더 잘 이해할 수 있도록 돕고, 교사/학생이 교실 환경 웹 응용 프로그램에서 가르치고 배울 수 있도록 돕는 것입니다. 보안. DVWA의 목표는 다양한 난이도의 간단하고 간단한 인터페이스를 통해 가장 일반적인 웹 취약점 중 일부를 연습하는 것입니다. 이 소프트웨어는
