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_type和query_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;