MySQL索引

索引是什么?

MySQL官方对索引的定义: 索引Index是帮助MySQL高效获取数据的一种数据结构.本质上索引是一种数据结构

目的在于提高查询效率,好比字典目录,没有索引需要进行全表扫描,在数据量大的场景下会导致查询效率非常慢. 索引是一个排好序的快速查询的数据结构

MySQL默认的索引B+树,MySQL还有hash索引 全文索引等,大多数情况下我们用的最多的就是B+树索引

索引的优劣势

优势

• 提高数据检索效率,降低数据库IO成本
• 通过索引对数据进行排序,降低数据排序成本,降低CPU的消耗

劣势

• 索引列本身也需要占用空间
• 虽然索引加快了查询速度,但是同时会降低更新表的速度. 对于 INSERT UPDATE DELETE语句,更新表数据不仅需要修改数据文件,还同时需要修改索引库. 可能还需要调整索引B+树的结构,对树进行旋转等操作达到B+树的规范.
• 在创建表的时需要考虑索引的合理规划和优化查询

索引分类和创建语句

• 单列索引: 一个索引只包含单个列,一个表可以创建多个单列索引
• 唯一索引: 索引列的值必须是唯一的,但是允许有空值
• 复合索引: 一个索引包含多个列

创建语法
CREATE [UNIQUE] INDEX index_name ON table_name (column_name(length))

ALTER table_name ADD [UNIQUE] INDEX index_name ON (column_name(length))

删除语法

DROP INDEX index_name ON table_name

查看语法

SHOW INDEX FROM table_name

ALTER命令方式

ALTER TABLE table_name ADD PRIMARY KEY (column_list) 添加一个主键索引,索引值必须是唯一的且不能为空 这里要跟唯一索引区别开! 唯一索引是可以为空的

ALTER TABLE table_name ADD UNIQUE index_name (column_list) 添加一个唯一索引(NULL值可以出现多次)

ALTER TABLE table_name ADD INDEX index_name (column_list) 添加普通索引 索引值可以出现多次

ALTER TABLE table_name ADD FULLTEXT index_name (column_list) 添加一个全文索引

索引结构和检索原理

MySQL索引结构

• BTree 索引
• hash索引
• fulltext全文索引
• R-Tree索引

B+树在MySQL的应用

MySQL B+树的特点

• 真实的数据只存在叶子节点 图上图的第三层的节点
• 非叶子节点不存储真实的数据,只存储索引方法的数据项 比如第二层的都不存储数据
• 叶子节点都存在一个指向下一个叶子节点的指针

真实的情况下,3层B+树可以存储上千万数据,查询只需要进行3次IO操作,性能提升巨大.如果没有索引需要每个数据进行全表扫描是非常耗时的.

适合建索引的情况

1. 主键自动建立唯一索引 (即使我们没有建立主键索引,MySQL底层也会隐式会我们创建一个主键索引)
2. 频繁作为查询条件的字段应该建索引
3. 查询中与其他表关联的字段 外键关系建索引
4. 单列索引和复合索引的选择 在高并发的情况下倾向建复合索引
5. 查询中的排序字段 建索引
6. 查询中的统计 或者分组 字段 建索引

不适合建索引的情况

1. 频繁更新的字段不适合建索引,因为每个更新不单是要更新记录还要维护索引树调整树的结构
2. 不作为where条件查询的字段不建索引
3. 表记录太少不建索引
4. 经常增删改的表不适合建索引
5. 数据重复且分布均匀的表字段不适合建索引. 比如 性别 男/女 成功与否 1/0