Mysql优化

数据库优化可以从单体优化和分布式优化来做。

单体优化

表结构

• 从字段上来说，可以使用char，不适用varchar，即用小不用大。
• 再创建表的时候，可以适当违反3NF创建冗余字段，比如员工和部门，前端使用员工人数比较多的话，可以在部门表设计一个字段表示员工的数量，省去join查询的时间，但是在员工表有人员变动的时候，可以使用触发器，更新部门表的人数。
• 创建合适的索引，减少查询时间。
• 使用合适的引擎。
• 优化sql。
• 垂直分表和水平分表。

关于索引

索引的底层原理

索引是Btree，是类似于二叉树，创建索引的时候，会将字段进行编号，存入到二叉树，查找的时候，以二叉树进行查找，就可以定位到需要查找的数据。

索引的分类

单列索引

单列索引又可以分为

• 普通索引
  允许重复的值出现，可以在任何字段上面添加
• 唯一索引
  除了不能有重复的记录外，其它和普通索引一样，可以在值是唯一的字段添加（用户名、手机号码、身份证、email，QQ），可以为null,并且可以有多个null
• 主键索引
  是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会給改列创建索引。这就是主键索引.唯一且没有null值（也被称为非聚集索引）
• 全文索引
  用来对表中的文本域(char，varchar，text)进行索引， 全文索引针对MyISAM有用

单列索引失效情况（不会使用索引）

• sql语句使用模糊查询，以%开头.
• 使用or，in，not in,is null,!=等

复合索引

复合索引就是使用多个字段创建的索引
复合索引失效的情况

• sql的where条件后面只是用了第二个字段。
• 以及单列索引失效的情况。.

索引的优缺点

优点：
索引提高了查询速度，select where 。
缺点：
因为索引的存在，降低了修改、删除，插入的效率，因为这三个操作会重构索引。而且因为索引的存在，要占用磁盘空间。

关于sql优化

DDL优化（定义）

• 通过禁用索引，提供数据导入性能，最后再恢复索引，即变多次索引维护为一次索引维护。
• 关闭唯一校验，提高数据导入性能，再后再开启校验，变多次校验为一次。
• 变多次事务提交为一次。
  DML（manage）
• 变多次插入为一次插入，使用批量插入。
  DQL优化
• order by 使用索引。
• limit优化 在分页是使用一个有索引的字段排序
  select film_id,description from film order by title limit 50,5;

存储引擎

• MYISAM：不支持事务，不支持外键，支持全文索引，支持表锁，速度快。
  适用于以select和insert为主。

• InooDB：支持事务，支持外键，比支持全文索引，支持表锁，但是访问速度慢。
  适用于有事务要求有update的需求的。

关于分表（数据量达到500w，可以考虑分表）

水平分表：是对行进行分为几个表。可以按照业务来分，比如说按月份来分。
关于水平分表主键id生成以及定位的问题：
可以采用雪花算法生成一个全局唯一的主键，然后java代码调用hashcode方法获取hash值，并对id值进行取余的方法确定数据存放的位置，类似于hashmap存值取值得原理。
垂直分表：垂直分表是对列进行拆分。比如用户表，像用户的地址是很少查询和变动，可以将用户的地址单独分一张表，使用同一个主键

多机优化

当单体优化不满足需求时，可以采用：

• 读写分离，主机只负责写，从负责读，通过二进制日志文件来同步。
• 按业务进行集群

使用缓存

使用redis做缓存，减少数据库访问次数。

排查慢sql

以慢查询方式启动数据库，设置值慢查询时间，在日志中查看慢sql，再使用explain 分析sql语句慢的原因。