Mysql开发篇

表类型(存储引擎)的选择

Mysql5.7之后支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、NDB等。其中InnoDB和NDB提供事务安全表，其他索引引擎都是非事务安全表。Mysql5.5以前默认的存储引擎是MyISAM，5.5之后改为InnoDB。

选择合适的存储引擎：

• MyISAM:如果应用是以读操作和插入操作为主，只有极少的更新和删除操作，并且对事务的完整性没有要求、没有并发写操作，那么选择这个存储引擎合适。OLTP环境一般不建议使用。因为MyISAM不支持事务，也不支持外键，没有聚簇索引，没有数据缓存。该表主要关注性能并且不支持高并发操作。
• InnoDB：对于大多数的应用系统，InnoDB都是合适的选择。如果应用对系统的完整性有比较高的要求，在并发条件下要求数据的一致性。数据操作除了查询和插入以外，还包括很多的更新和删除操作，那么应该优先选择InnoDB存储引擎。InnoDB除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚。InnoDB提供具有提交、回滚和崩溃恢复能力的事务安全保障，同时提供了更小的锁粒度和更强的并发能力，拥有自己独立的缓存和日志。对比MyISAM，InnoDB会占用更多的磁盘空间以保留数据和索引。并且InnoDB提供自动增长列，外键约束，在创建外键时要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应索引。并且InnoDB表创建时必须包含主键，如果没有显示指定主键的话，它会自动生成一个6字节的long类型隐藏字段作为主键。
• MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问速度。MEMORY的缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY通常用于更新不大的小表，用以快速得到访问结果。
• MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们，MERGE表的优点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。这对于诸如数据仓库等VLDB环境十分适合。

数据类型

• char和varchar：char是固定长度的，所以他的处理速度比varchar要快，但是其缺点是浪费存储空间，程序需要对后面的空格进行处理，所以，对那些长度变化不大，并且对查询速度有较高要求的数据可以考虑使用CHAR类型来存储。(注：对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列，使用固定长度的CHAR列不一定比使用可变长度的VARCHAR列性能要好，且CHAR平均占用的空间多余VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的)。
• TEXT和BLOB：二者都是保存较大文本的。主要差别是BLOB能用来保存二进制数据，比如照片；text只能保存一些字符数据，比如文章和日记。但他们都可能会引起一些性能问题，比如执行大量删除时，会给数据库留下很大的空洞，需要OPTIMIZE TABLE功能对这类表定期进行碎片整理。
• 浮点数和定点数：浮点数float和double，定点数BigDecimal和decimal。没有什么要点。

索引
InnoDB下默认的索引都是BTree索引
设计索引原则：

• 1、要在条件列上创建索引，而不是查询列，通俗点说就是在where子句中或者链接子句中的列，而不是select关键字后的选择表中的列。
• 2、尽量使用高选择度索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。
• 3、使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这么做。
• 4、利用最左前缀。在创建一个n列的索引时，实际相当于创建了Mysql的可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
• 5、对于InnoDB存储引擎的表，尽量手工指定主键。记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，按唯一索引的顺序保存。如果既没有主键也没有唯一索引，那么表中会自动生成一个内布列，按照这个列的顺序保存。但是按主键或者内布列时访问是最快的。所以InnoDB尽量自己指定主键。
  索引设计误区：
• 不是所有的表都需要创建索引。比如一些配置表、代码表等数据量很小的表，创建索引就没有什么意义(主键除外)。
• 不要过度索引。索引不是越多越好，每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。
• 谨慎创建低选择度索引。
• 谨慎使用唯一索引。

另：Mysql8.0增加了不可见索引和倒序索引。索引Mysql8.0的sql性能相比5.7提升了近10倍。
事务控制和锁定优化
MySQL通过SET AUTOCOMMIT、SRART TRANSASACTION、COMMIT和ROLLBACK等语句支持本地事务。默认情况下，Mysql是自动提交的如果需要通过明确的Commit和Rollback来提交和回滚事务，那么就需要通过明确的事务控制命令来开始事务，这是和Oracle的事务管理明显不同的地方。

• START TRANSACTION 或BEGIN语句可以开始一项新的事务。
• COMMIT和ROLLBACK用来提交或回滚事务。
• CHIAN和RELEASE子句分别用来定义在事务提交或者回滚之后的操作，CHAIN会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，RELEASE则会断开和客户端的连接。
• SET AUTOCOMMOT可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

在同一个事务中，最好使用相同的存储引擎的表，否则ROLLBACK时需要对非事务类型的表进行特别的处理，因为COMMIT、ROLLBACK只能对事务类型的表进行提交和回滚。并且和Oracle的事务管理相同，所有的DDL语句是不能回滚的，并且部分的DDL语句会造成隐式的提交。
在Mysql中，使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器。XA {START|BEGIN} xid 用于启动一个给定xid值的XA事务。每个XA事务必须有一个唯一的xid值，因此该值当前不能被其他的XA事务使用。
5.7之后,Mysql在事务上解决了严格持久化问题，在session断开和实例崩溃的情况下，事务都不会自动回滚，同时在XA PREPARE(XA PREPARE xid该命令是使事务进入准备状态)时，之前的事务信息就会被写入BINLOG并同步到备库中。有用户决定是将悬挂事务回滚还是提交。
SQL注入和SQL MODE
sql注入就是利用某些数据库的外部接口将用户数据插入到实际的数据库操作语言当中，从而达到入侵数据库乃至操作系统的目的。可以在Mysql上使用绑定变量来避免SQL注入(Java驱动JDBC是用PreparedStatement语句来实现)，也可以使用应用程序提供的转换函数或者自己定义的函数进行校验。
SQL MODE定义了Mysql应支持的SQL语法、数据校验等，这样更容易地在不同环境中使用Mysql。
常用来解决如下问题：

• 通过设置SQL Mode，可以完成不同严格程度的数据校验，有效的保障数据的准确性。
• 通过设置SQL Mode为ANSI模式，来保证大多数SQL符合标准的SQL语法，这样应用在不同数据库之间进行迁移时，则不需要对业务SQL进行较大修改。
• 在不同数据库之间进行数据迁移之前，通过设置SQL MODE可以使Mysql上的数据更方便地迁移到目标数据库中。

Mysql分区
分区有利于管理非常大的表，它采用“分而治之”的逻辑。分区引入分区键的概念，分区键用于根据某个区间值、特定值列表或者HASH函数值执行数据聚集，让数据根据规则分布在不同的分区中。
Mysql分区类型：

• RANGE分区：基于一个给定连续区间范围，把数据分配到不同的分区。
• LIST分区：类似于RANGE分区，区别在LIST分区是基于枚举出的值列表分区，RANGE是基于给定的连续区间范围分区。
• COLUMNS分区：类似于RANGE合LIST，区别在于分区键既可以是多列又可以是非整数。而LIST和RANGE只支持整数。
• HASH分区：基于给定的分区个数，把数据取模分配到不同的分区。
• KEY分区：类似于HASH分区，但使用MySql提供的哈希函数。
• 子分区：也叫复合分区或者组合分区，即在主分区下再做一层分区，将数据再次分割。

Mysql不禁止在分区键值上使用NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，Mysql的分区把NULL当作零值，或者一个最小值进行处理。并且Mysql提供了添加、删除、重定义、合并、拆分、交换分区的命令，这些都是基于ALTER TABLE 命令来实现的。

MVCC机制：
mvcc又称为多版本并发控制，用不加锁的方式处理数据库读写并发事物问题，从而提高数据库的并发性能。
mvcc三大块：隐形字段、undo log和read view视图。
三个隐型字段：
db_trx_id:最近修改的事物id
db_roll_ptr:回滚指针，用于配合 undo log，指向当前记录的上个版本
db_row_id:隐藏自增主键(如果表里没有主键的话，innodb会以它生成一个聚簇索引)。
另外：还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了，该字段这里不作详细描述。
undo log分为两种
1、insert undo log
代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
2、update undo log
事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge线程统一清除。
对 MVCC 有帮助的实质是 update undo log ，undo log 实际上就是存在 rollback 文件中旧记录链。流程为：当有一个修改事物A在对某一条数据进行update操作时，undo log会将该条数据拷贝一份放入undo log中，然后事物A的这条记录中的隐型字段DB_ROLL_PTR对应undo log中该条数据中的隐形主键db_row_id。后续新增事物以此类推，事物B的DB_ROLL_PTR对应事物A中该条数据中的隐形主键db_row_id，且此时事物A也进入undo log中。
Read View 读视图
Read View 主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个 Read View 读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的 undo log里面的某个版本的数据。
Read View 遵循一个可见性算法，主要是将 要被修改的数据 的最新记录中的 DB_TRX_ID（即当前事务 ID ）取出来，与系统当前其他活跃事务的 ID 去对比（由 Read View 维护），如果 DB_TRX_ID 跟 Read View 的属性做了某些比较，不符合可见性，那就通过 DB_ROLL_PTR 回滚指针去取出 Undo Log 中的 DB_TRX_ID 再比较，即遍历链表的 DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的 DB_TRX_ID , 那么这个 DB_TRX_ID 所在的旧记录就是当前事务能看见的最新老版本。
例：事物1：A；事物2：B；事物3：C；事物4：D。
当B是快照读，A、C和D是对该条数据修改，D在B快照读之前先提交了数据。此时B读到的是D修改后的数据。（因为Read view在根据up_limit_id去对比各个事物的DB_TRX_ID的时候，会判断出D不在当前活跃事物列表中，而A和C在，所以会根据可见性跳过A和C，去读到4提交后的版本）