MySQL的索引及其结构

MySQL的索引

索引是对数据库表中一个或多个列的值进行排序的结构&＃xff0c;建立索引有助于快速获取信息。

在数据库中&＃xff0c;索引也允许数据库程序迅速地找到表中的数据&＃xff0c;而不必扫描整个数据库。

InnoDB支持的索引&＃xff1a;主键索引(PRIMARY)&＃xff0c;普通索引(INDEX)&＃xff0c;唯一索引(UNIQUE)&＃xff0c;组合索引&＃xff0c;总体划分为两类&＃xff0c;主键索引也被称为聚簇索引&＃xff08;clustered index&＃xff09;&＃xff0c;其余都称呼为非主键索引也被称为二级索引&＃xff08;secondary index&＃xff09;。

主键索引&＃xff08;PRIMARY&＃xff09;

数据列不允许重复&＃xff0c;不允许为NULL&＃xff0c;一个表只能有一个主键。

唯一索引&＃xff08;UNIQUE&＃xff09;

数据列不允许重复&＃xff0c;允许为NULL值&＃xff0c;一个表允许多个列创建唯一索引。

确保每一行数据的一致性。

可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索

引

普通索引&＃xff08;INDEX&＃xff09;

可以通过 ALTER TABLE table_name ADD INDEX index_name (column); 创建普通索引

可以通过 ALTER TABLE table_name ADD INDEX index_name(column1, column2,

column3); 创建组合索引

全文索引&＃xff08;FULLTEXT&＃xff09;

可以通过 ALTER TABLE table_name ADD FULLTEXT (column); 创建全文索引

索引并非是越多越好&＃xff0c;创建索引也需要耗费资源&＃xff0c;一是增加了数据库的存储空间&＃xff0c;二是在插入和删除时要花费较多的时间维护索引

覆盖索引

覆盖索引简单来说就是只查询索引就能获取到数据不必再回表查询&＃xff0c;即要查询的列已经被索引列覆盖。避免了回表的产生减少了树的搜索次数&＃xff0c;从而提升性能。

那回表是什么&＃xff1a;在流程中从非主键索引树搜索回到主键索引树搜索的过程

借鉴https://juejin.cn/post/6844903967365791752

索引结构

https://www.cnblogs.com/rickiyang/p/13559507.html

如上文所述&＃xff0c;InnoDB 引擎是面向行存储的&＃xff0c;数据都是存储在磁盘的数据页中&＃xff0c;数据页里面按照固定的行格式存储着每一行数据。InnoDB存储引擎是 B&＃43; 树索引组织的&＃xff0c;所以数据即索引&＃xff0c;索引即数据。B&＃43; 树的叶子节点存储的都是数据段的数据。&＃61;&＃61;InnoDB 引擎对数据的存储必须依赖于主键&＃xff0c;主键对应的索引叫做聚集索引。&＃61;&＃61;如果建表没有建主键&＃xff0c;InnoDB 会从表字段中寻找第一个非空的唯一索引作为聚集索引&＃xff0c;如果还是找不到&＃xff0c;InnoDB 会生成一个不可见的名为 ROW_ID 的列&＃xff0c;该列是一个 6 字节的自增数字&＃xff0c;用来创建聚集索引。

MyISAM 也是使用 B&＃43; 树作为索引存储结构&＃xff0c;他的叶子节点 data 域存放的是数据的物理地址&＃xff0c;即索引结构和真正的数据结构其实是分开存储的。

InnoDB 中采用主键索引的方式&＃xff0c;所有的数据都保存在主键索索引中。

主键索引存储了整行数据&＃xff0c;而非主键索引中存储的值为主键id

执行sql语句&＃xff1a;

SELECT age FROM student WHERE name &＃61; &＃39;小李&＃39;&＃xff1b;


流程为&＃xff1a;

1. 在name索引树上找到名称为小李的节点 id为03
2. 从id索引树上找到id为03的节点 获取所有数据
3. 从数据中获取字段命为age的值返回 12

什么情况索引失效

条件查询、隐式查询

1.如果条件中有or&＃xff0c;即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意&＃xff1a;要想使用or&＃xff0c;又想让索引生效&＃xff0c;只能将or条件中的每个列都加上索引
2.对于复合索引&＃xff0c;如果不使用前列&＃xff0c;后续列也将无法使用&＃xff0c;类电话簿。
3.like查询是以%开头
4.存在索引列的数据类型隐形转换&＃xff0c;则用不上索引&＃xff0c;比如列类型是字符串&＃xff0c;那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.where 子句里对索引列上有数学运算&＃xff0c;用不上索引
6.where 子句里对有索引列使用函数&＃xff0c;用不上索引
7.如果mysql估计使用全表扫描要比使用索引快,则不使用索引&＃xff08;比如数据量极少的表&＃xff09;

like索引失效

• %放在右边&＃xff0c;前缀&＃xff0c;代表查询以"a"开头的数据&＃xff0c;如&＃xff1a;abc
• 两个%%&＃xff0c;中缀&＃xff0c;代表查询数据中包含"a"的数据&＃xff0c;如&＃xff1a;cab、cba、abc
• %放在左边&＃xff0c;后缀&＃xff0c;代表查询以"a"为结尾的数据&＃xff0c;如cba

一、%号放右边&＃xff08;前缀&＃xff09;

由于B&＃43;树的索引顺序&＃xff0c;是按照首字母的大小进行排序&＃xff0c;前缀匹配又是匹配首字母。所以可以在B&＃43;树上进行有序的查找&＃xff0c;查找首字母符合要求的数据。所以有些时候可以用到索引。

二、%号放左边

是匹配字符串尾部的数据&＃xff0c;我们上面说了排序规则&＃xff0c;尾部的字母是没有顺序的&＃xff0c;所以不能按照索引顺序查询&＃xff0c;就用不到索引。

三、两个%%号

这个是查询任意位置的字母满足条件即可&＃xff0c;只有首字母是进行索引排序的&＃xff0c;其他位置的字母都是相对无序的&＃xff0c;所以查找任意位置的字母是用不上索引的。