面试—MySQL(数据库优化)

面试—MySQL(数据库优化)

由该图可知数据结构和SQL语句和索引去优化数据库效果是最好的，所以可以从以下几个方面去优化数据库

数据库设计

• 根据数据库三范式来进行表结构的设计
• 尽量使用 TINYINT、SMALLINT、MEDIUM_INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED
• VARCHAR 的长度只分配真正需要的空间
• 尽量使用整数代替字符串类型
• 单表不要有太多字段，建议在 20 以内
• 避免使用 NULL 字段，很难查询优化且占用额外索引空间
• 不建议使用 select * from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。尽量避免向客户 端返回大数据量，若数据量过大，应该考虑相应需求是否合理
• 表与表之间通过一个冗余字段来关联，要比直接使用 JOIN 有更好的性能
• select count (*) from table；这样不带任何条件的 count 会引起全表扫描

索引优化

1、在需要建立索引的列上建立索引如：

• 在经常需要搜索的列上
• 在作为主键的列上
• 在经常用在连接的列上，这些列主要是一些外键
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
• 在经常需要**排序的列（group by 或者 order by）**上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
• 在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度

2、在不应该建立索引的列上避免建立索引如：

• 在查询中很少使用或者参考的列
• 很少数据值的列
• 定义为 text, image 和 bit 这种数据量很大的数据类型的列
• 当修改性能远远大于检索性能时，不应该创建索引。修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

3、避免索引失效

主从复制

通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力

实现流程

1. 每个事务更新数据完成之前，master 在二进制日志记录这些改变。写入二进制日志完成后，master 通知存储引擎提交事务。
2. Slave 将 master 的 binary log 复制到其中继日志。首先 slave 开始一个工作线程（I/O），I/O 线程在 master 上打开一个普通的连接，然后开始 binlog dump process。binlog dump process 从 master 的二进制日志中读取事件，如果已经跟上 master，它会睡眠并等待 master 产生新的事件，I/O 线程将这些事件写入中继日志。
3. Sql slave thread（sql 从线程）处理该过程的最后一步，sql 线程从中继日志读取事件，并重放其中的事件而更新 slave 数据，使其与 master 中的数据一致，只要该线程与 I/O 线程保持一致，中继日志通常会位于 os 缓存中，所以中继日志的开销很小。

分库分表

分表方式：

• 垂直拆分：垂直拆分就是要把表按模块划分到不同的数据库中，数据库按模块和功能把表划分出来，趋向于服务化
• 水平拆分：水平拆分就是要把一个表按照一定的规则把数据划分到不同的表或数据库中。比如按时间，账号规则，年份，取模算法等

使用场景：

• 根据经验，mysql 表数据一般达到百万级别，查询效率就会很低。
• 一张表的某些字段值比较大并且很少使用。可以将这些字段隔离成单独一张表，通过外键关联，例如考试成绩，我们通常关注分数，不关注考试详情。

水平拆分策略：

• 按时间分表：当数据有很强的实效性，例如微博的数据，可以按月分割。
• 按区间分表：例如用户表 1 到一百万用一张表，一百万到两百万用一张表。
• hash 分表：通过一个原始目标 id 或者是名称按照一定的 hash 算法计算出数据存储的表名。

缺点：

• 分页查询困难
• 查询十分受限

SQL调优

常用方式：开启慢查询日志然后使用explain工具进行逐步调优

慢查询日志：

MySQL 默认设置 10s 没有返回结果的，属于慢查询，并存到日志中 (在 my.ini 可以指定慢查询日志目录)

开启慢查询日志：

• slow_query_log 慢查询开启状态。
• slow_query_log_file 慢查询日志存放的位置（这个目录需要 MySQL 的运行帐号的可写权限，一般设置为 MySQL 的数据存放目录）。
• long_query_time 查询超过多少秒才记录。

可以直接在配置文件中开启，也可以在mysql命令行中通过set进行设置

分析慢查询：

在慢查询的sql语句之前加上explain命令，通过查询出的分析表进行分析查看是通过全表扫描还是索引扫描

• type：显示是搜索方式（全表扫描或者索引扫描）
• key：使用的索引字段，未使用则是null