MySQL索引(一)

             資料庫開發中索引的使用佔了很重要的位置，好的索引會使資料庫的讀寫效率加倍，爛的索引則會拖累整個系統甚至引發災難。

             索引分三類：

             index ----普通的索引,資料可以重複

             unique ----唯一索引,唯一索引,要求所有記錄都唯一

             primary key ----主鍵索引,也就是在唯一索引的基礎上相應的列必須為主鍵

             一般的建立索引的語句如下：

             

ALTER TABLE tb_name ADD INDEX index_name (column_list)</p><p>　　ALTER TABLE tb_name ADD UNIQUE index_name (column_list)</p><p>　　ALTER TABLE tb_name ADD PRIMARY KEY index_name (column_list)             MySQL 中會對 <，<=，=，>，>=，BETWEEN，IN 以及不以% _ 開頭的like 語句使用索引。

            

             為證實like處理方法以下為一個簡單的測試：

 

            

create table tb_test(fval varchar(50));
            先不添加索引，隨機插入100W 條記錄，並測試like 的查詢速度。

 

           

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_teset`()<br />BEGIN<br /> DECLARE v_val VARCHAR(20);<br />DECLARE v_str VARCHAR(20);<br /> DECLARE v_i INT ;<br /> DECLARE v_j INT;</p><p>SET v_str ='abcdefghijklmnopqrstuvwxyz';<br /> SET v_i=0;<br /> SET v_j=0;</p><p> WHILE v_i<1000000 DO<br /> SET v_val ='';</p><p> WHILE v_j< 13 DO<br /> SET v_val= CONCAT(v_val,SUBSTRING(v_str,1,FLOOR(1+RAND()*26)));<br /> SET v_j=v_j+1;<br /> END WHILE;<br /> SET v_j=0;<br /> INSERT INTO tb_test(fval)<br /> VALUES(v_val); </p><p> SET v_i =v_i+1;<br /> END WHILE;<br /> END$</p><p>DELIMITER ;         使用前後% 測試like 查詢速度：

 

 

SELECT *<br />FROM tb_test<br />WHERE fval LIKE '%ab';<br />70231條 用時0.531s

 

 

SELECT *<br />FROM tb_test<br />WHERE fval LIKE 'ab%'961406條用時1.422s

 

下面加入索引：

 

ALTER TABLE tb_test ADD INDEX my_index(fval);
再次測試like 的查詢速度：

 

SELECT *<br />FROM tb_test<br />WHERE fval LIKE '%ab';70231條 用時1.094s

SELECT *<br />FROM tb_test<br />WHERE fval LIKE 'ab%'961406條用時1.485s

表為MyISAM格式。 可以看到加了索引後查詢的效率並未得到提升。再使用 explain 關鍵字查看 like 'ab%' 的運行情況：

EXPLAIN
SELECT *
FROM tb_test IGNORE INDEX(my_index)
WHERE fval LIKE '%ab';

EXPLAIN SELECT *
FROM tb_test
WHERE fval LIKE '%ab';