mysql sql优化实例1(force index使用)

本文针对一条耗时2.7秒的MySQL慢查询进行优化,通过调整WHERE子句位置及使用FORCE INDEX,成功将查询时间缩短至17毫秒,提升了100倍。


今天和运维同学一块查找mysql慢查询日志,发现了如下一条sql:

SELECT sum(`android` + ios) total,pictureid,title,add_time FROM `juzi_access_statistic` LEFT JOIN juzi_news ON juzi_access_statistic.pictureid=juzi_news.id GROUP BY `pictureid` HAVING total >= 100000 AND pictureid >= 8092 AND title IS NOT NULL LIMIT 2;


该sql语句花费了 2.7s,那么总共多少条呢?


总共54万条记录,其实也不算太慢,但是我觉得应该有很大的优化空间。


一:先看一下表结构

CREATE TABLE `juzi_access_statistic` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `pictureid` int(11) NOT NULL DEFAULT '0' COMMENT '文章id',

  `date` date NOT NULL COMMENT '日期',

  `ios` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '苹果app访问量',

  `android` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '安卓app访问量',

  PRIMARY KEY (`id`),

  UNIQUE KEY `pictureid` (`date`,`pictureid`) USING BTREE,

  KEY `indexpictureid` (`pictureid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘访问统计表’;

二: 查看一下执行计划




在这里我发现了问题:juzi_access_statistic表竟然是全表扫描,pictureid字段上存在索引,为什么没有使用上呢?

我个人觉得原因是:因为存在条件pictureid >= 8092 和  juzi_access_statistic.pictureid=juzi_news.id(等价传递),所以mysql觉得使用juzi_news表的主键id查询效率是最高的。


三:使用force index 优化




看到效果了吧:key字段显示使用到了pictureid字段的索引,但扫描的行没有减少,执行时间如下:




四:正确使用where

   以上sql中的pictureid >= 8092 其实应该放到where子句中,以便过滤到更多的无用记录,修复后的执行计划如下:




效果也很明显:rows减少到不足10万,速度可想而知,见下图:




从开始的2.74s 优化到17ms, 优化了100倍


### 如何通过 MySQL 命令行导入 `.sql` 文件 要通过 MySQL 命令行导入 `.sql` 文件,可以通过两种主要方法实现:一种是在命令行外部直接调用 `mysql` 工具完成导入;另一种是进入 MySQL 的交互模式后使用 `source` 命令来加载 SQL 文件。 #### 方法一:直接在命令行中执行 可以直接通过命令行一次性完成数据库创建以及 SQL 文件的导入操作。以下是具体的操作: ```bash mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS db_name;" mysql -u root -p db_name < /path/to/import_script.sql ``` 上述命令的第一部分用于创建目标数据库[^1],第二部分则将指定路径下的 `.sql` 文件内容导入到刚刚创建的数据库中。注意替换 `/path/to/import_script.sql` 和 `db_name` 为实际使用的文件路径和数据库名称。 如果希望忽略导入过程中可能出现的一些警告或错误信息,则可以在第二个命令的基础上增加 `--force` 参数: ```bash mysql -u root -p db_name --force < /path/to/import_script.sql ``` #### 方法二:进入 MySQL使用 source 命令 另外一种方式是先进入 MySQL 控制台再利用内部指令完成数据恢复工作。按照如下步骤操作即可: 1. 打开终端或者 CMD 并切换至超级管理员身份; 2. 输入以下语句连接服务器实例(会提示输入密码): ```bash mysql -u root -p ``` 3. 成功登录之后建立新的存储空间(假如尚未存在的话),例如: ```sql CREATE DATABASE IF NOT EXISTS db_name; USE db_name; ``` 4. 接下来采用 `source` 指定脚本位置来进行批量处理: ```sql source /path/to/your_file.sql; ``` 此过程同样能够达到预期效果,并且相对更加直观一些[^4]。 需要注意的是,在尝试以上任何一种方案前,请确认本地的服务进程处于活动状态。如果没有正常开启可能会遇到无法访问的情况,解决办法可参照相关内容介绍[^5]。 ```python # 示例 Python 脚本模拟自动化流程 (仅作演示用途) import subprocess def run_command(command): result = subprocess.run(command, shell=True, capture_output=True, text=True) if result.returncode != 0: raise Exception(f"Error executing command: {result.stderr}") return result.stdout try: create_db_cmd = "mysql -u root -pYOUR_PASSWORD -e \"CREATE DATABASE IF NOT EXISTS test_db;\"" output_create = run_command(create_db_cmd) import_sql_cmd = f"mysql -u root -ptest_db YOUR_PASSWORD < '/absolute/path/to/file.sql'" output_import = run_command(import_sql_cmd) except Exception as e: print(e) ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值