MySql如何使用索引(一)

Magicio發表於2019-01-27
MySql索引

我們都知道在 MySql 中使用索引可以提高查詢效率,但有時候真正執行Sql查詢的時候卻沒有按照我們的預想使用索引，而是全表掃描，導致有慢Sql影響了整個網站的效率，甚至導致網站崩潰，所以我們需要了解Mysql是如何選擇使用索引的，以便建立合適的索引 （本文基於MySql5.7，InnoDB引擎）

前提：建立一張測試表

假設有一張使用者表

CREATE TABLE `test_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `birthday` date NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `height` int(11) NOT NULL,
  `weight` int(11) NOT NULL,
  PRIMARY KEY (`user_id`),
  KEY `idx_name_height_weight` (`name`,`height`,`weight`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
複製程式碼

我們建立了name,height,weight 的聯合索引，插入測試資料

INSERT INTO `test_user` (`user_id`, `name`, `birthday`, `sex`, `height`, `weight`)
VALUES
	(1, 'Tony', '1991-01-01', 1, 176, 65),
	(2, 'Mary', '1989-12-19', 2, 160, 50),
	(3, 'Tom', '1996-05-29', 1, 180, 70),
	(4, 'Kiven', '1994-08-09', 1, 190, 80),
	(5, 'John', '1992-11-12', 1, 182, 75);
複製程式碼

執行什麼操作的時候Mysql會使用索引

查詢與WHERE子句匹配的行

select * from test_user where name='mary'
複製程式碼

檢視執行計劃

MySql如何使用索引(一)
可見使用了idx_name_height_weight索引

從表中刪除一行資料

    DELETE from test_user where name='mary';
複製程式碼

檢視執行計劃

MySql如何使用索引(一)

查詢索引列的MIN()或MAX()的值

    SELECT MIN(height)  FROM `test_user`
複製程式碼

MySql如何使用索引(一)
這個和之前的執行計劃不太一樣，Select tables optimized away（選擇要優化的表）實際就是優化到不能再優化的意思，在這種情況下，MySql把每個Key的MIN() 和 MAX()值替換成一個常量，如果查到了這個常量就立即返回，然後看下面的例子，分別在索引列上和非索引列上使用MIN()函式的執行計劃：

    SELECT MIN(height)  from `test_user` where height>=176 AND height<=190;
複製程式碼

MySql如何使用索引(一)

    SELECT MIN(height)  from test_user where sex=1;
複製程式碼

MySql如何使用索引(一)

在索引列上執行 SORT 或 ORDER BY 操作

    SELECT name,height from test_user ORDER BY `name` DESC;
複製程式碼

MySql如何使用索引(一)
注意SELECT的欄位能匹配索引列，比如：

MySql如何使用索引(一)
將會出現Using filesort，Using filesort 是Mysql裡一種速度比較慢的外部排序，應當儘量避免

本文講述了MySql什麼時候會使用索引，下章說明什麼時候MySql不能使用索引，敬請期待

相關文章