MySQL 小览

MySQL介绍

什么是MySQL?

一个轻量级的关系型数据库

为什么使用MySQL?

开源免费,高性能,稳定性高,易维护

MySQL基础架构

在这里插入图片描述

查询执行计划

在这里插入图片描述

常用存储引擎

在这里插入图片描述

myisam

表锁 不支持事务 非簇表

在这里插入图片描述

innodb

行锁 支持4种隔离级别事务(MVCC) 簇表

在这里插入图片描述

archive

zlib压缩 只支持SELECT和INSERT

基础概念

MySQL 数据库，物理存在的操作系统文件集合

MySQL Server，管理MySQL数据的系统

MySQL 实例，MySQL进程以及其持有的内存结构,IP:PORT

SQL (Structured Query Languange)

DDL(Data Defination Language)

create table xxx alter table xxx drop table xxx

online DDL

1. MySQL 5.6以上支持在线DDL,但字符串长度在256前后变更,导致写锁
2. 请让DBA使用percona-toolkit(PT)执行
3. 请在低峰执行

DML(Data Manipulation Language)

CURD语句: Replace into xxx Insert Ignore into xxx

InnoDB索引

B+树

聚簇索引 优点:主键范围扫描 缺点:更新成本相对较高

索引场景

1. 前导列(最左前缀原则)
2. 索引列上范围查找
3. 复合索引

Explain

EXPLAIN SELECT XXXX

select employees.* from employees where emp_no in 
    (select emp_no from dept_emp where dept_no = 'd001') ;

在这里插入图片描述

show warngings 可以查询优化器优化之后的SQL

id: 执行顺序(ID相同,从上往下) type: ALL->index->range->ref->eq_ref->const,system->null rows: 预估需要扫描的行数 possible keys:可能使用的索引 key:实际使用的索引

自增主键

auto_increment_offset 偏移量 auto_increment_increment 自增值 (默认6字节整形)

简单全局唯一ID

create table sequence(id int unsigned not null);
insert into sequence values(0);
update sequence set id = last_insert_id(id + 1);
select last_insert_id();

字符集

统一设置UTF8/UTF8MB4(MySQL 5.5+)

show character set;

校对规则 默认: utf8_general_ci 不区分大小写 utf8_bin:区分大小写(select * from test where region='cn' collate utf8_bin;)

设计规范

1. 自增ID主键
2. 设置default not null
3. 必须设置默认值
4. 软删除代替硬删除
5. 使用update_time on update current_timestamp
6. 字段注释
7. 避免表名过长(部分DBA工具限制)

安全

权限: 申请适当权限(CURD)

grant select , update , delete on employees
    to test_rw@'127.0.0.1'  identified by 'test123' ;

SQL注入: 使用占位参数化查询

慢查询

开启慢查询

mysql> show variables like 'slow_query%' ;
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /usr/local/mysql/data/slow-query.log |
+---------------------+--------------------------------------+

mysql> show variables like 'long_query_time' ;
+-----------------+----------+
| long_query_time | 0.500000 |
+-----------------+----------+

分析慢查询

# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
# Query_time: 2.397048  Lock_time: 0.001538 Rows_sent: 1  Rows_examined: 10810030
SET timestamp=1566225900;
select count(*) from employees;
# User@Host: test_rw[test_rw] @ localhost [127.0.0.1]  Id:    17
# Query_time: 2.410302  Lock_time: 0.000069 Rows_sent: 2188111  Rows_examined: 2188111
SET timestamp=1566230367;
select *from employees;

使用mysqldumpslow分析慢查询

查询时间最长:mysqldumpslow -s t -t 1 /usr/local/mysql/data/slow-query.log

在这里插入图片描述

事务和锁

事务的ACID

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

隔离级别

1. read uncommitted
2. read committed
3. repeatable read
4. serializable

多版本并发控制(MVCC)

1. InnoDB multiversion concurrency control (MVCC)
2. 快照读
3. 版本链
4. read view

InnoDB锁

表锁

1.读锁: lock tables [table _name] read; 2. 表锁:lock tables [table_name] write; 3.全局: flush tables with read lock; 4.释放锁:unlock tables;

行锁

1.记录锁(record lock) 2. 间隙锁(gap lock) 3. next key锁

死锁

1.禁止抢占 2.持有和等待 3.互斥 4循环等待

MySQL监控

参数和运行状态

1. show variables like ‘%参数%’;
2. show full processlist;
3. show engine innodb status

性能

1. show global status like ‘com_%’
2. show global status like ‘select%’
3. information_schema.tables

常见问题

too many connections

1. 查看当前连接数 show global variables like ‘Threads_connected’;
2. show full processlist ;
3. 修改最大连接数max_connections

MySQL has gone away

1. show variables like ‘wait_timeout’ ;