mysql 组合索引 or_mysql索引优化实例(单列索引与组合索引)
索引一般用于在數據規模大時對查詢進行優化的一種機制，對于一般的查詢來說，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、測試查詢索引字段所用的時間，代碼如下:
復制代碼 代碼示例:
//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 "
";
$starttimeb = microtime();
$result = mysql_query($sqlb) or?? die( "invalid?? query:?? "?? .?? mysql_error());
$endtimeb = microtime();
echo "b表查詢所有記錄所用時間：".(($endtimeb-$starttimeb)*1000)."毫秒";
mysql_close($con); // by www.jbxue.com
}
?>
執行結果如下：
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、索引的優缺點
利用索引可以大大加快我們的搜索，但是維護索引需要額外的開銷，尤其是當索引較多的時候，大量的數據會很容易帶來索引量的膨脹，因此對于頻繁要用到的查詢，才需要做索引，這樣才能以最小的代價獲得最大的性能提升。
mysql索引優化應用實例
MySql索引優化注意要點
mysql索引與mysql索引優化查詢
Mysql索引優化方法解析
深入理解MySQL索引與優化
mysql索引優化實例分享
mysql索引使用與優化
分享:Mysql索引優化的技巧
mysql性能優化之索引優化
總結
以上是生活随笔為你收集整理的mysql 组合索引 or_mysql索引优化实例(单列索引与组合索引)的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： php如何直接使用iview,iview
- 下一篇： cmd中输入net start mysq