MySQL优化器中一个Count和覆盖索引的问题_MySQL
bitsCN.com
MySQL优化器中一个Count和覆盖索引的问题
现象说明
其实这里主要要说明的是一个优化器还需要改进的地方。
优化器会根据where条件和select_list里面的字段决定在使用一个索引(sta)后，是否需要回表—回到聚集索引取数据。
基本的做法是：在确定了一个索引后，将select_list和where中出现的所有字段都拿来判断一下，如果字段都存在于sta索引中，则可以使用覆盖索引。
第一个explan可以用上覆盖索引(Using Index), 是因为select_list里面只有count(*)，而count(*)在语法解析阶段就被特殊处理，不作为特殊字段。
第二个字段在现在的实现中，因为gmt不是sta索引的一部分（sta索引定义上只有sta一个字段，算上聚集索引结构，就是sta,auci）。所以最后判定为不能使用覆盖索引。
性能差别
表中放入500w数据以后，这两个查询的时间相差25倍(0.19s vs 5s)。
存在改进
其实我说这个“不科学”，是因为第二个语句居然不能使用覆盖索引。按照count(gmt)的语义，是计算gmt不为NULL的所有行的数目。但是表定义中，分分明已经说明了gmt为not null。那么就可以转成count(*)了！
目前因为count、sum、count(distinct)这些操作的处理方法被揉在一起，因此没有特别分开。
应用警惕
好在按照正常的习惯，需要计算总数时，最多是count(auci)或者count(*)， 不会故意去count一个别的字段。当时若使用的是MyISAM，就需要小心了。
从上面的分析可以推测得到，如果这是个MyISAM表，还是一样的where条件，count(*)是可以使用覆盖索引的，但是count(auci)就不行了。
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
1377
52
A Practical Guide to the Where Method in Laravel Collections
Mar 10, 2024 pm 04:36 PM
Practical Guide to Where Method in Laravel Collections During the development of the Laravel framework, collections are a very useful data structure that provide rich methods to manipulate data. Among them, the Where method is a commonly used filtering method that can filter elements in a collection based on specified conditions. This article will introduce the use of the Where method in Laravel collections and demonstrate its usage through specific code examples. 1. Basic usage of Where method
How to use the Where method in Laravel collections
Mar 10, 2024 pm 10:21 PM
How to use the Where method in Laravel collection Laravel is a popular PHP framework that provides a wealth of functions and tools to facilitate developers to quickly build applications. Among them, Collection is a very practical and powerful data structure in Laravel. Developers can use collections to perform various operations on data, such as filtering, mapping, sorting, etc. In collections, the Where method is a commonly used method for filtering the collection based on specified conditions.
From beginner to proficient: Master the skills of using is and where selectors
Sep 08, 2023 am 09:15 AM
From beginner to proficient: Master the skills of using is and where selectors Introduction: In the process of data processing and analysis, the selector is a very important tool. Through selectors, we can extract the required data from the data set according to specific conditions. This article will introduce the usage skills of is and where selectors to help readers quickly master the powerful functions of these two selectors. 1. Use of the is selector The is selector is a basic selector that allows us to select the data set based on given conditions.
The basic usage of mysql left join and the difference between on and where
Jun 02, 2023 pm 11:54 PM
Preface When we write SQL statements, we cannot avoid using connection keywords, such as inner connections and outer connections. There are many types. I will post here a picture I found elsewhere: I think this picture is very detailed. It shows the common link types in SQL statements. Take leftjoin in this article as an example. It is available online. As defined: the LEFTJOIN keyword will return all rows from the left table, even if there are no matching rows in the right table. In fact, literally speaking, leftjoin is relatively easy to understand, but there are still some problems during use. For example, if the condition is after on and after where, their results are completely different. Let's go from shallow to deep
Usage analysis of Where method in Laravel collection
Mar 09, 2024 pm 06:51 PM
Laravel is a popular PHP development framework that provides rich and convenient functions. Collection is one of the very important data structures in Laravel. The collection class provides many powerful methods, one of which is the where method. This article will use specific code examples to analyze the usage of the where method in Laravel collections. 1. Create a collection First, we need to create a collection that contains some data. Can
Detailed explanation of Where method of Laravel collection
Mar 10, 2024 pm 01:33 PM
Laravel is a popular PHP framework, and its Collections class provides powerful data processing functions. Among them, the Where method is one of the commonly used methods in collection classes, used to filter data that meets conditions. This article will introduce the Where method of Laravel collection in detail, including usage methods, parameter meanings, and specific code examples. 1. Overview of the Where method The Where method is used to filter elements in the collection that meet specified conditions and return a
Common errors and solutions to where method in Laravel
Mar 10, 2024 pm 06:03 PM
Common errors and solutions for the where method in Laravel. In the process of developing using the Laravel framework, we often use EloquentORM to operate the database. Among them, the where method is a very commonly used method for filtering data in the database. However, due to lack of familiarity with the Laravel framework or lack of deep understanding of EloquentORM, it is easy to make some common mistakes when using the where method. This article will introduce several common w
How to use the WHERE clause in SQL to specify selection criteria
Jun 03, 2023 pm 04:31 PM
SQLWHERE clause The WHERE clause is used to specify the selection criteria. To conditionally select data from a table, add a WHERE clause to the SELECT statement. The syntax is as follows: SELECT column name FROM table name WHERE column operator value The following operators can be used in the WHERE clause: =: equal to: not equal to >: greater than =: greater than or equal to 1965
