mysql笔记

衡量指标

TPS: 每秒传输的事务处理的个数，这是指服务器每秒处理的事务数，支持事务的存储引擎如InnoDB特有的一个性能指标。

 

QPS:每秒查询处理量，同时适应于InnoDB和MyISAM存储引擎。

 

等待时间：执行SQL等待返回结果之间的等待时间

 

TPS = (COM_COMMIT + COM_ROLLBACK) / UPTIME

QPS = QUESTIONIS / UPTIME

 

MySqlSlap:

MySqlSlap是从MySQL的5.1.4版本开始就开始官方提供的压力测试工具

1. 创建schema,table,test date;
2. 运行负载测试，可以使用多个并发客户端连接；
3. 测试环境清理（删除创建的数据，表等，断开连接）

 

目的：测试mysql服务器瓶颈

 

参数：

 

MySqlSlap测试：

1000个客户端，重复10次自动生成sql语句，总共1000个查询：

 

 

./mysqlslap -u root -p --concurrency=1000 --iterations 10 -a --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=1000

运行结果：

 

1,50,100,200个客户端，每一个测试三次，并打印内存，cpu信息：

./mysqlslap -u root -p root --concurrency=1,50,100,200 --iterations=3 --number-char-cols=5 --number-int-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=innodb,myisam --create-schema=’test1’ --debug-info

运行结果：

注意：

--debug-info 在wind 环境会出现异常，打印不出cup信息

 

 

Mysql逻辑架构

 

连接层

服务层

引擎层

存储处

 

 

 

连接层：

当mysql启动（mysql服务器就是一个进程），等待客户端连接，每一个客户端连接请求，服务器都会新建一个线层处理（如果是线层池的话，则分配一个空的线层），每个线程独立，拥有各自的内存处理空间，但是，如果这个请求指示查询，没关系，但若是修改数据，很显然，当两个线程修改同一块内存是会引起数据同步问题。

SQL处理层

连接到服务器，服务器需要对其进行验证，也就是用户名，IP，密码验证，一旦连接成功，换需要验证是否具有执行某个特定查询的权限（如，是否允许客户端对某个数据库某个表的某个操作）

 

这一层主要功能有：sql语句解析，优化，缓存的查询，mysql内置函数的实现，跨存储引擎功能（所谓跨存储引擎就是说每个引擎都需要提供的功能（引擎需对外提供接口）），例如，存储过程，触发器，视图等。

1如果是查询语句（select语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样跳到下一步）

2解析查询，创建一个内部数据结果（解析树），这个解析树主要用来sql语句的语义与语法的解析；

3优化：优化sql语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等，这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改配置，达到最优化。这一阶段还涉及到储存引擎，优化器会询问存储引擎，比如某个操作的开销信息，是否对特定索引有查询优化等

 

优化：

当where x = null 或者 1 = 1 ，mysql会自动优化

 

存储引擎：

MyISAM

Innodb

Archive

Memory

Federated

 

查看引擎：

show engines;

 

查看默认引擎：

show variables like '%storage_engine';

 

存储引擎-MyISAM

Mysql5.5之前默认的存储引擎是MyISAM

 

MyISAM存储引擎由MYD和MYI组成

Frm:存储表结构，是任何存引擎都具备的

MYD:数据文件

MYI:索引文件

 

特性：

并发性与锁级别-表级锁

支持全文检索

支持数据压缩

 

 

适用场景：

非事务型应用（数据仓库，报表，日志数据）

只读类应用

空间类应用（空间函数，坐标，如gis地图相关的系统）

 

存储引擎-InnoDB

Mysql5.5以及以后版本默认支持的存储引擎

 

Innodb_file_per_table

ON:独立表空间：tablename.ibd 开启时，会产生.ibd文件，用于存放数据+索引

OFF:系统表空间：ibdatax

 

Mysql5.6以前默认系统表空间

 

系统表空间无法简单地收缩文件大小

独立表空间可以通过optimze table 收缩系统文件

系统表空间会产生IO瓶颈

独立表空间可以同时向多个文件刷新数据

 

建议使用独立表空间

 

特性：

Innodb是一种事务性存储引擎

完全支持事务的ACID特性

Redo Log 和 Undo Log：

Innodb 支持行级锁（并发程度高）

 

innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志：
1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

 

 

适用于大多数OLTP(联机事务处理)应用

 

Innodb和MySIAM特性对比：

 

存储引擎-CSV

 

组成：

数据以文本方式存储在文件

.csv：文件存储内容

.csm：文件存储表得元数据如表状态和数据量

.frm：表结构

 

特性：

以csv格式进行数据存储

所有列都不能为null

不支持索引（不适合大表，不适合在线处理）

可以对数据文件直接编辑（保存文本文件内容，如1,”aaa”,”bbb”）

 

存储引擎-Archive

 

组成：

以zlib对表数据进行压缩，磁盘I/o更少

数据存储在ARZ为后缀的文件中

 

特性：

只支持insert和selsect操作

只允许在自增ID列上加索引

 

 

存储引擎-Archive

 

使用场景：

日志和数据采集应用

 

存储引擎-Memory

 

文件系统存储特点也称HEAP存储引擎，所以数据保存在内存中

支持HASH索引和Btree索引

所有字段都是固定长度varchar(10) = char(10)

不支持Blog和Text等大字段

Memory存储引擎使用表级锁

最大大小有max_heap_table_size参数决定

 

使用场景

Hash索引用于查询或者是映射表（邮编和地区的对应表）

用于保存数据分析中产生的中间表

用户缓存周期性聚合数据的结果表

 

Memory数据易丢失，所以要求数据可再生

 

存储引擎-Ferderated

 

特点：

提供远程访问Mysql服务器上表的方法

本地不存储数据，数据全放到远程服务器上

本地需要保存表结构和远程服务器的连接信息

 

使用场景：

偶尔的统计分析以及手工查询

 

默认禁止，启用需要在启东时增加federated参数

 

 

 

锁

 

为什么需要锁：

在淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人下单，那么如何解决是你买到还是他买到的问题？

1我们先从库存表中提取物品数量

2然后插入订单险信息

3付款后插入付款信息

4然后更新商品数量

在整个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾

 

锁的概念：

锁是计算机协调多个进程或线程并发访问某一资源的机制

在数据库中，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁对于数据而言显得尤其重要，也更加复杂。

 

不同的存储引擎支持不同的锁机制

1MyISAM和Memory存储引擎采用的是表级锁；

Innodb存储引擎即机制行级锁，也机制表级锁，但默认情况下采用行级锁

 

表级锁：开销小，加锁快，并发度低，粒度大，但不会出现死锁；

行级锁：开销大，加锁慢，并发度高，粒度小，会出现死锁；

页面锁：介于表级锁和行级锁之间，会出现死锁；

 

MyISAM表锁

表共享读锁（table read lock）

表独占写锁（table write lock）

 

实践：

1. 执行lock table test_mysiam READ，然后启动另外一个session执行SELECT * from test_mysiam 可以查询
2. 执行

INSERT into test_mysiam VALUES(3,"李四");

UPDATE test_mysiam SET id=2 where `name` = "王五"

报错：Table 'test_mysiam' was locked with a READ lock and can't be updated

1. 在另外一个session中执行：

INSERT into test_mysiam VALUES(3,"李四");

会一直等待

1. 在同一个session中操作另外一张表报错：

SELECT * from test_mysiam_copy

INSERT into test_mysiam_copy VALUES(5,"李四");

1. 在另一个session中添加输入成功：

INSERT into test_mysiam_copy VALUES(6,"李四");

 

查看表被锁过多少次：

show STATUS LIKE 'table_locks_waited';

 

 

写锁：

1. 执行LOCK TABLE test_mysiam WRITE

在同一session中：

INSERT test_mysiam VALUES(7,"李白");

DELETE FROM test_mysiam WHERE id = 7;

select * from test_mysiam;

不会报错

1. 对不同的表操作（报错）

DELETE FROM test_mysiam_copy WHERE id = 3;

select * from test_mysiam_copy;

1. 在其他session中（等待）

select * from test_mysiam;

 

总结：

1对MyISAM表的读操作，不会堵塞其他用户对同一表的读请求，但会阻塞对同一个表的写请求

2当一个session加了读锁，这个session可以查询锁定表中的记录，但是更新或者访问其他表都会提示错误；

3另一个session可以查询表现中的数据，但是更新就会出现锁等待；

4对MyISAM表的写操作，则会阻塞其他用户对同一表的读写操作；

5对MyISAM表的写操作，当前session可以对本表CRUD,但是对其他表操作会报错；

 

 

InnoDb行锁

 

分享锁：又称读锁，当一个事务对几行上读锁时，允许其他事务对这几行上进行读操作，但不允许进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁。

 

排它锁：又称写锁，当一个事务对某几行上写锁时，不允许其他事务写，但允许读，更不允许其他事务给这几行上任何锁，包括写锁。

 

语法：

共享锁：lock in share mode

例如：SELECT * from test_innodb WHERE id = 2 LOCK in SHARE MODE

 

排它锁：for update

例如：SELECT * from test_innodb WHERE id = 2 for UPDATE

 

注意：

1两个事务不能锁同一个索引

1. insert、delete、update在事务中都会自动默认加上排它锁。
2. 行锁必须有索引才能实现，否则就会自动锁全表，那么就不是行锁了。

 

测试：

1

BEGIN

SELECT * FROM test_innodb WHERE id = 1 FOR UPDATE

在另外一个session中

UPDATE test_innodb set `name` = "王五" WHERE id = 2;  成功

UPDATE test_innodb set `name` = "王五" WHERE id = 2; 等待

 

2

BEGIN

UPDATE test_innodb set name = "黎明" WHERE id = 2

在另一个session中

UPDATE test_innodb set `name` = "王五" WHERE id = 2; 等待

 

3

BEGIN

UPDATE test_innodb set name = "王五" WHERE name = "王五"

在另一个session中

UPDATE test_innodb set `name` = "黎明" WHERE `name` = "王五"; 等待

 

Innodb表锁

和MyISAM差别不大

注意：开启一个事务的时候就锁全表

 

 

1. 先看行锁：

第一个sessio中

SELECT * from test_innodb WHERE id = 1 for UPDATE

第二个session中

SELECT * from test_innodb WHERE id = 1 LOCK in SHARE MODE

 

回到第一个session UNLOCK TABLES 并不会解锁

使用commit 或者begin 或者 rollback 才会解锁

 

2.再看表锁

lock TABLE test_innodb WRITE

使用COMMIT，ROLLBACK 并不会解锁

使用UNLOCK TABLES或者BEGIN 才会解锁

 

物理结构修改

问题：系统运行一段时间，数据量已经很大了，这时候系统升级，有张A表需要增加两个字段，白天和晚上并发量都很大，请问该怎么修改表结构？（难点：修改表结构会导致表锁，数据量大修改时间长，导致大量用户堵塞，无法访问）

 

解决思路：

1首先创建一个相同的表结构的空表；

2在新表修改表结构，然后copy原表中的数据到新表；

3在原表上创建一个触发器，在数据copy过程中，将原表的更新数据的操作全部跟新到新表中来；

4copy完成后，用rename table 新表替代原表，默认删除原表；

 

补充：

触发器：

简单地说，触发器就是一张表发生了某事件（插入，删除，更新操作），然后自动触发了预先编写好的若干条sql语句的执行。

 

特点：

触发事件的操作和触发器里面的sql语句是同一个事务操作，具有原子性，要么全部执行，要么都不执行。

 

作用：

1. 安全性，可以基于数据库的值使得用户具有操作数据库的某种权利；
2. 审计，可以跟踪用户对数据库的操作；
3. 实现复杂的非标准数据库相关完整性规则，可以对数据库中相关的表进行联欢更新，能够拒绝和回退那些相关完整性的变化，取消视图进行数据更新的事务，当插入一个与其主键不匹配的外部键时，这种触发器会起作用。
4. 同步实时地复制表中的数据；
5. 自动计算值，如果数据的值达到一定的要求，则进行特定的处理；

 

弊端：

1. 增加程序的复杂度，有些业务逻辑在代码中，有些业务逻辑用触发器处理，会使得后期维护变得困难；
2. 如果需要变动整个数据集而数据量又较大时，触发器效率会非常低；
3. 对于批量操作不适合使用触发器，使用触发器实现的业务逻辑在出现问题是很难定位，特别是涉及到多个触发的情况协同开发时，写业务代码如果不分清楚数据库触发细节，容易搞不清出发了哪些触发器，大量的使用触发器会导致代码结构容易被打乱，阅读源码困难；

 

物理结构修改工具：pt-online-schema-change

教程：https://segmentfault.com/a/1190000014924677?utm_source=tag-newest

 

 

 

 

事务

 

事务的特性：原子性，一致性，隔离性，持久性，这四个属性通常称为ACID特性

 

原子性：一个事务是一个不可分割的工作单位，要不全部执行，要么都不执行；

一致性：事物必须是使数据库从一个一致性状态变到另一个一致性状态，一致性和原子性是密切相关的。

隔离性：一个事务的执行不能被其他事务干扰；

持久性：一个事务一旦提交，它对数据库中数据的改变是永久性的。

 

Mysql事务隔离性级别：

未提交读（read uncommited）脏读

已提交读（read commited）不可重复读

可重复读（repeatable read）（默认）

可串行化（serializable）

 

查看隔离性级别：

show variables like '%tx_isolation%';

 

事务并发问题

脏读：

事务A读取了事务B更新的数据，然后B回滚了操作，那么A读取到的数据就是脏读；

不可重复读：

事务A多次读取同一数据，事务B在A多次读取的过程中对数据进行修改并提交，导致A多次读取数据不一致；

幻读：

A在B修改的过程中插入一条新的数据，但是B未发现新插入的数据，所以没有改过来，这叫幻读；

 

注意：

不可重复读侧重于修改，解决需要锁住满足条件的行；

幻读侧重于新增和删除，解决需要锁表；

 

未提交读（脏读）验证：

 

修改隔离级别

set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

开启事务

START TRANSACTION

update test_innodb set name = "未提交" where id = 1 （已改变）

在另外一个session中查询

SELECT * from test_innodb （已改变）

回到第一个session中回滚事务

rolllback

在另一个session中查询：

SELECT * from test_innodb （未改变）

 

已提交读（不可重复读）

 

修改隔离级别

set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

开启事务

START TRANSACTION

update test_innodb set name = "已提交" where id = 1 （已改变）

在另外一个session中查询

SELECT * from test_innodb （未改变）

回到第一个session中回滚事务

COMMIT

在另一个session中查询：

SELECT * from test_innodb （已改变）

 

可重复读

 

修改隔离级别

set SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

开启事务

START TRANSACTION

update test_innodb set name = "已提交" where id = 1 （已改变）

在另外一个session中查询

SELECT * from test_innodb （未改变）

回到第一个session中回滚事务

COMMIT

在另一个session中查询：

SELECT * from test_innodb （未改变）

 

 

 

可串行化

set SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

开启一个事务

BEGIN

SELECT * from test_innodb

 

在另外开启一个session

BEGIN

INSERT INTO test_innodb VALUE(11,'可串行化');  等待

 

回到第一个session commit 第二个才会执行插入

 

 

事务隔离级别为可重复读时，如果没有索引，更新数据时会锁住整张表；

事务隔离级别为串行化时，读写都会锁住整张表；

隔离级别越高，越能保证数据完整性和一致性，但对性能影响大；

所以优先考虑read committed 它能够避免脏读，而且有较好并发性能；

 

 

 

逻辑设计

 

数据库设计的第一大范式：

1. 数据表中的所有字段都只具有单一属性
2. 单一属性的列是由基本数据类型构成
3. 设计出来的表都是简单的二维表

 

数据库设计的第二大范式：

1. 要求表中具有单一的业务主键，也就是说不能存在非主键列只对部分主键依赖关系

 

数据库设计的第三大范式

1. 每一个非业务主键既不部分依赖也不传递依赖业务主键，也就是在第二范式基础上加入了非主键对主键的传递依赖

 

问题：

大量的关联非常影响查询的性能，完全符合范式化的设计有时并不能得到良好的sql查询性能

 

反范式化设计

1. 针对范式化而言得
2. 为了性能和读取效率的考虑，适当的对数据库设计范式进行违反
3. 允许存在少量的冗余，利用空间换时间；

 

所以不能完全按照范式的要求设计，要考虑以后如何使用表

 

总结：

 

范式化设计优点：

1. 减少数据冗余
2. 更新操作快
3. 空间小

 

缺点：

1. 关联查询耗性能
2. 很难更新索引优化

 

反范式设计优点：

1. 减少关联查询
2. 有利于索引优化

 

缺点：

1. 存在数据冗余及数据维护异常
2. 数据修改成本高

 

 

物理设计

1. 定义数据库、表及字段的命名规范
2. 选择合适的存储引擎
3. 选择合适的数据类型
4. 建立数据库结构

 

命名规范：

1. 遵守可读性原则，如驼峰，下划线
2. 表意性原则，如表名称能够体现数据内容
3. 长命名原则，尽可能少或者不使用缩写

 

选择存储引擎：

 

选择数据类型：

1. 优先考虑数字类型
2. 其次是日期，时间类型
3. 最后是字符类型
4. 对于相同级别的数据类型，应该优先考虑占用空间小的数据类型

 

浮点类型：

 

日期类型：

Timestamp与datetime：

Timestap和时区有关，datetime无关

 

慢查询

查询慢日志，就是查询慢的日志，指mysql记录所有执行超过long_query_time参数设定的时间阈值的sql语句日志。默认关闭，需要开启。

 

启动慢查询

常用配置：

Slow_query_log启动停止慢查询日志

Slow_query_log_file指定慢查询日志的存储路径文件（默认和数据文件放在一起）

Long_query_time指定记录慢查询日志sql 执行时间的阈值（单位：秒，默认10秒）

Log_queries_not_using_indexes是否记录未使用索引的sql

Log_output日志存放的地方【table】、[file]、[file、table]

 

语法：

set GLOBAL Slow_query_log = 1;

set GLOBAL Long_query_time =0;

 

 

记录符合条件的sql

1. 查询语句
2. 数据修改语句
3. 已经回滚的sql

 

 

慢查询分析工具（mysqldumpslow）

 

汇总出查询条件外其他相同的sql,并将分析结果按照参数中指定的顺序输出

 

语法：

Mysqldumpslow -s r -t 10 slow-mysql.log

-s order（c,t,l,r,at,al,ar）

C:总次数

T:总时间

L:锁的时间

R:总数据

At,al,ar:t,l,r平均数

-t top 置顶取前几条作为结果输出

 

 

慢查询分析工具（pt_query_digest）;直观

 

 

索引

 

Mysql官方对索引的定义为：索引是帮助mysql高效获取数据的数据结果。

即索引是数据结构。

 

Mysql默认存储引擎innodb只显示支持B-tree(从技术上来说是B+Tree)索引

 

索引分类：

1. 普通索引：即一个索引只包含单列，一个表可以有多个单列索引
2. 唯一索引：索引列的值必须唯一，允许有空值
3. 复合索引：即一个索引包含多个列
4. 聚集索引：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，innodb的聚集索引其实就是在同一个索引中保存了B+Tree索引和数据行
5. 非聚集索引：不是聚集索引就是非聚集索引；

 

基础语法：

查看索引：

SHOW INDEX from test_innodb

 

执行计划的作用：

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

 

执行计划包含的信息列表：

 

执行计划-id:

表示查询中执行select字句操作表的顺序

 

三种情况：

1. id相同，执行顺序有上至下
2. id不同，如果是子查询，id序号会递增，id值越大优先级越高，越优被执行；
3. id相同不同，同时存在；

 

执行计划-select_type

查询类型：普通查询，联合查询子查询等符合查询

 

 

执行计划-type（重点）

记录访问的类型，是较为重要的指标，结果值从最好到最坏的依据是：

System>const>eq_ref>ref>fulltest>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

 

需要记忆的：

System>const>eq_ref>ref>range>index>all

 

1. System

表只有一行记录（等于系统表），这是const类型的特例

 

1. Const

标识通过索引一次就能找到了

Const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快

如将主键置于where列表中，mysql就能将该查询转换为一个常量

 

1. eq_ref

唯一性索引扫描，对于每个索引建，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描

 

1. Ref

非唯一性索引扫描，返回匹配某个单独值的所有行

 

1. Range

只检索给定范围的行，使用一个索引类选择所行。Key 列显示使用了哪个索引；

一般就是在where语句中出现between，<，>,in等的查询

 

1. All

将遍历全表扫描

 

 

执行计划-possible_keys

实际使用的索引，如果未null，则没有使用索引

查询中若使用了覆盖索引，则该索引和查询的select字段重叠

 

执行计划-key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。（注意：是索引字段的最大可能长度，不是实际长度）

 

根据这个值可以判断索引的使用情况，特别是组合索引的时候，判断所有的索引字段是否都被查到；

 

与编码有关，latin1占1个字节，gbk占用2个字节，utf8占用三个字节

 

整数、浮点数，时间类型的索引长度

 

Not null = 字段本身的字段长度

Null = 字段本身长度 + 1（是否为空标志占一个字节）

 

Datetime类型在5.6中字段长度是5个字节，在5.5中是8个字节

 

Varchar索引长度计算的时候需要加2，因为可变字段需要加2个字节

 

复合索引有最左前缀的特性

 

执行计划-ref

显示缩印的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查询索引的值。

 

执行计划-rows

根据表统计信息及索引选用情况，大致估算出找到的记录需要读取的行数

 

执行计划-extra

包含不适合在其他列中显示但十分重要的额外信息

 

覆盖索引：查询列被索引覆盖

 

 

 

优化

 

1. 尽量全值匹配
2. 最佳左前缀法则
3. 不要再索引列上做任何操作（计算，函数，类型转换，or等，会导致索引失效）
4. 范围条件放最后
5. 覆盖索引尽量使用（少用select *）
6. 不等于要慎用（！=，<>的时候会导致全表）
7. Null、not有影响
8. Like查询要当心
9. 字符串类型不加引用（失效）
10. Or改union效率高

 

 

Insert语句优化

1. 提交前关闭自动提交
2. 尽量使用批量insert语句提交
3. 使用MyISAM存储引擎

 

使用Load data infile 比一般insert语句块20倍