前言：什么是Mysql？
mysql是一种关系型数据库，建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。
架构
- 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
- 分析器： 先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
- 优化器： 按照 MySQL 认为最优的方案去执行。
- 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
- 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM等多种存储引擎(下文解释存储引擎)
查询语句的执行流程如下：权限校验—>分析器—>优化器—>权限校验—>执行器—>引擎
更新语句执行流程如下：分析器---->权限校验---->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit 状态)
1.数据库三大范式
1NF(第一范式)：属性不可再分,每个属性都是不可再分的原子项
2NF(第二范式)：2NF在1NF基础上增加了一个列，称为主键，非主键都要依赖于主键
3NF(第三范式)：3NF在2NF基础上，且不存在传递依赖（非主键和非主键之间不能有依赖关系），非主键必须直接依赖于主键。
2.MySql常用字段类型
2.1. 数值类型:
整型(INT(常用整型数据)、TINYINT(常用于布尔型01)、BIGINT(ID等信息))
浮点型(FLOAT、DOUBLE): 只能存储近似的小数值。
定点型（DECIMAL）:用于存储具有精度要求的小数(钱钱，对应java的BigDecimal)
2.2. 字符串类型
2.2.1最常用的
CHAR: 定长
VARCHAR：可变长度
2.2.2存储长文本
TEXT：0-65,535 字节 用于博客文章等
LONGTEXT：0-4,294,967,295 字节 更长的文章
3. 日期类型
DATETIME：没有时区信息，例如1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP: 和时区有关,例如1970-01-01 00:00:01 ~ 2037-12-31 23:59:59
3.存储引擎
Mysql的核心就是存储引擎(基于表的)，比较常用的就是MYISAM(5.5前)和INNODB了，在我们项目开发中都用INNODB，下面用一张表格来说明为什么要使用INNODB：
| MYISAM | INNODB | |
|---|---|---|
| 并发时锁粒度 | 表级锁 | 行级锁 |
| 是否支持外键 | × | √ |
| 事务支持 | × | √ |
| 恢复到崩溃前 | × | redolog |
| MVCC | × | √ |
| 性能 | 更强 |
INNODB完胜
4.日志
4.1. slow query log(慢查询日志)：
慢查询日志记录了执行时间超过long_query_time(默认是10s,通常设置为1s)的所有查询语句，在解决SQL慢查询(SQL执行时间过长)问题的时候经常会用到。找到慢sql后使用Explain+慢sql就可以对sql进行分析
4.2 redo log（重做日志）
redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。
4.3 binlog（归档日志）
只要发生了表数据更新，都会产生 binlog 日志。可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。
4.3 undo log（回滚日志）
我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。
5. 事务
5.1 事务四大特性(ACID)
原子性(通过undolog实现)。：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性(通过undolog、redolog、隔离性共同实现)：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性(通过加锁（当前读）&MVCC（快照读）实现。)：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性(通过redolog实现)：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
5.2并发事务带来的问题
5.2.1 脏读（Dirty read）：
一个事务读取数据并且对数据进行了修改，这个修改对其他事务来说是可见的，即使当前事务没有提交。这时另外一个事务读取了这个还未提交的数据，但第一个事务突然回滚，导致数据并没有被提交到数据库，那第二个事务读取到的就是脏数据
5.2.2不可重复读（Unrepeatable read）
指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改或删除导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
5.2.3幻读（Phantom read）
幻读与不可重复读类似。它发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
如何解决？
使用锁(悲观)
控制方式下会通过锁来显示控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。
- 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
- 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。
MVCC机制(乐观)
MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。
MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。
5.3SQL标准定义的事务隔离级别
SERIALIZABLE 隔离级别是通过锁来实现的，
READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | √ | √ | √ |
| 读已提交 | × | √ | √ |
| 可重复读(默认) | × | × | √ |
| 可串行化 | × | × | × |
6.锁
6.1表级锁和行级锁
表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。
行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高。
6.2共享锁和排他锁(读写锁)
共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。
7.索引
索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。
索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构
7.1为什么使用B+树？
-
更少的IO次数：B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B树多很多（即阶更大），因此B+树的高度更低，访问时所需要的IO次数少，
-
更适于范围查询：在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可。
-
更稳定的查询效率：B树的查询时间复杂度在1到树高之间（分别对应记录在根节点和叶节点)，而B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点。
对于一颗3层B+树，第一层（根节点）有1个页面，可以存储1000条记录；第二层有1000个页面，可以存储1000x1000条记录；第三层（叶节点）有1000x1000个页面，每个页面可以存储100条记录，因此可以存储1000x1000x100条记录，即1亿条。而对于二叉树，存储1亿条记录则需要26层左右。
7.2使用索引优缺点
优点：
- 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
缺点：
- 索引需要使用物理文件存储，也会耗费一定空间
- 创建和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
8.常用优化手段
8.1优化手段
- 避免使用select米 ：会消耗很多资源
- 连接表时候被连接的表应小于原表(最好避免连接)
- 尽量不做外键级联
- 合理使用索引：可以大大减小数据检索量
如何合理使用索引？
选择合适字段创建索引：不能为NULL、被频繁查询的字段、被作为条件查询的字段、频繁需要排序的字段、被经常频繁用于连接的字段
避免索引失效
使用select*、在索引列上做计算、使用like '%xx’百分号为首的模糊查询、使用or时有一个未加索引
8.2优化慢sql
为了优化慢 SQL ，我们首先要找到哪些 SQL 语执行速度比较慢
MySQL 慢查询日志是用来记录 MySQL 在执行命令中，响应时间超过预设值的 SQL 语句。因此，通过分析慢查询日志我们就可以找出执行速度比较慢的 SQL 语句。出于性能层面的考虑，慢查询日志功能默认是关闭的，你可以通过以下命令开启:
#开启慢查询日志功能
SET GLOBAL slow_query_log = 'ON';
#慢查询日志存放位置
SET GLOBAL slow_query_log_file = '/var/lib/mysql/ranking-list-slow.log';
#无论是否超时，未被索引的记录也会记录下来。
SET GLOBAL log_queries_not_using_indexes = 'ON';
# 慢查询阈值(秒)，SQL 执行超过这个值将被记录在日志中
SET SESSION long_query_time = 1;
#慢查询仅记录扫描行数大于此参数的 SQL
SET SESSION min_examined_row_limit = 100;
使用show variables like ‘slow%’;查看是否设置成功
实际项目中，慢查询日志通常会比较复杂，我们需要借助一些工具对其进行分析。像MySQL 内置的 mysqldumpslow工具就可以把相同的 SQL 归为一类，并统计出归类项的执行次数和每次执行的耗时等一系列对应的情况。
找到慢sql后使用Explain查询执行计划 Explain性能优化参数分析
常用参数配置
[mysqld]
#Mysql服务的唯一编号 每个mysql服务Id需唯一
server-id=1
#服务端口号 默认3306
port=3306
#mysql安装根目录（default /usr）
#basedir=/usr/local/mysql
#mysql数据文件所在位置
datadir=/var/lib/mysql
#pid
pid-file=/var/run/mysqld/mysqld.pid
#设置socke文件所在目录
socket=/var/lib/mysql/mysql.sock
#设置临时目录
#tmpdir=/tmp
# 用户
user=mysql
# 允许访问的IP网段
bind-address=0.0.0.0
# 跳过密码登录
#skip-grant-tables
#主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容
#skip-external-locking
#只能用IP地址检查客户端的登录，不用主机名
#skip_name_resolve=1
#事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
#transaction_isolation=READ-COMMITTED
#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server=utf8mb4
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server=utf8mb4_general_ci
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
#是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names=1
#最大连接数
max_connections=400
#最大错误连接数
max_connect_errors=1000
#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp=true
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet=128M
#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
#MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout=1800
wait_timeout=1800
#内部内存临时表的最大值 ，设置成128M。
#比如大数据量的group by ,order by时可能用到临时表，
#超过了这个值将写入磁盘，系统IO压力增大
tmp_table_size=134217728
max_heap_table_size=134217728
#禁用mysql的缓存查询结果集功能
#后期根据业务情况测试决定是否开启
#大部分情况下关闭下面两项
#query_cache_size = 0
#query_cache_type = 0
#数据库错误日志文件
#log-error=/var/log/mysqld.log
#慢查询sql日志设置
#slow_query_log=1
#slow_query_log_file=/var/log/mysqld_slow.log
#检查未使用到索引的sql
log_queries_not_using_indexes=1
#针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
log_throttle_queries_not_using_indexes=5
#作为从库时生效,从库复制中如何有慢sql也将被记录
log_slow_slave_statements=1
#慢查询执行的秒数，必须达到此值可被记录
long_query_time=8
#检索的行数必须达到此值才可被记为慢查询
min_examined_row_limit=100
#mysql binlog日志文件保存的过期时间，过期后自动删除
#expire_logs_days=5
binlog_expire_logs_seconds=604800
271
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
