第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
lQuery Profiler是MYSQL自带的一种query诊断分析工具,通过它可以分析出一条SQL语句的性能瓶颈在什么地方。(通常需要手动开启)
lshow profile和show profiles语句可以展示当前会话(退出session后,profiling重置为0) 中执行语句的资源使用情况.
lshow 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进行启动。