MySQL引擎笔记

MySQL引擎

mysql中所有的表结构、表数据和索引在服务器上都是以文件的形式存放在磁盘中，每个数据库都对应了一个文件夹，数据库中的表存放在对应的文件夹中．

MyISAM引擎

使用B+树作为索引结构

1. 文件存储：以3个文件存储在磁盘上，数据文件与索引文件分开存放．.frm：表结构文件；.MYD (MYData)：数据文件；MYI (MYIndex)：索引文件
2. 非聚集索引：主索引文件中只存放了数据的地址，没有存放数据内容

主索引：叶子节点key存放的是主键的值，数据部分存放的是数据记录的地址．

辅助索引：结构与主索引一样的，数据部分存放的是数据记录的地址，key存放的是辅助索引对应列的值．不同点在于主索引key不能重复，而辅助索引key可以重复．

检索流程：根据B+树搜索算法搜索索引，查到指定的key，取出对应的数据域存放的地址，根据获得的数据地址在数据文件中读取相应的记录．

InnoDB引擎

使用B+树作为索引结构

1. 文件存储：以2个文件存放在磁盘上，数据与索引存放在一个文件中．.frm：表结构文件；.ibd：数据与索引文件．
2. 聚集索引：主索引文件中包含了完整的数据记录．这种聚集索引可以使按主键搜索变得非常高效，但是使用辅助索引搜索时检索两遍索引，先检索辅助索引得到主键，然后通过主键在主键索引中获得记录．

主键索引：叶子节点key存放的是主键的值，数据部分存放的是完整的数据记录．
由于InnoDB中的数据是按主键索引聚集在一起的，所以InnoDB中的表必须要有主键，如果没有显式指定主键的话，MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

辅助索引：InnoDB中所有的辅助索引都是引用主键来作为数据域．使用辅助索引时，每次都需要先通过辅助索引查找到相应的主键，然后通过主索引取出数据记录．

优化索引：

1. 为什么不建议使用过长的字段作为主键？
   因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大
2. 为什么不建议使用用非自增的字段作为主键？例如字符串
   因为InnoDB数据是使用B+Tree结构存放的，非自增的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，性能低下．而且在数据检索时经常会使用到< 、<=、>、>=之类的范围搜索，使用字符串比较效率低．因此建议使用自增整型字段作为主键．

建索引的原则

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp('2014-05-29')。
5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

参考文章

https://www.kancloud.cn/kancloud/theory-of-mysql-index