MySQL探索之路——初识

本博客借鉴《高性能mysql》

MySQL逻辑架构

出处：《高性能mysql》
以上就是mysql的逻辑架构
①最上层是连接处理
②第二层包括了解析器、查询缓存、优化器，大多的服务功能都在这一层实现，除了解析、优化外还有一些内置函数，存储过程、触发器、视图等。
③第三次主要包含了存储引擎，负责了数据的存储和提取。

锁

在并发控制时通常采用共享锁和排它锁来处理，也就是所谓的读锁和写锁。读锁是共享的，写锁是排他的。
锁粒度：当锁定的数据量少，那么系统的并发程度肯定越高。加锁是需要开销的，锁策略就是在锁开销和数据安全之间寻求平衡。
表锁：表锁就是锁定整张表，锁开销小，但是并发性能却不好。表锁会在mysql本身和存储引擎中实现。
行级锁：行级锁可以最大程度地支持并发，但是锁开销也大，行级锁只在存储引擎层实现，mysql服务层并不实现。

事务

事务即是一组原子操作，事务内的语句要么全部执行成功要么全部失败。
事务四大特性
原子性：一个事务视为不可分割的最小工作单元，要么全部成功要么全部失败。
一致性：数据库总是从一个一致的状态切换到另一个一致的状态。
隔离性：通常来说一个事务所做的修改在提交前，对其他事务是不可见的，当然有不同的隔离级别。
持久性：一旦事务提交，数据就会永久保存在数据库中。
隔离级别
READ UNCOMMITTED（未提交读）
事务还未提交，其他事务就能读取到未提交的数据，也称为脏读。
READ COMMITTED（提交读）
一个事务提交后，其他事务才能看到修改的数据。其他事务多次查询记录结果不一致。
REPEATABLE READ（可重复读）
保证同一个事务在多次读取记录结果是一致的，但是会造成幻读，虽然也是结果不一致，但是跟不可重复读的区别在于，不可重复读在于读一条记录读两次不一样，而幻读是多次读一个范围性的数据会出现新的记录行。
这个隔离级别是mysql的默认级别，InnoDB和XtraDB通过多版本控制并发MVCC解决了幻读的问题。
SERIALIZABLE（可串行化）
强制事务串行执行，在读取每一行数据都会加锁。但是效率肯定不高。

多版本并发控制MVCC

为了提高性能，没有使用简单的行级锁，而是采用MVCC去控制。
InnoDB的MVCC通过每行记录后保存两个隐藏的列实现。一个保存行的创建时间，一个保存行的过期时间（或者说删除时间）。当然这不是实际时间，而是系统版本号。
SELECT：InnoDB查找选择行的创建版本要小于等于当前事务的版本，删除版本要么未定义，要么大于当前事务的版本，这样保证了行在当前事务执行前创建，执行后删除。
INSERT：保存当前系统版本号为行的创建版本号。
DELETE：保存当前系统版本号为行的删除版本号。
UPDATE：插入一条新记录，保存当前系统版本号为行的创建版本号，同时保存当前系统版本号到原来的行作为删除版本号。
MVCC只与提交读和可重复读兼容。

存储引擎

InnoDB
InnoDB是mysql的默认的事务型引擎。 InnoDB的数据存储在表空间中，表空间有一系列数据文件组成。InnnoDB基于聚簇索引建立，对主键查询又很高性能，二级索引中必须包含主键列。InnoDB采用MVCC支持高并发，并通过间隙锁防止幻读出现。InnoDB内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读。InnoDB还支持热备份。
MyISAM
MyISAM提供了大量的特性，包括全文索引，压缩等，但不支持事务和行级锁。崩溃后无法安全恢复。MyISAM对并发的处理是对整张表加锁，读取时加共享锁，写入时加排它锁。所以写的性能并不好。
MySQL中myisam与innodb的区别
(1)、问5点不同；
1>.InnoDB支持事务，而MyISAM不支持事务
2>.InnoDB支持行级锁，而MyISAM支持表级锁
3>.InnoDB支持MVCC, 而MyISAM不支持
4>.InnoDB支持外键，而MyISAM不支持
5>.InnoDB不支持全文索引，而MyISAM支持。
(2)innodb引擎的4大特性
插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

选择数据类型原则

①选择更小的数据类型
②选择简单的数据类型，使得计算机更容易处理
③尽量避免NULL，有NULL的列对mysql来说比较难优化，null的列需要更多存储空间，而且需要特殊处理，对索引、索引统计和值比较都更加复杂。

选择标识符

如何去选择一个合适的标识符，也可以说是主键。
一般来说主键通常有两种选择，一种就是无符号整型，一种就是随机字符串。
无符号整型一般是自动自增，随机字符串一般采用uuid。
对比：
基于InnoDB的聚簇索引，自增主键相较于随机字符串的性能更加好，对于随机字符串来说，插入值会随机地写到索引的不同位置，需要去寻找插入点，使得insert语句很慢，导致页分裂，磁盘随机访问。同时自增主键也比较节省空间，而自增主键是顺序的，写入的效率会高于随机字符串。
而对于select来讲，由于缓存的局部性原理，自增主键的效果也会比较好。

避免下列这些设计

①设计过多的列：mysql的存储引擎api在工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，在服务器层将缓存内容编码成各个列。从行缓冲中将编码过的列转换成行数据结构操作代价高，转换的代价依赖列的数量，所以尽量不要设计过多的列。
②太多的关联表：当关联表多的时候，解析和优化查询的代价会成为mysql的负担，单个查询最好在12个表以内做关联。