mysql 索引優化

jopen 12年前發布 | 39K 次閱讀 MySQL 數據庫服務器

索引一般用于在數據規模大時對查詢進行優化的一種機制，對于一般的查詢來說，mysql會去遍歷整個表，來查詢符合要求的結果；如果借助于索引，mysql會將要索引的字段按照一定的算法進行處理，并生成一個類似于書本目錄的文件存放在相應的位置，這樣在查詢時，mysql會先去查找這些"目錄"，然后根據這些"目錄"來快速定位所需記錄的位置，這樣的查找不用遍歷整個記錄集，速度自然會很快，對于海量數據尤其如此。

關于如何創建索引，網上有相關的例子，這里不多講，但是有一個需要注意，在向存在索引的表中插入數據時，因為要維護索引信息，要比不存在索引的表慢一些，因此當數據量大時，可以考慮在插入完數據之后再建立索引。索引分為單列索引和組合索引，對于這兩種索引，分別介紹其優化問題。

1、單列索引

單列所有只包含一個字段，一個表可以包含多個單列索引，但是不要把這個和組合索引混淆。利用以下sql創建測試表：

--創建包含單列索引的index_test_single_a表
CREATE TABLE `index_test_a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` char(255) CHARACTER SET utf8 NOT NULL,
  `content` text CHARACTER SET utf8,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `indexName` (`title`),
  UNIQUE KEY `numIndex` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=latin1;

--創建不包含單列索引的index_test_single_b表
CREATE TABLE `index_test_b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` char(255) CHARACTER SET utf8 NOT NULL,
  `content` text CHARACTER SET utf8,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=latin1;

其中a表包含title的單列索引，b表的title字段不存在索引，但是兩個表都有一個主鍵id，其實主鍵也是索引的一種，這個會在后面詳細解釋。

寫程序向這兩個表中各導入10000條數據，然后就可以測試了。

1.1、測試查詢索引字段所用的時間，代碼如下:

<?php
    //phpinfo();
    ini_set('max_execution_time', 200);
    $con = mysql_connect("localhost:3306","root","710100");
    if (!$con)
    {   
      die('Could not connect: ' . mysql_error());
    }
    else{
            mysql_select_db("test",$con);


        $sqlA = "select * from index_test_a where title = 'title_4999';";



        $sqlB = "select * from index_test_b where  title = 'title_4999';";

        $startTimeA = microtime();



        $result = mysql_query($sqlA) or   die( "Invalid   query:   "   .   mysql_error()); 

        $endTimeA = microtime();

        echo "A表查詢所有記錄所用時間：".(($endTimeA-$startTimeA)*1000)."毫秒";

        echo "<br>";

        $startTimeB = microtime();

        $result = mysql_query($sqlB) or   die( "Invalid   query:   "   .   mysql_error()); 

        $endTimeB = microtime();

        echo "B表查詢所有記錄所用時間：".(($endTimeB-$startTimeB)*1000)."毫秒";
        mysql_close($con);
    }


?>

執行結果如下：

A表查詢所有記錄所用時間：0.624毫秒

B表查詢所有記錄所用時間：44.484毫秒

可以看到僅僅10000條記錄的查找差別，時間已經相差了幾十倍，因此對于經常查詢的字段，索引是十分必要的。相應的，如果我們查詢沒有做索引的字段，那么是沒有區別的，將以上的sql語句改為如下所示：

$sqlA = "select * from index_test_a where content = 'content_4999';";

$sqlB = "select * from index_test_b where  content = 'content_4999';";

結果如下：

A表查詢所有記錄所用時間：23.848毫秒

B表查詢所有記錄所用時間：24.155毫秒

1.2、測試like查詢

在我們項目中，如果數據量大，則不推薦like查詢，因為其查詢效率比較低，但是對于索引字段來說，like能命中嗎？

可以將sql語句改成如下所示：

$sqlA = "select * from index_test_a where title like '4999%'";
$sqlB = "select * from index_test_b where title like '4999%'";

測試結果如下：

A表查詢所有記錄所用時間：0.488毫秒

B表查詢所有記錄所用時間：25.281毫秒

可以看到對于模糊查詢來說，如果是前綴匹配，則會命中索引，但是如果我們將sql改為后綴匹配或者任意匹配，那么二者所消耗的查詢時間是一致的：

$sqlA = "select * from index_test_a where title like '%4999'";
$sqlB = "select * from index_test_b where title like '%4999'";

$sqlA = "select * from index_test_a where title like '%4999'";
$sqlB = "select * from index_test_b where title like '%4999'";

A表查詢所有記錄所用時間：44.742毫秒

B表查詢所有記錄所用時間：45.752毫秒

即二者都沒有命中索引。

1.3、測試or語句，將sql改為如下所示：

$sqlA = "select * from index_test_a where  content='content_4999' or title='title_4999';";

$sqlB = "select * from index_test_b where  content='content_4999' or title='title_4999';";

測試結果如下:

A表查詢所有記錄所用時間：49.904毫秒

B表查詢所有記錄所用時間：50.131毫秒

繼續將sql改為如下：

$sqlA = "select * from index_test_a where  id=4999  or title='title_4999';";

$sqlB = "select * from index_test_b where  id=4999  or title='title_4999';";

測試結果如下：

A表查詢所有記錄所用時間：0.86毫秒

B表查詢所有記錄所用時間：47.318毫秒

從上面的結果可以看到，當or中有一個字段沒有索引的時候，那么將不會命中索引；反之，如果or運算的所有字段均做了索引，那么是可以命中的。

1.4、測試in，將sql語句繼續改為如下所示：

$sqlA = "select title from index_test_a  where title in ('title_4999','title_5000');";
$sqlB = "select title from index_test_b  where title in ('title_4999','title_5000');";

測試結果為：

A表查詢所有記錄所用時間：0.817毫秒
B表查詢所有記錄所用時間：24.234毫秒

可見對于索引字段，in也是可以命中索引的。

1.5、測試<,>,between等，將sql改為如下所示：

$sqlA = "select title from index_test_a  where num < 999;";
$sqlB = "select title from index_test_b  where num < 999;";

測試結果如下：

A表查詢所有記錄所用時間：11.469毫秒

B表查詢所有記錄所用時間：21.728毫秒

可見二者差別不是很大，因此是沒有命中索引的。

1.6、對于mysql函數，索引的命中，將sql改為如下所示：

$sqlA = "select num from index_test_a  where char(num) in ('999','9999');";
$sqlB = "select num from index_test_b  where  char(num) in ('999','9999');";

得到的結果如下所示:

A表查詢所有記錄所用時間：11.322毫秒

B表查詢所有記錄所用時間：12.429毫秒

所以如果在條件中使用函數，那么索引將會失效。

2、組合索引

組合索引包括對多個列的索引，而不是多個單列索引的組合，將表a中的所以改成（title,num）的組合索引，進行以下測試：

2.1、or測試

將sql語句改成如下所示：

$sqlA = "select * from index_test_a where  num=4999  or title='title_4999';";
$sqlB = "select * from index_test_b where  num=4999 or title='title_4999';";

結果如下所示：

A表查詢所有記錄所用時間：52.535毫秒

B表查詢所有記錄所用時間：53.031毫秒

這時索引沒有命中，索引組合索引的or運算和兩個單列索引的or運算是不同的，前者失效而后者依然有效。

2.2、and測試

將sql語句改成如下所示：

$sqlA = "select * from index_test_a where  num=4999  and title='title_4999';";
$sqlB = "select * from index_test_b where  num=4999 and title='title_4999';";

結果如下所示:

A表查詢所有記錄所用時間：0.666毫秒

B表查詢所有記錄所用時間：43.042毫秒

繼續改為如下所示：

$sqlA = "select * from index_test_a where  num=4999 ;";
$sqlB = "select * from index_test_b where  num=4999 ;";

得到的結果為：

A表查詢所有記錄所用時間：39.398毫秒

B表查詢所有記錄所用時間：41.057毫秒

而改成如下sql：

$sqlA = "select * from index_test_a where  title='title_'4999 ;";
$sqlB = "select * from index_test_b where  title='title_4999' ;";

得到的結果則為：

A表查詢所有記錄所用時間：0.753毫秒

B表查詢所有記錄所用時間：48.248毫秒

由以上三組結果可以看出，組合索引是最左前綴匹配的，即條件中要包含第一個索引列，才會命中索引。

3、索引的優缺點

利用索引可以大大加快我們的搜索，但是維護索引需要額外的開銷，尤其是當索引較多的時候，大量的數據會很容易帶來索引量的膨脹，因此對于頻繁要用到的查詢，我們才需要做索引，這樣才能以最小的代價獲得最大的性能提升。

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1349267056056.html

MySQL 數據庫服務器

mysql 索引優化

相關經驗

相關資訊

相關文檔

目錄