[MySQL] mysql优化实例-为join表关联字段增加索引
2023-02-18 15:36:35 时间
在排查所有查询语句效率的过程中 , 发现了join关联表的时候 , 被驱动表没有走索引而是进行的全表扫描
实际的sql语句如下:
explain select a.* from audit_rules a left join audit_rules_detail b on a.id=b.rule_id where a.ent_id=23684
输出:
+----+-------------+-------+------+---------------+------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------------+---------+-------+------+-------+ | 1 | SIMPLE | a | ref | idx_ent_id | idx_ent_id | 4 | const | 12 | | | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 35 | | +----+-------------+-------+------+---------------+------------+---------+-------+------+-------+
看到表b是全表扫描 , 这是因为b的字段rule_id没有索引
增加上索引以后
+----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+ | 1 | SIMPLE | a | ref | idx_ent_id | idx_ent_id | 4 | const | 12 | | | 1 | SIMPLE | b | ref | idx_rule_id | idx_rule_id | 4 | sinanet.a.id | 1 | Using index |
MySQL是只支持一种JOIN算法Nested-Loop Join(嵌套循环链接)
当关联字段有索引时,走的是Index Nested-Loop Join(索引嵌套链接)
没有索引时会走,Block Nested-Loop Join比Simple Nested-Loop Join多了一个中间join buffer缓冲处理的过程
没有索引时:
有索引时
相关文章
- [nginx] 解决:upstream timed out (110: Connection timed out) while reading response header from upstream
- [PHP] session_write_close()的作用
- [PHP]post传递数据时的报错Array to string conversion
- [PHP] laravel框架发送带附件邮件
- [PHP] 解决laravel 报错:Too Many Attempts
- [mysql] mysqldump导出指定数据库表和条件的数据
- [PHP] 解决windows下请求https 报错cURL error 60: SSL certificate problem: unable to get local issuer certificate
- [PHP] 解决laravel Please provide a valid cache path
- [PHP] php使用phpoffice/phpexcel 生成excel文件
- [PHP] Laravel中env函数返回null原因
- [PHP] laravel8 发送通知邮件
- [PHP] hyperf代码热更新-hyperf-watch
- [日常]wps插入页眉页脚
- [PHP] new static()和new self()的区别
- [docker] docker删除容器
- [docker] docker删除镜像
- [PHP] hyperf框架代码热更新
- [PHP] Swoole 关闭短别名swoole.use_shortname
- [PHP] laravel 中__callStatic的使用
- [PHP] laravel data_get函数以及?? ?: 测试用例