0%

Mysql系列之基础知识

规范

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

建模工具PDMan

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

Basic Knowledge

存储引擎

MyISAM

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

InnoDB

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

常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 设置主键自增初始值
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的级联更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

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

主键插入返回

1
2
3
4
5
6
7
<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编写优化

SQL手工拼写的防止注入

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

1
+ 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);

需要改成:

1
2
3
4
5
6
7
8
9
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

1
2
3
4
5
6
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))

改成下面即可:

1
2
3
4
5
6
7
8
9
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 服务器工作了多少秒。