和大家分享一下MYSQL索引,写了一上午,还有好多要写,特别是优化这块,以后有时间再写吧。希望能对大家理解索引有帮助。

piaoling  2012-11-19 12:00:16
和大家分享一下MYSQL索引,写了一上午,还有好多要写,特别是优化这块,以后有时间再写吧。希望能对大家理解索引有帮助。
一.什么是索引
索引是用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。
二.索引的类型 普通索引 :
这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:
创建索引,例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...],INDEX [索引的名字] (列的列表) );
唯一性索引:
这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种方式创建:
创建索引,例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...],UNIQUE [索引的名字] (列的列表) );
主键 :
主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定,例如“CREATETABLE tablename ( [...],PRIMARY KEY (列的列表) ); ”。但是,我们也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
全文索引:
MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。但效果一般。
三、单列索引与多列索引
单列字段索引,单条件查询的命中率比较高,但在实际应用中不常使用。
多列字段索引,组合条件查询,即where语句中所有条件字段的组合。在实际应用中经常使用,但不是所有where条件都放在索引就是完美,需按实际情况定义索引。
四、最左前缀
多列索引还有另外一个优点,它通过称为最左前缀(Leftmost Prefixing)的概念体现出来。比如现在我们有一个firstname、lastname、age列上的多列索引,我们称这个索引为fname_lname_age。当搜索条件是(firstname,lastname,age)、(firstname,lastname)以及(firstname)的组合时,MySQL将使用fname_lname_age索引。
五、选择索引列
在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:在WHERE子句中出现的列,在join子句中出现的列。
六、选择索引的数据类型
MySQL支持很多数据类型,选择合适的数据类型存储数据对性能有很大的影响。通常来说,可以遵循以下一些指导原则:
(1)越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。
(2)简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。
(3)尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
(4)选择标识符
选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型,而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型,应该保证所有相关的表都使用相同的数据类型。
  整型:通常是作为标识符的最好选择,因为可以更快的处理,而且可以设置为AUTO_INCREMENT。
  字符串:尽量避免使用字符串作为标识符,它们消耗更好的空间,处理起来也较慢。而且,通常来说,字符串都是随机的,所以它们在索引中的位置也是随机的,这会导致页面分裂、随机访问磁盘,聚簇索引分裂
七、MySQL中索引使用相关的限制:
1. MyISAM 存储引擎索引键长度总和不能超过1000 字节;
2. BLOB 和TEXT 类型的列只能创建前缀索引;
3. MySQL 目前不支持函数索引;
4. MySQL只有对以下操作符才使用索引:
  <,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE(是指以通配符(%)结尾的LIKE可以使用索引)。
  以下操作符不能使用索引:
  以通配符(%或者_)开头的LIKE将无法使用索引;
  不等于(!= 或者<>)的时候MySQL 无法使用索引;
5. 过滤字段使用了函数运算后(如abs(column)),MySQL 无法使用索引;
6. Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引;
7. 使用LIKE 操作的时候如果条件以通配符开始  
MySQL 无法使用索引;
8. 使用非等值查询的时候MySQL 无法使用Hash 索引;


八、松散索引和紧凑索引

松散索引就是当MySQL 完全利用索引扫描来实现GROUP BY 的时候,并不需要扫描所有满足条件的索引键即可完成操作得出结果。

要利用到松散索引扫描实现GROUP BY,需要至少满足以下几个条件:
◆ GROUP BY 条件字段必须在同一个索引中最前面的连续位置(组合索引);
◆ 在使用GROUP BY 的同时,只能使用MAX 和MIN 这两个聚合函数;
◆ 如果引用到了该索引中GROUP BY 条件之外的字段条件的时候,必须以常量形式存在;

效率:
因为在没有WHERE 子句,也就是必须经过全索引扫描的时候, 松散索引扫描需要读取的键值数量与分组的组数量一样多,也就是说比实际存在的键值数目要少很多。
而在WHERE 子句包含范围判断式或者等值表达式的时候, 松散索引扫描查找满足范围条件的每个组的第1 个关键字,并且再次读取尽可能最少数量的关键字。

 
紧凑索引就是当扫描实现GROUP BY 和松散索引扫描的区别主要在于他需要在扫描索引的时候,读取所有满足条件的索引键,然后再根据读取的数据来完成GROUP BY 操作得到相应结果。
效率:低于松散索引

By Eno 
类别 :  默认(739)  |  浏览(5117)  |  评论(0)
发表评论(评论将通过邮件发给作者):

Email: