MySQL性能调优及架构_mysql性能调优与架构设计

1-1

 

第一范式(1NF)：字段所有属性不可再分

第二范式(2NF)：要求实体属性完全依赖于主关键字，改进：加入中间表

第三范式(3NF)：表中不包含传递关系，每个属性都和主键有直接关系而不是间接关系

1-2

简单数据可以冗余（反范式化设计）

常见的反范式化设计：

汇总：用户发信息次数 （用户消息表 一条一次）

设计：user表的count字段统计消息发送次数

报表

计数器设计（网站点击、用户朋友数、下载次数等等）

mysql修改的时候存在互斥锁，高并发怎么解决？

增加一个slot列（作为槽，分流，分散热点）

1-3

更小的字段，更少的磁盘、内存、CPU高速缓存，CPU的处理周期更少

数据量比较大的而且要求精度时，考虑使用BIGINT代替DECIMAL，确保精度，确保效率，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万,然后将结果存储在BIGINT里，这样可以同时避免浮点存储计算不精确和 DECIMAL精确计算代价高的问题。

1-4

BLOB和TEXT

二进制和字符方式存储

枚举的使用：

CREATE TABLE
enum_test(e ENUM(' fish', 'apple', 'dog') NOT NULL);

INSERT INTO
enum_test(e) VALUES(1),(2),(3);

MySQL能存储的最小时间粒度为秒。

datetime 存储日期范围：1001年~9999年

timestamp 存储日期范围：1970年~2038年,并且跟时区有关系。

可以使用BIGINT类型存储微秒级别的时间截，或者使用DOUBLE存储秒之后的小数部分

1-5

数据库、表、字段的命名要遵守可读性原则

例：表名：order_shipping

       字段名：is_delivered

       其中 1 表示是（已发货），0 表示否（未发货）

MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母

表名不使用复数名词，数据库、表、字段的命名禁用保留字，如desc、range、match之类

主键索引名为pk字段名；唯一索引名为uk字段名；普通索引名则为idx_字段名

1-6

为什么MySQL不用哈希索引：

        1、不能实现范围查找；

        2、order by 排序不支持；

        3、如果使用hash表，组合索引会将几个字段合并hash，没办法支持部分索引 ；

        4、数据量很大hash冲突的概率也会非常大

B+Tree

二分查找 - 平衡二叉树 - B树

对比算法效率：时间复杂度 == 次数

顺序查找：平均  （1+2+3+4+5+6+7+8+9+10)/10=5.5

二分查找：平均    (4+3+2+4+3+1+4+3+2+3)/10=2.9

最坏：顺序查找10，二分查找4

1-7

树：除了根节点外所有节点有且仅有一个父节点

二叉树：子树个数 最大为两个节点

二叉查找树：左子树所有值小于根节点的值，右子树大于或等于根节点的值

平衡二叉树(AVL树)：左右两个子树高度差的绝对值不超过1的二叉查找树

B+树：多叉平衡查找树

树模拟的工具

Data Structure Visualizationhttps://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B+树的特征：

        1、相同节点数量情况B+树高度远低于平衡二叉树；

        2、叶子结点存储数据，非叶子结点存储索引；

        3、叶子节点由小到大（有序）串联在一起，相邻的叶子节点之间用指针相连 ，叶子页中的数据是由小到大排好序的。

B树和B+树区别：

B树非叶子节点也存放数据，B+树相邻的叶子节点有链表的结构

B* 树的话，与B+树的差别就是在非叶子节点之间，也有相互的指针指向。

MySQL中实现的B+树，叶子节点之间的链表是双向链表

实际的B+树是单链表

Oracle中使用的是B*树

MySQL不用B树而使用B+树？

1、相同数据规模的情况下B树会增加io次数，而B+树，则数据量较小，一次可以返回多条记录，io次数较少

2、范围查询B+树明显优于B树

2-1

主键，InnoDB中使用了聚集索引，B+树

没有唯一性索引，MySQL也会创建一个隐含列RowID做主键，聚集索引

二级索引(辅助索引)，只包含本列，叶子节点还包含书签，书签中包括了主键

使用!=或<>操作符：这些操作符通常会使索引失效。

2-2

回表：辅助索引 -> 主键 -> 行记录，需要使用到2棵B+树，应该避免回表

查询优化器(SQL)：回表   vs   全表扫描

联合索引(复合索引)：包含多个列的索引

(note, b)二叉树：最左前缀法则

有一个联合索引(a, b, c)，当查询条件为a 或 a和b，或 a、b和c时，都可以有效地使用这个索引。但是如果查询条件只有b和c，那么这个索引就不会被使用

优化： SQL,减少索引的数量，联合(复合)索引

联合索引是针对查询条件优化的，而覆盖索引是针对查询结果优化的

实际上语法都一样，覆盖索引也具有联合索引的查询条件的最左前缀法则

CREATE INDEX idx_name ON users(first_name, last_name);

B+树索引总结

聚集索引 -- 主键 + 所有的列数据

辅助索引/二级索引 -- 索引列 + 主键    只包含当前列数据  

联合索引 -- 最左前缀法则  -> 要做到覆盖索引

2-3

B+树 ： 查询效率   B+树高度   生产    高度   3,4次   I/O

监控：热数据 索引  

Hash索引    一次

MySQL 5.7默认开启自适应哈希索引

/*
查看自适应哈希索引的使用情况
non- hash searches可以大概了解使用哈希索引后的效率
*/
show engine innodb status;
 
-- 禁用或启动此特性，默认AHI为开启状态
innodb_adaptive_hash_index;
 
 

哈希索引  只能  等值或者in的查询     而对于范围查找，排序，模糊，or是不能使用哈希索引的

or可以替换为UNION 或者 UNION ALL连接结果

2-4

MySQL一般查询语句只会用一个索引，会忽略别的索引

索引列的类型尽量小，索引列的选择，不重复的索引值占总记录的比值越高则查询效率越高

-- 表示计算左侧三个字符的唯一值数量和全部字符唯一值数量占总行数的比例
SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
COUNT(DISTINCT order_note)/COUNT(*) As total
FROM order_exp;

已经找到了合适的前缀长度，如何创建前缀索引:

ALTER TABLE order_exp ADD KEY (order_note(14));

MySQL不支持后缀索引，可以把字符串反转后存储，并基于此建立前缀索引

2-5

三星索引(高性能创建策略)：

第一颗星：窄索引片，将相关的记录放在一起

第二颗星：索引中的列可以帮助优化查询中的排序操作

第三颗星：索引中的列包含了所有查询中需要的列（比重50%）

使用前缀模糊查询或在查询中使用函数会使索引失效

总结：建立复合索引需要覆盖查询的字段，条件优先重复率最低的放在前面，排序尽量在B+树中刚好是顺序结构，则为三星索引

3-1

MySQL调优

慢查询:数据太多了

执行计划：sql前面加上EXPLAIN

type出现比较多的是system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref

3-2

查询优化器

高性能索引创建策略：索引列不用表达式，不用函数，最佳左前缀，范围条件放最后，查询结果覆盖索引，不等于慎用，or可以替换为union all，排序要覆盖索引，主键顺序插入 

"Extra"列显示"Using index"不需要回表。如果"Extra"列显示"Using where"或其他的值，需要回表

4-1 事务

原子性，一致性，隔离性，持久性

原子性：要么全部执行，要么全部失败

一致性：总和不变

隔离性：并发执行的各个事务之间不能互相干扰

持久性：事务提交，永久保存到数据库

脏读：事务b读取到未提交的修改的事务A

不可重复度：两次得到的结果不同 （修改，数据不一致）

幻读：后读读到了前次没有的数据（新增）

严重程度：脏读 >  不可重复度 > 幻读

4-2 事务隔离级别

未提交读，已提交读，可重复读，可串行化（加锁）

MySQL在REPEATABLE READ隔离级别下，是可以很大程度避免幻读

保存点

隐式提交

DDL  CREATE、ALTER、DROP 就会隐式的提交前边语句所属于的事务

4-3 MVCC(乐观锁)

快照读

undo日志（版本链）    ：   每一条记录有事务id(trx_id)，回滚指针(roll_ptr)

ReadView每次都生成只能解决脏读，只生成同一个则解决了不可重复读问题

MVCC 并不能完全禁止幻读（第一次读如果是空且在自己事务中进行了该条数据的修改）

5-1 MySQL中的锁

锁定读：读取当前版本，读取数据加锁，阻塞其他的事物同时修改相同的记录

共享锁（s锁）和独占锁（x锁）

锁的粒度：行锁，表锁

InnoDB提出了一种意向锁

非InnoDB都是表锁

IS锁、IX锁：意义 判断（防止遍历） 不能手动添加

索引上加的才是行锁，执行计划中确定真正使用了索引  加行锁

间隙锁实质上是对索引前后的间隙上锁，不对索引本身上锁

死锁：是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象

MySQL检测到了死锁，并结束了会话2中事务的执行，此时，切回会话1，发现原本阻塞的SQL语句执行完成了

6-1 MySQL8新特性

账户与安全

索引增强（隐藏、降序、函数索引）

CTE MySQL8.0开始支持通用表表达式(CTE）

with recursive staff_view(id,name,m_id) as
(select id ,name ,cast(id as char(200)) 
 from staff where m_id =0
 union ALL 
 select s2.id ,s2.name,concat(s1.m_id,'-',s2.id)
 from staff_view as s1 join  staff as s2
 on s1.id = s2.m_id
)
select * from staff_view order by id

MySQL 8.0支持窗口函数(Window Function)，也称分析函数。窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果。聚合窗口函数: SUM /AVG / COUNT /MAX/MIN等等

select year,country,product,sum,
			sum(sum) over (PARTITION by country) as country_sum,
			avg(sum) over (PARTITION by country) as country_avg
 from sales
order by country,year,product,sum;

JSON增强

MySQL :: MySQL 8.0 Reference Manual :: 11.5 The JSON Data Typehttps://dev.mysql.com/doc/refman/8.0/en/json.html

7-1 MySQL体系架构

MySQL是由连接池、管理工具和服务、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成

MySQL 5.5版本将InnoDB作为默认的存储引擎(优先)

MySQL中的数据目录    show variables like 'datadir';

8-1 MySQL中的系统库

performance_schema

主要保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。

查看最近执行失败的SQL语句

select * from events_statements_history where mysql_errno=1064\G

information_schema

保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些是一些描述性信息，称之为元数据。

sys

通过视图的形式把information_schema和performance_schema结合起来，让程序员可以更方便的了解MySQL服务器的一些性能信息。

mysql

主要存储了MySQL的用户账户和权限信息，还有一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

9-1 MySQL执行原理

Intersection索引合：某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集

什么时候使用？

1、二级索引列必须是等值匹配的情况

2、主键列可以是范围匹配

连接查询：驱动表只需要访问一次，被驱动表可能被访问多次

对于外连接，驱动表和被驱动表的关系就很重要，左外连接和右外连接的驱动表和被驱动表不能轻易互换

通常会选择被驱动表的数据加载进Join Buffer

join buffer 每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，可以显著减少被驱动表的I/O代价

9-2 MySQL的查询成本

I/O成本(1.0)：从磁盘到内存这个加载的过程损耗的时间

CPU成本(0.2)：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作

连接查询的成本计算公式是这样的：

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

10-1 InnoDB引擎底层解析

InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中

InnoDB的三大特性：

• 双写机制 （性能下降5%~10%）

• Buffer Pool

• 自适应Hash索引

这三种特性共同保证了 InnoDB 的数据完整性和高性能。

双写机制保证了数据的安全性，缓冲池和自适应哈希索引则通过减少磁盘 I/O 和加速数据访问，提高了数据库的性能

MySQL写数据页时，会写两遍到磁盘上，第一遍是写到doublewrite buffer，第二遍是写到真正的数据文件中。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个页数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了

以用表空间号 + 页号作为key，缓存页作为value创建一个哈希表，先从哈希表中根据表空间号 + 页号看看有没有对应的缓存页，如果有，直接使用该缓存页，如果没有，从free链表中选一个空闲的缓存页，然后把磁盘中对应的页加载到该缓存页的位置

LRU链表按照一定比例分成两截，分别是：

一部分存储使用频率非常高的缓存页，所以这一部分链表也叫做热数据，或者称young区域。

另一部分存储使用频率不是很高的缓存页，所以这一部分链表也叫做冷数据，或者称old区域

对于从磁盘上被加载到LRU链表的old区域的某个页来说，如果第一次和最后一次访问该页面的时间间隔小于1s（很明显在一次全表扫描的过程中，多次访问一个页面中的时间不会超过1s），那么该页是不会被加入到young区域的

当某些数据被频繁访问时，InnoDB引擎会尝试建立一个哈希索引，使得对这些数据的查找更加高效。比如，如果一个应用经常根据某个特定的ID来查询某张表的数据，InnoDB可能就会为该表的ID列建立一个哈希索引

11-1 事务底层与高可用原理

在InnoDB中是以页为单位来进行磁盘IO的，也就是说在该事务提交时不得不将一个完整的页面从内存中刷新到磁盘，一个页面默认是16KB大小，只修改一个字节就要刷新16KB的数据到磁盘上太浪费了

重做日志，英文名为redo log，也可以称之为redo日志。与在事务提交时将所有修改过的内存中的页面刷新到磁盘中相比，只将该事务执行过程中产生的redo日志刷新到磁盘

​​​​​​​redo日志占用的空间非常小，redo日志是顺序写入磁盘的

redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来

binlog 是用作人工恢复数据，redo log 是 MySQL 自己使用，用于保证在数据库崩溃时的事务持久性

redo log主要用于确保持久性，防止系统崩溃后数据丢失；而undo log主要用于实现原子性和隔离性，支持事务回滚和多版本并发控制