mysql数据库索引学习

最近做的一个项目用的是mysql数据，编写sql 的时候，因为业务复杂，经常有很多表联合查询的sql需要编写，于是我就思考着，如果数据量将来打起来后，这么复杂的sql运行起来会不会慢，后人接手我的项目后，会不会因为性能原因骂我？带着问题，开始学习mysql索引相关的内容起来。学了一段，写篇文章记录一下，做个总结，也给自己留点资料。

• 索引的概念 

       在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

• 索引的作用

    相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

•  索引的类型

    索引的具体创建方法，create index命令就可以操作了，具体因数据库服务器开发厂家不同略有不同。但目前多数开发人员都会使用数据库客户端工具操作数据库以及库表，主键和索引都可以用可视化工具操作，比命令方便多了。我感觉我都忘了命令怎么敲了，还是工具方便，所以下面就不讲索引的创建命令了，就列下具体的索引类型大家了解下：

    1  普通索引

        最基本的索引类型，没有唯一性之类的限制。

    2  唯一索引

        唯一索引是不允许其中任何两行具有相同索引值的索引。

    3  主键索引

        简称为主索引，数据库表中一列或列组合（字段）的值唯一标识表中的每一行。该列称为表的主键。

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键索引。

    4   候选索引

        与主索引一样要求字段值的唯一性，并决定了处理记录的顺序。在数据库和自由表中，可以为每个表建立多个候选索引。

    5  聚集索引

        也称为聚簇索引，在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引， 即如果存在聚集索引，就不能再指定CLUSTERED 关键字。索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引更适用于对很少对基表进行增删改操作的情况。

    6  非聚集索引

        也叫非簇索引，在非聚集索引中，数据库表中记录的物理顺序与索引顺序可以不相同。一个表中只能有一个聚集索引，但表中的每一列都可以有自己的非聚集索引。

   7  全文索引

       从3.23.23版开始支持全文索引和全文检索，FULLTEXT， 可以在char、varchar或text类型的列上创建。 

   8  复合索引   

       比如 创建一个索引 ，该索引由 a,b,c三列值联合起作用。那么当查询条件为a，或者a，b，或者a，b，c时才会使用上这个索引这个规则就最左前缀原则。关于该原则，明天晚上再补上。

•   索引的使用场景

    1  条件查询，比如 select * from table where columnname='' .....

    2  表连接，比如select * from A a left join B b on a.columnname=b.columnname,检查查询的 WHERE 和 JOIN 子句。在任一           子句中包括的每一列都是索引可以选择的对象。

    3  group by语句后的列，或者order by后面的列

    以上几种场景的查询列都可以根据情况加索引。如果表数据量不是很大，就不必加了。索引只有再数据量很大的情况下，才能表现出明显的效率差别。对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

• 什么时候索引会失效

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2. 对于多列索引，不是使用的第一部分，则不会使用索引（即不符合最左前缀原则）
3. like查询是以%开头
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

     此外，查看索引的使用情况

     show status like ‘Handler_read%’;
    大家可以注意：
    handler_read_key:这个值越高越好，越高表示使用索引查询到的次数

    handler_read_rnd_next:这个值越高，说明查询低效

 

• 注意事项

      并非所有的数据库都以相同的方式使用索引。作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。如果应用程序非常频繁地更新数据或磁盘空间有限，则可能需要限制索引的数量。在表较大时再建立索引，表中的数据越多，索引的优越性越明显。

     可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。

     如果经常同时搜索两列或多列或按两列或多列排序时，索引也很有帮助。例如，如果经常在同一查询中为姓和名两列设置判据，那么在这两列上创建多列索引将很有意义。

  确定索引的有效性：

检查查询的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以选择的对象。

对新索引进行试验以检查它对运行查询性能的影响。

考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。

检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。

检查某列中唯一数据值的数量，并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性，这有助于确定该列是否适合建立索引，如果适合，确定索引的类型。

 

今天先弄到这儿。明儿晚有空补上最左前缀原则和b树索引说明。