导图社区 三.mysql
存储引擎,sql优化,主从部署
编辑于2020-07-05 01:00:20三.mysql
1.安装配置
环境准备
上传mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz
依赖安装
yum -y install perl
yum -y install perl-devel
yum -y install 'perl(Data::Dumper)'
安装5.6.10
解压
tar -zxvf mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz
初始化安装
创建目录
初始化
cd /usr/local/mysql/scripts
./mysql_install_db --user=root --basedir=/usr/local/mysql --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid --tmpdir=/tmp --explicit_defaults_for_timestamp=true
配置my.cnf文件
vi /etc/my.cnf basedir = /usr/local/mysql datadir = /data/mysql socket = /tmp/mysql.sock log-error = /data/mysql/error.log pid-file = /data/mysql/mysql.pid user = root tmpdir = /tmp
修改环境变量
vi /etc/profile export MYSQL_HOME=/usr/local/mysql export PATH=$MYSQL_HOME/bin:$PATH source /etc/profile
启动mysql
service mysql start
登录mysql, 修改root密码
mysql>use mysql; mysql>update user set password=password('root') where user='root' and host='localhost'; mysql>flush privileges;
允许远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
flush privileges;
关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
多实例安装
解压缩到不同的目录,比如mysql3306,mysql3307,mysql3308
basedir = /home/mysql/mysql3306 datadir = /home/mysql/mysql3306/data port = 3306 server_id = 1
5.6升级5.7
启动命令
关闭命令
安装5.7.9
准备工作
单实例安装
shell> bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up
shell> bin/mysql_ssl_rsa_setup # MySQL 5.7.6 and up
shell> bin/mysqld_safe --user=mysql &
多实例安装
[mysqld1] server-id = 11
[mysqld2] server-id = 12 socket = /tmp/mysql.sock2
连接数配置
show variables like '%max_connections%';
3.mysql进阶
授权-用户标识
用户+IP
权限表
User的一行记录代表一个用户标识
db的一行记录代表对数据库的权限
table_priv的一行记录代表对表的权限
column_priv的一行记录代表对某一列的权限
数据类型
Int
TinyInt
1字节
SmallInt
2字节
MediumInt
3字节
Int
4字节
BigInt
8字节
Char,Varchar
binary,varbinary
tinyblob,blob,mediumblob,longblob
tinytext,text,mediumtext,longtext
日期
Datetime
8字节
与时区无关
Date
3字节
Timestamp
4字节
与时区有关
year
1字节
time
3字节
JSON类型
json_extract抽取
json_object-将对象转json
json_merge
5.锁和事务
锁
锁是计算机协调多个进程或线程并发访问某一资源的机制
mysql锁机制-最显著的特点是不同的存储引擎支持不同的锁机制
表级锁
更适合于以查询为主,只有少量按索引条件更新数据的应用,如OLAP系统
行级锁
适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用
如一些在线事务处理(OLTP)系统
MySQL的表级锁 有两种模式
表共享读锁(Table Read Lock)
lock table 表名 read
表独占写锁(Table Write Lock)
lock table 表名 write
总结
读操作,不会阻塞其他用户对同一表的读请求
读操作,不会阻塞当前session对表读,当对表进行修改会报错
lock read: 可以查询锁定表中的记录,但更新或访问其他表都会提示错误
写操作,则会阻塞其他用户对同一表的读和写操作
写操作,当前session可以对本表做CRUD,但对其他表进行操作会报错
InnoDB引擎 支持行锁
共享锁又称:读锁
select * from 表 where 条件 lock in share mode
排它锁又称:写锁
select * from 表 where 条件 for update
注意
1.两个事务不能锁同一个索引。
2.insert ,delete , update在事务中都会自动默认加上排它锁。
3.行锁必须有索引才能实现,否则会自动锁全表,那么就不是行锁了。
事务
协调一个线程并发访问多个资源的机制
cmd
show engines;
show variables like '%storage_engine%';
Create table .... type=InnoDB; Alter table table_name type=InnoDB;
4个属性:原子性、一致性、隔离性、持久性
隔离级别:
未提交读(READ UNCOMMITED)脏读
set SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
已提交读 (READ COMMITED)不可重复读
可重复读(REPEATABLE READ)
幻读
不可重复读侧重于修改,幻读侧重于新增或删除
解决不可重复读的问题只需锁住满足条件的行, 解决幻读需要锁表
默认的事务隔离级别
可串行化(SERIALIZABLE)
总结
事务隔离级别为可重复读时, 如果有索引(包括主键索引)的时候,以索引列为条件更新数据, 会存在间隙锁间、行锁、页锁的问题,从而锁住一些行; 如果没有索引,更新数据时会锁住整张表
语法
开启
1、begin
2、START TRANSACTION(推荐)
3、begin work
提交
commit
回滚
rollback
还原点
savepoint
7.慢查询分析
慢查询配置
slow_query_log 启动停止技术慢查询日志
slow_query_log_file 指定慢查询日志得存储路径及文件
long_query_time 指定记录慢查询日志SQL执行时间得伐值 (单位:秒,默认10秒)
log_queries_not_using_indexes 是否记录未使用索引的SQL
慢查询分析
工具1:mysqldumpslow
汇总除查询条件外其他完全相同的SQL, 并将分析结果按照参数中所指定的顺序输出
mysqldumpslow -s r -t 10 slow-mysql.log
-t top 指定取前面几天作为结果输出
-s order (c,t,l,r,at,al,ar) c:总次数 t:总时间 l:锁的时间 r:总数据行 at,al,ar :t,l,r平均数 【例如:at = 总时间/总次数】
工具2:pt_query_digest
pt-query-digest --explain h=127.0.0.1, u=root,p=password slow-mysql.log
汇总的信息【总的查询时间】、【总的锁定时间】、 【总的获取数据量】、【扫描的数据量】、【查询大小】
9.sql优化
优化实战
1.尽量全值匹配
2.最佳左前缀法则
复合索引
3.不在索引列上做任何操作
4.范围条件放最后
5.覆盖索引尽量用
减少select *
6.不等于要甚用
无法使用索引会导致全表扫描
7.Null/Not 有影响
有Not-null约束
is null
不可达
is not null
全表扫描
无Not-null约束
is null
使用索引
is not null
全表扫描???
8.Like查询要当心
尽量左侧不用%
9.字符类型加引号
10.OR改UNION效率高
批量导入
insert提效
提交前关闭自动提交
尽量使用批量insert语句
可以使用MyISAM存储引擎
LOAD DATA INFLIE;
使用LOAD DATA INFLIE ,比一般的insert语句快20倍
select * into OUTFILE 'D:\\product.txt' from product_info
load data INFILE 'D:\\product.txt' into table product_info
8.索引与执行计划
索引
what-is
是帮助MySQL高效获取数据的数据结构
分类
普通索引
唯一索引
索引列的值必须唯一,但允许有空值
复合索引
包含多个列
聚簇索引
是一种数据存储方式
InnoDB的聚簇索引其实就是 在同一个结构中保存了B+Tree索引和数据行
非聚簇索引
不是聚簇索引,就是非聚簇索引
基础语法
SHOW INDEX FROM table_name\G
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length)); ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
DROP INDEX [indexName] ON mytable;
执行计划
what-is
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句
作用
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
详解
id
id相同,执行顺序由上至下
id不同,如果是子查询,id的序号会递增 id值越大优先级越高,越先被执行
id相同不同,同时存在
select_type
查询的类型,主要是用于区别 普通查询、联合查询、子查询等的复杂查询
simple
primary
包含任何复杂子部分,最外层标记
subquery
derived
在from列表中包含子查询
union
union result
table
type
system>const>eq_ref>ref>range>index>ALL
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system
表只有一行记录(等于系统表),这是const类型的特列
const
通过索引一次就找到了
通过索引一次就找到了
eq_ref
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配
常见于主键或唯一索引扫描
ref
非唯一性索引扫描,返回匹配某个单独值的所有行
本质上也是一种索引访问
属于查找和扫描的混合体
range
只检索给定范围的行,使用一个索引来选择行
index
all
Full Table Scan,将遍历全表以找到匹配的行
possible_keys
key
实际使用的索引。如果为NULL,则没有使用索引
若使用了覆盖索引,则该索引和查询的select字段重叠
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
显示的值为索引字段的最大可能长度,并非实际使用长度
latin1占用1个字节,gbk占用2个字节,utf8占用3个字节
总结
变长字段需要额外的2个字节-作为长度域 固定长度字段不需要额外的字节
NULL都需要1个字节的额外空间,所以索引字段最好不要为NULL
复合索引有最左前缀的特性, 如果复合索引能全部使用上,则是复合索引字段的索引长度之和
ref
索引的哪一列被使用了
rows
估算出找到所需的记录所需要读取的行数
Extra
包含不适合在其他列中显示但十分重要的额外信息
Using filesort
使用外部排序索引
Using temporary
使用临时表保存中间表
Using index
是否使用了覆盖索引
Using where
表明使用了where过滤
Using join buffer
使用了连接缓存:
impossible where
where子句的值总是false,不能用来获取任何元组
6.业务设计
逻辑设计
范式设计
完全符合范式化的设计有时并不能得到良好得SQL查询性能
三大范式
No.1
表中的所有字段都只具有单一属性
No.2
要求表中只具有一个业务主键
不能存在非主键列只对部分主键的依赖关系
NO.3
每一个非主属性既不部分依赖于也不传递依赖于业务主键
问题
对于查询需要对多个表进行关联
更难进行索引优化
优点
可以尽量得减少数据冗余
范式化的更新操作比反范式化更快
范式化的表通常比反范式化的表更小
反范式设计
反范式化就是使用空间来换取时间
允许存在少量得冗余
优点
可以减少表的关联
可以更好的进行索引优化
缺点
存在数据冗余及数据维护异常
对数据的修改需要更多的成本
物理设计
命名规范
遵循可读性原则
遵守表意性原则
遵守长名原则
存储引擎选择
数据类型选择
优先考虑数字类型
其次是日期、时间类型
最后是字符类型
对于相同级别的数据类型 应该优先选择占用空间小的数据类型
4.存储引擎
逻辑架构
连接层:Connectors
等待客户端连接,每一个客户端连接请求, 服务器都会新建一个线程处理
如果是线程池的话,则是分配一个空的线程
每个线程独立,拥有各自的内存处理空间
身份认证:用户名、IP、密码验证
权限认证:privs
SQL处理层
SQL语句的解析、优化,缓存的查询 MySQL内置函数的实现,跨存储引擎功能
查询逻辑
查询请求
缓存是否命中
yes:返回结果
no:解析查询
sql优化
执行查询,返回结果
缓存
show variables like '%query_cache_type%'
SET GLOBAL query_cache_size = 4000;
解析查询
from:笛卡尔积
ON:主表保留
Join:不服务on也添加
where:非聚合
非select别名
group by:改变对象引用
having-只作用分组后
select:distinct
order by-可使用select别名
limit: rows-offset
优化
where name=''
where 1=1
where id is null
不可达的
存储引擎
show engines;
show variables like '%storage_engine%';
MyISAM
MySql 5.5之前默认的存储引擎
MyISAM 存储引擎由MYD和MYI组成
特性
并发性与锁级别-表级锁
支持全文检索
支持数据压缩
myisampack -r -f testmysam.MYI
适用场景
非事务型应用(数据仓库,报表,日志数据)
只读类应用
空间类应用(空间函数,坐标)
InnoDB
MySql 5.5以及以后版本默认存储引擎
表空间
innodb_file_per_table
ON:独立的表空间:tablename.ibd
OFF:系统表空间:ibdataX
建议:Innodb使用独立表空间
对比
系统表空间无法简单的收缩文件大小
独立表空间可以通过optimize table 收缩系统文件
系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据
特性
Innodb是一种事务性存储引擎
完全支持事务得ACID特性
Redo Log 和 Undo Log
Innodb支持行级锁(并发程度更高)
场景
Innodb适合于大多数OLTP应用
CSV
数据以文本方式存储在文件
.csv文件存储内容
.csm文件存储表得元数据如表状态和数据量
.frm 表结构
特点
所有列都不能为null的
不支持索引(不适合大表,不适合在线处理)
可以对数据文件直接编辑(保存文本文件内容)
Archive
组成
以zlib对表数据进行压缩,磁盘I/O更少
数据存储在ARZ为后缀的文件中
特点
只支持insert和select操作
只允许在自增ID列上加索引
场景
日志和数据采集应用
Memory
也称HEAP存储引擎,所有数据保存在内存中
特性
支持HASH索引和BTree索引
所有字段都是固定长度 varchar(10) = char(10)
Row_format=fixed
不支持Blog和Text等大字段
Memory存储引擎使用表级锁
最大大小由max_heap_table_size参数决定
临时表
系统使用临时表
超过限制用Myisam
未超限制使用Memory
自己建立临时表
create temporary table
使用场景
hash索引用于查找或者是映射表(邮编和地区的对应表)
用于保存数据分析中产生的中间表
用于缓存周期性聚合数据的结果表
Ferderated
默认禁止,启用需要再启动时增加federated参数
mysql://user_name[:password]@hostname[:port_num]/db_name/table_name
2.mysql架构
概述
好处
提高可用性
提高性能
集群的可用方案
mysql-Cluster
优势:可用性非常高,性能非常好
维护非常复杂,存在部分Bug
线上系统,所以不推荐
DRBD磁盘网络镜像
Distributed Replicated Block Device
看做一种网络RAID
mysql-Replication
通过简单的增加价格低廉的硬件设备成倍 甚至成数量级地提高了原有系统的性能
mysql复制
what-is
将主数据库的 DDL和 DML 操作通过二进制日志传到复制服务器
DML(data manipulation language)数据操纵语言
SELECT、UPDATE、INSERT、DELETE
DDL(data definition language)数据库定义语言
CREATE、ALTER、DROP
原理
主库在事务提交时会把数据变更作为事件 Events 记录在二进制日志文件Binlog中
推送二进制日志文件 Binlog中的事件到从库的中继日志 Relay Log
从库根据中继日志 Relay Log重做数据变更操作
三个线程
Binlog Dump线程跑在主库上
I/0线程和 SQL线程跑在从库上
SHOW PROCESSLIST命令
各类文件解析
日志文件
二进制日志文件( Binlog)和中继日志文件( Relay Log)
不会记录 Select操作
SQL线程会自动删除当前中继日志文件 Relay Log
三种复制技术
Statement:基于 SQL语句级别的 Binlog
默认
Row:基于行级别,记录每一行数据的变化
Mixed:混合Statement和Row模式
相关命令
show variables like '%binlog%format%';
set global binlog_format = 'ROW';
set global binlog_format = 'STATEMENT';
常用复制架构
一主多从
多级复制
双主复制- Dual-Mater
双主多级复制
复制过程搭建
异步复制
创建复制用户repl
GRANT REPLICATION SLAVE ON *.* To 'rep1'@'192.168.56.103' IDENTIFIED BY '1234test';
修改主-my.cnf
log-bin=/home/ mysql/log/mysql-bin. log server-id= 1
show master status:
修改从-my.cnf
[mysqld] server-id=2
从库-创建复制关系
mysql> CHANGE MASTER TO ->MASTER_HOST=master host name ->MASTER_USER=replication_user_name -> MASTER PASSWORD=replication_password ->MASTER_LOG_FILE='recorded_log_file_name ->MASTER_LOG_POS=recorded _log_position
启动 slave线程:
start slave
show processlist \G;
半同步复制
等待其中一个从库也接收到Binlog事务并成功写入Relay Log之后 才返回Commit操作成功给客户端
安装半同步 复制插件
select @@have_dynamic_loading;
主库安装
install plugin rpl_semi_sync_master SONAME 'semisync_master.so'
从库安装
install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
select * from mysql.plugin;
开启
默认半同步设置是不打开的
主库
mysql> set global rpl_semi_sync_master_enabled=1;
mysql> set global rpl_semi_sync_master timeout 30000;
从库
mysql> set global rpl_semi_sync_slave_enabled=1;
读写分离实战
1.思路:选择数据库执行这个关键性问题
2.AbstractRoutingDataSource
targetDataSources是设置的目标数据源
resolvedDataSources是实际的数据源
getConnection方法中最终调用 determineTargetDataSource方法 来定位实际的数据源
我们覆盖determineCurrentLookupKey方法 来从容器resolvedDataSources获得实际的数据源
3.枚举类
DBTypeEnum: MASTER,SLAVE
4.用ThreadLocal保存当前线程数据源对象
5.@bean myRoutingDataSource
6.定义aop
定义了一个SpringAop类DataSourceAop
其中定义了两个切点,slavePointcut和masterPointcut
7.定义一个 @Master
keepalive高可用
Keepalived实现双主高可用
Keepalived的作用是检测服务器的状态
是基于VRRP协议的一款高可用软件
VRRP(Virtual Router Redundancy Protocol 虚拟路由器冗余协议)
原理
IP层: Keepalived会定期向服务器群中的服务器发送一个ICMP的数据包
TCP层:主要以TCP端口的状态来决定服务器工作正常与否
应用层:对指定的URL执行HTTP GET
和HAProxy的区别
HAProxy请求分发
类似于通过nginx来作后端的负载均衡
除支持http7层处理外,还顺便为mysql支持4层转发
haproxy可以通过监听一个统一的端口对外提供能力,然后内部进行分发
最关键的部分其实是Mysql的两个服务器要互为主从,所以在my.cnf的配置文件中,除了log-bin参数以外,还需要增加并设置参数logs-slave-updates=1