MySQL数据库优化和MySQL故障

一、MySQL数据库优化

硬件优化
CPU:推荐使用S.M.P.架构的多路对称CPU

内存:4GB以上的物理内存

磁盘:RAID-0+1磁盘阵列或固态硬盘

MySQL配置文件优化
调整配置项

SQL优化
尽量使用索引进行查询
优化分页
GROUP BY优化

MySQL架构优化

架构选择:主从、主主、一主多从、多主多从

1.1.使用索引

设置了合适的索引之后,数据库利用各种快速定位技术,能够大大加快查询速度,这是创建索引的最主要的原因。

当表很大或查询涉及到多个表时,使用索引可以成千上万倍地提高查询速度。

可以降低数据库的IO成本,并且索引还可以降低数据库的排序成本。

通过创建唯一(键)性索引,可以保证数据表中每一行数据的唯一性。

可以加快表与表之间的连接。

在使用分组和排序时,可大大减少分组和排序的时间。

索引的副作用

索引需要占用额外的磁盘空间。
   对于 MyISAM 引擎而言,索引文件和数据文件是分离的,索引文件用于保存数据记录的地址。
而 InnoDB 引擎的表数据文件本身就是索引文件。

在插入和修改数据时要花费更多的时间,因为索引也要随之变动。

创建索引的原则依据

  1. 索引随可以提升数据库查询的速度,但并不是任何情况下都适合创建索引。

  2. 因为索引本身会消耗系统资源,在有索引的情况下,数据库会先进行索引查询,

  3. 然后定位到具体的数据行,如果索引使用不当,反而会增加数据库的负担。

1.2.事务

主要用于处理操作量大,复杂度高的数据。

1.事务是一种机制、一个操作序列,包含了一组数据库操作命令,并且把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么都执行,要么都不执行。

2.事务是一个不可分割的工作逻辑单元,在数据库系统上执行并发操作时,事务是最小的控制单元。

3.事务是一个不可分割的工作逻辑单元,在数据库系统上执行并发操作时,事务是最小的控制单元。

4.事务适用于多用户同时操作的数据库系统的场景,如银行、保险公司及证券交易系统等等。

1.3.创建临时表

使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。

1.4.子查询

子查询也被称作内查询或者嵌套查询,是指在一个查询语句里面还嵌套着另一个查询语 句。子查询语句是先于主查询语句被执行的,其结果作为外层的条件返回给主查询进行下一 步的查询过滤。

子语句可以与主语句所查询的表相同,也可以是不相同表

二、MySQL故障

故障现象1

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/mysql.sock'(2)

问题分析
数据库未启动或者数据库端口被防火墙拦劫

解决方法
启动数据库或者防火墙开放数据库监听端口

故障现象2

ERROR 1045 (28000): Access denied for user 'root'@"ocalhost'(using password:NO)

问题分析
密码不正确或者没有权限访问

解决方法
修改my.cnf主配置文件,在[mysqld]下添加skip-grant-tablesupdate更新user表authentication string字段口
重新授权D

故障现象3
在使用远程连接数据库时偶尔会发生远程连接数据库很慢的问题

问题分析
DNS解析慢、客户端连接过多

解决方法
修改my.cnf主配置文件(增加skip-name-resolve参数)
数据库授权禁止使用主机名

故障现象4
Can't open file:'xxx forums.MYl'.(errno; 145)

问题分析
服务器非正常关机,数据库所在空间已满,或一些其它未知的原因对数据库表造成了损坏
因拷贝数据库导致文件的属组发生变化

解决方法
修复数据表(myisamchk、phpMyAdmin)

修改文件的属组

故障现象5
ERROR 1129 (HY000): Host 'xxx.xxx.xxx.xxx'is blocked because of many

connection errors: unblock with 'mysgladmin flush-hosts'

问题分析
超出最大连接错误数量限制

解决方法
清除缓存(flush-hosts关键字)
修改mysql配置文件(maxconnecterrors=1000)

故障现象6
Too many connections

问题分析
连接数超出MySQL的最大连接限制

解决方法
修改MySQL配置文件(maxconnections=10000)
临时修改参数
set GLOBAL max connections=10000;

故障现象7
Warning: World-writable config file /etc/my.cnf is ignoredERROR! MySQL is running but PlD file could not be found

问题分析
MySQL的配置文件/etc/my.cnf权限问题

解决方法
chmod 644 /etc/my.cn

故障现象8
InnoDB:Error: page 14178 log sequence number 29455369832InnoDB: is in the future! Current system log sequence number 29455369832

问题分析
innodb数据文件损坏

解决方法
修改my.cnf配置文件(innodb force recovery=4)
启动数据库后备份数据文件
利用备份文件恢复数据

故障现象9
从库的Slave lO Running为NO

The slave l/0 thread stops because master and slave have equal MySQL serverids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not alwavs make sense.please check the manual before using it).

问题分析:

主库和从库的server-id值一样

解决方法
修改从库的server-id的值,修改为和主库不一样

重新启动数据库并再次同步。

故障现象:10

从库的Slavel0 Running为NO主键冲突或者主库删除或更新数据,从库内找问题分析:不到记录,数据被修改导致
解决方法
方法一mysql> stop slave; mysql> start slave; mysqI> set GLOBAL SQL SLAVE SKIP COUNTER=1;

     
方法二set alobal read only=true:

故障现象11
Error initializing relay log position: l/O error reading the header from the binarylog

问题分析
从库的中继日志relay-bin损坏

解决方法
手工修复,重新找到同步的binlog和pos点,然后重新同步即可
mysqI> CHANGE MASTER TO MASTER LOG FE='mysql-bin.xxx',MASTER LOG POS=xxx;

相关推荐

  1. MySQL数据库优化MySQL故障

    2024-04-02 09:58:03       7 阅读
  2. mysql数据库故障排查与优化

    2024-04-02 09:58:03       5 阅读
  3. MYSQL数据库故障排除与优化

    2024-04-02 09:58:03       4 阅读
  4. MYSQL数据库故障排除与优化

    2024-04-02 09:58:03       4 阅读
  5. MySQL常见故障优化

    2024-04-02 09:58:03       4 阅读

最近更新

  1. leetcode705-Design HashSet

    2024-04-02 09:58:03       5 阅读
  2. Unity发布webgl之后打开streamingAssets中的html文件

    2024-04-02 09:58:03       5 阅读
  3. vue3、vue2中nextTick源码解析

    2024-04-02 09:58:03       6 阅读
  4. 高级IO——React服务器简单实现

    2024-04-02 09:58:03       5 阅读
  5. 将图片数据转换为张量(Go并发处理)

    2024-04-02 09:58:03       4 阅读
  6. go第三方库go.uber.org介绍

    2024-04-02 09:58:03       6 阅读
  7. 前后端AES对称加密 前端TS 后端Go

    2024-04-02 09:58:03       7 阅读

热门阅读

  1. AI技术创业有哪些机会?

    2024-04-02 09:58:03       6 阅读
  2. 计算机网络入门基础知识详解

    2024-04-02 09:58:03       5 阅读
  3. MySQL 数据库的优化

    2024-04-02 09:58:03       4 阅读
  4. Linux安装 nginx

    2024-04-02 09:58:03       4 阅读
  5. shell实现两台服务器的文件实时同步

    2024-04-02 09:58:03       6 阅读
  6. 什么是VPN服务器

    2024-04-02 09:58:03       5 阅读
  7. imu测试--UDP、PTP

    2024-04-02 09:58:03       6 阅读
  8. RUST 中什么情况下要使用 .unwrap ( )

    2024-04-02 09:58:03       5 阅读