首頁 資料庫 mysql教程 类型转换对MySQL选择索引的影响_MySQL

类型转换对MySQL选择索引的影响_MySQL

Jun 01, 2016 pm 01:44 PM
影響

bitsCN.com

遇到了几例 MySQL 没用使用预期索引的问题,读了些文档之后,发现 MySQL 的类型转换对索引选择的影响还真是一个不大不小的坑。

比如有这样一张 MySQL 表:

CREATE TABLE `indextest` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` tinyint(3) unsigned NOT NULL DEFAULT ’0′,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_age` (`age`),
KEY `idx_create` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

name 是一个有索引的 varchar 字段,表内数据是这样的:

+—-+——–+—–+———————+
| id | name | age | create_time |
+—-+——–+—–+———————+
| 1 | hello | 10 | 2012-02-01 20:00:00 |
| 2 | world | 20 | 2012-02-02 20:00:00 |
| 3 | 111222 | 30 | 2012-02-03 20:00:00 |
| 4 | wow | 40 | 2012-02-04 20:00:00 |
+—-+——–+—–+———————+

使用字符串 ’111222′ 作为参数对 name 字段查询,Execution Plan 如预期的一样,会使用 name 字段上的索引 idx_name:

mysql [localhost] {msandbox} (test) > explain select age from
-> indextest where name=’111222′/G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ref
possible_keys: idx_name
key: idx_name
key_len: 13
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

而使用数字作为参数对 name 字段做查询时,explain 表明这将是全表扫描:

mysql [localhost] {msandbox} (test) > explain select age from
-> indextest where name=111222/G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ALL
possible_keys: idx_name
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)

究其原因,是当文本字段与数字进行比较时,由于类型不同,MySQL 需要做隐式类型转换才能进行比较,结果就如上面的例子所提到的一样。

MySQL 的文档 (Type Conversion in Expression Evaluation) 中提到,在做比较时,会按这样的规则进行必要的类型转换:

两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换
两个参数都是字符串,会按照字符串来比较,不做类型转换
两个参数都是整数,按照整数来比较,不做类型转换
十六进制的值和非数字做比较时,会被当做二进制串,和数字做比较时会按下面的规则处理
有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp
有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较
所有其他情况下,两个参数都会被转换为浮点数再进行比较
比如:

mysql [localhost] {msandbox} (test) > SELECT ’18015376320243459′ =
-> 18015376320243459;
+—————————————–+
| ’18015376320243459′ = 18015376320243459 |
+—————————————–+
| 0 |
+—————————————–+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > SELECT ’18015376320243459′ + 0;
+————————-+
| ’18015376320243459′ + 0 |
+————————-+
| 1.80153763202435e+16 |
+————————-+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT
-> cast(’18015376320243459′ as unsigned) = 18015376320243459;
+———————————————————–+
| cast(’18015376320243459′ as unsigned) = 18015376320243459 |
+———————————————————–+
| 1 |
+———————————————————–+
1 row in set (0.00 sec)

因为浮点数精度(53 bits)问题,并且 MySQL 将字符串转换为浮点数和将整数转换为浮点数使用不同的方法,字符串 ’18015376320243459′ 和整数 18015376320243459 相比较就不相等,如果要避免隐式浮点数转换带来的精度问题,可以显式地使用 cast 做类型转换,将字符串转换为整数。

按照这些规则,对于上面的例子来说,name 字段的值和查询参数 ’111222′ 都会被转换为浮点数才会做比较,而很多文本都能转换为和 111222 相等的数值,比如 ’111222′, ’111222aabb’, ‘ 111222′ 和 ’11122.2e1′,所以 MySQL 不能有效使用索引,就退化为索引扫描甚至是全表扫描。

而反过来,如果使用一个字符串作为查询参数,对一个数字字段做比较查询,MySQL 则是可以有效利用索引的:

mysql [localhost] {msandbox} (test) > explain select name from
-> indextest where age=’30′/G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ref
possible_keys: idx_age
key: idx_age
key_len: 1
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)

原因则是,MySQL 可以将查询参数 ’30′ 转换为确定的数值 30,之后可以快速地在索引中找到与之相等的数值。

除此之外,使用函数对索引字段做显式类型转换或者计算也会使 MySQL 无法使用索引:

mysql [localhost] {msandbox} (test) > explain select name from
-> indextest where cast(age as unsigned)=30/G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)

如上,使用 cast 函数对 age 做显式的类型转换,会使索引失效,当然了,在实际的代码中很少会有这样的写法,但类似下面这样对时间字段做运算的用法就比较多了:

mysql [localhost] {msandbox} (test) > explain select * from
-> indextest where date(create_time)=’2012-02-02′/G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)

对于本例的需求,是想查找 create_time 是 2012-02-02 这一天的记录,用变通的方法,避免在索引字段上做运算就可以有效使用索引了:

mysql [localhost] {msandbox} (test) > explain select * from
-> indextest where create_time between ’2012-02-02′ and ’2012-02-03′/G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: range
possible_keys: idx_create
key: idx_create
key_len: 4
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)

MySQL 的 How … 系列文档值得读一读,比如:

  • How MySQL Uses Indexes
  • How MySQL Uses Memory
  • How MySQL Uses Internal Temporary Tables
  • How to Cope with Deadlocks
  • How MySQL Opens and Closes Tables
  • How MySQL Uses Threads for Client Connections
  • How to Determine What is Causing a Problem

伟大开源软件的文档总是需要经过反复阅读,才能逐步被理解和正确运用,RTFM 和 RTFS 的光辉无限

bitsCN.com
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

AI Hentai Generator

AI Hentai Generator

免費產生 AI 無盡。

熱門文章

R.E.P.O.能量晶體解釋及其做什麼(黃色晶體)
1 個月前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
1 個月前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.如果您聽不到任何人,如何修復音頻
1 個月前 By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.聊天命令以及如何使用它們
1 個月前 By 尊渡假赌尊渡假赌尊渡假赌

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版,非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

VBOX_E_OBJECT_NOT_FOUND(0x80bb0001)VirtualBox錯誤 VBOX_E_OBJECT_NOT_FOUND(0x80bb0001)VirtualBox錯誤 Mar 24, 2024 am 09:51 AM

在VirtualBox中嘗試開啟磁碟映像時,可能會遇到錯誤提示,指示硬碟無法註冊。這種情況通常發生在您嘗試開啟的VM磁碟映像檔與另一個虛擬磁碟映像檔具有相同的UUID。在這種情況下,VirtualBox會顯示錯誤代碼VBOX_E_OBJECT_NOT_FOUND(0x80bb0001)。如果您遇到這個錯誤,不必擔心,有一些解決方法可以嘗試。首先,您可以嘗試使用VirtualBox的命令列工具來變更磁碟映像檔的UUID,這可以避免衝突。您可以執行命令`VBoxManageinternal

使用飛航模式接收電話的效果如何 使用飛航模式接收電話的效果如何 Feb 20, 2024 am 10:07 AM

飛航模式別人打電話會怎麼樣手機已經成為人們生活中不可或缺的工具之一,它不僅是通訊工具,也是娛樂、學習、工作等多種功能的集合體。隨著手機功能的不斷升級和改進,人們對於手機的依賴也越來越高。在飛航模式出現後,人們可以更方便地在飛行中使用手機。但是,有人擔心在飛航模式下別人打電話的情況會對手機或使用者產生什麼樣的影響呢?本文將從幾個方面進行分析和討論。首先

如何關閉抖音評論功能?關閉抖音評論功能後會怎麼樣? 如何關閉抖音評論功能?關閉抖音評論功能後會怎麼樣? Mar 23, 2024 pm 06:20 PM

在抖音平台上,使用者不僅可以分享自己的生活點滴,還可以與其他使用者互動交流。有時候評論功能可能會引發一些不愉快的經歷,如網路暴力、惡意評論等。那麼,如何關閉抖音評論功能呢?一、如何關閉抖音評論功能? 1.登入抖音APP,進入個人首頁。 2.點選右下角的“我”,進入設定選單。 3.在設定選單中,找到「隱私設定」。 4.點選“隱私設定”,進入隱私設定介面。 5.在隱私設定介面,找到「評論設定」。 6.點選“評論設定”,進入評論設定介面。 7.在評論設定介面,找到「關閉評論」選項。 8.點選「關閉評論」選項,確認關閉評論

Java中的檔案包含漏洞及其影響 Java中的檔案包含漏洞及其影響 Aug 08, 2023 am 10:30 AM

Java是一種常用的程式語言,用於開發各種應用程式。然而,就像其他程式語言一樣,Java也存在安全漏洞和風險。其中一個常見的漏洞是檔案包含漏洞(FileInclusionVulnerability),本文將探討檔案包含漏洞的原則、影響以及如何防範這種漏洞。文件包含漏洞是指在程式中透過動態引入或包含其他文件的方式,但卻沒有對引入的文件做充分的驗證和防護,從

資料稀缺對模型訓練的影響問題 資料稀缺對模型訓練的影響問題 Oct 08, 2023 pm 06:17 PM

資料稀缺對模型訓練的影響問題,需要具體程式碼範例在機器學習和人工智慧領域,而資料是訓練模型的核心要素之一。然而,現實中我們經常面臨的一個問題是資料稀缺。資料稀缺指的是訓練資料的量不足或標註資料的缺乏,這種情況下會對模型訓練產生一定的影響。資料稀缺的問題主要體現在以下幾個方面:過度擬合:當訓練資料量不夠時,模型很容易出現過擬合的現象。過擬合是指模型過度適應訓練數據,

硬碟壞道會導致什麼問題 硬碟壞道會導致什麼問題 Feb 18, 2024 am 10:07 AM

硬盘坏道是指硬盘的物理故障,即硬盘上的储存单元无法正常读取或写入数据。坏道对硬盘的影响是非常显著的,它可能导致数据丢失、系统崩溃和硬盘性能下降等问题。本文将会详细介绍硬盘坏道的影响及相关解决方法。首先,硬盘坏道可能导致数据丢失。当硬盘中的某个扇区出现坏道时,该扇区上的数据将无法读取,从而导致文件损坏或无法访问。这种情况尤其严重,如果坏道所在的扇区中存储了重要

礦卡對遊戲有什麼具體的影響? 礦卡對遊戲有什麼具體的影響? Jan 03, 2024 am 09:05 AM

為了圖便宜可能有些用戶會考慮入手礦卡,這些卡畢竟是頂級的顯示卡,但是也有部分遊戲玩家很擔心礦卡打遊戲有什麼影響,下面就看看具體的介紹吧。礦卡打遊戲有什麼影響:1.礦卡打遊戲沒辦法保證穩定性,因為礦卡的壽命很短很可能玩玩就廢了。 2.礦卡基本上等於原版的閹割版,由於長期的損耗,各方面性能可能都弱了。 3.這樣用戶在玩遊戲的時候可能就不能將遊戲的效果全部展示了。 4.而且顯示卡的電子元件都會提前的老化,更何況打遊戲也很消耗顯示卡,因此等於更大程度上的來將其榨乾,因此對遊戲的影響是很大的。 5.總的來說,使用礦卡打遊

顯示卡配置低的影響什麼 顯示卡配置低的影響什麼 Feb 15, 2024 pm 03:27 PM

一台電腦的運作好壞基本上都和他的顯示卡有著非常大的影響,一部分用戶對於顯示卡不是很了解,也不清楚顯示卡到底對電腦的哪些方面會有影響,為了方便大家觀看,這裡就給大家介紹一下顯示卡配置低的一些影響。顯示卡配置低的影響什麼答:1、一些大型的3D類型的遊戲無法運作。 2.播放一些高畫質影片的時候電腦會有很大的壓力。 3.對於一些比較專業的軟體,需要進行繪圖和3D模型渲染時沒有辦法運作良好。 4.顯示卡的配置低,那就會導致遊戲打不開,或者頻繁地閃退卡頓和卡死,電腦也會花屏,藍色屏幕。 5.遊戲裡面最重要的就是顯示卡了,因為很多畫面需

See all articles