Mysql故障和优化

一、MySQL故障 

 

二、MySQL优化

         1.硬件优化： 

        2.数据库设计与规划

1.提前估计数据量，使用什么存储引擎

2.数据库服务器专机专用，避免额外的服务可能导致的性能下降和不稳定性

3.增加多台服务器，以达到稳定、高效的效果。主从同步、负载均衡、高可用性集群

        3.MySQL配置文件

通常默认的 my.cnf 配置文件无法发挥出 MySQL 最高的性能

下面是物理内存为 32G 的数据库优化参数，具体从全局、二进制日志、主从、innodb、myisam 几个方面优化

1.默认 MySQL 使用的是系统时区，修改为北京时间，也就是所说的东八区

default-time-zone=+8:00

2.服务器关闭交互式连接前等待活动的秒数

interactive_timeout = 120

3.服务器关闭非交互连接之前等待活动的秒数

wait_timeout = 120

4.MySQL 服务器打开文件句柄数限制

open_files_limit = 10240

5.MySQL 默认的拼接最大长度为 1024 个字节，由于 1024 个字节会出现不够用的情况， 根据实际情况进行修改

group_concat_max_len = 102400

6.使用 mysql 用户运行

user=mysql

7.设置字符集为 utf8

character-set-server=utf8、init_connect='SET NAMES utf8'

8.如果系统在短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的 TCP/IP 连接的监听队列的大小。默认值 50

back_log = 600

9.MySQL 允许最大的进程连接数，如果经常出现 Too Many Connections 的错误提示， 则需要增大此值

max_connections = 5000

10.设置每个主机的连接请求异常中断的最大次数

max_connect_errors = 6000

11.数据表调整缓冲区大小。它设置表高速缓存的数目

table_cache = 1024

12.指定表高速缓存的大小

table_open_cache = 2048

13.用户可以创建的内存表(memory table)的大小

max_heap_table_size = 256M

14.使用 skip-external-lockingMySQL 选项以避免外部锁定。该选项默认开启

external-locking = false

15.设置在网络传输中一次消息传输量的最大值。系统默认值为 1MB，最大值是 1GB，必须设置 1024 的倍数

max_allowed_packet = 32M

        4，MySQL语句优化

1.建表时表结构要合理，每个表不宜过大；在任何情况下均应使用最精确的类型

2.索引，建立合适的索引。

3.查询时尽量减少逻辑运算（与运算、或运算、大于小于某值的运算）；

4.减少不当的查询语句，不要查询应用中不需要的列，比如说 select * from  等操作。

5.减小事务包的大小；

6.将多个小的查询适当合并成一个大的查询，减少每次建立/关闭查询时的开销；

7.将某些过于复杂的查询拆解成多个小查询，和上一条恰好相反

8.建立和优化存储过程来代替大量的外部程序交互。

5.索引优化

• 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）
• 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
• 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
• 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
• 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
• 对于经常在where子句使用的列，最好设置索引
• 对于有多个列where或者order by子句，应该建立复合索引
• 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
• 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
• 不要使用RLIKE正则表达式会导致索引失效
• 查询时，能不要就不用，尽量写全字段名，比如:select id,name,age from students;
• 大部分情况连接效率远大于子查询
• 在有大量记录的表分页时使用limit
• 对于经常使用的查询，可以开启查询缓存
• 多使用explain和profile分析查询语句
• 查看慢查询日志，找出执行时间长的sql语句优化

6.操作系统优化

1.网卡 bonding 技术

2.设置TCP连接数量限制，优化系统打开文件的最大限制。

3.使用64位操作系统，64位系统可以分给单个进程更多的内存，计算更快 。禁用不必要启动的服务

4.文件系统调优，给数据仓库一个单独的文件系统，推荐使用XFS，一般效率更高、更可靠。

5.可以考虑在挂载分区时启用 noatime 选项。 #不记录访问时间最小化原则:

6.安装系统最小化。

7.开启程序服务最小化原则。

8.操作最小化原则。

9.登录最小化原则。

10.权限最小化。

7.MYSQL服务优化

保持每个表都不要太大，可以对大表做横切和纵切

对查询进行缓存

强制限制 mysql 资源设置,确保系统负载不会导致资源耗尽的情况出现。

8.my.cnf 内参数的优化

给 MySQL的资源太少，则 MySQL施展不开：给 MySQL的资源太多，可能会拖累整个 OS

---->40%资源给 OS， 60%-70% 给MySQL（内存和 CPU）