关于Index Condition Pushdown特性_MySQL
ICP简介
Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHEREcondition for the rows. With ICP enabled, and if parts of the WHERE condition can be evaluated by using only fields from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.
也就说:利用索引(二级索引)来过滤一部分where条件
测试
导入数据库
wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2 tar jxf employees_db-full-1.0.6.tar.bz2 cd employees_db mysql -uroot -p < employees.sql
表结构
mysql> show create table employees \G *************************** 1. row *************************** Table: employees Create Table: CREATE TABLE `employees` ( `emp_no` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(14) NOT NULL, `last_name` varchar(16) NOT NULL, `gender` enum('M','F') NOT NULL, `hire_date` date NOT NULL, PRIMARY KEY (`emp_no`), KEY `index_bh` (`birth_date`,`hire_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
一些表数据
mysql> select @@optimizer_switch like '%index_condition_pushdown%' \G *************************** 1. row *************************** @@optimizer_switch like '%index_condition_pushdown%': 1 1 row in set (0.00 sec) mysql> select @@optimizer_switch like '%index_condition_pushdown%' \G *************************** 1. row *************************** @@optimizer_switch like '%index_condition_pushdown%': 1 1 row in set (0.00 sec) mysql> select @@query_cache_type; +--------------------+ | @@query_cache_type | +--------------------+ | OFF | +--------------------+ 1 row in set (0.01 sec) mysql> select count(*) from employees; +----------+ | count(*) | +----------+ | 300024 | +----------+ 1 row in set (0.17 sec) mysql> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec)
建立索引
alter table employees add index index_bh (`birth_date`,`hire_date`);
查询分析
mysql> explain select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%'; +----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+ | 1 | SIMPLE | employees | range | index_bh | index_bh | 3 | NULL | 46318 | Using where | +----+-------------+-----------+-------+---------------+----------+---------+------+-------+-------------+ 1 row in set (0.00 sec) mysql> SET optimizer_switch='index_condition_pushdown=on'; Query OK, 0 rows affected (0.00 sec) mysql> explain select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%'; +----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+ | 1 | SIMPLE | employees | range | index_bh | index_bh | 3 | NULL | 46318 | Using index condition; Using where | +----+-------------+-----------+-------+---------------+----------+---------+------+-------+------------------------------------+ 1 row in set (0.01 sec)
执行查询
mysql> show profiles; +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 0.00278025 | desc employees | | 2 | 0.00049775 | show create table employees | | 3 | 0.07444550 | select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%' | | 4 | 0.00027500 | SET optimizer_switch='index_condition_pushdown=off' | | 5 | 0.12347025 | select * from employees where birth_date between '1955-01-01' and '1955-12-31' and datediff(hire_date,birth_date)>12300 and first_name like 'S%b%' | +----------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
从结果可以看出来开启ICP之后确实快不少
启用ICP之后,可以用索引来筛选 datediff(hire_date,birth_date)>12300 记录,不需要读出整条记录
ICP原理
如下图所示(图来自MariaDB)
1、优化器没有使用ICP时
在存储引擎层,首先读取索引元组(index tuple),然后使用(index tuple)在基表中(base table)定位和读取整行数据
到服务器层,匹配where条件,如果该行数据满足where条件则使用,否则丢弃
指针向下一行移动,重复以上过程
2、使用ICP的时候
如果where条件的一部分能够通过使用索引中的字段进行过滤,那么服务器层将把这部分where条件Pushdown到存储引擎层
到存储引擎层,从索引中读取索引元组(index tuple),使用索引元组进行判断,如果没有满足where条件,则处理下一条索引元组(index tuple),只有当索引元组满足条件的时候,才会去基表中读取数据
ICP的使用条件
1、只能用于二级索引(secondary index)
2、explain显示的执行计划中type值(join 类型)为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据,即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)
3、ICP可以用于MyISAM和InnnoDB存储引擎,不支持分区表(5.7将会解决这个问题)
4、ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例

ホットAIツール

Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。

Undress AI Tool
脱衣画像を無料で

Clothoff.io
AI衣類リムーバー

AI Hentai Generator
AIヘンタイを無料で生成します。

人気の記事

ホットツール

メモ帳++7.3.1
使いやすく無料のコードエディター

SublimeText3 中国語版
中国語版、とても使いやすい

ゼンドスタジオ 13.0.1
強力な PHP 統合開発環境

ドリームウィーバー CS6
ビジュアル Web 開発ツール

SublimeText3 Mac版
神レベルのコード編集ソフト(SublimeText3)

ホットトピック











win7システムには、win7Ultimateバージョン、Win7Professionalバージョン、Win7Homeバージョンなど、多くのバージョンがあることは誰もが知っています。多くのユーザーは、HomeバージョンとUltimateバージョンの間で迷っており、どのバージョンを選択すればよいかわかりません。そこで今日は、Win7 Family Meal と Win7 Ultimate の違いについて説明します。 1. Experience Different Home Basic Edition を使用すると、日常の操作がより迅速かつ簡単になり、最も頻繁に使用されるプログラムやドキュメントにより迅速かつ便利にアクセスできるようになります。 Home Premium は最高のエンターテインメント体験を提供し、お気に入りのテレビ番組、写真、ビデオ、音楽を簡単に楽しんだり共有したりできます。 Ultimate Edition は、各エディションのすべての機能を統合し、Windows 7 Home Premium のすべてのエンターテイメント機能とプロフェッショナル機能を備えています。

SpringMVC の主要な機能を理解する: これらの重要な概念を習得するには、特定のコード例が必要です。 SpringMVC は、開発者が Model-View-Controller (MVC) アーキテクチャ パターンを通じて柔軟でスケーラブルな構造を構築するのに役立つ Java ベースの Web アプリケーション開発フレームワークです。ウェブアプリケーション。 SpringMVC の主要な機能を理解して習得すると、Web アプリケーションをより効率的に開発および管理できるようになります。この記事では、SpringMVC の重要な概念をいくつか紹介します。

5g の 3 つの特徴は次のとおりです: 1. 高速; 実用的なアプリケーションでは、5G ネットワークの速度は 4G ネットワークの 10 倍以上です。 2. 低遅延; 5G ネットワークの遅延は約数十ミリ秒であり、人間の反応速度よりも高速です。 3. 幅広い接続: 5G ネットワークの出現と他のテクノロジーの組み合わせにより、すべてのインターネットの新しいシーンが生まれます。

Golang (Go 言語) には伝統的な意味でのクラスの概念はありませんが、構造体と呼ばれるデータ型が提供され、これによってクラスと同様のオブジェクト指向機能を実現できます。この記事では、構造体を使用してオブジェクト指向機能を実装する方法を説明し、具体的なコード例を示します。構造体の定義と使用法 まず、構造体の定義と使用法を見てみましょう。 Golang では、type キーワードを通じて構造を定義し、必要に応じて使用できます。構造には属性を含めることができます

インターネットの急速な発展に伴い、プログラミング言語は常に進化し、更新されています。中でもGo言語はオープンソースのプログラミング言語として近年注目を集めています。 Go 言語は、シンプル、効率的、安全で、開発とデプロイが簡単になるように設計されています。高い同時実行性、高速なコンパイル、メモリの安全性などの特徴を備えており、Web開発、クラウドコンピューティング、ビッグデータなどの分野で広く使用されています。ただし、現在、Go 言語のさまざまなバージョンが利用可能です。適切な Go 言語バージョンを選択するときは、要件と機能の両方を考慮する必要があります。頭

Golang は高速で効率的なプログラミング言語として、Web 開発の分野でも広く使用されています。その中でも、ミドルウェアは重要な設計パターンであり、開発者がコードをより適切に整理および管理し、コードの再利用性と保守性を向上させるのに役立ちます。この記事では、Golang のミドルウェアの主な機能とアプリケーション シナリオを紹介し、具体的なコード例を通じてその使用法を説明します。 1. ミドルウェアの概念と機能 ミドルウェアはプラグイン コンポーネントとして、アプリケーションの要求と応答の処理チェーンに配置され、使用されます。

C++ 関数には、単純関数、const 関数、静的関数、および仮想関数というタイプがあり、インライン関数、デフォルト パラメーター、参照戻り値、およびオーバーロードされた関数などの機能が含まれます。たとえば、calculateArea 関数は π を使用して、指定された半径の円の面積を計算し、それを出力として返します。

Java の特徴は次のとおりです: 1. シンプルで学習が簡単; 2. オブジェクト指向なので、コードの再利用性と保守性が向上します; 3. プラットフォームに依存せず、さまざまなオペレーティング システムで実行可能; 4. 自動ガベージによるメモリ管理収集メカニズム メモリの管理 5. 強力な型チェック、変数は使用前に型を宣言する必要がある 6. セキュリティ、不正なアクセスや悪意のあるコードの実行を防ぐことができる 7. マルチスレッドのサポート、パフォーマンスと応答性を向上させることができるプログラム ; 8. 例外処理によりプログラムのクラッシュを回避できる; 9. 多数の開発ライブラリとフレームワーク; 10. オープンソースのエコシステム。
