Mysql sow proseee_MySQL学习笔记

## 下载安装

### 下载解压

wget http://iso.mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-8.0/mysql-8.0.16-linux-glibc2.12-x86_64.tar

tar -xvf mysql-8.0.16-linux-glibc2.12-x86_64.tar

tar -xvf mysql-8.0.16-linux-glibc2.12-x86_64.tar.xzmv mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz mysqlcp -rp mysql /usr/local/

### 环境配置

cd /usr/local/useradd-s /sbin/nologin -M mysqlchown -R mysql:mysql mysql

mysql/bin/mysqld --initialize --user=mysql

#此时必须复制保存好临时密码

vim/etc/profile

export PATH=$PATH:/usr/local/mysql/bin

source/etc/profilecp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

cd/etc/init.d/

chmod +x mysql

service mysql start

mysql-u root -p ===>密码

alter user 'root' @'localhost' identified by '123456';

## 架构介绍

### 架构图

- Connectors连接层

- JDBC，.NET，PHP，Python，ODBC，Native C API，Cobol，Perl，Ruby

- MySQL Server服务层

- Connection Pool连接池

- Management Services & Utillities管理工具

- SQL Interface语句接口

- Parser分析器层

- Optimizer优化器层

- Caches & Buffers缓冲层

- Pluggable Storage Engines引擎层

- InnoDB

- MyISAM

- NDB

- Archive

- Federated

- Memory

- Merge

- Partner

- Community

- Custom

- File System存储层

- NTFS，ufs，ext2/3，NFS，SAN，NAS

- Files & Logs存储层

- Redo，Undo，Data，Index，Binary，Error，Query，Sow

- 和其它数据库相比，MySQL在不同的场景中都可以发挥良好作用，这基于其可插拔式的存储引擎架构，MySQL做到将数据任务处理和数据存储提取相分离，这种架构利于根据业务不同选择合适的存储引擎

### 主要存储引擎

- InnoDB

- 支持主外键，支持事物，支持行锁高并发，缓存真实数据时也缓存索引，内存要求高，表空间大

- MyISAM

- 不支持主外键，不支持事物，支持表锁低性能，只缓存引擎，不缓存真实数据，表空间小

## 表操作

### 表的创建和删除

- create/drop table + 表名

### 增删改

- insert into + 表名 + (列名，...) + values + (数据，...);

- delete from + 表名 + where + 条件;

- update + 表名 + set + 列名1=值1, 列名2=值2, ... where + 条件

- select + 列名1，列名2，... + from + 表名 + [条件];

### 查询

- where数据过滤

- as

- not

- is null

- like

- %

- _

- between ... and ...

- in

- 如：select * from user where id in (1,2,3);

- and

- or

- order by

- asc升序

- desc降序

- limit

- 如：select * from user limit 0 , 3;

- group by

- having组过滤

- GROUP BY 子句必须出现在WHERE子句之后，ORDER BY 语句之前

- 关联查询

- 内关联

- 外关联

- 左外关联

- 右外关联

### 函数

- 聚合函数

- count(列名)

- avg(列名)

- max(列名)

- min(列名)

- sum(列名)

- 文本处理函数

- 数值处理函数

- 日期处理函数

## MySQL的事物

### 概要

- ACID

- 原子性(Atomicity)

- 事物是一个原子操作单元，对数据修改时，要么全部执行，要么全部不执行

- 一致性(Consistent)

- 在事物开始和结束时，数据必须保持一致

- 隔离性(Isolation)

- 数据库系统提供一定的隔离机制，保证事物在执行过程中不受外部并发操作的影响

- 持久性(Durable)

- 事物完成后，事物对数据的修改时永久性的，即使出现系统故障也能保证数据不变

- 相关语法

- 查看当前数据库事物隔离级别

- show variables like "tx_isolation"

- 事物开始

- set autocommit = 0

- 事物回滚

- rowback

- 事物提交

- commit

### 并发时事物处理可能问题

- 更新丢失(Lost Update)

- 事物A和事物B同时对同一条数据操作，最后修改的覆盖了之前修改的数据，造成之前的数据失效

- 脏读(Dirty Reads)

- 事物A读到了事物B已修改但未提交的事物数据，且事物A在此数据上进行操作

- 不可重复读(Non-Repeatable Reads)

- 事物A读到了事物B已经提交的修改数据

- 幻读(Phantom Reads)

- 事物A读取到了事物B提交的新数据

### 事物隔离级别

- 未提交读(Read uncommitted)

- 最低级别，会出现脏读、不可重复读、幻读

- 已提交读(Read committed)

- 语句级别，会出现不可重复读、幻读

- 可重复读(Repeatable read)[MySQL默认级别]

- 事物级别，会出现幻读

- 可序列化(Serializable)

- 最高级别，事物级别

### MySQL的锁机制

- 定义

- 锁是计算机协调多个进程或线程并发访问某一资源的机制。为保证用户访问的数据的一致性和有效性，锁冲突就影响数据库并发性能的重要因素

- MySQL中的3种锁

- 表锁(偏读)

- 特点

- 开销小、加锁快、无死锁、锁定力度大、发生锁冲突概率小，偏向MyISAM存储引擎，并发能力低

- 语法

- lock table 表名 read/write

- unlock tables

- 行锁(偏写)

- 特点

- 开销大、加锁慢、会出现死锁、锁定力度小、发生锁冲突概率最低、并发量大，偏向InnoDB存储引擎，支持事物

- 问题

- 索引失效导致行锁变表锁

- 按照范围来锁行会导致产生间隙锁

- 主动锁定一行

- 语法

- begin

- SQL语句+for update/insert

- commit

- 查看数据库锁的情况语法

- show status like "innodb_row_lock%"

- 优化建议

- 尽可能让所有数据检索通过索引来完成，避免无索引行锁升级为表锁

- 合理设计索引，尽量缩小锁的范围

- 尽可能减少检索条件，避免间隙锁

- 尽量控制事物大小，减少锁定资源量和时间长度

- 尽可能低级别事物隔离

- 页锁

- 开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定力度界于表锁行锁之间，并发力度一般

## 索引和优化

### 定义

- 索引是帮助MySQL高效获取数据的数据结构，用于对数据排序和查找

- 索引本身很大，其往往以索引文件方式存储在磁盘上

- 一般索引所指的是B树结构的索引

### 优/劣

- 优势

- 提高数据检索效率，降低数据库IO成本

- 降低数据排序成本，降低CPU消耗

- 劣势

- 索引也是一张表，指向实体表的纪录，索引页占空间，降低更新表的速度

- 对于大量数据的表，需要花时间建立好的索引

### 索引分类

- 单个索引

- 唯一索引

- 符合索引

### 使用情况

- 合适

- 主键自动创建唯一索引

- 频繁作为查询条件的字段

- 与其它表关联的字段，外键

- 高并发下倾向创建组合索引

- 排序字段

- 统计和分组字段

- 不合适

- 频繁更新，增删改的表或字段

- 纪录少的表

- where条件很少用到的字段

- 重复数据多的表

### 性能分析

- MySQL Query Optimizer

- MySQL有专门负责优化查询的优化器模块，通过计算分析系统中收集到的统计信息，为客户端提供最优的执行方式

- MySQL常见瓶颈

- CPU

- CPU在饱和的时候一般会发生在数据装入内存或从磁盘读取数据的时候

- IO

- 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

- 其它硬件

- top，free，iostat，vmstat来查看系统性能状态

- Explain

- 定义

- 使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句。分析查询语句或表结构的性能瓶颈。是一种衡量SQL语句是否足够优化的检测方式

- 作用

- 解释表的读取顺序

- 解读数据读取操作的类型

- 解读哪些索引可以使用

- 解读哪些索引实际被使用

- 解读表之间的引用

- 解读表的多少行被优化器查询

- 字段解读

- id

- 表示查询的序列号，查询中子句或表操作的执行顺序

- id相同，执行顺序由上而下

- id不同，如果是子查询，id序号会递增，id越大优先级越高先被执行

- id相同和不同都存在

- select_type

- 查询类型；显示此次查询用到哪些查询类型

- 常见值类型

- simple

- 简单查询，不包含子查询和union

- primary

- 若查询中包含任何复杂的子部分，最外层的查询被标记为此值

- subquery

- 在select或where中包含子查询

- derived

- 在from中包含子查询标记此值，MySQL会递归执行这些子查询，把结果放在临时表里

- union

- 若第二个select出现在union后，则标记此值，若union包含在from的子查询中，外层select被标记为derived

- union result

- 从union表获取结果的select

- table

- 指明这行数据是关于那个表的

- type

- 访问类型；显示查询使用了何种类型；从最好到最差顺序为：system>const>eq_ref>ref>range>index>all

- 常见值类型

- system

- 表示表只有一行纪录，是const的特例，平时不会出现，可忽略不计

- const

- 表示通过索引一次就找到数据，只匹配了一行数据，所以很快；如将主键置于where后，MySQL就能将该查询转换为一个常量

- eq_ref

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

- ref

- 非唯一性索引扫描，返回匹配谋和单独值的所有行，本质也是索引访问，它返回所有匹配某个单独之的行，但它可能会找到多个符合条件的行，所以它属于查找和扫描的混合体

-range

- 只检索给定范围的行，使用一个索引来选择行。一般在where后出现between、>、

- index

- 遍历全表索引树扫描，因为索引文件通常比数据文件小，速度比all快

- all

- 遍历全表查找匹配的行

- null

- 空

- possible_keys

- 显示可能应用到这个表查询的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引会被列出，但不一定被实际查询所使用

- key

- 显示实际查询中用到的索引。如果是null，则没有用到索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中

-key_len

- 表示索引字段最大可能长度，并非实际使用长度。在不损失精确性情况下，长度越短越好。该长度是根据表定义计算得出，不是通过表内检索得出

- ref

- 显示索引的哪一列被使用，如果可能，是一个常数。哪些列或常数被用于查找所有列上的值

- rows

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

- extra

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

- 常见值

-using filesort

- 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”

- using temporary

- 使用了临时表保存中间结果，MySQL对查询结果排序时使用了临时表。常见于order by、group by

- using index

- 表示相应的select中使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找操作

-using where

- 表明使用了where过滤

- using join buffer

- 表明使用了连接缓存

- Impossible where

- where子句的值总是false，不能用来获取任何元组

- select tables optimized away

- 在没有group by子句的情况下，基于索引优化MAX/MIN操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

- distinct

- 优化distinct操作，在找到第一匹配的元组后即停止找同样值得操作

### 索引优化

- 索引优化案例

- 单表优化

- 两表优化

- 三表优化

- 索引失效情况

- 未使用全值配置

- 最佳左前缀法则

- 查询从条件的最左边开始，并且不跳过索引中的列

- 不在索引列上做任何操作

- 存储引擎不能使用索引中范围条件右边的列

- 覆盖索引

- 使用不等于时无法使用索引导致扫描全表

- is null，is not null无法使用索引

- like使用通配符开头导致索引失效索引

- 字符串不加单引号

- 用or来连接造成索引失效

- 一般性建议

- 对于单键索引，尽量选择针对当前查询过滤性更好的索引

- 在选择组合索引时，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好

- 在选择组合索引时，尽量选择可以能够包含当前查询中的where字句中更多字段的索引

- 尽可能通过分析统计信息和调整查询的写法来达到最合适索引的目的

## 主从复制

### MySQL主从复制原理

- 从节点slave会从主节点master读取binlog来进行数据同步

- 步骤

- master将改变纪录到二进制日志文件(binary log)中。这些纪录过程叫做二进制日志事件(binary log events)

- slave将master的binary log events拷贝到它的中继日志(relay log)

- slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步和串行化的

### MySQL主从复制原则

- 每个slave只有一个master

- 每个slave只能有唯一一个服务器ID

- 每个master可以有多个slave

- MySQL版本要求一致

### MySQL主从复制问题

- 延时

### MySQL主从复制配置

- 主节点配置

- 修改my.cnf文件

- 在[mysqld]下添加如下配置

#[必须配]

server-id=1log-bin=/home/mysq/bin/mysql-bin

log-err=/home/mysql/err/err-log

#[可不配]

basedir=MySQL本地路径

binlog-ignore-db=mysql

binlog-do-db=test

- 启动主节点

- 关闭防火墙

- 主节点授权grant replication slave on *.* to 'slave01' @'192.168.150.103' identified by '123456'

- 刷新flush privileges

- 查看主机状态show master status

- 记住File、Position的值，作为从节点的连接配置选项

- 从节点配置

-修改my.cnf文件

- 在[mysqld]下添加如下配置

server-id=2log-bin=mysql-bin

- 启动从节点

-关闭防火墙

- 连接主节点

change master to master_host='192.168.150.102',master_user='slave01',master_password='123456',master_log_file='mysql-bin.0001',master_log_post=520;

- 启动从节点

start slave;

- 查看从节点状态

show slave status;

- Slave_IO_Running:Yes和Slave_SQL_Running:Yes表示成功