設(shè)置MySQL中的數(shù)據(jù)類型來優(yōu)化運(yùn)行速度的實(shí)例
今天看了一個(gè)優(yōu)化案例覺的挺有代表性,這里記錄下來做一個(gè)標(biāo)記,來紀(jì)念一下隨便的字段定義的問題。
回憶一下,在表的設(shè)計(jì)中很多人習(xí)慣的把表的結(jié)構(gòu)設(shè)計(jì)成Varchar(64),Varchar(255)之類的,雖然大多數(shù)情況只存了5-15個(gè)字節(jié).那么我看一下下面這個(gè)案例.
查詢語句:
SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;
該表(client_id,channel)是一個(gè)組合索引.
利用explain,看一下執(zhí)行計(jì)劃,對于索引使用上看上非常完美
mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel; +----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+ | 1 | SIMPLE | xxx_sources | index | idx_client_channel | idx_client_channel | 1032 | NULL | 20207319 | Using where; Using index | +----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+ 1 row in set (0.00 sec)
看一下實(shí)際執(zhí)行:
mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel; +---------+----------+ | channel | visitors | +---------+----------+ | NULL | 0 | +---------+----------+ 1 row in set (11.69 sec)
實(shí)際執(zhí)行的情況非常的糟糕.傳通的想法,這個(gè)執(zhí)行從索引上執(zhí)行計(jì)劃上看非常完美了,好象和MySQL沒什么關(guān)系了. 在去看一下表的設(shè)計(jì)會發(fā)現(xiàn)client_id也是設(shè)計(jì)成了
varchar(255).看到這里不防可以使用下面的方法試一下:
mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel; +----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+ | 1 | SIMPLE | xxx_sources | ref | idx_client_channel | idx_client_channel | 258 | const | 457184 | Using where; Using index | +----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+ 1 row in set (0.00 sec)
從執(zhí)行計(jì)劃上來看,差不多,但實(shí)際差多了.具體上來看key_len從1032降到了258,執(zhí)行計(jì)劃變成了const基于等于的查找,行數(shù)從原來千萬級到了十萬級了.不算也能明白IO
節(jié)省了很多.
再來看實(shí)際執(zhí)行:
mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel; +---------+----------+ | channel | visitors | +---------+----------+ | NULL | 0 | +---------+----------+ 1 row in set (0.25 sec)
哇,從11.69秒變成了0.25秒,這是什么概念,優(yōu)化了多少倍,算一下吧.
看到這里在想什么呢,記住這個(gè)案例,嗯,不錯(cuò),以后還可以加引號優(yōu)化一下.那為什么不問一下,能不能在優(yōu)化了,為什么會這樣呢?
我們先來看一下第一個(gè)問題:
能不能在優(yōu)化了?
答案是當(dāng)然可以了.從索引的長度上來看258還是一個(gè)非常大的數(shù)據(jù),對于client_id這個(gè)字段從名字上來看,也只會存數(shù)據(jù)型的值,那為什么不用的一個(gè)int unsigned去存呢,
索引的長度馬上會從258降到4。這樣不是又節(jié)省了很多嗎?
接下來看一下第二個(gè)問題,為什么會這樣呢?
原因有兩點(diǎn),同時(shí)基于一個(gè)原則,基于成本的優(yōu)化器。對于client_id在表的定義時(shí)定義成了字符型的值,在查詢時(shí)傳入了數(shù)值型的值,需要經(jīng)過一個(gè)數(shù)值轉(zhuǎn)換,悲劇的開始,最終
導(dǎo)致MySQL選擇了一個(gè)完成的索引去掃描。
從這個(gè)案例上,我們需要注意什么呢?
合理的選擇數(shù)據(jù)類型,基本工太重要了,就這叫贏在起跑線,一切都不能隨便了,別把一個(gè)表定義成了降了主建外其它全是Varchar(255)。對數(shù)據(jù)庫的double/float這種字段做索引時(shí)一定要小心。
相關(guān)文章
MySQL出現(xiàn)this?is?incompatible?with?sql_mode=only_full_grou
mysql是高版本,當(dāng)執(zhí)行g(shù)roup?by時(shí),select的字段不屬于group?by的字段的話,sql語句就會報(bào)錯(cuò),下面這篇文章主要給大家介紹了關(guān)于MySQL出現(xiàn)this?is?incompatible?with?sql_mode=only_full_group_by錯(cuò)誤的解決辦法,需要的朋友可以參考下2023-02-02MySQL Left JOIN時(shí)指定NULL列返回特定值詳解
我們有時(shí)會有這樣的應(yīng)用,需要在sql的left join時(shí),需要使值為NULL的列不返回NULL而時(shí)某個(gè)特定的值,比如0。這個(gè)時(shí)候,用is_null(field,0)是行不通的,會報(bào)錯(cuò)的,可以用ifnull實(shí)現(xiàn),但是COALESE似乎更符合標(biāo)準(zhǔn)2013-07-07