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