数据库索引

索引介绍及其工作机制

就以最简单的Student表为例吧,如果现在需要查询 name = ‘张三’ 这个人的信息, 此时数据库会一条数据一条数据的去查询名字叫张三这个人的信息。此刻其实就是全表查询了嘛。假如现在Student表数据量很大，那么其实就gg了。所以索引就运用而生了。
啰嗦半天,索引是怎么样的一个工作机制,这个神奇的东东是怎样提高性能的呢？
不使用索引:
数据库基本存储结构是页,每页数据的记录是一个单向链表。各页之间又是一个双向链表。如果是全文查找的话,首先要遍历双向链表， 找到所在页，然后在所在页去遍历单向链表,去查找页内的数据。
使用索引:
现在通过”目录”能很快对应到对应的页。

索引:

索引是存储在表中某一列上的一种数据结构,该索引列上面的所有值就存储在这个数据结构上。那么哪种数据结构可以作为索引呢？如图所示:

细心的同学可能发现, 在创建索引不光要选择哪种数据结构作为索引, 还有一个索引类型。 这个索引类型包括以下几种:

  - FULLTEXT(搜索引擎使用)

在检索长文本的时候,例如一篇很长的文章,效果最好。
PS:
1.尽量先创建出表来以后再创建全文索引, 这样效率比在创建表时就直接创建全文索引效率高。
—> 这个原因待考究
2.如果查询字符串长度过短其实查询效果只会适得其反,效果反而不如普通索引。数据量越大, 全文索引效果好, 比较小的数据会返回一些难以理解的结果。(默认最小长度是4个字符) —> 可以sql确认
3.mysql 为例: 全文引擎只用于数据库引擎是MYISAM的数据表, 其他引擎,全文引擎不会生效。此外，MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。
🤔️数据库引擎又是什么东东? 详情请见补充1.

  - NORMAL

普通的索引, 大多数情况都可以使用。

  - UNIQUE

要求唯一不允许重复。primary key(主键) 其实就是 unique + not null

  - SPATIAL 用处待考究？？

数据库索引数据结构,B-tree和哈希表。
B-tree 是最常用的数据结构, 因为他的时间复杂度低,删除插入操作都可以在对数时间内完成。另外一个重要原因是存储在B-tree中的数据是有序的。
Hash表, 将索引上的健值换算成hash值, 索引时候不需要从根结点逐级查找, 一次定位。效率远高于b-tree。但是存在很多弊端：
1.仅仅满足等值查询, 不可以范围查询,并且无法排序。
2.对于多列的组合索引，不支持最左匹配规则。 对于组合键，hash值是组合起来计算的。所以通过其中一个或几个索引去查询无法利用索引。
3.对于不同的键值会存在相同的hash值，那么无法在hash表中的定位到数据，那么可能就得去全表扫描。对于大量hash值重复的情况，那么索引效率极低。(Hash碰撞)

索引使用

最左前缀原则(搭配复合索引使用,很重要) 详见补充2

-- 普通索引
alter table user add index idx_name(`name`); 
-- 唯一索引
alter table user add unique idx_unique_name(`name`);
-- 主键索引
alter table user add primary key idx_primary_id(`id`);
-- 全文索引
alter table user add fulltext idx_full_id(`id`);
-- 全文索引使用
select * from user where match(`id`) against('84ff')

-- 删除索引
ALTER TABLE `test`.`user` DROP PRIMARY KEY,DROP INDEX `idx_name`,DROP INDEX `idx_unique_name`;
drop index `idx_id_name` on user;

-- 组合索引
alter table user add index idx_id_account__name(`id`,`account`,`name`);
-- 建立这样一个组合索引,相当于建立(id), (id,account),(id,account,name) 三个索引 最左前缀 从最左边开始组合

使用索引优化sql需要注意的地方

1.索引不包含有null的列

某一列中包含了null值, 那么这些数据就都不会包含在索引中。 复合索引只要有一列是null值, 那么这一列对于复合索引是无效的。

2.使用短索引

对于串列进行索引, 如果可能通过指定一个前缀长度,(有一个长度是255的列, 如果对于10个或者20个长度,可以确定内容是唯一的) 那么就不需要去对整个列进行索引。
alter table user add primary key idx_primary_id(id(8));

上述是使用短索引,击中结果得到的结果.

alter table user add primary key idx_primary_id(id);

上述是整列作为索引, 击中索引后得到结果。

3.like语句的操作

对于使用’%xxx%’是不会击中索引的, 但是’xxx%’是会击中索引的。
使用’%xxx%’情况:

使用’xxx%’情况:

4.不要在列上进行计算

select from table1 where year(date) < 2019 这样会导致索引失效,然后全表扫描。
select from tables where date < ‘2019-01-01’

5.索引的列排序 ？？ 没明白🤔️

6.mysql只会对以下操作符生效

(<,<=,=,>=,>between,in) 以及like(‘aaa%’)
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写；
不等空值还有OR，索引影响要注意；
VAR引号不可丢， SQL优化有诀窍。
*

Explain简单介绍:

select_type:每个select子句的类型
table: 是那张表或者哪个子查询的。
type: 常用类型有:ALL, index,range,ref,eq_ref,const,system,NULL(性能依次变好)
possible_keys: 可能会用到的索引。但不一定使用
key: 实际使用到的索引.
key_len: 索引使用的字节数, 长度越短越好。
ref: 那些列或者常量被用于查询索引列上的值
rows: 估计找到所需记录需要读取的行数。

补充1: 数据库引擎:

先甩一段生硬的介绍: 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。mysql的核心就是存储引擎。

InnerDB存储引擎(聚集索引)

1.支持事务。提供了具有提交，回滚(其实就是保证了事务的ACDI四个特性)，奔溃恢复能力的事务安全
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
2.实现了SQL标准的四种隔离级别

3.支持行锁定和外键.
如果一个事物对表中某行执行了锁定操作，而另一个事务也需要对同样的行执行锁定操作，这样第二个事务的锁定操作有可能被阻塞，一旦被阻塞第二个事务只能等到第一个事务执行完毕（提交或回滚）或超时。
4.不支持全文索引,没有保存数据库行数, count(*) 需要全表扫描
5.支持自增的列属性 auto_increment
innerDB引擎的索引实现:
InnerDB 引擎完全按照B+tree的模式来的, 子节点保存的是索引和数据。

使用InnerDB注意的点:
1.innerDB表推荐使用整型当主键。首先uuid更消耗存储空间, 其次在叶节点比较时候 整型数据运算速度更快。第三, 在插入数据时候, 整型自增主键会在叶子节点末尾添加数据,不会破坏左侧子树结构。uuid是随机生成的,在插入的时候, 有可能会重构树结构,消耗时间。第四, 自增整型在磁盘上是连续存储的,uuid是分散的, 不适合执行where id > 5等这些条件查询语句。

MyISAM存储引擎(非聚集索引)

1.快速读取 适合插入和更新较少，查询比较频繁的
2.保存了数据库行数，执行count时，不需要扫描全表
3.不支持数据库事务
4.不支持行级锁和外键
5.不支持故障恢复
6.支持全文检索FullText，压缩索引

MyISAM引擎索引实现:
MyISAM引擎是索引文件和数据文件分开的。MyISAM引擎也是采用的B+tree实现的。但是叶子节点存储的是索引和文件指针。如图所示:

补充2: 最左前缀原则

1.对于联合索引, 索引会一直向右进行匹配, 遇到查询范围(>,<,BETWEEN AND)就停止匹配。
2.对于创建联合索引 union_index(a,b,c) 其实创建了 (a),(a,b),(a,b,c)三个索引。
3.复合索引字段不一定完全按照顺序来,mysql优化器帮我们做优化, 可以识别正确形式。

alter table user add index idx_id_account__name(id,account,name);

explain select * from user where name = ‘zs’ -> 不会击中索引。

explain select * from user where id = ‘35390359-f20b-44ae-ad1f-40e5dc1797c2’

explain select from user where id = ‘35390359-f20b-44ae-ad1f-40e5dc1797c2’ and account = ‘15735101234’ -> 击中索引
explain select from user where id = ‘35390359-f20b-44ae-ad1f-40e5dc1797c2’ and account = ‘15735101234’ and name = ‘zs’
explain select * from user where account = ‘15735101234’ and name = ‘zs’ and id = ‘35390359-f20b-44ae-ad1f-40e5dc1797c2’

上述结果说明复合索引并不是匹配越多, 效果越好,其实如果单个字段可以精确查询 那么最好还是用单个字段去查询。

补充三: SQL的执行顺序