1.数据生成

1.创建数据库

CREATE DATABASE `test_bai` -- 创建数据库 
USE `test_bai` -- 切换对应的数据库

2.创建表

CREATE TABLE `app_user`(
	`id` INT  NOT NULL AUTO_INCREMENT COMMENT '主键',
	`name` VARCHAR(50) DEFAULT '' COMMENT '用户名称',
	`email` VARCHAR(50) NOT NULL COMMENT '邮箱',
	`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
	`gender` TINYINT DEFAULT '0' COMMENT '性别（0-男  ： 1-女）',
	`password` VARCHAR(100) NOT NULL COMMENT '密码',
	`age` TINYINT DEFAULT '0' COMMENT '年龄',
	`create_time` DATETIME DEFAULT NOW(),
	`update_time` DATETIME DEFAULT NOW(),
	PRIMARY KEY (`id`) 
)ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT='app用户表'

3.新增100万条数据的函数

SET GLOBAL log_bin_trust_function_creators=TRUE; -- 创建函数一定要写这个
DELIMITER $$   -- 写函数之前必须要写，该标志

CREATE FUNCTION mock_data()	-- 创建函数（方法）
RETURNS INT 		-- 返回类型
BEGIN			-- 函数方法体开始
	DECLARE num INT DEFAULT 1000000; -- 定义一个变量num为int类型。默认值为100 0000
	DECLARE i INT DEFAULT 0; 
	
	WHILE i < num DO -- 循环条件
		 INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) 
		 VALUES(CONCAT('用户',i),'2548928007qq.com',CONCAT('18',FLOOR(RAND() * ((999999999 - 100000000) + 1000000000))),FLOOR(RAND()  *  2),UUID(),FLOOR(RAND()  *  100));
		SET i =  i + 1;	-- i自增	
	END WHILE;		-- 循环结束
	RETURN i;
END; -- 函数方法体结束

4.调用函数

SELECT mock_data(); -- 调用函数

2.索引实践

1.不加索引

首先在email和phone两个字段不加索引的情况下执行一个查询语句，并分析

EXPLAIN SELECT * FROM app_user WHERE email="2548928007qq.com" and phone="181818284465";

可以看到，没有走索引，总共查询了992427条数据，而表中总共也是一百万条数据，相当于全表扫描了。

2.加单独索引

alter table app_user add index `idx_email`(`email`);
alter table app_user add index `idx_phone`(`phone`);

可以看到：加上索引后and查询是可以走索引的，但是只有一个索引起作用，对于另一个索引字段还是要进行遍历，而且and查询会根据关联性高(符合该条件的行数少)选择具体走哪个索引。

3.explain

通过创建索引的手段来优化我们的查询sql语句后，通过explain便可详细分析我们的sql好坏，Mysql官方对explain的作用描述如下：当EXPLAIN与可解释语句一起使用时，MySQL将显示来自优化器的关于语句执行计划的信息。也就是说，MySQL解释了它将如何处理语句，包括关于表如何连接以及以何种顺序连接的信息。

explain能解释Mysql是如何处理sql语句，以及表的加载顺序，连接情况，以及索引的使用情况，是sql优化的重要工具。执行一条explain语句，重要字段分析如下：

EXPLAIN SELECT * FROM app_user WHERE email="xxxxxx.com" and phone="181818284465";

id，表明执行顺序，id相同，由上到下执行；id不同，从大到小执行。
select_type 表明select的查询类型，有simple，primary，union，subquery等。
table 显示数据来自于哪张表。
type 直接表明sql语句的性能是否高效，性能由好到坏排序为：
- system 针对系统表查询，且表数据只有一行，几乎不会出现。
- const 命中主键索引或唯一索引的等值查询，只会返回一行数据，效率极高。
- eq_ref 在多表查询情况下，命中主键索引或唯一索引的等值连接查询。
- ref 命中普通索引，返回匹配某个单独值得所有数据。
- fulltext 全文索引，仅在myisam引擎中才会用到。
- ref_or_null ref的特例，在ref的查询基础上加上or column = null。
- range 范围查询，命中索引列中的between and，IN，like，>,<,is null
- index 全盘扫描索引树，性能差，但优于全表扫描，一般用于count统计
- all 全表扫描，未命中索引，性能最差
possible keys（重要） , 该条sql语句可能会命中的索引
key（重要） , 该条sql语句实际命中的索引
Key_len（重要） , 命中的索引长度（索引所用到的字节数），在联合索引中有大用处
- 索引为int类型时，占用4个字节；bigint类型，占用8个字节；date类型，占用3个字节；datetime类型，占用4个字节
- 索引为char类型时，char(n)占用 m × n个字节，n为字符个数，m为1个字符占用的字节大小。因此，m视字段的字符集变动，utf8对应的m为3，utf8mb4对应的m为4，gbk对应的m为2
- 索引为varchar类型时，varchar(n)占用m × n + 2个字节，2个字节记录varchar的实际长度，如果该varchar字段允许为null，所占用的字节数还要多1
row, MySQL预估要扫描的行数，这个不一定准
Extra（重要） , 执行sql产生的额外信息
- Using index, 运用到了覆盖索引，只用到索引的信息，性能高
- Using Where, MySQL的Server层在引擎层检索后再进行的过滤
- Using filesort, 排序时出现，表明用到了额外内存来排序
- Using temporary, 创建临时表来查询，常发生于无索引情况
- Using index condition, 出现索引下推

参考链接:blog.csdn.net/shadow_2011… juejin.cn/post/731078…

MySQL 学习笔记（三）生成百万数据 练习索引