mysql 索引 测试_mysql的索引测试
Mysql高級
mysql索引測試
一、生成測試數據
1、創建測試表
/*創建t_user表用于數據測試*/
DROP TABLE IF EXISTS t_user;
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`address` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13997 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
2、創建隨機字符串函數
我們通過定義一個自定義函數rand_string來創建隨機字符串。
/*創建rand_string方法自動生成隨機字符串*/
DROP FUNCTION IF EXISTS rand_string;
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i=i+1;
END WHILE;
RETURN return_str;
END
$$
3、創建存儲過程
定義存儲過程，生成大量的數據用作測試，數據生成過程中會調用隨機字符串函數，防止大量數據重復影響測試結果。代碼中通過i<1000001控制生成的數據量，可以生成100萬的數據。
/*創建create_data存儲過程，自動生成100萬的隨機數據*/
DROP PROCEDURE IF EXISTS create_data;
DELIMITER $$
CREATE PROCEDURE create_data()BEGIN
DECLARE i INT;SET i=1;
WHILE i<1000001 DOINSERT INTO t_user (NAME,address,phone,age)
VALUES(rand_string(10),rand_string(10),rand_string(10),i);
SET i=i+1;END WHILE;
END
$$
4、調用存儲過程
通過調用create_data存儲過程，生成數據。
/*調用create_data存儲過程，生成數據*/
CALL create_data();
執行時間較長，大概需要15分鐘左右。
5、測試數據的生成情況
使用COUNT函數查看表中數據量，應該是100萬。
/*查詢t_user總數據量*/
SELECT COUNT(*) FROM t_user;
二、索引性能驗證
1、無索引列的查詢
在where條件中查詢沒有添加索引的列，性能會比較差。我們可以先在sqlyog中打開表t_use的數據，然后復制一個名字出來進行查詢。
/*無索引列的查詢，索引不會命中*/
SELECT * FROM t_user WHERE NAME = 'ZYWMUoLMAu';
上面的SQL執行時間大約在0.4秒左右，耗時較長。
2、主鍵列查詢
主鍵默認是唯一索引，我們可以嘗試進行查找一條記錄。
/*主鍵列查詢，索引會命中*/
SELECT * FROM t_user WHERE id = 1;
上面的SQL執行時間大約在1毫秒左右，這說明主鍵上包含索引，性能提升非常大。
3、在NAME列上增加索引
在NAME上增加BTREE索引。
/*添加NAME列上的索引*/
ALTER TABLE `t_user` ADD INDEX index_name ( `name` ) ;
隨著數據量越大，索引的創建時間會越長。添加完索引之后，再進行SQL的查詢。
/*在NAME列上有索引，索引會命中*/
SELECT * FROM t_user WHERE NAME = 'ZYWMUoLMAu';
上面的SQL執行時間大約在10毫秒左右，這說明NAME列上索引已經生效。
4、范圍查詢的索引效果
我們在age列上進行范圍查詢，測試性能。
/*在age列上沒有索引，索引不會命中*/
SELECT * FROM t_user WHERE age > 100 AND age < 200;
上面的SQL執行時間大約在0.4秒左右，性能較差。
在age列上添加BTREE索引,再進行測試。
/*在age列上有索引，索引會命中*/
ALTER TABLE `t_user` ADD INDEX index_age ( `age` ) ;
上面的SQL執行時間大約在10毫秒左右，這說明age列上索引對于范圍查詢來說已經生效。
5、排序的索引效果
我們在address列上進行排序查詢，測試性能，限制查詢數據量為100。
/*在address列上沒有索引，索引不會命中*/
SELECT * FROM t_user ORDER BY address DESC LIMIT 100;
上面的SQL執行時間大約在1秒左右，性能較差。
在age列上添加BTREE索引,再進行測試。
/*在address列上有索引，索引會命中*/
ALTER TABLE `t_user` ADD INDEX index_address ( `address` ) ;
上面的SQL執行時間大約在30毫秒左右，這說明address列上索引對于排序查詢來說已經生效。
三、高性能的索引策略
1、獨立的列：
如果查詢中的列不是獨立的，則Mysql就不會使用索引。獨立的含義是指索引列不能是表達式的一部分，也不能是函數的參數。
/*使用了表達式，不是獨立的列，不會命中索引*/
SELECT * FROM t_user WHERE id + 0 = 1;
上面的SQL等價于id = 1但是Mysql的索引就會因此失效，執行時間大約在0.4秒左右。
2、like查詢不能以%開頭
如果查詢中包含like查詢以%為開頭，則索引會失效。
/*LIKE查詢以%為開頭，不會命中索引*/
SELECT * FROM t_user WHERE NAME LIKE '%ZYWMU%';
上面的SQL語句執行時間在0.5秒左右，然后我們將開頭的%去掉，進行測試。
/*LIKE查詢不以%為開頭，會命中索引*/
SELECT * FROM t_user WHERE NAME LIKE 'ZYWMU%';
當like查詢不以%為開頭之后，查詢時間在0.02秒，證明索引已經命中，性能得到非常大的提升。
3.列類型是字符串，一定要在條件中將數據使用引號引用起來
嘗試以下SQL，在name列有索引的情況下
/*在name列有索引，字符串未用引號引用，不會命中索引*/
SELECT * FROM t_user WHERE NAME = 123;
未查找到數據，執行時間0.6秒，如果我們將數據使用引號引用
/*在name列有索引，字符串用引號引用，會命中索引*/
SELECT * FROM t_user WHERE NAME = '123';
未查找到數據，執行時間0.01秒，證明索引已經命中。
4.最左匹配原則
在mysql建立聯合索引時會遵循最左前綴匹配的原則，即最左優先，在檢索數據時從聯合索引的最左邊開始匹配，刪除所有索引，對列name、列address和列phone建一個聯合索引
ALTER TABLE `t_user` ADD INDEX index_combile ( `name`, `address`,`phone`);
聯合索引 index_combile實際建立了(name)、(name,address)、(name,address,phone)三個索引。所以下面的三個SQL語句都可以命中索引。
SELECT * FROM t_user WHERE address = 'KPSTOrpBMf' AND phone = 'pjZvejKYKF' AND NAME = 'myGKHeCwAm'
SELECT * FROM t_user WHERE NAME = 'myGKHeCwAm' AND address = 'KPSTOrpBMf'
SELECT * FROM t_user WHERE NAME = 'myGKHeCwAm';
上面三個查詢語句執行時會依照最左前綴匹配原則，檢索時分別會使用索引
(name,address,phone)
(name,address)
(name)
進行數據匹配。
索引的字段可以是任意順序的，如：
/*優化器會幫助我們調整順序，下面的SQL語句都可以命中索引*/
SELECT * FROM t_user WHERE address = 'KPSTOrpBMf' AND phone = 'pjZvejKYKF' AND NAME = 'myGKHeCwAm';
Mysql的優化器會幫助我們調整where條件中的順序，以匹配我們建立的索引。
聯合索引中最左邊的列不包含在條件查詢中，所以根據上面的原則，下面的SQL語句就不會命中索引。
/*聯合索引中最左邊的列不包含在條件查詢中，下面的SQL語句就不會命中索引*/
SELECT * FROM t_user WHERE address = 'KPSTOrpBMf' AND phone = 'pjZvejKYKF';
四、擴展
使用explain語句查看索引的命中情況
我們可以使用explain語句查看SQL語句的執行計劃，判斷索引是否命中。
explain SELECT * FROM t_user WHERE address = 'KPSTOrpBMf' AND phone = 'pjZvejKYKF' AND NAME = 'myGKHeCwAm';
顯示結果如下：
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1
SIMPLE
t_user
(NULL)
ALL
(NULL)
(NULL)
(NULL)
(NULL)
996086
1.00
Using where
EXPLAIN列的解釋：
table：顯示這一行的數據是關于哪張表的
type：這是重要的列，顯示連接使用了何種類型。從最好到最差的連接類型為const、eq_reg、ref、range、indexhe和ALL
possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句
key： 實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX(indexname)來強制使用一個索引或者用IGNORE INDEX(indexname)來強制MYSQL忽略索引
key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好
ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數
rows：MYSQL認為必須檢查的用來返回請求數據的行數
Extra：關于MYSQL如何解析查詢的額外信息。
type
以下排序從上到下，性能由壞到好。
a.ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行
b.index：Full Index Scan，index與ALL區別為index類型只遍歷索引樹
c.range：索引范圍掃描，對索引的掃描開始于某一點，返回匹配值域的行，常見于between、等的查詢
d.ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。常見于使用非唯一索引即唯一索引的非唯一前綴進行的查找
e.eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描
f.const、system：當MySQL對查詢某部分進行優化，并轉換為一個常量時，使用這些類型訪問。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量
g.NULL：MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引
所以由type可以進行分析，如果是ref、eq_ref那么索引命中且性能較好。
總結
以上是生活随笔為你收集整理的mysql 索引 测试_mysql的索引测试的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Linux内存Mem和Swap那点事,L
- 下一篇： linux 单网卡 路由,CentOS下