java架构学习——20. MySQL优化1

本篇博文主要包含：

• MySQL如何优化
• 数据库设计三范式
• 分表分库
  -垂直拆分
  -水平分割案例
  -如何使用水平拆分数据库
  -使用取摸方式分表
  -分表后的缺点
• 定位慢查询
  -如何将慢查询定位到日志中
• show status命令

一、MySQL如何优化
表的设计合理化(符合3NF)
添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
SQL语句优化
分表技术(水平分割、垂直分割)
读写[写: update/delete/add]分离
存储过程 [模块化编程，可以提高速度]
对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
mysql服务器硬件升级
定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

二、数据库设计
第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)
第二范式：2NF是对记录的惟一性约束，表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键不能包含业务逻辑。
第三范式：3NF是对字段冗余性的约束，它要求字段没有冗余。 没有冗余的数据库设计可以做到。
但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

三、分表分库

1. 垂直拆分
   垂直拆分用于分布式场景。
   垂直拆分就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式），这种拆分在大型网站的演变过程中是很常见的。当一个网站还在很小的时候，只有小量的人来开发和维护，各模块和表都在一起，当网站不断丰富和壮大的时候，也会变成多个子系统来支撑，这时就有按模块和功能把表划分出来的需求。其实，相对于垂直切分更进一步的是服务化改造，说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务，通过服务间的调用来满足业务需求看，因此表拆出来后要通过服务的形式暴露出去，而不是直接调用不同模块的表，淘宝在架构不断演变过程，最重要的一环就是服务化改造，把用户、交易、店铺、宝贝这些核心的概念抽取成独立的服务，也非常有利于进行局部的优化和治理，保障核心模块的稳定性。

2. 水平拆分
   上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。例如像计费系统，通过按时间来划分表就比较合适，因为系统都是处理某一时间段的数据。而像SaaS应用，通过按用户维度来划分数据比较合适，因为用户与用户之间的隔离的，一般不存在处理多个用户数据的情况，简单的按user_id范围来水平切分

通俗理解：水平拆分行，行数据拆分到不同表中， 垂直拆分列，表数据拆分到不同数据库中。

3. 水平分割案例
   思路:在大型电商系统中，每天的会员人数不断的增加。达到一定瓶颈后如何优化查询。
   可能大家会想到索引，万一用户量达到上亿级别，如何进行优化呢？
   使用水平分割拆分数据库表。

4. 如何使用水平拆分数据库
   使用水平分割拆分表，具体根据业务需求，有的按照注册时间、取摸、账号规则、年份等。

5. 使用取摸方式分表
   先放入主表，然后根据主表生成的id取模，在存入对应的分表中。
   代码实现：
   service层：

public class TeacherServiceImpl implements TeacherService {
	
	@Autowired
	private TeacherInfoMapper teacherInfoMapper;

	/*
	 * 保存数据
	 */
	@Override
	public Object insertTeacher() {
		
		for(int i=0; i<10; i++) {
			QueryCondition t = new QueryCondition();
			t.setTeacherName("teacher"+i);
			t.setTableName("teacher_info");
			t.setTeacherNo("teacherNo"+i);
			//添加到主表中，并获取其id
			teacherInfoMapper.insertTeacher(t);
			//根据取模结果，将数据存放在对应的分表中
			t.setTableName("teacher_info"+(t.getId()%3+1));
			teacherInfoMapper.insertTeacher(t);
		}
		return "ok";
	}

	/*
	 * 从分表中查询数据
	 */
	@Override
	public Object selectTeacher(QueryCondition qc) {
		//根据取模结果，从对应的分表中获取数据
		qc.setTableName("teacher_info"+(qc.getId()%3+1));
		return teacherInfoMapper.selectByTeacherId(qc);
	}

}

sql语句：

 <insert id="insertTeacher" parameterType="wmq.fly.table.depots.QueryCondition" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
	 	insert ${tableName}(id,teacherName,teacherNo) values(#{id},#{teacherName},#{teacherNo})
	 </insert>

	<select id="selectByTeacherId" parameterType="wmq.fly.table.depots.QueryCondition" resultMap="BaseResultMap">
		select <include refid="Base_Column_List"/> from ${tableName} where id = #{id}
	</select>	
	

表结构：

6. 分表后的缺点
   a. 分页查询
   b. 查询受限

四、定位慢查询
在一个大项目中，可以使用定位慢查询的方法，迅速的定位执行速度慢的语句。

1. 什么是慢查询
   MySQL默认10秒内没有响应SQL结果,则为慢查询
   可以去修改MySQL慢查询默认时间。

2. 如何修改慢查询
   –查询慢查询时间
   show variables like ‘long_query_time’;
   –修改慢查询时间
   set long_query_time=1;
   但是重启mysql之后，long_query_time依然是my.ini中的值。

3. 如何将慢查询定位到日志中
   在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以，先找打mysql安装文件夹，使用命令行模式到bin目录下，执行命令：
   bin\mysqld.exe --safe-mode --slow-query-log [mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复）
   bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]
   先关闭mysql,再启动, 如果启用了慢查询日志，默认把这个文件放在 my.ini 文件中记录的位置
   #Path to the database root
   datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

五、show status命令
SHOW [统计范围] STATUS [LIKE ‘状态项名称’]
–统计范围关键字分为GLOBAL和SESSION<默认>(或LOCAL)两种。

–查看MySQL本次启动后的运行时间(单位：秒)
show status like ‘uptime’;