前言
我们知道,索引的选择是优化器阶段的工作,但是优化器并不是万能的,它有可能选错所要使用的索引。一般优化器选择索引考虑的因素有:扫描行数,是否排序,是否使用临时表。
使用explain分析sql
explain是很好的自测命令,勤于使用explain有助于我们写出更合理的sql语句以及建立更合理的索引:
mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1; +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+ | 1 | SIMPLE | t | NULL | range | a,b | b | 5 | NULL | 50223 | 1.00 | Using index condition; Using where | +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+------------------------------------+ 1 row in set, 1 warning (0.01 sec)
其中:
table字段:表示关于哪张表;
type字段:system,const,eq_reg,ref,range,index,all。一般来说要达到range级别以上;
system、const:可以将查询的变量转为常量,如id=1;id为主键或唯一键;
eq_ref:访问索引,返回某单一行的数据,通常在连接时出现,查询使用的索引为主键或唯一键;
ref:访问索引,返回某个值得数据(可能是多行),通常使用=时发生;
range:使用索引返回一个范围内的行信息,如使用>,<,between
index:以索引的顺序进行全表扫描,虽然有索引不用排序,但是要全表扫描;
all:全表扫描
key字段:实际使用的索引;
key_len字段:使用的索引长度(在不损失精度的情况下,长度越短越好);
ref字段:显示索引的哪一列被使用了;
rows字段:MySQL认为检索需要的数据行数;
Extra字段:查询的额外信息,主要有以下几种:
using index:使用了索引
using where:使用了where条件
using tmporary:用到临时表去处理当前查询
using filesort:用到额外的排序,如order字段无索引
range checked for eache record(index map:N):无索引可用
using index for group-by:表名可以在索引中找到分组所需的所有数据,不需要查询实际的表
一般遇到Using temporary和Using filesort就要想办法优化一下了,因为用不到索引。
MySQL怎么计算需要检索的行数
实际中,MySQL所统计的扫描行数并不是精确值,有时候甚至会相差很远,而扫描行数则是基于索引的基数来计算的。
在MySQL中,通过采样统计的方式去获取索引基数:系统默认选取 N 个数据页,统计数据页上不同值得平均值,然后乘以索引的页面数得到基数,而且MySQL会在变更的数据行数超过 1/M 时来触发重做索引统计的操作。
在MySQL中,有2种存储索引统计的方式,可以通过设置innodb_stats_persistent参数来选择:
设置为 on 的时候,表示统计信息会持久化存储。这时,默认的 N 是 20,M 是 10。
设置为 off 的时候,表示统计信息只存储在内存中。这时,默认的 N 是 8,M 是 16。
一般来说,基数统计出来的数据和真实的行数没有很大差距,但是涉及到删除数据新增数据比较频繁的数据表,可能会出现数据表有10万条数据但是基数统计却有20万的情况,这就可能是MVCC在作怪了,因为MySQL的InnoDB的事务支持,需要维持多个数据版本,就有可能某些事务还没结束,还在使用删除了很久的数据导致已删除的数据空间无法释放,而新增的数据又开辟了新的空间,那么这时候就导致基数统计中数据页数量可能出现失误,出现较大误差。
一个很好的修正方式就是执行analyze table 表名,该命令用来重新统计索引信息。
索引选错了我们到底怎么办
当我们正确的建立必须的索引后,大部分情况下,优化器其实并不会选择错索引,当我们遇到索引选错的情况下,该怎么去处理呢?
1、使用force index强制使用某个索引。
2、转换思路,优化一下sql语句可能就会使用到该使用的索引。
3、新建更合适的索引或删除掉误用到的不合理的索引。(有些时候,可能真的是这个索引是多余的,还不是最优的,优化器又刚好使用到了它)。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对的支持。
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
更新日志
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]