MySQL性能优化总结

发布日期: 2022-03-28

本文转载自程序员鱼皮的文章，侵删

https://mp.weixin.qq.com/s/CP-kJNuaPzKQWyzLzvRRAg

查询数据库表信息

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='指定数据库名'
order by data_length desc, index_length desc;

一、表结构设计

1、设计中间表

设计中间表，一般针对于统计分析功能，或者实时性不高的需求（报表统计，数据分析等系统）。

2、设计冗余字段

为减少关联查询，创建合理的冗余字段（创建冗余字段还需要注意数据一致性问题）。这里分库分表时较为常用。

3、拆表

对于字段太多的大表，考虑拆表（比如一个表有100多个字段）对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表。

4、主键优化

每张表建议都要有一个主键（主键索引），而且主键类型最好是int类型，建议自增主键（分布式系统的情况下建议雪花算法）

5、字段的设计

数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

使用可以存下数据最小的数据类型，合适即可
尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED；
VARCHAR的长度只分配真正需要的空间；
对于某些文本字段，比如”省份”或者”性别”，使用枚举或整数代替字符串类型；在MySQL中， ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多
尽量使用TIMESTAMP而非DATETIME；
单表不要有太多字段，建议在20以内；
尽可能使用 not null 定义字段，null 占用4字节空间，这样在将来执行查询的时候，数据库不用去比较NULL值。
用整型来存IP。
尽量少用 text 类型，非用不可时最好考虑拆表

二、SQL语句及索引

如果发现SQL查询比较慢，可以开启慢查询日志进行排查。

# 开启全局慢查询日志
SET global slow_query_log = ON;
# 设置慢查询日志文件名
SET global slow_query_log_file = 'slow-query.log';
# 记录未使用索引的SQL
SET global log_queries_not_using_indexes = ON;
# 慢查询的时间阈值，默认10秒
SET long_query_time = 10;

注：索引并不是越多越好，要根据查询有针对性的创建。

1、索引创建和使用原则

单表查询：哪个列作查询条件，就在该列创建索引
多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段
不要对索引列进行任何操作（计算、函数、类型转换）
索引列中不要使用 !=，<> 非等于
字符字段只建前缀索引，最好不要做主键；
尽量不用UNIQUE，由程序保证约束
不用外键，由程序保证约束
索引列不要为空，且不要使用 is null 或 is not null 判断
索引字段是字符串类型，查询条件的值要加’’单引号，避免底层类型自动转换

2、使用 EXPLAIN 分析 SQL

这里对explain的结果进行简单说明：

select_type：查询类型
- SIMPLE 简单查询
- PRIMARY 最外层查询
- UNION union后续查询
- SUBQUERY 子查询
type：查询数据时采用的方式
- ALL 全表（性能最差）
- index 基于索引的全表
- range 范围（< > in）
- ref 非唯一索引单值查询
- const 使用主键或者唯一索引等值查询
possible_keys：可能用到的索引
key：真正用到的索引
rows：预估扫描多少行记录
key_len：使用了索引的字节数
Extra：额外信息
- Using where 索引回表
- Using index 索引直接满足条件（覆盖索引）
- Using filesort 需要排序
- Using temprorary 使用到临时表

对于以上的几个列，我们重点关注的是type，最直观的反映出SQL的性能。

3、sql语句优化

SQL语句尽可能简单

一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库。

对于连续数值，使用 BETWEEN 不用 IN

SELECT id FROM t WHERE num BETWEEN 1 AND 5；

SQL 语句中 IN 包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。如果数值较多，需要在内存进行排序操作，产生的消耗也是比较大的。

SELECT 语句必须指明字段名称

SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；减少了使用覆盖索引的可能性。

当只需要一条数据的时候，使用 limit 1

limit 相当于截断查询。

例如：对于select * from user limit 1; 虽然进行了全表扫描，但是limit截断了全表扫描，从0开始取了1条数据。

排序字段加索引

排序的字段建立索引在排序的时候也会用到

如果限制条件中其他字段没有索引，尽量少用or

尽量用 union all 代替 union

union和union all的差别就在于union会对数据做一个distinct的动作，而这个distanct动作的速度则取决于现有数据的数量，数量越大则时间也越慢。而对于几个数据集，要确保数据集之间的数据互相不重复，基本是O(n)的算法复杂度。

区分 in 和 exists、not in 和 not exists

如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

使用合理的分页方式以提高分页的效率

limit m n，其中的m偏移量尽量小。m越大查询越慢。

避免使用 % 前缀模糊查询

例如：like ‘%name’或者like ‘%name%’，这种查询会导致索引失效而进行全表扫描。但是可以使用like ‘name%’，这种会使用到索引。

避免在 where 子句中对字段进行表达式操作

这种不会使用到索引：

select user_id,user_project from user_base where age*2=36;

可以改为：

select user_id,user_project from user_base where age=36/2;

任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

避免隐式类型转换

where 子句中出现的 column 字段要和数据库中的字段类型对应

必要时可以使用 force index 来强制查询走某个索引

有的时候 MySQL 优化器采取它认为合适的索引来检索 SQL 语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用 forceindex 来强制优化器使用我们制定的索引。

使用联合索引时注意范围查询

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

某些情况下，可以使用连接代替子查询

因为使用 join，MySQL 不会在内存中创建临时表。

使用JOIN的优化

使用小表驱动大表，例如使用inner join时，优化器会选择小表作为驱动表

小表驱动大表，即小的数据集驱动大的数据集

如：以 A，B 两表为例，两表通过 id 字段进行关联。

#当 B 表的数据集小于 A 表时，用 in 优化 exist；使用 in ，两表执行顺序是先查 B 表，再查 A 表
select * from A where id in (select id from B)

#当 A 表的数据集小于 B 表时，用 exist 优化 in；使用 exists，两表执行顺序是先查 A 表，再查 B 表
select * from A where exists (select 1 from B where B.id = A.id)

三、系统配置

1、保证从内存读取

MySQL 会在内存中保存一定的数据，通过 LRU（最近最少使用）算法将不常访问的数据保存在硬盘文件中。尽可能的扩大内存中的数据量，将数据保存在内存中，从内存中读取数据，可以提升 MySQL 性能。

MySQL 使用优化过后的 LRU 算法：

普通LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰

改进LRU：链表分为new和old两个部分，加入元素时并不是从表头插入，而是从中间 midpoint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。每当有新的page数据读取到buffer pool时，InnoDb引擎会判断是否有空闲页，是否足够，如果有就将free page从free list列表删除，放入到LRU列表中。没有空闲页，就会根据LRU算法淘汰LRU链表默认的页，将内存空间释放分配给新的页。

LRU 算法针对的是 MySQL 内存中的结构，这里有个区域叫 Buffer Pool（缓冲池） 作为数据读写的缓冲区域。把这个区域进行相应的扩大即可提升性能，当然这个参数要针对服务器硬件的实际情况进行调整。

通过以下命令可以查看相应的BufferPool的相关参数：

show global status like 'innodb_buffer_pool_pages_%'

输入以下命令可以查看 BufferPool 的大小：

show variables like "%innodb_buffer_pool_size%"

在这里我们可以修改这个参数的值，如果该服务器是 MySQL 专用的服务器，我们可以 修改为总内存的 60%~80% ，当然不能影响系统程序的运行。

这个参数是只读的，可以在 MySQL 的配置文件（my.cnf 或 my.ini）中进行修改。Linux 的配置文件为 my.cnf。

# 修改缓冲池大小为750M
innodb_buffer_pool_size = 750M

2、数据预热

数据预热相当于将磁盘中的数据提前放入 BufferPool 内存缓冲池内。一定程度提升了读取速度。

对于 InnoDB，这里提供一份预热 SQL 脚本：

#mysql5.7版本中，如果DISTINCT和order by一起使用将会报3065错误，sql语句无法执行。这是由于5.7版本语法比之前版本语法要求更加严格导致的。
#推荐在mysql的配置文件my.cnf文件(linux)/my.ini文件(window) 的mysqld中增加或者修改sql_model配置选项
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#重启后生效
SELECT DISTINCT
    CONCAT('SELECT ',rowlist,' FROM ',db,'.',tb,
    ' ORDER BY ',rowlist,';') selectSql
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) rowlist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA 
ORDER BY db,tb;

3、降低磁盘的写入次数

（1）增大 redo log，减少落盘次数：

redo log 是重做日志，用于保证数据的一致，减少落盘相当于减少了系统 IO 操作。

innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size

（2）通用查询日志、慢查询日志可以不开，binlog 可开启。

通用查询和慢查询日志也是要落盘的，可以根据实际情况开启，如果不需要使用的话就可以关掉。binlog 用于恢复和主从复制，这个可以开启。

查看相关参数的命令：

# 慢查询日志
show variables like 'slow_query_log%'
# 通用查询日志
show variables like '%general%';
# 错误日志
show variables like '%log_error%'
# 二进制日志
show variables like '%binlog%';

（3）写 redo log 策略 innodb_flush_log_at_trx_commit 设置为 0 或 2

对于不需要强一致性的业务，可以设置为 0 或 2。

0：每隔 1 秒写日志文件和刷盘操作（写日志文件 LogBuffer –> OS cache，刷盘 OS cache –> 磁盘文件），最多丢失 1 秒数据
1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作
2：事务提交，立刻写日志文件，每隔 1 秒钟进行刷盘操作

4、系统调优参数

back_log

back_log值可以指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。可以从默认的50升至500。

wait_timeout

数据库连接闲置时间，闲置连接会占用内存资源。可以从默认的8小时减到半小时。

max_user_connection

最大连接数，默认为0无上限，最好设一个合理上限。

thread_concurrency

并发线程数，设为CPU核数的两倍。

skip_name_resolve

禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问。

key_buffer_size

索引块的缓存大小，增加会提升索引处理速度，对MyISAM表性能影响最大。对于内存4G左右，可设为256M或384M，通过查询show status like ‘key_read%’，保证key_reads / key_read_requests在0.1%以下最好。

innodb_buffer_pool_size

缓存数据块和索引块，对InnoDB表性能影响最大。通过查询show status like ‘Innodb_buffer_pool_read%’，保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好。

innodb_additional_mem_pool_size

InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小。

innodb_log_buffer_size

InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB。

query_cache_size

缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当某个表的数据有任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们数据变化非常频繁的情况下，使用Query Cache可能得不偿失。根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大。可以通过命令show status like ‘Qcache_%’查看目前系统Query catch使用大小。

read_buffer_size

MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小来提高其性能。

sort_buffer_size

MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小。

read_rnd_buffer_size

MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

record_buffer

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，可能想要增加该值。

thread_cache_size

保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的。

table_cache

类似于thread_cache _size，但用来缓存表文件，对InnoDB效果不大，主要用于MyISAM。

四、阿里MySQL规范解读

1、建表规约

【强制】表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是unsigned tinyint（1表示是，0表示否）。说明：任何字段如果为非负数，必须是unsigned。注意：POJO类中的任何布尔类型的变量，都不要加is前缀，所以，需要在设置从is_xxx到Xxx的映射关系。数据库表示是与否的值，使用tinyint类型，坚持is_xxx的命名方式是为了明确其取值含义与取值范围。正例：表达逻辑删除的字段名is_deleted，1表示删除，0表示未删除。
【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。说明：MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。正例：aliyun_admin，rdc_config，level3_name 反例：AliyunAdmin，rdcConfig，level_3_name
【强制】表名不使用复数名词。说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于DO类名也是单数形式，符合表达习惯。
【强制】禁用保留字，如desc、range、match、delayed等，请参考MySQL官方保留字。
【强制】主键索引名为pk_字段名；唯一索引名为uk_字段名；普通索引名则为idx_字段名。说明：pk_ 即primary key；uk_ 即 unique key；idx_ 即index的简称。
【强制】小数类型为decimal，禁止使用float和double。说明：在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。
【强制】如果存储的字符串长度几乎相等，使用char定长字符串类型。
【强制】varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
【强制】表必备三字段：id, gmt_create, gmt_modified。说明：其中id必为主键，类型为bigint unsigned、单表时自增、步长为1。gmt_create, gmt_modified的类型均为datetime类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。
【推荐】表的命名最好是遵循“业务名称_表的作用”。正例：alipay_task / force_project / trade_config
【推荐】库名与应用名称尽量一致。
【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：1）不是频繁修改的字段。2）不是唯一索引的字段。3）不是varchar超长字段，更不能是text字段。正例：各业务线经常冗余存储商品名称，避免查询时需要调用IC服务获取。
【推荐】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。正例：无符号值可以避免误存负数，且扩大了表示范围。

2、索引规约

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
【强制】超过三个表禁止join。需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。说明：即使双表join也要注意表索引、SQL性能。
【强制】在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
【推荐】如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。正例：where a=? and b=? order by c; 索引：a_b_c 反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引a_b无法排序。
【推荐】利用覆盖索引来进行查询操作，避免回表。说明：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用explain的结果，extra列会出现：using index。
【推荐】利用延迟关联或者子查询优化超多分页场景。说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。正例：先快速定位需要获取的id段，然后再关联：SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
【推荐】SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。说明：1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。2） ref 指的是使用普通的索引（normal index）。3） range 对索引进行范围检索。反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。
【推荐】建组合索引的时候，区分度最高的在最左边。正例：如果where a=? and b=?，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使c的区分度更高，也必须把d放在索引的最前列，即建立组合索引idx_d_c。
【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。
【参考】创建索引时避免有如下极端误解：1）索引宁滥勿缺。认为一个查询就需要建一个索引。2）吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。3）抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决。

3、SQL语句

【强制】不要使用count(列名)或count(常量)来替代count()，count()是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。说明：count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。
【强制】count(distinct col) 计算该列除NULL之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。
【强制】当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL，因此使用sum()时需注意NPE问题。正例：可以使用如下方式来避免sum的NPE问题：SELECT IFNULL(SUM(column), 0) FROM table;
【强制】使用ISNULL()来判断是否为NULL值。说明：NULL与任何值的直接比较都为NULL。1） NULL<>NULL的返回结果是NULL，而不是false。2） NULL=NULL的返回结果是NULL，而不是true。3） NULL<>1的返回结果是NULL，而不是true。反例：在SQL语句中，如果在null前换行，影响可读性。select * from table where column1 is null and column3 is not null; 而ISNULL(column)是一个整体，简洁易懂。从性能数据上分析，ISNULL(column)执行效率更快一些。
【强制】代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。
【强制】不得使用外键与级联，一切外键概念必须在应用层解决。说明：（概念解释）学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更新学生表中的student_id，同时触发成绩表中的student_id更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
【强制】数据订正（特别是删除或修改记录操作）时，要先select，避免出现误删除，确认无误才能执行更新语句。
【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id; 反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出1052异常：Column ‘name’ in field list is ambiguous。
【推荐】SQL语句中表的别名前加as，并且以t1、t2、t3、…的顺序依次命名。说明：1）别名可以是表的简称，或者是根据表出现的顺序，以t1、t2、t3的方式命名。2）别名前加as使别名更容易识别。正例：select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;
【推荐】in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。
【参考】因国际化需要，所有的字符存储与表示，均采用utf8字符集，那么字符计数方法需要注意。说明：SELECT LENGTH(“轻松工作”)； 返回为12 SELECT CHARACTER_LENGTH(“轻松工作”)； 返回为4 如果需要存储表情，那么选择utf8mb4来进行存储，注意它与utf8编码的区别。
【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发trigger，有可能造成事故，故不建议在开发代码中使用此语句。说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

4、ORM映射

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。说明：1）增加查询分析器解析成本。2）增减字段容易与resultMap配置不一致。3）无用字段增加网络消耗，尤其是text类型的字段。
【强制】POJO类的布尔属性不能加is，而数据库字段必须加is_，要求在resultMap中进行字段与属性之间的映射。说明：参见定义POJO类以及数据库字段定义规定，在sql.xml增加映射，是必须的。
【强制】不要用resultClass当返回参数，即使所有类属性名与数据库字段一一对应，也需要定义；反过来，每一个表也必然有一个与之对应。说明：配置映射关系，使字段与DO类解耦，方便维护。
【强制】sql.xml配置参数使用：#{}，#param# 不要使用${} 此种方式容易出现SQL注入。
【强制】iBATIS自带的queryForList(String statementName,int start,int size)不推荐使用。说明：其实现方式是在数据库取到statementName对应的SQL语句的所有记录，再通过subList取start,size的子集合。

正例：

Map<String, Object> map = new HashMap<>();
map.put(“start”, start);
map.put(“size”, size);

【强制】不允许直接拿HashMap与Hashtable作为查询结果集的输出。反例：某同学为避免写一个，直接使用HashTable来接收数据库返回结果，结果出现日常是把bigint转成Long值，而线上由于数据库版本不一样，解析成BigInteger，导致线上问题。
【强制】更新数据表记录时，必须同时更新记录对应的gmt_modified字段值为当前时间。
【推荐】不要写一个大而全的数据更新接口。传入为POJO类，不管是不是自己的目标更新字段，都进行update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行SQL时，不要更新无改动的字段，一是易出错；二是效率低；三是增加binlog存储。
【参考】@Transactional事务不要滥用。事务会影响数据库的QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
【参考】中的compareValue是与属性值对比的常量，一般是数字，表示相等时带上此条件；表示不为空且不为null时执行；表示不为null值时执行。

五、数据类型选择

在MySQL中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步：

确定合适的大类型：数字、字符串、时间、二进制；
确定具体的类型：有无符号、取值范围、变长定长等。

在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL.

1、数值类型

优化建议：

如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
建议使用TINYINT代替ENUM、BITENUM、SET。
避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。
DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

2、日期和时间=

优化建议：

MySQL能存储的最小时间粒度为秒。
建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。
TIMESTAMP是UTC时间戳，与时区相关。
DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它。

3、字符串

优化建议:

字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。
尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
BLOB和TEXT都不能有默认值。

PS：INT显示宽度

我们经常会使用命令来创建数据表，而且同时会指定一个长度，如下。但是，这里的长度并非是TINYINT类型存储的最大长度，而是显示的最大长度。

CREATE TABLE `user`(
 `id` TINYINT(2) UNSIGNED
);

这里表示user表的id字段的类型是TINYINT，可以存储的最大数值是255。所以，在存储数据时，如果存入值小于等于255，如200，虽然超过2位，但是没有超出TINYINT类型长度，所以可以正常保存；如果存入值大于255，如500，那么MySQL会自动保存为TINYINT类型的最大值255。

在查询数据时，不管查询结果为何值，都按实际输出。这里TINYINT(2)中2的作用就是，当需要在查询结果前填充0时，命令中加上ZEROFILL就可以实现，如：

`id` TINYINT(2) UNSIGNED ZEROFILL

这样，查询结果如果是5，那输出就是05。如果指定TINYINT(5)，那输出就是00005，其实实际存储的值还是5，而且存储的数据不会超过255，只是MySQL输出数据时在前面填充了0。

换句话说，在MySQL命令中，字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入，只会在使用ZEROFILL时有用，让查询结果前填充0。

数据库

时区问题

2022-04-10 BUG处理

BUG处理

Canal数据同步

2022-03-27 数据库

数据库