Mysql索引常见问题汇总

Q1:数据库有哪些索引？优缺点是什么？

1.B树索引：大多数数据库采用的索引（innoDB采用的是b+树）。能够加快访问数据的速度，尤其是范围数据的查找非常快。缺点是只能从索引的最左列开始查找，也不能跳过索引中的列，如果查询中有某个列用到了范围查询，则右边所有列都无法使用索引优化查找。

2.哈希索引：基于哈希表实现。在MySQL中，只有Memory引擎显式的支持哈希搜索。哈希查找的速度非常快，但哈希索引只包含哈希值和行指针，不存储字段值，所以不能用索引中的值来避免读取行，也不能进行排序。由于哈希索引使用的是索引列的全部内容来计算哈希值的，所以不支持部分所有列匹配查找。哈希只支持等值比较，不支持任何范围查询。一旦哈希冲突很多的话，维护成本非常高。innoDB支持“自适应哈希索引”（adaptive hash index)。

3.全文索引：全文索引是一种特殊类型的索引，它查找的是文本中的关键字，而不是比较索引的值。最初只能在MyISAM上使用，5.6.24以后innoDB也支持了全文索引。全文索引的查询要使用Match....against，在相同的列上同时创建全文搜索和基于值的B-Tree索引不会有冲突。

4.空间数据索引（R-tree索引），MyISAM支持R树索引，好处是无需前缀查询，会从所有纬度来索引数据，可以用作地理数据的存储；缺点是必须使用MySQL的GIS相关函数如MBRCONTAINS( )等来维护数据，但由于MySQL中的GIS并不完善，因此大多数人不会使用这个特性。

Q2：为什么不实用二叉查找树或者红黑树作为数据库索引。

二叉树在处理海量数据时，树的高度太高，虽然索引效率很高，达到logN，但会进行大量磁盘io，得不偿失。而且删除或者插入数据可能导致数据结构改变变成链表，需要增进平衡算法。而红黑树，插入删除元素的时候会进行频繁的变色和旋转（左旋，右旋），很浪费时间。但是当数据量很小的时候，完全可以放入红黑树中，此时红黑树的时间复杂性比b树低。因此，综上考虑，数据库最后选择了b树作为索引。

Q3:B tree和B+ tree应用场景：

1.B树常用于文件系统，和少部分数据库索引，比如mongoDB。

2.B+树主要用于mysql数据库索引。

Q4:B+ tree对比B tree的优点

B树的每个节点除了存储指向 子节点的索引外，还要存储data域，因此单一节点指向子节点的索引并不是很多，树的高度较高，磁盘io次数较多。B+树的高度更低，且所有data都存储在叶子节点，叶子节点都处于同一层，因此查询性能稳定，便于范围查找。

Q5:多列排序时使用索引的坑

A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

根据Mysql文档的说明，创建索引的时候，可以加上asc或者desc，例如：add index idx(a asc,b desc).但是实际Mysql是会忽略的（好坑。。。）好像8.0版本之后支持desc了。

这会有什么影响呢？

假如有列test1和test2，都是int类型。

我们创建索引``idx1(test1,test2)，
假如我们要按test1和test2排序，例如SQL

explain select * from table order by test1 ,test2 limit 1;

可以使用索引的排序：

  • order by test1
  • order by test1 desc
  • order by test1,test2
  • order by test1 desc,test2 desc

不可以使用索引的排序：

  • order by test1,test2,desc
  • order by test1 desc,test2

因为索引不支持desc，所以多列的索引是按全部列的升序存储的。所以只排序一列，全部列升序，全部列降序，都能用索引。但是第一列用升序，第二列用降序，或者第一列降序，第二列用升级，都不能使用索引。

以上就是Mysql索引常见问题汇总的详细内容，更多关于MySQL 索引的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • mysql索引必须了解的几个重要问题

    本文讲述了mysql索引必须了解的几个重要问题.分享给大家供大家参考,具体如下: 1.索引是做什么的? 索引用于快速找出在某个列中有一特定值的行.不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行. 表越大,花费的时间越多.如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据. 大多数MySQL索引(PRIMARY KEY.UNIQUE.INDEX和FULLTEXT)在B树中存储.只是空间列类型的索引使用R-树,并且MEMORY

  • 解决MySQL中IN子查询会导致无法使用索引问题

    解决MySQL中IN子查询会导致无法使用索引问题

    今天看到一篇关于MySQL的IN子查询优化的案例, 一开始感觉有点半信半疑(如果是换做在SQL Server中,这种情况是绝对不可能的,后面会做一个简单的测试.) 随后动手按照他说的做了一个表来测试验证,发现MySQL的IN子查询做的不好,确实会导致无法使用索引的情况(IN子查询无法使用所以,场景是MySQL,截止的版本是5.7.18) MySQL的测试环境 测试表如下 create table test_table2 ( id int auto_increment primary key, p

  • 分析MySQL中索引引引发的CPU负载飙升的问题

    收到一个mysql服务器负载告警,上去一看,load average都飙到280多了,用top一看,CPU跑到了336%,不过IO和内存的负载并不高,根据经验,应该又是一起索引引起的惨案了. 看下processlist以及slow query情况,发现有一个SQL经常出现,执行计划中的扫描记录数看着还可以,单次执行耗时为0.07s,还不算太大.乍一看,可能不是它引发的,但出现频率实在太高,而且执行计划看起来也不够完美: mysql> explain SELECT count(1) FROM a

  • 分析Mysql表读写、索引等操作的sql语句效率优化问题

    上次我们说到mysql的一些sql查询方面的优化,包括查看explain执行计划,分析索引等等.今天我们分享一些 分析mysql表读写.索引等等操作的sql语句. 闲话不多说,直接上代码: 反映表的读写压力 SELECT file_name AS file, count_read, sum_number_of_bytes_read AS total_read, count_write, sum_number_of_bytes_write AS total_written, (sum_number

  • Mysql索引性能优化问题解决方案

    mysql 创建的优化就是加索引,可是有时候会遇到加索引都没法达到想要的效果的情况, 加上了所以,却还是搜索的全数据,原因是sql EXPLAIN SELECT cs.sid, -- c.courseFrontTitle, -- c.imgBig, cs.studyStatus, coi.fee, -- act.PROC_INST_ID_ AS processId, cs.createDTM, cs.payStatus, cs.isCompleted, cs.saleChannel, cs.is

  • MySQL批量插入和唯一索引问题的解决方法

    MySQL批量插入和唯一索引问题的解决方法

    MySQL批量插入问题 在开发项目时,因为有一些旧系统的基础数据需要提前导入,所以我在导入时做了批量导入操作 ,但是因为MySQL中的一次可接受的SQL语句大小受限制所以我每次批量虽然只有500条,但依然无法插入,这个时候代码报错如下: nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576). You can change this va

  • mysql中索引与FROM_UNIXTIME的问题

    零.背景 这周四收到很多告警,找DBA看了看,发现有个慢查询. 简单收集一些信息后,发现这个慢查询问题隐藏的很深,问了好多人包括DBA都不知道原因. 一.问题 有一个DB, 有一个字段, 定义如下. MySQL [d_union_stat]> desc t_local_cache_log_meta; +----------------+--------------+------+-----+---------------------+ | Field | Type | Null | Key |

  • php mysql索引问题

    显然这是一个凡是使用MySQL的朋友都会遇到的问题. 回忆一下当初在配置环境时提到的MySQL GUI工具,对了,就是它,大家可以到http://www.mysql.com中找到它.因为sunec也只是一名程序爱好者,远没有达到程序员水平,所以借助一些工具的帮助还是很有必要的~相信一些朋友也和我一样~ 用GUI工具创建表,建立REF字段,把REF设置为主键,定义类型为int,并在AUTO INC选项上打勾.好了,在MySQL端的工作就结束了. 接下去就交给PHP了,用之前专题中介绍过的inser

  • Mysql索引常见问题汇总

    Q1:数据库有哪些索引?优缺点是什么? 1.B树索引:大多数数据库采用的索引(innoDB采用的是b+树).能够加快访问数据的速度,尤其是范围数据的查找非常快.缺点是只能从索引的最左列开始查找,也不能跳过索引中的列,如果查询中有某个列用到了范围查询,则右边所有列都无法使用索引优化查找. 2.哈希索引:基于哈希表实现.在MySQL中,只有Memory引擎显式的支持哈希搜索.哈希查找的速度非常快,但哈希索引只包含哈希值和行指针,不存储字段值,所以不能用索引中的值来避免读取行,也不能进行排序.由于哈希

  • MySQL 索引知识汇总

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度. 打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车. 拿汉语字典的目录页(索引)打比方,我们可以按拼音.笔画.偏旁部首等排序的目录(索引)快速查找到需要的字. 索引分单列索引和组合索引.单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引.组合索引,即一个索引包含多个列. 创建索引时,你需要确保该索引是应用在

  • Mysql 5.7.14 使用常见问题汇总(推荐)

    Mysql 5.7.14 使用常见问题汇总(推荐)

    常见问题汇总: 一. 当我们用navicate premiun 连接远程数据库时,若出现如下问题: 笔者结合自己的实践,总结有如下原因: 1.远程数据库服务没有打开连接端口 2.本机IP地址没有得到授权 3.本地网络故障 笔者的情况属于第二种情况,当更换IP访问时,就可以连接成功. 以上所述是小编给大家介绍的Mysql 5.7.14使用常见问题汇总,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的.在此也非常感谢大家对我们网站的支持!

  • MySQL索引失效的几种情况汇总

    一.索引不存储null值 更准确的说,单列索引不存储null值,复合索引不存储全为null的值.索引不能存储Null,所以对这列采用is null条件时,因为索引上根本 没Null值,不能利用到索引,只能全表扫描. 为什么索引列不能存Null值? 将索引列值进行建树,其中必然涉及到诸多的比较操作.Null值的特殊性就在于参与的运算大多取值为null. 这样的话,null值实际上是不能参与进建索引的过程.也就是说,null值不会像其他取值一样出现在索引树的叶子节点上. 二.不适合键值较少的列(重复

  • Mysql事务索引知识汇总

    目录 一.事务 1.事务特性 2.事务并发时出现的问题 二.索引 1.索引使用 2.索引类型 3.索引注意原则 三.SQL 1.SQL优点 2.SQL分类 3.SQL语法及关键字 四.数据库设计 1.数据库三大范式 2.数据库规范设计 一.事务 mysql事务是用于处理操作量大.复杂性高的数据 1. 事务特性 原子性:保证每个事务所有操作要么全部完成或全部不完成,不可能停滞在中间环节:如事务在执行过程中出现错误,则会回滚到一致性:事务开始结束后保证数据库的完整性没有被破坏,数据都是按照数据库要求

  • MySQL索引用法实例分析

    本文实例分析了MySQL索引用法.分享给大家供大家参考,具体如下: MYSQL描述: 一个文章库,里面有两个表:category和article.category里面有10条分类数据.article里面有20万条.article里面有一个"article_category"字段是与category里的"category_id"字段相对应的.article表里面已经把 article_category字义为了索引.数据库大小为1.3G. 问题描述: 执行一个很普通的查

  • mysql索引基数概念与用法示例

    本文实例讲述了mysql索引基数概念与用法.分享给大家供大家参考,具体如下: Cardinality(索引基数)是mysql索引很重要的一个概念 索引基数是数据列所包含的不同值的数量.例如,某个数据列包含值1.2.3.4.5.1,那么它的基数就是5.索引的基数相对于数据表行数较高(也就是说,列中包含很多不同的值,重复的值很少)的时候,它的工作效果最好.如果某数据列含有很多不同的年龄,索引会很快地分辨数据行.如果某个数据列用于记录性别(只有"M"和"F"两种值),那么

  • mysql索引覆盖实例分析

    本文实例讲述了mysql索引覆盖.分享给大家供大家参考,具体如下: 索引覆盖 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.这种查询速度非常快,称为"索引覆盖". 假设有一张t15表,在表中建立了一个联合索引:cp(cat_id,price) 当我们使用下面的sql语句,会出现索引覆盖的情况.不信我们可以来查看一下,这里的Extra中显示了Using index,表示这条sql语句刚好用到了索引覆盖. select price from t1

  • mysql索引对排序的影响实例分析

    本文实例讲述了mysql索引对排序的影响.分享给大家供大家参考,具体如下: 索引不仅能提高查询速度,还可以添加排序速度,如果order by 后面的语句用到了索引,那么将会提高排序的速度. 测试 1.创建测试表:t15表 CREATE TABLE `t15` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `cat_id` int(10) unsigned NOT NULL DEFAULT '0', `price` decimal(10,2) N

  • MySql 索引、锁、事务知识点小结

    MySql 索引、锁、事务知识点小结

    本文总结了MySql 索引.锁.事务知识点.分享给大家供大家参考,具体如下: 1. 索引 索引,类似书籍的目录,可以根据目录的某个页码立即找到对应的记录. 索引的优点: 天生排序. 快速查找. 索引的缺点: 占用空间. 降低更新表的速度. 注意点:小表使用全表扫描更快,中大表才使用索引.超级大表索引基本无效. 索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引. 详细说说 6 种索引

随机推荐