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()BEGINDECLARE i INT DEFAULT 1;WHILE i <= 1000000 DOINSERT 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.1Connection id:220Current database:index_testCurrent user:root@localhostSSL:Not in useCurrent pager:stdoutUsing outfile:''Using delimiter:;Server version:5.1.73 Source distributionProtocol version:10Connection:Localhost via UNIX socketServer characterset:latin1Db     characterset:latin1Client characterset:latin1Conn.  characterset:latin1UNIX socket:/var/lib/mysql/mysql.sockUptime:4 days 12 hours 58 min 9 secThreads: 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，使用索引，重新查询

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.1Connection id:220Current database:index_testCurrent user:root@localhostSSL:Not in useCurrent pager:stdoutUsing outfile:''Using delimiter:;Server version:5.1.73 Source distributionProtocol version:10Connection:Localhost via UNIX socketServer characterset:latin1Db     characterset:latin1Client characterset:latin1Conn.  characterset:latin1UNIX socket:/var/lib/mysql/mysql.sockUptime:4 days 13 hours 14 min 24 secThreads: 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)

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