Inhaltsverzeichnis
实验
安装数据库
导入示例数据
建立联合索引
查询
原理
关闭ICP
开启ICP
注意事项
参考
Heim Datenbank MySQL-Tutorial MySQL索引与Index Condition Pushdown

MySQL索引与Index Condition Pushdown

Jun 07, 2016 pm 04:39 PM
index mysql 索引

大约在两年前,我写了一篇关于MySQL索引的文章。最近有同学在文章的评论中对文章的内容提出质疑,质疑主要集中在联合索引的使用方式上。在那篇文章中,我说明联合索引是将各个索引字段做字符串连接后作为key,使用时将整体做前缀匹配。 而这名同学在这个页面

大约在两年前,我写了一篇关于MySQL索引的文章。最近有同学在文章的评论中对文章的内容提出质疑,质疑主要集中在联合索引的使用方式上。在那篇文章中,我说明联合索引是将各个索引字段做字符串连接后作为key,使用时将整体做前缀匹配。

而这名同学在这个页面找到了如下一句话:index condition pushdown is usually useful with multi-column indexes: the first component(s) is what index access is done for, the subsequent have columns that we read and check conditions on。从而认为联合索引的使用方式与文中不符。

实际上,这个页面所讲述的是在MariaDB 5.3.3(MySQL是在5.6)开始引入的一种叫做Index Condition Pushdown(以下简称ICP)的查询优化方式。由于本身不是一个层面的东西,前文中说的是Index Access,而这里是Query Optimization,所以并不构成对前文正确性的影响。在写前文时,MySQL还没有ICP,所以文中没有涉及相关内容,但考虑到新版本的MariaDB或MySQL中ICP的启用确实影响了一些查询行为的外在表现。所以决定写这篇文章详细讲述一下ICP的原理以及对索引使用方式的优化。

实验

先从一个简单的实验开始直观认识ICP的作用。

安装数据库

首先需要安装一个支持ICP的MariaDB或MySQL数据库。我使用的是MariaDB 5.5.34,如果是使用MySQL则需要5.6版本以上。

Mac环境下可以通过brew安装:

brew install mairadb
Nach dem Login kopieren

其它环境下的安装请参考MariaDB官网关于下载安装的文档。

导入示例数据

与前文一样,我们使用Employees Sample Database,作为示例数据库。完整示例数据库的下载地址为:https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2。

将下载的压缩包解压后,会看到一系列的文件,其中employees.sql就是导入数据的命令文件。执行

mysql -h[host] -u[user] -p 
<p>就可以完成建库、建表和load数据等一系列操作。此时数据库中会多一个叫做employees的数据库。库中的表如下:</p>
<pre class="brush:php;toolbar:false">MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.00 sec)
Nach dem Login kopieren

我们将使用employees表做实验。

建立联合索引

employees表包含雇员的基本信息,表结构如下:

MariaDB [employees]> DESC employees.employees;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int(11)       | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
Nach dem Login kopieren

这个表默认只有一个主索引,因为ICP只能作用于二级索引,所以我们建立一个二级索引:

ALTER TABLE employees.employees ADD INDEX first_name_last_name (first_name, last_name);
Nach dem Login kopieren

这样就建立了一个first_name和last_name的联合索引。

查询

为了明确看到查询性能,我们启用profiling并关闭query cache:

SET profiling = 1;
SET query_cache_type = 0;
SET GLOBAL query_cache_size = 0;
Nach dem Login kopieren

然后我们看下面这个查询:

MariaDB [employees]> SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 254642 | 1959-01-17 | Mary       | Botman    | M      | 1989-11-24 |
| 471495 | 1960-09-24 | Mary       | Dymetman  | M      | 1988-06-09 |
| 211941 | 1962-08-11 | Mary       | Hofman    | M      | 1993-12-30 |
| 217707 | 1962-09-05 | Mary       | Lichtman  | F      | 1987-11-20 |
| 486361 | 1957-10-15 | Mary       | Oberman   | M      | 1988-09-06 |
| 457469 | 1959-07-15 | Mary       | Weedman   | M      | 1996-11-21 |
+--------+------------+------------+-----------+--------+------------+
Nach dem Login kopieren

根据MySQL索引的前缀匹配原则,两者对索引的使用是一致的,即只有first_name采用索引,last_name由于使用了模糊前缀,没法使用索引进行匹配。我将查询联系执行三次,结果如下:

+----------+------------+---------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                     |
+----------+------------+---------------------------------------------------------------------------+
|       38 | 0.00084400 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
|       39 | 0.00071800 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
|       40 | 0.00089600 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
+----------+------------+---------------------------------------------------------------------------+
Nach dem Login kopieren

然后我们关闭ICP:

SET optimizer_switch='index_condition_pushdown=off';
Nach dem Login kopieren

在运行三次相同的查询,结果如下:

+----------+------------+---------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                     |
+----------+------------+---------------------------------------------------------------------------+
|       42 | 0.00264400 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
|       43 | 0.01418900 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
|       44 | 0.00234200 | SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man' |
+----------+------------+---------------------------------------------------------------------------+
Nach dem Login kopieren

有意思的事情发生了,关闭ICP后,同样的查询,耗时是之前的三倍以上。下面我们用explain看看两者有什么区别:

MariaDB [employees]> EXPLAIN SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+
| id   | select_type | table     | type | possible_keys        | key                  | key_len | ref   | rows | Extra                 |
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | employees | ref  | first_name_last_name | first_name_last_name | 44      | const |  224 | Using index condition |
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
Nach dem Login kopieren
MariaDB [employees]> EXPLAIN SELECT * FROM employees WHERE first_name='Mary' AND last_name LIKE '%man';
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+
| id   | select_type | table     | type | possible_keys        | key                  | key_len | ref   | rows | Extra       |
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+
|    1 | SIMPLE      | employees | ref  | first_name_last_name | first_name_last_name | 44      | const |  224 | Using where |
+------+-------------+-----------+------+----------------------+----------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
Nach dem Login kopieren

前者是开启ICP,后者是关闭ICP。可以看到区别在于Extra,开启ICP时,用的是Using index condition;关闭ICP时,是Using where。

其中Using index condition就是ICP提高查询性能的关键。下一节说明ICP提高查询性能的原理。

原理

ICP的原理简单说来就是将可以利用索引筛选的where条件在存储引擎一侧进行筛选,而不是将所有index access的结果取出放在server端进行where筛选。

以上面的查询为例,在没有ICP时,首先通过索引前缀从存储引擎中读出224条first_name为Mary的记录,然后在server段用where筛选last_name的like条件;而启用ICP后,由于last_name的like筛选可以通过索引字段进行,那么存储引擎内部通过索引与where条件的对比来筛选掉不符合where条件的记录,这个过程不需要读出整条记录,同时只返回给server筛选后的6条记录,因此提高了查询性能。

下面通过图两种查询的原理详细解释。

关闭ICP

在不支持ICP的系统下,索引仅仅作为data access使用。

开启ICP

在ICP优化开启时,在存储引擎端首先用索引过滤可以过滤的where条件,然后再用索引做data access,被index condition过滤掉的数据不必读取,也不会返回server端。

注意事项

有几个关于ICP的事情要注意:

  • ICP只能用于二级索引,不能用于主索引。
  • 也不是全部where条件都可以用ICP筛选,如果某where条件的字段不在索引中,当然还是要读取整条记录做筛选,在这种情况下,仍然要到server端做where筛选。
  • ICP的加速效果取决于在存储引擎内通过ICP筛选掉的数据的比例。

参考

[1] https://mariadb.com/kb/en/index-condition-pushdown/

[2] http://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn

Heiße KI -Werkzeuge

Undresser.AI Undress

Undresser.AI Undress

KI-gestützte App zum Erstellen realistischer Aktfotos

AI Clothes Remover

AI Clothes Remover

Online-KI-Tool zum Entfernen von Kleidung aus Fotos.

Undress AI Tool

Undress AI Tool

Ausziehbilder kostenlos

Clothoff.io

Clothoff.io

KI-Kleiderentferner

Video Face Swap

Video Face Swap

Tauschen Sie Gesichter in jedem Video mühelos mit unserem völlig kostenlosen KI-Gesichtstausch-Tool aus!

Heiße Werkzeuge

Notepad++7.3.1

Notepad++7.3.1

Einfach zu bedienender und kostenloser Code-Editor

SublimeText3 chinesische Version

SublimeText3 chinesische Version

Chinesische Version, sehr einfach zu bedienen

Senden Sie Studio 13.0.1

Senden Sie Studio 13.0.1

Leistungsstarke integrierte PHP-Entwicklungsumgebung

Dreamweaver CS6

Dreamweaver CS6

Visuelle Webentwicklungstools

SublimeText3 Mac-Version

SublimeText3 Mac-Version

Codebearbeitungssoftware auf Gottesniveau (SublimeText3)

Wie man phpmyadmin öffnet Wie man phpmyadmin öffnet Apr 10, 2025 pm 10:51 PM

Sie können PhpMyAdmin in den folgenden Schritten öffnen: 1. Melden Sie sich beim Website -Bedienfeld an; 2. Finden und klicken Sie auf das Symbol phpmyadmin. 3. Geben Sie MySQL -Anmeldeinformationen ein; 4. Klicken Sie auf "Login".

MySQL: Eine Einführung in die beliebteste Datenbank der Welt MySQL: Eine Einführung in die beliebteste Datenbank der Welt Apr 12, 2025 am 12:18 AM

MySQL ist ein Open Source Relational Database Management -System, das hauptsächlich zum schnellen und zuverlässigen Speicher und Abrufen von Daten verwendet wird. Sein Arbeitsprinzip umfasst Kundenanfragen, Abfragebedingungen, Ausführung von Abfragen und Rückgabergebnissen. Beispiele für die Nutzung sind das Erstellen von Tabellen, das Einsetzen und Abfragen von Daten sowie erweiterte Funktionen wie Join -Operationen. Häufige Fehler umfassen SQL -Syntax, Datentypen und Berechtigungen sowie Optimierungsvorschläge umfassen die Verwendung von Indizes, optimierte Abfragen und die Partitionierung von Tabellen.

Mysqls Platz: Datenbanken und Programmierung Mysqls Platz: Datenbanken und Programmierung Apr 13, 2025 am 12:18 AM

Die Position von MySQL in Datenbanken und Programmierung ist sehr wichtig. Es handelt sich um ein Open -Source -Verwaltungssystem für relationale Datenbankverwaltung, das in verschiedenen Anwendungsszenarien häufig verwendet wird. 1) MySQL bietet effiziente Datenspeicher-, Organisations- und Abruffunktionen und unterstützt Systeme für Web-, Mobil- und Unternehmensebene. 2) Es verwendet eine Client-Server-Architektur, unterstützt mehrere Speichermotoren und Indexoptimierung. 3) Zu den grundlegenden Verwendungen gehören das Erstellen von Tabellen und das Einfügen von Daten, und erweiterte Verwendungen beinhalten Multi-Table-Verknüpfungen und komplexe Abfragen. 4) Häufig gestellte Fragen wie SQL -Syntaxfehler und Leistungsprobleme können durch den Befehl erklären und langsam abfragen. 5) Die Leistungsoptimierungsmethoden umfassen die rationale Verwendung von Indizes, eine optimierte Abfrage und die Verwendung von Caches. Zu den Best Practices gehört die Verwendung von Transaktionen und vorbereiteten Staten

Warum MySQL verwenden? Vorteile und Vorteile Warum MySQL verwenden? Vorteile und Vorteile Apr 12, 2025 am 12:17 AM

MySQL wird für seine Leistung, Zuverlässigkeit, Benutzerfreundlichkeit und Unterstützung der Gemeinschaft ausgewählt. 1.MYSQL bietet effiziente Datenspeicher- und Abruffunktionen, die mehrere Datentypen und erweiterte Abfragevorgänge unterstützen. 2. Übernehmen Sie die Architektur der Client-Server und mehrere Speichermotoren, um die Transaktion und die Abfrageoptimierung zu unterstützen. 3. Einfach zu bedienend unterstützt eine Vielzahl von Betriebssystemen und Programmiersprachen. V.

So stellen Sie eine Verbindung zur Datenbank von Apache her So stellen Sie eine Verbindung zur Datenbank von Apache her Apr 13, 2025 pm 01:03 PM

Apache verbindet eine Verbindung zu einer Datenbank erfordert die folgenden Schritte: Installieren Sie den Datenbanktreiber. Konfigurieren Sie die Datei web.xml, um einen Verbindungspool zu erstellen. Erstellen Sie eine JDBC -Datenquelle und geben Sie die Verbindungseinstellungen an. Verwenden Sie die JDBC -API, um über den Java -Code auf die Datenbank zuzugreifen, einschließlich Verbindungen, Erstellen von Anweisungen, Bindungsparametern, Ausführung von Abfragen oder Aktualisierungen und Verarbeitungsergebnissen.

So starten Sie MySQL von Docker So starten Sie MySQL von Docker Apr 15, 2025 pm 12:09 PM

Der Prozess des Startens von MySQL in Docker besteht aus den folgenden Schritten: Ziehen Sie das MySQL -Image zum Erstellen und Starten des Containers an, setzen

CentOS installieren MySQL CentOS installieren MySQL Apr 14, 2025 pm 08:09 PM

Die Installation von MySQL auf CentOS umfasst die folgenden Schritte: Hinzufügen der entsprechenden MySQL Yum -Quelle. Führen Sie den Befehl mySQL-server aus, um den MySQL-Server zu installieren. Verwenden Sie den Befehl mySQL_SECURE_INSTALLATION, um Sicherheitseinstellungen vorzunehmen, z. B. das Festlegen des Stammbenutzerkennworts. Passen Sie die MySQL -Konfigurationsdatei nach Bedarf an. Tune MySQL -Parameter und optimieren Sie Datenbanken für die Leistung.

So installieren Sie MySQL in CentOS7 So installieren Sie MySQL in CentOS7 Apr 14, 2025 pm 08:30 PM

Der Schlüssel zur eleganten Installation von MySQL liegt darin, das offizielle MySQL -Repository hinzuzufügen. Die spezifischen Schritte sind wie folgt: Laden Sie den offiziellen GPG -Schlüssel von MySQL herunter, um Phishing -Angriffe zu verhindern. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting

See all articles