mysql索引知识

      在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

     索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。在Navicat工具中设计表时，可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。

    并非所有的数据库都以相同的方式使用索引。作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。在多数情况下，索引用于数据检索的速度优势大大超过它的不足之处。但是，如果应用程序非常频繁地更新数据或磁盘空间有限，则可能需要限制索引的数量。

   索引分为聚簇索引和非聚簇索引,聚簇索引并不是一种单独的索引类型，而是一种数据存储方式,具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引,是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法,特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。

MySQL目前主要有以下几种索引类型：常规索引 , 主键索引 , 唯一索引 , 全文索引。

一、常规索引
常规索引也叫普通索引,它可以常规地提高查询效率.一张数据表中可以有多个常规索引.常规索引是使用最普遍的索引类型,如果没有明确指明索引的类型,我们所说的索引都是指常规索引.

二、主键索引
主键索引也简称主键,它可以提高查询效率,并提供唯一性约束,一张表中只能有一个主键.被标志为自动增长的字段一定是主键,但主键不一定是自动增长.一般把主键定义在无意义的字段上,知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅索引都引用主索引，过长的主索引会令辅索引变得过大,再如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

三、唯一索引
提供唯一性约束,一张表中可以有多个唯一索引,当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。

主键和唯一索引的区别

    1.主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

    2.主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

    3.唯一性索引列允许空值，而主键列不允许为空值。

    4.主键列在创建时，已经默认为空值 + 唯一索引了。

    5.主键可以被其他表引用为外键，而唯一索引不能。

    6.一个表最多只能创建一个主键，但可以创建多个唯一索引。

    7.主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

    8.在 RBO 模式下(Oracle查询优化器的RBO(基于规则的优化器)和CBO(基于代价的优化器))，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。 

四、全文索引
全文索引可以提高全文搜索的查询效率,一般使用sphinx替代.但sphinx不支持全文检索,Coreseek是支持中文的全文检索引擎,也称作具有中文分词功能的sphinx,实际项目中,我们用到的是Coreseek.

http://www.360doc.com/content/17/1211/13/33260087_712076317.shtml

MySQL目前主要有以下几种索引方法：B-Tree，Hash，R-Tree。

一、B-Tree
B-Tree是最常见的索引类型，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）,B-Tree在MyISAM里的形式和Innodb稍有不同：MyISAM表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址;InnoDB表数据文件本身就是主索引，叶节点data域保存了完整的数据记录

二、Hash索引
1.仅支持"=",“IN"和”<=>"精确查询，不能使用范围查询：
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash
2.不支持排序：
由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
3.在任何时候都不能避免表扫描：
由于Hash索引比较的是进行Hash运算之后的Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果
4.检索效率高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引
5.只有Memory引擎支持显式的Hash索引，但是它的Hash是nonunique的，冲突太多时也会影响查找性能。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引

三、R-Tree索引
R-Tree在MySQL很少使用

索引的优点
1.大大加快数据的检索速度;

 2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

 3.加速表和表之间的连接;

 4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

索引的缺点
1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。在创建索引之前，您必须确定要使用哪些列以及要创建的索引类型。

一些优化原则

1、缺省情况下建立的索引是非聚簇索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：
a.有大量重复值、且经常有范围查询（ > ,< ，> =,< =）和order by、group by发生的列，可考虑建立群集索引(SQL Server)；
b.经常同时存取多列，且每列都含有重复值可考虑建立组合索引(https://www.cnblogs.com/wxgblogs/p/5743895.html)
c.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。

2、ORDER BY和GROPU BY使用ORDER BY和GROUP BY短语，任何一种索引都有助于SELECT的性能提高。

3、多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。

4、任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

5、IN、OR子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。
Sql的优化原则2：
1、只要能满足你的需求，应尽可能使用更小的数据类型：例如使用MEDIUMINT代替INT
2、尽量把所有的列设置为NOT NULL，如果你要保存NULL，手动去设置它，而不是把它设为默认值。
3、尽量少用VARCHAR、TEXT、BLOB类型
4、如果你的数据只有你所知的少量的几个。最好使用ENUM类型