56,687
社区成员
发帖
与我相关
我的任务
分享
EXPLAIN
SELECT * FROM sms_send_real_log_hisdata_20150801 WHERE STATUS = '1'
SELECT SUM(sms_num),username,STATUS,sms_type FROM sms_send_real_log_hisdata_201508
WHERE send_time >'2015-08-01' AND send_time < '20150831'
GROUP BY username,STATUS, sms_type
你看我这种情况应该如何加索引[/quote]
把 send_time字段加在第一个位置:
send_time,username,STATUS, sms_type
你这个是 date类型的吗,不是字符串类型吧
SELECT SUM(sms_num),username,STATUS,sms_type FROM sms_send_real_log_hisdata_201508
WHERE send_time >'2015-08-01' AND send_time < '20150831'
GROUP BY username,STATUS, sms_type
你看我这种情况应该如何加索引
EXPLAIN
SELECT SUM(sms_num),STATUS,username,sms_type FROM sms_send_real_log_hisdata_201508
GROUP BY sms_type,STATUS,username
显示已使用索引
SELECT SUM(sms_num),STATUS,username,sms_type FROM sms_send_real_log_hisdata_201508
GROUP BY sms_type,STATUS,username
sql语句执行速度没上去是为啥。。[/quote]
其实正在要让查询快,索引的作用有效。
一般在oltp系统中,比如淘宝的交易系统,之所以速度挺快,原因是因为 都是对少量数据的操作,不管是更新、查询,这种场景下,索引就能起作用。
而你现在的是没有过滤条件,是整个表的所有数据,虽然建了索引,但也是对索引进行扫描,效率不会有太大的提升,这种就是索引作用不到的情况。
EXPLAIN
SELECT SUM(sms_num),STATUS,username,sms_type FROM sms_send_real_log_hisdata_201508
GROUP BY sms_type,STATUS,username
SELECT SUM(sms_num),STATUS,username,sms_type FROM sms_send_real_log_hisdata_201508
GROUP BY sms_type,STATUS,username
SELECT SUM(sms_num),username,STATUS,sms_type FROM sms_send_real_log_hisdata_201508
GROUP BY username,STATUS,sms_type
[/quote]
那就应该4列建索引:
username,STATUS,sms_type, sms_num
这样的好处是 数据已经排序了,直接从索引访问数据就可以了,不需要再去访问表
SELECT SUM(sms_num),username,STATUS,sms_type FROM sms_send_real_log_hisdata_201508
GROUP BY username,STATUS,sms_type