Mysql应用mysql索引优化实例(单列索引与组合索引)

概述介绍《Mysql应用mysql索引优化实例(单列索引与组合索引)》开发教程，希望对您有用。

《MysqL应用MysqL索引优化实例(单列索引与组合索引)》要点：
本文介绍了MysqL应用MysqL索引优化实例(单列索引与组合索引)，希望对您有用。如果有疑问，可以联系我们。

导读：索引一般用于在数据规模大时对查询进行优化的一种机制,对于一般的查询来说,MysqL会去遍历整个表,来查询符合要求的结果；如果借助于MysqL...

索引一般用于在数据规模大时对查询进行优化的一种机制,来查询符合要求的结果；MysqL学习

如果借助于MysqL索引,MysqL会将要索引的字段依照一定的算法进行处理,并生成一个类似于书本目录的文件存放在相应的位置,这样在查询时,MysqL会先去查找这些"目录",然后根据这些"目录"来快速定位所需记录的位置,这样的查找不用遍历整个记录集,速度自然会很快,对于海量数据尤其如此.MysqL学习

注意,在向存在索引的表中插入数据时,因为要维护索引信息,要比不存在索引的表慢一些,因此当数据量大时,可以考虑在插入完数据之后再建立索引.
索引分为单列索引和组合索引,对于这两种索引,分别介绍其索引优化问题.MysqL学习

1、单列索引
单列所有只包含一个字段,一个表可以包含多个单列索引,但是不要把这个和组合索引混淆.利用以下sql创建测试表：
 MysqL学习

--创建包含单列索引的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;MysqL学习

--创建不包含单列索引的index_test_single_b表
create table `index_test_b` (
  `ID` int(11) not null auto_increment,
  primary key (`ID`)
) engine=innodb auto_increment=10000 default charset=latin1;
 MysqL学习

其中a表包含Title的单列索引,b表的Title字段不存在索引,但是两个表都有一个主键ID,其实主键也是索引的一种,这个会在后面详细解释. 
写程序向这两个表中各导入10000条数据,然后就可以测试了.MysqL学习

1.1、测试查询索引字段所用的时间,代码如下:
 MysqL学习

@H_403_61@<?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); // by www.jbxue.com
  }
 
?>
 

执行结果如下：
a表查询所有记录所用时间：0.624毫秒MysqL学习

b表查询所有记录所用时间：44.484毫秒MysqL学习

可以看到仅仅10000条记录的查找差别,时间已经相差了几十倍,因此对于经常查询的字段,索引是十分必要的.相应的,如果我们查询没有做索引的字段,那么是没有区别的,将以上的SQL语句改为：
 MysqL学习

$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毫秒MysqL学习

1.2、测试like查询MysqL学习

在我们项目中,如果数据量大,则不推荐like查询,因为其查询效率比较低,但是对于索引字段来说,like能命中吗?MysqL学习

可以将SQL语句改成如下所示：
 MysqL学习

$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毫秒MysqL学习

可以看到对于模糊查询来说,如果是前缀匹配,则会命中索引,但是如果我们将sql改为后缀匹配或者任意匹配,那么二者所消耗的查询时间是一致的：
 MysqL学习

$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毫秒MysqL学习

即二者都没有命中索引.MysqL学习

1.3、测试or语句,将sql改为如下所示：
 MysqL学习

$sqla = "select * from index_test_a where  content='content_4999' or title="MysqL索引优化实例(单列索引与组合索引)";";
$sqlb = "select * from index_test_b where  content='content_4999' or title="MysqL索引优化实例(单列索引与组合索引)";";
 

测试结果如下:
a表查询所有记录所用时间：49.904毫秒MysqL学习

b表查询所有记录所用时间：50.131毫秒MysqL学习

继续将sql改为如下：
 MysqL学习

$sqla = "select * from index_test_a where  ID=4999  or title="MysqL索引优化实例(单列索引与组合索引)";";
$sqlb = "select * from index_test_b where  ID=4999  or title="MysqL索引优化实例(单列索引与组合索引)";";
 

测试结果如下：
a表查询所有记录所用时间：0.86毫秒MysqL学习

b表查询所有记录所用时间：47.318毫秒MysqL学习

从上面的结果可以看到,当or中有一个字段没有索引的时候,那么将不会命中索引；反之,如果or运算的所有字段均做了索引,那么是可以命中的.MysqL学习

1.4、测试in,将SQL语句继续改为如下所示：
 MysqL学习

$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毫秒MysqL学习

 可见对于索引字段,in也是可以命中索引的.MysqL学习

1.5、测试<,>,between等,将sql改为如下所示：
 MysqL学习

$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毫秒MysqL学习

可见二者差别不是很大,因此是没有命中索引的.MysqL学习

1.6、对于MysqL函数,索引的命中,将sql改为如下所示：
 MysqL学习

$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毫秒MysqL学习

所以如果在条件中使用函数,那么索引将会失效.MysqL学习

2、组合索引MysqL学习

组合索引包括对多个列的索引,而不是多个单列索引的组合,将表a中的所以改成(Title,num)的组合索引,进行以下测试：MysqL学习

2.1、or测试MysqL学习

将SQL语句改成如下所示：
 MysqL学习

$sqla = "select * from index_test_a where  num=4999  or title="MysqL索引优化实例(单列索引与组合索引)";";
$sqlb = "select * from index_test_b where  num=4999 or title="MysqL索引优化实例(单列索引与组合索引)";";
 

 结果如下所示：
a表查询所有记录所用时间：52.535毫秒
b表查询所有记录所用时间：53.031毫秒MysqL学习

这时索引没有命中,索引组合索引的or运算和两个单列索引的or运算是不同的,前者失效而后者依然有效.MysqL学习

2.2、and测试MysqL学习

将SQL语句改成如下所示：
 MysqL学习

$sqla = "select * from index_test_a where  num=4999  and title="MysqL索引优化实例(单列索引与组合索引)";";
$sqlb = "select * from index_test_b where  num=4999 and title="MysqL索引优化实例(单列索引与组合索引)";";
 

 结果如下所示：
a表查询所有记录所用时间：0.666毫秒
b表查询所有记录所用时间：43.042毫秒MysqL学习

继续改为：
 MysqL学习

$sqla = "select * from index_test_a where  num=4999 ;";
$sqlb = "select * from index_test_b where  num=4999 ;";
 

 得到的结果：
a表查询所有记录所用时间：39.398毫秒
b表查询所有记录所用时间：41.057毫秒MysqL学习

 而改成如下sql：
 MysqL学习

$sqla = "select * from index_test_a where  title="MysqL索引优化实例(单列索引与组合索引)"4999 ;";
$sqlb = "select * from index_test_b where  title="MysqL索引优化实例(单列索引与组合索引)" ;";
 

 得到的结果则为：
a表查询所有记录所用时间：0.753毫秒
b表查询所有记录所用时间：48.248毫秒MysqL学习

由以上三组结果可以看出,组合索引是最左前缀匹配的,即条件中要包含第一个索引列,才会命中索引.MysqL学习

3、索引的优缺点MysqL学习

利用索引可以大大加快我们的搜索,但是维护索引需要额外的开销,尤其是当索引较多的时候,大量的数据会很容易带来索引量的膨胀,因此对于频繁要用到的查询,才需要做索引,这样才能以最小的代价获得最大的性能提升.

MysqL索引优化应用实例
MysqL索引优化注意要点
MysqL索引与MysqL索引优化查询
MysqL索引优化办法解析
深入理解MysqL索引与优化
MysqL索引优化实例分享
MysqL索引使用与优化
分享:MysqL索引优化的技巧
MysqL性能优化之索引优化MysqL学习

《MysqL应用MysqL索引优化实例(单列索引与组合索引)》是否对您有启发，欢迎查看更多与《MysqL应用MysqL索引优化实例(单列索引与组合索引)》相关教程，学精学透。内存溢出PHP学院为您提供精彩教程。

总结

以上是内存溢出为你收集整理的Mysql应用mysql索引优化实例(单列索引与组合索引)全部内容，希望文章能够帮你解决Mysql应用mysql索引优化实例(单列索引与组合索引)所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。