mysql慢查询优化实例------建立索引

       我们都知道， 很多时候， 要对数据库增加索引， 可以提升查询速度， 有兴趣的可以看看数据库索引原理， 本文来实际测试一下。
       测试数据库记录大概10000条， 先看看没有索引的情况。 

       表结构为：

mysql> show create table tb_test;                            
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| Table   | Create Table                                                                                                                                                                                                                                                                                                                 |  
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| tb_test | CREATE TABLE `tb_test` (  
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',  
  `name` varchar(32) NOT NULL COMMENT 'test',  
  `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'test',  
  PRIMARY KEY (`id`)  
) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 COMMENT='测试表， 无实际作用'          |  
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
1 row in set (0.00 sec)  

       开启profiling(命令是set profiling=1;),  然后进行两次 select操作， 分析结果如下：

mysql> show profiles;                            
+----------+------------+----------------------------------------------------+  
| Query_ID | Duration   | Query                                              |  
+----------+------------+----------------------------------------------------+  
|    10021 | 0.00306534 | select * from tb_test where name = 'n1'            |  
|    10022 | 0.00298983 | select * from tb_test where score = 1              |  
+----------+------------+----------------------------------------------------+  

      

       将name字段设置为索引， 表结构为：

mysql> show create table tb_test;                            
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                            |  
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| tb_test | CREATE TABLE `tb_test` (  
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',  
  `name` varchar(32) NOT NULL COMMENT 'test',  
  `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'test',  
  PRIMARY KEY (`id`),  
  KEY `idx_name` (`name`)  
) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 COMMENT='测试表， 无实际作用'          |  
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
1 row in set (0.00 sec)  

       再次进行两次相同的select操作， 分析结果如下：

mysql> show profiles;                            
+----------+------------+----------------------------------------------------+  
| Query_ID | Duration   | Query                                              |  
+----------+------------+----------------------------------------------------+  
|    10021 | 0.00306534 | select * from tb_test where name = 'n1'            |  
|    10022 | 0.00298983 | select * from tb_test where score = 1              |  
|    10023 | 0.00074623 | select * from tb_test where name = 'n1'            |  
|    10024 | 0.00298101 | select * from tb_test where score = 1              |  
+----------+------------+----------------------------------------------------+  

       可见， Query_ID为10023的那条查询快了很多。

       继续将score字段设置为索引， 表结构为：

mysql> show create table tb_test;  
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                         |  
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
| tb_test | CREATE TABLE `tb_test` (  
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',  
  `name` varchar(32) NOT NULL COMMENT 'test',  
  `score` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'test',  
  PRIMARY KEY (`id`),  
  KEY `idx_name` (`name`),  
  KEY `idx_score` (`score`)  
) ENGINE=InnoDB AUTO_INCREMENT=10004 DEFAULT CHARSET=utf8 COMMENT='测试表， 无实际作用'          |  
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+  
1 row in set (0.00 sec) 

       进行两次相同的select操作， 分析结果如下：

mysql> show profiles;                            
+----------+------------+----------------------------------------------------+  
| Query_ID | Duration   | Query                                              |  
+----------+------------+----------------------------------------------------+  
|    10021 | 0.00306534 | select * from tb_test where name = 'n1'            |  
|    10022 | 0.00298983 | select * from tb_test where score = 1              |  
|    10023 | 0.00074623 | select * from tb_test where name = 'n1'            |  
|    10024 | 0.00298101 | select * from tb_test where score = 1              |  
|    10027 | 0.00073865 | select * from tb_test where name = 'n1'            |  
|    10028 | 0.00072649 | select * from tb_test where score = 1              |  
+----------+------------+----------------------------------------------------+  

        可见， Query_ID为10027和10028d的那两条快了很多。



        综上所述： 
        1. 索引能提升查询速度。 当然， 索引也有弊端， 不能任意妄为。
        2. 如上记录仅仅是10000条， 如果记录条数为100万， 那么索引的效果就更能很好体现了。