导图社区 MySQL
MySQL
事务
特性
原子性
一致性
隔离性
永久性
分类
扁平事务
带有扁平点的扁平事务
链事务
嵌套事务
分布式事务
隔离级别
read uncommitted 读未提交
read committed 读已提交
可以解决脏读:指一个线程中的事务读取到了另外一个线程中未提交的数据
repeatable read 可重复读( MySQL 默认隔离级别)
可以解决不可重复读:指一个线程中的事务读取到了另外一个线程中提交的update的数据
serializable 串行化
可解决幻读:指一个线程中的事务读取到了另外一个线程中提交的insert的数据
锁
锁机制
表级锁
开销小,加锁快;不会出现死锁;颗粒度大,发生冲突概率高,并发度低
行级锁
开销大,加锁慢;会出现死锁;颗粒度小,发生冲突概率低,并发度高
页面锁
开销和加锁时间介于表级锁和行级锁之间;会出现死锁;锁定颗粒度介于两者之间,并发度一般
引擎与锁
MyISAM 和 MEMORY 支持表锁 BDB 支持页锁 InnoDB 既支持行锁,也支持表锁,默认行锁
索引
类型
1 Normal :普通索引
是最基本的索引,它没有任何限制
2 UNIQUE: 唯一索引
与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
3 组合索引:
指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用
4 Fulltext: 全文检索, MySQL 的全文检索只能用 MyISAM 引擎,并且性能低,不建议使用
方法
B+树
特点
B+Tree只在最末端叶子节点存数据,叶子节点是以链表的形势互相指向的
为什么用B树作为索引
B+Tree的高度是可控的,mysql通常是3到5层
Hash: 把索引的值做 hash 运算,并存放到 has 表中,使用较少,一般是 memory 引擎使用 缺点:只适用于精确的值比较,无法使用范围查找,无法使用索引排序,如果大量 hash 值相同,性能较低
创建
1 较频繁的作为查询条件的字段应该创建索引
2 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
3 更新频繁的字段不适合作为索引,原因:索引有维护成本
失效
1 有 or 中不是索引的字段
2 复合索引未用左列字段
3 like 以 % 开头
4 需要类型转换
5 where 中索引列有运算
6 where 中索引列使用了函数
7 如果 MySQL 觉得全表扫描更快时(数据少)
引擎与索引
MyISAM是非集聚引擎,支持全文索引;不支持事务;它是表级锁;会保存表的具体行数.
innoDB是集聚引擎,5.6以后才有全文索引;支持事务;它是行级锁;不会保存表的具体行数.
引擎
InnoDB
MyISAM
Memory
如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用Memory。Memory表至少比MyISAM表要快一个数量级,因为所有的数据都保存在内存中,不需要进行磁盘I/O。
表结构对性能的影响
冗余数据处理(可以提高系统的整体查询性能三范式)
1 每一列只能有一个值
2 每一行可以被唯一区分
3 不包含其他表的已包含的非关键信息
大表拆小表
1 一般不设计属性过多的表
2 一般不会超过 500 到 1000 万数据的表
3 有大数据的列单独拆为小表
根据需求展示更加合理的表结构
常用属性分离为小表
MySQL优化
索引优化
1.只要列中含有NULL值,就最好不要在此例设置索引,复合索引如果有NULL值,此列在使用时也不会使用索引
2.尽量使用短索引,如果可以,应该制定一个前缀长度
3.对于经常在where子句使用的列,最好设置索引,这样会加快查找速度
4.对于有多个列where或者order by子句的,应该建立复合索引
5.对于like语句,以%或者‘-’开头的不会使用索引,以%结尾会使用索引
6.尽量不要在列上进行运算(函数操作和表达式操作)
7.尽量不要使用not in和<>操作
sql语句优化
如何捕获低效sql
slow_query_log:这个参数设置为ON,可以捕获执行时间超过一定数值的SQL语句。 (set @@global.slow_query_log=1)
ong_query_time:当SQL语句执行时间超过此数值时,就会被记录到日志中,建议设置为1或者更短 (set @@global.slow_query_time=3)
log_queries_not_using_indexes:这个参数设置为ON,可以捕获到所有未使用索引的SQL语句,尽管这个SQL语句有可能执行得挺快
优化原则
1 查询时,能不要*就不用*,尽量写全字段名
2 大部分情况连接效率远大于子查询
3 多使用explain和profile分析查询语句
4 查看慢查询日志,找出执行时间长的sql语句优化
5 多表连接时,尽量小表驱动大表,即小表 join 大表
6 在千万级分页时使用limit
7 对于经常使用的查询,可以开启缓存
表优化
表的字段尽可能用NOT NULL
字段长度固定的表查询会更快
把数据库的大表按时间或一些标志分成小表
将表拆分
水平切分:将记录散列到不同的表中,各表的结构完全相同,每次从分表中查询, 提高效率。
垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。
主从复制
半同步复制
主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。
优点:利用数据库原生功能,比较简单
缺点:主库的写请求时延会增长,吞吐量会降低
数据库中间件
1所有的读写都走数据库中间件,通常情况下,写请求路由到主库,读请求路由到从库 2记录所有路由到写库的key,在主从同步时间窗口内(假设是500ms),如果有读请求访问中间件,此时有可能从库还是旧数据,就把这个key上的读请求路由到主库。 3在主从同步时间过完后,对应key的读请求继续路由到从库
相关中间件
canal
otter
优点: 能保证绝对一致
缺点:数据库中间件的成本较高
子主题
写流程: 1 如果key要发生写操作,记录在cache里,并设置“经验主从同步时间”的cache超时时间,例如500ms 2 然后修改主数据库 读流程: 1 先到缓存里查看,对应key有没有相关数据 2 有相关数据,说明缓存命中,这个key刚发生过写操作,此时需要将请求路由到主库读最新的数据。 3 如果缓存没有命中,说明这个key上近期没有发生过写操作,此时将请求路由到从库,继续读写分离。
优点: 相对数据库中间件,成本较低
缺点: 为了保证“一致性”,引入了一个cache组件,并且读写数据库时都多了缓存操作。