高并发解决方案-mysql数据库层的优化

关注

原创最新推荐文章于 2026-02-21 10:42:29 发布 732 阅读 AI 写同款· GEO 优化 ›

高并发解决方案-mysql篇 1、mysql篇 高并发大多的瓶颈在后台，在存储，mysql的正常的优化方案如下： 1）代码中sql语句优化 2）数据库字段优化，索引优化 3）加缓存，redis/memcache等 4）主从，读写分离 5）分区表 6）垂直拆分，解耦模块 7）水平切分点评： 1、1&2是最简单，也是提升效率最快的方式。也许有人说这两点你已经做的很好了，你的每条语句都命中了索引，是最高阅读详情

mysql数据库层的优化

数据表数据类型
索引优化
sql语句
存储引擎
数据表结构设计
- 分区操作
- 分库分表
数据库架构

数据表数据类型

数据类型介绍

数值类型

整型

tinyint, smallint, mediumint, int ,bigint（8，16，24，32，64位存储空间）
属性：unsigned
int（3） 指定显示的宽度（显示字符的个数） ，不会限制值的合法范围
属性：zerofill 
12  int （3） zerofill  012

实数类型

float,double, decimal

decimal可存储比bigint还大的整数；可以用于存储精确的小数
float和double类型支持使用标准的浮点数进行近似计算

字符串类型

varchar，char，text，blob

varchar类型用于存储可变长字符串，它比定长更节省空间
varchar使用一个或两个额外字节记录字符串的长度，列长度小雨255字节，使用1个字节表示，否则用2个
varchar长度，如果存储内容超过指定长度，会被截断。

char是定长的，根据定义的字符串长度分配足够的空间
char会根据需要采用空格进行填充以方便比较
char适合存储很短的字符串，或者所有值都接近同一个长度
char长度，超过设定的长度，会被截断

对于经常变更的数据，char比varchar更好，char不容易产生碎片
对于非常短的列，char比varchar在存储空间上更有效率，只分配真正需要的空间，更长的列会消耗更多的内存。

尽量避免使用blob/text类型，查询会使用临时表，导致严重的性能开销。

枚举

有时可以使用枚举代替常用的字符串类型，把不重复的集合存储为一个预定义的集合，非常紧凑，把列表值压缩到一个或两个字节，内部存储的是整数。
尽量避免使用数字作为enum枚举的常量，易混乱
排序是按照内存存储的整数进行排序
枚举表会使表大小大大减小

日期和时间类型

尽量使用timestamp，比datetime空间效率更高
用整数保存时间戳的方式通常不方便处理
如果需要存储微秒，可以用bigint存储

date, time, year, datetime, timestamp
[
    YYYY-MM-DD (1000-01-01~9999-12-31)
    HH:MM:SS(-838:59:59~838:59:59)
    YYYY(1901~2155)
    YYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00~9999-12-31 23:59:59)
    YYYY-MM-DD HH:MM:SS(1970-01-01 00:00:00~2037)
    
]

列属性

auto_increment, default, not null, zerofill

优化

字段使用什么数据类型更合适
字段使用什么数据类型性能更快
举例：
考虑空间、范围

tinyint smallint bigint

考虑长度是否固定

char  手机号，md5后的密码（32位）
varchar 姓名

特定，固定的分类可以用enum，效率更快
ip 使用整型来存，可以用php内置函数转换`long2ip ip2long

索引优化

索引对性能的影响

大大减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机I/O变成顺序I/O
大大提高查询速度，降低写的速度，占用磁盘（缺点）

索引类型：（实现在存储引擎层）

普通索引：最基本的索引，没有任何约束限制
唯一索引：与普通索引类似，但是具有唯一性约束
主键索引 —特殊的唯一索引，不允许有空值，一个表只能有一个主键索引
组合索引：将多个列组合在一起创建索引，可以覆盖多个列
外键索引 —InnoDB，保证数据一致性，完整性和实现级联操作
全文索引 —MyISAM ,只能对英文进行全文索引

建立合适的索引

索引的创建原则

1.最适合索引的列是出现在WHERE子句中的列，或连接子句中的列而不是出现在SELECT关键字后的列

2.索引列的基数越大，索引的效果越好

3.对字符串进行索引，应该制定个前缀长度，可以节省大量的索引空间

4.根据情况创建复合索引，复合索引可以提高查询效率

5.避免创建过多索引，索引会额外占用磁盘空间，降低写操作效率

6.主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率

索引不是越多越好，在合适的字段创建合适的索引
复合索引的前缀原则

索引的注意事项

复合索引的前缀原则
like查询，%在前无法利用索引
column is null 也可以使用索引
如果mysql估计使用索引比全表扫描更慢，会放弃使用索引
如果or前的条件列有索引但是后面的没有，索引都不会被用到
字符串索引查询时需要加上‘’，否则会出现隐式转换，无法利用索引

sql语句

优化查询过程中的数据访问

数据访问太多导致查询性能下降
确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
确定mysql服务器是否在分析大量不必要的数据行

sql注意

查询不需要的记录，使用limit解决
多表关联返回需要的指定列，不要返回全部列
返回列不用*，总是取出全部列，select * 会让优化器无法完成索引覆盖扫描的优化
重复查询的数据，可以缓存，下次直接查询缓存

是否在扫描额外的记录

使用explain来进行分析，如果发现查询需要扫描大量的数据但只返回少数的行，可以通过如下技巧去优化:

使用索引覆盖扫描，把所有用的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果

改变数据库和表的结构，修改数据表范式

重写SQL语句，让优化器可以以更优的方式执行查询

优化长难句的查询语句

变复杂为简单

MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多
使用尽可能少的查询是好的，但是有时将一个大的查询分解为多个小的查询是很有必要的

切分查询

分解关联查询

可以将一条关联语句分解成多条SQL来执行
让缓存的效率更高
执行单个查询可以减少锁的竞争
在应用层做关联可以更容易对数据库进行拆分

优化特定类型的查询语句

优化count()

count(*)中的*会忽略所有的列，直接统计所有列数，因此不要使用count(列名)
MyISAM中，没有任何WHERE条件的count(*)非常快
当有WHERE条件，MyISAM的count统计不一定比其他表引擎快

优化关联查询

确定ON或者USING子句的列上有索引
确保GROUP BY和ORDER BY中只有一个表中的列，这样MySQL才有可能使用索引

优化子查询

尽可能使用关联查询来替代

优化group by 和distinct

这两种查询均可使用索引来优化，是最有效的优化方法关联查询中，使用标识列进行分组的效率会更高

如果不需要ORDER BY，进行GROUP BY时使用ORDER BY NULL，MySQL不会再进行文件排序

WITH ROLLUP超级聚合,可以挪到应用程序处理

优化limit和union

LIMIT偏移量大的时候，查询效率较低
可以记录上次查询的最大ID，下次查询时直接根据该ID来查询
UNION ALL 的效率高于 UNION
使用union all不会合并重复的记录行

存储引擎

InnoDB表引擎

默认事务型引擎，最重要最广泛的存储引擎，性能非常优秀
数据存储在共享表空间，可以通过配置分开
对主键查询的性能，高于其他类型存储引擎
内部做了很多优化，从磁盘读取数据时自动在内存构建hash索引
插入数据时，自动构建插入缓冲区
通过一些工具和机制支持真正的热备份
支持崩溃后的安全恢复
支持行级锁
支持外键

MyISAM表引擎

5.1以前，MyISAM是默认的存储引擎
拥有全文索引，压缩，空间函数
不支持事务和行级锁，不支持崩溃后的安全恢复
表存储在两个文件， .MYD和.MYI
设计简单，某些场景下性能很好

其他表引擎

Archive， Blackhole， CSV, Memory

尽量使用InnoDB存储引擎

数据表结构设计

分区操作

通过特定的操作对数据表进行物理拆分
对用户透明

 partition by

分库分表

水平拆分（行 - 查所有数据用union）
垂直拆分（列 - 查所有数据用join）

数据库架构

数据备份和恢复

全量备份（mysqldump）

全量备份的代价非常高：
首先，备份文件包含数据库中的所有数据，占用的磁盘空间非常大；其次，每次备份操作都要拷贝大量数据，备份过程中会占用数据库服务器大量的 CPU、磁盘 IO 资源，并且为了保证数据一致性，还有可能会锁表，这些都会导致备份期间，数据库本身的性能严重下降。

增量备份（binlog）

原理：
MySQL 自带了 Binlog，就是一种实时的增量备份。Binlog 里面记录的就是 MySQL 数据的变更的操作日志，开启 Binlog 之后，我们对 MySQL 中的每次更新数据操作，都会被记录到 Binlog 中。Binlog 是可以回放的，回放 Binlog，就相当于把之前对数据库所有数据更新操作按照顺序重新执行了一遍，回放完成之后数据自然就恢复了。