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的存储引擎
- 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