MySQL学习笔记

基础操作

创建数据库

CREATE DATABASE db_name;

切换数据库

USE db_name;

创建表

CRETAE TABLE table_name(column type,...);

查看表结构

DESC table_name;

修改表名称

ALTER TABLES old_table_name RENAME TO new_table_name;

显示所有数据库

SHOW DATABASES;

显示所有表

SHOW TABLES;

修改数据库默认编码

ALTER DATABASE db_name DEFAULT CHARACTER SET encoding;

给表添加列

ALTER TABLE table_name ADD new_column type,…;

给表删除列

ALTER TABLE table_name DROP columnname;

插入数据

插入所有字段

INSERT INTO table_name VALUES(colum1, .....);

插入部分字段

INSERT table_name(colum1,..) VALUES(value1,...);

修改数据

UPDATE tableble_name SET columnname=value WHERE 条件;

删除全表

DELETE FROM table_name;
  • 可以带条件删除
  • 只能删除表的数据,不能删除表的约束
  • 可以回滚
TRUNCATE TABLE table_name;
  • 不能带条件删除
  • 既可以删除表的数据,也可以删除表的约束
  • 不可以回滚

表

修改表

alter table TABLENAME opt
修改字段类型 change old_field new_field new_type
添加字段 add field new_field type
添加字段并指定位置 add new_field new_type [完整性约束] [first | after 原有字段]
add [unique | fulltext | spatial] index index_name (field[len]) [asc | desc]
修改字段并指定位置 modify field type [完整性约束] [first | after 原有字段]
删除字段 drop field
删除外键 drop foreign key foreign_name
修改表明 rename new_table_name

视图

create [algorithm = {undefined | merge | temptable}]
       view view_name [{field...}]
       as select 语句
       [with [cascaded | local] check option];

查询

查询所有列

SELECT * FROM table_name;

查询指定列

SELECT column1,… FROM table_name;

查询时指定别名

SELECT column AS ‘column_new’,… FROM table_name AS table_name_new;

查询时添加常量列

SELECT column1,…’常量列’ FROM table_name;

查询时合并列

SELECT columname, (column1+column2) FROM table_name;
  • column1和column2必须为数值类型

查询时去除重复列

SELECT DISTINCT column FROM table_name;
SELECT DISTINCT(column) FROM table_name;
  • 条件查询(WHERE)
  • 逻辑条件:and(与) or(或)
SELECT * FROM table_name WHERE column1=value AND column2=value 
SELECT * FROM table_name WHERE column1=value OR column2=value
  • 比较条件: > < >= <= <>(不等于) between and
SELECT * FROM table_name WHERE column > value1 AND column < valu2;
SELECT * FROM table_name WHERE column BETWEEN value1 AND value2;
  • 判空条件(null 空字符串) is null, is not null, =’’, <>’’
SELECT column,… FROM table_name WHERE column is null;
  • 模糊条件(LIKE)
  • % 任意个字符
  • _ 一个字符
SELECT * FROM table_name WHERE column LIKE “”

聚合查询

常用聚合函数

函数名 功能
SUM() 求和
AVG() 求平均值
MAX() 最大值
MIN() 最小值
COUNT() 计数
COUNT(*) 列数
COUNT(column) 行数

分页查询(LIMIT 起始行, 查询几行)

  • 起始行从0开始
  • 分页: 当前页 每页显示条数
  • 分页查询当前页的数据sql:SELECT * FROM table_name LIMIT (当前页-1)*每页显示条数,每页显示条数;
SELECT * FROM table_name LIMIT start_line, count_line;

查询排序

SELECT * FROM table_name ORDER BY column ASC/DESC;

多列排序条件

SELECT * FROM table_name ORDER BY column1 ASC/DESC, column2 ASC/DESC;
  • 先按column1排序,对column1值相同的行,按column2排序

分组查询(GROUP BY)

SELECT column,COUNT(column/*) FROM table_name GROUP BY column;

分组查询后筛选

SELECT column,COUNT(column/*) FROM table_name GROUP BY column HAVING COUNT(column/*);

表约束

唯一:

  • unique:不可可重复,可以为空,一张表可以有多个

主键: 非空+unique

  • primary key:不可重复,不可为空,一张表只能有一个
    自增长
CREATE TABLE table_name(column type auto_inrement);
CREATE TABLE table_name(column type zerofill auto_inrement);
  • 零填充不能影响自增长约束
DELETE FROM table_name;
  • 可以影响自增长约束
TRUNCATE TABLE table_name;
  • 外键约束
CREATE TABLE table_name(
column type,
CONSTRAINT column_fk FOREIGN KEY(column) REFERENCES table_name_fk(column)
           外键名称   外键                           参考表 (参考字段));
  • 有外键约束时:
  1. 添加数据时:先添加主表(参考表),再添加副表
  2. 修改数据时:先修改副表,再修改主表(参考表)数据
  3. 删除数据时:先删除副表,再删除主表(参考表)数据
  • 级联操作
  1. 有外键约束时,通过修改或删除主表,影响副表
CREATE TABLE table_name( column type, CONSTRAINT column_fk FOREIGN KEY(column) REFERENCES table_name_fk(column) ON UPDATE CASCADE[ ON DELETE CASCADE]);
                                                    外键名称               外键                参考表(参考字段)

级联修改:ON UPDATE CASCADE
级联删除:ON DELETE CASCADE

数据库设计

需求分析

需求设计
概要设计
抽取实体:业务模型->实体模型(类)
数据库设计:业务模型/实体模型->数据模型()硬盘
详细设计
类详细,属性,方法
三大范式
设计原则:设计的表尽量遵守三大范式
第一范式:表中每个字段必须是不可分割的独立单元
第二范式:在第一范式的基础上,非主属性完全函数依赖于任何一个候选码
第三范式:在第二范式的基础上,非主属性既不传递依赖于码,也不函数依赖于码

MySQL存储过程

  • 执行效率快,在服务端执行
  • 移植性差

创建存储过程

DELIMITER $     声明结束符
CREATE PROCEDURE proname()
BEGIN
SELECT * FROM table_name;
...
END $

执行存储过程

CALL proname();
参数
IN  输入参数,可以携带数据到存储过程中
OUT     输出参数,可以从存储过程中返回结果
INOUT   输入输出参数,既有输入功能,也有输出功能

带有存储输入参数的存储过程

DELIMITER $
CREATE PROCEDURE proin(IN col  type)
BEGIN
SELECT * FROM table_name WHERE column=col;
END $

CALL proin(col);

带有输出参数的存储过程

DELIMITER $
CREATE PROCEDURE proout(OUT col type)
BEGIN
 SET col = 'Hello MySQL!';
END $

删除存储过程

DROP PROCEDURE proout;

mysql变量

全局变量(内置变量)

SQL 意义
SHOW varibales 查看所有全局变量
select @@变量名 查看某个全局变量
set 变量名=新值 修改全局变量
character_set_client mysql服务器的接收数据的编码
character_set_results mysql服务器输出数据的编码

会话变量:只存在于当前客户端与数据库服务端的依次连接中,如果连接断开,那么会话变量全部丢失

SET @var='value';

局部变量:在存储过程中使用的变量

  1. 定义一个会话变量name
  2. 使用name会话变量接受存储过程的返回值

CALL proout(@name);
SELECT @name;

带有输入输出参数的存储过程

DELIMITER $
CREATE PROCEDURE proinout(INOUT var type)
BEGIN
SELECT var;
SET var = value;
END $

SET @var=value;
CALL proinout(@var)

带有条件判断的存储过程

输入整数,返回对应星期天

DELIMITER $
CREATE PROCEDURE proif(IN num INT, OUT str VARCHAR(20))
BEGIN
IF num=1 THEN
SET str='星期一';
IF num=2 THEN
SET str='星期二';
IF num=3 THEN
SET str='星期三';
ELSE
SET str='输入错误';
END IF;
END $

CALL proif(1,@str);

SELECT @str;

带有循环功能的存储过程

输入数值,求从一到该数值的累加和

DELIMITER $
CREATE PROCEDURE prowhile(IN num INT, OUT result INT)
BEGIN
// 定义一个局部变量
DECLARE i INT DEFAULT 1;
DECLARE vsum INT DEFAULT 0;
WHILE i<=num DO
SET vsum=vsum+i;
SET i=i+1;
END WHILE;
SET result=vsum;
END $

CALL prowhile(122, @result)

SELECT @result;

MySQL触发器

当操作某张表时,同时触发一些动作
插入数据后,同时向日志表中插入

  • 创建触发器(插入)
CREATE TRIGGER triname AFTER INSERT ON table_name FOR EACH ROW
INSERT INTO log_table_name(clomn,...) VALUES('INSERT');
  • 创建触发器(修改)
CREATE TRIGGER triname AFTER UPDATE ON table_name FOR EACH ROW
INSERT INTO log_table_name(clomn,...) VALUES('update');
  • 创建触发器(删除)
CREATE TRIGGER triname AFTER DELETEON table_name FOR EACH ROW
INSERT INTO log_table_name(clomn,...) VALUES('delete');

mysql权限

修改密码

UPDATE USER SET PASSWORD=PASSWORD('new_passwd') WHERE USERN='USErname';

分配权限

GRANT SELECT ON DATABASE.table TO 'USEr'@'accounttype' IDENTIFIED BY 'password';
GRANT DELETE ON DATABASE.table TO 'USEr'@'accounttype' IDENTIFIED BY 'password';

mysql备份和还原

不需要登录

MYSQLDUMP -u USErname -p DATABASE > path
MYSQL -u USErname -p DATABASE < path
  • 复制表
CREATE TABLE table_name LIKE tablename;

事务

查询正在执行的事务(kill事务的线程ID（trx_mysql_thread_id）)

SELECT
	* 
FROM
	information_schema.INNODB_TRX;

查看正在锁的事务

SELECT
	* 
FROM
	INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

SELECT
	* 
FROM
	INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

查看事务等待状况

SELECT
	r.trx_id waiting_trx_id,
	r.trx_mysql_thread_id waiting_thread,
	r.trx_query waiting_query,
	b.trx_id blocking_trx_id,
	b.trx_mysql_thread_id blocking_thread,
	b.trx_query blocking_query 
FROM
	information_schema.innodb_lock_waits w
	INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
	INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

查看更具体的事务等待状况

SELECT
	b.trx_state,
	e.state,
	e.time,
	d.state AS block_state,
	d.time AS block_time,
	a.requesting_trx_id,
	a.requested_lock_id,
	b.trx_query,
	b.trx_mysql_thread_id,
	a.blocking_trx_id,
	a.blocking_lock_id,
	c.trx_query AS block_trx_query,
	c.trx_mysql_thread_id AS block_trx_mysql_tread_id
FROM
	information_schema.innodb_lock_waits a
	LEFT JOIN information_schema.innodb_trx b ON a.requesting_trx_id = b.trx_id
	LEFT JOIN information_schema.innodb_trx c ON a.blocking_trx_id = c.trx_id
	LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id
	LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id 
ORDER BY
	a.requesting_trx_id;

查看未关闭的事务

SELECT
	a.trx_id,
	a.trx_state,
	a.trx_started,
	a.trx_query,
	b.id,
	b.USER,
	b.HOST,
	b.db,
	b.command,
	b.time,
	b.state,
	b.info 
FROM
	information_schema.innodb_trx a
	LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id 
WHERE
	b.command = 'sleep';

未关闭事务信息

SELECT
	t1.trx_id,
	t1.trx_started,
	t1.trx_mysql_thread_id,
	t3.event_id,
	t3.end_event_id,
	t3.sql_text,
	concat( 'mysql --login-path=3306 -e ''kill', t1.trx_mysql_thread_id, '''' ) 
FROM
	information_schema.innodb_trx t1
	LEFT JOIN `performance_schema`.threads t2 ON t1.trx_mysql_thread_id = t2.processlist_idleft
	JOIN `performance_schema`.events_statements_history t3 ON t2.thread_id = t3.thread_id 
WHERE
	t1.trx_started < date_sub( now(), INTERVAL 1 MINUTE ) 
	AND t1.trx_operation_state IS NULL 
	AND t1.trx_query IS NULL 
ORDER BY
	event_id DESC;

查看某段时间以来未关闭事务

SELECT
	trx_id,
	trx_started,
	trx_mysql_thread_id 
FROM
	information_schema.innodb_trx 
WHERE
	trx_started < date_sub( now(), INTERVAL 1 MINUTE ) 
	AND trx_operation_state IS NULL 
	AND trx_query IS NULL;

SQL优化

使用union all替代or

select id from t where num=10 or num=20;
select id from t where num=10
union all
select id from t where num=20;

查询优化器

  • 根据条件找到所有可能使用的索引
  • 计算全表扫描的代价
  • 计算使用不同索引的代价
  • 对比各种执行方案,找出成本最低的一个

慎用in和not in

select id from t1 where num in (select id from t2 where id > 10);
select id from t1, (select id from t1 where id > 10) t2 where t1.id=t2.id;

将字段函数计算放在等号右边

使用符合索引的第一个字段作为查询条件

使用exists替代in

select num from a where num in (select num from b);
select num from a where exists (select 1 from b where num=a.num);

多使用数字类型字段

全局锁

flush tables with read lock(FTWRL)
set global readonly=true

表级锁

表锁

lock tables xxx read/writ // 加锁
unlock tables xxx read/writ // 释放锁

元数据锁MDL(metadata lock)

  • 进行增删改查时自动加上，保证数据读写正确性
  • 读写锁之间互斥，保证表结构变更的正确性

行级锁

  • 在需要的时候加上，事务结束后释放

帮助

?CONTENTS

MySQL索引

查看索引

SHOW INDEX FROM table_name;

创建索引

ALTER TABLE table_name ADD INDEX index_name(column_list);
CREATE INDEX index_name ON table_name(column_list);

创建唯一索引

ALTER TABLE table_name ADD UNIQUE(column_list);
CREATE UNIQUE INDEX index_name ON table_name(column_list);

创建主键索引

ALTER TABLE table_name ADD PRIMARY KEY(column_list);

删除索引

ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name ON table_name;

删除主键

ALTER TABLE table_name DROP PRIMARY KEY;

MySQL视图

  • 创建视图
CREATE VIEW view_name AS SELECT * FROME table_name WHERE ....;
  • 删除视图
DROP VIEW view_name;
  • 修改视图
ALTER VIEW view_name;

MySQL内置函数

字符串函数

功能 函数名
连接 CONCAT(string[,...])
转小写 LCASE(string)
转大写 UCASE(string)
长度 LENGTH(string)
去除左端空格 LTRIM(string)
去除右端空格 RTRIM(string)
重复count次 REPEAT(string, count)
替换 REPLACE(str, search_str, replace_str)
从position开始,截取length个字符 SUBSTRING(str, position[,length])
生成count个空格 SPACE(count)

数学函数

功能 函数名
十进制转二进制 BIN(decimal_number)
向上取正 CEILING(number)
向下取正 FLOOR(number)
取最大值 MAX(num1, num2)
取最小值 MIN(num1, num2)
开平方 SQRT(number)
返回0-1内的随机值 RAND()

日期函数

功能 函数名
返回当前日期 CURDATE()
返回当前时间 CURTIME()
返回当前日期时间 NOW()
返回当前date的UNIX时间戳 UNIX_TIMESTAMP(date)
返回UNIX时间戳日期值 FROM_UNIXTIME()
返回date为一年中的第几周 WEEK(date)
返回date的年份 YEAR(date)
返回expr和expr2之间的天数 DATEDIFF(expr, expr2)

MySQL预处理

  • 设置预处理
PREPARE pre_name FROM 'SELECT ... FROM table_name WHERE ?';
  • 设置变量
SET @var=value;
  • 执行预处理
EXECUTE pre_name using @var;
  • 删除预处理
DROP PREPARE pre_name;

MySQL事务处理(MyISAM引擎不支持)

功能 语句
关闭自动提交功能 SET AUTOCOMMIT=0;
创建还原点 SAVEPOINT p_name;
回滚还原点 ROLLBACK TO p_name;
回滚所有 ROLLBACK;
  • 重排AUTO_INCREMENT值
    清空表时用
TRUNCATE TABLE table_name;
ALTER TABLE table_name AUTO_INCREMENT=1;
  • 正则表达式
SELECT * FROM * WHERE col REGEXP "recp";
  • 提取随机行
SELECT * FROM table_name ORDER BY RAND();
  • 统计更多信息
SELECT * FROM table_name GROUP BY ... WITH ROLLUP;
  • 数值之间逻辑位运算
SELECT col0, BIT_OR(col1) FROM table_name GROUP BY col0;
SELECT col0, BIT_AND(col1) FROM table_name GROUP BY col0;
  • MyISAM引擎不支持外键

优化SQL语句

  • 各种SQL执行频率
SHOW [SESSION|GLOBAL] STATUS;
SESSION (默认) 当前连接
GLOBAL 自数据库启动
  • Com_XXX表示XXX语句执行次数
字段名 意义
Com_select 一次查询只累计加1
Com_update 执行update次数
Com_insert 批量插入只算一次
Com_delete 执行delete次数
  • 只针对InnoDB存储引擎
字段名 意义
Innodb_rows_read 执行select操作次数
Innodb_rows_updated 执行update操作次数
Innodb_rows_inserted 执行insert操作次数
Innodb_rows_deleted 执行delete操作次数
字段名 意义
CONNECTIONS 连接MySQL的次数
Uptime 数据库已运行的时间(秒)
Slow_queries 慢查询的次数
  • 定位执行效率较低的SQL语句
EXPLAIN SELECT * FROM table_name WHERE ...;
DESC SELECT * FROM table_name WHERE ...;

MySQL优化

索引优化

  • MyISAM独立表空间
  • InnoDB共享表空间

使用索引

  • 查询条件中用到复合索引的第一列
  • 列名是索引, 使用column_name IS NULL 将使用索引
  • 使用LIKE的查询, 使用常量并且只有%号不在第一个字符, 索引才可能被使用
  • 对大的文本进行搜索, 使用全文索引而不使用LIKE'%...%'

不使用索引

  • MySQL估计使用索引比全表扫描更慢, 则不使用索引
  • 使用MEMORY/HEAP表并且WHERE条件中不使用'=', 则不会使用索引
  • 用OR分割的条件中, 前面列有索引, 后面列没有索引, 则不使用索引
  • WHERE子句的条件中, 用到的列不是复合索引的第一列
  • 使用LIKE的查询, 使用常量并且只有%号在第一个字符
  • 把数值型常量赋值给一个字符串型的列, 即使字符串型的列上有索引, 也不会用到

查看索引使用情况

SHOW STATUS LIKE 'Handler_read%';
Handler_read_first  16
  • 一行被索引值读的次数

Handler_read_key 1079
Handler_read_last 0

  • 值越高 查询运行效率越低

Handler_read_next 152
Handler_read_prev 0
Handler_read_rnd 130
Handler_read_rnd_next 4166

分析表(检查表是否有错误)

CHECK TABLE table_name[, table_name]...[option] = {QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

优化表

OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG] TABLE table_name[, table_name]

批量导入大量数据

LOAD DATA INFILE 'file_name' INTO TABLE table_name;

批量导出大量数据

SELECT ... FROM table_name INTO OUTFILE 'file_name'

提高导入效率

  • 将导入数据按主键顺序排列
  • 关闭唯一性检验(必须保证唯一键唯一)
set unique_checks=0
  • 关闭自动提交
set autocommit=0

优化INSERT语句

  • 尽量插入多个值(减少频繁INSERT对数据库连接,关闭等的损耗)
  • 使用INSERT DELAYED(马上执行)

优化GROUP BY 语句

  • 查询包含GROUP BY但不需要排序, 使用ORDER BY NULL来禁止排序

优化嵌套查询(使用JOIN)

表优化

  • 对常用列建索引

数据库优化

  • 分库分表
  • 分区

MySQL优化(MYISAM)

读锁
写锁

MySQL服务器优化

四种字符集

binary log日志

slow log慢查询日志
log_slow_queries=slow.log
long_query_time=5

查询优化

1、使用慢查询日志，找出执行慢的查询。
https://p1.pstatp.com/large/pgc-image/ed50483ead2d44ef84c7c40271e21fec
关于Mysql 数据库查询优化的24条优化建议
2、使用 EXPLAIN 来决定查询功能是否合适。
3、经常测试你的查询，看是否需要做性能优化
性能可能会随着时间的变化而变化。
4、避免在整个表上使用count(*) ，它可能会将整个表锁住。
5、保持查询一致，这样后续类似的查询就能使用查询缓存了。
6、如果合适，用 GROUP BY 代替 DISTINCT。
7、在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引。
8、保证索引简单，不要在同一列上加多个索引。
9、有时，MySQL 会选择错误的索引，这种情况使用 USE INDEX。
https://p1.pstatp.com/large/pgc-image/46efd30ffaba46c99c2309f6dcc98759
关于Mysql 数据库查询优化的24条优化建议
10、使用 SQL_MODE=STRICT 来检查问题。
11、索引字段少于5个时，UNION 操作用 LIMIT，而不是 OR。
12、使用 INSERT ON DUPLICATE KEY 或 INSERT IGNORE 来代替 UPDATE，避免 UPDATE 前需要先 SELECT。
13、使用索引字段和 ORDER BY 来代替 MAX。
14、避免使用 ORDER BY RAND()。
15、LIMIT M,N 在特定场景下会降低查询效率，有节制使用。
16、使用 UNION 来代替 WHERE 子句中的子查询。
17、对 UPDATE 来说，使用 SHARE MODE 来防止排他锁。
18、重启 MySQL 时，记得预热数据库，确保将数据加载到内存，提高查询效率。
19、使用 DROP TABLE ，然后再 CREATE TABLE ，而不是 DELETE FROM ，以删除表中所有数据。
20、最小化你要查询的数据，只获取你需要的数据，通常来说不要使用 *。
21、考虑持久连接，而不是多次建立连接，已减少资源的消耗。
22、基准查询，包括服务器的负载，有时一个简单的查询会影响其他的查询。
23、当服务器的负载增加时，使用SHOW PROCESSLIST来查看慢的/有问题的查询。
24、在存有生产环境数据副本的开发环境中，测试所有可疑的查询

锁

乐观锁-++                               ++-表锁
        |                               |
        |                               |
        |+加锁机制+---+      +---+锁粒度++-页锁
        |             |      |          |
        |             |      |          |
悲观锁-++             |======|          ++-行锁
                      |  锁  |
                      |======|
共享锁-++             |      |          ++-记录锁
        |             |      |          ++-gap锁
        |+加锁机制+---+      +---+锁模式++-next-key锁
        |                               ++-意向锁
排它锁-++                               ++-插入意向锁

幻读

一次事务中,多次查询的结果集不一致

快照读

普通select * from xx where xx;属于快照读

将历史数据存一份快照,从快照中读

当前读(next-key锁)

  • 记录锁(行锁),加在索引上
  • 间隙锁
select * from xx where xx for update;
select * from xx where xx lock in share mode;
insert
update
delete

MVCC

write UPDATE和DELETE

  • 每个write操作使对象产生新的版本
  • 每个并发read操作依赖于隔离级别读取不同的版本

锁监控

  • 15s输出一次
  • 服务重启后自动复位

标准监控

判断是否存在名为innodb_monitor的数据表,来开启标准监控

create table innodb_monitor (a int) engin=innodb;
drop table innodb_monitor;

set global innodb_status_output=on;
set global innodb_status_output=off;

锁监控

create table innodb_lock_monitor (a int) engin=innodb;
drop table innodb_lock_monitor;

set global innodb_status_output=on;
set global innodb_status_output_locks=on;
set global innodb_status_output_locks=off;

表空间监控

create table innodb_tablespace_monitor (a int) engin=innodb;
drop table innodb_tablespace_monitor;

表监控

create table innodb_table_monitor (a int) engin=innodb;
drop table innodb_table_monitor;

规范

一、基础规范

  • 表存储引擎必须使用InnoDB

  • 表字符集默认使用utf8，必要时候使用utf8mb4
    解读：
    （1）通用，无乱码风险，汉字3字节，英文1字节
    （2）utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它

  • 禁止使用存储过程，视图，触发器，Event
    解读：
    （1）对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层
    （2）调试，排错，迁移都比较困难，扩展性较差

  • 禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径

  • 禁止在线上环境做数据库压力测试

  • 测试，开发，线上数据库环境必须隔离

二、命名规范

  • 库名，表名，列名必须用小写，采用下划线分隔

解读：abc，Abc，ABC都是给自己埋坑

  • 库名，表名，列名必须见名知义，长度不要超过32字符
    解读：tmp，wushan谁TM知道这些库是干嘛的

  • 库备份必须以bak为前缀，以日期为后缀

  • 从库必须以-s为后缀

  • 备库必须以-ss为后缀

三、表设计规范

  • 单实例表个数必须控制在2000个以内

  • 单表分表个数必须控制在1024个以内

  • 表必须有主键，推荐使用UNSIGNED整数为主键

  • 潜在坑：删除无主键的表，如果是row模式的主从架构，从库会挂住

  • 禁止使用外键，如果要保证完整性，应由应用程式实现
    解读：外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈

  • 建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据
    解读：具体参加《如何实施数据库垂直拆分》

四、列设计规范

  • 根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节

  • 根据业务区分使用char/varchar
    解读：
    （1）字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高
    （2）字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间

  • 根据业务区分使用datetime/timestamp
    解读：前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime

  • 必须把字段定义为NOT NULL并设默认值
    解读：
    （1）NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化
    （2）NULL需要更多的存储空间
    （3）NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑

  • 使用INT UNSIGNED存储IPv4，不要用char(15)

  • 使用varchar(20)存储手机号，不要使用整数
    解读：
    （1）牵扯到国家代号，可能出现+/-/()等字符，例如+86
    （2）手机号不会用来做数学运算
    （3）varchar可以模糊查询，例如like ‘138%’

  • 使用TINYINT来代替ENUM
    解读：ENUM增加新值要进行DDL操作

五、索引规范

  • 唯一索引使用uniq_[字段名]来命名

  • 非唯一索引使用idx_[字段名]来命名

  • 单张表索引数量建议控制在5个以内
    解读：
    （1）互联网高并发业务，太多索引会影响写性能
    （2）生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引
    （3）异常复杂的查询需求，可以选择ES等更为适合的方式存储

  • 组合索引字段数不建议超过5个
    解读：如果5个字段还不能极大缩小row范围，八成是设计有问题

  • 不建议在频繁更新的字段上建立索引

  • 非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引
    解读：踩过因为JOIN字段类型不一致，而导致全表扫描的坑么？

  • 理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)

六、SQL规范

  • 禁止使用select *，只获取必要字段
    解读：
    （1）select *会增加cpu/io/内存/带宽的消耗
    （2）指定字段能有效利用索引覆盖
    （3）指定字段查询，在表结构变更时，能保证对应用程序无影响

  • insert必须指定字段，禁止使用insert into T values()
    解读：指定字段插入，在表结构变更时，能保证对应用程序无影响

  • 隐式类型转换会使索引失效，导致全表扫描

  • 禁止在where条件列使用函数或者表达式
    解读：导致不能命中索引，全表扫描

  • 禁止负向查询以及%开头的模糊查询
    解读：导致不能命中索引，全表扫描

  • 禁止大表JOIN和子查询

  • 同一个字段上的OR必须改写问IN，IN的值必须少于50个

  • 应用程序必须捕获SQL异常
    解读：方便定位线上问题

MySQL脚本

碎片整理-存储过程

DELIMITER $$
DROP PROCEDURE
IF
    EXISTS `mysql`.`sp_optimize_tables2` $$ CREATE PROCEDURE `mysql`.`sp_optimize_tables2` (
        IN db_name VARCHAR ( 255 )) BEGIN-- To optimize all the tables in exact database.
    DECLARE
        cnt INT DEFAULT 0;
    DECLARE
        i INT DEFAULT 0;
    SELECT
        count(*) AS total 
    FROM
        information_schema.TABLES 
    WHERE
        table_schema = db_name INTO cnt;
    WHILE
            i < cnt DO-- Get the table's exact name.
            
            SET @stmt = concat( 'select table_name from information_schema.tables where table_schema = ''', db_name, ''' order by table_name asc limit ', i, ',1 into @tb_name' );
        PREPARE s1 
        FROM
            @stmt;
        EXECUTE s1;
        DROP PREPARE s1;
        
        SET @stmt = '';
        
        SET @stmt = concat( 'alter table ', db_name, '.', @tb_name, ' engine=innodb' );
        PREPARE s1 
        FROM
            @stmt;
        EXECUTE s1;
        DROP PREPARE s1;
        
        SET @stmt = '';
        
        SET i = i + 1;
        
    END WHILE;-- Refresh tables.
    FLUSH TABLES;

END $$DELIMITER;
posted @ 2019-10-27 12:09  薛定谔的博客  阅读(135)  评论(0)  编辑  收藏  举报