第3章 Mysql架构和索引及性能优化
3.1 Mysql架构
1.逻辑架构图一:
2.逻辑架构图二:
3.执行流程图:
3.2 存储引擎介绍:
* 多存储引擎是mysql有别于其他数据库的一大特性;
* 存储引擎是针对表的
* MySQL 5.5之后，默认的存储引擎由MyISAM变为InnoDB。
* 查看存储引擎：show engines;
|
Innodb |
Myisam |
|
|
存储文件 |
.frm 表定义文件 .ibd 数据文件 |
.frm 表定义文件 .myd 数据文件 .myi 索引文件 |
|
锁 |
表锁、行锁 |
表锁 |
|
事务 |
ACID |
不支持 |
|
CRDU |
读、写 |
读多 |
|
count |
扫表 |
专门存储的地方 |
|
索引结构 |
B+ Tree |
B+ Tree |
3.2.1 索引的存储结构:
-
B Tree和B+ Tree的特点与区别
* 树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。
* 如果是三层树结构---支撑的数据可以达到20G，如果是四层树结构---支撑的数据可以达到几十T
* B Tree和B+ Tree的最大区别在于非叶子节点是否存储数据的问题。B Tree是非叶子节点和叶子节点都会存储数据。而B+ Tree只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是由顺序的。
-
非聚集索引
* 叶子节点只会存储数据行的指针，简单来说数据和索引不在一起，就是非聚集索引。
* 主键索引和辅助索引都会存储指针的值
-
聚集索引（InnoDB）
* 主键索引（聚集索引）的叶子节点会存储数据行，也就是说数据和索引是在一起，这就是聚集索引。
* 辅助索引只会存储主键值
* 如果没有没有主键，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定规则创建聚集索引。
3.3 Mysql性能优化之慢查询
MySQL 数据库有一个“慢查询日志”功能,
MySQL的慢查询日志功能，默认是关闭的，需要手动开启。
查看是否开启慢查询功能,slow_query_log_file=OFF则没有开启,若为ON则为开启;
临时开启慢查询功能:
在 MySQL 执行 SQL 语句设置，但是如果重启 MySQL 的话将失效
mysql>set global slow_query_log = ON;
mysql>set global long_query_time = 1;
永久开启慢查询功能:
修改/etc/my.cnf配置文件，重启 MySQL, 这种永久生效.
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
慢日志格式:
mysql>select sleep(3);
3.4 Mysql性能分析语句show profile
l Query Profiler是MYSQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。(通常需要手动开启)
l show profile 和 show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行语句的资源使用情况.
l show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况.显示的记录数由变量:profiling_history_size 控制,默认15条
查看profile是否开启,OFF(关闭),ON(开启)
* select @@profiling;
* show variables like ‘%profil%’;
开启:set profiling=1;
3.4.1Mysql性能优化细节
3.5 Mysql 事务处理
3.5.1 事务概述
* 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
* 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
* 事务用来管理DDL、DML、DCL操作，比如 insert,update,delete 语句
[一般来说，事务是必须满足4个条件（ACID）：
Atomicity（原子性）:构成事务的的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行
Consistency（稳定性、一致性）:数据库在事务执行前后状态都必须是稳定的
Isolation（隔离性）:事务之间不会相互影响
Durability（可靠性、持久性）:事务执行成功后必须全部写入磁盘]
3.5.2事务并发问题:
在事务的并发操作中可能会出现一些问题：
1.脏读：一个事务读取到另一个事务未提交的数据。
2.不可重复读：一个事务因读取到另一个事务已提交的数据。导致对同一条记录读取两次以上的结果不一致。update操作
3.幻读：一个事务因读取到另一个事务已提交的数据。导致对同一张表读取两次以上的结果不一致。insert、delete操作
3.5.3 事务隔离级别
为了避免上面出现的几种情况，在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同
四种隔离级别：
现在来看看MySQL数据库为我们提供的四种隔离级别（由低到高）：
① Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
② Read committed (读已提交)：可避免脏读的发生。
③ Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
④ Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
默认隔离级别
大多数数据库的默认隔离级别是Read committed，比如Oracle、DB2等。
MySQL数据库的默认隔离级别是Repeatable read。
l注意事项：
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。
对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。
3.6 Mysql锁
锁就是为了保证一些事务是安全的
行级锁定（row-level）:开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低()
表级锁定（table-level）:开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
表级锁的两种模式:
表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
table_locks_immediate：产生表级锁定的次数；
* table_locks_waited：出现表级锁定争用而发生等待的次数；
手动增加表锁
lock table 表名称 read(write),表名称2 read(write)，其他;
查看表锁情况
show open tables;
删除表锁
unlock tables;
页级锁定（page-level）:开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
3.7 集群搭建和主从复制
主服务器配置:
1.修改my.conf文件:
#启用二进制日志
log-bin=mysql-bin
#服务器唯一ID，一般取IP最后一段
server-id=133
2.重启Mysql服务:
service mysqld restart
3.建立帐户并授权slave:
mysql>GRANT FILE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'backup'@'%' identified by '123456';
4.查询master的状态:
mysql> show master status;
3.8 集群分区和读写分离
MySQL-Proxy下载
MySQL-Proxy安装
l创建mysql-proxy.cnf文件
修改rw-splitting.lua脚本
MySQL-Proxy启动域测试
mysql-proxy --defaults-file=mysql-proxy.cnf配置文件的地址
注意事项：如果没有配置profile文件的环境变量，则需要去拥有mysql-proxy命令的目录通过./mysql-proxy进行启动。