MySQL数据库引擎和索引

ISAM
执行读取的速度快但是不支持容错也不支持事务处理。所以如果硬盘上崩溃了数据就无法恢复了

MyISAM:
提供了锁表的功能来优化并发读写的情况，提供了索引和字段管理的功能。MyISM强调了快速读取因此受到了WEB开发者的青睐，但是它有一个重要缺陷就是在表损坏后不能进行数据恢复。而且不支持事务。

InnoDB：
不支持全文索引，支持事务和外键，虽然要比MyISAM慢，但是有了事务和外键会方便许多。

InnoDB和MyISAM的区别
InnoDB和MyISAM是最常用的两个表类型，InnoDB支持行锁，MyISAM强调的是性能，而InnoDB提供事务支持和外键等高级数据库的功能。
细节上的区别
1. InooDB支持行级锁但是MyISAM只支持表锁
2. InnoDB不会保存表中的行数所以在进行select count(*) from table的时候就会遍历整个表，而MyISAM是保存了行数的，在进行行数的查询上会很快
3. 对于AUTO_INCREMENT的字段，InnoDB中必须包含只含有该字段的索引，但是在MyISAM中可以和其他列建立联合索引。
4. InnoDB的行级锁不是绝对的，如果在一个SQL语句中不确定要扫描的范围那么InnoDB也是会锁表的。
MyISAM适合:
1. 频繁的查找，少量的更新操作
2. 做许多的count统计
3. 没有事务
InnoDB适合：
1. 可靠性的要求比较高或者要求事务
2. 表更新和查询都相当频繁

创建时候用 type=引擎名称 来指定使用哪个引擎
更改表的引擎使用 alter table 表名 change TYPE=引擎名 例子 alter table user change type=InnoDB;

什么是索引
索引就是对数据列上的值进行结构化排序的一个东西，能优化查找的效率。
聚簇索引：
聚簇索引是索引的顺序结构和物理结构一致的索引，一个表只能有一个聚簇索引。
什么时候适合使用聚簇索引：列经常被分组排序，返回某范围内的数据，小数目的不同值，主键列和外键列。
聚簇索引实际上是在同一个结构中保存了B-Tree索引和数据行，聚簇索引的数据行就保存在它的叶子节点中，因为无法同时把数据行保存在不同的地方，所以一个表只能有一个聚簇索引。
MySQL默认将主键设置为默认索引，因为聚簇索引的性能最好而且具有唯一性，所以要根据这个最常用的查询方式来设置要进行聚簇索引的字段
非聚簇索引：
非聚簇索引的罗辑结构和物理结构没有关联，一个表可以有多个非聚簇索引。
什么时候适合用非聚簇索引：列经常被排序，大数目的不同值，频繁更新的列，外键列与主键列，频繁的修改索引的值。
索引类型：
1. 普通索引：最基本的索引，没有任何的限制
2. 主键索引：特殊的唯一索引，索引列的值要唯一，但是允许有空值。
3. 唯一索引：与普通索引类似但是索引列不允许重复，可以为空
4. 全文索引：仅可以用在MyISAM中
5. 组合索引：为了提高SQL的查询效率可以使用组合索引，遵循最长前缀匹配原则

索引分为单列索引和组合索引：单列索引在一个表中可以存在多个，但是在查询的时候尽管你可能对多个已经进行了单列索引的字段进行条件查找，但是MySQL只能用到它认为最有效率的那种。所以单列索引对于多个列进行条件查询的时候效率不如组合索引高。
组合索引遵从MySQL的最左匹配原则，它只能从最左面开始匹配所以一个查询条件不包含创建索引的时候最左面的字段那么是无法使用组合索引的。

MySQL只会对<,<=,=,>,>=,between,in，以及某些时刻的like才会使用索引，当like以‘%’或者‘_’开头的时候就不会使用索引。

索引的缺点：
降低更新表的速度，在对表中数据进行更新操作的时候就会在更新索引上消耗一定的时间，所以会降低整体的效率。
会增加额外的空间：当有一张大表，创建了许多的组合索引的时候，索引文件会膨胀的特别快。
Hash索引
Hash索引中存储的是向数据行的指针，发生冲突生成链表。只有精确匹配所有列才能计算出有效的哈希值才有效。
Hash索引的优点：由于只存储Hash值所以索引的结构十分紧凑，这让Hash查找的速度非常快。
Hash索引的缺点：
1. hash索引只包含Hash值和行指针，而不存储字段的值，所以无法避免读取行
2. 因为不是按照索引的顺序读取的，所以不能进行排序。
3. 无法进行范围查询
4. 不支持索引列的部分匹配，因为在生成Hash值的时候用的就是Hash索引中所包含的全部的列
5. Hash冲突很高的时候，效率会变得很低下，而且Hash冲突很高的时候对索引的维护代价也特别大。
InnoDB有一项自适应Hash索引的功能，当数据库引擎发现当前某个字段用于查找十分频繁的时候，就会自动为其创建Hash索引以提高查找效率。

数据库优化方式：
1. 选择最适合的数据类型，因为数据库中的表越小它的查询效率越高，我们应该将表中的字段类型尽可能设置的小，如果数据只有已知的几个选择，比如省份，那么我们可以考虑将其设置为enum类型，因为在MySQL中Enum类型被当做数值类型来进行处理。应当尽可能将字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
2. 使用连接（JOIN）代替子查询，使用join之所以效率高是因为MySQL不需要创建一个临时表来完成逻辑上需要两个步骤的操作。
3. 使用UNION来代替手动创建临时表，它可以把需要使用临时表的两条或者多条select查询合并在一个查询中。在客户端查询结束后临时表会被自动删除，从而保证数据库整齐高效。使用UNION要保证各个查询中select字段的数目要相同。
4. 事务，保证数据库操作的原子性，一致性，隔离性，持久性。
5. 锁定表，表锁和行锁，不需要锁定整个数据库
6. 使用外键，表锁定可以保证数据完整性但是不能保证数据关联性，这个时候我们可以使用外键。
7. 使用索引
8. 优化查询语句：最好是在相同类型的字段间进行比较，在建立有索引的字段上尽量不要使用函数，也尽量不要把索引作为表达式中的值，那样会降低索引的效率，在搜索字符类型的时候，使用LIKE和通配符会降低效率，应该尽可能少的使用。
字段只有作为独立的列才会被作为索引（不是函数中的参数，不参与表达式运算）