嘀嘀嘀~~~  页面这在飞快的跑来 . . .

MySQL索引刨析


MySQL索引

MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。创建索引时,需要确保该索引是应用在SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

索引的重要性

从MySQL数据库中查找书籍,就类似于从图书馆找书,如果书比较少,那么查找的速度会比较快,如果书比较多的时候,那么查找的速度就会非常的慢。这个时候图书的分类管理就显得非常重要了,图书分类管理就类似于索引(或者理解成给所有的图书设定目录)。

普通索引

最基本的索引,它没有任何限制。

创建

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

删除

DROP INDEX [indexName] ON mytable;

唯一索引

索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

创建

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构(添加索引)

ALTER table mytable ADD UNIQUE [indexName] (username(length))

索引的本质

在没有索引的情况下,为什么查询的速度会比较慢呢?

因为数据都是存储在磁盘上的,那么再执行sql语句的时候,一定会去磁盘上读取相关的数据,那就会产生磁盘IO。如果没有索引,那么会进行多次磁盘的IO操作,比较耗费时间。

为什么要使用索引?

其目的就是为了减少磁盘的IO操作,这样就可以提高查询数据的速度。

索引本质是什么?

索引是帮助快速检索出数据的数据结构。
索引的作用是快速检索出数据。
索引的本质就是一种数据结构。也就是说你创建了一个索引,其实MySQL的数据库会帮你创建一个数据结构,然后使用该数据结构来存储数据,该数据结构就是索引。

索引底层数据结构

二叉树

二叉树是一颗相对平衡的有序二叉树,对其进行插入,查找,删除等操作性能都比较好。

特点:它的左子节点的值比父节点的值要小,右节点的值要比父节点的大。

二叉树的优点:可以优化磁盘IO的次数。节点存在有顺序,可以进行范围的查询。

二叉树的缺点:插入数据的速度会比较慢,因为会更改数据结构。不平衡的问题,会产生倾斜的二叉树。

完全平衡二叉树

插入数据会平衡,但是插入的时候会改变树的结构。插入的数据的时候比较慢。而且树的层级会变高,会增加磁盘IO的次数。

二叉树是有顺序的,范围查找都是支持的。

B树

B树或者B+树的节点可以存储多个数据,所以相对于完全平衡二叉树的高度肯定会低,那么就会降低磁盘IO的次数。

B+树

B+树相对于B树有数据的冗余,叶子节点中的数据是有顺序的。那么再进行顺序查找的时候就非常的方便,只要在叶子节点顺序向后遍历即可。

MySQL索引是如何存储数据的

MySQL中是如何使用B+树的

MySQL中Innodb和myisam存储引擎默认都是使用B+树数据结构做索引的,存储的数据结构如下

为什么只有叶子节点会存储数据,而非叶子节点不存储数据呢?

局部性原理:当一个数据被用到时,其附近的数据也可能被使用,所以操作系统为了提高效率,读取数据的时候往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,操作系统也会从这个位置开始,顺序向后读取一定长度的数据放入内存中,这里的长度叫做页。也就是计算机操作系统操作磁盘的基本单位,一般操作系统中一页的大小为 4kb。

在MySQL中可以使用如下的命令查看Innodb引擎页的默认大小

SHOW GLOBAL STATUS LIKE ‘Innodb_page_size’

MySQL的页的大小默认是16kb,B+数的设计非常适合读取数据。

如果节点又存储数据和索引,数据比较多,索引就比较少,那么树的高度就越高。导致磁盘IO变多,效率变低。

叶子节点可以存储多少数据

一个节点能存储16KB的数据,这是MySQL默认的节点大小,一页16KB。
假设一行记录的大小为1kb(其实已经比较大了)
那么一个叶子节点就能存储16条数据。
非叶子节点里面存储的是索引的值和指针,MySQL默认的索引值大小是8B,指针大小是6B,合在一起是14B。那么非叶子节点可以存储的索引+指针的个数为:
161024/14 = 1170 个
那么如果数的高度是2层,叶子节点的个数也就是 1170个,那么可以存储的数据条数为:1170 16 = 18720条。
如果树的高度是3层,那每个叶子节点可以存储的条数为:11701170
16= 21902400条

myisam引擎存储结构

可以使用命令创建一张新的表结构,使用myisam引擎来创建。myisam引擎的索引采用的非聚集索引,索引和表数据是分开存储的。

user2.frm文件是创建表的文件
user2.MYD文件是表的数据文件
user2.MYI文件是索引文件

myisam引擎的索引结构图

索引文件

数据文件

myisam引擎通过索引查找数据的时候,通过索引值找到对应的地址,通过地址找到数据。如果已name字段设置索引,效果是一样的。

Innodb引擎存储结构

数据文件user.frmuser.ibd

user.frm是创建表的文件

user.ibd是数据+索引文件

索引Innodb引擎是聚集索引,索引和数据文件在一起的。

数据文件

如果已name字段设置索引,那就是二级索引(辅助索引)。叶子节点中存储的该列的数据和主键值,也就意味着还需要再通过主键去查找一次数据。

索引文件

补充

数据库中最常见的慢查询优化方式是什么?

加索引

为什么加索引能优化慢查询?

因为索引是一种优化查询的数据结构,比如MySQL中的索引是用B+树实现的。而B+树就是一种数据结构,可以利用索引快速查找数据。所以能优化查询。

哪些数据结构可以提高查询速度?

哈希表 完全平衡二叉树 B树 B+树等

InnoDB聚集索引和普通索引有什么差异?

InnoDB聚集索引的叶子节点存储行记录,因此, InnoDB必须要有,且只有一个聚集索引:

  1. 如果表定义了PK,则PK就是聚集索引;
  2. 如果表没有定义PK,则第一个not NULL unique列是聚集索引;
  3. 否则,InnoDB会创建一个隐藏的row-id作为聚集索引;

为什么有的字段建立索引,查询速度反而变慢了呢?

列的散列度越低,不推荐建立索引。

散列度公式:count(distinct(column_name)) / count(*)

现在有一列字段,假如列的名称是 sex性别,存储的值是或者,表中有500w条数据,现在执行sql:select * from t_user where sex = '男' 查询花费的时间2秒多,现在对sex 列建立了索引,再执行之前的sql语句,但是花费的时间变成了 20多秒。

在什么字段上创建索引?

where、join、 order by

索引个数不要过度,散列度低的字段,不要建立索引,随机无序或频繁更新的值,不适合作为主键,创建符合索引时避免冗余索引。

联合索引,最左匹配原则

必须从联合索引的第一个字段开始,不能跳过,不能中断。

两个字段一般是同时出现的,推荐建立联合索引,例如高考成绩查询:身份证号+考号

CREATE INDEX index_name_sex on t_user (name,sex)

EXPLAIN SELECT * from t_user where name = 'name4999008' and sex = '男'

EXPLAIN SELECT * from t_user where sex = '男' and name = 'name4999008'

EXPLAIN SELECT * from t_user where name = 'name4999008' 

EXPLAIN SELECT * from t_user where sex = '男'

第四条不会应用上该索引,其余三条都可以(第二条MySQL优化器)。


文章作者: HaiLin Zhou
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 HaiLin Zhou !
评论
  目录