notebook mysql_noteBook/mysqlH.md at master · forks-library/noteBook · GitHub

高性能MySQL

一、mysql简介

1、mysql逻辑架构

最上层，客户端

连接处理、授权认证、安全

中间层，核心服务

查询解析、分析、优化、缓存、内置函数、存储引擎、触发器、视图

最下层，存储引擎

存储引擎负责mysql中数据的存储和提取

2、并发控制

每个客户端连接都会在服务器进程中拥有一个线程

MySQL会解析查询，并创建内部数据结构(解析树)，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引

读锁又叫共享锁，多用户可以同一个时刻读取同一个资源，而互不干扰

写锁又叫排它锁，同一个时刻只能有一个用户读写数据

表锁，开销最小的策略

行锁，最大程度支持并发处理，同时开销极大

3、事务

事务就是一组原子性的sql查询，事务内的语句，要么全部执行，要么全部执行失败

start transaction;

xxx

rollback;

commit;

ACID特性：

原子性 A

事务要不就在执行中，要不然就是成功或者失败的状态

一致性 C

执行事务过程中，所有对数据库写入的操作都应该是合法的，并不能产生不合法的数据状态

隔离性 I

一个事务结束前，对另一个事务是不可见的

持久性 D

一旦事务被提交，那么数据一定会被写入到数据库中并持久存储起来。

4、隔离级别

未提交读 READ-UNCOMMITTED RU

事务可以读取未提交的数据，会产生脏读

提交读 READ-COMMITTED RC

不可重复读，一个事务从开始到提交之前，所做的任何修改其他事物都是不可见的

可重复读 REPEATABLE-READ RR

mysql默认隔离级别，会产生幻读，意思是当事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录

可串行化 SERIALIZABLE

最高隔离级别

5、死锁

指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用资源，从而导致恶性循环的现象

简单解决办法：将持有最少行级排他锁的事务进行回滚

6、多版本并发控制 MVCC

可以认为MVCC是行级锁的一个变种，并且在很多情况下避免了加锁操作，因此开销更低

MVCC是通过保存数据在某个时间点的快照实现的

隐藏列，创建时间和过期时间，是系统版本号

7、存储引擎

show table status like 'users'\G

*************************** 1. row ***************************

Name: users

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 2

Create_time: 2019-03-06 22:15:42

Update_time: NULL

Check_time: NULL

Collation: utf8mb4_unicode_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.05 sec)

二、mysql测试和性能

吞吐量

响应时间或者延迟

并发性

可扩展性

。。。

三、mysql数据类型

1、选择优化的数据类型

更小的通常更好

简单更好

尽量避免NULL

2、整数类型

有符号范围：-2^(n-1)到2^(n-1)-1

无符号unsigned范围：0到2^(n-1+1)-1

注意： int(10)和int(1)只是规定了交互工具来显示�字符的个数，对于存储和计算来说，是一样的

类型

存储空间(字节)

tinyint

1

smallint

2

mediumint

3

int

4

bigint

8

3、实数类型

精确运算用decimal类型

类型

存储空间(字节)

decimal

9【小数点左边4字节，小数点1字节，小数点右边4字节】

float

4

double

8

4、字符串类型

类型

存储空间(字节)

varchar

n

char

n

blob

n

text

n

enum

n

5、日期和时间类型

from_unixtime()：将时间戳转换成日期

unix_timestamp()：将日期转换成时间戳

类型

存储空间(字节)

timestamp

4

datetime

8

6、范式与反范式

范式的优点：

更新操作更快

很少的重复数据，只需要修改更少的数据

表通常小，执行操作更快

很少冗余数据，更少需要distinct或者group by操作

范式的缺点：schema通常需要关联

反范式的优点：避免关联

四、mysql高性能索引

1、索引基础

索引是存储引擎用于快速找到记录的一种数据结构

索引类型：

B-Tree(技术上说B+Tree)

哈希索引

索引的优点：

大大减少了服务器需要扫描的数据量

可以帮助服务器避免排序和临时表

可以将随机I/O变为顺序I/O

2、高性能索引策略

独立的列

select a_id from xx where a_id + 1 = 5;

以上sql无法自动解析，所以不会走索引

前缀索引和索引选择性

select count(*) as cnt, left(city, 7) as pref from xxx group by pref order by cnt desc limit 10;

选择合适的索引长度

多列索引

索引合并

索引列顺序

最左前缀原则

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式

数据行实际上存放在索引的叶子页中，术语叫聚簇

索引列包含的是整数值

myisam主索引和次索引都指向物理行，比如id指向了物理行，由索引到磁盘拿数据(回行)

innodb在主索引行上直接存储行的数据，称为聚簇索引，次索引指向主索引，比如id行包括了name、age等等数据，name包括了id

索引覆盖：查找的字段正好是索引，速度快

延迟关联：让一部分数据走索引，比全表扫描好

五、mysql查询性能优化

六、mysql高级特性

七、参考资料