记一次MySql索引使用记录

   学习MySql索引,看下数据量多的情况下,使用索引来进行查询和不使用索引来进行查询的区别大不大。当然这里准备的数据,肯定不会是啥公司的数据或者什么真实的数据,是通过存储函数来进行插入的100w的数据,来进行索引操作的使用学习记录。

---

 

先来创建一张表 ; 这里我记录的使用的索引是 InnoDB

create table t_users (
    id bigint(20) not null auto_increment,
    name varchar(255) not null,
    age bigint(20) not null,
    num bigint(20) not null,
    primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

使用存储过程来插入数据

DELIMITER $$
CREATE
 PROCEDURE `test`.`insert_user_func`()
    BEGIN
     DECLARE i INT;
     SET i=0;
     WHILE  i>=0 && i<= 1000000 DO
           INSERT INTO test.`t_users`
                   (`name`, `age`, `num`) VALUES
                   (CONCAT('format-',i),i,RAND()*100);
           SET i=i + 1;
     END WHILE;
     
    END$$

 

调用存储过程

call insert_user_func();

调用完了;MySql 需要一点时间去插入数据。

可以使用count(1) 来查询看你这个表中的数量多少。

select count(1) from test.t_users; 

 

  当存储函数执行完了的时候,数据就来了,就可以看到具体的效果。

  先来看下用不用索引的效果

 

 

这个上面就是name 这个字段是否使用索引的情况,可以从时间和explain的分析上可以看出来效果。因为在存储过程中,name这个字段是根据i的一次叠加,所以是不会又重复的。

下面来看下num这个随机生成100以内的数据,是肯定会有重复的数据的。

 

 

虽然我们在num字段上加了索引，但是由于num值在这100w条数据中有很多重复的数据，这个时候索引对查询速度的提高就没有那么明显了。 num 和 name这二个字段的区别就是在于,name这个字段是唯一的,num是会有很多重复的,从explain中的rows就可以看出来效果了。

因为不论是hash类型的索引还是btree类型的索引，他们对于重复的数据的查询并没有提高多少。相反，由于添加了索引，导致数据写入性能变差，而查询性能又没有增强多少。所以说不能盲目地添加索引。

---

 

下面来看下复合索引; 复合索引是支持最左匹配原则.

删除之前的单个索引;创建一个复合索引.复合索引支持最左原则，也就是说INDEX_FOR_NAME_AGE索引支持name字段的查找，支持name，age字段的查找，但是不支持age，name字段的查找以及age字段的查找。

drop index IDX_FOR_NUM on t_users;
drop index IDX_FOR_NAME on t_users;

create index INDEX_FOR_NAME_AGE on t_users(name, age);

 

从这个图片上的效果看出来,当我们使用age name 去组合的时候,居然也会中索引.这是怎么回事呢？

这是因为MySQL内部有个查询优化器帮我们进行了优化。

---

是不是索引创建完了就完事了呢？如果索引没有使用上,那么和不创建索引有啥区别呢？

下面来看看索引失效的  explain来分析的.

索引创建之后，查询的过程并不一定会使用索引。整理如下(t_users表中name和num字段都有单独的索引)：

当使用索引查询比全表扫描更慢。比如下面这句sql中num字段的值分布在1-10之间

mysql> explain select * from t_users where num > 1 and num < 8;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
| id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref  | rows   | filtered | Extra                            |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
|  1 | SIMPLE      | t_users | NULL       | range | IDX_FOR_NUM   | IDX_FOR_NUM | 8       | NULL | 112416 |   100.00 | Using index condition; Using MRR |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+--------+----------+----------------------------------+
1 row in set, 1 warning (0.01 sec)

使用or进行查询，并且or左右两边的列有不存在索引的列

mysql> explain select * from t_users where name = 'format-4000' or age = 50;
+----+-------------+---------+------------+------+---------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                   | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_users | NULL       | ALL  | INDEX_FOR_NAME_AGE,IDX_FOR_NAME | NULL | NULL    | NULL | 996519 |    19.00 | Using where |
+----+-------------+---------+------------+------+---------------------------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

or两边的列都有索引：

mysql> explain select * from t_users where name = 'format-4000' or num = 50;
+----+-------------+---------+------------+-------------+---------------------------------------------+--------------------------------+---------+------+-------+----------+---------------------------------------------------------------+
| id | select_type | table   | partitions | type        | possible_keys                               | key                            | key_len | ref  | rows  | filtered | Extra                                                         |
+----+-------------+---------+------------+-------------+---------------------------------------------+--------------------------------+---------+------+-------+----------+---------------------------------------------------------------+
|  1 | SIMPLE      | t_users | NULL       | index_merge | INDEX_FOR_NAME_AGE,IDX_FOR_NUM,IDX_FOR_NAME | INDEX_FOR_NAME_AGE,IDX_FOR_NUM | 767,8   | NULL | 18205 |   100.00 | Using sort_union(INDEX_FOR_NAME_AGE,IDX_FOR_NUM); Using where |
+----+-------------+---------+------------+-------------+---------------------------------------------+--------------------------------+---------+------+-------+----------+---------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

使用like，并以 % 开头

mysql> explain select * from t_users where name like "%format-200";
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996519 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

 

以 % 结尾的查询会使用索引：

mysql> explain select * from t_users where name like "format-200%";
+----+-------------+---------+------------+-------+---------------------------------+--------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                   | key                | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------------------------+--------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t_users | NULL       | range | INDEX_FOR_NAME_AGE,IDX_FOR_NAME | INDEX_FOR_NAME_AGE | 767     | NULL | 1111 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------------------------+--------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.02 sec)

 

对varchar 类型进行查询的时候,不加上''的问题

mysql> explain select * from t_users where name = 111;
+----+-------------+---------+------------+------+---------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                   | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_users | NULL       | ALL  | INDEX_FOR_NAME_AGE,IDX_FOR_NAME | NULL | NULL    | NULL | 996519 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------------------------+------+---------+------+--------+----------+-------------+
1 row in set, 5 warnings (0.00 sec)

explain select * from t_users where name = "111";

 

mysql> explain select * from t_users where name = "111";
+----+-------------+---------+------------+------+---------------------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                   | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_users | NULL       | ref  | INDEX_FOR_NAME_AGE,IDX_FOR_NAME | INDEX_FOR_NAME_AGE | 767     | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

从explain 中可以看出具体的Sql效果是怎么样的。

---

总结:

   hash索引和btree索引的区别

1. hash索引不能使用范围查询，只能使用一些比如 “=”, “<>”, “in”查询。因为hash索引会计算索引列的hash值，计算出后的hash值经过了hash算法与原先的值完全不一样，只能进行等值的过滤，不能基于范围的过滤
2. hash索引遇到大量hash值相同的情况下，性能比btree要差
3. hash索引并不一定一次可以定位到数据。因为基于索引列计算出的hash值会有重复，重复的话需要扫描hash表进行比较
4. 由于hash索引中存放的是经过hash计算之后的hash值，而且hash值的大小关系并不一定和hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
5. 对于组合索引，hash索引在计算hash值的时候是组合索引键合并后再一起计算hash值，而不是单独计算hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，hash索引也无法被利用
6. InnoDB和MyISAM引擎不支持hash索引