Mysql的优化

王守钰 2020-03-12 23:03:37

Mysql 数据库的优化

  • 表的设计合理
  • 添加适当的索引(唯一索引(主键索引)、普通索引、组合索引、全文索引)。
    • 主键索引:不允许为null,主键索引只能有一个。
    • 唯一索引:允许数据为null,可以有多个唯一索引。
    • 组合索引:最左匹配原则。
  • 分库分表
  • 读写分离
  • 选择适当的存储引擎

SQL语句的优化

  • 避免全表扫描,考虑在where和order by 涉及的列上加索引。
  • 避免在子语句中使用!=或者<>,否则将放弃使用索引全表扫描。
  • 避免在where语句中使用or,否则将放弃使用索引全表扫描,尽量使用union all。
  • 尽量避免使用in和not in,如果连续的数字尽量使用between and
  • 避免使用%%,否则将放弃使用索引,尽量确定前查询信息百分号在后。
  • 避免在where子语句中使用表达式进行操作,否则将放弃索引使用全表扫描。
  • 不要在 where 子句中的=左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引
  • 使用组合索引时候,要保证最左匹配原则,否则将放弃索引
  • 索引不要建立在大量重复的列上。
  • 大量创建索引可以提高select效率,但是会降低delete、update的效率。
  • 只含有数值的列中尽量不要设计为字符型,否则会降低连接性能、增加存储开销。
  • 尽量使用varchar来代理char,可变长度可以节省一部分空间。
  • 查询的时候尽量避免使用select *。
  • 避免频繁的创建和删除临时表。
  • 大量数据导入临时表时候尽量使用select into 代替create table/insert into 避免产生大量的log提高效率。
  • 删除临时表尽量使用truncate table然后在drop掉

Mysql的高可用

  • 主从复制+读写分离
  • Mysql cluster

Mysql的存储引擎

  • InnoDB,存在行锁和表锁,行锁是在命中索引的情况下才生效;支持事务;支持四种隔离级别(读未提交、读已提交、可重复读、串行化)默认是可重复读;
  • MyIsam,只支持表锁,不支持事务。MyIsam有单独的索引文件,所以在数据库读取方面性能很高。
  • InnoDB和MyIsam都是使用B+Tree来存储数据。

MySql的隔离级别

  • Read Uncommited(读未提交)可能会产生脏读、幻读、不可重复读。
  • Read Commited(读已提交)可能会产生不可重复的和幻读。
  • Repeatable Read (可重复读)可能会产生幻读。
  • Serializable(序列化)

脏读、幻读、不可重复读

  • 脏读:
    • 当一个事务正在修改数据,还没提交到数据库中,这时另外一个事务也访问了这个数据,使用了这个数据。
  • 幻读:
    • 一个事务对数据进行了修改(全表),另一个事务进行插入数据。第一个事务就可能会发现有一条没有改变。
  • 不可重复读:
    • 在一个事务内,第一个读取一条数据,没有提交事务;另外一个事务对这条数据进行了修改,这时第一个事务又进行了一次读取,发现两条数据不一致。

Mysql的ACID

  • Atomictiy(原子性)语句要么全执行,要么全不执行,是事务最核心的特性,事务本身就是以原子性来定义的;实现主要基于undo log
  • Consistency(一致性)事务追求的最终目标,一致性的实现既需要数据库层面的保障,也需要应用层面的保障
  • Isolation(隔离性)保证事务执行尽可能不受其他事务影响;InnoDB默认的隔离级别是RR,RR的实现主要基于锁机制、数据的隐藏列、undo、log和类next-key lock机制
  • Durability(持久性)保证事务提交后不会因为宕机等原因导致数据丢失;实现主要基于redo log