高性能mysql
2020-03-18 16:03:41 4 举报AI智能生成
高性能mysql
作者其他创作
大纲/内容
1.架构与历史
1.逻辑架构
2.并发控制
1.服务器层
1.读写锁,也叫共享锁,排它锁
2.锁粒度
2.存储引擎层
1.表锁
2.行级锁
3.页锁
3.事务
1.特性
1.原子性
2.一致性
3.隔离性
4.持久性
2.隔离级别
1.未提交读
定义
事务中的修改,即使未提交,对其他的事务也是可见的
问题
其他事务会读取到一个本来会回滚的数据,读到脏数据
2.提交读
定义
一个事务所做的任何修改,在没有提交之前,对其他事务都是不可见的
3.可重复读
定义
一个事务中多次读取同样记录的结果是一致的
问题
无法解决幻读的问题,所谓幻读,就是某个事务在读取某个范围内的记录时,另一个事务又在该范围内插入新的记录,当之前的事务再次读取该范围内的记录时,会产生幻行
解决方法
多版本并发控制(MVCC)
4.可串行化
定义
强制事务串行执行,会在读取的每一行数据上都加上锁,所以可能导致大量的超时和锁争用的问题
3.死锁
1.what
两个或以上事务在同一资源相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象
2.why
数据冲突
存储引擎的实现方式
3.how
死锁检测,立即返回错误
死锁超时
InnoDB将持有最少行级排他锁的事务进行回滚
4.多版本并发控制(MVCC)
1.作用
很多情况下避免加锁操作,开销更低
非阻塞的读
2.原理
某个时间点的快照
3.实现方法
是通过在每行记录后面保存两个隐藏的列实现的,这两个列,一个保存了行创建时候的版本号,一个保存行删除时的版本号.每开始一个事务,系统版本号就会自动递增.事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号做比较
4.隔离级别限制
提交读
可重复读
5.存储引擎
分类
InnoDB(默认)
1.采用MVCC支持高并发
2.基于聚簇索引建立
3.支持热备份
MyIsAM
0.支持地理空间搜索
1.不支持事务和行级锁
2.崩溃后无法安全恢复
3.支持表锁
Mysql内建的其他存储引擎
第三方存储引擎
如何选择
一般都是InnoDB
特有的特性
只有MyIsAM支持地理空间搜索
可以转换引擎
5.创建高性能的索引
1.索引的类型
1.B-Tree索引
1.特性
1.值按顺序存储
2.适合查找范围数据
2.适用查询
1.全值匹配
2.匹配最左前缀
3.匹配列前缀
4.匹配范围值
5.排序
3.限制
非最左列查找无法使用索引
2.Hash索引
1.特性
1.基于索引列的hash码
2.索引结构紧凑
2.优点
查询速度快
3.限制
1.全值匹配才会使用索引
2.不适用于排序
3.hash冲突越高,索引维护代价越高
3.自建hash索引
在列中存放键的hash值,再创建b-Tree索引
4.空间数据索引
1.特性
1.MyIsAM引擎支持
2.用作地理数据存储
2.限制
必须使用mySql GIS相关函数
5.全文索引
1.特性
1.查找文本中的关键字
2.只有match against操作才触发
3.同一个列可以共建全文索引和B-Tree索引
2.限制
2.索引的优点
1.快速定位到表的指定位置
2.减少表扫描的数据量
3.避免排序和临时表
4.将随机IO变为顺序IO
3.高性能的索引策略
使用
1.索引列不能是表达式的一部分,不能是函数的参数
2.对于很长的字符串类型的列,使用前缀索引会带来更好的性能
3.索引列顺序
1.避免随机IO和排序最重要
2.其次是能排除越多数据的列放前面
4.使用索引进行排序
建立
2.在应用程序进行多表关联
1.多列索引效率可能不如联合索引
2.聚簇索引
1.定义
是数据存储结构,InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行
2.特性
节点页只包含了索引列,叶子页包含了行所有数据
3.建立
InnoDB默认主键建立聚簇索引
4.优点
1.数据访问更快,数据和索引放在一起
5.缺点
1.插入速度依赖于插入顺序
2.更新聚簇索引代价很高
3.可能导致全表扫描变慢
6.查询性能优化
1.查询的生命周期
1.网络
2.CPU计算
3.生成统计信息和执行计划
4.锁等待
5.IO操作
2.常见问题
1.查询不需要的记录
2.多表关联是返回全部列
3.总是取出全部列
3.执行计划
explain select ......
1.有没有使用索引
2.扫描了多少数据
4.重构查询
1.多个简单查询代替一个复杂查询
2.分解关联查询
1.定义
1.数据库执行单表查询
2.优点
1.执行单个查询可以减少锁的竞争
2.在应用层做关联,可以更容易对数据库做拆分
4.Schema 与数据类型优化
1.选择优化的数据类型
1.原则
1.更小的通常更好
2.简单就好
3.尽量避免null
2.数据类型
1.整数类型
组成
1.tinyInt
8位
2.smallInt
16位
3.mediuInt
24位
4.int
32位
5.bingInt
64位
属性
unsigned 不允许负值
宽度
指定宽度没有实际意义
2.实数类型
类型
精确
decimal
mysql自身实现,速度较慢
不精确
double
CPU直接支持,运算较快
float
CPU直接支持,运算较快
提升效率方法
在数据量较大时,用bigInt代替decimal,在除以放大倍数即可
3.字符串类型
使用枚举替换字符串类型
优点
enum和enum关联会很快
缺点
字符串是固定的,修改要是用alter table
2.汇总表和缓存表
用计数器表代替汇总
7. MySQL 高级特性
1.分区表
1.定义
将数据按照一个较粗的粒度分在不通的表中
2.创建分区表
表定义时,使用parttion by关键字
3.优点
1.对应用透明
2.只需查询特定的分区,减少查询数据量
2.基准测试
3.服务器性能剖析
0 条评论
下一页
