MySQL 之 架构和性能优化

kk起个名吧
137 阅读12分钟

一、 存储引擎

1. 存储引擎概述

存储引擎是MySQL数据库中的一个组件，负责执行实际的数据I/O操作，工作在文件系统之上，数据库的数据会先传到存储引擎，在按照存储引擎的格式，保存到文件系统。

常用的存储引擎：InnoDB 、MyISAM

2. MyISAM 和 InnoDB 对比

  • storage limits（存储上限）
    • myisam 256TB
    • innodb 64TB （但是mysql 根本达不到这么大的数据量）
  • transactions（事务）
    • myisam 不支持
    • innodb 支持
  • locking granularity（锁级别）
    • myiasam 表级
    • innodb 行级
  • 提高mvcc（多版本的并发控制）
    • myiasam 不支持
    • innodb 支持 （提高数据库的并发性）
  • data caches （数据缓存）
    • myiasam 不支持
    • innodb 支持
  • foreign key （外键）
    • myiasam 不支持
    • innodb 支持
对比MyISAMInnoDB
存储限制256TB64TB
事务不支持支持
锁定方式表级锁定行级锁定
mvcc不支持支持
数据缓存不支持支持
外键约束不支持支持
全文索引支持不支持
读写性能只支持单独的查询与插入，读写阻塞读写和事务并发能力较好
硬件硬件资源占用较小缓存能力较好，可以减少磁盘IO的压力
存储文件数据文件和索引文件分开存储，存储为三个文件（.frm的表结构文件，.MYD的数据文件，.MYI的索引文件）数据文件也是索引文件，存储成两个文件(.frm的表结构文件，.ibd的数据和索引文件)
使用场景适用不需要事务支持，单独的查询和插入的业务场景适用于需要事务支持，一致性要求比较高，数据会频繁更新，读写并发高的业务场景

2.1 MyISAM 引擎特点

  • 不支持事务
  • 表级锁定
  • 读写相互阻塞，写入不能读，读时不能写
  • 只缓存索引
  • 不支持外键约束
  • 不支持聚簇索引
  • 读取数据较快，占用资源较少
  • 不支持MVCC（多版本并发控制机制）高并发
  • 崩溃恢复性较差
  • MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景

  • 只读（或者写较少）
  • 表较小（可以接受长时间进行修复操作）

MyISAM 引擎文件

  • tbl_name.frm 表格式定义 （表格属性 字段 数据类型）
  • tbl_name.MYD 数据文件 （数据 myisam data）
  • tbl_name.MYI 索引文件 （索引 目录和文章 分开储存）

2.2 InnoDB 引擎特点

  • 行级锁
  • 支持事务，适合处理大量短期事务
  • 读写阻塞与事务隔离级别相关
  • 可缓存数据和索引
  • 支持聚簇索引
  • 崩溃恢复性更好
  • 支持MVCC高并发
  • 从MySQL5.5后支持全文索引
  • 从MySQL5.5.5开始为默认的数据库引擎

3. 管理存储引擎

  • 查看mysql支持的存储引擎
show engines;
查看引擎.png
  • 查看当前默认的存储引擎
show variables like '%storage_engine%';
查看默认引擎.png
  • 设置默认的存储引擎
vim /etc/my.cnf
[mysqld]
default_storage_engine = InnoDB
//默认的就是 InnoDB ，一般不做修改

二、 MySQL 中的系统数据库

  • mysql 数据库

是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

  • information_schema 数据库

MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与"数据字典"，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

  • performance_schema 数据库

MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

  • sys 数据库

MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况

三、 服务器配置和状态

1. 服务器选项

2. 服务器变量

3. 状态变量

四、 索引

1. 索引介绍

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

索引的概念

  • 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。
  • 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
  • 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
  • 索引是表中一列或者若干列值排序的方法。
  • 建立索引的目的是加快对表中记录的查找或排序。

索引的作用优点：

  • 加快查询速度，提高数据库性能
  • 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因
  • 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。避免排序和使用临时表
  • 可以降低数据库的IO成本（减少io次数），并且索引还可以降低数据库的排序成本。将随机I/O转为顺序I/O
  • 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性
  • 可以加快表与表之间的连接
  • 在使用分组和排序时，可大大减少分组和排序的时间
  • 建立索引在搜索和恢复数据库中的数据时能显著提高性能

缺点：

  • 占用额外的磁盘空间，影响插入速度 ，占用磁盘空间

索引类型：

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

2. 索引结构

  • 二叉树
  • 红黑树
  • B树
  • B+树

3. 索引管理

3.1 查看索引

show index from students;
查看索引.png

3.2 建立索引

语法：

create index 索引名 on 表名 (列名[(length)]);

示例：

create index idx_name on students (name(5)); //以name为字段建立索引
建立索引.png

3.3 删除索引

语法：

drop index 索引名 on 表名;

示例：

drop index idx_name on students;
删除索引.png

3.4 explain 工具

以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询

语法：

EXPLAIN SELECT clause

示例：

explain select * from students where name='xu xian';  //可以看到使用了索引
explain.png

4. 索引优化

  • 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）
  • 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
  • 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
  • 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
  • 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
  • 对于经常在where子句使用的列，最好设置索引
  • 对于有多个列where或者order by子句，应该建立复合索引
  • 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
  • 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
  • 不要使用RLIKE正则表达式会导致索引失效
  • 查询时，能不要就不用，尽量写全字段名，比如: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 锁的释放，此种情况也称为锁冲突

锁粒度：

  • 表级锁：MyISAM 存储引擎 mysql
  • 行级锁：InnoDB

实现：

  • 存储引擎：自行实现其锁策略和锁粒度
  • 服务器级：实现了锁，表级锁，用户可显式请求

分类：

  • 隐式锁：由存储引擎自动施加锁
  • 显式锁：用户手动请求

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

1.1 手动加锁

人为加锁比较少见，一般在备份的时候会加锁，备份期间数据一直在改，可能备份时与备份的内容不一致

语法：

lock tables 表名 type；

示例：

lock tables students  read;   //给students表加读锁，只可以查不能修改
加锁.png

1.2 解锁

unlock tables;   //释放锁

1.3 全局加锁

通常在备份前加全局读锁

flush tables  with  read  lock;   //整个数据库加锁

2. 事务

2.1 事务的概念

  • 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
  • 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
  • 事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
  • 事务通过事务的整体性以保证数据的一致性。
  • 事务能够提高在向表中更新和插入信息期间的可靠性。

2.2 ACID特性

  • A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么失败后回滚
  • C：consistency 一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于质量守恒定律(A1wB 0 A1w 给 B转1w 始终保持A+B=1w)
  • I： Isolation 隔离性；一个事务所做出的操作在提交之前，能不能为其他事务所见，和隔离级别有关系，4个隔离级别
  • D：durability 持久性；一旦事务提交，其所做的修改会永久保存于数据库中
begin     //开启事务
commit    //提交，相当于vi中的wq保存退出
rollback  //回滚，相当于vi中的q!不保存退出

2.3 事务隔离级别

MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格

  • READ UNCOMMITTED 未提交可读

    可读取到未提交数据，产生 脏读 （隔离性最差）

  • READ COMMITTED 提交可读

    可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致可读

  • REPEATABLE READ 幻读

    可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置

  • SERIALIZABLE 串读

    可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞其它事务的读写（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

avatar
文章
阅读
粉丝