MySQL存储引擎
InnoDB和MyISAM的区别
（1）InnoDB和MyISAM都是Mysql的存储引擎，现在MyISAM也逐渐被InnoDB给替代，主要因为InnoDB支持事务和行级锁，MyISAM不支持事务和行级锁，MyISAM最小锁单位是表级。因为MyISAM不支持行级锁，所以在并发处理能力上InnoDB会比MyISAM好。
（2） 数据的存储上：MyISAM的索引也是由B+树构成，但是树的叶子结点存的是行数据的地址，查找时需要找到叶子结点的地址，再根据叶子结点地址查找数据。
InnoDB的主键索引的叶子结点直接就是存储行数据，查找主键索引树就能获得数据：
若是根据非主键索引查找，非主键索引的叶子结点存储的就是，当前索引值以及对应的主键的值，若是联合索引存储的就是联合索引值和对应的主键值。
（3）数据文件构成：MyISAM有三种存储文件分别是扩展名为：.frm（文件存储表定义）、.MYD (MYData数据文件)、.MYI (MYIndex索引文件)。而InnoDB的表只受限于操作系统文件的大小，一般是2GB
（4）查询区别：对于读多写少的业务场景，MyISAM会更加适合，而对于update和insert比较多的场景InnoDB会比较适合。
（5）count(*)区别：select count(*) from table，MyISAM引擎会查询已经保存好的行数，这是不加where的条件下，而InnoDB需要全表扫描一遍，InnoDB并没有保存表的具体行数。
（6）其它的区别：InnoDB支持外键，但是不支持全文索引，而MyISAM不支持外键，支持全文索引，InnoDB的主键的范围比MyISAM的大。
总结
| MyISAM | InnoDB | |
|---|---|---|
| 默认 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 外键 | 不支持 | 支持 |
| 聚集索引 | 不支持 | 支持 |
| 索引和数据 | 分开存储 | 存储在一起 |
| MVCC | 不支持 | 支持 |
| 备份 | 不支持 | 在线热备 |
| 全文索引 | 支持 | 不支持 |
| 主键范围 | 小 | 大 |
| 并发能力 | 低 | 高(通过MVCC来支持) |
| 锁粒度 | 表锁 | 行锁、表锁、页锁 |
| 查询 | 读多写少 | 更新和插入较多的场景 |
| 行数 | 保存 | 不保存 |
| 崩溃恢复 | 慢，易丢失 | 概率低 |
| 数据文件 | .frm（文件存储表定义）.MYD (MYData数据文件).MYI (MYIndex索引文件) | .frm.ibd只受限于操作系统文件的大小 一般是2GB |
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Michael's Blog！
相关推荐
2022-05-20
MySQL优化经验总结
线上SQL的调优经验 slow_query_log 日志中收集到的慢 SQL ，结合 explain 分析是否命中索引。 减少索引扫描行数，有针对性的优化慢 SQL。 建立联合索引，由于联合索引的每个叶子节点包含检索字段的信息，按最左前缀原则匹配后，再按其它条件过滤，减少回表的数据量。 还可以使用虚拟列和联合索引来提升复杂查询的执行效率。 监控sql执行情况，发邮件、短信报警，便于快速识别慢查询sql 打开数据库慢查询日志功能 简化业务逻辑 代码重构、优化 异步处理 sql优化 索引优化 SQL优化 分页优化。比如电梯直达，limit 100000,10 先查找起始的主键id，再通过id>#{value}往后取10条 尽量使用覆盖索引，索引的叶节点中已经包含要查询的字段，减少回表查询 SQL优化（索引优化、小表驱动大表、虚拟列、适当增加冗余字段减少连表查询、联合索引、排序优化、慢日志 Explain 分析执行计划）。 where子句 避免对字段进行null值判断、表达式操作等； where表之间的连接，必须写在其他where条件之前...
2019-03-20
MySQL主从复制与读写分离
主从同步 master主库，有数据更新，将此次更新的事件类型写入到主库的binlog文件中 主库会创建log dump 线程通知slave有数据更新 slave从库，向master节点的 log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的Relay log 中继日志中 slave 再开启一个SQL 线程读取Relay log事件，并在本地执行redo操作。将发生在主库的事件在本地重新执行一遍，从而保证主从数据同步 主从延迟 指一个写入SQL操作在主库执行完后，将数据完整同步到从库会有一个时间差，称之为主从延迟。 主库生成一条写入SQL的binlog，里面会有一个时间字段，记录写入的时间戳 t1 binlog 同步到从库后，一旦开始执行，取当前时间 t2 t2-t1，就是延迟时间 注意：不同服务器要保持时钟一致。 主从延迟排查方法 通过 show slave status 命令输出的Seconds_Behind_Master参数的值来判断 为零：表示主从复制良好 正值：表示主从已经出现延时，数字越大，表示从库延迟...
2021-03-20
MySQL - 锁
[TOC] 锁机制 计算机协调 多个进程或线程 并发访问某一资源的机制。 锁的类型及其特点开销、加锁、颗粒度、冲突、并发等角度分析 表级锁(table-level locking) 开销大，加锁快，不会出现死锁，颗粒度大，锁冲突概率高，并发度小； 页面锁(page-level locking) 开销、加锁效率、颗粒度介于表锁和行锁之间，会出现死锁，并发度一般； 行级锁(row-level locking) 开销大，加速慢，会出现死锁，颗粒度小，锁冲突概率小，并发度高； 锁的类型应用场景表锁 - 适合 以查询为主，只有少量按索引条件更新数据的应用，如web应用； 行锁 - 适合 大量按索引条件并发 更新少量不同数据，同时又有并发查询的应用，如在线事务处理系统； 锁的支持度及情况MyISAM 只支持 表锁 InnoDB 默认支持 行锁，也支持表锁 12-- 查询表锁争用情况show status like 'table%'; # Table_locks_waited 大则表锁争用情况严重 锁的维度划分共享锁(S) - 读锁 排他锁(X) - 写锁 意向共享锁...
2019-03-20
SQL语句性能分析工具 - explain
通过explain，如以下例子： 1EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26'; id select_type table partitions type possible_keys key key_len ref filtered rows Extra 1 SIMPLE titles null const PRIMARY PRIMARY 59 const,const,const 10 1 id：在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id ，如 explain select * from s1 where id = (select id from s1 where name = 'egon1'); 第一个select的id是1，第二个select的id是2。 有时候会出现两个sel...
2019-03-20
MySQL主键
主键使用自增ID还是UUID?能说说原因吗？ 自增ID和UUID作为主键的考虑主要有两方面， 一个是性能 另一个就是存储的空间大小， 一般没有特定的业务要求都不推荐使用UUID作为主键。 因为使用UUID作为主键插入并不能保证插入是有序的，有可能会涉及数据的挪动，也有可能触发数据页的分裂，因为一个数据页的大小就是16KB，这样插入数据的成本就会比较高。 而自增ID作为主键的话插入数据都是追加操作，不会有数据的移动以及数据页的分裂，性能会比较好。 另一方面就是存储空间， 自增主键一般整形只要4个字节，长整形才占8字节的大小空间， 而使用UUID作为主键存储空间需要16字节的大小，会占用更多的磁盘， 在二级索引中也会存出一份主键索引，这样多占用消耗的空间就是两倍，性能低， 所以不推荐使用。 自增id是连续的，插入过程也是顺序的，总是插入在最后，减少了页分裂，有效减少数据的移动。 所以尽量不要使用字符串（如：UUID）作为主键。
2021-03-20
MySQL 面试题速查
本文是浓缩版速答。每题尽量一句话讲清结论；详细原理点对应链接。 一、索引Q: 为什么 MySQL 用 B+ 树而不是 B 树/红黑树/Hash？B+ 树非叶节点不存数据 → 一个节点能装更多 key → 树更矮 → 磁盘 IO 少；叶子节点链表 → 范围查询快。Hash 不支持范围、不支持排序。 Q: 聚簇索引 vs 二级索引？ 聚簇索引：叶子节点存整行数据，InnoDB 主键即聚簇索引 二级索引：叶子节点存主键值，需要”回表” Q: 什么是覆盖索引？ → 详细SELECT 的字段全在二级索引里，不用回表。EXPLAIN 显示 Using index。 Q: 最左前缀原则？联合索引 (a, b, c) 只能命中 a / a+b / a+b+c，不能 b 或 b+c。 Q: 索引为什么会失效？ → 12 种原因函数运算、隐式转换、%xxx、OR 含非索引列、违反最左前缀、负向查询… Q: 什么时候不该建索引？ → 优缺点小表、低选择性字段、写多读少、字段经常更新。 二、事务与隔离级别Q: ACID 是什么？ A 原子性：要么全做，要...
评论