mysql索引总结----索引使用测试

1.      mysql索引在某种程度上是可以加快查询的,举一个例子吧

a)首先创建数据库index_test

b)创建表:

Create Table: 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

c)插入测试数据PROCEDURE:

DELIMITER //
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 //

d)执行PROCEDURE:

                   call gen_data();

         以上数据库,表,数据文件添加完毕

e)开启profile,用来查看sql执行时间

mysql> show variables like "%pro%";
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| profiling                 | OFF   |
| profiling_history_size    | 15    |
| protocol_version          | 10    |
| slave_compressed_protocol | OFF   |
+---------------------------+-------+
4 rows in set (0.01 sec)

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

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

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)

g)通过profiles进行查看：

                   mysql>show profiles;

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

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

 

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

         mysql> ALTER TABLE persons ADD indextest(LastName);

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

Records: 1001637  Duplicates:0  Warnings: 0

i)使用索引，重新查询

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)

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

使用explain 进行对比测试结果如下：

mysql> insert into persons (LastName) value("100000000000000000");
Query OK, 1 row affected (0.06 sec)

mysql> explain select * from persons where LastName="100000000000000000";
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | persons | ref  | test          | test | 768     | const |    1 | Using where |
+----+-------------+---------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> select * from persons where LastName="100000000000000000";
+---------+--------------------+-----------+---------+------+
| Id      | LastName           | FirstName | Address | City |
+---------+--------------------+-----------+---------+------+
| 1001638 | 100000000000000000 | NULL      | NULL    | NULL |
+---------+--------------------+-----------+---------+------+
1 row in set (0.00 sec)

mysql> insert into persons (FirstNameName) value("100000000000000000");
ERROR 1054 (42S22): Unknown column 'FirstNameName' in 'field list'
mysql> insert into persons (FirstName) value("100000000000000000");
Query OK, 1 row affected (0.02 sec)

mysql> select * from persons where FirstName="100000000000000000";
+---------+----------+--------------------+---------+------+
| Id      | LastName | FirstName          | Address | City |
+---------+----------+--------------------+---------+------+
| 1001639 | NULL     | 100000000000000000 | NULL    | NULL |
+---------+----------+--------------------+---------+------+
1 row in set (1.26 sec)

mysql> explain select * from persons where FirstName="100000000000000000";
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | persons | ALL  | NULL          | NULL | NULL    | NULL | 1001534 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql>

explain 使用解释：

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。


使用方法，在select语句前加上explain就可以了：


如：
explain select surname,first_name form a,b where a.id=b.id 
EXPLAIN列的解释：
table： 显示这一行的数据是关于哪张表的
type： 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL


possible_keys： 显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句


key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引


key_len： 使用的索引的长度。在不损失精确性的情况下，长度越短越好


ref： 显示索引的哪一列被使用了，如果可能的话，是一个常数


rows： MYSQL认为必须检查的用来返回请求数据的行数


Extra： 关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢


extra列返回的描述的意义


Distinct: 一旦MYSQL找到了与行相联合匹配的行，就不再搜索了


Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一


Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行


Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候


Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上 Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）


system 表只有一行： system表。这是const连接类型的特殊情况


const: 表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。
因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待


eq_ref: 在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref: 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好


range: 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况


index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）


ALL: 这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免