mysql规范

一、数据库命名规范

1.库名、表名、字段名必须使用小写字母，并采用下划线分割

MySQL有配置参数lower_case_table_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较。

show VARIABLES like '%lower_case_table_names%';

如果大小写混合使用，可能存在abc，Abc，ABC等多个表共存，容易导致混乱

字段名显示区分大小写，但实际使用时不区分，即不可以建立两个名字一样但大小写不一样的字段

为了统一规范， 库名、表名、字段名使用小写字母，不允许 - 号

反例(表名应全部为小写)

CREATE TABLE Wap_User_Tel_Number_Region
​
(
​
  user_name VARCHAR(100),
​
  pass_word VARCHAR(16)
​
);

正例

CREATE TABLE wap_user_tel_number_region
​
(
​
  user_name VARCHAR(100),
​
  pass_word VARCHAR(16)
​
);

2.库名、表名、字段名禁止超过32个字符，需见名知意，建议使用名词不是动词，不要使用复数名词。

库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，禁止超过32个字符

反例(表命名超过32个字符，且为复数名称)

CREATE TABLE wap_user_telephone_number_region_users
​
(
​
  user_name VARCHAR(100),
​
  pass_word VARCHAR(16)
​
);

正例

CREATE TABLE wap_user_tel_number_region
​
(
​
  user_name VARCHAR(100),
​
  pass_word VARCHAR(16)
​
);

3.**库名、表名、字段名禁止使用MySQL保留字

当库名、表名、字段名等属性含有保留字时，SQL语句必须用反引号引用属性名称，这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。

反例(user为数据库保留字)

CREATE TABLE wap_user
​
(
​
  USER VARCHAR(100),
​
  pass_word VARCHAR(16)
​
);

正例

CREATE TABLE wap_user
​
(
​
  user_name VARCHAR(100),
​
  pass_word VARCHAR(16)
​
);

4.临时库、表名必须以tmp为前缀，并以日期为后缀

反例（缺少日期后缀）

CREATE TEMPORARY TABLE tmp_user_account
​
(
​
user_name VARCHAR(100),
​
pass_word VARCHAR(16)
​
);

正例

CREATE TEMPORARY TABLE tmp_user_account_20190313

 

(

 

user_name VARCHAR(100),

 

pass_word VARCHAR(16)

 

);

5.备份库、表必须以bak为前缀，并以日期为后缀

形如：bak_user_account_20190313

6.历史表后缀建议用“_his”

形如：user_order_his

7.在不同的库或表中，要保证所有存储相同数据的列名和列类型必须一致

一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低

反例（字段tel在两个有业务关系的表中字段类型不一致，导致索引失效）

CREATE TABLE wap_user

 

(

 

  user_name VARCHAR(100),

 

  pass_word VARCHAR(16),

 

  tel VARCHAR(20)

);

 

CREATE TABLE wap_user_login_log

 

(

 

  user_name VARCHAR(100),

 

  login_date DATE,

 

  tel int(20)

);

正例

CREATE TABLE wap_user

 

(

 

  user_name VARCHAR(100),

 

  pass_word VARCHAR2(16),

 

  tel VARCHAR(20)

 

);

 

CREATE TABLE wap_user_login_log

 

(

 

  user_name VARCHAR(100),

 

  login_date DATE,

 

  tel VARCHAR(20)

 

);

以下是建议的公共字段名称及类型

create_user	VARCHAR(32)	建档人
create_time	datetime	创建时间
update_user	VARCHAR(32)	更新人
update_time	datetime	更新时间
remark	VARCHAR(255)	备注
contact_name	VARCHAR(32)	联系人
tel	VARCHAR(20)	电话号码
mob	VARCHAR(20)	手机号码
address	VARCHAR(100)	联系地址
zip_code	VARCHAR(10)	邮编
identity_card	VARCHAR(25)	身份证号
fax	VARCHAR(20)	传真
email	VARCHAR(64)	电邮

建议: 字段名建议不要用JAVA关键字来命名

8.表的命名最好是加上“业务名称_表的作用”，同类业务的表，以相同的表示该类业务的英文开头

假定表wap_user和表user_login_log都属于wap类业务

反例

CREATE TABLE wap_user

 

(

 

  user_name VARCHAR(100),

 

  pass_word VARCHAR(16)

 

);

 

CREATE TABLE user_login_log

 

(

 

  user_name VARCHAR(100),

 

  login_date DATE

 

);

正例

CREATE TABLE wap_user

 

(

 

  user_name VARCHAR(100),

 

  pass_word VARCHAR(16)

 

);

 

CREATE TABLE wap_user_login_log

 

(

 

  user_name VARCHAR(100),

 

  login_date DATE

 

);

说明：各子系统不用加子系统名称前缀,如POS系统的表不用都加pos_前缀，如果遇到需要同步其他系统的表的表名与本系统的表名相同时，用子系统名称做后缀的形式重命名其他子系统表名，如POS系统需要同步MDM表bill_item_dtl，而POS系统也存在这样的表名，则把MDM的表名重新命名为bill_item_dtl_mdm。

9.视图命名应以“v”+“表名[表名[表名]]”命名，如果表名过多可以用“v”+“功能描述”来命名

反例(视图和表是不可以同名的，如下语句会引起错误且不符合规范)

CREATE VIEW wap_user

 

AS

 

SELECT first_name, last_name, job_id

 

FROM wap_user;

正例

CREATE VIEW v_wap_user

 

AS

 

SELECT first_name, last_name, job_id

 

FROM wap_user;

10.函数命名以”func_”开头，后接函数的功能

反例（未以func_开头）

CREATE FUNCTION get_money

 

BEGIN

 

……

 

END;

正例

CREATE FUNCTION func_get_money

 

BEGIN

 

……

 

END;

11.存储过程以“proc_”开头，后接功能描述

反例(未以proc_开头)

CREATE PROCEDURE update_user

 

BEGIN

 

……

 

END;

正例

CREATE PROCEDURE proc_update_user

 

BEGIN

 

……

 

END;

12.触发器以“trig”+表名+“ins/del/upd”+”_before/after”命名

反例（未满足命名规范）

CREATE TRIGGER trigger1

 

AFTER DELETE ON wap_user

 

BEGIN

 

……

 

END;

正例

CREATE TRIGGER trig_wap_user_del_after

 

AFTER DELETE ON wap_user

 

BEGIN

 

……

 

END;

 

二、数据库基本设计规范

1. 除了空间数据存储表之外，所有表必须使用Innodb存储引擎。

没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎（mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb） Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好

2. 数据库和表的字符集统一使用utf8mb4,排序规则统一使用utf8mb4_unicode_ci

采用 utf8 编码的 MySQL 无法保存占位是 4 个字节的 Emoji 表情。为了使后端的项目全面支持客户端输入的 Emoji 表情，升级编码为 utf8mb4 是最佳解决方案；兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效

3. 所有表和字段都需要添加注释

使用comment从句添加表和列的备注，从一开始就进行数据字典的维护

4. 尽量控制单表数据量的大小，建议控制在500万以内，容量不超过2G

500万并不是Mysql数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题 可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

5. 谨慎使用Mysql分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表 谨慎选择分区键，跨分区查询效率可能更低，建议采用物理分表的方式管理大数据

6. 尽量做到冷热数据分离，减小表的宽度

Mysql限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节

减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据。经常一起使用的列放到一个表中（避免更多的关联操作）

7. 尽量不在数据库中存储图片，文件等大的二进制数据，使用文件服务器+路径方式存储

通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息

8.如无必要，不得使用触发器

触发器作为数据库后台的程序一般用来保障数据库数据的安全性。其使用频率与其定义的类型（插入、更shu新、删除等）和其所属基本表的操作（插入、更新、删除）频率有关。比如是更新类触发器，如果它所属的表总是频繁去更新，那么触发器就会频繁有效（频繁运行后台的程序），导致数据库性能下降。另外，触发器因为其隐蔽性容易被忽视，不好维护。应用的完整性不应由触发器保证，而是通过代码的事务控制；

9.如无必要，不建议使用存储过程

如果业务数据模型有变动，存储过程必须跟着业务代码一起更改，如果是大型项目，业务要求改动太大，那存储过程就几乎都得改，再就是移植性太差，且不好调试。

 

三、数据库字段设计规范

1. 优先选择符合存储需要的最小的数据类型

原因是：列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO次数也就越多， 索引的性能也就越差

• 业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间
• 能用int的就不用char或者varchar
• 能用tinyint的就不用int
• 使用 UNSIGNED 存储非负数值
• 使用tinyint来代替 enum和boolean
• 存储 ip 最好用 int存储而非 char(15)
• 将字符串转换成数字类型存储，如：将IP地址转换成整形数据

mysql提供了两个方法来处理ip地址

inet_aton 把ip转为无符号整型(4-8位) inet_ntoa 把整型的ip转为地址

插入数据前，先用inet_aton把ip地址转为整型，可以节省空间 显示数据时，使用inet_ntoa把整型的ip地址转为地址显示即可。

SELECT INET_ATON('209.207.224.40'); 3520061480

SELECT INET_NTOA(3520061480); 209.207.224.40

• 对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型来存储

因为：无符号相对于有符号可以多出一倍的存储空间 SIGNED INT -2147483648~2147483647 UNSIGNED INT 0~4294967295

VARCHAR(N)中的N代表的是字符数，而不是字节数。在utf8mb4下，英文占用1个字节，一般汉字占3个字节，emoji表情占4个字节。

字节与字符长度查询语句

select length(column),char_length(column) from table

过大的长度会消耗更多的内存。

• 表中的自增列（auto_increment属性），推荐使用bigint类型

2. 避免使用TEXT、BLOB数据类型，最常见的TEXT类型可以存储64k的数据

varchar是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，Mysql还是要进行二次查询，会使sql性能变得很差。但是不是说一定不能使用这样的数据类型，如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select * 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询

TEXT或BLOB类型只能使用前缀索引

因为MySQL对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且TEXT列上是不能有默认值的

3. 避免使用ENUM类型，用TINYINT替代

修改ENUM值需要使用ALTER语句 ENUM类型的ORDER BY操作效率低，需要额外操作 禁止使用数值作为ENUM的枚举值

4. 尽可能把所有列定义为NOT NULL。

修改时间，审核时间等用“0000-00-00 00:00:00”这样的默认值进行替代，备注用‘’的空字符进替代。 索引NULL列需要额外的空间来保存，所以要占用更多的空间； 进行比较和计算时要对NULL值做特别的处理。NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑

NULL与任何值的直接比较都为 NULL。

1） NULL<>NULL的返回结果是 NULL，而不是 false。

2） NULL=NULL的返回结果是 NULL，而不是 true。

3） NULL<>1的返回结果是 NULL，而不是 true。

5. 日期字段只需要表达年月日的选用DATE类型，需要精确到时间使用TIMESTAMP（4个字节）或DATETIME（8个字节）

TIMESTAMP 占用4字节和INT相同，但比INT可读性高。超出TIMESTAMP取值范围的使用DATETIME类型存储

	DATETIME	TIMESTAMP
存储长度	8字节	4字节
时区支持	不支持	支持
表达范围	1000-01-01 00:00:00 9999-12-31 23:59:59	1970-01-01 00:00:012038-01-19 03:14:07
保存格式	实际格式保存	UTC格式

经常会有人用字符串存储日期型的数据（不正确的做法）

缺点：

1：无法用日期函数进行计算和比较

2：用字符串存储日期要占用更多的空间

6. 小数类型（如财务金额、地理坐标）数据必须使用decimal类型

• 非精准浮点：float,double
• 精准浮点：decimal

decimal类型为精准浮点数，在计算时不会丢失精度。很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal的范围，建议将数据拆成整数和小数分开存储。

7.使用varchar(20)存储手机号，不要使用整数

牵扯到国家代号，可能出现+/-/()等字符，例如+86

手机号不会用来做数学运算

varchar可以模糊查询，例如like ‘138%’

8.根据业务区分使用char/varchar

字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高。单字符长度使用char(1)

字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间

9.整形定义中不添加长度，比如使用INT，而不是INT[4]

值类型括号后面的数字只是表示宽度而跟存储范围没有关系

10.核心表必须有行数据的创建时间和最后更新时间、操作人相关信息

核心表（如用户表，金钱相关的表）必须有行数据的创建时间字段create_time、最后更新时间字段update_time、操作人id字段operator_id、操作人姓名字段operator便于查问题。

其它常用字段

remark 备注 varchar

status 状态  varchar

creator 创建人 varchar

updator 修改人 varchar

deleted 删除状态 tinyint

checker 审核人 varchar

check_date 审核日期 Date

check_status  审核状态 tinyint

11.表达是与否概念的字段，必须使用 deleted命名，数据类型是 unsigned tinyint（ 1表示是，0表示否）。

说明：任何字段如果为非负数，必须是 unsigned。 表达逻辑删除的字段名 deleted，1 表示删除，0 表示未删除。

12.字段允许适当冗余，以提高查询性能，但必须考虑数据一致。

冗余字段应遵循：

1）不是频繁修改的字段。

2）不是 varchar超长字段，更不能是 text字段。

正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。

13.私密信息（如密码、手机号、身份证、空间数据）建议在数据库加密后存储

用户密码一般采用不可逆加密如SHA512,敏感信息（手机号、身份证、空间数据）用对称加密加密算法AES256。

14.字段命名应具有含义，能反映该字段存储的内容，且字段应有字段备注。如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释

反例(假定存储的字段为用户名和密码，如下的字段名毫无意义也没有备注)

CREATE TABLE wap_user

 

(

 

    col1 VARCHAR(100),

   

    col2 VARCHAR(16)

 

);

正例

CREATE TABLE wap_user

 

(

 

    user_name VARCHAR(100) COMMENT '用户名',

   

    pass_word VARCHAR(16) COMMENT '密码'

 

);

 

四、索引设计规范

索引其实就是一种数据结构，（哈希表、树等等）不同类型的索引有着不同的数据结构和功能。

MySQL的查询速度依赖良好的索引设计，因此索引对于高性能至关重要。合理的索引会加快查询速度，不合理的索引会降低速度

1.索引的作用

• 加速查询速度
• 维护数据的约束性（完整性、一致性）

对于加速查询，使用索引不一定是最好的选择。小表就直接全表扫描，中到大表就建索引，超大表就分区分表。其实主要就要索引带来的好处和维护索引的成本之间的权衡。

2.MYSQL 中索引的限制

MYISAM 存储引擎索引长度的总和不能超过 1000 字节

BLOB 和 TEXT 类型的列只能创建前缀索引

使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引

过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引

join语句中join条件字段类型不一致的时候MYSQL无法使用索引

使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引。

使用非等值查询的时候, MYSQL 无法使用 Hash 索引

3.索引SET规范

(1). 限制每张表上的索引数量，建议单张表索引不超过5个

索引并不是越多越好！索引可以提高效率同样可以降低效率

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率

因为mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能

(2). 禁止给表中的每一列都建立单独的索引

5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好

(3). 每个Innodb表必须有个主键

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。Innodb是按照主键索引的顺序来组织表的

主键准则： 不使用更新频繁的列，默认使用非空的唯一键。无特别要求的，字段名统一定义为 id； 涉及到要做分库分表的表用有序UUID做主键，UUID主键类型选择CHAR(32)；不涉及分库分表的表选用自增长ID做主键，主键类型使用unsigned int或unsigned big int；

关于UUID: 好处就是本地生成，不要基于数据库来了；不好之处就是，UUID 太长了、占用空间大，作为主键性能太差了；更重要的是，UUID 不具有有序性，会导致 B+ 树索引在写的时候有过多的随机写操作（连续的 ID 可以产生部分顺序写），还有，由于在写的时候不能产生有顺序的 append 操作，而需要进行 insert 操作，将会读取整个 B+ 树节点到内存，在插入这条记录后会将整个节点写回磁盘，这种操作在记录占用空间比较大的情况下，性能下降明显。

(4). 尽量避免使用外键约束，但一定要在表与表之间的关联键上建立索引

外键可用于保证数据的参照完整性，但建议在业务端实现 外键会影响父表和子表的写操作从而降低性能

以学生和成绩的关系为例，学生表中的 student_id是主键，那么成绩表中的 student_id则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新， 即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群； 级联更新是强阻塞，存在数据库更新风暴的风险； 外键影响数据库的插入速度

(5). 常见索引列建议

1. 出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列
2. 包含在ORDER BY、GROUP BY、DISTINCT中的字段

并不要将符合1和2中的字段的列都建立一个索引， 通常将1、2中的字段建立联合索引效果更好

1. 多表join的关联列
2. 索引字段建议有高的选择性和过滤性（count(distinct)/count(*)>0.6）
3. 建立索引的时候，建议考虑到SELECT和INSERT,UPDATE,DELETE的平衡
4. 一般建议在查询数据量10%以下使用索引

(6). 如何选择索引列的顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。 索引根据左前缀原则，当建立一个联合索引(a,b,c)，则查询条件里面只有包含(a)或(a,b)或(a,b,c)的时候才能走索引，(a,c)作为条件的时候只能使用到a列索引，所以这个时候要确定a的返回列一定不能太多，不然语句设计就不合理，(b,c)则不能走索引。

a、区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）。比如单号、userid等。而type，status等筛选性一般不建议放在最前面

b、字段选择性接近，使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

c、存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a>? and b=? 那么即使 a的区分度更高，也必须把 b放在索引的最前列。

(7). 避免建立冗余索引和重复索引

增加了查询优化器生成执行计划的时间

重复索引示例：primary key(id)、index(id)、unique index(id)

冗余索引示例：index(a,b,c)、index(a,b)、index(a)

(8). 对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引

UPDATE、DELETE 语句的 WHERE 条件列

ORDER BY、GROUP BY、DISTINCT 的字段

多表 JOIN 的字段

覆盖索引的好处:

1. 避免Innodb表进行索引的二次查询

Innodb是以聚集索引的顺序来存储的，对于Innodb来说，非聚集索引在叶子节点中所保存的是行的主键信息， 如果是用非聚集索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，非聚集索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了IO操作，提升了查询效率

1. 可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的，对于IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多， 因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO

注意：

a、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。

b、如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

(9). 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引

不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的； 另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生

(10).主键索引名为 pk_ 字段名；唯一索引名为 uk _ 字段名；普通索引名则为 idx_ 字段名

pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称

 

五、数据库SQL开发规范

1. 禁止使用SELECT * 必须使用SELECT <字段列表> 查询，不要返回用不到的字段,避免全盘扫描！

原因：

消耗更多的CPU和IO以网络带宽资源 无法使用覆盖索引 可减少表结构变更带来的影响

反例

SELECT * FROM A

正例

SELECT id,name FROM A

2. 禁止使用不含字段列表的INSERT语句

反例

insert into values ('a','b','c');

正例

insert into t(c1,c2,c3) values ('a','b','c');

3. 用批量插入替代单条插入

反例

INSERT into person(name,age) values('A',24)

INSERT into person(name,age) values('B',24)

INSERT into person(name,age) values('C',24)

正例

INSERT into person(name,age) values('A',24),('B',24),('C',24),

注意：批量插入也不是一次性插入的记录越多越好，一次性写入的记录数可以设置成1000条。

4. 避免使用子查询，可以把子查询优化为join操作

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化

子查询性能差的原因： 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询

inner join 和 left join、right join、子查询**

• 第一：inner join内连接也叫等值连接是，left/rightjoin是外连接。

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

 

SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;

 

SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

经过来之多方面的证实 inner join性能比较快，因为inner join是等值连接，或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接，如：

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

推荐：能用inner join连接尽量使用inner join连接

• 第二：子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。

反例

mysql是先对外表A执行全表查询，然后根据uuid逐次执行子查询，如果外层表是一个很大的表，我们可以想象查询性能会表现比这个更加糟糕。

Select * from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

正例

Select * from A inner join B ON A.uuid=B.uuid where b.uuid>=3000;

• 第三：使用JOIN时候，应该用小的结果驱动大的结果

left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向。如：

反例

Select * from A left join B A.id = B.ref_id where  A.id > 10

正例

select * from (select * from A wehre id > 10) T1 left join B on T1.id = B.ref_id;

5. 避免使用JOIN关联太多的表

对于Mysql来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置 在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多， 所占用的内存也就越大。

查看join_buffer_size语句，单位：字节

show VARIABLES like '%join_buffer_size%';

如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性

同时对于关联操作来说，会产生临时表操作，影响查询效率 Mysql最多允许关联61个表，建议不超过3个 需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引；即使双表 join 也要注意表索引、 SQL 性能

6. 在明显不会有重复值时使用UNION ALL 而不是UNION

UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作， UNION ALL 不会再对结果集进行去重操作

7. 拆分复杂的大SQL为多个小SQL

大SQL:逻辑上比较复杂，需要占用大量CPU进行计算的SQL。MySQL 一个SQL只能使用一个CPU进行计算，SQL拆分后可以通过并行执行来提高处理效率

8.能确定返回结果只有一条时，使用 limit 1

在保证数据不会有误的前提下，能确定结果集数量时，多使用limit，尽快的返回结果。

9.不要使用 count(列名)来替代 count(*)

count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行

注意：count(*)与count(1)的区别:

一般情况下，select count (*)和select count(1)两着返回结果是一样的

假如表沒有主键(primary key), 那么count(1)比count(*)快

如果有主键的話，那主键作为count的条件时候count(主键)最快

如果你的表只有一个字段的话那count(*)就是最快的

10.in 控制元素数量

需要仔细评估 in 后边的集合元素数量，控制在 1000个之内。元素过多会导致查询缓慢，数据库CPU飙高。

11.建议使用合理的分页方式以提高分页效率

当偏移量特别大时，limit效率会非常低。

反例

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000，10;

正例

SELECT * FROM table a inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000，10) b ON a.id=b.id

分别在于，前者需要更多I/O浪费，因为先读索引，再读数据，然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了，然后通过id读取需要的列。

12.利用limit 1 取得一行，避免全表扫描

有些业务判断记录是否存在时，可以用limit1替代count记录数，避免全表扫描。

反例

SELECT count(1) FROM A

根据count结果判断是否存在，会导致全表扫描

正例

SELECT id FROM A limit 1

只要id存在即返回，说明存在对应的记录

13.从表中同一笔记录中获取记录的字段值，须使用一SQL语句得到，不允许分多条SQL语句

反例（从同一个表中取出记录，分成两条语句分别扫描）

UPDATE employees_new

 

SET first_name =

 

(

 

  SELECT first_name

 

  FROM employees

 

  WHERE job_id = 100

 

)

 

WHERE job_id = 100;

 

 

UPDATE employees_new

 

SET last_name=

 

(

 

  SELECT last_name

 

  FROM employees

 

  WHERE job_id = 100

 

)

 

WHERE job_id = 100;

正例

UPDATE employees_new

 

SET first_name =

 

(

 

  SELECT first_name

 

  FROM employees

 

  WHERE job_id = 100

 

),

 

last_name =

 

(

 

  SELECT last_name

 

  FROM employees

 

  WHERE job_id = 100

 

)

 

WHERE job_id = 100;

14.当一个SQL 语句中涉及到多个表时，始终使用有意义的别名来限定字段名

说明：别名命名时，尽量避免使用无意义的代号a、b 、c… ， 而应该有意义( 如表mtl_system_items_b 对应别名为msi，po_headers_all 别名对应为pha)。

反例(未使用有明确含义的表别名)

SELECT a.wip_entity_name, a.wip_entity_id, a.date_released

 

FROM wip.wip_entities b,

 

wip.wip_discrete_jobs a

 

WHERE b.wip_entity_id = a.wip_entity_id

 

AND a.status_type = 3

正例

SELECT wdj.we_entity_name, wdj.wip_entity_id, wdj.date_released

 

FROM wip.wip_entities we,

 

      wip.wip_discrete_jobs wdj

 

WHERE we.wip_entity_id = wdj.wip_entity_id

 

AND we.status_type = 3

15.慎用for update语句

在使用for update子句时一定注意限制条件，避免锁定全表或者不需要被锁定的行记录。如无必要锁定数据则应避免使用for update；

16.尽量使用if来简化SQL访问数据库的次数

反例

SELECT  COUNT(*) , SUM(salary)

 

FROM employees

 

WHERE department_id = 20

 

AND first_name LIKE 'SMITH%';

 

 

SELECT COUNT(*) , SUM(salary)

 

FROM employees

 

WHERE department_id = 30

 

AND first_name LIKE 'SMITH%';

正例

SELECT COUNT(IF(department_id=20, '*', NULL)) d20_count,

 

       COUNT(IF(department_id=30, '*', NULL)) d30_count,

    

       SUM(IF(department_id=20, salary, NULL)) d20_sal,

   

       SUM(IF(department_id=30, salary, NULL)) d30_sal

 

FROM employees

 

WHERE first_name LIKE 'SMITH%';

17.只有在对group by的结果删选的时候才需要用到having语句

反例（非分组结果进行函数运算列进行筛选使用了having）

SELECT last_name, avg(salary)

 

FROM employees

 

GROUP BY last_name

 

HAVING last_name != 'Grant'

 

AND last_name !=  'Fay'

正例（不涉及对分组结果进行函数运算及筛选使用where）

SELECT last_name, avg(salary)

 

FROM employees

 

WHERE last_name != 'Grant'

 

AND last_name != 'Fay'

 

GROUP BY last_name

正例（当需要对分组结果进行函数运算及筛选使用having）

SELECT last_name, avg(salary)

 

FROM employees

 

WHERE last_name != 'Grant'

 

AND last_name != 'Fay'

 

GROUP BY last_name

 

HAVING avg(salary) > 10000

18. 充分利用表上已经存在的索引，避免索引失效发生

（1）、避免使用前缀%号的查询条件

反例

SELECT id FROM A WHERE name like '%abc%'

由于abc前面用了“%”，因此该查询必然走全表查询,除非必要(模糊查询需要包含abc)，否则不要在关键词前加%

正例

SELECT id FROM A WHERE name like 'abc%'

（2）、一个SQL只能利用到复合索引中的一列进行范围查询

如 有 a,b,c列的联合索引，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到， 在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的左侧

（3）、exist & in 优化

SELECT * from A WHERE id in ( SELECT id from B )

SELECT * from A WHERE id EXISTS ( SELECT 1 from A.id= B.id )

分析:

in 是在内存中遍历比较

exist 需要查询数据库，所以当B的数据量比较大时，exists效率优于in

in()只执行一次，把B表中的所有id字段缓存起来，之后检查A表的id是否与B表中的id相等，如果id相等则将A表的记录加入到结果集中，直到遍历完A表的所有记录。

In 操作的流程原理如同一下代码

    List resultSet = {};

 

    Array A = (select * from A);

    Array B = (select id from B);

 

    for(int i = 0;i < A.length; i++) {

          for(int j = 0;j < B.length; j++) {

          if(A[i].id == B[j].id) {

             resultSet.add(A[i]);

             break;

          }

       }

    }

    return resultSet;

可以看出，当B表数据较大时不适合使用in()，因为会把B表数据全部遍历一次

如：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历10000*1000000次，效率很差。

再如：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000*100次，遍历次数大大减少，效率大大提升。

结论：in()适合B表比A表数据小的情况

exist()会执行A.length()次，执行过程代码如下

List resultSet={};

Array A=(select * from A);

for(int i = 0;i< A.length; i++) {

    if(exists(A[i].id) {  //执行select 1 from B where B.id = A.id是否有记录返回

       resultSet.add(A[i]);

    }

}return resultSet;

当B表比A表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行。

如：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。

如：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。

再如：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库

（4）、使用 not exists 来优化not in 操作

因为如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

反例

 select * from A where A.id not in (select id from B);

等价于: select * from A where A.id != 1 and A.id != 2 and A.id != 3;//select id from B的返回值是1,2,3 可以知道not in是个范围查询，这种!=的范围查询无法使用任何索引,等于说A表的每条记录，都要在B表里遍历一次，查看B表里是否存在这条记录

正例

select * from A where not exists (select * from B where B.id = A.id);

用了B的索引; 和exists的查询方式一样

（5）. 对应同一列进行or判断时，使用in代替。对不同索引列进行or判断，使用union all替代。

in 操作可以更有效的利用索引，or大多数情况下很少能利用到索引

反例：同一列进行or

SELECT id FROM A WHERE num = 10 or num = 20

正例：使用in

SELECT id FROM A WHERE num in (10,20);

反例：不同索引列进行or

SELECT id FROM A WHERE num = 10 or name = '张三'

正例：使用union all

SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE name = '张三';

（6）. WHERE从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

不推荐：

where date(create_time)='20190101'

推荐：

where create_time >= '20190101' and create_time < '20190102'

（7）避免数据类型的隐式转换

反例

select name,phone from customer where phone_no = 18588287654;

电话号码phone_no为字符型，等号后为数字，会导致格式隐式转换，使phone_no上的索引失效:

正例

select name,phone from customer where phone_no = '18588287654';

（8）避免使用 != 等非等值操作符，会导致用不到索引

（9）where子句中使用 IS NULL 或 IS NOT NULL 的优化

反例

SELECT id FROM A WHERE num IS NULL

在where子句中使用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会进行全表查询。

正例

优化成num上设置默认值0，确保表中num没有null值, IS NULL 的用法在实际业务场景下SQL使用率极高，我们应注意避免全表扫描

SELECT id FROM A WHERE num=0

（10）排序的索引问题

mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引。因此数据库默认排序可以符合要求情况下不要使用排序操作；

尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

19.代码调用中，注重效率和安全性的提高

(1)绑定变量使用规范

a、应用端所有查询的 where 条件中的变量，都需要使用预编译绑定变量来实现，以防SQL注入，同时相同语句可以一次解析，多次使用，提高处理效率，性能也会更优；

b、在 myBatis 的 SqlMap 文件中绑定变量使用 "#{var_name}"表示，替代变量使用"${var_name}"；所有需要动态 Order By 条件的查询，在使用替代变量过程中，需要将可能传入的内容以枚举类写死在代码中，禁止接受任何外部传入内容；对于不变的常量条件，请使用常量而不是变量；

c、IN子句，使用"Iterate + 数组类型变量"的方式实现绑定变量而不是通过代码拼接 Query 语句;

(2)减少同数据库的交互次数

数据库更适合处理批量操作，合并多个相同的操作到一起，作为一个事物统一提交，可以提高处理效率

(3)开发代码中慎用truncate

TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE无事务且不触发trigger有可能造成事故，故不建议在开发代码中使用此语句。

(4)要返回MySQL自增序列的ID值，可以考虑使用函数LAST_INSERT_ID()

此函数只能返回同一个SESSION最近一次对有AUTO_INCREMENT属性表INSERT的ID值

<insert id="insertStudent" parameterType="com.czd.mybatis.bean.Student">   

 

      INSERT stu(name) VALUES  (#{name})   

 

      <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">       

 

            SELECT LAST_INSERT_ID()   

 

      </selectKey>

 

 </insert>

##

六、数据库操作行为规范

1.超100万行的批量写（UPDATE、DELETE、INSERT）操作，要分批多次进行操作

(1). 避免产生大事务操作

大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对MySQL的性能产生非常大的影响 特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批

(2). 大批量操作可能会造成严重的主从延迟

主从环境中,大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间， 而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况

(3). binlog日志为row格式时会产生大量的日志

大批量写操作会产生大量日志，特别是对于row格式二进制数据而言，由于在row格式中会记录每一行数据的修改，我们一次修改的数据越多， 产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因

2. 程序连接不同的数据库使用不同的账号，禁止跨库查询

为数据库迁移和分库分表留出余地

降低业务耦合度

避免权限过大而产生的安全风险

3.禁止为程序使用的账号赋予super权限

当达到最大连接数限制时，还运行1个有super权限的用户连接。

super权限只能留给DBA处理问题的账号使用

4.对于程序连接数据库账号，遵循权限最小原则

程序使用数据库账号只能在一个DB下使用，不准跨库

程序使用的账号原则上不准有drop权限

5. 禁止在线上做数据库压力测试

6. 禁止从开发环境，测试环境直接连接生产环境数据库

 

七、其它

1.MySQL数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间 和 字符串(字符)类型。

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

MySQL之varchar的设计

• 存储规则：

4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）

5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放20个，最大大小是65532字节

• 限制规则

1）存储限制

varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度，小于255为1个字节，大于255则要2个字节

2）编码长度限制

字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766

字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845

字符类型若为utf8mb4，每个字符最多占4个字节，最大长度不能超过16383

3）表长度限制

mysql的一个表总共字段长度不超过65535

• varchar(10)和varchar(100)的区别

保存"hello"时占用的空间是一样的，但是更小的varchar(10)有着更好的性能

比如：MySQL建立索引时如果没有限制索引的大小，索引长度会默认采用的该字段的长度，也就是varchar(100)建立的索引存储大小要比varchar(10)建立索引存储大小大的多，加载索引使用的内存也更多

位、字节、字符的区别

位（bit）：计算机存储信息的最小单位，二进制的一个“0”或一个“1”叫一位

字节（byte）：是计算机中数据处理的基本单位，习惯上用大写 B 来表示，1B（byte,字节）= 8bit（位）

字符：是指计算机中使用的字母、数字、字和符号，不同的编码里，一个字符对应几个字节是不同的

ASCIIS码： 1个英文字母（不分大小写）= 1个字节的空间 1个中文汉字 = 2个字节的空间 1个ASCII码 = 一个字节

UTF-8编码： 1个英文字符 = 1个字节 英文标点 = 1个字节 1个中文（含繁体） = 3个字节 中文标点 = 3个字节

utf8mb4 ：中文 = 3个字节 emoji表情符号 = 4个字节

2.MySQL常用函数

日期操作函数

获取当前时间：NOW()，CURDATE()、CURTIME()

其中， NOW()函数精确到秒， 格式：YYYY-MM-DD HH:MM:SS

CURDATE函数精确到天，格式：YYYY-MM-DD

CURTIME函数精确到秒，格式：HH:MM:SS

日期数值的加减函数

DATE_ADD(date,INTERVAL expr type)

SELECT DATE_ADD('2021-01-01',INTERVAL 1 month)

DATE_ SUB(date,INTERVAL expr type)

SELECT DATE_SUB('2021-03-01',INTERVAL 1 month)

常用的几种type类型：YEAR、MONTH、DAY、HOUR、MINUTE，其中expr可以为正数或负数，我们在开过程中，一般使用DATE_ADD()函数，若要作日期减去一个数字的方式，就使用负数。

DATEDIFF(expr1,expr2)，是返回 开始日期expr1与 结束日期expr2之间，相差的天数 ，返回值为正数或负数。

SELECT DATEDIFF('2021-04-11','2021-03-21')

返回日期某部分信息的函数：

YEAR(expr1) 返回日期expr1部分的年份；

MONTH(expr1) 返回日期expr1部分的月份；

DAY(expr1)返回expr1部分的天数；

WEEKDAY(expr1)返回expr1对应的星期数字

类型转换函数

字符串转换成日期方式，DATE_FORMAT()或STR_TO_DATE()，

两个函数的格式如下：

DATE_FORMAT(expr1,format)

STR_TO_DATE(expr1, format)

常用的日期格式YYYY-MM-DD HH:MM:SS 对应的format为%Y-%m-%d %H:%i:%S

通用的类型转换函数：

CAST(expr AS type)

CONVERT(expr,type)

SELECT CAST('2021-04-11 00:20:21' AS TIME);

SELECT CONVERT('2021-04-11 00:20:21' , TIME);

在不同字符集之间转换数据

CONVERT(expr USING transcoding_name)

SELECT CONVERT(_latin1'Müller' USING utf8);

 

3.INNODB与MYISAM的主要区别

	MyISAM	InnoDB
构 成上的区别：	每个MyISAM表在磁盘上存储三个文件。文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。.MYD文件存储数据 (MYData)。.MYI文件存储索引 (MYIndex)。	InnoDB表分空间数据文件和日志文件，索引和数据存储未分离。
事务处理上方面:	MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持	InnoDB提供事务支持事务，外键等高级数据库功能
SELECT UPDATE,INSERT，Delete操 作	如果执行大量的SELECT，MyISAM是更好的选择	1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表 2.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的 删除。 3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用
表的具体行数	select count() from table,MyISAM只要简单的读出保存好的行数，注意的是，当count()语句包含 where条件时，两种表的操作是一样的	InnoDB中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行
锁	表锁，MyISAM表锁读写互相阻塞，写锁优先级高于读锁。参数选项low_priority_updates设置写锁优先级比读锁低、参数选项concurrent_insert配置是否使用并发插入特性，concurrent_insert=0 表示不允许并发插入，concurrent_insert=1表示允许对没有空数据块的表使用并发插入（缺省），concurrent_insert=2表示对所有表允许并发插入	提供行锁(locking on row level)，提供与Oracle类型一致的不加锁读取(non-locking read in SELECTs)。MySQL的行锁是针对索引加的锁，不是针对记录加的锁，在不通过索引条件查询的时候，InnoDB使用的是表锁，而不是行锁。另外，即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁