sql 优化

 

sql初始化语句　　

DROP TABLE IF EXISTS `t_person`;
CREATE TABLE `t_person` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `birthdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `isMale` bit(1) DEFAULT b'0',
  `height` double DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL COMMENT '部门id',
  `desc` varchar(255) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `union_index_name_age` (`age`,`desc`,`name`) USING BTREE,
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of t_person
-- ----------------------------
INSERT INTO `t_person` VALUES ('1', 'Alan', '20', '2019-09-26 16:14:05', '\0', '1.2', '1', null, '2019-11-29 23:02:40');
INSERT INTO `t_person` VALUES ('2', 'Peter', '18', '2019-09-26 16:13:28', '\0', '2.2', '1', null, '2019-11-29 23:02:42');
INSERT INTO `t_person` VALUES ('3', 'Helen', '20', '2019-09-26 16:17:18', '
', '3.2', '1', null, '2019-11-29 23:02:45');
INSERT INTO `t_person` VALUES ('4', 'Halo', '18', '2019-09-26 16:19:25', '\0', '4.2', '2', null, '2019-11-29 23:02:49');
INSERT INTO `t_person` VALUES ('5', 'Zed', '19', '2019-09-26 16:19:33', '\0', null, '2', null, '2019-11-28 21:57:16');
INSERT INTO `t_person` VALUES ('6', 'Linda', '23', '2019-09-26 16:19:48', '
', null, '4', null, '2019-11-28 11:42:24');
INSERT INTO `t_person` VALUES ('7', 'Sinda', '19', '2019-11-28 12:35:37', '\0', null, '1', null, '2019-11-28 12:35:37');
INSERT INTO `t_person` VALUES ('8', 'Neo', '23', '2019-11-28 16:30:16', '\0', null, '4', null, '2019-11-28 16:30:16');

 

explain的解析的字段

　　type字段，效率从低到高排列：ALL < index < range < ref < ref_eq < const, system

　　all，全表扫描

　　index，根据索引来读取数据，如果索引中已经包含了查询数据，则只需要扫描索引树，否则也需要进行全表扫描

　　range，有范围的索引扫描

　　ref，非唯一索引的访问

　　eq_ref，唯一性索引查找

　　const，常量查找，

 

explain type 的const和ref，

　　const出现的场景：使用唯一索引（或者主键）进行相等查询时

　　ref出现的场景：使用非唯一索引进行相等查询时

　　下面的sql语句，如果name是唯一索引，则执行计划的类型为const，如果是非唯一索引，则为ref

explain select * from t_person where name = "Alan";

 

explain type 的ref和eq_ref，

　　eq_ref，我只在联合查询中重现出来过，就是其中一个表和另外地一个表的关联键值，这个关联的列，得是唯一索引或者主键索引（有个细节，如果两个表都有数据，则执行计划分析出来是ref而不是eq_ref，我把其中一个表的数据清空了之后，才出来eq_ref）

 

explain的extra

　　using index, 使用了覆盖索引

　　using where， 

　　using filesort， 使用文件排序，常见于group by 或者order by

　　using temporary，使用临时表，常见于group by，union

　　impossible where、no matching row in const table，没有符合条件的行

 

导致无法命中索引的因素：

　　1. 在where条件中使用不等于判断条件，<>、!=、is not null等

explain select * from t_person where name <> "a";

　　2. where中使用like，并且在左边加上通配符%

explain select * from t_person where name like "%a";

　　3. where中，对列进行算术运算

explain select * from t_person where age -1 > 20;

　　4. 使用in、not in，也无法命中索引

explain select * from t_person where age in (19,20);

　　

优化策略

　　索引覆盖：

正例：explain select id from t_person where id >3;

反例：explain select * from t_person where id >3;

　　子查询优化成联合查询

子查询：explain select * from t_person where department_id in (select id from t_department);

联合查询：explain select p.* from t_person p join t_department d on p.department_id = d.id

　　使用between代替in

使用in：explain select * from t_person where age in (19,20,21);

使用between：explain select age from t_person where age BETWEEN 19 and 21;

　　使用union来替换or（当or的连个条件一个有索引，另外一个没有索引时，通过union分隔查询来使其中一个命中索引）

union： explain select * from t_person where age = 12 union select * from t_person where height >3

or： explain select * from t_person where age = 12 or height >3