Mysql-09-索引

1. 页面管理

• CPU位数准确地说应该是CPU一次能够并行处理的数据宽度，一般就是指数据总线宽度
• 页面的大小是4K

https://www.cnblogs.com/lfs2640666960/p/8550452.html

1.1 mysql索引和页的关系

• B-tree，B是balance，一般用于数据库的索引。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。而B+tree是B-tree的一个变种，MySQL就普遍使用B+tree实现其索引结构。　　

• 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

• 为了达到这个目的，磁盘按需读取，要求每次都会预读的长度一般为页的整数倍。而且数据库系统将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。并把B-tree中的m值设的非常大，就会让树的高度降低，有利于一次完全载入。

2. B+树索引分类

[参考博客][https://blog.csdn.net/kennyrose/article/details/7532032?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.edu_weight&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.edu_weight]

[面试题合集][https://blog.csdn.net/ThinkWon/article/details/104778621]

• 首先要搞明白的是聚簇索引和非聚簇索引底层实现都是B+树，所以都叫做B+树索引

• B+Tree结构都可以用在MyISAM和InnoDB上。

2.1 非聚簇索引

• MyISAM 的是非聚簇索引，B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址

2.2 聚簇索引

• InnoDB的数据文件本身就是索引文件，B+Tree的叶子节点上的data就是数据本身，key为主键，这是聚簇索引。聚簇索引，叶子节点上的data是主键(所以聚簇索引的key，不能过长)。

3. 另外一种分类

1. 索引的作用

• 提高查询的速度
• 确保数据的唯一性
• 可以加速表和表之间的连接，实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时，可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化

分类：

• 主键索引（Primary Key）
• 唯一索引（Unique）
• 常规索引（Index）
• 全文索引（FullText）

3.1 主键索引

作用：避免同一个表中某数据列中的值重复

特点：

• 最常见的索引类型

• 确保数据记录的唯一性

• 确定特定数据记录在数据库中的位置

3.2 唯一索引

作用：避免同一个表中某数据列中的值重复

与主键索引的区别：

• 主键索引只能有一个
• 唯一索引可能有多个

3.3 常规索引

作用：快速定位特定数据

注意：

• index 和 key关键字都可以设置为常规索引
• 应加载查询找条件的字段
• 不宜添加太多常规索引，影像数据的插入，删除和修改操作

3.4 全文索引

作用：快速定位特定数据

注意：

• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

4. 创建和删除索引

/*
#方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                );


#方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;


#方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                             
                             
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;


#显示索引信息: SHOW INDEX FROM student;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`); 

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

/*使用全文索引*/
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

5. 索引的原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不建议加索引
• 索引一般应该加载查找条件的字段上

打赏