网站建设龙采科技,杭州seo整站优化,跳舞游戏做的广告视频网站,郑州找人公司文章目录 一、索引不同1 InnoDB聚簇索引#xff0c;MyISAM非聚簇索引1 InnoDB聚簇索引2 MyISAM非聚簇索引 2 InnoDB必须要有主键#xff0c;MyISAM允许没有主键3 InnoDB支持外键4 InnoDB不支持全文索引5 索引保存位置不同 二、对事物的支持三、存储结构不同四、存储空间不同五… 文章目录 一、索引不同1 InnoDB聚簇索引MyISAM非聚簇索引1 InnoDB聚簇索引2 MyISAM非聚簇索引 2 InnoDB必须要有主键MyISAM允许没有主键3 InnoDB支持外键4 InnoDB不支持全文索引5 索引保存位置不同 二、对事物的支持三、存储结构不同四、存储空间不同五、支持锁粒度不同六、count()函数不同七、常见问题1 InnoDB 为什么一定要有主键2 InnoDB 为什么推荐使用整型的自增主键做索引?3 为什么InnoDB主键索引结构叶子节点存储的是主键值4 聚簇索引和非聚簇索引哪个查询效率更快5 联合索引的底层结构长什么样6 最左前缀原则 一、索引不同
1 InnoDB聚簇索引MyISAM非聚簇索引
MyISAM 这棵树的叶子结点存储数据是物理地址InnoDB的叶子结点直接存储数据记录这也是簇索引与非簇索引的区别。
1 InnoDB聚簇索引
表数据文件本身就是按BTree组织的一个索引结构文件数据文件是和主键索引绑在一起的。聚集索引 - 叶子节点包含了完整的数据记录必须要有主键通过主键索引效率高。如果建表时未创建主键MySQL会从表中找到一列全部不相等的数据作为主键维护索引树如果找不到则维护一个隐藏列用于维护索引树。这个工作尽量让我们自己完成不必再消耗MySQL的性能。辅助索引叶子节点存储的数据为主键id使用辅助索引查询时需要查询两次先查询到主键然后再通过主键查询到数据。
innodb主键索引查找数据流程 -- 查询主键 30的数据
1 将根节点加载到内存中在内存中通过二分查找算法快速找出30的位置找到对应的页
2 再将这一页数据加载到内存中快速找出30的位置找到对应的页
3 最后定位到叶子节点对应位置从叶子节点中取出数据data。innodb辅助索引 查找数据流程 - - 辅助索引 Eric
1 将辅助索引的根结点加载到内存中在内存中通过二分查找找出对应页
2 以此类推直到定位到叶子节点对应位置从叶子节点中取出当条数据的主键id
3 使用主键id在主键索引树中回表查询查处对应数据。2 MyISAM非聚簇索引
MyISAM是非聚簇索引索引和数据分开存储也是使用BTree作为索引结构索引保存的是数据文件的指针。主键索引和辅助索引是独立的。MyISAM的B树主键索引和辅助索引的叶子节点都是数据文件的地址指针。叶子节点只包含索引地址不包含数据数据在另外一个文件MYD文件。
MyISAM 查找数据流程查找col 30的数据
1 从MYI文件中查找索引树定位数据所在的叶子节点通过叶子节点中包含的数据地址
2 在图中右下加MYD文件中找出具体的数据。2 InnoDB必须要有主键MyISAM允许没有主键
MyISAM 允许没有任何索引和主键的表存在索引都是保存行的地址。InnoDB 如果没有设定主键或者非空唯一索引就会自动生成一个6字节的主键(用户不可见)数据是主索引的一部分附加索引保存的是主索引的值。
3 InnoDB支持外键
InnoDB支持外键而MyISAM 不支持。对一个包含外键的InnoDB表转为MYISAM会失败
4 InnoDB不支持全文索引
InnoDB不支持fulltext全文索引MyISAM支持InnoDB不支持FULLTEXT类型的全文索引但是InnoDB可以使用sphinx插件支持全文索引并且效果更好。
5 索引保存位置不同
MyISAM 的索引以表名.MYI文件分别保存。InnoDB的索引和数据一起保存在表空间里。
二、对事物的支持
InnoDB支持事务MyISAM不支持。InnoDB支持事务支持外键、行锁、事务是他的最大特点对于InnoDB每一条SQL语言都默认封装成事务自动提交这样会影响速度所以最好把多条SQL语言放在begin和commit之间组成一个事务。MyISAM 强调的是性能每次查询具有原子性。其执行数度比InnoDB类型更快但是不提供事务支持)。MyISAM 默认表类型它是基于传统的ISAM类型它是存储记录和文件的标准方法。不是事务安全的而且不支持外键。
三、存储结构不同
MyISAM 数据和索引是分别存储的数据文件的扩展名为(.MYD)。索引文件的扩展名是(.MYI)。InnoDB 数据和索引是集中存储的(.ibd)InnoDB表的大小只受限于操作系统文件的大小一般为2GB。
四、存储空间不同
MyISAM 可被压缩存储空间较小。支持三种不同的存储格式静态表(默认但是注意数据末尾不能有空格会被去掉)、动态表、压缩表。InnoDB 需要更多的内存和存储它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
五、支持锁粒度不同
MyISAM 只支持表级锁用户在操作MyISAM 表时,selectupdatedeleteinsert语句都会给表自动加锁如果加锁以后的表满足insert并发的情况下可以在表的尾部插入新的数据。InnoDB支持事务和行级锁行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁只是在WHERE的主键是有效的非主键的WHERE都会锁全表的。
六、count()函数不同 MyISAM 保存有表的总行数如果select count(*) from table;会直接取出出该值。 InnoDB 没有保存表的总行数如果使用select count(*) from table;就会遍历整个表消耗相当大但是在加了wehre条件后MyISAM 和InnoDB处理的方式都一样。
七、常见问题
1 InnoDB 为什么一定要有主键
1 bd文件在存储的时候这个文件必须用B树的结构来组织B树来源
2 有主键直接用主键
3 没有主键不建索引MySQL会从表中找到一列全部不相等的数据作为主键维护索引树
4 如果找不到则维护一个隐藏列用于维护索引树。
5 这个工作尽量让我们自己完成不必再消耗MySQL的性能。2 InnoDB 为什么推荐使用整型的自增主键做索引?
整型
1 查找元素都从根节点开始查找经历了很多次比较大小的操作
使用整型比较大小更快字符串比较大小逐位比较从左到右整型效率更快。
2 一个索引占用的磁盘空间越小整个索引占用的空间也就越小。节约磁盘很贵空间整型占用的页大小更小 。
自增
1 B树叶子节点是双向指针排好序的对范围查找友好
2 B树插入非自增的索引索引树可能需要分裂、树需要再次平衡
插入自增主键可以减少索引树分裂和再次平衡的问题3 为什么InnoDB主键索引结构叶子节点存储的是主键值
1 一致性和节省存储空间
2 innodb只有一个聚簇索引如果建了主键就会直接用主键做聚簇索引。
3 二级索引的叶子节点放的值是聚集索引的索引值。为了保证一致性、节省存储空间减少复杂度。二级索引是非聚集索引稀疏索引需要回表查询。4 聚簇索引和非聚簇索引哪个查询效率更快
1 聚簇索引更快定位到叶子节点时就已经拿到了整行数据
2 非聚簇索引需要跨文件去查在MYI文件中找到叶子节点中数据地址
在通过数据地址在MYD文件中去查找整行数据5 联合索引的底层结构长什么样
1 图中是联合主键索引是聚集索引按照联合主键字段从左到右的顺序左小右大的顺序进行排序。
叶子节点数据页保存的是具体的数据不是聚集索引的值不用做回表操作。
2 树的高度要提升即需要把当前页的第一个数据的索引提升上去6 最左前缀原则
1 索引是排好序的数据结构
2 不按照最左前缀原则走使用的索引不符合排好序的原则索引就会失效。如有缺漏或不对的地方还请各位指正。
欢迎关注我的个人公众号
