MySQL 如何正确的使用索引( 三 ) _MySQL

上面说的各种情况，大家都多看一下图中数据，认真分析一下查询的过程，基本上都可以理解了。
上面这种查询叫做最左匹配原则。
索引区分度
我们看2个有序数组
[1,2,3,4,5,6,7,8,8,9,10]
[1,1,1,1,1,8,8,8,8,8]
上面2个数组是有序的，都是10条记录，如果我需要检索值为8的所有记录，那个更快一些？
咱们使用二分法查找包含8的所有记录过程如下：先使用二分法找到最后一个小于8的记录，然后沿着这条记录向后获取下一个记录，和8对比，知道遇到第一个大于8的数字结束，或者到达数组末尾结束。
采用上面这种方法找到8的记录，第一个数组中更快的一些。因为第二个数组中含有8的比例更多的，需要访问以及匹配的次数更多一些。
这里就涉及到数据的区分度问题：
索引区分度 = count(distint 记录) / count(记录) 。
当索引区分度高的时候，检索数据更快一些，索引区分度太低，说明重复的数据比较多，检索的时候需要访问更多的记录才能够找到所有目标数据。
当索引区分度非常小的时候，基本上接近于全索引数据的扫描了，此时查询速度是比较慢的。
第一个数组索引区分度为1，第二个区分度为0.2，所以第一个检索更快的一些。
所以我们创建索引的时候，尽量选择区分度高的列作为索引。
正确使用索引
准备400万测试数据
/*建库JAVAcode2018*/DROP DATABASE IF EXISTS javacode2018;CREATE DATABASE javacode2018;USE javacode2018;/*建表test1*/DROP TABLE IF EXISTS test1;CREATE TABLE test1 ( id INT NOT NULL COMMENT '编号', name VARCHAR(20) NOT NULL COMMENT '姓名', sex TINYINT NOT NULL COMMENT '性别,1：男，2：女', email VARCHAR(50));/*准备数据*/DROP PROCEDURE IF EXISTS proc1;DELIMITER $CREATE PROCEDURE proc1() BEGIN DECLARE i INT DEFAULT 1; START TRANSACTION; WHILE i <= 4000000 DO INSERT INTO test1 (id, name, sex, email) VALUES (i,concat('javacode',i),if(mod(i,2),1,2),concat('javacode',i,'@163.com')); SET i = i + 1; if i%10000=0 THEN COMMIT; START TRANSACTION; END IF; END WHILE; COMMIT; END $DELIMITER ;CALL proc1();

上面插入的400万数据，除了sex列，其他列的值都是没有重复的。

无索引检索效果

400万数据，我们随便查询几个记录看一下效果。

按照id查询记录
mysql> select * from test1 where id = 1;+----+-----------+-----+-------------------+| id | name | sex | email |+----+-----------+-----+-------------------+| 1 | javacode1 | 1 | javacode1@163.com |+----+-----------+-----+-------------------+1 row in set (1.91 sec)

id=1的数据，表中只有一行，耗时近2秒，由于id列无索引，只能对400万数据进行全表扫描。

主键检索

test1表中没有明确的指定主键，我们将id设置为主键：

id被置为主键之后，会在id上建立聚集索引，随便检索一条我们看一下效果：

mysql> select * from test1 where id = 1000000;+---------+-----------------+-----+-------------------------+| id | name | sex | email |+---------+-----------------+-----+-------------------------+| 1000000 | javacode1000000 | 2 | javacode1000000@163.com |+---------+-----------------+-----+-------------------------+1 row in set (0.00 sec)这个速度很快，这个走的是上面介绍的`唯一记录检索` 。
between and范围检索
mysql> select count(*) from test1 where id between 100 and 110;+----------+| count(*) |+----------+| 11 |+----------+1 row in set (0.00 sec)