mysql的一个表中有60万条数据查询近一分钟

Question

我的mysql数据库中的一个表有将近60万条数据，在网页上是分页显示的，一页显示50条数据，显示速度近一分钟，请大神们帮忙看看，打赏分必须多
我的sql语句是：select idx,namex,parentx,sj,paixu,filex,attr0,attr2,attr3,attr4,attr5,attr19,specialx from lanmu_zhaobiaogonggao where date_sub(curdate(), interval 6 month) <= date(attr2) order by attr2 desc
attr2是时间字段，where条件后面是要显示这个表近6个月的数据。
我的表结构是：
我给这个表加的索引是：

Answer 1

date_sub(curdate(), interval 6 month) <= date(attr2)改成attr2＞＝date_sub(curdate(), interval 6 month)

Answer 2

可通过explain sql语句，查看执行计划

Answer 3

select idx,namex,parentx,sj,paixu,filex,attr0,attr2,attr3,attr4,attr5,attr19,specialx from lanmu_zhaobiaogonggao order by attr2 desc
现在我不加任何条件，现在这个表下面也才只有四十多万条数据，不应该查询这么慢呢，一分钟都出不来信息

Answer 4

select idx,namex,parentx,sj,paixu,filex,attr0,attr2,attr3,attr4,attr5,attr19,specialx from lanmu_zhaobiaogonggao order by attr2 desc
现在我不加任何条件，现在这个表下面也才只有四十多万条数据，不应该查询这么慢呢，一分钟都出不来信息

Answer 5

你这个查得慢，估计是走全表扫描的原因，不走索引是因为你使用了date(attr2);
如果attr2是时间类型的数据，那么这个列就不应该是longtext类型，找个时间把这个列的类型转换成datetime吧，这样就不需要在SQL中使用date来转换类型了，也就可以使用attr2上的索引；另外，最好贴一下执行计划，和说明一下你需要查询的数据占比是多少，例如你的表数据是60万，那么你要查询的六个月数据是多少呢？

Answer 6

我现在的sql是这样写的了
select idx,namex,parentx,sj,paixu,filex,attr0,attr2,attr3,attr4,attr5,attr19,specialx from lanmu_zhaobiaogonggao where attr2>=date_sub(curdate(), interval 6 month) order by attr2 desclanmu_zhaobiaogonggao表中有439539条数据，近6个月的数据是397661条执行计划是：
lanmu_zhaobiaogonggao这个表的索引是：

Answer 7

explain查看是否使用了索引

Answer 8

我现在的sql是这样写的了
select idx,namex,parentx,sj,paixu,filex,attr0,attr2,attr3,attr4,attr5,attr19,specialx from lanmu_zhaobiaogonggao where attr2>=date_sub(curdate(), interval 6 month) order by attr2 desclanmu_zhaobiaogonggao表中有439539条数据，近6个月的数据是397661条执行计划是：
lanmu_zhaobiaogonggao这个表的索引是：从执行计划来看，你的SQL没用到索引，所以要处理一下attr2列的类型问题，要使用索引，就不能对attr2使用date函数做转换。

Answer 9

我where后面的子句就一个attr2字段，目前我查询的近6个月的数据占总数据的90%，问题是我现在要是把attr2去掉，直接从表中访问得到全部的数据100%，现在表中是441385条数据为什么会这么慢呢，这样的话根本就走不了索引这条线了，这要如何解决呢？
select idx,namex,parentx,sj,paixu,filex,attr0,attr2,attr3,attr4,attr5,attr19,specialx from lanmu_zhaobiaogonggao

Answer 10

打个比方，你翻一本书的时候需要把90%的内容都看一遍，那么书的目录对你来说有意义不？
一个表有这么多 longtext类型，而且排序也是 longtext字段，快不起来。
缩小数据范围，减少需要显示的列，在分页后再读取出来，用日期或者it主键来排序。
有些时候光从技术上是不好优化的，需要和产品需求等协商

Answer 11

1、你现在的语句：
select idx,namex,parentx,sj,paixu,filex,attr0,attr2,attr3,attr4,attr5,attr19,specialx from lanmu_zhaobiaogonggao   order by attr2 desc
这样一般情况下是使用不到索引的，如果是mysql8的话，建议在"attr2"加上降序索引，在解释计划中你可以看到使用了索引。
2、这个字段的数据类型能缩小的话尽量缩小。在你一开始的语句中有date(attr2)，那么是否可以考虑一下加一个date(attr2)的虚拟列，在虚拟列上使用普通索引，在作为查询条件时，相信速度会有所提升

Answer 12

attr2列是longtext字段类型，占用内存会比较大，查看下执行计划，是否用到了磁盘排序，是的话可以适当调大 sort_buffer_size

Answer 13

select 
    idx,namex,parentx,sj,paixu,filex,attr0,attr2,attr3,attr4,attr5,attr19,specialx 
from 
    lanmu_zhaobiaogonggao 
where  
    attr2 > '2019-02-07 00:00:00' -- 日期在程序中计算
order by 
    attr2 desc;-- attr2  字段类型 datatime，或  时间戳（int） -- 已经有了  索引
attr2_idx日期也可以用时间戳

Answer 14

首先，单表查询，除了数据条数以外，最好关注一下表占用空间的大小，怎么查询表占用空间的大小，可以百度一下。
第二，看这个表，有好多字段都是longtext类型的，有必要一定用longtext类型吗，就像您说的attr2字段，既然是存储的时间类型，那么就该成date类型应该就可以。
建议，把attr2修改成时间类型的，并且加索引。然后把语句改成这样。
select idx,namex,parentx,sj,paixu,filex,attr0,attr2,attr3,attr4,attr5,attr19,specialx from lanmu_zhaobiaogonggao 
where attr2>=date_sub(curdate(), interval 6 month)  order by attr2 desc
另外，不建议用时间做索引字段，这个属于一个小众化的知识点，开始的时候数据量不大，可能感觉不到什么，但是当数据量大了以后，特别是系统运行时间比较长的时候，效率可能会特别低。
有两个方式解决这个问题。
第一，定期把时间字段的索引删除后重新建立。
第二，把日期类型的字段，修改成字符型的，时间格式是年月日时分秒，修改成秒分是日月年。因为时间字段后录入的数据，肯定比之前的大，修改成秒分是日月年，新来的数据，就不一定会是最大值了。
当然，如果您只用到了年月日，修改成日月年也是可以的。
最后，有必要一次性返回这么多的数据吗，是否可以多个查询，每个查询，只查询一个月的，或者一天的，最后union到一起，应该也会提高效率。