MySQL的索引

一、索引

索引index是帮助MySQL高效获取数据的数据结构，索引本质上就是已经排好序的可以快速查找的数据结构。

在数据之外，数据库系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引一般以索引文件的形式存储在磁盘上。索引一般指的都是B树(多路搜索树)结构组织的索引，其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引。除了B+树这种类型的索引还有哈希索引。

二、索引的优势和劣势

优势：

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

劣势：

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也占用空间
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行插入、更新、删除。因为更新表的同时，不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的信息。

三、索引的分类

• 单值索引：一个索引只包含一个列，一个表可以有多个单列索引。
• 唯一索引：索引列的值必须唯一，但可以有空值。
• 复合索引：一个索引包含多个列

四、基本语法

创建

create [unique] index indexName on tableName(columnName(length));

alter tableName add [unique] index [indexName] on (columnName(length));

删除

drop index [indexName] on tableName;

查看

show index from tableName;

添加

//添加一个主键，这意味着索引值必须是唯一的，且不能为null
alter table tableName add primary key(columnName);
//创建唯一索引，创建索引的值必须是唯一的，null除外，null可能出现多次
alter table tableName add unique indexName(columnName);
//添加普通索引，索引值可出现多次
alter table tableName add index indexName(columnName);
//添加fulltext类型的索引，用于全文索引
alter table tableName add fulltext indexName(columnName);

五、索引的结构

BTree索引

检索原理

真实的数据存在于叶子结点，即最后一行3、5、9、10······

非叶子结点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

Hash索引

full-text全文索引

R-Tree索引

六、适合建立索引的情况

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该建立索引
• 查询中与其他表关联的字段，外键关系应该建立索引
• 频繁更新的字段不适合建立索引，因为每次更新不只更新了记录还会更新索引
• where条件里面用不到的字段不应该创建索引
• 在高并发的条件下 更适合创建复合索引
• 查询中排序的字段，排序字段如果通过索引去访问将大大提高排序速度
• 查询中统计或者分组的字段

七、不适合建立索引的情况

• 表记录太少
• 经常增删改的表
• 数据重复且分布平均的表字段，因为应该只为最经常查询和最经常排序的数据列建立索引

八、性能分析

MySQL Query Optimizer

MySQL中专门负责优化select语句的优化器模块，主要功能是通过计算分析系统中收集到的统计信息，为客户端query的请求提供他认为最优的执行计划。

当客户端向MySQL请求一条query，命令解析器模块完成请求的分类，区别出是select语句并转发给MySQL Query Optimizer时，它会首先对整条query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对query中的查询条件进行简化和转换，如去掉一些无用或者是显而易见的条件、结构调整等。然后分析query中的Hint信息(如果有)，看显示的Hint信息是否可以完全确定该query的执行计划，如果没有Hint或者Hint中的信息还不足以完全确定query的执行计划，则会读取所涉及到的对象的统计信息，根据query进行相应的计算分析，然后再得出最后的执行计划。

常见瓶颈

• CPU饱和一般发生在数据装入内存或者从磁盘中读取数据的时候
• 磁盘IO瓶颈发生在装入内存的数据远大于内存容量的时候
• 服务器硬件的瓶颈，使用top、free、iostat、vmstat命令可以查看系统的性能状态