mysql 优化

Question

数据量百万，查询类似如下：

SELECT
    COUNT(*) AS t_count
FROM
    `log_history`
WHERE
    `status` >= 0
AND (
    `txt_id` IN (
        '6',
        '16',
        '19',
        '26',
        '28',
        '39',
        '101',
        '107',
        '117',
        '141',
        '143',
        '144',
        '50',
        '109',
        '54',
        '87',
        '105',
        '110',
        '125',
        '127',
        '130',
        '138',
        '142',
        '145',
        '103',
        '118',
        '104',
        '112',
        '115',
        '123',
        '136',
        '106',
        '116',
        '121',
        '124',
        '126',
        '128',
        '131',
        '146',
        '114',
        '119',
        '120',
        '129',
        '132',
        '137',
        '139',
        '147'
    )
)
LIMIT 1

单表统计，大概10来秒，txt_id已做索引，无效果。如何优化？

EXPLAIN SQL:

Answer 1

EXPLAIN SELECT
    COUNT(*) AS t_count
FROM
    `log_history`
WHERE
    `status` >= 0
AND (
    `txt_id` IN (
        '6',
        '16',
        '19',
        '26',
        '28',
        '39',
        '101',
        '107',
        '117',
        '141',
        '143',
        '144',
        '50',
        '109',
        '54',
        '87',
        '105',
        '110',
        '125',
        '127',
        '130',
        '138',
        '142',
        '145',
        '103',
        '118',
        '104',
        '112',
        '115',
        '123',
        '136',
        '106',
        '116',
        '121',
        '124',
        '126',
        '128',
        '131',
        '146',
        '114',
        '119',
        '120',
        '129',
        '132',
        '137',
        '139',
        '147'
    )
)
LIMIT 1

结果是什么

Answer 2

status加索引，数据库热数据放入ssd存储器