规范

alibabaMysql开发规范和常见问题处理
 参考1

建模工具PDMan

还是可视化视图清晰点，还可以导出自动生成sql。

Basic Knowledge

存储引擎

MyISAM

MyISAM是MySQL的默认数据库引擎（5.5版之前），它不支持事务和表锁设计，支持全文索引，主要面向一些在线分析处理(OLAP)数据库应用。说白了主要就是查询数据，对数据的插入，更新操作比较少。

InnoDB

MySQL的默认存储引擎，它支持事务，其设计目标主要面向在线事务处理(OLTP)的应用。

常用命令

# 设置主键自增初始值
alter table t_ins_sequence AUTO_INCREMENT = 100000;
# 修改表名
alter table sys_user_role rename to sys_user_role_rel;
# 查询数据库里面各个表的大小
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name',
    table_rows AS 'Number of Rows',
    CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size',
    CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' ,
    CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total'
FROM information_schema.TABLES
WHERE table_schema LIKE 'inspect_db';
# 修改主键自增
alter table word_fetch_queue modify queue_id int auto_increment;
# 修改主键被外键约束show create table table_name;

SET FOREIGN_KEY_CHECKS = 0;
# 约束解除，进行修改，完了还原
SET FOREIGN_KEY_CHECKS = 1;
# 查询表的建表DDL
show create table table_name;
# 查询表的索引
SHOW INDEX FROM <tablename>;
# 查看表空间模式
show variables like '%innodb_file_per_table%' ;
# Mysql基本可以将schema当成database；下面二个命令都会创建对应数据库
create schema xxxName;
create databse xxxName;
# 查询某个字符开头的所有表
show tables like 'ACT_%';

mysqladmin的用法

1	mysqladmin -u root -p status

然后输入密码，命令参考

Using Options on the Command Line(在命令行使用可选项)

横线和下划线在命令中是可以互换来使用的，比如 –skip-grant-tables and –skip_grant_tables效果相同，但是官方默认使用的是-，看起来也比较直观。
但是要注意的是，前面的起引导作用的横线不能被替换，–skip不能替换为__skip

执行计划EXPLAIN

Mysql执行计划教程

Mysql的级联更新

update tb1,
tb2
set tb1.a=tb2.a,tb1.b=tb2.b
where tb1.id=tb2.id;

update tb1,
(select * from tb2 where xxx) as tb2
set tb1.a=tb2.a,tb1.b=tb2.b
where tb1.id=tb2.id;

update (select * from tb1 group by C) as temp,
       tb2,
       tb1
set tb1.a = tb2.a,
    tb1.b = tb2.b
where tb1.id = tb2.id and
      temp.id = tb2.id;

update tb1 left join tb2 on tb1.id = tb2.id
set tb1.a = tb2.a,
    tb1.b = tb2.b
where xxx;

Mysql和Oracle的Sql的不同之处

delete在oracle中是支持别名的，mysql不支持

1	delete from table_name tn;// mysql error

Oracle可以直接sequence，Mysql不能，如果非要在Mysql使用类似Oracle的sequence功能，可以通过一张表来模拟，参考：
https://blog.csdn.net/u012373815/article/details/78179411

Mysql没有类似Oracle一样的表空间概念，mysql的表空间分为两个文件一个是*.ibd文件存储数据索引…等数据信息，且只针对一张表，还有一个文件是*.frm这个文件是存储表的元数据。

time、timestamp、date、datetime

DATE: It is used for values with a date part but no time part. MySQL retrieves and displays DATE values in YYYY-MM-DD format. The supported range is 1000-01-01 to 9999-12-31.

DATETIME: It is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format. The supported range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

TIMESTAMP: It is used for values that contain both date and time parts. TIMESTAMP has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.

TIME: Its values are in HH:MM:SS format (or HHH:MM:SS format for large hours values). TIME values may range from -838:59:59 to 838:59:59. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

char vs varchar

MySQL - CHAR vs VARCHAR - What is the difference? CHAR is fixed length while VARCHAR is variable length. That means, a CHAR(x) string has exactly x characters in length, including spaces. A VARCHAR(x) string can have up to x characters and it cuts off trailing spaces, thus might be shorter than the declared length.

Mysql一对多数据显示成一行

使用GROUP_CONCAT

Mysql常用函数大全

常用函数

数据库的导入导出（数据迁移、数据备份恢复）

mysqldump导入导出

快速备份某个表

1
2
3

DROP TABLE IF EXISTS B;
CREATE TABLE B LIKE A;
INSERT INTO B SELECT * FROM A;

导出备份单个表

1	mysqldump -u root -p dbname tablename > table.sql

导出备份整个库

1	mysqldump -u root -p dbname > db.sql

导入通过mysql命令

1	mysql -u root -p vocabulary_enhancer_db < /mysql_sql_temp/old_bak.sql

临时表备份

1	CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name;

跨服务器导出导入数据

1	mysqldump --host=cdb-0bhxucw9.gz.tencentcdb.com --port=10069 -uroot -pfengORZ123 -C --databases kiwi_db \|mysql --host=localhost -uroot -pfengORZ123 kiwi_db

所有子查询都要用唯一别名

主键插入返回

<insert id="genSequence" parameterType="com.ccssoft.ngbip.sdk.core.entity.Sequence">
    insert into ${tableName} (stub) values ('b')
    <selectKey resultType="integer" keyProperty="id">
        select last_insert_id() as id
        <!-- select 'NEXT VALUE FOR MYCATSEQ_${tableName}' as id -->
    </selectKey>
</insert>

https://blog.csdn.net/czd3355/article/details/71302441/

表设计

外键设计

尽量避免使用外键，如果一定要使用外键的情况下，必须要考虑是否要在主表加上ON DELETE CASCADE或者ON UPDATE CASCADE，大部分情况下是必须要加上这个级联约束的，但是要注意的是，关系表中的外键，一定要建索引，否则在删除主键时级联删除外键会很慢很慢。

主键使用uuid和自增int的区别

http://www.mysqltutorial.org/mysql-uuid/
https://juejin.im/post/5c32a7a86fb9a049ad7734b3
最近在重构公司一个老的模块，重构当然要从数据库开始着手，关于数据表主键的实现方式选择，我想到的有二种方式，一种是uuid，一种是自增int，
先抛开是否分布式架构不说，选用uuid网上都会提到两个明显的弊端：
1、是数据暂用空间大.
2、数据无序性对索引这种树结构的存储有性能上的影响。

第一点我倒是觉得没什么大的影响，现在的硬盘已经不是缺那点点存储的问题了。
选用自增int的话，也有两个明显的弊端：
1、存在安全性问题，将一个已知主键加减某个值就得到另外一个主键了。
2、如果多个表数据合并，可能自增主键存在冲突。

真到了分布式的可以有第三方成熟的实现方案可供选择。
结论：尽量使用整型做主键，数据无序性的开销太大了，这点足以让整型的弊端再多，也必须竭力去解决弊端后选择整型做主键。

主键自增int的实现方式

通过全局自增序列实现

全局表设计：

1
2
3

id   int(20) auto_increment
    primary key,
stub char not null

stub用于标识不同表、不同业务
在利用last_insert_id()获取最新的ID，当对table进行insert操作时，返回具有Auto_increment（自动增长）特性的属性列的最新值。

主键int还是bigint

int的范围：从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。
bigint的范围：从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。

int的上限已经很大了，一般的应用不会超出这个范围，所以主键一般使用int，也可以节省存储。

分布式主键自增实现方案

美团Leaf
twitter的snowflake

创建和更新时间

必要时，尽量在表里面添加创建时间（create_time）和更新事件（update_time）

表的DDL维护成本高，必要时可以考虑多留几个扩展字段备用

https://www.jianshu.com/p/47a1d7998fe0

Mysql 的表尽量设置成 K-V (Key-Value) 结构，什么情况下适合设计成K-V结构？

表Key-value结构介绍
在存储一些数据结构经常变动的数据适合采用K-V结构。

为什么要尽量避免表字段为NULL值

表字段避免 null 值出现，null 值很难查询优化且占用额外的索引空间，推荐默认数字 0 代替 null。

字段类型的选择

尽量使用 INT 而非 BIGINT，如果非负则加上 UNSIGNED（这样数值容量会扩大一倍），当然能使用 TINYINT、SMALLINT、MEDIUM_INT 更好。
使用整数代替字符串类型
枚举类型的弊端

IP使用整型来存储

为什么推荐使用
注意最后一段：
The load on MySQL when inserting integer IPs could likely be slightly reduced by doing that conversion in your application, rather than using MySQL’s INET_ATON() function.
ip的转换可以考虑放到应用程序来做，而不是通过Mysql的函数做转换。

Mysql的分库分表，水平拆分-垂直拆分 todo

共享表空间和独享表空间的区别和优缺点

http://blog.itpub.net/15498/viewspace-2124040/

单表大小限制与表空间的关系

https://blog.csdn.net/wsh900221/article/details/80242299

timestamp 默认当前时间不能同时是not null

char 和 varchar 的区别

char列长度固定为创建表时声明的长度，长度值范围是1到255当char值被存储时，它们被用空格填充到特定长度。

非负值的字段注意使用无符号数

合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

布尔类型字段

表字段类型可以使用bit，同时类映射字段使用Boolean。

Mysql的int和bigint字段类型，映射到Java的Integer和Long类型时，勾选UNSIGNED无符号会导致越界转换

使用Mysql查询数据时，自动映射数据类型。
有时候Mysql的int字段，会正确映射到Java的Integer类型，有时候又会映射到Java的Long类型上，最后终于找到原因。
原来是Mysql的int字段，勾选了unsigned无符号选项，这样就导致int类型，变成了long类型。
同理，MySQL的bigint类型，本来应该映射到Java的Long类型。但是如果勾选了unsigned无符号，则也会映射为BigInteger类型。
————————————————
版权声明：本文为CSDN博主「dalu2048」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_42127613/article/details/84791794

命名

索引使用_ind结尾，字段的多个单词取单词的首字母组合，例如：sample 表 member_id 上的索引:sample_mid_ind。
主键约束: pk 结尾，_pk;
unique 约束:_uk 结尾，uk;
check 约束: _ck 结尾，ck;
外键约束: _fk 结尾，以 pri 连接本表与主表，_pri_fk;
数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取值含义与取值范围;
表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只
出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑;

Mysql关键字大全

https://blog.csdn.net/weixin_43201975/article/details/88953903

约束

为什么推荐外键约束放在应用层，不在数据库库?

https://learnku.com/articles/25116

索引

索引并不是越多越好，要根据查询有针对性的创建，考虑在 WHERE 和 ORDER BY 命令上涉及的列建立索引，可根据 EXPLAIN 来查看是否用了索引还是全表扫描
应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描
值分布很稀少的字段不适合建索引，例如 “性别” 这种只有两三个值的字段
字符字段太长的话，考虑用前缀索引
字符字段最好不要做主键 - 超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致;多表关联查询时，保证被关联的字段需要有索引。(即使双表 join 也要注意表索引、SQL 性能)

前缀索引

前缀索引解释
如果是对已经存在的表建前缀索引，要先去比较重复率再择优创建索引：

联合索引之最左原则（左前缀原则）

https://blog.csdn.net/wdjxxl/article/details/79790421
https://blog.csdn.net/Wuhaotian1996/article/details/80469353

多对多的关联表索引

一般是在二个关联的主键字典建唯一联合索引，避免重复数据，提升查询效率。

覆盖索引

Order by的时候，如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须扫表。

order by出现file_sort

order by 官方的优化文档
 Order by 优化
没有使用到索引来排序是，执行计划会显示file_sort。
需要重点注意的是，索引创建是也是有排序的，默认是ASC，如果Order by的排序和索引创建的时候指定的不一致，也会导致file_sort。

非联合索引不受order by后面的升序和降序影响到是否使用索引排序，联合索引的情况下应该考虑order by的升序降序是否和索引创建时一致。

适合建立聚集索引的要求

既不能绝大多数都相同，又不能只有极少数相同。

聚集索引和非聚集索引使用原则

动作描述使用聚集索引使用非聚集索引
列经常被分组排序应应
返回某范围内的数据应不应
一个或极少不同值不应不应
小数目的不同值应不应
大数目的不同值不应应
频繁更新的列不应应
外键列应应
主键列应应
频繁修改索引列不应应

时间类字段是否应该建索引

我自己测试了一下建索引确实是性能上有明显提升的，
没加索引：

1	[2019-12-25 15:13:59] 1 row retrieved starting from 1 in 592 ms (execution: 576 ms, fetching: 16 ms)

加上索引之后：

1	[2019-12-25 15:19:28] 1 row retrieved starting from 1 in 258 ms (execution: 239 ms, fetching: 19 ms)

什么情况下SQL不会使用到索引

like出现左匹配：’%XXX’
where语句中使用<>, !=, not in
where语句中使用 or，但是没有把or中所有字段加上索引，这种情况，如果需要使用索引需要将or中所有的字段都加上索引。
where语句中对字段表达式操作（包括使用一些函数，比如if()等）
使用索引的时候没有遵从索引的左前缀原则

SQL编写优化

避免 select *，将需要查找的字段列出来，这里并不是因为select * 就一定是性能明显更差的原因，具体解释参考https://blog.csdn.net/qq_38836118/article/details/88708933

SQL手工拼写的防止注入

varchar类型的字段，在拼写条件值得时候，外面用单引号包围起来，如：

1 2	+ objId + " and o.disp_object_type='" + objType + "'");

Problem Solution

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

大部分原因是由于数据库回收了连接，而系统的缓冲池不知道，继续使用被回收的连接所致的。
https://stackoverflow.com/questions/6865538/solving-a-communications-link-failure-with-jdbc-and-mysql
很有可能是jdk的版本有问题，比如我自己用到的：

换成常用的hostpot就可以了

也可能是数据连接池满了

1 2	show global status like 'Max_used_connections'; show variables like '%max_connections%';

也可以是时区问题：
mysql修改时区
jdbc连接的url可以加上时区：
https://www.cnblogs.com/coderLeo/p/12739535.html

required a bean of type ‘org.springframework.cache.CacheManager’ that could not be found.

需要再spring.foctories添加org.springframework.boot.autoconfigure.EnableAutoConfiguration配置，
至少需要配置RedisTemplate

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (111)

可能是数据库服务资源紧张挂掉了，mysqld进程我已查看都不在了，重启mysql就好了，当然也有其他原因也会导致这个报错。

delete语句子查询not exists是不会生效的

1
2
3

delete
from word_paraphrase_star_rel
where not exists(select * from word_paraphrase wp where wp.paraphrase_id = paraphrase_id);

需要改成：

delete
from word_paraphrase_star_rel
where paraphrase_id in (select tmp.paraphrase_id
                        from (select wpsr.paraphrase_id
                              from word_paraphrase_star_rel wpsr
                              where not exists(
                                      select *
                                      from word_paraphrase wp
                                      where wp.paraphrase_id = wpsr.paraphrase_id)) tmp)

You can’t specify target table ‘word_paraphrase_star_rel’ for update in FROM clause

delete
from word_paraphrase_star_rel
where paraphrase_id in (select wpsr.paraphrase_id
                        from word_paraphrase_star_rel wpsr
                        where not exists(
                                select * from word_paraphrase wp where wp.paraphrase_id = wpsr.paraphrase_id))

改成下面即可：

delete
from word_paraphrase_star_rel
where paraphrase_id in (select tmp.paraphrase_id
                        from (select wpsr.paraphrase_id
                              from word_paraphrase_star_rel wpsr
                              where not exists(
                                      select *
                                      from word_paraphrase wp
                                      where wp.paraphrase_id = wpsr.paraphrase_id)) tmp)

Show Status

MySQL的 show status命令
命令：show status like ‘%下面变量%’;

Aborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。

Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。

Connections 试图连接MySQL服务器的次数。

Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。

Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。

Delayed_writes 用INSERT DELAYED写入的行数。

Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。

Flush_commands 执行FLUSH命令的次数。

Handler_delete 请求从一张表中删除行的次数。

Handler_read_first 请求读入表中第一行的次数。

Handler_read_key 请求数字基于键读行。

Handler_read_next 请求读入基于一个键的一行的次数。

Handler_read_rnd 请求读入基于一个固定位置的一行的次数。

Handler_update 请求更新表中一行的次数。

Handler_write 请求向表中插入一行的次数。

Key_blocks_used 用于关键字缓存的块的数量。

Key_read_requests 请求从缓存读入一个键值的次数。

Key_reads 从磁盘物理读入一个键值的次数。

Key_write_requests 请求将一个关键字块写入缓存次数。

Key_writes 将一个键值块物理写入磁盘的次数。

Max_used_connections 同时使用的连接的最大数目。

Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。

Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。

Open_tables 打开表的数量。

Open_files 打开文件的数量。

Open_streams 打开流的数量(主要用于日志记载）

Opened_tables 已经打开的表的数量。

Questions 发往服务器的查询的数量。

Slow_queries 要花超过long_query_time时间的查询数量。

Threads_connected 当前打开的连接的数量。

Threads_running 不在睡眠的线程数量。

Uptime 服务器工作了多少秒。