一 mysql 逻辑架构图
客户端和连接服务
核心服务功能
存储引擎层
数据存储层
二 msql单实例常见故障
1, 故障一
故障现象
问题分析
数据库未启动或者数据库端口被防火墙拦截
解决方法
启动数据库或者防火墙开放数据库监听端口
2, 故障二
故障现象
问题分析
密码不正确或者没有权限访问
解决方法
修改my.cnf主配置文件，在[mysqld]下添加skip-grant-tables（类似linux的单用户模式，改完密码记得把这个再注释掉）
update更新user表authentication string字段
重新授权
3, 故障三
故障现象
在使用远程连接数据库时偶尔会发生远程连接数据库很慢的问题
问题分析
DNS解析慢、客户端连接过多
解决方法
修改my.cnf主配置文件(增加skip-name-resolve参数)
数据库授权禁止使用主机名
4, 故障四
故障现象
问题分析
服务器非正常关机，数据库所在空间已满，或一些其它未知的原因，对数据库表造成了损坏
因拷贝数据库导致文件的属组发生变化
解决方法
修复数据表(myisamchk、phpMyAdmin) 不一定能成功
修改文件的属组 注意！ 拷贝数据时 保留原数据的属主数组
5, 故障五
故障现象
问题分析
超出最大连接错误数量限制
解决方法
清除缓存(flush-hosts关键字)
修改mysql配置文件(maxconnecterrors=1000)
6, 故障六
故障现象
问题分析
连接数超出MySQL的最大连接限制
解决方法
修改MySQL配置文件(maxconnections=10000)
临时修改参数
7, 故障七
故障现象
问题分析
MySQL的配置文件/etc/my.cnf权限问题
解决方法
8, 故障八
故障现象
问题分析
innodb数据文件损坏
解决方法
修改my.cnf配置文件(innodb_force_recovery=4)
启动数据库后备份数据文件
利用备份文件恢复数据
三 mysql 主从环境常见故障
1, 故障一
故障现象
从库的Slave_l0_Running为NO
问题分析
主库和从库的server-id值一样
解决方法
修改从库的server-id的值，修改为和主库不一样
重新启动数据库并再次同步
2, 故障二
故障现象
从库的Slave l0 Running为NO
问题分析
主键冲突或者主库删除或更新数据，从库内找不到记录，数据被修改导致
解决方法
3, 故障三
故障现象
问题分析
从库的中继日志relay-bin损坏
解决方法
手工修复，重新找到同步的binlog和pos点，然后重新同步即可
四 mysq 常见优化
1，硬件优化
CPU:推荐使用S.M.P.架构的多路对称CPU
内存:4GB以上的物理内存
磁盘:RAID-0+1磁盘阵列或固态硬盘
2，mysql 配置文件优化
调整配置项
3，sql 优化
尽量使用索引进行查询
优化分页
GROUP BY优化
4，mysql 架构优化
架构选择:主从、主主、一主多从、多主多从
五 mysql 数据库性能优化相关命令
1，查看每个客户端IP过来的连接消耗了多少资源
mysql> select * from sys.x$host_summary;
2，查看某个数据文件上发生了多少IO请求。
mysql> select * from sys.x$io_global_by_file_by_bytes;
3， 查看每个用户消耗了多少资源
mysql> select * from sys.x$user_summary;
4，查看总共分配了多少内存
mysql> select * from sys.x$memory_global_total;
5， 数据库连接来自哪里，以及这些连接对数据库的请求情况是怎样的？
查看当前连接情况。
mysql> select host, current_connections, statements from sys.x$host_summary;
6，查看当前正在执行的SQL和执行show full processlist的效果相当
mysql> select conn_id, user, current_statement, last_statement from sys.x$session;
7，数据库中哪些SQL被频繁执行？ 执行下面命令查询TOP10 SQL。
mysql> select db,exec_count,query from sys.x$statement_analysis order by exec_count desc limit 10;
8，哪个文件产生了最多的IO，读多，还是写的多？
mysql> select * from sys.x$io_global_by_file_by_bytes limit 10;
9，哪个表上的IO请求最多？
mysql> select * from sys.x$io_global_by_file_by_bytes where file like "%ibd" order by total desc limit 10;
您在尝试使用MySQL查询语句从sys.x$io_global_by_file_by_bytes系统视图中选择数据时遇到问题。该视图提供了关于文件级别的I/O操作统计信息，按照您的查询，您希望获取文件名中包含“ibd”（InnoDB表空间文件扩展名）的记录，并按total字段降序排列，最后显示前10条结果。
10 ，哪个表被访问的最多？
先访问statement_analysis，根据热门SQL排序找到相应的数据表。
哪些语句延迟比较严重？
查看statement_analysis中avg_latency的最高的SQL。
mysql> select * from sys.x$statement_analysis order by avg_latency desc limit 10;
11， 哪些SQL执行了全表扫描，如果没有使用索引，则考虑为大型表添加索引
mysql> select * from sys.x$statements_with_full_table_scans;
12， 列出所有做过排序的规范化语句
mysql> select * from sys.x$statements_with_sorting
13，哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表
查看statement_analysis中哪个SQL的tmp_tables 、tmp_disk_tables值大于0即可。
mysql> select db, query, tmp_tables, tmp_disk_tables from sys.x$statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20;
14， 列出所有使用临时表的语句——访问最高的磁盘临时表，然后访问内存临时表
mysql>select * from sys.statements_with_temp_tables
15， 哪个表占用了最多的buffer pool？
mysql> select * from sys.x$innodb_buffer_stats_by_table order by allocated desc limit 10;
16，每个库（database）占用多少buffer pool？
mysql> select * from sys.x$innodb_buffer_stats_by_schema order by allocated desc limit 10;
六 总结
MySQL 常见的优化方法涵盖了许多方面，旨在提高查询性能、减少资源消耗和提升整体数据库系统的效率。以下是一些常见的MySQL优化策略：
- 选择合适的字段属性：
- 使用最适合数据特性和业务需求的数据类型，如使用INT而非VARCHAR存储整数。
- 控制字段长度，如在不影响数据完整性的前提下，减小VARCHAR字段的最大长度。
- 尽可能避免使用NULL，因为NULL值会增加存储开销且在查询时可能需要特殊处理。
- 创建和使用索引：
- 在频繁用于筛选、排序、关联的列上创建索引，尤其是主键和外键。
- 选择合适的索引类型，如B-tree、Hash、全文索引等，依据查询模式和数据分布来决定。
- 注意索引覆盖（covering index），即索引包含查询所需的所有列，以避免回表操作。
- 定期分析和维护索引，删除冗余或很少使用的索引，监控并优化索引使用情况。
- 查询优化：
- 使用
EXPLAIN分析查询计划，了解查询如何使用索引、进行全表扫描等情况，以便调整查询或索引设计。 - 避免在
WHERE子句中使用否定条件和复杂的表达式，这些可能妨碍索引的使用。 - 减少
IN子句中的值数量，大量值可能导致查询性能下降。 - 明确指定
SELECT语句中的字段名，避免使用SELECT *，特别是当表结构庞大时。 - 当只需要单个结果时，使用
LIMIT 1尽早终止查询。 - 避免在未使用索引的列上进行排序，或者尽可能减少排序操作。
- 连接操作优化：
- 使用有效率的连接方式（如INNER JOIN、LEFT JOIN等），并确保关联字段上都有适当的索引。
- 尽可能通过调整查询结构和添加适当索引来避免全表扫描和嵌套循环连接。
- 在适当的情况下，使用JOIN替代子查询，因为JOIN通常能更有效地利用索引。
- 使用UNION替代临时表：
- 对于需要合并多个查询结果的情况，使用UNION而非手动创建临时表，以减少中间结果的存储和管理开销。
- 事务管理：
- 保持事务短小，避免长时间持有锁，减少死锁概率。
- 合理设置事务隔离级别，平衡数据一致性与并发性能。
- 服务器参数调优：
- 根据服务器硬件、数据库负载和应用特点调整MySQL配置参数，如
innodb_buffer_pool_size、query_cache_size、max_connections等。 - 监控系统性能指标（如CPU、内存、磁盘I/O等），并据此调整参数。
- 数据分片与分区：
- 对于超大型表，考虑使用分表（水平拆分）或分区（垂直拆分）策略，分散数据和查询负载。
- 选择合适的分片/分区键，确保数据分布均匀，查询能够高效路由。
- 缓存策略：
- 利用MySQL内置的查询缓存（如果适用），但需注意其在高并发场景下的局限性。
- 对于读多写少的场景，考虑使用外部缓存系统（如Memcached、Redis）缓存热点数据。
- 定期维护与清理：
- 定期分析和优化表，如运行
ANALYZE TABLE和OPTIMIZE TABLE命令。 - 清理不需要的数据，如归档旧数据、删除冗余记录，以减少数据规模和查询范围。
综上所述，MySQL优化是一个综合性的过程，涉及到数据库设计、查询编写、服务器配置、硬件资源等多个层面。实施优化时应结合实际业务场景，持续监控数据库性能，并根据性能分析结果进行有针对性的调整。
