1.表创建语句

 CREATE TABLE`user`(
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 )ENGINE=InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET=utf8 COMMENT ='用户表';

INSERT INTO user(name,age,position,hire_time)VALUES('LiLei',22,'manager',NOW());  INSERT INTO user(name,age,position,hire_time)VALUES('HanMeimei',23,'dev',NOW()); INSERT INTO user(name,age,position,hire_time)VALUES('Lucy',23,'dev',NOW());

2.Mysql如何选择合适的索引

表中字段没有索引

EXPLAIN select * from user where name > 'a';

[图片上传失败...(image-ba338-1594189637012)]

如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描 还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果，如下:

加上name age position 字段索引，走了覆盖索引type=index

EXPLAIN select name,age,position from user where name > 'a' 

image-01.png

EXPLAIN select * from user where name > 'cc' ;

image-03.png

3.常见sql深入优化

3.1Order by与Group by优化

3.1.利用最左前缀法则:中间字段不能断，出现了Using filesort，因为索引的创建顺序为 name,age,position

EXPLAIN
select *
from user
where
    position = 'dev'
order by age ;

image-04.png

3.2利用最左前缀法则:中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用 在排序过程中，因为Extra字段里没有using filesort

EXPLAIN select * from user where name = 'lilei'  order by  age

image-05.png

4.优化总结:

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index

效率高，filesort效率低。
2、order by满足两种情况会使用Using index。

1. order by语句使用索引最左前列。

2. 使用where子句与order by子句条件列组合满足索引最左前列。 3、尽量在索引列上完成排序，遵循索引建立(索引创建的顺序)时的最左前缀法则。
   4、如果order by的条件不在索引列上，就会产生Using filesort。
   5、能用覆盖索引尽量用覆盖索引
   6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。