总结mysql

mysql版本5.7.22

一些常用命令：

查询mysql提供的所有存储引擎，show engines;

每一种存储引擎见Comment。常用的为InnoDB和MyISAM。

查看mysql当前默认的存储引擎，show variables like '%storage_engine%';

查看表的存储引擎，show table status like 'table_name';

MyISAM和InnoDB的区别：

MyISAM在5.5版之前是mysql的默认数据库引擎，mysql5.5版本以后默认的存储引擎为InnoDB。

MyISAM和InnoDB对比：

是否支持行级锁：MyISAM只有表级锁（table-level locking），而InnoDB支持行级锁（row level locking）和表级锁，默认为行级锁。

是否支持事务和崩溃后的安全恢复：MyISAM强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型更快，但是不提供事务支持；但是InnoDB提供事务支持，外键等高级数据库功能。具有事务提交（commit），回滚（rollback）和崩溃修复能力（crash recovery capabilities）和事务安全型（transcation-safe(ACID compliant)）表。

是否支持外键：MyISAM不支持，InnoDB支持。

是否支持MVCC：InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效；MVCC只在READ COMMITED 和 REPEATABLE READ两个隔离级别下工作；MVCC可以使用乐观锁和悲观锁实现；

MVCC：多版本并发控制，指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undolog中实现的，通过undolog可以找回数据的历史版本。找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。

大表优化：

1、禁止不带任何限制数据范围条件的查询语句。

2、读写分离。主库负责写，从库负责读。

3、垂直分区。

4、水平分区。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。

数据库分片的两种常见方案：

• 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
• 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

一条sql语句在Mysql中是如何执行的？

连接器->缓存（8.0后移除）->分析器->优化器->执行器

？？？连接的时候已校验权限，执行时为什么还要校验权限呢？？？

Mysql高性能优化建议：

1、单表数据量建议控制在500W以内。

2、谨慎选择分区键，跨分区查询效率可能更低。

3、冷热数据分离，减少表的宽度。

4、禁止在数据库中存储图片，文件等大的二进制数据。通常存储在文件服务器，数据库只存储图片或文件地址信息。

5、单表索引建议不超过5个。

6、每个InnoDB表必须有主键。

7、避免建立冗余索引或重复索引。冗余索引比如：index(a,b,c) index(a,b) index(a)。

8、尽量避免使用外键约束？？？ 外键会影响父表和从表的写操作从而降低性能。

9、避免使用子查询，因为子查询不走索引，会临时存到内存或磁盘。子查询返货数据量大尤为严重。

事务的四大特性ACID：

原子性（Atomicity），一致性（Consistency），隔离性（Isolation），持久性（Durability）。

并发事务问题：脏读，丢失修改，不可重复读，幻读。

与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使用 REPEAaTABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

InnoDB存储引擎的锁的算法有三种：Record lock,Gap lock,Next-key lock。

tips:

1、如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。

本文完。

 

勤助手-教育机构好助手。一款专注中小机构教学管理的软件。

网址：https://www.qinzs.com

注册可免费使用。