mysql索引原理及使用

现在没有牛仔了
148 阅读4分钟

索引

  • 使用场景
    • 查询频率高，数据量大的表
    • where条件中查询频次高的字段
  • 优势
    • 提高检索效率
    • 提高排序效率
  • 劣势
    • 会多占用一些内存空间
    • 对表的更新操作，也需要在索引中进行相关的更新，所以降低了更新表的效率

索引数据结构

mysql的索引使用B+树结构，这是一种由B树发展来的数据结构，并且mysql在B+树的基础上又做了一些改进提高查询效率。

为什么采用树结构

如果一张表没有建立索引，那么如果要查询表中最后一行的某个字段，则需要从第一行一直寻找到最后一行，效率很低。而当对这个字段建立索引后就可以通过树的查找算法很快的找到指定数据。

mysql中B+树的介绍

首先了解一下二叉查找树

image.png 根节点为10，当添加一个节点时，将比他小的节点放到他的左边，比他大的节点放到他的右边；如果他已经存在子节点，则继续将添加节点与他的子节点比较，并按相同规则排列位置。

在这个基础上，又有了B树结构
B树可以是一个多叉树，假如将树杈的数量定义为m，那么每个节点则最多存储m-1个元素并最多包含m个指针分别指向各自的子节点。向B树中添加一个数据的时候，会按照跟二叉查找树相同的算法找到相应的节点，如果这个节点已经包含m-1个元素，那么将当前元素添加到节点后会把节点元素的中间数据向上分裂，分裂到父节点上。相比二叉查找树，B树的优点是一个节点存放多个数据，减少了查询数据的步长，提高查询效率。

而在B树的基础上，又有了B+树
B+树节点中的元素数量与树杈的数量相等，而B树为m-1。对于B+树的非叶子节点来说，他们的作用仅仅是存储叶子节点数据的索引，起到了索引的作用，叶子节点包含了所有的信息。由于查寻任何元素都要从根节点索引到叶子节点，所以查任何数据的效率都差不多，所以B+树查询更稳定。

Mysql对B+树的优化
在B+树的基础上，每个叶子节点都会有指向相邻节点的指针，实现了所有叶子节点数据的顺序访问，并提高了访问范围数据的性能。

使用索引

索引分类
  • 普通索引
  • 唯一索引：索引列数据不能重复，但是允许多个null值
  • 复合索引：一个索引对应多个列
添加一张表
CREATE TABLE details(
	d_id INT(11) NOT NULL AUTO_INCREMENT,
	d_name VARCHAR(50) NOT NULL,
	d_message VARCHAR(50) NOT NULL,
	PRIMARY KEY (d_id)
) CHARSET=utf8;

INSERT INTO details VALUES(1,'测试','今天你过得好吗？');

查询这张表的索引，会发现已经存在一个索引字段，因为将一个字段设为主键时就会把他设置为索引

image-1.png

手动创建索引的语法如下，创建一个普通索引

CREATE INDEX idx_d_name ON details(d_name);

查询details表的索引

image-2.png

如果想要删除索引，语法如下

DROP INDEX idx_d_name ON details;

创建唯一索引，语法如下

ALTER TABLE details ADD UNIQUE idx_d_name(d_name);

创建完毕

image-3.png

创建复合索引

ALTER TABLE details ADD FULLTEXT idx_d_full(d_name,d_message);
复合索引匹配原则
  • 最左匹配原则：最左侧优先匹配，从最左侧开始的任何连续的索引都能匹配
  • 没有匹配最左列是，不会使用索引而是全表扫描
  • 使用like模糊查询时，前缀表达式会走索引，其他都是全表扫描

关于索引的使用建议

  • 尽量使用唯一索引，唯一索引的查询效率最高
  • 尽量使用短索引提高IO效率
  • 有两种创建索引的方式，但是实际区别不大
    • create index
    • alter
avatar
文章
阅读
粉丝