三，Mysql高频考点 索引| 小册免费学

山花
262 阅读5分钟

面试官总是再问什么是索引

提到索引总会迅速的想到B+树索引有关的八股文，但却对索引本身没有概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引就相当于目录。 为了方便查找书中的内容，通过对内容建立索引形成目录。而数据库索引是一个文件，无论是维护他所产生的时间消耗，还是他本身的空间消耗，都是他为了更高的效率所产生的小代价

MySQL有哪几种索引类型

  • b树
  • r树（地理空间用）
  • hash
  • 全文索引（只有MyISAM有）

为什么B+树一枝独秀，为什么不用b树，不用红黑树，不用hash

  • 为什么不用红黑树

红黑树必须都存在内存中，而且二叉的话树过深，频繁IO读写

  • 为什么不是B，B+比B高度低

B+树的层级更少；B+树查询速度更稳定[都得走到叶子节点]，具备排序功能，全节点遍历快

由于只有叶子节点有数据，所以非叶子节点小，所以一次IO读更多节点。

  • 为什么不用hash（hash函数加数组加链表）

hash做不到范围查询，B+树可以分批查找，不用都装入内存,B+树得到的结果是有序的。

hash建立索引需要以全部列作为索引，因此很难支持部分索引的查找

聚簇索引与非聚簇索引

  • 数据和索引在一起：聚簇
  • 数据的主键和索引在一起：非聚簇

我们自己建的索引基本都是非聚簇索引

联合索引的顺序

我们平常排序的时候，最简单的比如说数字

肯定是先排百位数，再排十位数，再排个位数（听起来像个桶排序）

所以当我们使用联合索引的时候（MySQL可以使用多个字段同时建立一个索引，叫做联合索引），在联合索引中，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

因此(a,b,c)的索引不但能用于自身，还可以用于(a,b),(a),(a,c)[没错可以用于ac]

当然不能用于（b,a） (b,c)

一般情况下，将查询需求频繁或者字段选择性高的列放在前面。

桥豆麻袋!(a,b,c)的索引为什么可以ac

建立索引

image.png

查询ac

SELECT * FROM user_address WHERE province = '广东' 
AND district = '南雄市'

image.png

结论：abc联合索引，ac中的c不能命中这三个字段的联合索引，a可以命中，所以possible_keys列会显示使用了联合索引

再次基础上理解最左匹配

  • mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配
  • =和in可以乱序

比如a=3 and b=4 and c>5 and d=6如果建立（a，b，c，d）顺序的索引，d是用不到索引的，如果建立（a，b，d，c）的索引则都可以用到，a，b，d的顺序可以任意调整。

怎么查看MySQL语句有没有用到索引

image.png image.png

type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等。如const(主键索引或者唯一二级索引进行等值匹配的情况下),ref(普通的⼆级索引列与常量进⾏等值匹配),index(扫描全表索引的覆盖索引) 。

通常来说, 不同的 type 类型的性能关系如下: ALL < index < range ~ index_merge < ref < eq_ref < const < system

创建索引的方式

  • 在执行CREATE TABLE时创建索引
CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);
  • 使用ALTER TABLE命令去增加索引。
ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

  • 使用CREATE INDEX命令创建。
CREATE INDEX index_name ON table_name (column_list);

创建索引的原则

  • 表的某个字段值得离散度越高，该字段越适合选作索引的关键字
  • 占用存储空间少的字段更适合选作索引的关键字。
  • 存储空间固定的字段更适合选作索引的关键字
  • Where子句中经常使用的字段应该创建索引
  • 更新频繁的字段不适合创建索引
  • 有NULL值不当索引

文章末尾请带上以下文字及链接：本文正在参与「掘金小册免费学啦！」活动, 点击查看活动详情

avatar
文章
阅读
粉丝