10.Mysql深入分析

MySQL

MySQL v5.6版本安装

1. 查询系统中已有的MySQL

rpm -qa|grep mysql

如果有卸载MySQL并删除相关文件（这里是基于默认安装的）

yum remove -y mysql mysql-libs mysql-common
rm -rf /var/lib/mysql
rm -rf /etc/my.cnf

2. 安装v5.6 MySQL

wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
rpm -ivh mysql-community-release-el6-5.noarch.rpm
yum install -y mysql-community-server

3. 配置MySQL

vim /etc/my.cnf

4. 启动MySQL

service mysqld start

5. 设置root密码

/usr/bin/mysqladmin -u root password '123456'

数据库应用

• 不同的业务系统一定要独立授权用户

where条件解析顺序

• MySQL是从左向右执行where条件（查询优化器会去优化where条件，但效果不一定）
• Oracle是从右向左执行where条件

SQL执行顺序

FROM

ON

JOIN

WHERE

GROUP BY

HAVING

SELECT columns

DISTINCT

ORDER BY

LIMIT

全文索引

• 只有在MyISAM引擎上才能使用
• CHAR、VARCHAR、TEXT字段才能使用

SQL的执行过程

客户端

连接器：管理连接、做权限验证 username、password

查询缓存：key-value 如果有就直接返回，全SQL匹配

• preparestatement：args string
• statement：string
• 如果数据表发生结构变化及数据内容变化，MySQL会立即清空对应表缓存
• MySQL 8.0 直接删除了，彻底没了
• 会在结构返回前做一次权限验证

分析器

• 词法分析：错别字
• 语法分析：语法结构
• Preparestatement、#{}

优化器：执行计划生产，索引选择

执行器：操作引擎

• 如果你对这个表没有权限，就会返回错误 grant
• 从数据引擎里取数据

MySQL

• MyISAM：查询和插入的高速引擎，但不支持事务
• InnoDB：v5.5 支持事务支持行锁
• ISAM：5.0以后就不装了
• Memory：内存引擎，速度快，但掉电丢失数据
• Falcon：InnoDB的替代方向
• Archive：归档引擎，只支持写入和查询，适合大数据量，将数据压缩后存储
• CSV

show engines;
show table status like '%user_role%' \G;

MySQL存储的物理结构

• MySQL是通过文件系统对数据和索引进行存储的
• MySQL从物理结构上可以分为日志文件和数据索引文件
• MySQL在linux下默认安装文件路径 /var/lib/mysql

日志文件

MySQL通过日志记录数据库的操作和错误信息，常用到的日志：错误日志、查询日志、慢查询日志、二进制日志Binlog

show variables like '%log%';

错误日志

默认是开启的

log_error=/var/log/mysqld.log
log_warnings=1

log_warnings：是否开启警告信息一并写入错误日志 0|1

log_warnings>1 : 将失败的连接，拒绝访问相关信息也记录到错误日志中

查询日志

• 在mysql中被称为general log

• 会记录所有操作，无论对错

• 存放方式有三种

  • 日志 general_log_file /var/lib/mysql/MySQL.log
  • 将查询日志存入mysql.general_log表中
  • 同时存放

• 三种方式的配置项

  • log_output FILE|TABLE|FILE,TABLE

general_log=ON
log_output=FILE

慢查询日志

目录授权

chown -R mysql:mysql /usr/local/slowfile/

mysqldumpslow使用

• -s排序规则，降序，-t 2显示结果前两行
• c ：执行次数
• l ： 锁定时间
• r ： 返回记录
• t ：执行时间
• al、ar、at

percona-toolkit使用

可以直接对日志进行时间的区间查询

Binlog二进制日志

默认是关闭的，需要通过配置进行开启

log_bin=mysql-bin

log_output=FILE

**慢查询日志**

目录授权

```sql
chown -R mysql:mysql /usr/local/slowfile/

mysqldumpslow使用

• -s排序规则，降序，-t 2显示结果前两行
• c ：执行次数
• l ： 锁定时间
• r ： 返回记录
• t ：执行时间
• al、ar、at

percona-toolkit使用

可以直接对日志进行时间的区间查询

Binlog二进制日志

默认是关闭的，需要通过配置进行开启

log_bin=mysql-bin``