一：基础数据准备

DROP TABLE IF EXISTS `tbl_user`;

CREATE TABLE `tbl_user` (

  `id` int() NOT NULL AUTO_INCREMENT,

  `username` varchar() DEFAULT NULL,

  `email` varchar() DEFAULT NULL,

  `age` tinyint() DEFAULT NULL,

  `type` int() DEFAULT NULL,

  `create_time` datetime DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;

INSERT INTO `tbl_user` VALUES

('', 'admin', 'admin@126.com', '', '', '2018-07-09 11:08:57'),

('', 'mengday', 'mengday@163.com', '', '', '2018-07-09 11:09:00'),

('', 'mengdee', 'mengdee@163.com', '', '', '2018-07-09 11:09:04'),

('', 'root', 'root@163.com', '', '', '2018-07-09 14:36:19'),

('', 'zhangsan', 'zhangsan@126.com', '', '', '2018-07-09 14:37:28'),

('', 'lisi', 'lisi@gmail.com', '', '', '2018-07-09 14:37:31'),

('', 'wangwu', 'wangwu@163.com', '', '', '2018-07-09 14:37:34'),

('', 'zhaoliu', 'zhaoliu@163.com', '', '', '2018-07-11 18:29:24'),

('', 'fengqi', 'fengqi@163.com', '', '', '2018-07-11 18:29:32');

DROP TABLE IF EXISTS `tbl_userinfo`;

CREATE TABLE `tbl_userinfo` (

  `id` int() NOT NULL AUTO_INCREMENT,

  `address` varchar() DEFAULT NULL,

  `user_id` int() DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_userId` (`user_id`)

) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8;

INSERT INTO `tbl_userinfo` VALUES

('', '上海市', ''),

('', '北京市', ''),

('', '杭州', ''),

('', '深圳', ''),

('', '广州', ''),

('', '海南', '');

二：五百万数据插入

上面插入几条测试数据，在使用索引时还需要插入更多的数据作为测试数据，下面就通过存储过程插入500W条数据作为测试数据

-- 修改mysql默认的结束符号，默认是分号；但是在函数和存储过程中会使用到分号导致解析不正确

DELIMITER $$

-- 随机生成一个指定长度的字符串

CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR()

BEGIN

 # 定义三个变量

 DECLARE chars_str VARCHAR() DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

 DECLARE return_str VARCHAR() DEFAULT '';

 DECLARE i INT DEFAULT ;

 WHILE i < n DO

   SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(+RAND()*), ));

   SET i = i + ;

 END WHILE;

 RETURN return_str;

END $$

-- 创建插入的存储过程

CREATE PROCEDURE insert_user(IN START INT(), IN max_num INT())

BEGIN

    DECLARE i INT DEFAULT ;

    SET autocommit = ;

    REPEAT

        SET i = i + ;

        INSERT INTO tbl_user VALUES ((START+i) ,rand_string(), CONCAT(rand_string(), '@random.com'), +FLOOR(RAND()*), , NOW());

        UNTIL i = max_num

    END REPEAT;

   COMMIT;

END $$

-- 将命令结束符修改回来

DELIMITER ;

-- 调用存储过程，插入500万数据，需要等待一会时间，等待执行完成

CALL insert_user(,);

-- Query OK,  rows affected ( min 49.89 sec) 

SELECT COUNT(*) FROM tbl_user;

三：使用索引和不使用索引的比较

使用索引之前的查询

然后给username创建索引再次查询（数据库卡死了，我用sqlyog做）

创建索引用了40秒，属实有点慢

然后再查询试试，基本是秒查了，效率提升很明显

之前再黑窗口加的索引也上去了

然后删除一个索引，byusername

四：explain命令

explain参数详解

查看索引的使用情况：show status like 'Handler_read%'

Handler_read_key: 越高越好
Handler_read_rnd_next:越低越好

查询优化器：

重新定义表的关联顺序(优化器会根据统计信息来决定表的关联顺序)
将外连接转化成内连接(当外连接等于内连接)
使用等价变换规则（如去掉1=1）
优化count()、min()、max()
子查询优化
提前终止查询
in条件优化

mysql可以通过 EXPLAIN EXTENDED 和 SHOW WARNINGS 来查看mysql优化器改写后的sql语句

下图提示我们别用*查询，应该写具体那一列

五：走索引的情况和不走索引的情况

1. in走索引

in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。

2. 范围查询走索引

但是条件必须是一个具体的值，如果条件为 now() 当前时间，则会导致全表扫描

3. 模糊查询只有左前缀使用索引

4. 反向条件不走索引 != 、 <> 、 NOT IN、IS NOT NULL

一个优化的实例：

# 常见的对not in的优化，使用左连接加上is null的条件过滤

SELECT id, username, age FROM tbl_user WHERE id NOT IN (SELECT user_id FROM tbl_order);

SELECT u.id, u.username, u.age

FROM tbl_user u

LEFT JOIN tbl_order o ON u.id = o.user_id

WHERE o.user_id IS NULL;