MySQL存储引擎以及索引

MySQL是一种插件是的存储引擎架构，他可以支持很多种存储引擎。

一、常用的存储引擎

1、innodb存储引擎：他是我们日常工作使用中最重要最常用的存储引擎，他支持事务处理（ACID）。他使用聚簇索引，这意味着索引（主键）的顺序就是数据物理存储的顺序。

他支持行级锁，只会在数据行加锁，可以增强数据处理并发能力。支持btree索引hash索引，全文索引，空间索引

2、myisam存储引擎：他是一款非事物存储引擎。5.6之前最常用。数据以堆表方式存储，没有任何顺序，没有聚簇索引但是索引直接指向数据的物理位置，这也使得他的查询io要少，查询性能要高于innodb。支持空间索引，btree索引，全文索引。

使用表及锁，读操作与写操作会有互相阻塞。不适合高并发读写混合场景，适合读请求远远大于写请求的场景。

3、csv存储引擎：采用csv文件存储，不支持事物。每一列以逗号分隔并使用双引号括起数据，所有列不能为null，不支持索引，不适合频繁查询和更新。

4、memory存储引擎：内存引擎，断电会丢数据，不支持事物，所有字段都是char类型（不支持大字段，text类型，类型长度固定）。支持btree和hash索引。

适合场景：缓存码表字典表（数据不多，但是需要频繁关联查询的表）、临时表数据存储、分析性数据（类似redis）。

5、archive存储引擎：只支持新增查询，不支持修改的非事务存储引擎，适合记录历史日志。使用zlib压缩插入的行，更加节约存储空间。只能在自增id建立索引

6、ndb存储引擎：支持集群使用，可以确保表的数据一致性，并支持事物。数据需要加载到内存中在使用。支持行级锁

二、MySQL的事物

什么是事务：一组要执行的sql，要么都成功要么都失败

原子性：要么都成功要么都失败。使用undo log记录数据修改前的状态。

一致性：事物开始前后，数据遵照既定逻辑存储，没有遭到破坏 redo log记录数据修改后的状态。

隔离性：每个事物操作与其他事物隔离。使用（共享锁 排它锁）保证数据的隔离性。四种隔离等级（默认提交读、未提交读，重量级可串行化，可重复读）。

持久性：事物一旦提交，就会持久化，不会丢失。

innodb的事物使用MVCC（多版本并发控制）实现

例如：事务1修改产生undo日志和redo日志，事务2不会查询未提交的数据，而是查询undo日志，从日志中获取修改前的数据。

锁的兼容性：排它锁（修改）和共享锁（查询）不兼容、排它锁不兼容排它锁、共享锁兼容共享锁。

三、MySQL索引概念

索引的意义：相当于一种目录，快速查找定位数据的物理位置，大大减少扫描数据的数据量。索引按照按照键值顺序存放，所以可以使用索引排序，也可以将磁盘的随即查找变成顺序查找，提高io性能。mysql的索引是在存储引擎层实现，而不是服务器实现，所以不同的引擎支持的索引类型也不同。相同的索引类型对于不同引擎的实现也不同。因此需要了解每一种索引的特点。

MySQL支持的索引类型：B-Tree索引（使用B+树存储数据）、Hash索引

B-Tree索引：索引的大小远小于数据的大小，根节点存在指向下层节点的指针，指针中定义了子节点的上限和下限，所以b树索引会找到要找的主键。他适合范围查找。索引节点也存储了索引列的数据，因此如果查询覆盖索引会很快。

什么情况会使用b树索引：

• 全值匹配查询，例如id='110103199400000000'。
• 匹配最左前列查询，联合索引最左索引 例如建立id和name的联合索引，那么id='110103199400000000' 可以利用索引。
• 匹配列前缀查询，可以匹配某一列的开头部分 ，例如 id like '110103%'。
• 范围查找 例如 age>'50'
• 精确匹配左前列并范围匹配另一列 ，例如建立id和age的联合索引，查询 id='110103199400000000' and age>'50'
• 只访问索引的查询，只返回索引不反回数据行
• order by

关于B树索引的限制：

• 如果不是按照最左列开始查找，则无法使用索引。例如建立id和name的联合索引，如果只查询name那么无法使用索引
• 使用索引是不能跳过索引中左边的列。 例如建立联合索引：id name age联合索引，如果查询按照id 和 age条件查询，那么只能按照id进行索引过滤。
• Not in和<>无法使用索引。
• 如果查询的时候索引列使用范围查询，那么右边的列都无法使用索引。

Hash索引：只能用在精确匹配查询，基于Hash表进行查询，只有查询条件与Hash索引中定义列完全匹配时，才能使用Hash索引。不支持范围和模糊查询。对于定义Hash索引的每一列，引擎会计算每一行的hash值并存储在索引中，真实数据地址指针存储在hash表中，可以快速定位数据。

Hash索引的限制：

• 使用hash索引必须先找到行指针，在找到行数据（二次io）。
• 无法排序。
• 无法范围，模糊查询
• 可能会存在hash冲突，不适合用在原则性很差的数据上，例如民族性别。
• 不支持覆盖索引。

四、索引优化

1、索引不是越多越好，在插入数据的时候还需要维护索引，所以入库的效率会降低。如果想要增加数据导入速度，那么需要删除所有的索引，保留自增主键。索引唯一性越高,选择性越高,效率越高。

2、过多的索引会增加查询优化器的选择消耗，增加系统负担。

3、查询索引列不能使用表达式或函数 例如 ABS(data) < XXX 是错误的，应该是data < ABS(XXX);

4、b树innodb索引最大大小不能超过767字节，myisam是1000。

5、大字段索引列可以使用前缀索引。 create index index_name on table(col_name(n));

6、联合索引中列的顺序很重要，因为索引列的顺序应该和查询是列的顺序一致，才能达到索引的最佳功能。因此保证选择性高，经常会被使用的列当做索引的最左列，之后的列以此类推

7、使用覆盖索引。如果只需要读取索引就能完成查询，那么这将极大的减少磁盘io操作。

8、不要使用'%XXX%'过滤查询