非集群环境下分区表的mysql select查询优化

Question

我对包含 1.23 亿条记录的分区表进行选择查询，需要 10 分钟以上才能获取数据。我的查询看起来像 'select * from tableName where column1='1.1.1.1' order by timestamp desc';表已在第 1 列上建立索引。

感谢任何帮助。

(来自评论)

CREATE TABLE mytable (
    column1 varchar(256) NOT NULL, 
    column2 varchar(100) NOT NULL, 
    column3 smallint(5) unsigned NOT NULL, 
    column4 smallint(5) unsigned NOT NULL, 
    timestamp bigint(20) unsigned NOT NULL, 
    KEY mytable_idx (column2,timestamp,column3,column4), 
    KEY ip_addr_index (column1), 
    KEY ts_idx (timestamp)
    )  /*!50100 PARTITION BY RANGE ((TIMESTAMP))
           (PARTITION p1498800000 VALUES  LESS THAN (1498800000) ENGINE = InnoDB,
            PARTITION p1500000000 VALUES  LESS THAN (1500000000) ENGINE = InnoDB,
            PARTITION p1501200000 VALUES  LESS THAN (1501200000) ENGINE = InnoDB,
            PARTITION p1502400000 VALUES  LESS THAN (1502400000) ENGINE = InnoDB,
            PARTITION p1503600000 VALUES  LESS THAN (1503600000) ENGINE = InnoDB,
            PARTITION p1504800000 VALUES  LESS THAN (1504800000) ENGINE = InnoDB,
            PARTITION p1506000000 VALUES  LESS THAN (1506000000) ENGINE = InnoDB
                          ) */

Answer 1

对于此查询:

select *
from tableName
where column1 = '1.1.1.1'
order by timestamp desc;

您想要在(column1，timestamp desc)上建立索引。注意:在早期版本的 MySQL 中，desc 可能会被忽略。