mysql 千万级数据提高查询速度

mysql 8.0, 默认engine: InnoDB,数据放在固态硬盘上

表存放有千万级别的数据,虽然有针对 where查询条件做了联合索引,但查询速度还是有点卡。
使用explain select 发现查询未使用到已建立的索引,导致select进行了全表千万级别数据的扫描。进一步检查发现是表格的索引全部 invisible 状态,导致优化器将索引忽略。
使用这个语句将索引更新为 visible 状态:
ALTER TABLE tbl_name ALTER INDEX idx_name VISIBLE;
这篇文档详细介绍了 explain 的优化思路

另外也查到对 select 操作比较多,没有事务要求的表,其实engine用 MyISAM 比较合适。

MyISAM和InnoDB适合场景的对比:
MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能。

MyISAM适合:

(1)做很多count 的计算;
(2)插入不频繁,查询非常频繁,如果执行大量的SELECT,MyISAM是更好的选择;
(3)没有事务。

InnoDB适合:

(1)可靠性要求比较高,或者要求事务;
(2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况指定数据引擎的创建;
(3)如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表;
(4)DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的 删除;
(5)LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
参考网址

修改语句:
ALTER TABLE tbl_name ENGINE = MyISAM ;

Leave a Reply

Your email address will not be published.