MySQL——索引

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

MySQL——索引

2023-01-04 17:12:05 来源：博客园

1、原理

索引其实就像一本书的目录，便于我们快速找到想要看的内容。就像我们的《新华字典》，可以按部首查，可以按拼音查，可以快速查到想要查的字。

(相关资料图)

2、类型

B-Tree

使用B-Tree数据结构来存储数据（实际使用B+Tree更多），所有的值都是按顺序存储的，可以加快查找速度，把查找复杂度从O(n) 变为O(log(N))

另：可以了解下树形数据结构，二叉树、平衡二叉树、B-Tree、B+Tree、红黑树

哈希索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效

空间数据索引
全文索引

3、索引的优点

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变为顺序I/O

4、索引是最好的解决方案吗

只有当索引帮助存储引擎快速找到记录带来的好处大于其带来的额外工作时，索引才是最有效的

对于非常小的表，大部分情况全表扫描更高效

对于中到大型的表，索引非常有效

对于特大型的表，建立索引的代价将随之增长，这个时候可以使用分区技术

5、使用索引注意事项

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。最好不要给数据库留NULL，尽可能的使用NOT NULL填充数据库.
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：
```
select id from t where num=10 or Name = "admin"
```
可以这样查询：
```
select id from t where num = 10union allselect id from t where Name = "admin"
```
in 和 not in 也要慎用，否则会导致全表扫描，如：
```
select id from t where num in(1,2,3)
```
对于连续的数值，能用 between 就不要用 in 了：
```
select id from t where num between 1 and 3
```
很多时候用exists 代替 in是一个好的选择：
```
select num from a where num in(select num from b)
```
用下面的语句替换：
```
select num from a where exists(select 1 from b where num=a.num)
```
like左边不能模糊，下面的查询也将导致全表扫描：
```
select id from t where name like ‘%abc%’
```
若要提高效率，可以考虑全文检索。
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。如：
```
select id from t where num/2 = 100
```
应改为:
```
select id from t where num = 100*2
```
符合索引要复合最左前缀原则，即多个字段组成的索引，如果左边的用不到右边的肯定用不到，其中一个使用了范围后面的也用不到

6、功能分类

主键索引（聚簇索引）

既存储索引又存储实际数据行，所以一个表只能有一个主键索引。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

复合索引（多列索引）

是在多个列上创建的索引，复合索引的使用遵循最左匹配原则，只有索引左边的列匹配到，后面的列才能继续匹配。

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

覆盖索引

通常普通索引叶子节点中存储的是主键的值，这样通过普通索引查到主键后还要再查一次（回表），如果索引包含（覆盖）所需要查询字段的值，就不需要回表了，这种索引我们称为覆盖索引。

不是所有类型的索引都可以成为覆盖索引，覆盖索引必须要存储索引列的值，而哈希索引、空间索引、全文索引都不存储索引列的值，所以mysql只能使用B-Tree索引做覆盖索引。

前缀索引（压缩索引）

只在字段的一部分添加索引，比如 userInfo 表里有一个字段 email，我们可以给email字段前6个字符添加索引：

alter table userInfo add index index2(email(6));

关键词：数据结构全文索引