mysql测试索引200万条数据

一、索引介绍

索引是依靠某些数据结构和算法来组织数据，最终引导用户快速检索出所需要的数据。

1.创建索引：
方式1：直接创建

create [unique] index 索引名称 on 表明（列名（lenth)）

方式2：修改表的方式添加索引

alter 表 名 add [unique] index 索引名 on（列名（length)）

如果字段是char、varchar类型，length可以小于字段实际长度，如果是blog、text等长文本类型，必须指定length。
[unique]:中括号代表可以省略，如果加上了unique，表示创建唯一索引。如果table后面只写一个字段，就是单列索引，如果写多个字段，就是复合索引，多个字段之间用逗号隔开。

删除索引

drop index 索引名称 on 表名;

查看索引

show index from 表名;

示例：
准备200万数据

# 测试索引
CREATE DATABASE indextest;
DROP TABLE IF EXISTS test1;

CREATE TABLE `indextest`.`test1`(  
  `id` INT NOT NULL COMMENT '编号',
  `name` VARCHAR(20) NOT NULL COMMENT '姓名',
  `sex` TINYINT NOT NULL COMMENT '性别：1--男,2--女',
  `email` VARCHAR(50)
) ENGINE=INNODB CHARSET=utf8;

# 准备200万数据
/*使用存储过程循环插入200万条数据*/ 
DROP PROCEDURE IF EXISTS proc1;
DELIMITER $
CREATE PROCEDURE proc1()
  BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION;
    WHILE i <= 2000000 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();
SELECT COUNT(*) FROM test1;

二、测试

1.无索引

无索引查询速度，按id查询了一条记录耗时920毫秒，我们在id上面创建索引感受一下变化。

2.id上创建索引

create index idx1 on test1(id);

可以看出加了索引之后我们的查询速度，是原先不加索引的9倍；
3.我们在name上也创建个索引，感受一下查询的神速，如下：

create unique index idx2 on test1(name);

有没有感觉查询快如闪电，索引就是如此神奇。

4.创建索引并指定长度
通过email检索数据

耗时680毫秒，插入时的数据，可以发现所有的email记录，每条记录的前面15个字符都是不同的，结尾是一样的，都是@163.com，通过前面15个字符就可以定位一个email了，那么我们可以对email创建索引的时候指定一个长度为15，这样相对于整个email字段更短一些，查询效果是一样的，这样一个页中可以存储更多的索引记录，命令如下：

create index idx3 on test1(email(15));

看一下查询效果：

耗时连1毫秒都不到。

5.查看表中的索引：

可以看到test1表中3个索引的详细信息（索引名称、类型、字段）

6.删除索引

drop index idx1 on test1;

总结

这篇博客主要是记录索引的一些操作，都是比较基础的东西。通过一个小例子可以看到mysql中的索引的重要性，希望大家能够有收获。 未完待续....