数据库 —— Mysql索引

        索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

        1、越小的数据类型通常更好

        2、简单的数据类型更好

        3、尽量避免NULL

一、优缺点

（一）优点

        查询速度快。

（二）缺点

        增删改慢，因为数据库要同步去维护索引文件，所以速度慢。

二、分类

（一）普通索引

        create index index_name on table(column)；

（二）唯一索引

        类似普通索引，索引列的值必须唯一，可为空

    create unique index index_name on table(column)；或者创建表时指定unique index_name column

（三）主键索引

        特殊的唯一索引，不允许为空，一般是在建表时指定primary key(column)

（四）组合索引

        在多个字段上创建索引，遵循最左前缀原则。alter table t add index index_name(a,b,c);

（五）全文索引

        全文索引，目前MySQL中只有MyISAM存储引擎支持，并且只有CHAR、VARCHAR、TEXT类型支持。它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

（六）覆盖索引

        如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。

三、主键索引和非主键索引的区别

        非主键索引的叶子节点存放的是主键的值，而主键索引的叶子节点存放的是整行数据。

        非主键索引也被称为非聚簇索引，而主键索引也被称为聚簇索引。

（一）聚簇索引 / 主键索引

        优点：数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快,聚簇索引对于主键的排序查找和范围查找速度非常快。

        缺点：插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。

（二）非聚簇索引/ 非主键索引

        非聚簇索引访问数据总是需要二次查找。通过非聚簇索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

 四、聚集索引

        从物理存储角度，分为两种：

        聚集索引：物理存储按照索引排序  

        非聚集索引：物理存储不按照索引排序

        在SQLServer中，主键的创建必须依赖于索引，默认创建的是聚集索引，但主键也可以声明为非聚集索引。

        问题：主键索引是聚集索引还是非聚集索引？

        在Innodb下是聚集索引，在Myisam下是非聚集索引

五、索引何时失效

    （1）组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引；

    （2）like未使用最左前缀，where A like '%China'；

    （3）搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引 ；

    （4）or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）

    （5）如果列类型是字符串，要使用引号。例如where A='China'，否则索引失效（会进行类型转换）；

    （6）使用 关键字 in ，or ，null，!=

六、索引优化

全值匹配我最爱，最左前缀要遵守；

联合索引最左前缀原则（又叫最左侧查询）

• 如果在(a,b,c)三个字段上建立联合索引，那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。

例如登录业务需求，代码如下。

selectuid, login_time from user where login_name=? andpasswd=?

可以建立(login_name, passwd)的联合索引。

因为业务上几乎没有 passwd 的单条件查询需求，而有很多 login_name 的单条件查询需求，所以可以建立(login_name, passwd)的联合索引，而不是(passwd, login_name)。

• 建联合索引的时候，区分度最高的字段在最左边。

• 如果建立了(a,b)联合索引，就不必再单独建立 a 索引。同理，如果建立了(a,b,c)联合索引，就不必再单独建立 a、(a,b) 索引。

• 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如     where a>? and b=?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

• 最左侧查询需求，并不是指 SQL 语句的 where 顺序要和联合索引一致。

下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引。

selectuid, login_time from user where passwd=? andlogin_name=?

索引列上少计算，范围之后全失效；

范围列可以用到索引（联合索引必须是最左前缀）。

• 范围条件有：<、<=、>、>=、between等。

• 范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而 title 和 from_date 则使用不到索引

select * fromemployees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

Like百分写最右，覆盖索引不写星；

例如下面 SQL 语句不能使用索引。

select * fromdoc where title like '%XX'

而非前导模糊查询则可以使用索引，如下面的 SQL 语句。

select * fromdoc where title like 'XX%'

不等空值还有or，索引失效要少用；

负向条件查询不能使用索引，可以优化为 in 查询。

负向条件有：!=、<>、not in、not exists、not like 等。

把计算放到业务层而不是数据库层。

如果有 order by、group by 的场景，请注意利用索引的有序性。

• order     by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现     file_sort 的情况，影响查询性能。

• 例如对于语句 where a=? and b=? order by     c，可以建立联合索引(a,b,c)。

• 如果索引中有范围查找，那么索引有序性无法利用，如 WHERE     a>10 ORDER BY b;，索引(a,b)无法排序。

假如有联合索引(empno、title、fromdate)，下面的 SQL 是否可以用到索引，如果可以的话，会使用几个列？

select * fromemployees.titles where emp_no between '10001' and'10010' and title='Senior Engineer' and from_date between '1986-01-01'and '1986-12-31'

回答：可以使用索引，可以用到索引全部三个列，这个 SQL 看起来是用了两个范围查询，但作用于 empno 上的“between”实际上相当于“in”，也就是说 empno 实际是多值精确匹配，在 MySQL 中要谨慎地区分多值匹配和范围匹配，否则会对 MySQL 的行为产生困惑。

where a= 3

答：是，使用了 a 列。

where a= 3 and b = 5

答：是，使用了 a，b 列。

where a = 3 and c = 4 and b = 5

答：是，使用了 a，b，c 列。

where b= 3

答：否。

where a= 3 and c = 4

答：是，使用了 a 列。

where a = 3 and b > 10 andc = 7

答：是，使用了 a，b 列。

where a = 3 and b like 'xx%' andc = 7

答：是，使用了 a，b 列。