第23天，记录一下平时的SQL注意事项

最新推荐文章于 2023-07-28 10:22:25 发布

AlexNii

最新推荐文章于 2023-07-28 10:22:25 发布

阅读量147

点赞数

文章标签：字符串数据库 mysql java 运维

本文链接：https://blog.csdn.net/weixin_40920882/article/details/105373916

版权

想去阿里的那些天专栏收录该内容

25 篇文章 1 订阅

订阅专栏

MySQL单表记录数过大的时候，增删改查的性能都会急剧下降，下面记录一下优化的策略

单表优化

除非单表的数据未来会一直不断的上涨，否则不要一开始就考虑拆分，拆分会带来逻辑，部署，运维的各种复杂度，一般以整型为主的表在千万级一下，字符串在五百万以下是没有什么大问题的。而事实上很多时候MySQL的性能依然存在不少优化的空间，甚至能支撑千万级的数据量

字段

尽量使用TINYINT,SMALLINT,MEDIUN_INT作为整型类型而非INT，如果非负则加上UNSIGNED
VARCHAR的长度适当
使用枚举或者整型代替字符串类型
尽量使用TIMESTAMP而不是DATETIME
单表字段不宜过多，建议在20个以下
避免使用null字段，很难查询优化占用额外的索引空间

索引

索引并不是越多越好，而是要根据查询进行针对性的创建，考虑where和order上涉及的列创建索引，可以使用explain来查看是否用了索引还是全表扫描
尽量避免在where子句中对字段进行null值判断，这将导致引擎放弃使用索引而进行全表扫描
对于值分布很少的字段不适合建立索引，如性别这类字段只有几个值得字段
字符字段只建前缀索引
字符串字段最好不要做主键
不要用外键，而是通过程序控制
尽量不要NUIQUE，而是用程序控制
使用多列索引的时候注意顺序与查询条件一致，同时删除不必要的单列索引

查询SQL

开启慢查询日志找出慢的SQL
不做列运算（select id where age+1 = 10），任何对列的操作都会导致全表扫描，包括数据库函数，计算表达式，查询时尽量将操作移到等号右边
SQL语句尽可能的简单，一条SQL只能在一个cpu中运算，大语句拆成小语句，减少锁时间，一条大SQL可以堵死整个数据库
不要用select *
OR改写成IN，or的效率是n级别的，而in的效率是log（n）级别，in的个数建议在200以内
不使用函数和触发器
避免%xxx式的查询
少用join
使用同类型的比较，如“123”和“123”，123和123
尽量避免where中使用！=或者<>操作符，这将导致引擎放弃使用索引而进行全表扫描
对于连续的值使用brtween而不是in
列表数据不要拿全表，使用limit来分页，每页数据不要过大

引擎

MyISAM

MySQL5.1之前的默认引擎

不支持行锁
读取的时候需要对所有读到的表加锁，写入的时候则对表加排他锁
不支持事务
不支持外键
不支持奔溃后的安全恢复
在表有读取数据的时候支持插入新的数据
支持BLOB和TEXT的前500个字符索引，支持全文索引
支持延迟更新索引，以提高写入性能
对于不会修改的表，支持压缩表，极大的减少磁盘空间的占用

InnoDB

支持行锁，采用MVCC来支持高并发
支持事务
支持外键
支持奔溃后的安全恢复
不支持全文索引

总体来说MyISAM适合select密集的表，InnoDB适合insert和update密集的表

系统性能的调优

可以使用下面几个工具来做基准的测试

sysbench：一个模块化，跨平台以及多线程的性能测试工具
libench-mysql：基于Java的MySQL/Percona/MariaDB索引进行插入性能测试的工具
tpcc-mysql：Percona开发的TPC-C测试工具

这里介绍一下重要的参数

back_log：back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中，也就是说，如果MySQL的连接数据达到max_connection时，新来的请求会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即为back_log，如果等待的连接数超过kack_log,将不被授予连接资源，可以从默认的50提升至500
wait-timeout：数据库连接闲置时间，闲置连接占用系统资源，可以从默认的八小时改成半小时
max_user_connection：最大连接数默认为0无上限，最好设置一个合理的上限
thread_concurrentcy：并发线程数，设为CPU核心数的两倍
skip_name_resolve：禁止对外部连接进行DNS解析，消除DNS解析时间，但需要所有远程主机用IP访问
key_buffer_size：索引块缓存大小，增加会提升索引的处理速度，对MyISAM表性能影响最大。对于内存4G左右，可以设置为256M或者384M，通过查询show status like ‘key_read%’，可以key_read / 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越高越好
innobd_addition_mem_pool_size：InnoDB存储引擎用来存放数据字典信息以及一些内部数据的内存空间大小，当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL会记录Warning信息到数据库的错误日志中，这时就需要该调整这个参数大小
innodb_log_buffer_size：InnoDB存储引擎的事务日志所使用的缓冲区，一般来说不建议超过32MB
query_cache_size：缓存MySQL的resultSet，就是一条SQL语句的执行结果集，所以仅仅只针对select语句，当某张表的数据发送变化的时候，都会导致所有引用了该表的select语句在缓存中的缓存数据失效，所以，当我们的数据变化频繁的时候使用query_cache_size可能会得不偿失，根据命中率：(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256M就够了，大型配置静态数据可适当调大，可以通过show status like 'Qcache_%'查看目前系统Query cache使用大小
read_buffer_size:MySQL执行排序使用的缓冲大小，如果按照。如果想增加order by的速度，首先看是否可以让MySQL使用索引而不是增加额外的排序阶段，如果不能，可以尝试增加sort_buffer_size变量的大小
read_rnd_buffer_size：MySQL的随机读缓冲区，当按照任意顺序读取行的时候，将分配一个随机读缓冲区，比如进行排序查询时，MySQL会首先扫描一遍该缓冲，可以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当提高该值，以避免内存开销过大
record_buffer：每个进行顺序扫描的线程，为其扫描的表分配这个大小的一个缓冲区，如果你做很多顺序扫描，可能会想要增加该值
thread_cache_szie：保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的
table_cache：类似于thread_cache_size，用于缓存表文件，对InnoDB效果不大，主要用于MyISAM

缓存

缓存发生在以下层次：

MySQL内部：上文已经讲了
数据访问层：比如MyBatis针对SQL语句做的缓存，而Hibernate可以精确到单条记录，这里缓存的对象主要是持久化对象Persistion Object
应用服务层：这里可以通过编程手段对缓存做到更精确的控制和更多的实现策略，这里缓存的对象是数据传送对象Data Transfer Object
web层：针对web页面做缓存
浏览器缓存：用户端的缓存

根据实际情况可以在一个或者多个层次结合加入缓存

直写式（Write Through）：在数据写入数据库后同时更新缓存，维持数据库和缓存的一致性，这也是当前大多数应用缓存框架如Spring Cache的工作方式，实现简单，同步好，但效率一般
回写式（Write Back）:当有数据写入数据库的时候，之会更新缓存，然后异步批量的将缓存数据同步到数据库上，这种实现比较复杂，需要较多的应用逻辑，同时可能会产生数据库和缓存的不同步，但效率高

表分区

MySQL在5.1引入的分区是一种简单的水平拆分，用户需要在建表的时候增加分区的参数，对于应用来说是透明的是无序修改代码的

对于用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实际上是通过对一组底层表的对象封装，但对于SQL层来说是一个完全封装底层的黑盒子，MySQL实现分区的方式也意味着索引也是按照分区的子表来定义的，没有全局索引

用户的SQL语句是需要针对分区表进行优化的，SQL条件中要带上分区条件的列，从而使查询定位到少量的分区上，否则就会扫描全部分区，可以通过explain partitions来查看某条SQL语句会落在那些分区上，从而进行SQL优化，下图五条记录落在两个分区上：

mysql> explain partitions select count(1) from user_partition where id in (1,2,3,4,5);
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table          | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user_partition | p1,p4      | range | PRIMARY       | PRIMARY | 8       | NULL |    5 | Using where; Using index |
+----+-------------+----------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

分区的好处

可以让单表存储更多的数据
分区表的数据更加容易维护，可以通过清除整个分区来批量删除大量的数据，也可以通过增加一个新的分区表来支持新插入的数据，另外还可以对一个独立分区进行优化，检查，修复等操作
部分查询能够从查询条件确定只落在少数分区上，速度更快
分区表的数据可以分布在不同的物理设备上，从而达到可以使用多个物理硬件
可以避免某些特殊的瓶颈，例如InnoDB单个索引的互斥访问，ext3文件系统的inode锁竞争
可以备份和恢复单个分区

缺点：

一个表只能有1024个分区
如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引都必须包含进来
分区无法使用外键约束
null值会使分区过滤无效
所有分区必须使用相同的存储引擎

分区的类型

RANGE分区：基于一个给定连续的区间的列值，把多行分配给分区
LIST分区：类似于RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
HASH分区：基于用户定义的表达式的返回值进行选择的分区，该表达式使用将要插入到表中的这些列值进行计算。这个函数可以包含MySQL中有效的产生非负整数值得任何表达式
KEY分区：类似HASH分区，区别在于key分区只支持计算一列或者多列，且MySQL服务器提供自身的哈希函数，必须一列或者多列包含整数值

分区适合的场景：

最适合场景数据的时间序列性比较强，可以按照时间来区分：

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能容易批量删除

如果数据没有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在一个分区中，让这个分区的数据能够有机会被缓存在内存中，查询时只需要访问一个很小的分区表就行，能够有效的使用索引和缓存

另外MySQL有一种早期的简单分区实现-合并表（merge table），限制较多且缺乏优化，建议使用，应该使用新的分区机制来替代

垂直拆分

垂直拆分是根据数据库里的数据表的相关性进行拆分，比如：一个数据库既存在用户的数据，又存在订单数据，那么垂直拆分可以把用户数据放到用户库中，订单数据放到订单库中，垂直分表是对数据库进行垂直拆分的一种方式，常见的是把一个多字段的大表按照常用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用主键关联
原始的用户表：

垂直拆分之后：

优点：

可以使得行数据变小，数据块（Block）就能存放更多的数据，查询时减少IO的次数（每次查询的时候读取的Block就少）
可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变得字段放在一起，将经常用到的放在一起
数据维护简单

缺点：

主键会出现冗余，需要管理冗余列
会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库的压力
依然存在单表数据量过大的问题（需要水平拆分）
事务处理复杂

水平拆分

水平拆分是通过某种策略将数据分片来存储，分库内分表和分库两部分，每片数据会分散到不同的MySQL表或者库中，达到分布式的效果，能够支持非常大的数据量，前面的表分区本质上也是一种特殊的库内分表

库内分表，仅仅是单纯的解决单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有起到太大的作用，大家还是在竞争同一台物理设备上的IO、CPU、网络，这个需要通过分库来解决

前面垂直拆分的树用户表如果进行水平拆分，结果如下：

实际情况是水平拆分和垂直拆分结合，即将Users_A_M和Users_N_Z再拆成Users和UserExtras，这样一共四张表

优点：

不存在单库的大数据和高并发的性能瓶颈
应用端改造较少
提高系统的稳定性和负载能力

缺点

分片事务一致性难以解决
跨节点join性能差，逻辑复杂
数据多次拓展难度和维护量极大

分片原则

能不分就不分，参考单表优化
分片数量尽量少，分片尽量均匀分布在多个数据节点上，因为一个查询跨分片越多，则总体性能越差，虽然要好过所有数据在一个分片的结果，只要在必要的时候进行扩容，增加分片数量
分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片的关联性问题，以及分片扩容的问题最近的分片模式为范围分片，枚举分片，一致性hash分片，这几种分片都有利于扩容
尽量不要在一个事务中的SQL跨越多个分片，分布式事务一直都是一个不好处理的问题
查询条件尽量优化，尽量避免select *的方式，大量数据结果集下，会消耗大量的带宽和cpu资源，尽量避免返回大量的结果集，并且尽量为频繁使用的查询语句建立索引
通过数据冗余和表分区依赖降低join的可能

解决方案

由于水平拆分牵涉的逻辑比较复杂，当前也有不少比较成熟的解决方案，分为两大类：客户端结构和代理架构

客户端架构

通过修改数据库访问层，如jdbc、Data Source、MyBatis，通过配置来管理多个数据源，直连数据库，并在模块内完成数据的分片整合，一般以jar包的方式管理

可以看到分片的实现是和应用服务器在一起的，通过修改Spring JDBC来实现

优点：

应用直连数据库，降低外围系统依赖所带来的宕机风险
集成成本低，无需额外运维的组件

缺点：

限于只能在数据库访问层上做文章，拓展性一般，对于复杂的系统可能会力不从心
将分片逻辑放在应用服务器上，造成额外的风险

代理架构

通过独立中间件来同一管理所有的数据源分片整合，后端数据库对前端应用程序透明，需要独立部署和运维代理组件

代理组件为了分流和防止单点，一般以集群形式存在，同时可能需要Zookeeper之类的服务组件来管理

优点：

能够处理非常复杂的需求，不受数据库访问层来实现控制，拓展性增强
对于应用服务器透明且没有额外的负载

缺点：

需要部署和运维独立的代理中间件，成本高
应用需要经过代理连接数据库，网络层多了一跳，性能有损失且有额外的风险

各方案比较

选择的思路

确定是使用代理架构还是客户端架构，中小型规模或者简单的场景倾向于选择客户端架构，复杂场景或者大规模的系统倾向于代理架构
具体功能十分满足，比如跨节点Order By，那么支持该功能的优先考虑
不考虑一年内没有更新的产品，说明开发停滞，甚至无人维护和技术支持
最好按大公司-》社区-》小公司-》个人这样的出品顺序进行选择
选择口碑好的，比如github的星数，使用者的数量和反馈
开源的优先，往往项目有特殊需求可能需要改动源代码

兼容MySQL且可以水平拓展的数据库

目前开源数据库兼容MySQL协议的：

TiDB
Cubrid

元数据库

阿里云PetaData
阿里云OceanDase
腾讯云DCDB

##NOSQL
在MySQL上做Sharding是一种戴着镣铐的跳舞，事实上很多大表本身对MySQL这种RDBMS的需求并不大，并不要求ACID，可以考虑将这些表迁移到NoSQL，彻底解决水平扩展问题，例如：

日志类，监控类，统计类数据
非结构化或者弱结构化数据
对事物要求不强，并没有太大关联操作的数据

转载自https://mp.weixin.qq.com/s/jx4_dvOw8BvoZ0jfUaFXIQ

终于完整的看了一篇，有抄一遍，希望能够有用得到的地方

AlexNii

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
第23天，记录一下平时的SQL注意事项

MySQL单表记录数过大的时候，增删改查的性能都会急剧下降，下面记录一下优化的策略单表优化除非单表的数据未来会一直不断的上涨，否则不要一开始就考虑拆分，拆分会带来逻辑，部署，运维的各种复杂度，一般以整型为主的表在千万级一下，字符串在五百万以下是没有什么大问题的。而事实上很多时候MySQL的性能依然存在不少优化的空间，甚至能支撑千万级的数据量字段尽量使用TINYINT,SMALLINT...
复制链接

扫一扫