MySQL数据库基础（二）索引部分

上篇文章提到索引、主键、外键、事务、存储引擎等等。
本篇来谈谈MySQL索引

索引：可以看成一种为了提高查询效率的数据结构，基于字段建立

索引分类

• 普通索引：普通字段建立的索引
• 主键索引：以主键字段建立索引
• 唯一索引：以唯一键字段建立索引（索引值必须唯一，不允许有空值）
• 全文索引：仅限于MyISAM存储引擎，从char、varchar列中，（对于大容量的数据表，生成全文索引很小号硬盘空间）
• 组合索引：以两个字段建立组合索引，组合索引有一个最左前缀原则。
  最左前缀原则：从最左边开始组合的查询就可以用到组合索引
  建立一张学生信息表，里面有学号，性别，年龄，入学时间等等，以学号和入学时间建立组合索引，以学号和入学时间查询时，可以用到该索引，以入学时间查询时，则用不到，以学号和年龄查询时，又可以用到该索引。

InnoDB索引和MyISAM索引建立规则

MyISAM存储引擎，数据和索引分离（非聚集索引）（将数据地址存放到叶子结点）.frm .myd .myi三个文件分别存放创建表的信息，数据，索引

1. 主索引：主键约束唯一，以主键建立的索引为主索引，也可以以其他字段建立主索引，主索引不唯一。
2. 辅助索引：索引值允许重复

InnoDB存储引擎，索引当成数据的一部分，数据和索引结合（聚集索引）建表时没有给出索引，系统会创建索引

系统创建索引规则：
有主键时，按照主键建立索引，该索引为主索引
没有主键但有唯一键，建立唯一索引，以该索引为主索引
没有主键也没有唯一键，则系统自己添加6字节字段（autoincreament类型，自增长类型），并以该字段建立索引
以其他字段（允许重复）建立的索引称之为辅助索引，该索引存储的并非数据，而是存储主索引的索引值。以辅助索引查询时，查询到主索引的索引的索引值，再以主索引查询数据。

索引优化

适合建立索引的情况

• 1、查询多
• 2、主键、外键
• 3、用于连接两个表的字段
• 4、经常需要排序的字段
• 5、经常用于范围查询的列

不适合建立索引的情况

• 1、查询少 （基本不以该字段查询）
• 2、数值少 （比如性别字段）
• 3、数据量大 （比如text类型，导致结构庞大，效率低）
• 4、修改操作大于查询操作的字段

索引的注意事项

• 索引不能包含含空值的列，只要列中包含有空值，那么这一列对于此复合索引就是无效的

• 对串列进行索引，如果可能指定一个前缀长度，那么就不要对整个列进行索引。（短索引可以提高查询速度，节省磁盘空间）

• 数据库默认排序可以符合要求时，不要使用排序操作。

• like语句，一般不推荐使用like操作，like"%aaa%"不会使用索引，like"aaa%"可以使用索引。

• 不要在列上运算，select * from users where
  YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。