MySQL物理查询优化技术---index dive辨析
MySQL据此,提供了一个参数ldquo;eq_range_index_dive_limitrdquo;,指示MySQL在这种情况下使用哪种方式。
一 引子
在MySQL的官方手册上有这么一句话:
the optimizer can estimate the row count for each range using dives into the index or index statistics.
这是在说: 优化器为每一个范围段(如“a IN (10, 20, 30)”是等值比较, 括3个范围段实则简化为3个单值,分别是10,20,30)估计每个范围段(用范围段来表示是因为MySQL的“range”扫描方式多数做的是范围扫描,此处单值可视为范围段的特例)中包括的元组数, 而估计方法有2种,一是dive到index中即利用索引完成元组数的估算,简称index dive; 二是使用索引的统计数值,进行估算.
相比这2种方式,在效果上:
1 index dive: 速度慢,但能得到精确的值(MySQL的实现是数索引对应的索引项个数,所以精确)
2 index statistics: 速度快,但得到的值未必精确
二深入
为什么要区分这2种方式呢?
简单地说:
1 查询优化器使用代价估算模型计算每个计划的代价,选择其中代价最小的
2 单表扫描时,需要计算代价;所以单表的索引扫描也需要计算代价
3 单表的计算公式通常是:代价=元组数*IO平均值
4 所以不管是哪种扫描方式,都需要计算元组数
5 当遇到“a IN (10, 20, 30)”这样的表达式的时候,发现a列存在索引,,则需要看这个索引可以扫描到的元组数由多少而计算其索引扫描代价,所以就用到了本文提到的“index dive”、“index statistics”这2种方式。
三优化
MySQL据此,提供了一个参数“eq_range_index_dive_limit”,指示MySQL在这种情况下使用哪种方式。用法如下:
This variable indicates the number of equality ranges in an equality comparison condition when the optimizer should switch from using index dives to index statistics in estimating the number of qualifying rows. It applies to evaluation of expressions that have either of these equivalent forms, where the optimizer uses a nonunique index to look up col_name values:
col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN
--------------------------------------分割线 --------------------------------------
Ubuntu 14.04下安装MySQL
《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF
Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主从服务器
Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群
Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb
MySQL-5.5.38通用二进制安装
--------------------------------------分割线 --------------------------------------
本文永久更新链接地址:

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.

Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.

The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values and pointers to data rows, and is suitable for non-primary key column queries.

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

The article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.
