MySQL索引的示例剖析
发布时间：2022-02-09 16:14:24 所属栏目：MySql教程 来源：互联网
导读：这篇文章给大家分享的是有关MySQL索引的示例分析的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 前言 我们知道，索引的选择是优化器阶段的工作，但是优化器并不是万能的，它有可能选错所要使用的索引。一般优化器选择索引考虑
|
这篇文章给大家分享的是有关MySQL索引的示例分析的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 前言 我们知道，索引的选择是优化器阶段的工作，但是优化器并不是万能的，它有可能选错所要使用的索引。一般优化器选择索引考虑的因素有：扫描行数，是否排序，是否使用临时表。 使用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、新建更合适的索引或删除掉误用到的不合理的索引。（有些时候，可能真的是这个索引是多余的，还不是最优的，优化器又刚好使用到了它）。 感谢各位的阅读！关于“MySQL索引的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！ （编辑：拼字网 - 核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐