mysql索引

MyISAM是MySQL的默认数据库引擎（5.5版之前），由早期的ISAM所改良。

虽然性能极佳，但却有一个缺点：不支持事务处理（transaction）

 

InnoDB的最大特色就是支持了ACID兼容的事务 （Transaction）功能

 

MyISAM与InnoDB区别：

1）事务支持：

MYISAM：强调的是性能，每次查询具有原子性，其执行速度比InnoDB快，但是不支持事务

InnoDB支持事务，外键等高级功能，具有事务回滚和崩溃修复能力。

2）表锁差异

MyISAM只支持表级锁，用户在操作MyISAM表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

InnoDB支持行级锁，提高了多用户并发操作的性能，但是InnoDB的行锁，只是在where的主键是有效的，非主键的where都会锁全表

3）全文索引

MyISAM支持FULLTEXT类型的全文索引

InnoDB不支持FULLTEXT类型的全文索引，单InnoDB可以使用sphinx插件支持全文索引，并且效果更好

4）表的具体行数

MyISAM保存有表的总行数，如果select count(*)from table回直接取出该值

InnoDB：没有保存表的总行数，但是在加了where条件之后，myisam和innodb处理的方式都一样

5）外键

MyISAM不支持，InnoDB支持

 

索引的实现：

在Mysql中索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址

在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，但是辅助索引可以重复

MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录

MyISAM索引与InnoDB索引的区别：

1）InnoDB的数据文件本身就是索引文件，MyISAM索引文件和数据文件是分离的

叶节点包含了完整的数据记录，这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记 录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形

2）InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录

 

了解索引实现区别的好处：

例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都应用主索引，过长的主索引会令辅助索引变得过大。

例如使用非单调的字段作为主键在InnoDB中不是好主意，因为InnoDB数据文件本身就是一个B+Tree，非单调的主键会造成在插入新纪录时数据文件为了维持B+Tree的特性而频繁的分裂调整

 

聚集索引：索引的叶节点就是数据节点

非聚集索引：仍是索引节点，只不过有一个指针指向对应的数据块

普通索引：没有任何限制

唯一索引：索引列的值必须唯一，单允许有空值，如果是组合索引，则列值的组合必须唯一

主键索引：一种特殊的唯一索引，不允许有空值，一般是在建表的时候同时创建主键索引

 

使用技巧：

建立索引的时机：在where和join中出现的列建立索引，但不完全如此，MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引

 

索引的不足：

降低更新表的速度，占用磁盘空间

 

使用索引的注意事项：

1）索引不会包含有null值得列，复合索引中只要有一列含有null值，那么此复合索引是无效的，所以我们在设计数据库时不要让字段的默认值为null

2）使用短索引

3）索引列排序：mysql查询只使用一个索引，如果where字句中已经使用了索引的话，那么order by中的列是不会使用索引的，尽量不要使用多个列的排序，如果需要最好给这些列创建复合索引

4）一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like“aaa%”可以使用索引

5）不要在列上进行运算

6）不使用not in和 <>操作