MySQL ( 目前最流行的关系型数据库管理系统 )
什么是索引?
- MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构,可以帮助我们快速的进行数据的查找
索引是什么样的数据结构?
- 索引的数据结构跟存储引擎有关,在MySql中使用较多的索引有B+数索引和哈希索引,我们常用的MYISAM和InnoDB存储引擎的默认索引实现为:B+树索引
Hash索引和B+树索引有什么区别或者说优劣呢?
-
首先要知道Hash索引和B+树索引的底层实现原理:
hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据;B+树底层实现是多路平衡查找树,对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.
-
有以下的不同:
1、hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询(因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围)
2、hash索引不支持使用索引进行排序,原理同上;
3、hash索引不支持模糊查询以及多列索引的最左前缀匹配,原理也是因为hash函数的不可预测AAAA和AAAAB的索引没有相关性
4、hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询
5、hash索引虽然在等值查询上较快,但是不稳定。性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差。而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低。
因此在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度,而不需要使用hash索引
建立索引的原则有哪些?
- 选择唯一性索引,为经常需要查询、排序、分组和联合操作的字段建立索引,限制索引的数目,最左前缀匹配原则(非常重要),尽量选择区分度高的列作为索引,字段尽力设置不为null,索引列上不计算
主键、外键和唯一索引的区别
-
主键:唯一标识一条记录,不能有重复的,不允许为空
-
外键:表的外键是另一表的主键, 外键可以有重复的, 可以是空值
-
唯一索引:该字段没有重复值,但可以有空值
什么是回表查询和覆盖索引?
-
如果使用普通索引,则查询时间先查找到主键值,之后再通过主键值查找到自己想要的数据;需要扫码两遍索引树;这就是回表查询
-
索引覆盖:只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表
MySQL 支持哪些存储引擎?
MySQL 支持的存储引擎主要有四种
-
InnoDB 支持事务,行级锁定和外键,是事务型数据库的首选引擎;MySQL5.5.5 之后的默认存储引擎
-
MyISAM 拥有较高的插入、查询速度,但不支持事务。MySQL5.5.5 之前的默认存储引擎
-
Memory 基于散列,存储在内存中,对临时表有用。常见的应用场景是:临时存放数据,数据量不大,并且不需要较高的数据安全性
-
Archive 支持高并发的插入操作,但是本身不是事务安全的。常见的应用场景:存储归档数据,如记录日志信息可以使用 Archive
InnoDB 和 MyISAM 有什么区别呢?
-
InnoDB 支持事务;而 MyISAM 不支持事物,强调的是性能,查询速度更快
-
InnoDB 支持行级锁和表级锁(默认行级锁),而 MyISAM 只支持表级锁
-
InnoDB 支持 MVCC, 而 MyISAM 不支持 MVCC
-
InnoDB 支持外键,而 MyISAM 不支持外键
-
InnoDB 早期版本不支持全文索引(从 MySQL5.6 开始支持全文索引),而 MyISAM 支持
-
InnoDB 不保存表的具体行数,count () 时要扫描一遍整个表来计算有多少行;MyISAM 则内置了一个计数器,count () 时它直接从计数器中读
MySQL 索引底层是什么结构?为什么采用此结构?
-
mysql 底层采用 B+ 树的存储结构,采用 B+ 树的原因:
-
索引文件很大,不能全部存储在内存中,只能存储到磁盘上,因此索引的数据结构要尽量减少查找过程中磁盘 I/O 的存取次数(重点)
-
数据库系统利用了磁盘预读原理和磁盘预读,将一个节点的大小设为等于一个页,这样每个节点只需要一次 I/O 就可以完全载入。而 B + 树的高度是 2~4,检索一次最多只需要访问 4 个节点(4 次,即树的高度)( B + 树索引并不能直接找到具体的行,只是找到被查找行所在的页,然后 DB 通过把整页读入内存(磁盘预读),再在内存中查找(局部性原理,即当一个数据被用到时,其附近的数据也通常会马上被使用) )
-
为什么不用 B 树?
- B+ 树所有的 Data 域在叶子节点,其余节点用来索引,而 B 树是每个索引节点都会有 Data 域;并且 B + 树所有叶子节点之间都有一个链指针。 这样遍历叶子节点就能获得全部数据,从而支持区分查询。在数据库中基于范围的查询是非常频繁的,而 B 树不支持这样的遍历操作
为什么不用哈希表?
-
哈希表只适用与查找等值查询, 不能支持区分条件(大于小于查询)、模糊查询等
-
hash 索引虽然在等值查询上较快,但是不稳定,性能不可预测,当某个键值存在大量重复的时候,发生 hash 碰撞,此时效率可能极差,而 B + 树的查询效率比较稳定
为什么不用红黑树?
- 红黑树往往高度过大,从页造成磁盘 IO 读写过于频繁,效率低下。而且逻辑上很近的节点(父子)物理上可能很远,无法利用局部性原理
MySQL 支持的索引类型是哪些?
- 普通索引:用表中的普通列构建的索引,没有任何限制
- 唯一索引:唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一
- 主键索引:是一种特殊的唯一索引,根据主键建立索引,不允许重复,不允许空值
- 全文索引:通过建立倒排索引,快速匹配文档的方式。MySQL 5.7.6 之前仅支持英文,MySQL 5.7.6 之后支持中文
- 组合索引:又叫联合索引。用多个列组合构建的索引,这多个列中的值不允许有空值。可以在创建表的时候指定,也可以修改表结构
聚集索引和非聚集索引?
-
聚集索引 (clustered index),又称为主索引, MySQL里主键就是聚集索引 ,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。因为数据真正的数据只能有一种排序方式,所以一个表上只能有一个聚簇索引
-
非聚集索引 (secondary index),又称为辅助索引、普通索引,该索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表可以包含多个非聚集索引
-
在 InnoDB 中它们还有一个非常重要的区别:聚集索引的叶子节点的的 data 域包含了完整的数据记录,而非聚集索引的叶子节点的 data 域记录着主键的值,因此在使用非聚集索引进行查找时,需要先查找到主键值,然后再到聚集索引中进行查找,这称之为回表查询
索引什么时候会失效?
索引失效通常有以下原因:
-
条件中有 or
-
like 查询且以 % 开头
-
如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
-
对列进行函数运算(如 where md5 (password) = “xxxx”)
-
负向查询条件会导致无法使用索引,比如 NOT IN,NOT LIKE,!= 等
-
对于联合索引,不是使用的第一部分 (第一个),则不会使用索引(最左匹配原则)
-
如果 mysql 评估使用全表扫描要比使用索引快,则不使用索引
索引有什么缺点?
- 索引需要额外的占用物理空间,索引越多,所以空间占用越多
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的写入速度
使用索引查询一定能提高查询的性能吗?为什么
-
通常,通过索引查询数据比全表扫描要快,但是我们也必须注意到它的代价
-
索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
- 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
- 基于非唯一性索引的检索
MySQL 中 Float、Decimal 类型存储金额的区别?
- Float 是非标准数据类型,存储金额时存储的是近似值,存在精度问题;Decimal 是标准数据类型,存储金额时存储的是精确值(以字符串的形式保存数值)
- 扩展: 在 MySQL 中,Float,Double 属于非标准数据类型,在 DB 中保存的是近似值;Decimal、Numeric 属于标准数据类型,在 DB 中以字符串的形式保存数值
Datetime、Timestamp 存储时间的区别?
-
两者的存储方式不一样 :
-
对于TIMESTAMP,它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储。查询时,将其又转化为客户端当前时区进行返回。( 对于跨时区的业务,TIMESTAMP更为合适 )
-
对于DATETIME,不做任何改变,基本上是原样输入和输出
-
-
两者所能存储的时间范围不一样:
- timestamp所能存储的时间范围为:‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’
- datetime所能存储的时间范围为:‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’
文章强烈推荐:
聚集索引与非聚集索引的总结
MySQL索引背后的数据结构及算法原理
MySQL索引原理及慢查询优化(美团)
InnoDB 简介
MySQL优化:如何避免回表查询?什么是索引覆盖?
官方文档解释MySQL最左匹配(最左前缀)原则