Mysql进阶知识-索引

1、索引介绍

1.1、索引的类别

# 数据结构的在第5节讲的索引原理中查看

# 聚簇索引：只有一个，一般来说是主键来当，没有主键就第一个unique来当，叶子节点中存放的即为整张表的行记录数据。

# 非聚簇索引：叶节点的data域存放的是数据记录的地址，对比二级索引就是二级索引是利用主键回表查询的，它是直接存储地址

# 二级索引：叶子节点中存储索引字段的值和主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录。

  组合索引（一种特殊的二级索引）：就是针对多个列组合的索引，前面排序的基础上再排序
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

# 哈希索引：哈希索引是无序索引，因为通过Hash Table实现的，使用index key快速定位的

# 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较（类似百度）。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
eg：CREATE FULLTEXT INDEX index_content ON article(content)

2、索引使用

2.1、建立索引

alter table 表名 add index 索引名(对应列名) [asc|desc]
或者
create index 索引名 on 表名(对应列名) [asc|desc]

对于文本或字符
create fullindex 索引名 on 表名(对应列名(长度)) [asc|desc]

建表的时候建立索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

2.2、删除索引

DROP index 索引名 on 表名

2.3、查看使用索引情况

EXPLAIN SELECT id from test;

id：查询序列号。表示查询中执行 select 子句或操作表的顺序，id 值越大优先级越高，越先被执行。id 相同，执行顺序由上至下。
select_type：查询类型
table：输出行所引用的表
possible_keys：指出 MySQL 能在该表中使用哪些索引有助于查询。如果为空，说明没有可用的索引  
key：MySQL 实际从 possible_key 选择使用的索引。
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了
rows：MYSQL 认为必须检查的用来返回请求数据的行数

3、建立索引的注意事项

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

当然并不是建立了索引就能正常调用索引，首先相关查的东西是建立了索引的，然后还有一些规则，
可以避免索引失效。

全值匹配，对索引列中所有的列都指定具体指。该情况下，索引生效，执行效率高
最左前缀法则

 如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，并且不跳过索引中的列。
 违反最左前缀法则，索引失效，符合最左法则，但是出现跳过一列，只有最左列索引生效。
 范围查询右边的列，不能使用索引，例如a = 1 and b = 2 and c > 3 and d = 4,d的索引会失效，因为索引是在ab的条件下去c>3的顺序，排完之后d的顺序已经无序了。

不要在索引列上进行运算操作，索引将失效
尽量使用覆盖索引，避免 select *
不要使用like "%a" 这样的模糊搜索，会让索引失效

explain SELECT name from country where country.Name like "%a"

4、索引的原理

mysql有三大引擎：

InnoDB（主流）：B+Tree
Memory：哈希索引
MyISAM：非聚簇索引

这里主要介绍InnoDB引擎里的B+Tree结构原理

B+树是B树的一个升级版，相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。为什么说B+树查找的效率要比B树更高、更稳定；我们先看看两者的区别
B+跟B树不同B+树的非叶子节点存储不真实的数据，只存储指引搜索方向的数据项，这样使得B+树每个非叶子节点所能保存的关键字大大增加，整个树的层级降低；
B+树叶子节点保存了父节点的所有关键字记录的指针，所有数据地址必须要到叶子节点才能获取到。所以每次数据查询的次数都一样；
B+树叶子节点的关键字从小到大有序排列，双向链表结构，且左边结尾数据都会保存右边节点开始数据的指针。

特点
1、B+树的层级更少：相较于B树B+每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快；
2、B+树查询速度更稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;
3、B+树支持范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
4、B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

posted @ 2022-07-09 20:03 吴承勇阅读(76) 评论(0) 编辑收藏举报

刷新页面返回顶部