MySQL 索引优化（一）

索引创建原则

1. 先写代码，再建索引
   不应该在创建完表之后立马就创建索引，等主体业务开发完毕以后，再把涉及到该表的 sql 分析过后再建立索引。
2. 联合索引尽量覆盖查询条件
   在设计一个联合索引的时候，让联合索引尽可能的包含 sql 语句中的 where、order by、group by 的字段，还要确保这些字段尽量满足 sql 最左前缀原则。
3. 选取索引字段选值比较多、区分度高的字段
   比如我有一张用户表，它有一个性别字段，只包含男女两种值，这就没有办法进行快速的二分查找，不如全表扫描，索引也就失去意义了。建立索引，尽量选取值比较多的字段，才能更好的发挥 B+ 树二分查找的优势。
4. 长字符串可以采用前缀索引
   选取索引尽量对索引字段类型较小的列设计索引，字段小，占用磁盘空间也小，搜索的时候性能也会好一些。如果需要给长字符串建立索引，比如 varchar(255)，比如这时就可以针对这个字段的前 20 歌字符建立索引，也就是说，把这个字段每个值的前面 20 位放到索引树里，比如 KEY index(name(20),age,position)。
5. where 和 order by 冲突时优先 where
   在 where 和 order by 出现索引设计冲突时，优先让 where 条件去使用索引快速筛选出一部分数据，再进行排序。因为大多数情况下，基于索引进行 where 筛选往往可以最快速筛选出需要的少部分数据，再进行排序的成本可能就会降低很多。
6. 数据量足够大的情况下再建立索引
   一般单表超过十万条数据以后，为了改善用户体验，再建立索引。

索引优化案例演示

示例表结构和数据

CREATE TABLE `student` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
	`age` INT(11) NOT NULL DEFAULT '0' COMMENT '年龄',
	`school` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学校名',
	`start_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
	PRIMARY KEY (`id`),
	KEY `idx_name_age_school` (`name`,`age`,`school`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='学生表';

INSERT INTO student(NAME,age,school,start_time) VALUES('老大',11,'老大小学',NOW());
INSERT INTO student(NAME,age,school,start_time) VALUES('老二', 12,'老大小学',NOW());
INSERT INTO student(NAME,age,school,start_time) VALUES('老三',13,'老大小学',NOW());


DROP PROCEDURE IF EXISTS insert_student;
DELIMITER ;;
CREATE PROCEDURE insert_student()
BEGIN
	DECLARE i INT;
	SET i=1;
	WHILE(i<=100000)DO
		INSERT INTO student(NAME,age,school) VALUES(CONCAT('老',i),i,'老大小学');
		SET i=i+1;
	END WHILE;
END;;
DELIMITER ;
CALL insert_student();

覆盖查询优化

第一个例子，联合索引的