[关闭]
@octopus 2020-10-09T10:00:04.000000Z 字数 8544 阅读 251

mysql

mysql


数据库设计规范

索引设计规范

单列索引与联合索引

为 test表的 a,b,c 三列设置联合索引

  1. KEY `a_b_c` (`a`,`b`,`c`)
  1. SELECT * FROM `test` WHERE a='1'
  1. SELECT * FROM `test` WHERE b='1'
  1. SELECT * FROM `test` WHERE c='1'
  1. SELECT * FROM `test` WHERE a='1' and b='1'
  1. SELECT * FROM `test` WHERE b='1' and a='1'
  1. SELECT * FROM `test` WHERE a='1' and c='1'
  1. SELECT * FROM `test` WHERE b='1' and c='1'
  1. SELECT * FROM `test` WHERE a='1' and b='1' and c='1'
  1. SELECT * FROM `test` WHERE a='1' or b='1'

单列索引在多条件查询时,仅能用到一列索引。为 test表的 a,b,c 三列设置单列索引:

  1. KEY `a` (a)
  2. KEY `b` (b)
  1. SELECT * FROM `test` WHERE a='1' and b='1'
  1. SELECT * FROM `test` WHERE a='1' or b='1'

数据库字段设计规范

SQL 开发规范

sql 操作规范

三范式

事务

InnoDB 支持事务,Myisam 不支持

简单的事务操作

  1. mysql> start transaction; #手动开启事务
  2. mysql> insert into t_user(name) values('pp');
  3. mysql> commit; #commit之后即可改变底层数据库数据
  4. mysql> select * from t_user;
  5. +----+------+
  6. | id | name |
  7. +----+------+
  8. | 1 | jay |
  9. | 2 | man |
  10. | 3 | pp |
  11. +----+------+
  12. 3 rows in set (0.00 sec)
  13. mysql> start transaction;
  14. mysql> insert into t_user(name) values('yy');
  15. mysql> rollback;
  16. mysql> select * from t_user;
  17. +----+------+
  18. | id | name |
  19. +----+------+
  20. | 1 | jay |
  21. | 2 | man |
  22. | 3 | pp |
  23. +----+------+
  24. 3 rows in set (0.00 sec)

四大特性

四种隔离级别

级别(中) 级别(英) 脏读 不可重复读 幻读
读未提交 read uncommitted
读已提交 read committed x
可重复读 repeatable read x x
串行化 Serializable

(在事务中)领导给 a 发了 1000 块工资,还没有提交,这时 a 读取了账户余额,发现有 1000 块。领导又撤销了操作,a 再读发现没发工资,白高兴一场。

并发的事务中,B可以读A未提交的操作,称为脏读。

解决了读未提交的问题,在领导确定并提交了工资后,a 才能读到改变后的工资。

a 拿着新发的 1000 块去消费,买东西时看了一眼余额有 1000 块,同时, a 的老婆把1000块转到了自己账户里,然后 a 付款时发现余额不足。

在同一事务中,对同一条数据的多次查询结果不同,称为不可重复读

解决了读已提交中的问题,a 开始读余额的时候,其他事务就不可以进行修改(写锁)。

a 在消费前,a的老婆就在查工资单了,发现有 1条 +1000 块的数据,这时 a 消费了 800, a的老婆再查发现有两条数据, +1000 与 -800。

前后多次读取,数据总量不一致,称为幻读,多是插入或删除操作引起。

事务A和事务B,事务A在操作数据库时,事务B只能排队等待,这种隔离级别很少使用,并发量太低,用户体验差。

Innodb 与 Myisam

字段类型

整型

int(4) 其中的宽度 4 是配合 zerofill 使用的,当数据小于这个宽度时会自动填充0。比如插入数字 1,会显示 0001,与存储空间没有关系。

varchar

varchar 用于存储变长字符串,只占用必要的存储空间,但会按定义的最大长度来分配内存。如果最大长度小于255则只占用一个额外字节记录最大长度,否则占用两个额外字节。适用于保存很少被更新的数据。

char

char 用于存储定长字符串,最大宽度为 255,字符串存储在 char 类型列中会被删除末尾空格。
char 类型适合存储长度近似的值,如 md5 的密码,身份证号等等

注意, varchar(n) 与 char(n) 中的宽度 n 都是字符,根据编码方式不同占用字节数也不同。

explain

无法展示触发器,存储过程,UDF对查询的影响

id

id 表示执行顺序,值越大优先级越高,图上代码会先执行 id:3 的语句,即最内层 select。

若 id 值相等,则从上而下执行。

select_type

含义
simple 不包含子查询或者union操作的简单查询
primary 如果包含了任何子查询,最外层将被标记为primary
subquery 内层的子查询(select 列表中的子查询 )
dependent subquery 依赖外层查询 (危!!)
... ...

table

table 表示该语句用到的表,也可能是临时表。

图上例子,先在 product_info 表中查询,然后在 b(product_comment) 表中查询,<derivedN>表示使用 id 为 N 的结果集,所以 <derived2> 是使用了 id:2 的查询结果。

含义
tableName 表名
<derivedN> id 为 n 的语句的执行结果集
<unionM,N> 由 id 为 M,N 查询 union 产生的结果集

partitions

对于分区表,显示查询的分区ID,对没有分区的表显示 NULL。

如果用分区键进行查询,partitions 仅显示所在分区,即会在所在分区查询,如 partitions : p1。

如果不使用分区键进行查询,会在全分区扫描,如 partitions : p1,p2,p3 。

type

mysql 查询数据的方式

性能 含义
system const 方式的特例,查询的表只有一行
. const 对主键或唯一索引的查询,且结果有且只有一行
. eq_ref 对主键或唯一索引的查询,对每个索引键,结果只有一行
. ref 非唯一索引的查询,返回索引命中的所有行
. ref_or_null 类似 ref,但附加了对 null 值的查询
. index_merge 使用了索引合并优化的方法
. range 索引范围扫描,通常使用了 > < between等
. index 全索引扫描
all 全表扫描,未使用索引

key

查询优化器实际使用到的索引,如果没有可用索引,显示 NULL

rows

查询语句大致需要读取的行数

filtered

表示返回结果的行数占读取行数的百分比,值越大越好。

常见业务处理

删除重复数据

场景:删除同一订单,同一商品的多条评论,只保留最早一条。

  1. SELECT order_id,product_id,COUNT(*)
  2. FROM product_comment
  3. GROUP BY order_id,product_id HAVING COUNT(*)>1
  1. DELETE a
  2. FROM product_comment a JOIN(
  3. SELECT order_id,product_id,MIN(comment_id) as comment_id
  4. FROM product_comment
  5. GROUP BY order_id,product_id
  6. HAVING COUNT(*)>1
  7. ) b ON a.order_id = b.order_id AND a.product_id = b.product_id AND a.comment_id>b.comment_id

捕获有问题的 sql

慢日志查询

  1. # 设置慢查询日志文件位置
  2. SET GLOBAL slow_query_log_file = /sql_log/slow_log.log;
  3. # 设置未使用索引查询时记录
  4. SET GLOBAL log_queries_not_using_indexes = ON;
  5. # 设置语句执行时间超过0.001秒时记录
  6. SET GLOBAL long_query_time = 0.001;
  7. # 开启
  8. SET GLOBAL low_query_log = ON;
  1. mysql> show variables like 'slow_query%';
  2. +---------------------+--------------------------------------+
  3. | Variable_name | Value |
  4. +---------------------+--------------------------------------+
  5. | slow_query_log | ON |
  6. | slow_query_log_file | /var/lib/mysql/44cba697f41b-slow.log |
  7. +---------------------+--------------------------------------+
  8. 2 rows in set (0.01 sec)
  9. mysql> show variables like "long_query_time";
  10. +-----------------+----------+
  11. | Variable_name | Value |
  12. +-----------------+----------+
  13. | long_query_time | 0.010000 |
  14. +-----------------+----------+
  15. 1 row in set (0.00 sec)

分析慢查询日志

由于慢查询日志可能会生成大量且重复的内容,逐条查看不现实,可以使用 mysqldumpslow 工具,合并查看日志。

  1. mysqldumpslow /var/lib/mysql/44cba697f41b-slow.log

备份

备份可以分为逻辑备份和物理备份。

逻辑备份的结果为sql语句,适用于所有存储引擎,但比较慢

物理备份是对数据库目录的拷贝,对于内存表,只能备份结构

mysqldump

xtrabackup

集群

目的:增加额外的服务器,同一集群中的服务器有相同的数据,任一服务器宕机后其他服务器可以取代。

主从复制

1. 主库db的更新事件(update、insert、delete)被写到binlog
2. 主库创建一个binlog dump thread,把binlog的内容发送到从库
3. 从库启动并发起连接,连接到主库
4. 从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log
5. 从库启动之后,创建一个SQL线程,从relay log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db

注意有的版本可能不会默认开启 binlog 二进制日志,需要手动开启。如果没有在一开始就配置,则需要关掉 mysql 服务重启。

配置步骤:

master:

  1. # 开启二进制日志
  2. # vi /etc/my.cnf
  3. [mysqld]
  4. log-bin = /data/mysq/log/mysql-bin # 日志路径和前缀,配置这项就会开启二进制日志。
  5. server-id = 1     # 必需

slave:

  1. # vi /etc/my.cnf
  2. [mysqld]
  3. relay-log = /data/mysq/log/relay-bin
  4. read_only = on # 禁止写操作
  5. server-id = 2
  6. skip_slave_start = on # 禁止自动启动复制链路,当服务器启动后,检查无误,再手动启动。

用于 IO 进程连接 master 服务器获取 binlog 日志,仅需要 replication slave 权限即可。

  1. create user 'repl'@'ip' identified by 'password';
  2. grant replication slave on *.* to 'repl'@'ip';
  1. mysql> change master to master_host='ip', master_user='repl', master_password='password', master_log_file='mysql-bin.00000x', master_log_pos=xxx;
  2. mysql> start slave;
  3. mysql> show slave status;
添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注