MySQL选错索引的原因以及解决方案_Mysql

来源:脚本之家  责任编辑:小易  

先从数据结构的角度来答。 题主应该知道B-树和B+树最重要的一个区别就是B+树只有叶节点存放数据,其余节点用来索引,而B-树是每个索引节点都会有Data域。 这就决定了B+树更适合用来存储外部数据,也就是所谓的磁盘数据。 从Mysql(Inoodb)的角..www.zgxue.com防采集请勿采集本网。

MySQL 中,可以为某张表指定多个索引,但在语句具体执行时,选用哪个索引是由 MySQL 中执行器确定的。那么执行器选择索引的原则是什么,以及会不会出现选错索引的情况呢?

在一步步引出B树以及为什么MySQL数据库索引选择使用B+树! 学过数据结构的一般对 AVL和红黑树,似乎我们还没有摸到MySQL为什么要使用B+树作为索引的实现,不要急,接

先看这样一个例子:

索引,但这不是组合索引。组合索引,即一个索包含多个列。 MySQL索引类型包括: 一、   INDEX [indexName] (username(length))   );  -- 删除索引的语

创建表 Y,设置两个普通索引, 创建一个存储过程用于插入数据。

这种情况全表扫描还要快于利用索引,只要理解索引的本质不难明白MySQL为何不使用索引。 极端点的情况,90万的数据,source只有0和1两个值,利用索引要先读索引文件,然后

MySQL: 5.7.27, 隔离级别: RR

索引mysql也不会使用,如果使用了索引有可能会更慢 请检查你的那三个字段,是不是唯一性太差,删除索引再试试 2. 学会使用 Explain 和Profiling,找出真正原因才能解决 3. 优

CREATE TABLE `Y` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `b` (`b`)) ENGINE=InnoDB;

`table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引 mysql&g

delimiter ;;create procedure idata()begin declare i int; set i=1; while(i<=100000)do insert into Y (`a`,`b`) values(i, i); set i=i+1; end while;end;;delimiter ;call idata();

那为什么还需要其他的树形索引呢? 在这里愚安就不自己总结了。引用下园子里其他大神的文章:来自 14的路 的MySQL的btree索引和hash索引的区别 (1)Hash 索引仅仅能满足

查看如下事务:

建表时,在字段定义后面,加key 索引名(字段名.) 如果表已存在,用alter 语句 alter table add index 索引名(字段名.) 准确的索引可以提高查询的效率, 索引太多或太大,会造成资

Session A Session B
start transaction with consistent snapshot;
delete from t;
call idata();
explain select * from Y where a between 10000 and 20000;
explain select * from Y force index(a) where a between 10000 and 20000;
commit;

city usernname 为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左 (6)索引的不足之处 上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此

如果单独执行 Session B 中 select * from Y where a between 10000 and 20000;,毫无疑问会选择 a 这个索引。

如果要删除的数据量比较小的话对索引以及性能是微乎其微的,但是一旦需要删除的数据很大(比如总数据1KW,500W是需要删除的数据)就会对造成整体查询变慢,新数据的插入

但如果安装 Session A,Session B 的顺序执行,发现索引的选择如下:

DATA_DIR/*/*.MYI 其中的/tmp/mysql.sock是MySQL监听的Sock文件位置,对于使用RPM安装的用户应该是/var/lib/mysql/mysql.sock,对于使用源码安装则是/tmp/mysql.sock可

可以发现,在 Session B 的场景下,执行器却没有选择 a 所在的索引,而是选择基于主键索引的全表扫描。

mysql explain explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助 key_len 使用的索引的长度。在不损失精确性的情况下,长度越短越好 ref 显示索引的哪

set long_query_time=0;--将慢查询日志打开,并将阙值设为 0. 在记录的日志中,可以发现 MySQL 并没有选择 a 所在的索引,同时花费了更长的时间。

mysql查询in用不上索引,说明查询语句本身有问题。 MySQL是查询语句,最好用Access 包括几个表中的数据,也可以使用查询同时更新或删除几个记录,以及对数据执行预定义

这样看,MySQL 的优化器不一定每次都能选择合适的索引。想要理解出现该现象的原因,就要从优化器的选择逻辑说起。

这种情况下影响结果集巨大,就会全表扫描。这种情况全表扫描还要快于利用索引,只要理解索引的本质不难明白MySQL为何不使用索引。

优化器

MySQL 中优化器的目的就是找到一个最优的执行方案,从而用最小的代价去执行语句。

优化器在选择索引时,主要会考虑如下的因素: 扫描的行数:扫描的行数越少,就证明访问磁盘数据的次数越少,消耗的 CPU 资源就越少。 有没有涉及到临时表 排序

关于扫描行数的确定

计算索引的基数

MySQL 在执行语句前,其实并不能准确的计算出扫描的行数,而是通过数学统计信息来估算记录数。这个统计信息被称为索引的“区分度”,在索引上不同的值越多,区分度就越高。在一个索引上不同值的个数,称为“基数”。基数越大,索引的区分度越好。

这里的 Cardinality 就是索引的基数,但基数并不是完全准确的。MySQL 是在获取基数时,实际上是采用采样统计的方式。

计算时,会选择 N 个数据页,并统计这些页面上的不同值,得到一个平均值,然后乘以该索引的页面数,然后得到的就是索引的基数。

在 MySQL 中,有两种存储索引的方式,可通过设置 innodb_stats_persistent 来切换: on 时:表示统计信息会持久化存储,默认 N 为 20,M 为 10. off 时,统计信息仅会存储在内存中,默认 N 为 8,M 为 16.

由于表中数据是不断变化的,所以当更新的值超过 1/M 时,会自动触发索引统计。

但需要注意的是,由于是采样统计,所以基数的值不是准确的。

预估扫描行数的错误

之前看到,执行 Select * from Y where a between 10000 and 20000 预估的行数是 100015,这个是能理解的,因为走的是全表扫描。

之后执行 select * from Y force index(a) where a between 10000 and 20000 预估的行数是 37116,这个就不能理解了,理想的情况下应该是 10001 行 (需要遍历到 20001)。

而且更奇怪的是,虽然 37116 行的预估行数不太合理,但也远小于全表扫描的 100015,为什么优化器还是选择全表扫描呢?

首先先看第二个问题,选择 100015 的原因是因为如果使用索引 a 的话,除了需要在 a 索引扫描外,还需要回表,主键索引上的查询代价,优化器也需要算进去,所以选择了全表扫描。

这时再看第一个问题,为什么没有得到正确的行数。这个就和一致性视图有关了,首先 Session A 中,开启了一致性视图,并没有提交。之后的 Session 清空了 Y 表后,又重新创建了相同的数据,这时每行数据都有两个版本,旧版本是 delete 前的数据,新版本是标记为删除的数据。所以索引 a 上的数据其实有两份。也就造成了行数的预估错误。

mysql 是通过标记删除的方法来删除记录的,并不是在索引和数据文件中真正的删除。而且由于一致性读的保证,不能删除 delete 的空间,再加上 insert 的空间。导致统计信息有误。

选用错误索引的解决办法

对于行数预估错误的情况, 可采用如下的方法:

如果遇到 EXPLAIN 和预估的行数,数值相差较大时,可以通过analyze table 来重新统计索引信息。

直接通过 force index 强制指定需要使用的索引,不让优化器进行判断。但使用 force 也可能带来一些问题: 迁移数据库时,语法不支持 不容易变更并且不太方便,因为选错索引的情况一般不会经常发生,在生产环境出现问题后,才需要改代码,但还需要重新进行上线测试,部署。

优化 SQL 语句,引导优化器使用正确的索引

再看一个类似的例子:

先来看一下这句

SQL select * from Y where a between 1 and 1000 and b between5000 100000 order by b limit 1;

在执行这句话时,可以选索引 a,也可以选索引 b. 我们知道,每个索引对应了一颗B+树。这里由于取得是 a 和 b 的交集,如果选用索引 a 的话,需要遍历 1 - 10001 行。选用索引 b 需要遍历 50000 - 100001 行。理论上来说,应该选择 a 作为索引,可以优化器又偏偏选择了 b 作为索引。

这里选择 b 作为索引的原因,是因为优化器看到了后面的 order by 语句,由于要排序,而 B+ 树本身就是有序的,省去了排序的过程,所以选择了 b 作为索引。

但从实际的执行时间来看,索引 a 执行时间更短,所以这里 MySQL 又选择了错误的索引。

我们可以将上述语句中 order by b limit 改为 order by b,a limit 1 这时由于 a,b 索引都要排序,扫描的行数就成为执行器主要参考的条件,引导选择正确的索引。

这样做的前提一定要保证执行的逻辑结果是一致的,比如在 limit 1 的情况下,order by b,a order by b 的结果一致,如果换成 limit 100 就不一定了。

还有一种改发

select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;

现在可以看到,优化器选择了合适的索引。原因在于 limit 100 让优化器认为,使用索引 b 的代价较高,进而选择索引 a. 其实就是通过 limit 100 诱导优化器做出选择。

调整索引

能否找到更优,更合适的索引,或者利用索引的原则,删除一些不必要的索引。

总结

现在我们知道,MySQL 在选择索引时,是会出现错误的情况的。优化器选择索引的原则主要有三个,扫描的行数,是否存在临时表,以及排序。行数的扫描,主要和基数有关,而基数的统计则是通过统计抽样决定的,进而预估的行数可能会是不准确的。

在遇到扫描的行数不正确时,可以通过 analyze table 来重新统计表的信息,通过 force index 强制指定索引,或通过手动改变 sql 的语义,诱导优化器做出正确的选择。

以上就是MySQL选错索引的原因以及解决方案的详细内容,更多关于MySQL 索引的资料请关注真格学网其它相关文章! 您可能感兴趣的文章:MySql索引提高查询速度常用方法代码示例MySQL索引失效的几种情况汇总详解MySQL 聚簇索引与非聚簇索引Mysql索引性能优化问题解决方案导致MySQL索引失效的一些常见写法总结MySQL索引的各种类型MySQL性能优化之如何高效正确的使用索引MySQL索引的基本语法Mysql之组合索引方法详解

mysql教程:索引的使用以及索引的优缺点1. 索引(index)是帮助MySQL高效获取数据的数据结构。它对于高性能非常关键,但人们通常会忘记或误解它。索引在数据越大的时候越重要。规模小、负载轻的数据库即使没有索引,也能有好的性能, 但是当数据增加的时候,性能就会下降很快。Tip:蠕虫复制,可以快速复制大量的数据例:insert into emp select * from emp; 2. MySQL中常见的索引 ◆普通索引 ◆唯一索引 ◆主键索引 ◆组合索引 ◆全文索引◆外键 (只有innodb存储引擎才支持)2.1普通索引:这是最基本的索引,它没有任何限制。有以下几种创建方式:有以下几种创建方式:◆创建索引CREATE INDEX indexName ON tablename(username(length)); ◆修改表结构ALTER tablename ADD INDEX indexName (username(length))Tip:length可以小于字段实际长度;如果是BLOB 和 TEXT 类型,必须指定length ,下同◆创建表的时候直接指定CREATE TABLE mytableuuu( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX indexName (username(length)) ); CREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL);create index index1 on mytable(id); //创建普通索引◆删掉索引:drop index index1 on mytable;有一个概念,行定义:在声明字段(列)的时候定义的,比如primary key表定义:在所有字段(列)声明完之后定义的,比如primary key,indexCREATE TABLE mytable(id INT NOT NULL,username VARCHAR(16) NOT NULL,index index1(username));3.0唯一索引(unique)索引列的值必须唯一,但允许有空值。1)创建索引:Create UNIQUE INDEX indexName ON tableName(tableColumns(length))2)修改表结构:Alter tableName ADD UNIQUE [indexName] ON (tableColumns(length)3)创建表的时候直接指定:Create TABLE tableName ( [...], UNIQUE [indexName](tableColumns(length)); 4.0主键索引(primary key)内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • mysql read_buffer_size 设置多少合适
  • mysql中无group by情况下直接使用having语句的问题探究
  • mysql中text与blob字段类型的区别
  • mysql 5.6 从陌生到熟练之_数据库备份恢复的实现方法
  • mysql中decimal类型用法的简单介绍
  • mysql指定日期区间的提取方法
  • mysql innodb表空间卸载、迁移、装载的使用方法
  • sysbench对mysql压力测试的详细教程
  • 解析mysql备份与恢复简单总结与tee命令的使用介绍
  • 带你5分钟读懂mysql字符集设置
  • 如何创建mysql索引以及索引的优缺点
  • 为什么有关MongoDB采用B树索引,以及Mysql B+树做索引
  • MySQL索引的缺点以及MySQL索引在实际操作中有哪些事项
  • mysql 索引分类以及用途分析
  • 为什么MySQL数据库索引选择使用B+树?
  • MySQL索引类型总结和使用技巧以及注意事项
  • MySQL 索引使用 为什么不起作用
  • 一个很困惑的mysql索引问题,跪求解决方案!!!!!
  • MYSQL 如何建立索引及如何使用索引
  • MYSQL数据库索引类型都有哪些
  • 如何创建mysql索引以及索引的优缺点
  • mysql的索引是那种数据结构,为什么使用这种数据结构
  • mysql 中,逻辑删除对索引以及性能会造成什么影响
  • mysql 中,逻辑删除对索引以及性能会造成什么影响
  • Mysql建表与索引使用规范详解
  • mysql查询in为什么用不上索引
  • mysql建立的索引为什么没有用到
  • 怎么MySql添加全文索引
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysql索引提高查询速度常用方法代码示例mysql索引失效的几种情况汇总详解mysql 聚簇索引与非聚簇索引mysql索引性能优化问题解决方案导致mysql索引失效的一些常见写法总结mysql索引的各种类型mysql性能优化之如何高效正确的使用索引mysql索引的基本语法mysql之组合索引方法详解mysql read_buffer_size 设置多少合适mysql中无group by情况下直接使用having语句的问题探究mysql中text与blob字段类型的区别mysql 5.6 从陌生到熟练之_数据库备份恢复的实现方法mysql中decimal类型用法的简单介绍mysql指定日期区间的提取方法mysql innodb表空间卸载、迁移、装载的使用方法sysbench对mysql压力测试的详细教程解析mysql备份与恢复简单总结与tee命令的使用介绍带你5分钟读懂mysql字符集设置mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql日期数据类型、时间类型使用mysql——修改root密码的4种方法mysql之timestamp(时间戳)用法mysql update语句的用法详解mysql覆盖索引详解mysql中null对索引的影响深入讲解mysql常见的错误提示问题处理小结mysql数据库锁的产生原因及解决办法深入浅析mysql columns分区mysql主键相关的sql语句集锦什么是分表和分区 mysql数据库分区和分表php定时备份mysql与mysqldump语法参数详解mysqli预处理编译的深入理解mysql(myisam)的读写互斥锁问题的解决方法
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved