MySQL中的count(*) 和 count(1) 区别性能对比分析
作者:酷酷- 发布时间:2024-01-24 03:12:51
1前言
我们本节来看看我们常说的索引下推。
2什么是索引下推
索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本的新特性,用于优化数据查询。
不使用索引条件下推优化时存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件。
当使用索引条件下推优化时,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。
索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。
换句话说:索引下推能减少回表查询次数,提高查询效率。
3索引下推优化的原理
我们先简单了解一下MySQL大概的架构:
MySQL从上至下分为以下几层:
MySQL服务层:包括NoSQL和SQL接口、查询解析器、优化器、缓存和Buffer等组件。
存储引擎层:各种插件式的表格存储引擎,实现事务、索引等各种存储引擎相关的特性。
文件系统层: 读写物理文件。
MySQL服务层负责SQL语法解析、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。
索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理。
我们来具体看一下,在没有使用ICP的情况下,MySQL的查询:
获取下一行,首先读取索引信息,然后根据索引将整行数据读取出来。
然后通过where条件判断当前数据是否符合条件,符合返回数据。
使用ICP的情况下,查询过程:
获取下一行的索引信息。
检查索引中存储的列信息是否符合索引条件,如果符合将整行数据读取出来,如果不符合跳过读取下一行。
用剩余的判断条件,判断此行数据是否符合要求,符合要求返回数据。
4索引下推适用条件
需要整表扫描的情况。比如:range, ref, eq_ref, ref_or_null 。
适用于InnoDB 引擎和 MyISAM 引擎的查询。(5.6版本不适用分区表查询,5.7版本后可以用于分区表查询)。
对于InnDB引擎只适用于二级索引,因为InnDB的聚簇索引会将整行数据读到InnDB的缓冲区,这样一来索引条件下推的主要目的减少IO次数就失去了意义。因为数据已经在内存中了,不再需要去读取了。
引用子查询的条件不能下推。
调用存储过程的条件不能下推,存储引擎无法调用位于MySQL服务器中的存储过程。
触发条件不能下推。
5EXPLAN分析
当使用explan进行分析时,如果使用了索引条件下推,Extra会显示Using index condition。并不是Using index。
因为并不能确定利用索引条件下推查询出的数据就是符合要求的数据,还需要通过其他的查询条件来判断。
6索引下推的具体实践
理论比较抽象,我们来上一个实践。使用一张用户表tuser,表里创建联合索引(name, age)。
如果现在有一个需求:检索出表中名字第一个字是张,而且年龄是10岁的所有用户。那么,SQL语句是这么写的:
select * from tuser where name like '张%' and age=10;
假如你了解索引最左匹配原则,那么就知道这个语句在搜索索引树的时候,只能用 张,找到的第一个满足条件的记录id为1。
那接下来的步骤是什么呢?
没有使用ICP
在MySQL 5.6之前,存储引擎根据通过联合索引找到name likelike '张%' 的主键id(1、4),逐一进行回表扫描,去聚簇索引找到完整的行记录,server层再对数据根据age=10进行筛选。我们看一下示意图:
可以看到需要回表两次,把我们联合索引的另一个字段age浪费了。
使用ICP
而MySQL 5.6 以后, 存储引擎根据(name,age)联合索引,找到,由于联合索引中包含列,所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。我们看一下示意图:
可以看到只回表了一次。
除此之外我们还可以看一下执行计划,看到Extra一列里 Using index condition,这就是用到了索引下推。
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra || 1 | SIMPLE | tuser | NULL | range | na_index | na_index | 102 | NULL | 2 | 25.00 | Using index condition |
7相关系统参数
索引条件下推默认是开启的,可以使用系统参数optimizer_switch来控制器是否开启。
查看默认状态:
mysql> select @@optimizer_switch\G;
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on
切换状态:
set ="index_condition_pushdown=off";
set ="index_condition_pushdown=on";
8小结
索引下推优化技术其实就是充分利用了索引中的数据,尽量在查询出整行数据之前过滤掉无效的数据。
由于需要存储引擎将索引中的数据与条件进行判断,所以这个技术是基于存储引擎的,只有特定引擎可以使用。并且判断条件需要是在存储引擎这个层面可以进行的操作才可以,比如调用存储过程的条件就不可以,因为存储引擎没有调用存储过程的能力。
来源:https://www.cnblogs.com/kukuxjx/p/17422223.html
猜你喜欢
- 一、数学相关1、绝对值:abs(-1)2、最大最小值:max([1,2,3])、min([1,2,3])3、序列长度:len('ab
- 优化场景利用视图函数(views)查询数据之后可以通过上下文context、字典、列表等方式将数据传递给HTML模板,由template引擎
- MySQL修改字段的默认值和空值修改字段默认值修改:ALTER TABLE 表名 ALTER COLUMN 字段名 SET DEFAULT
- 有些项目可能涉及到使用多个数据库的情况,方法很简单。1.在settings中设定DATABASE比如要使用两个数据库:DATABASES =
- 前言最近找几个老友准备聊天发现几个已经被删除好友名单,做为潜水党多年的我已经不知道成为多少人的黑名单,但是好友列表却依然有不是好友的名单,面
- 后台数据库用是Access,客户用了一年后说打开界面非常慢,查看了数据库后发现数据表中的记录已有五万多条,自己试过将记录复制到10 万条,打
- 一. 引言在数据分析和可视化领域,数据的有效呈现是至关重要的。Python作为一种强大的编程语言,提供了多种数据可视化工具和库。其中,Plo
- 一、背景描述csv格式文件是一种类似于excel的文件格式asc格式文件是一种可以用text打开的文本文件csv转asc本来可以用arcgi
- 一、FFmpeg 多个音频合并的2种方法多个mp3文件合并成一个mp3文件一种方法是连接到一起ffmpeg64.exe -i "c
- 阅读系列教程上一篇:FrontPage2002简明教程二:文字与图像的处理通常网页的布局使用到的是FrontPage 2002中的表格和框架
- 为什么选择Python进行数据分析?Python是一门动态的、面向对象的脚本语言,同时也是一门简约,通俗易懂的编程语言。Python入门简单
- python的异常,以及用try复合语句处理异常。运行代码时有时会出现各种各样的错误,致使解析器中断执行,并提示xxxxxxErorr的提示
- 本文给出一条 SQL 语句用于展示在同一名服务器上,不同的数据库间查询,注意当前连接用户要对两个库都有权限SQL Server 中 SQL
- Debian Etch Linux上成功安装了MySQL 5.0.27 icc版本,并使用JDBC测试中文成功!中文问题的关键是全部使用UT
- 最近准备做一个关于scrapy框架的实战,爬取腾讯社招信息并存储,这篇博客记录一下创建项目的步骤pycharm是无法创建一个scrapy项目
- 前言今天我要教大家的是 如何实现nonebot插件之ChatGpt注意,本文涉及异步爬虫,json文件读写等知识点准备1.获取开发者key获
- 如下所示:# coding:utf-8import osfrom PIL import Image# bmp 转换为jpgdef bmpTo
- TXT文本文件,WORD文档点击后弹出另存为对话框,然后保存下载,而不是在浏览器中打开的asp实现方法,使用了asp中的stream对象,同
- 1.cookie的作用cookie 是指某些网站为了辨别用户身份、进行session跟踪而储存在用户本地终端上的数据,就像有些网站上的一些数
- 通过session,我们可以在多次浏览器请求中保持数据, 接下来的部分就是用session来处理用户登录了。 当然,不能仅凭用户的一面之词,