目錄
MySQL(讀作/ma? ?si?kw?l/「My Sequel」)是一個開放源碼的關係資料庫管理系統,原開發者為瑞典的MySQL AB公司,目前為Oracle旗下產品。
被甲骨文公司收購後,自由軟體社群們對於Oracle是否還會持續支持MySQL社群版(MySQL之中唯一的免費版本)有所隱憂,因此MySQL的創始人麥克爾·維德紐斯以MySQL為基礎,成立分支計劃MariaDB。原先一些使用MySQL的開源軟體,部分轉向了MariaDB或其它的資料庫。
不可否認的是,MySQL由於其性能高、成本低、可靠性好,已經成為最流行的開源資料庫之一,隨著MySQL的不斷成熟,它也逐漸用於更多大規模網站和應用,非常流行的開源軟體組合LAMP中的「M」指的就是MySQL。
在眾多開源免費的關係型資料庫系統中,MySQL有以下比較出眾的優勢: 1. 運行速度快 2. 易使用 3. SQL語言支持 4. 移植性好 5. 功能豐富 6. 成本低廉
對於其中運行速度,根據官方介紹,MySQL 8.0 比之前廣泛使用的版本 MySQL 5.7 有了兩倍的提升。
在其官方的Benchmarks中,只讀的性能超過了每秒一百萬次:
讀寫的性能接近每秒二十五萬次:
從概念上講,資料庫是數據表的集合,數據表是數據行和數據列的集合。當你執行一個SELECT語句從數據表中查詢部分數據行的時候,得到的就是另外一個數據表和數據行的集合。
SELECT語句
當然,我們都希望獲得這個新的集合的時間儘可能地短,效率儘可能地高,這就是優化查詢。
提升查詢速度的技術有很多,其中最重要的就是索引。當你發現自己的查詢速度慢的時候,最快解決問題的方法就是使用索引。索引的使用是影響查詢速度的重要因素。在使用索引之前其他的優化查詢的動作純粹是浪費時間,只有合理地使用索引之後,才有必要考慮其他優化方式。
首先,在你的MySQL上創建t_user_action_log 表,方便下面進行演示。
t_user_action_log
CREATE DATABASE `ijiangtao_local_db_mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE ijiangtao_local_db_mysql;
DROP TABLE IF EXISTS t_user_action_log;
CREATE TABLE `t_user_action_log` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 主鍵id, `name` VARCHAR(32) DEFAULT NULL COMMENT 用戶名, `ip_address` VARCHAR(50) DEFAULT NULL COMMENT IP地址, `action` INT4 DEFAULT NULL COMMENT 操作:1-登錄,2-登出,3-購物,4-退貨,5-瀏覽, `create_time` TIMESTAMP COMMENT 創建時間, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.1, 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.3, 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.4, 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.1, 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 1, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 3, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 5, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 2, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 3, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 3, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 5, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 3, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 3, CURRENT_TIMESTAMP); INSERT INTO t_user_action_log (name, ip_address, `action`, create_time) values (LiSi, 8.8.8.2, 4, CURRENT_TIMESTAMP);
假如我們要篩選 action為2的所有記錄,SQL如下:
action
2
SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;
通過查詢分析器explain分析這條查詢語句:
explain
EXPLAIN SELECT id, name, ip_address FROM t_user_action_log WHERE `action`=2;
分析結果如下:
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| |--|-----------|-----|----------|----|-------------|---|-------|---|----|--------|-----| |1|SIMPLE|t_user_action_log||ALL|||||1|100.00|Using where|
其中type為ALL表示要進行全表掃描。這樣效率無疑是極慢的。
type
ALL
下面為action列添加索引:
ALTER TABLE t_user_action_log ADD INDEX (`action`);
然後再次執行查詢分析,結果如下:
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| |--|-----------|-----|----------|----|-------------|---|-------|---|----|--------|-----| |1|SIMPLE|t_user_action_log||ref|action|action|5|const|1|100.00||
我們看到這次查詢就使用索引了。加索引前Extra的值是Using Where,加索引後Extra的值為空。
Extra
那麼為什麼索引會提高查詢速度呢?原因是索引會根據索引值進行分類,這樣就不用再進行全表掃描了。
比如上圖,action值為2的索引值分類存儲在了索引空間,可以快速地查詢到索引值所對應的列。
下面介紹一下如何使用SQL創建、查看和刪除索引。
三種方式:
CREATE INDEX
CREATE INDEX indexName ON tableName (columnName(length));
例如我們對ip_address這一列創建一個長度為16的索引:
ip_address
CREATE INDEX index_ip_addr ON t_user_action_log (ip_address(16));
ALTER
ALTER TABLE tableName ADD INDEX indexName(columnName);
ALTER語句創建索引前面已經有例子了。下面提供一個設置索引長度的例子:
ALTER TABLE t_user_action_log ADD INDEX ip_address_idx (ip_address(16));
SHOW INDEX FROM t_user_action_log;
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment| |-----|----------|--------|------------|-----------|---------|-----------|--------|------|----|----------|-------|-------------| |t_user_action_log|1|ip_address_idx|1|ip_address|A|1|16||YES|BTREE|||
CREATE TABLE tableName( id INT NOT NULL, columnName columnType, INDEX [indexName] (columnName(length)) );
可以通過show語句查看索引:
show
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment| |-----|----------|--------|------------|-----------|---------|-----------|--------|------|----|----------|-------|-------------| |t_user_action_log|0|PRIMARY|1|id|A|199,368||||BTREE||| |t_user_action_log|1|action|1|action|A|4|||YES|BTREE||| |t_user_action_log|1|index_ip_addr|1|ip_address|A|1|16||YES|BTREE|||
使用ALTER命令可以刪除索引,例如:
ALTER TABLE t_user_action_log DROP INDEX index_ip_addr;
索引由於其提供的優越的查詢性能,似乎不使用索引就是一個愚蠢的行為了。但是使用索引,是要付出時間和空間的代價的。因此,索引雖好不可貪多。
下面介紹幾個索引的使用技巧和原則,在使用索引之前,你應該對它們有充分的認識。
索引在提高查詢速度的同時,也由於需要更新索引而帶來了降低插入、刪除和更新帶索引列的速度的問題。一張數據表的索引越多,在寫操作的時候性能下降的越厲害。
與沒有加索引比較,加索引會更快地使你的磁碟接近使用空間極限。
為查詢條件、分組、連接條件的列加索引,而不是為查詢輸出結果的列加索引。
例如下面的查詢語句:
select ip_address from t_user_action_log where name=LiSi group by action order by create_time;
所以可以考慮增加在 name action create_time 列上,而不是 ip_address。
name
create_time
例如action列的值包含:1、2、3、4、5,那麼該列的維度就是5。
維度越高(理論上維度的最大值就是數據行的總數),數據列包含的獨一無二的值就越多,索引的使用效果越好。
對於維度很低的數據列,索引幾乎不會起作用,因此沒有必要加索引。
例如性別列的值只有男和女,每種查詢結果佔比大約50%。一般當查詢優化處理器發現查詢結果超過全表的30%的時候,就會跳過索引,直接進行全表掃描。
對短小的值加索引,意味著索引所佔的空間更小,可以減少I/O活動,同時比較索引的速度也更快。
尤其是主鍵,要儘可能短小。
另外,InnoDB使用的是聚集索引(clustered index),也就是把主鍵和數據行保存在一起。主鍵之外的其他索引都是二級索引,這些二級索引也保留著一份主鍵,這樣在查詢到索引以後,就可以根據主鍵找到對應的數據行。如果主鍵太長的話,會造成二級索引佔用的空間變大。
比如下面的action索引保存了對應行的id。
前邊已經講過短小索引的種種好處了,有時候一個字元串的前幾個字元就能唯一標識這條記錄,這個時候設置索引的長度就是非常划算的做法。
前面已經提供了設置索引length的例子,這裡就不舉例子了。
length
創建複合索引的語法如下:
CREATE INDEX indexName ON tableName (column1 DESC, column2 DESC, column3 ASC);
我們可以看到,最左側的column1索引總是有效的。
對於InnoDB來說,索引可以讓查詢鎖住更少的行,從而可以在並發情況下擁有更佳表現。
下面演示一下查詢鎖與索引之間的關係。
前面使用的t_user_action_log表目前有一個id為主鍵,還有一個二級索引action。
id
下面這條語句的修改範圍是id值為1 2 3 4所在的行,查詢鎖會鎖住id值為1 2 3 4 5所在的行。
1
3
4
5
update ijiangtao_local_db_mysql.t_user_action_log set name=c1 where id<5;
set autocommit=0;
begin;
-- 沒有被鎖 update ijiangtao_local_db_mysql.t_user_action_log set name=c2 where id=6; -- 被鎖 update ijiangtao_local_db_mysql.t_user_action_log set name=c2 where id=5;
你會發現id=5的數據行已經被鎖定,id=6的數據行可以正常提交。
id=5
id=6
id=1
-- 在連接1提交事務 commit;
ip_address沒有索引的話,會鎖定全表。
連接1開啟事務以後commit;之前,連接2對該表的update全部需要等待連接1釋放鎖。
commit;
update ijiangtao_local_db_mysql.t_user_action_log set name=c1 where ip_address=8.8.8.1;
如果索引包含滿足查詢的所有數據,就被稱為覆蓋索引(Covering Indexes),覆蓋索引非常強大,可以大大提高查詢性能。
覆蓋索引高性能的原因是:
ijiangtao_local_db_mysql表的action列包含索引。使用explain分析下面的查詢語句,對於索引覆蓋查詢(index-covered query),分析結果Extra的值是Using index,表示使用了覆蓋索引 :
ijiangtao_local_db_mysql
Using index
explain select `action` from ijiangtao_local_db_mysql.t_user_action_log;
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| |--|-----------|-----|----------|----|-------------|---|-------|---|----|--------|-----| |1|SIMPLE|t_user_action_log||index||action|5||199,703|100.00|Using index|
聚簇索引(Clustered Indexes)保證關鍵字的值相近的元組存儲的物理位置也相同,且一個表只能有一個聚簇索引。
字元串類型不建議使用聚簇索引,特別是隨機字元串,因為它們會使系統進行大量的移動操作。
並不是所有的存儲引擎都支持聚簇索引,目前InnoDB支持。
如果使用聚簇索引,最好使用AUTO_INCREMENT列作為主鍵,應該盡量避免使用隨機的聚簇主鍵。
AUTO_INCREMENT
從物理位置上看,聚簇索引表比非聚簇的索引表,有更好的訪問性能。
從數據結構角度來看,MySQL支持的索引類型有B樹索引、Hash索引等。
B樹索引對於<、<=、 =、 >=、 >、 <>、!=、 between查詢,進行精確比較操作和範圍比較操作都有比較高的效率。
B樹索引也是InnoDB存儲引擎默認的索引結構。
Hash索引僅能滿足=、<=>、in查詢。
Hash索引檢索效率非常高,索引的檢索可以一次定位,不像B樹索引需要從根節點到枝節點,最後才能訪問到頁節點這樣多次的I/O訪問,所以Hash索引的查詢效率要遠高於B樹索引。但Hash索引不能使用範圍查詢。
最後提供幾個查詢優化的建議。
前面已經演示過如何使用explain命令分析查詢語句了,這裡再解釋一下其中幾個有參考價值的欄位的含義:
select_type表示查詢中每個select子句的類型,一般有下面幾個值: - SIMPLE 簡單SELECT,不使用UNION或子查詢等。
type表示MySQL在表中找到所需行的方式,又稱「訪問類型」,常用的類型有:
ALL, index, range, ref, eq_ref, const, system, NULL。
從左到右,性能從差到好。
key列顯示MySQL實際決定使用的鍵(索引),如果沒有選擇索引,鍵是NULL。
possible_keys指出MySQL能使用哪個索引在表中找到記錄,查詢涉及到的欄位上如果存在索引則該索引將被列出,但不一定被查詢使用。
ref表示上述表的連接匹配條件,即哪些列或常量被用於查找索引列上的值。
rows表示MySQL根據表統計信息,以及索引選用的情況,找到所需記錄需要讀取的行數。這個行數是估算的值,實際行數可能不同。
當數據列被聲明為NOT NULL以後,在查詢的時候就不需要判斷是否為NULL,由於減少了判斷,可以降低複雜性,提高查詢速度。
如果要表示數據列為空,可以使用0等代替。
MySQL對數值類型的處理速度要遠遠快於字元串,而且數值類型往往更加節省空間。
例如對於「Male」和「Female」可以用「0」和「1」進行代替。
如果你的數據列的取值是確定有限的,可以使用ENUM類型代替字元串。因為MySQL會把這些值表示為一系列對應的數字,這樣處理的速度會提高很多。
CREATE TABLE shirts ( name VARCHAR(40), size ENUM(x-small, small, medium, large, x-large) );
INSERT INTO shirts (name, size) VALUES (dress shirt,large), (t-shirt,medium), (polo shirt,small);
SELECT name, size FROM shirts WHERE size = medium;
索引是一個單獨的,存儲在磁碟上的數據結構,索引對數據表中一列或者多列值進行排序,索引包含著對數據表中所有數據的引用指針。
本教程從MySQL開始講起,又介紹了MySQL中索引的使用,最後提供了使用索引的幾條原則和優化查詢的幾個方法。
無論你是DBA還是軟體開發,菜鳥程序員還是資深工程師,相信本節提到的關於索引的知識,對你都會有所幫助。
推薦閱讀: