MySQL数据库入门

发表于 | 分类于 ，

介绍与安装

特点

  • 门槛低，简单
  • 群众基础广大，资料多
  • 免费

安装

rpm包

1
sudo rpm -ivh *.rpm

yum源

1
sudo yum install q-dba-mysql-56

apt

1
sudo apt install mysql-server mysql-client

DML

SELECT

1
2
3
4
5
SELECT FROM `基本表或(和)视图集合`
[WHERE 条件表达式]
[GROUP BY `列名1`[,`列名2`...]]
[HAVING 组条件表达式]
[ORDER BY `列名1`[,`列名2`...]];

INSERT

1
INSERT INTO `表名` (`列名1`[,`列名2`...]) VALUES ('列值1'[,'列值2'...]);

UPDATE

1
UPDATE `表名` SET `列名`='列改变值' [WHERE 条件表达式];

DELETE

1
DELETE FROM `表名` [WHERE 条件表达式];

JOIN

INNER JOIN

左右两表只有满足连接条件的会显示，默认为INNER JOIN

LEFT JOIN

左表中的行全部显示，右表中不存在的行以NULL填充

RIGHT JOIN

右表中的行全部显示，左表中不存在的行以NULL填充

DDL

CREATE

DATABASE

1
CREATE DATABASE `数据库名` [其它参数];

TABLE

1
2
3
4
5
CREATE TABLE `表名` (
  `COLUMN1` TYPE CONSTRAINTS [,
  `COLUMN2` TYPE CONSTRAINTS]
  ...
) [CONSTRAINTS];

DROP

1
2
DROP TABLE `NAME` [OPTIONS];
DROP DATABASE `NAME` [OPTIONS];

TRUNCATE

1
TRUNCATE TABLE `NAME`;

RENAME

1
RENAME TABLE `NAME` TO `NEW_NAME` [,`NAME2` TO `NEW_NAME2`...];

ALTER

ADD

1
ALTER TABLE `T_NAME` ADD COLUMN `C_NAME`;

MODIFY/CHANGE

MODIFY
1
ALTER TABLE `T_NAME` MODIFY COLUMN `C_NAME` [OPTIONS];
CHANGE
1
ALTER TABLE `T_NAM`E CHANGE COLUMN `OLD_NAME` `NEW_NAME` [OPTIONS];

DROP

1
ALTER TABLE `T_NAME` DROP COLUMN `C_NAME`;

存储引擎

MySQL体系架构

单进程多线程

MyISAM

  • 存储大小无限制
  • 表级锁
  • 支持B树索引
  • 支持全文索引

InnoDB

  • 存储大小最大64TB
  • 支持事务
  • 行级锁
  • 支持快照读
  • 支持B树索引
  • 支持Hash索引
  • 支持聚簇索引

使用建议

MyISAM 速度快于 InnoDB，而MyISAM不支持事务，若若需要事务支持用InnoDB，若不需要事务支持切对速度要求比较高使用MyISAM

字段类型

数字

整形

  • TINYINT
  • SMALLINT
  • MEDIUMINT
  • INT
  • BIGINT

定点型

  • DECIMAL

浮点型

  • FLOAT
  • DOUBLE

字符型

CHAR/VARCHAR

  • CHAR定长
  • VARCHAR变长，若长度 <256，占用一个字节，若长度 >=256 ，占用2个字节

TEXT

  • 变长，可以记录更多字符
  • 不能设置NN，和默认值
  • 只能建立前缀索引

BLOB

  • 记录二进制字符串类型，没有字符集和校验规则的限制
  • 只能建立前缀索引

时间日期型

建议使用 DATETIME 代替 TIMESTAMP

YEAR

1Byte

DATE

3Byte

TIME

3Byte + fractional seconds storage

DATETIME

5Byte + fractional seconds storage

TIMESTAMP

4Byte + fractional seconds storage

权限管理

MySQL权限验证

  • 读取权限信息到内存
  • 对连接的用户进行身份认证
  • 对操作权限验证

常见场景举例

创建用户

使用CREATE USER，GRANT语句

1
2
CREATE USER {user}@{host} IDENTIFIED BY {password};
GRANT {privileges} ON {database}.{table} TO {user}@{host} IDENTIFIED BY {password};

直接插入用户到mysql.user（禁用）

1
2
insert into mysql.user(user,host..) values({user},{host}...);
flush privileges;

删除用户

使用DROP USER删除

1
DROP USER {username}@{host}

直接在mysql.user中删除

1
2
delete from mysql.user where user={username} and host={host};
flush privileges;

用户权限授予

1
GRANT {privileges} ON {database}.{table} TO {user}@{host} (with grant option);

用户权限删除

1
REVOKE {privileges} ON {database}.{table} FROM {user}@{host} (with grant option);

常用权限

权限 说明
ALL [PRIVILEGES] 设置除GRANT OPTION之外所有的权限
SELECT 查询权限
INSERT 插入权限
DELETE 删除权限
UPDATE 更新权限
FILE 允许使用select … into {outfile}和load data {infile}
SUPER 允许使用CHANGE MASTER，KILL和SET GLOBAL等语句
PROCESS 允许使用SHOW FULL PROCESSLIST（查看用户的操作明文）
WITH GRANT OPTION 允许授予权限

常用系统命令

查看帮助信息

命令 说明
help/? 查看所有帮助信息
help/? contents 查看server端的帮助信息
help/? Data Definition 查看数据定义类别下的帮助信息
help/? CREATE TABLE 查看CREATE TABLE语句的帮助信息

SHOW命令

查看数据库

1
show databases [like {regex}]

查看表

1
show tables [like {regex}]

查看数据库定义

1
show create database {database}

查看表定义

1
show create table {table}

查看表的基本情况

1
show table status {table}/(like {regex})

查看表的索引

1
show index from {table}

查看表的列信息

1
show columns from {table}

查看用户的授权语句

1
show grants for [{user}@{host}]

查看变量值

会话变量

1
show [session] variables {variable}/(like {regex})

全局变量

1
show global variables {variable}/(like {regex})

查看状态信息

会话信息

1
show [session] status {variable}/(like {regex})

全局信息

1
show global status {variable}/(like {regex})

查看当前连接的用户以及执行的SQL

1
show [full] processlist

SET命令

设置变量值

会话变量

1
set [session] {variable}={value}

全局变量

1
set global {variable}={value}

Master-Slaver原理

异步复制由来

为了解决MySQL的单点数据库的缺点
单点数据库的缺点：

  • 数据丢失风险大
  • 读写无法分离，单点承受能力有限
  • 无法快速进行Failover
  • 随着业务需求的增多，无法横向扩展
  • 压力过大无法备份数据

Binlog，Relay Log

Binlog

  • 可以理解为开发执行的SQL语句（增删改，DDL）按照提交的先后顺序在MySQL内部生成的日志
  • 可以在数据回滚几异步复制时使用

Relay Log

  • 通过异步复制Master中的Binlog，生成Slave的执行日志
  • 内部也是按SQL提交的限购顺序储存

Binlog的存储格式

Binlog的存储格式是由binlog_format控制，有三个值

  • row：会存储全部的行信息（推荐）
  • statement：记录语句信息，即执行的SQL
  • mixed：会根据不同的情况生成statement或row（不建议）

异步复制原理

  • 主库
    • binlog dump thread：
      当从库连接主库的时候，会发送binlog event并持有该event的lock，发送完（IO thread）释放该event的lock
  • 从库
    • I/O thread：
      当读到主库发送的binlog event，生成relay log
    • SQL thread：
      读relay log，并在从库上执行

GTID

  • 全局事务ID，由source_id:transaton_id组成的唯一比标识
  • 可以依托于传统方式和半同步方式，只是复制从原来的file-pos方式，改变为GTID方式
  • GTID是集群化的第一步，目前MGR只能在GTID模式下才能工作

优点

  • 在同一组MS中，主从切换更方便
  • 故障切换更简单

缺点

  • 不支持非事务的存储引擎
  • 不支持create table …select语句复制
  • 在一个集群中，必须要求统一开启或关闭GTID
  • 不支持create/drop temporary table

索引原理

索引是什么

  • 定义
    • 索引是存储引擎用于快速定位数据的一种数据结构
  • 作用
    • 优化数据访问
    • 保持数据完整性

B+ Tree

是一种平衡查找树

  • 叶子结点包含所有数据
  • 内结点包含了一部分叶子节点的键值
  • 搜索的时间复杂度的比较稳定
  • 所有记录结点都是按键值的大小顺序存放在同一层的叶子节点上，由叶子结点指针进行连接

索引类型介绍

  • 聚簇索引（主键）：叶子结点包含（主键列值 事务ID 回滚主键 非主键列值）
  • 辅助索引（非主键、二级）：叶子节点包含（索引列值 对应的聚簇索引主键列值）
  • 单列索引：由单个字段组成
    WHERE条件中的字段查询时可以应用到索引
    alter table product add index idx_orderno(orderno);
  • 联合索引：由多个字段组成
    alter table product add index idx_orderno_createtime(orderno,createtime);
  • 覆盖索引（Covering Index）：包含所有要查询的字段的值的索引

不使用索引的情况

  • where条件中没有内容
  • 否定条件 <>，not in，not exits
  • join中连接字段类型或字符集不一致
  • 扫描内容超过全表的20%
  • where条件的字段存在函数运算
  • 前模糊匹配
  • 出现隐式字符类型转换