MySQL的架构和性能优化

一、架构

MySQL逻辑架构整体分为三层，最上层为客户端，并非MySQL独有，诸如：连接处理，授权认证，安全等功能均在这一层处理
MySQL大多数核心服务均在中间这一层，包括查询解析，分析优化，缓存，内置函数，所有的跨存储引擎功能也在这一层实现：存储过程，触发器，视图等。
最下层为存储引擎，负责MySQL中的数据存储和提取

1.MySQL查询过程

连接层（客户端/服务端通信协议）

• MySQL客户端/服务端的通信协议通常是半双工，要么服务器向客户端发送数据，要么客户端向服务器发送数据，当客户端要查询的时候向服务端发送消息，进行TCP/IP连接或者socket连接，并且向服务端提供自己的用户名和密码，服务端提供专用连接线程，接受用户的SQL，后返回结果

SQL层

• 接受上层传送的SQL语句
• 进行语义检查：判断sql语句的类型
• 权限检查
• 对sql语句进行解析
  • 通过关键字将sql语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和校验，比如sql中是否使用了错误的关键字或者关键字的顺序是否正确等。预处理会根据M有SQL规则进一步检查解析树是否合法等
• 进行查询优化
  • 重新定义表的关联顺序
  • 优化MIN()和MAX()函数（找出某列的最小值，如果该列有索引，只需要查找B+tree索引最左端）
  • 提前终止查询（比如：使用limit时，查找到满足数量的结果集合后立即终止查询）
  • 优化排序（老版本中先读取行指针和需要排序的字段你在内存中对其排序，然后再更具排序结果去读取数据行，新版本中采用单词传输排序，一次性读取所有的数据行，然后根据给定的列排序，对于I/O密集型应用，会提高效率）
• 性能优化
  • 用多个小表代替一个大表，不要过度设计
  • 批量插入代替循环单挑插入
  • 合理控制缓存空间大小，一般来说其大小设置为及时找比较合适
• 查询缓存（不建议轻易打开查询缓存）
  • 在解析一个查询语句钱，如果查询缓存时打开的，那么mysql会检查这个查询语句是否名字拆线呢缓存中的数据，如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果
  • mysql将缓存放在一个引用表中，可以理解为一个类似于HashMap的数据结构
  • 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
  • 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

存储引擎层（类似于linux中的文件）
负责根据SQL层的执行结果，从磁盘上拿数据，返回给客户端

2.MyISAM和InnoDB的特点

MyISAM引擎特点：

• 不支持事务（保证数据安全ACID）
• 表级锁定
• 读写相互阻塞，写时不能读，读时不能写
• 只缓存索引
• 读取数据较快，占用资源较少
• 不支持MVCC（多版本并发控制机制）高并发
• 奔溃恢复性较差

使用场景：
只读（或者写较少时），表较小
InnoDB引擎特点

• 行级锁
• 支持事务，适合处理大量短期事务
• 读写阻塞与事务隔离级别有关
• 可缓存数据和索引
• 奔溃后恢复性较好
• 支持MVCC高并发
• MYSQL5.5后支持全文索引，也是5.5后的默认搜索引擎

备份：备份的时候备份业务数据库和mysql数据库（因为mysql数据库中有用户及权限设置）

3.修改mysql的最大并发连接数

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> set global max_connections =2000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.00 sec)
# 也可在配置文件中修改
[root@server ~]# vim /etc/my.cnf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

4.服务器状态变量

服务器状态变量：分全局和会话两种
状态变量（只读）：用于保存mysqld运行中的统计数据变量，不可更改

show global status;
show [session] status;

5.服务器变量SQL_MODE

SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置
常见MODE:

• NO_AUTO_CREATE_USER： 禁止GRANT创建密码为空的用户
• NO_ZERO_DATE：在严格模式，不允许使用’0000-00-00’的时间
• ONLY_FULL_GROUP_BY： 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
• NO_BACKSLASH_ESCAPES： 反斜杠""作为普通字符而非转义字符
• PIPES_AS_CONCAT： 将"||"视为连接操作符而非"或"运算符

二、索引

1.index索引介绍

索引：索引是排序的快速查找的特殊数据结构，定义作为查找条件的字段，又称为key，索引通过存储引擎实现，加快查询速度

2.索引优缺点

索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的；联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。
索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了
优点

• 降低服务需要扫描的数据量，减少I/O次数
• 索引可以帮助服务器避免排序和使用临时表
• 索引可以帮助将随机I/O转为顺序 I/O

缺点：

• 占用额外空间
• 影响插入速度

索引类型：

• B+ TREE、HASH、R TREE、FULL TEXT
• 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
• 主键索引、二级（辅助）索引
• 稠密索引、稀疏索引：是否索引了每一个数据项
• 简单索引、组合索引: 是否是多个字段的索引
• 左前缀索引：取前面的字符做索引
• 覆盖索引：从索引中即可取出要查询的数据，性能高

3.B+TREE

B+tree索引：按顺序储存，每一个叶子节点到根节点的举例是相同的；左前缀索引，适合查询范围类的索引；
B-TREE

B+TREE

页面搜索严禁做模糊或者全模糊，如果需要择走搜索引擎来解决

说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引

可以使用B+TREE索引的查询类型

• 全值匹配：精确所有索引列
• 匹配最左前缀：只使用索引的第一列
• 匹配列前缀：只匹配一列值开头部分
• 精确匹配某一列范围并匹配另一列
• 只访问索引的查询

B+TREE索引的限制

• 如不从最左列开始，则无法使用索引
• 不能跳过索引中的列

4.索引优化

• 独立的使用列：经历避免其参与运算，独立的列指索引列不能是表达式的而一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不在列上进行运算
• 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，指定一个前缀长度
• 多列索引和索引顺序：AND操作时更适合使用多列索引，而非为每个列创建单独的索引；
  • 当出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引
  • 当出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回大量合并数据时，查询成本更高
• 选择合适的索引列顺序：无排序和分组时。将选择性最高放左侧
• 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有null值，此列在使用时也不会使用索引
• 对于经常在where子句使用的列，最好设置索引
• 对于有多个列where或者order by子句，应该建立复合索引
• 对于like语句，以%或者_开头的不会使用索引，以%结尾会使用索引
• 尽量不要使用not in 和<>操作，虽然可能使用索引，但性能不高
• 不要使用RLIK正则表达式会导致索引失效
• 查询时，能不用就不用，尽量写全字段名，比如select id，name,age from students;
• 大部分情况连接效率远大于子查询
• 在有大量记录的表分页时使用limit
• 对于经常使用的查询，可以开启查询缓存
• 多使用explain和profile分析查询语句
• 查看慢日志，找出执行时间长的sql语句优化

三、并发控制

1.锁机制

类型：

  - 读锁：共享锁，也成为S锁，只读不可写（包括当前事务），多个读互不阻塞
  - 写锁：独占锁，排它锁，也成为X锁，写锁会阻塞其他事务（不包括当前事务）的的读和写
  - S锁和S锁是兼容的，X锁和其他锁都不兼容
     - 例：事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突
1
2
3
4

锁粒度：

  - 表级锁：MyISAM
  - 行级锁：InnoDB
1
2

实现

  - 存储引擎：自行实现其锁策略和锁粒度
  - 服务器级：实现了锁、表级锁、用户可显示请求
1
2

分类：

  - 隐式锁：由于存储引擎自动施加锁
  - 显式锁：用户手动请求
1
2

锁策略：在锁粒度以及数据安全性寻求的平衡机制

四、事务

简介：事务Transactios：一组原子性的sql语句，或一个独立工作单元
事务日志：记录事务信息，实现undo，redo故障恢复功能

1.ACID特性：

• A：automictiy原子性：整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
• C：consistency一致性：数据库总是从一个状态转换为另一个一致性状态，类似于能量守恒定律
• I：isolation隔离性：一个事务所作出的操作是在提交之前，是不能为其他事务所见；隔离有多种隔离级别，实现并发（事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。）
• D：Durability持久性：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，所做的修改永久保存于数据库中，接下来即使数据库发生故障也不应该对其有任何影响，

begin
说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。
commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。
rollback ：回滚事务
将内存中，已执行过的操作，回滚回去

注意：只有事务型存储引擎中的DML语句方能支持此类操作
自动提交：
set autocommit={1|0} 默认未1，未0时设为非自动提交，为0可以提高数据库性能
死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
开始事务流程：

1、检查autocommit是否为关闭状态
select @@autocommit;
或者：
show variables like 'autocommit';

2、开启事务,并结束事务
begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
rollback;

begin
delete from student where name='alexsb';
update student set name='alexsb' where name='alex';
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含 3 个隐藏列

• db_row_id：隐藏的行 ID。在没有自定义主键也没有 Unique 键的情况下，会使用该隐藏列作为主键。
• db_trx_id：操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。
• dbroll_ptr：回滚指针，也就是指向这个记录的 Undo Log 信息。Undo Log 中存储了回滚需要的数据

2.事务的隔离级别

多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。
如果不考虑隔离性，可能会引发如下问题：

• 幻读 : 是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
  • 事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读。
• 不可重复读取 ：是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。
  • 在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据并修改数据。那么，在第一个事务的两次读数据之间。由于另一个事务的修改，那么第一个事务两次读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。
• 脏读：指一个事务读取了另外一个事务未提交的数据

# 这是非常危险的，假设Ａ向Ｂ转帐100元，对应sql语句如下所示
1. update account set money=money+100 where name=‘b’;    
2. update account set money=money-100 where name=‘a’;
1
2
3

数据库共定义了四种隔离级别：

• Serializable：可避免脏读、不可重复读、虚读情况的发生。（串行化）
• Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读）
• Read committed：可避免脏读情况发生（读已提交）。可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次
  读取数据不一致
• Read uncommitted：最低级别，以上情况均无法保证。(读未提交)

MVCC和事务的隔离级别：
MVCC（多版本并发控制机制）只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其
他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前
事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁