mysql百万数据字符串匹配查询速度优化之覆盖索引法

发表于 |

什么是“索引覆盖”？

简单来的说，就是让查询的字段（包括where子句中的字段），都是索引字段。索引覆盖的好处是什么？好处是极大的、极大的、极大的提高查询的效率！重要的说三遍！

特别说明：

  1. where条件中如果使用like关键词，即使字段是索引中的，也不构成索引覆盖。
  2. 单个字段的索引，对于搜索单个where条件有较好的效果。但索引覆盖需要使用多字列索引。

假设创建一个zaho_user表：

1
2
3
4
5
6
7
 CREATE TABLE `zaho_user` (
 `u_id` int(11) NOT NULL AUTO_INCREMENT,
 `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `u_name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
 `u_sex` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别',
 `u_desc` varchar(200) NOT NULL DEFAULT '' COMMENT '介绍'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT '用户表';

这里我们创建一个索引：

1
alter table `zaho_user` add index (`u_name`,`insert_time`,`u_sex`);

那么，我们执行如下查询时，效率将会非常高：

1
select `u_name`,`insert_time` from `zaho_user` where `u_sex`=1

例子中的三个字段：u_name,insert_time,u_sex创建了一个多列索引，而SQL查询时形成了索引覆盖。 在百万数据量匹配查询时速度提升非常可观

网站速度优化

为什么网站访问很慢, 想优化可以参考以下方式, 优先级由高到低:

  1. 数据库位置优化 (优先采用内网访问而非外网)
  2. 硬盘优化 (优先固态硬盘)
  3. SQL语句优化
  4. 服务器硬件和外网带宽升级
  5. 应用代码优化
  6. 定期重启mysql 连接优化 (亲测效果明显)

网站维护记录

本人有一个博客采集站, 内置文章3万左右, 访问和渲染速度非常快, 后来爬取文章增量到6万左右, 感觉网站速度明显变慢, 起初以为是内容过多导致, 可是也才6万就这么慢了那百万数据岂不没法玩了

我甚至在想 可能是服务器配置以及博客程序有限导致的 也没再管它

直到有一天 数据库容器挂了 , 重启数据库后, 网站恢复访问, 速度竟惊人地变快, 和最开始没两样, 于是乎我否断了之前的猜测, 之前速度慢有可能仅仅是数据库连接过多 垃圾缓存导致的

因此 定期重启数据库 似乎可以纳入我的定时任务计划中

本文为作者原创 转载时请注明出处 谢谢

乱码三千 – 点滴积累 ,欢迎来到乱码三千技术博客站

0%