mysql索引和慢查询

1.工作中，可能我们会遇到有些sql语句，我们用了索引，为什么还会记录在慢查询日志中呢？

慢查询：是把sql的执行时间跟long_query_time这个系统参数作比较，大于这个时间，就写写入慢查询日志，通常是1s；查询慢查询的sql语句为：

show variables like 'long_query_time';

设置慢查询的sql语句为：

set long_query_time=1

通过一张表，我们来看看索引：表的ddi语句

create table testTable(
 id int(11) not null,
  a int(11) DEFAULT NULL,
  b int(11) DEFAULT NULL,
PRIMARY key (id),
KEY a (a)

) ENGINE=INNODB

而对于索引，是指sql语句是否执行了索引，比如下面的几个例子：

sql语句为：select * from testTable；key栏是null,是没有索引的，如下图：

sql语句为：select * from t where id=2;key 是Primary,使用了主键索引

 sql语句：select a from testtable;key是啊，说明使用到了a索引

图2和图3都使用了索引，但是图3 是扫描了整个索引书，图2只扫描了一行，如果数据很多的话，图3执行的时间就比较慢了，如果极端的情况下，可能图2的执行也会超过设置的慢查询时间，是否使用索引只是说一个sql的执行过程，而是否进入慢查询是有sql执行的时间决定的，取决于好多元素，所以使用了索引和慢查询没有必然的联系。

执行select * from testtable where id>0;看执行情况是主键索引，看扫描行数一定是全表扫描，但是解释器认为需要查询到第一条id>0的数据，所以使用了索引。

还有那些除了全表扫描，那些只用了索引，执行不够快的例子呢？这就说到了索引的过滤性

假设有一张表，记录了一个城市的人员信息，表的语句

CREATE TABLE `user` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

 如果我们相差年龄在8到12的小朋友，我们的sql语句可能会这样写，

select * from user where age BETWEEN 8 and 12;

这个一般我们会在age上建索引，建了索引之后，执行的时间还是很长，因为数据很大。扫描的行数很大，所以我们在讨论有没有使用索引的时候，我们关心的是扫描行数，对于一个大表，不仅要有索引，索引的过滤性也要比较好。age的索引过滤性不好，索引的区分度要比较高，想身份证号。如果你的过滤性好了，是不是扫描的行数就变少呢，如果这个表上有一个年龄+姓名的过滤性应该不错。如果sql语句是

select * from user where username='张三' AND AGE=3

就可以在索引上快速找到符合条件的数。但是我们如果想查询姓张的并且年龄为8的小朋友，我们的sql语句估计会这样写：

select * from user where username Like '张%' AND age=8

但是这个sql执行过程中回表的次数比较多。mysql 5.6引入了 index condition pushdown的优化。和上面的相比，讲age=3这个条件下推到遍历索引树上，减少了回表的次数。但是这个优化还是没有绕开最左前缀的限制。

我们还可以把姓名的第一个字和年龄做一个联合索引，这里可以使用mysql引入的虚拟列来实现。

sql语句是这样写的：

ALTER TABLE `user` add name_first VARCHAR(2) generated always as (LEFT(username,1)),add INDEX(name_first,age);

执行完成会后，查看表的ddl语句：

CREATE TABLE `user` (
  `userid` int NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `name_first` varchar(2) GENERATED ALWAYS AS (left(`username`,1)) VIRTUAL,
  PRIMARY KEY (`userid`),
  KEY `name_first` (`name_first`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

这个name_first的值不能修改，有定义自动生成，我们如果需要查询姓张且年龄等于22的小朋友，可以这样写

SELECT * FROM `user` where name_first='张' AND age=22;

这个减少了扫描的行数和回表的次数。这次优化的本质就是创建了一个更紧凑的索引。来加速查询的过程。

最后，使用了索引的过程也有可能慢查询，优化的过程就是减少扫描的行数。和回表的次数。