导图社区三.mysql

三.mysql

存储引擎，sql优化，主从部署

编辑于2020-07-05 01:00:20

java架构师

21Learner

三.mysql

社区模板帮助中心，点此进入>>

21Learner

相似推荐
大纲

三.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相同不同，同时存在

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的

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

可以对数据文件直接编辑（保存文本文件内容）