MySQL数据库优化

常用的MYSQL优化方案

• 第一点就是创建数据库时：

  • 字段的数据范围尽量小，使得表结构变小

  • 避免null值，尽量使用默认值。null值很难查询优化且占用额外的索引空间

  • 尽量使用枚举或整数代替字符串类型，创建 字段名 enum（‘str1’，'str2'），插入 insert values(1),('str1'),(2)，如果插入vaules(0)就是插入空字符串 “”

  • 不要一张表放太多字段

  • mysql>create table enum_test(i enum('dog','cat'));
    mysql>insert into enum_test values('dog'),(2),('cat');
    ​
    mysql> select * from enum_test;
    +------+
    | i    |
    +------+
    | dog  |
    | cat  |
    | cat  |
    +------+

• 第二点就是创建索引时：

  • 不是越多索引越好，太多的索引会浪费存储空间，最重要的是更新操作的速度会变慢，每次新增或更新都要维护索引

  • 字符字段不要当主键

  • 不要使用外键，唯一键，由程序保证

  • 尽量使用复合索引

• 第三点就是使用索引时，也就是sql语句尽量走索引

  • 尽量不使用or，is null，is not null ，<>, !=

  • like 模糊匹配不要 以%开头

  • 字符串类型的一定要记得加单引号

  • 避免使用select *，尽量覆盖索引，这样就不用了回表

  • 尽量使用join连接表，不使用嵌套子查询

  • 子查询的话，小表驱动大表

  • 不做列计算表达式age/2=?，不使用函数sin()等

  • join不要连接过多的表

  • 连续数值不要使用in，使用between

  • 确保 ON 或 USING 子句中的列上有索引，在创建索引时就要考虑到关联的顺序。

  • 确保 ON 或 USING 子句中的列上有索引，在创建索引时就要考虑到关联的顺序。

  • MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要服务器消除重复的行，否则一定要使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。

MySQL的主从复制

• MySQL主从复制的基础是主服务器对数据库修改记录的二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

数据库的ACID原理

• A（atomic）原子性：一个事务要么都发生，要么都不发生；不能一个-100操作，然后断电，另一个+100操作中断了

• C（constant）一致性：数据的完整性要保持一致，两个人账户操作前后的总额都是2000

• I（island）隔离性：保证数据库操作时，其他事务操作不会影响到我，保证并发修改操作

• D（during）持久性：数据库修改后的数据，一旦事务提交，要持久化，不能因为断电就丢失数据

数据库的三大范式

• 第一，原子性，要保证数据库字段不能在分出其他字段

• 第二，完全依赖，表必须由主键，而且其他字段必须完全依赖与主键，不能只依赖一部分

• 第三，消除传递依赖，表中的其他字段必须直接依赖主键，不能传递依赖，如非A依赖B，B依赖主键

• 数据库的三大范式是教学级别的，有时会和生产冲突，为了生产方便有时会反范式。