MySQL核心概念与优化技巧-优快云博客

mysql默认数据库

information_schema:主要存储系统中的一些数据库对象信息，比如用户表信息，列信息，权限信息，字符集信息，分区信息等
cluster：存储了系统的集群信息
mysql：存储了系统的用户权限信息

SQL分类

DDL(Data Definition Languages)：定义不同的数据段，数据库，表，列，索引
DML(Data Manipulation Language)：数据操作，增删改查
DCL(Data Control Language)：定义数据库，表，字段，用户的访问权限和安全级别

数据类型

timestamp vs bigint

timestamp会随时区的变化而变化，而bigint是一个固定的，别的时区的人就会觉得时间上有差别。

char vs varchar

char是固定长度，而varchar是变长；char会忽略末尾的空格，而varchar会保留。

MyISAM存储引擎：建议使用固定长度的数据列代替可变长度的数据列
MEMORY存储引擎：char和varchar都按照char处理
InnoDB存储引擎：建议使用varchar类型

text vs blob

较大文本用text或者blob。blob用于保存二进制数据，比如照片；text只能保存字符数据，比如文章。text分text,mediumtext和longtext，根据需要选择最小的类型；同理，blob。

性能

blob和text的删除操作会在数据表中留下很大的“空洞”，填入这些”空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE功能对这表进行碎片整理，避免因为”空洞”导致性能问题。

示例：
插入大量数据
insert into t values(1, repeat('haha', 100));
查询物理空间
du -sh t.*
删除数据
delete from t where id=1;
查询物理空间，发现物理空间几乎没有变化。
优化操作后
optimize table t;
查询物理空间，发现物理空间减小了。

使用合成(Synthetic)索引来提高大文本字段(blob和text)的查询性能，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就是通过检索散列值找到数据行了，这种技术只能用于精确匹配查询(对于<或者>=等范围搜索操作符没用)。
```
insert into t values(1, repeat('beijing',2), md5(content));
```

运算符

NULL不能用于比较
比较后true，则返回1；false则返回0.

regexp:正则匹配

示例：select 'abcdef' regexp 'ab'

如果'abcdef'含有'ab'则返回1，否则返回0.

优先级

优先级顺序	运算符
1	：=
2	\|\|、or、xor
3	&&、and
4	not
5	between、case、when、then和else
6	=、<=>、>=、>、<=、<、<>、!=、is、like、regexp、in
7	\|
8	&
9	<<和>>
10	-和+
11	*、/、div、%和mod
12	^
13	-和~
14	！

字符串函数

concat(s1,s2,…,sn)
insert(str, x, y, instr):将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
lower(str)
upper(str)
left(str,x)：返回左边x个字符
right(str, x)：返回右边x个字符
lpad(str,n,pad):用pad对str最左边进行填充，直到长度为n个字符长度
rpad(str,n,pad):用pad对str最右边进行填充，直到长度为n个字符长度
ltrim(str)
rtrim(str)
trim(str)
repeat(str,x):返回str重复x次
replace(str,a,b)：用字符串b替换str出现的a
strcmp(s1,s2):比较字符串s1和s2
substring(str,x,y):返回字符串str的x位置起，y个字符长度的字符串。

字符集

需要处理各国文字，可以采用UTF-8
如果数据库只需要支持一般中文，数据量很大，性能要求很高，可以采用GBK。GBK中文2字节，UTF-8是3字节，可以减少磁盘I/O，数据库cache以及网络传输时间，从而提高性能。如果应用主要处理英文字符，仅有少量汉字数据，那么选择UTF-8更好。
如果数据库需要做大量的字符运算，比如: 比较、排序等，那么选择定长字符集可能更好，定长字符集处理速度比变长字符集的处理速度快。
如果所有客户端程序都支持相同的字符集，则应优先选择该字符集作为数据库字符集，这样可以避免字符集转换的性能开销和数据损失。

定义

字符集(charset)：是一套符号和编码
校对规则(collation)：是在字符集内用于比较字符的一套规则，比如有的规则区分大小写，有的则无视

mysql服务器中有六个关键位置使用了字符集的概念，分别是：

client: 是客户端使用的字符集，相当于网页中的字符集设置。
connection: 是连接数据库的字符集设置类型，如果java没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。
database: 是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。
results: 是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。
server: 是服务器安装时指定的默认字符集设定。
system: 是数据库系统使用的字符集设定。

设置数据库字符集的规则如下

如果指定了字符集和校对规则，则使用指定的字符集和校对规则
如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则
如果指定了校对规则但未指定字符集，则字符集使用与该校对规则关联的字符集
如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则

表字符集和校对规则

和数据库基本一样

列出可用的字符集

SHOW CHARACTER SET

列出一个字符集的校对规则，例如以latin1开头的校对规则

SHOW COLLATION LIKE 'latin1%'

修改数据库字符集：

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...]

如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

只是修改表的默认字符集：

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];
如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

修改字段的字符集：

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];
如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

查看数据库编码：

SHOW CREATE DATABASE db_name;

查看表编码：

SHOW CREATE TABLE tbl_name;

查看字段编码：

SHOW FULL COLUMNS FROM tbl_name;

视图

视图主要有以下作用：

安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。