Mysql优化

表名/编码/连接数/数据包大小

设置表名不区分大小写/字符编码/连接数

修改 /etc/my.cnf

vim /etc/my.cnf

添加以下的4行

[mysqld]
lower_case_table_names=1
character_set_server = utf8
max_connections = 1000
max_allowed_packet = 100M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

设置后保存 重新启动

service mysqld restart

设置缓存

在项目用redis等缓存后,建议关闭数据库缓存

修改 /etc/my.cnf

query_cache_type=1
query_cache_size=64M
query_cache_limit=1M

设置后保存 重新启动

service mysqld restart

是否启用mysql查询缓存,可以通过2个参数:query_cache_typequery_cache_size

其中任何一个参数设置为0都意味着关闭查询缓存功能。

  • query_cache_type 值域为:0 -– 不启用查询缓存; 值域为:1 -– 启用查询缓存,只要符合查询缓存的要求,客户端的查询语句和记录集斗可以缓存起来,所有其他客户端共享使用; 值域为:2 -– 启用查询缓存,只要查询语句中添加了参数:SQL_CACHE,且符合查询缓存的要求,客户端的查询语句和记录集,则可以缓存起来,共其他客户端共享使用;
  • query_cache_size 允许设置query_cache_size的值最小为40K,对于最大值则可以几乎认为无限制,实际生产环境的应用经验告诉我们,该值并不是越大, 查询缓存的命中率就越高,也不是对服务器负载下降贡献大,反而可能抵消其带来的好处,甚至增加服务器的负载,至于该如何设置,下面的章节讲述,推荐设置 为:64M;
  • query_cache_limit 限制查询缓存区最大能缓存的查询记录集,可以避免一个大的查询记录集占去大量的内存区域,而且往往小查询记录集是最有效的缓存记录集,默认设置为1M,建议修改为16k~1024k之间的值域,不过最重要的是根据自己应用的实际情况进行分析、预估来设置;
  • query_cache_min_res_unit 设置查询缓存分配内存的最小单位,要适当地设置此参数,可以做到为减少内存块的申请和分配次数,但是设置过大可能导致内存碎片数值上升。默认值为4K,建议设置为1k~16K
  • query_cache_wlock_invalidate 该参数主要涉及MyISAM引擎,若一个客户端对某表加了写锁,其他客户端发起的查询请求,且查询语句有对应的查询缓存记录,是否允许直接读取查询缓存的记录集信息,还是等待写锁的释放。默认设置为0,也即允许;

长时间未访问 断开

mysql有一个连接超时时间的概念。。。查询此项目的数据库的连接超时时间为28800秒,即为8小时。。

mysql> show global variables like 'wait_timeout';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | wait_timeout  | 28800 |
 +---------------+-------+
 1 row in set

于是不难得知,肯定是由于项目长时间没有请求数据库,数据库过了8小时和这个连接超时时间之后,就会断开连接。。

而我们的项目使用的是c3p0的连接池,,过了8小时后,连接池中的连接已经被mysql断开了,即连接失效。。

但是c3p0认为此连接却依然有效,此时当我们发请求请求数据库中的数据时,由于连接失效,并不能去连接数据库操纵数据,所以服务器会抛出一个500的错误

问题的原因已经找到,那我们该怎么解决呢??

其实解决起来很简单的。。

先说第一种办法吧,就是将数据库的连接超时时间设置大一点,

msyql> set global wait_timeout=1814400;
msyql> set global interactive_timeout=1814400;

当然这种办法我并不推荐,,这个办法不太好,弊端太多了 比如占用数据库资源,关键是这种办法并不能彻底根治mysql连接断开这种情况

所以我推荐第二种办法:设置c3p0隔多少时间自动检测与数据库的连接,如果断开则自动重连

<bean id="pooledDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">  
    <property name="jdbcUrl" value="${jdbc.jdbcUrl}"></property>  
    <property name="driverClass" value="${jdbc.driverClass}"></property>  
    <property name="user" value="${jdbc.user}"></property>  
    <property name="password" value="${jdbc.password}"></property>  
	//下面两个属性就是设置c3p0隔28800秒自动检测与数据库的连接(28800也是mysql的默认的连接超时时间)  
    <property name="testConnectionOnCheckin" value="true"></property>  
    <property name="idleConnectionTestPeriod" value="28800"></property>  
</bean>

获取慢查询

查询Mysql版本

select version();

或者

mysql --version

获取现在的配置

show variables like '%slow%';
show variables like 'long_query_time';

开启

set global long_query_time=5;
set global slow_query_log=1;

关闭

set global slow_query_log=0;

创建文件

cd /var/log/
mkdir mysql
cd mysql
vi mysql-slow.log

设置权限

chmod a+w /var/log/mysql/mysql-slow.log

打开配置文件

vi /etc/my.cnf

找到[mysqld]下面加上

slow_query_log = 1 
slow-query-log-file = /var/log/mysql/mysql-slow.log 
long_query_time = 5
  • long_query_time = 3中的3表示查询超过3秒才记录;

清空日志后慢查询就不会继续写入了,重启后才能继续写入

set global slow_query_log=0;
set global slow_query_log=1;

连接优化

常见的连接参数

jdbc:mysql://localhost:3306/test?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true
  • characterEncoding=utf8 字符编码utf8
  • zeroDateTimeBehavior=convertToNull 时间传入了0000-00-00 自动转换为null
  • rewriteBatchedStatements=true 高性能的批量插入或更新(保证5.1.13以上版本的驱动)

Linux 内核参数优化

将vm.swappiness设置为0-10

具体这样做:

1) 查看你的系统里面的swappiness

cat /proc/sys/vm/swappiness

不出意外的话,你应该看到是0

2) 修改swappiness值为10

sudo sysctl vm.swappiness=10

但是这只是临时性的修改,在你重启系统后会恢复默认的值,所以,还要做一步:

vi /etc/sysctl.conf

在这个文档的最后加上这样一行:

vm.swappiness = 10

然后保存,这样重启之后配置也不会失效。


将vm.dirty_background_ratio设置为5-10,将vm.dirty_ratio设置为它的两倍左右,以确保能持续将脏数据刷新到磁盘,避免瞬间I/O写,产生严重等待

cat /proc/sys/vm/dirty_background_ratio
cat /proc/sys/vm/dirty_ratio

sudo sysctl vm.dirty_background_ratio=10
sudo sysctl vm.dirty_ratio=20

my.cnf参数优化

此优化主要针对innodb引擎

创建慢查询日志文件

cd /var/log/
mkdir mysql
cd mysql
vi mysql-slow.log

打开配置文件

vi /etc/my.cnf

找到[mysqld]下面加上

[mysqld]
lower_case_table_names=1
character_set_server = utf8
max_allowed_packet = 100M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 强烈推荐采用innodb引擎,
default-storage-engine = InnoDB

# 连接排队列表总数
# back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说,如果MySql的连接数达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源
back_log = 200
max_connections = 1000

# 查询结果缓存
# 建议关闭query cache功能或降低设置不要超过512M
# 如果项目用了redis等缓存,建议把数据库缓存关闭
query_cache_type=0
query_cache_size=64M
query_cache_limit=1M

# 取消文件系统的外部锁
skip-external-locking

# 默认,innodb_flush_log_at_trx_commit=1,表示在每次事务提交的时候,都把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话,数据库对IO的要求就非常高了,如果底层的硬件提供的IOPS比较差,那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。
# innodb_flush_log_at_trx_commit=0时,每隔一秒把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话,可能丢失1秒的事务数据。
# innodb_flush_log_at_trx_commit=2时,在每次事务提交的时候会把log buffer刷到文件系统中去,但是每隔一秒调用文件系统的“flush”操作将缓存刷新到磁盘上去。如果只是MySQL数据库挂掉了,由于文件系统没有问题,那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下,数据库的事务数据可能丢失1秒之类的事务数据。这样的好处就是,减少了事务数据丢失的概率,而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中,一般只是从log buffer的内存转移的文件系统的内存缓存中,对底层IO没有压力)。MySQL 5.6.6以后,这个“1秒”的刷新还可以用innodb_flush_log_at_timeout 来控制刷新间隔。
innodb_flush_log_at_trx_commit=2

# 索引缓存,根据内存大小而定,如果是独立的db服务器,可以设置高达80%的内存总量
# 指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器来说,该参数可设置为256MB或384MB。
key_buffer_size = 512M

# 打开表缓存总数,可以避免频繁的打开数据表产生的开销
# 给经常访问的表分配的内存,物理内存越大,设置就越大。调大这个值,一般情况下可以降低磁盘IO,但相应的会占用更多的内存,这里设置为614。
table_open_cache = 614 

# 每个线程排序所需的缓冲
sort_buffer_size = 4M

# 每个线程读取索引所需的缓冲
read_buffer_size = 4M

# 缓存可重用的线程数
thread_cache_size = 128

# 记录慢查询,然后对慢查询一一优化
slow_query_log = 1 
slow-query-log-file = /var/log/mysql/mysql-slow.log 
long_query_time = 5


# 调整innodb_buffer_pool_size大小,考虑设置为物理内存的50%-60%左右。
# InnoDB使用一个缓冲池来保存索引和原始数据,设置越大,在存取表里面数据时所需要的磁盘I/O越少。
# 强烈建议不要武断地将InnoDB的Buffer Pool值配置为物理内存的50%~80%,应根据具体环境而定。
innodb_buffer_pool_size = 3096M  

# 设置innodb_file_per_table = 1,使用独立表空间
innodb_file_per_table = 1

# 设置innodb_log_file_size=256M,设置innodb_log_files_in_group=2,基本可满足90%以上的场景;
# 不要将innodb_log_file_size参数设置太大,这样可以更快同时又更多的磁盘空间,丢掉多的日志通常是好的,在数据库崩溃后可以降低恢复数据库的事件
innodb_log_file_size=256M
innodb_log_files_in_group=2


# 设置内存临时表最大值。如果超过该值,则会将临时表写入磁盘,其范围1KB到4GB。
tmp_table_size = 64M  

# 独立的内存表所允许的最大容量。
max_heap_table_size = 64M

关于库表的设计规范

  1. 推荐utf-8字符集,虽然有人说谈没有latin1快
  2. 固定字符串的列尽可能多用定长char,少用varchar 存储可变长度的字符串使用VARCHAR而不是CAHR—节省空间,因为固定长度的CHAR,而VARCHAR长度不固定(UTF8不愁此影响)
  3. 所有的InnoDB表都设计一个无业务的用途的自增列做主键
  4. 字段长度满足需求前提下,尽可能选择长度小的
  5. 字段属性尽量都加NOT NULL约束(空的字段不能走索引,查询速度慢) 对于某些文本字段,例如“省份”或者“性别”我们可以将他们定义为ENUM类型
  6. 尽可能不使用TEXT/BLOB类型,确实需要的话,建议拆分到子表中,不要和主表放在一起,避免SELECT*的时候读性能太差。
  7. 读取数据时,只选取所需要的列,不要每次都SELECT 避免产生严重的随机读问题,尤其是读到一些TEXT/BLOB类型,确实需要的话,建议拆分到子表中,不要和主表放在一起,避免SELECT的时候读性能太差
  8. 对一个VARCHAR(N)列创建索引时,通常取其50%(甚至更小)左右长度创建前缀索引就足以满足80%以上的查询需求了,没必要创建整列的全长度索引。
  9. 多用符合索引,少用多个独立索引,尤其是一些基础(Cardinality)太小(如果说:该列的唯一值总数少于255)的列就不要创建独立索引了。

SQL语句的优化

  1. 白名单机制一百度 1)项目开发,DBA参与,减少上线后的慢SQL数据 抓出慢SQL,配置my.cnf slow_query_log = 1 slow-query-log-file = /var/log/mysql/mysql-slow.log long_query_time = 2 按天轮询:slow-log.log 2)慢查询的日志分析工具——mysqlsla或pt-query-digest(推荐) pt-quey-diges,mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfileter 3) 每天晚上0点定时分析慢查询,发到核心开发,DBA分析,及高级运维,CTO的邮箱里 DBA分析给出优化建议–>核心开发确认更新–>DBA线上操作处理 4) 定期使用pt-duplicate-key-checker检查并删除重复的索引 定期使用pt-index-usage工具检查并删除使用频率很低的索引 5)使用pt-online-schema-change来完成大表的ONLINE DDL需求 6)有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX 7)使用explain及set profile优化SQL语句
  2. 大的复杂的SQL语句拆分成多个小的SQL语句
  3. 数据库是存储数据的地方,但不是计算数据的地方
  4. 搜索功能,like '%haha%' 一般不要用MySQL数据库 比如这个语句在这个数据库查询很慢: select a from tms where b like ‘%haha%’ order by time limit 100; 第一种优化方法(注意:这种方法只适用于haha开头的): 将haha字段和time字段加索引(联合索引还是普通索引自己看情况) select a from news where b like ‘haha%’ order by time limit 100; 第二种优化方法(注意:这种方法只适用于mysql引擎是myisam的): 语句不变,将haha字段加为全文索引,time字段变为普通索引 select a from news where b like ‘%haha%’ order by time limit 100; 第三种方法: 这是因为我必须用 %haha% 这种方式,而且还不能更换数据库引擎的情况下。 我是把like的操作放到了java程序中来处理,一次取定量数据进行筛选,如果没取够,再取,再筛选……这样就避免了完全扫表了,只扫部分数据。成功解决。
  5. 使用连接(JOIN)来代替子查询(Sub_Queries)
  6. 避免在整个表上使用count(*),它可能锁住整张表
  7. 多表联接查询时,关联字段类型尽量一致,并且都要有索引。
  8. 在WHERE子句中使用UNION代替子查询
  9. 多表连接查询时,把结果集小的表(注意,这里是指过滤后的结果集,不一样是全表数据量小的)作为驱动表

LIMIT 1

如果sql语句返回只可能只有一条,一定要添加LIMIT 1,来避免全部检索。

索引

参见:Mysql优化-索引

Show Profile【重点】

是什么

mysql提供可以用来分析当前会话中语句执行的资源消耗情况。 可以用于SQL的调优的测量,相比explain,show profile展示的数据更加详尽。

怎么用

-- 查看是否开启
show variables like 'profiling';

-- 开启功能,默认是关闭,使用前需要开启
set profiling=1;

-- 查看结果
show profiles;

-- 诊断SQL
show profile cpu,block io for query <query_id>;
-- 还可以下面这句通过获取
SELECT * FROM information_schema.profiling WHERE query_id = <query_id> ORDER BY seq;

网站集成架构优化

  1. 服务器上跑多实例,2-4个(具体需要看服务器的硬件信息)
  2. 主从复制一主五从,采用mixed模式(混合或行模式),尽量不要跨机房同步(进程远程读本地写),(数据要一致,拉光纤,没有网络延迟)
  3. 定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异(重构)
  4. 业务拆分:搜索功能,like ‘%oldboy% ‘ 一般不要用MySQL数据库
  5. 业务拆分:某些业务应用使用nosql持久化存储,例如:memcached、redis、ttserver 例如粉丝关注,好友关系等
  6. 数据库前端必须要加cache,例如:redis,用户登录,商品查询
  7. 动态的数据库静态化,整个文件静态化,页面片段静态化
  8. 数据库集群与读写分离。一主多从,通过程序或dbproxy进行集群读写分离
  9. 单表超过800万,拆库拆表。人工拆表拆库(登录、商品、订单)
  10. 百度、阿里国内前三公司,会选择从库进行备份,对数据库进行分库分表

MySQL基础安全

  1. 启动程序700,属主和用户组为MySQL。
  2. 为MySQL超级用户root设置密码。
  3. 如果要求严格可以删除root用户,创建其他管理用户,例如admin。
  4. 登录时尽量不要在命令行暴露密码,备份脚本中如果有密码,给设置700,属主和密码组为mysql或root。
  5. 删除默认存在的test库。
  6. 初始删除无用的用户,只保留。 | root | 127.0.0.1 | | root | localhost |
  7. 不要一个用户管理所有的库,尽量专库专用户(少量库)
  8. 清理mysql操作日志文件~/.mysql_history(权限600,可以不删)
  9. 禁止开发获得到web连接的密码,禁止开发连接操作生产对外的库
  10. 服务器禁止设置外网IP
  11. 防SQL注入(WEB)php.ini或web开发插件监控,waf控制

常见错误解决

The total number of locks exceeds the lock table size错误

show variables like "innodb_buffer_pool_size";

默认为8M

修改 innodb_buffer_pool_size的值为3G:

3*1024*1024*1024,不要忘记;号

SET GLOBAL innodb_buffer_pool_size=67108864;

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券