MYSQL之慢查询与索引

慢查询为查询时间超过指定值，mysql中语句执行超过设定值long_query_time，即被记录到慢查询日志中去，一般作为查询的优化手段，建立索引是一个比较不错的选择，那么我们可以说我们建立好索引后，sql用到了此索引，就可以解决慢查询问题了吗？

其实，mysql慢查询与索引其实没有必然的联系，因为查询的过滤性与索引的过滤性不同。我们以下面为例子。

现在我们有一个用户列表，表结构如下：

名称	类型	长度
id	bigint	0
name	varchar	10
phone	varchar	20
age	int	0
adreess	varchar	50

 

 

 

 

 

 

字段name与age建立联合索引index_name|_age（name,age）;

假设我们现在要查询表中符合name为张三，年龄为20的数据，我们的sql为：

select t.* from t_test t where t.name ='张三' and t.age=20 

这时候我们发现查询的语句很快，原因如下：

1.符合条件的语句很少；

2.索引的过滤性足够好，根据最左匹配原则，name与age是查询全等数据，且Btree的查询规则为顺序查询,那么在维护的联合索引树上只要从用第一个符合name为张三，age为20，顺序查找到name不为张三，或者age不为20的数据即可，

以我的模拟数据为例子，表中的模拟数据如下：

 

id	name	phone	age	adreess
1	张三	13100000000	20	北京市
2	张三	13100000000	20	天津市
3	张三	13100000000	21	武汉市
4	张四	13100000000	20	石家庄市

 

 

 

 下面我们看下sql执行计划,语句为：

explain select t.* from t_test t where t.name ='张三' and t.age=20 

执行结果如下：

d	select_type	table	partitions	type	possible_keys	key	key_length	ref	rows	filtered	Extra
1	SIMPLE	t		ref	iindex_name_age	index_name_age	38	const	2	100

 

 

根据rows可以看出，我们只查询了符合条件的2行数据。

那么下面我们有了一个新的要求，要查询出符合name第一个名字为张的，年龄为20的数据，那么我们该怎么办呢，我们可能会这样编写sql：

select t.* from t_test t where t.name like '张%' and t.age=20 

查询结果是我们想要的，并且查看下执行计划：

d	select_type	table	partitions	type	possible_keys	key	key_length	ref	rows	filtered	Extra
1	SIMPLE	t		range	iindex_name_age	index_name_age	38		4	100	Using index condition

 

 

我们用到了我们建立的联合索引，这时我们可能以为达到要求了，但是rows中的4，数据扫描了表中的所有数据，即为四行，同时，Extra中Using index condition 为通过联合索引优化了你的回表次数，那么我们可以减少表的扫描行数吗，答案是肯定的。我们使用5.7版本后引入的虚拟列，具体语法如下：

ALTER TABLE t_test ADD name_first VARCHAR (2) generated always AS (LEFT(NAME, 1)),ADD INDEX (name_first, age);

这时我们看表中就多了一个名为name_first的列，并且多了一个名字为name_first的索引，此时的name_first列不可更改与指定，是随着我们name字段自动生成的，这时我们重新编写sql语句：

select t.* from t_test t where t.name_first ='张' and age =20

我们看下执行计划，语句省略，执行计划如下：

d	select_type	table	partitions	type	possible_keys	key	key_length	ref	rows	filtered	Extra
1	SIMPLE	t		ref	name_first	name_first	14	const,const	3	100

 

 

 

可以看到，根据最左匹配原则，过滤器只需要查找符合规定的数据即可，同样省去了优化器帮助我们减少回表次数。

这时由于之前的索引树中，数据存储的顺序是这样的（树形状省略）：（张三，20），（张三，20），（张三，21），（张四，20），这样一来顺序匹配的要经过（张三，21），所以为四行。

我们加入虚拟列之后的联合索引存储数据为（树形状省略）：（张,20）,（张,20）,（张,20）,（张,21）,这样我们只要根据顺序匹配前三个即可，然后根据取出的主键id，进行回表数据查找。