文章目录
6 MySQL中的索引
6.1 索引
6.1.1 定义
- 索引是为了提高数据查找速度的一种数据结构，提高查找速度的主要手段是减少数据查找时的磁盘IO次数。其本质是排好序的快速查找数据结构
- 简单理解：如图左边是一个数据表，若要查找时复杂度为O(n)，若我们对其排序，得到右边的搜索二叉树，那么时间复杂度就降低为了log(n)，达到了提高搜索效率的目的，其中搜索二叉树就是一种数据结构。
搜索二叉树：左子树永远比右子树小
6.1.2 优点：
- 减少磁盘IO提高搜索次数
- 通过创建唯一索引，可以确保数据的唯一性
6.1.3 缺点
- 创建和维护索引需要时间，且随着数据量的增加，索引的维护时间也会增加。
- 索引会占据磁盘空间
- 索引减低了表的更新效率，因此数据一旦修改，则索引需要动态进行修改
6.2 B+tree(数据页)
- MySQL中储存引擎采用的是innodb，其内部索引结果为B+tree
- B+树由可以简单看成由数据页和目录页组成
数据页（叶子节点）：每个叶子节点都是数据页，其储存着每条数据记录和其关键字（关键字即该记录的唯一识别，通常为主键），数据页中记录的储存结构为单项链表的形式；数据页之间的储存结构为双向链表的形式。64kb每页，64 * 16 = 1024kb = 1Mb目录页（非叶子节点）：每个数据页有自己的地址，目录页中的每条数据记录着数据页的位置及该数据页的最小关键字，目录页中的储存结构同样为单项链表，目录页之间为双向链表。
- 总结：
- 在B+tree中，记录储存在叶子节点中，每个叶子节点中存在有多条记录，每个叶子节点叫为数据页。非叶子节点称为目录页，记录着下一层节点的位置即关键字，目录页可以有多层。
- 本质为：树+单向链表+双向链表的索引数据结构，每个节点内都是单项链表，同一层节点之间为双向链表。
- 数据储存在叶子节点
- 优点：
- 查找的IO次数较少，效率高
- 查找稳定，每次查找都回从根节点到叶子节点
- 每个节点的记录都是根据关键字排好序的，方便进行二叉查找。
- 缺点：
- 建树速度完全受限于关键字的顺序，当插入的记录没有按关键字排序时，速度很慢，因此在实际中需要保证关键字自增。
- 修改数据时，速度较慢，因为需要修改的数据可能会导致，多个节点进行修改，且叶子节点中的位置交换。
- B+tree一般不超过4层，为什么？
- 从磁盘io角度：B+tree的查找io次数与其层数相等，当层数太多时，查找效率低。
- 从数据量角度：假设每个数据页可以储存记录数为n；因为目录页只记录位置和关键字，每条记录的量远比数据页少的多，目录页可以储存的记录数一定大于n，假设为n+m；因此当有两层时，可储存的数据量为：n(n+m)，三层为n(n+m)(n+m)以此类推，当到达四层时，数据量以及及其庞大了，够用。
- B+树比B-树效率要高，更适合文件管理系统，为什么？
- B-tree：也为B树，是一个多路平衡搜索树，其每一个节点都储存着数据与关键字，由根节点开始，数据不断分开到孩子节点中去，树很深。查找时越靠近根节点查找越快。
- B-tree与B+tree的区别主要在于B-tree的树层数太多，导查找时io次数过多，效率低。
- B+tree的查找稳定，每次查找都需从根节点找到叶子节点，页B-tree不是。
6.3 索引基本类别
-
根据B+tree叶子节点储存的数据可以将索引结构分为聚簇索引、二级索引或辅助索引、联合索引。
在B+tree的建立中，需要以某个字段为关键字进行，假设为c1，查询时，也需要c1字段来进行查找，当需要按其他字段进行查找时，假设为c2，则按照c1建立的c1tree就会失效，因此我们希望以c2为关键字建立一颗B+tree进行查找；但是这样就出现另一个问题，若按照c2再建立一颗tree的，就得储存两份数据了，因此为了既能利用B+tree快速查找，又能不储存两份数据，我们采取如下策略：在根据c2字段建立的B+tree中，叶子节点不再储存全部字段，而只储存c2字段和c1字段，这就是所谓的二级索引。当然有时候还需要同时根据c2，c3字段进行查找，这样我们上述建立的c1tree和c2tree都会失效，这个时候我们同样根据c2，c3的值建立新的tree，叶子节点只储存c2、c3、c1字段，这就是联合索引。
-
聚簇索引：
叶子节点中储存着全部字段的数据，称为聚簇索引，通常以主键为关键字进行建立，这也说明了一个表只能有一个聚簇索引。
-
二级索引：
叶子节点只储存两个字段的数据，其中一个字段为主键，另一个字段为建立B+tree的关键字，一个表中可以存在有多个二级索引。
-
联合索引：
叶子节点储存多个字段的数据，最后一个为主键，一个表可以存在有多个联合索引
-
查询过程：
可以看出若根据主键进行查找时，可以直接搜索聚簇索引B+tree既可找到，只需遍历一次B+树。当根据非主键进行查找时，需遍历二次B+树，第一次即遍历该字段的二级索引B+tree，找到主键后，再遍历聚集索引B+tree，找到需要的记录这就是回表。
-
例子：假设c1为主键 ，c2~c10为非主键
select * from t1; # 通过聚簇索引获得 select * from t1 where c2=..; # 通过二级索引和聚簇索引获得 select * from t1 where c2=.. and c2 =..; # 通过联合索引和聚簇索引获得
6.4 innodb中B+tree的注意事项
-
根节点万年不变
根节点万年不变主要体现在建树过程中：当开始建树时，只有一个数据页，其即为根节点也为叶子节点，假设该节点为A，当数据逐渐变多超出数据页时，下一个节点会怎么生成呢？这就会出现“根节点万年不变性了”，即A仍为叶子节点，然后复制节点A节点作为B，并创建节点C，将A节点中的数据代替为BC的位置即对应关键字。
可见A节点从数据页节点变为了目录页节点，树的建立是从根开始的，一旦根节点满了，就会复制根节点作为其子节点，并用子节点的信息代替原数据。
-
内节点关键字项唯一
主要体现在非聚簇索引中，二级索引是根据非主键字段进行建立的，因此会出现有关键字相同的情况，此时可以通过为其添加主键的形式来实现唯一性。
-
一个节点至少有两条数据
6.5 创建索引
- 索引的创建方式有两种，随表的创建而创建，或单独创建。
- 上述讲到索引分为聚簇索引和非聚簇索引，其是根据索引储存情况来分类的，若根据创建索引的列情况进行分类，则可以分为主键索引、单值索引（普通索引）、唯一索引、复合索引、全文索引
基本语法：
-
随表一起创建
CREATE TABLE customer ( id INT(10) UNSIGNED AUTO_INCREMENT , customer_no VARCHAR(200), customer_name VARCHAR(200), # 以下为索引 PRIMARY KEY(id), # 主键索引 KEY (customer_name), # 普通索引 UNIQUE (customer_name), # 唯一性索引（唯一索引时允许空值的） KEY (customer_no,customer_name) # 符合索引 ); -
单独创建：主键索引和非主键索引的创建有所不同
# 主键索引的创建 ALTER TABLE customer drop PRIMARY KEY ; # 需先删除原主键索引 ALTER TABLE customer add PRIMARY KEY customer(customer_no); # 再添加主键索引 # 非主键索引的创建 create [unique] index [key] 索引名 on 表名(列名[,列名]) # 为表customer创建customer_no的唯一性索引 create unique index idx_customer on customer(customer_no) -
删除索引：
drop index [索引名] on 表名; -
查看索引：
show index from 表名;
6.6 索引的创建时机
- 适合创建索引的情况：
- 主键自动创建唯一索引
- 频繁作为查询条件的列
- 不需要经常修改的列
- 按顺序排序的列
- 查询中与其它表关联的字段，外键关系建立索引
- 不适合创建索引的情况
- 表记录太少
- 经常增删改的表或字段
6.7 全文索引
-
全文索引包括主要利用算法为倒排索引，和分词器：
当建立索引时，MySQL会对词语进行分词，具体分词的大小可以利用参数指定，分词结束后进行倒排索引。
-
倒排索引：将主键作为字典，语句作为索引，即将每一个词作为key值，而该词出现的行的集合作为value值，在进行搜索时，会先对搜索句子进行分词，然后定位到全文索引中去，将涉及的行进行返回，这其中涉及了相似度的匹配问题。
# 在MySQL中的/etc/my.cnf进行配置
[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1
# 创建
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
create fulltext index ft_index on 表名(列名) with parse ngram; 创建全文索引，并指定分词器
# 查找
select * from account where match(列名) against("查找的词")
934
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
