数据库MySQL之索引

1.索引的定义和优缺点

1.1 索引的定义

• 帮助数据库提高查询效率而且已经排序的数据结构。

1.2 索引的优点

• 保证数据的准确性，唯一的索引值对应着唯一的数据。
• 加快检索速度，提高系统性能。

1.3 索引的缺点

• 索引需要占用数据库资源。
• 索引和数据都需要占用内存空间。如果要建立聚集索引，需要的空间更大。
• 创建和维护索引要耗费时间，降低数据库的速度。

2.索引分类

2.1 主键索引、唯一索引和普通索引

• 主键索引：不允许有空值。
• 唯一索引：索引列的值必须唯一但允许有空值，但只能有一个null。
• 普通索引：最基本的索引，没有任何限制，也称为二级索引、辅助索引或者单值(列)索引。

2.2 全文索引和组合索引

• 全文索引：用于全文搜索。只有InnoDB(mysql 5.7以后)和MyISAM存储引擎支持全文索引和仅适用于char 、var和text。
• 组合索引：即一个索引包含多个列。组合索引满足匹配最左前缀和查询过程中动态调整查询字段顺序以便利用索引。

案例：当索引为组合索引(name,age)时：
select * form tablename where name=? and age=?;
select * form tablename where name=?;
select * form tablename where age=?;
select * form tablename where age=? and name=?;
在上面语句中，第1，2，4语句会走索引。

2.3 普通索引和组合索引区别

实例：现在我们想查出满足以下条件的用户id：select uid from people where iname='Liu' and iname='xiaoxin' and age=26;。因为尽量避免扫描整表，故考虑用索引。

• 普通索引：alter table people add index lname(lname);。将lname列建索引，这样就把范围限制在iname='Liu'的结果集1上，之后扫描结果集1，产生满足iname='Zhiqun'的结果集2，再扫描结果集2，找到age=26的结果集3，即最终结果。由于建立lname列的索引，与执行表的完全扫描相比效率提高很多，但要求扫描的记录数量仍远远超过实际所需要。虽然可以删除lname列上的索引，再创建fname或者age列的索引。但是不论在哪个列上创建索引，搜索效率仍旧相似。
• 组合索引：alter table people add index iname_fname_age (iname,fname,age);。为了提高搜索效率，所以需要考虑运用组合索引。

2.4聚簇索引索引和非聚簇索引

2.4.1 聚簇索引和非聚簇索引的定义

• 聚簇索引：索引和数据一起存储，例如innodb使用的是聚簇索引。由于数据和索引一起存储，同页会有多行数据。当访问同页不同行数据时，已经将页加载到缓存中，再次访问时，会在内存中直接完成访问而不必访问磁盘。如果按照主键id来组织数据，获得数据更快。
• 非聚簇索引：索引和数据分开存储，例如myisum使用的是非聚簇索引索引。非聚簇索引只存储索引值和数据所在的位置，通过索引找到数据所在的位置，然后再根据数据所在位置去获取数据。
• innodb的叶子结点存放的是数据本身，而myisum存放的是数据的地址。

2.4.2 聚簇索引和聚簇索引优缺点比较