MySQL调优思路

MySQL是一种开源、关系型数据库。调优MySQL是提高系统性能的一个关键部分。本文将介绍一些常见的MySQL调优思路和技巧。

确定瓶颈

要调优MySQL，首先需要确定瓶颈所在。常见的瓶颈包括CPU、内存、磁盘和网络。了解这些瓶颈所在，可以有针对性地优化MySQL。

选择合适的存储引擎

MySQL支持多种不同的存储引擎。不同的存储引擎有不同的优势和劣势。InnoDB是MySQL的默认存储引擎，它适用于事务处理。MyISAM是另一种常见的存储引擎，适用于读取频繁、写入不频繁的应用。选择合适的存储引擎可以提高MySQL的性能。

优化查询语句

优化查询语句可以提高MySQL的性能。在设计查询语句时，需要考虑以下几个方面：

• 避免使用SELECT *
• 遵循查询的原则
• 避免使用子查询
• 避免使用表连接（尤其是多表连接）
• 使用索引进行查询

设置合适的缓冲区大小

适当地设置缓冲区大小可以提高MySQL的性能。MySQL中有一些缓冲区，如查询缓存、表缓存、连接缓存和排序缓存等。适当地设置这些缓冲区的大小，可以避免频繁访问磁盘，从而提高MySQL的性能。

使用分区表

如果有大量的数据需要操作，使用分区表可以提高MySQL的性能。分区表将数据分成多个部分，每个部分可以单独进行操作，从而提高操作效率。

使用主从复制

使用主从复制可以提高MySQL的性能和可用性。主从复制是将主数据库中的数据复制到从数据库中，从数据库可以用于读取操作，从而减轻主数据库的负载。

定期维护数据库

定期进行数据库的维护可以提高MySQL的性能。维护工作包括备份数据库、优化数据库结构、删除不需要的数据和优化数据库配置等。

以上是MySQL调优的一些常见思路和技巧。通过合理调优MySQL，可以提高性能和可用性，从而更好地满足业务需求。

MySQL分库分表实践

概述

MySQL是目前应用最广泛的关系型数据库之一，随着数据量的增长和系统访问的增加，单一的MySQL实例可能无法承受大量的访问和数据存储压力，分库分表成为MySQL扩容的一个最佳实践方案，本文将介绍如何实现MySQL分库分表。

分库分表架构

分库分表架构本质上就是将一个大的数据库拆分为多个数据库实例，然后将每个实例再拆分为多个数据表，最后将访问各个表的业务逻辑分别分配到多个数据库实例上。分库分表的目的在于提高MySQL的性能和扩展能力，降低单机MySQL对系统的压力。

分库分表的最终目的是能够处理海量数据的查询请求，因此我们必须保证分库分表的策略是正确和有效的。一个好的分库分表策略不仅能够大幅度提高MySQL的性能，还能在一定程度上提高MySQL的可用性和可靠性。

常见的MySQL分库分表策略包括：

• 垂直拆分：将数据库中的不同表按照逻辑功能拆分为不同的数据库实例，这种方法一般用于不同表之间没有关联关系或者关联关系不太紧密的情况。
• 水平拆分：将数据库中的同一张表按照一定的规则拆分为多个表，每个表存储一部分数据，这种方法一般用于数据存储量非常大的情况。
• 分区：将同一张表按照一定的规则拆分为若干个子表，每个子表存储一部分数据，这种方法一般用于数据存储量非常大并且查询请求比较频繁的情况。

实践步骤

步骤一：确定业务需求和分库分表策略

在Java代码中使用分库分表的方式有很多，比如使用Sharding-JDBC、MyCAT等中间件，分别在代码层面和数据层面实现分库分表的功能。在使用这些中间件之前，我们需要先确定业务需求和分库分表的策略，包括数据库的切分规则、表的切分规则、数据量以及各种数据操作的约束等。

步骤二：设计分库分表方案

在确定业务需求和分库分表的策略之后，我们需要设计分库分表的方案，包括如何切分数据库、如何切分表、如何在各个数据库实例之间实现数据的同步等。

步骤三：实现数据源的配置和使用

在设计好分库分表方案之后，我们需要在代码中实现数据源的配置和使用。在Java中常使用spring-jdbc和spring-mybatis等框架对分库分表进行实现，通过在配置文件中配置数据源和MyBatis的Mapper接口等信息，我们可以轻松地使用分表查询和分表插入等操作。

步骤四：测试和优化

在实现分库分表系统之后，我们需要对系统进行测试和优化。针对不同业务场景下的数据操作，我们需要评估查询效率、数据写入效率、系统稳定性以及数据一致性等方面的性能。

总结

MySQL分库分表是提高系统性能和扩展能力的一个最佳方案，设计好分库分表的方案并正确地应用在实际场景中，可以极大地提高系统的性能和扩展能力。希望本文能够为大家使用MySQL分库分表提供一些实践经验和思路，让大家更好地应用MySQL分库分表技术。

MySQL 使用规范

索引规范

1. 在表中使用索引来加速数据查询和服务，但不要过度使用，因为索引需要占用磁盘空间和计算资源。
2. 选择唯一索引或组合索引来提高查询效率和减少冗余数据。在执行操作时注意索引的效率和复杂性，并避免竞争条件。
3. 对于数据类型为字符串的列，应该在其前面定义一个前缀索引，避免不必要的空间占用和计算开销。

SQL 规范

1. 对于任何 SQL 查询操作，都应该使用合适的语法和正确的顺序来避免错误和提高执行效率。
2. 对于 SELECT 语句，在使用通配符时应该尽量避免使用 SELECT *，而是在需要的列上明确指明。
3. 在使用计数函数 COUNT() 时，应该使用 COUNT(*) 而不是 COUNT(column) 来避免空值的统计问题。
4. 对于 UPDATE 和 DELETE 操作，在对数据表做出重大更改之前应该先备份原有数据，以防止被错误操作所破坏。

命名规范

1. 对于数据库和表，应该使用下划线作为单词之间的分隔符，如 “my_database” 和 “my_table”。
2. 对于字段名，应该使用下划线或者驼峰命名方式，如 “my_field_name” 或 “myFieldName”。
3. 对于存储过程或视图，应该使用有意义的名称来描述它们的功能和用途，以方便管理和维护。
4. 对于索引，应该使用有意义的名称来反映它们的内容和作用，避免混淆和重复。

以上是 MySQL 使用规范的一些要点，遵循这些规范可以提高数据库的可读性、可维护性和性能。

驱动

Mysql的驱动jar, 默认有两个功能, 主从分离和HA.
如果你只是需要一个主从分离, failover功能, 不要sharding, 一个驱动就够,也就不需要引入中间层.

有个Replication协议, 在5.1.x版本之后,增加了这些功能, 以用来支持”multi-host”集群拓扑的访问范式. 这个功能是在驱动层实现的.

jdbc连接如下

1

jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8

通过协议改造后的jdbc连接如下

1

jdbc:mysql:replication://127.0.0.1:3306,127.0.0.1:3307,127.0.0.1:3308/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=false&loadBalanceStrategy=random

协议后的第一个连接, 表示主库Master
后面一堆连接,表示从库Slave, 当然可以有多个
当你把Master的连接也放在后面的一堆里面, 那么它也拥有”读库”的属性了
后面有一堆参数, 来控制这所有连接, 到底要如何相处

代码层

对于Spring来说, 就可以使用Transaction注解来控制这个属性, 一个事务不可能跨两个连接, 所以是读还是写, 由最高层决定.

1
2
3
4
5

public interface UserManager{
	public UserDO get(int id);
	public void insert(UserDo user);
	public void update(int id);
}

1
2
3
4
5
6
7
8
9
10

@Component
public class UserManagerImpl implements UserManager{
	@Autowired
    private UserDao userDao;
    //...
    @Transactional(readOnly = false, propagation=Propagation.REQUIRED)
    public void insert(UserDO user){
		this.userDao.insert(user);
    }
}

参数

• readFromMasterWhenNoSlaves当所有的salve死掉后, 此参数用来控制主库师傅参与读
• loadBanlanceStrategy策略用来指定从库的轮询规则. 有轮询, 也有权重, 也可用指定具体策略实现. 当你维护或迁移某个实例时, 先置空流量, 这回非常有用. 或许, 你会给某个DB一个预热的可能.
• allowMasterDownConnections如果主机宕机,当连接池获取新的连接时会失败.
• retriesAllDown 当所有的hosts都无法连接时重试的最大次数（依次循环重试）,默认为120.
• autoReconnect 实例既然有下线、就有上线。上线以后要能够继续服务，此参数用来控制断线情况下自动重连而不抛出异常.