mysql索引创建以及索引使用测试

 

对于刚开始学习sql索引的时候，只知道索引可以使sql的查询速度更快但是一直不能很直观的利用数据来测试到底有多快，怎么区测试索引的优化效果如何。

 

下面就将测试方法及步骤如下

1，创建一个表

 

CREATE TABLE `persons` (  
  `Id` int(11)  not null auto_increment,  
  `LastName` varchar(255) DEFAULT NULL,  
  `FirstName` varchar(255) DEFAULT NULL,  
  `Address` varchar(255) DEFAULT NULL,  
  `City` varchar(255) DEFAULT NULL,  
  PRIMARY KEY (`Id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2,插入测试数据--此处只能使用存储过程来插入数据了，创建存储过程gen_data()

 

 

CREATE PROCEDURE gen_data()
	BEGIN
		DECLARE i INT DEFAULT 1;
		WHILE i <= 1000000 DO
			INSERT INTO persons (LastName,FirstName,Address) VALUES ((SELECT floor(rand() * 1000) AS randNum), (SELECT floor(rand() * 1000) AS randNum),(SELECT floor(rand() * 1000) AS randNum));
			SET i = i + 1;
		END WHILE;
	END

 

 

3,执行此存储过程，并将数据插入到persons表中

call gen_data();

 

4,利用profile来查看sql的执行时间

a）首先来查看profile是否开启

执行语句：show variables like "%pro%";

 

 

4,开启profile用来查看sql执行时间。

 

 

从图中可以看出profile没有开启，现在需要开启profile

b）开启profile的命令：set profiling=1;

5，执行操作并查看执行过程和所用时间等信息

 

mysql> select count(*) from persons where LastName=3\s;
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:		220
Current database:	index_test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.1.73 Source distribution
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			4 days 12 hours 58 min 9 sec
Threads: 2  Questions: 3006129  Slow queries: 0  Opens: 31  Flush tables: 1  Open tables: 13  Queries per second avg: 7.663
--------------

+----------+
| count(*) |
+----------+
|      979 |
+----------+
1 row in set (0.63 sec)

 

 

6，通过profiles进行查看：

 

 

                   mysql>show profiles;

                   9| 0.63250900 | select count(*) from persons where LastName=3

  其中的一条数据，数据过多且比较不美观，我提取了比较重要的一句，也就是上面的sql语句，执行时间是0.63250900

 

7，增加索引，目前的索引是最最简单的索引，为了直观的测试索引真的在某种程度上可以提高查询速度

         ALTER TABLE persons ADD index indextest(LastName);

Query OK, 1001637 rows affected (4 min 5.94 sec)

Records: 1001637  Duplicates:0  Warnings: 0

8，使用索引，重新查询

 

 

<span style="color:#333333">mysql> select count(LastName) from persons where LastName=3\s;
--------------
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id:		220
Current database:	index_test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.1.73 Source distribution
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			4 days 13 hours 14 min 24 sec

Threads: 2  Questions: 3006150  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 12  Queries per second avg: 7.644
--------------

+-----------------+
| count(LastName) |
+-----------------+
|             979 |
+-----------------+
1 row in set (0.45 sec)</span>

执行速度变为了0.45s,也就是说在某称程度上增加了查询数据。我的机器单核，内存小但结果的可见性还是很直观的