我有以下查询需要在某些页面上运行多达 40-50 次。
SELECT lid, dest, rebuild FROM {store_links_link}
WHERE source = :url;
Source 列是 URL,问题是 urls/source 的某些值确实很长,这会极大地减慢查询速度。
我知道它应该固定在应用程序级别,但是可以在 Mysql 配置中进行哪些更改来加速此查询?
store_links_link 表有超过 50,000 个链接,有些链接可能很长。我启用了查询缓存,这在一定程度上有所帮助。以前,该应用程序在 MySql 5.6 上运行,它能够很好地应对,但 Mariadb 10 根本无法应对。
输出SHOW CREATE TABLE
:
CREATE TABLE store_links_link
(
lid char(32) NOT NULL COMMENT 'Link ID using MD5, also act as cloaked link.',
accid int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Account ID from affiliate_links_account.accid.',
SOURCE varchar(5000) DEFAULT NULL,
dest text NOT NULL COMMENT 'Converted link URL with ID info.',
rebuild tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 'Whether the account link should be rebuilt.',
COUNT int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Click count for cloaked link.',
nid varchar(255) DEFAULT NULL COMMENT 'Node ID',
created timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'creation and upadtion time of source/dest link',
PRIMARY KEY (lid),
KEY affiliate_urls (source(768))
) ENGINE=InnoDB DEFAULT
CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='URL link conversion table.'
您可以尝试在 reverse(source(32)) 上添加索引,并将查询更改为
768对于一个索引键来说太大了,会使索引树的层级变深,比较计算耗时;但少量会降低索引键的独特性。
source
因此,在具有可比较的小长度值的列的反向值上创建索引应该是有帮助的。似乎表上的所有
char
/类型都不是真正的,因为 urls、md5 和可能的 nid(做检查)只是.varchar
utf8mb4
latin1
更改为 latin1 将减少这些存储的大小,因此需要更少的 IO 来读取索引以找到该项目。
如果可能,应避免部分索引。更改为 latin1 后,最大长度为 3072。是否可以将长度减少
source
到这个?或者,为了减少索引大小,您可以存储源的 sha1,将其作为单独的列进行索引并对其进行查找(通过在应用程序中执行 sha1)吗?这将进一步减小索引大小。
文本(和 blob)不与主键一起存储,需要在内部进行额外查找以检索 url。更改
dest
为 avarchar
以将其内联存储。lid
你在or上做更多的查找吗source
?最常见的应该是主键,因为主键查找速度更快。如果您在 #2 中更改了它,请使用的作为sha1
主键。source
很可能以前在 MySQL-5.6 上的表是 MyISAM,它可以通过操作系统文件缓存获得可观的收益。更改为 InnoDB,尤其是 MariaDB-10.6+,可能需要显式设置一个
innodb_buffer_pool_size
. 一个好的起点是 80% 的可用 RAM。如果您每页需要 40-50 个查询,则需要时间。即使在配置良好的 1 毫秒响应时间上,这也只是检索时间的约 50 毫秒。
考虑一种方法,单个查询可以一次检索所有需要的 URL。确保这也被正确索引。