mysql =》 InnoDB 好 MyISAM 的区别

目录

索引： 

索引的最左匹配特性：

使用B+tree作为索引结构的原因？

InnoDB

MyISAM

SQL语句的优化：（https://mp.weixin.qq.com/s/nAQK3T-ZdYB1SILn4n1ZPA） 

MySQL优化

InnoDB为什么推荐使用自增ID作为主键？

---

1. 事务 ： InnoDB支持事务， MyISAM不支持；
2. 锁：  InnoDB支持表级锁， 行级锁；   MyISAM不支持；
3. 外键：  InnoDB 支持，  MyISAM 不支持。  一个带有外键的InnoDB表转为MyISAM会失败；
4. 索引：  InnoDB 是聚簇索引，  MyISAM 是非聚簇索引；  
5. 计数： 对于select count(*) from table， InnoDB 不保存表的具体行数, 需要扫描全表，时间复杂度O(n) ， MyISAM 用一个变量保存了表的所有行数， 时间复杂度O(1)， 注意不能带where条件;
6. 唯一索引： InnoDB 必须要有唯一性索引，如主键索引， 如果未指定，会自己找一个唯一性列或自动创建一个隐式的主键row_id来充当默认主键；  InnoDB 可以没有。
7. 全文索引：InnoDB 不支持全文索引（MySQL 5.7 之后支持了）， MyISAM 支持；

索引： 

高效查询数据的数据结构！

索引的最左匹配特性：

联合索引： 多字段索引(比如 name,age,sex)，  避免第一个字段无法使用索引的现象。因为搜索时，始终是先根据第一个字段进行索引搜索，如果搜不到， 后面几个字段也就无法使用索引进行查询；

使用B+tree作为索引结构的原因？

首先， 什么是B+tree？

真实数据存储在叶子节点（叶节点的数据域存储了真实数据）， 非叶子节点并不存储真实数据，存储虚拟数据和指针。   如图，17和35都是虚拟数据。

降低磁盘IO次数。 其数据结构特点， 在查找数据时把磁盘IO次数控制在很小的数量级。

操作系统 页缓存预读，  根节点也会预先加载到内存；

相同点：

• 底层都是B+tree；

区别：

InnoDB

分为主键索引和辅助索引，

主键索引叶子节点存储真实数据，索引表本身也是数据表；

辅助索引叶子节点存储主键ID；

InnoDB的索引是 聚簇索引；

InnoDB索引本身要按照主键聚簇， 因此主键不能缺失；  MyISAM 则不需要主键非空；

InnoDB 如果没有显示指定主键， 则会自动选择一个可以唯一标识数据记录的列作为主键， 如果不存在这种列， 则MySQL会自动为InnoDB创建一个隐含的字段作为主键，该字段长度为6个字节，

int占4个字节（byte），

bigint，即long型，占8个字节； 

short int 占2个byte ，

char占2个字节(byte)，

 浮点 float 占4个字节(byte)，

double 占 8个字节(byte)，

Boolean 类型只占 1（bit）

使用自增有序的字段作为索引主键会比非有序字段作为主键更高效；

MyISAM

底层实现也是B+ tree, 但是 非叶子节点不存储数据， 叶子节点存储数据再数据库的真实地址指针（数据记录的地址），不存数据；

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

因为索引上仅存储数据记录的地址， 因此也叫 非聚簇索引；

SQL语句的优化：（https://mp.weixin.qq.com/s/nAQK3T-ZdYB1SILn4n1ZPA） 

1. 遵从索引的最左前缀匹配原则；
2. 索引并不是越多越好。 因为索引会占空间， 对数据的写入、更新、删除也会需要而外资源处理索引，MySQL在允许时也要消耗资源维护索引。
3. 尽量使用自增字段作为索引主键；   InnoDB使用聚簇索引，叶子节点上存储主键，每个叶子节点也就是一个page， 每个page页内，主键按顺序存放， 如果主键有序，则新写入的数据只需要顺序写到page页即可， page页满了则新建page页，继续写， 较高效，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。  但是如果主键是乱序的， 则会导致page 数据有可能为了保证有序需要移动现有数据顺序， 甚至目标页的数据已经写入磁盘，缓存中也没有了， 此时不得不从磁盘读出，增加了额外的开销， 同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
4.  = 和 in 查询可以乱序。  比如 比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式；
5. 尽量使用区分度高的列作为索引。    区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0；
6. 索引列不要参与计算，保持列“干净”。  
7. 尽量扩展索引，而不是新增索引。   比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可，当然要考虑原有数据和线上使用情况

---

MySQL优化

常用的配置， 如 

innodb_buffer_pool_size ：  缓冲池容量。   

                                         缓冲池是数据和索引缓存的地方。 典型的值是内存的70%， 如 5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

max_connections ： 最大的数据库连接数， 默认151个。 max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连                                   接池或者在MySQL里使用进程池有助于解决这一问题。

InnoDB为什么推荐使用自增ID作为主键？

InnoDB中， 主键索引的叶子节点，是有序存储的，  如果自增ID作为主键， 则可以将叶子节点所在的页追加写， 不需要调整顺序，调整顺序会带来页中数据的移动。B+tree 的避免B+tree 频繁的 分裂和合并；