一、mysql的特性:
ACID:原子性,一致性,隔离性,持久性
二、mysql的索引
- 索引的分类:
普通索引(一个以上字段为组合索引)
唯一索引(一个以上字段为组合索引)
主键索引
覆盖索引
全文索引 - 创建索引的时候需要考虑:
结合实际业务场景,在哪些字段上创建索引,创建什么类型的索引。 - 索引的好处:
快速定位到表的位置,减少服务器扫描的数据;
有些索引存储了实际值,这种情况下使用索引就能完成查询。 - 索引的坏处:
会浪费磁盘空间,所以不要创建非必要的索引;
插入、更新、删除需要维护索引,带来额外的开销;
索引过多,修改表的时候重构索引性能差。 - 索引的优化:
TEXT,BLOG类型的字段使用前缀索引,只检索前几个字符,提高检索速度;
尽量使用字段长度少的作为索引,索引值过长查询速度会受影响;
内容变动少且查询频繁,可以多建立几个索引;
内容变动频繁,谨慎创建索引;
如果某个业务字段常用来做查询条件,则为该字段建立索引来提高查询速度;
组合索引选择业务查询最相关的字段;
选择合适的索引顺序。
三、mysql的查询关键词及执行顺序
- select:查询哪些字段
- from:查询那张表
- where:初步过滤条件
- group by:过滤后进行分组
- having:对分组后的数据进行分组
- order by:排序
四、mysql的相似字段类型
- varchar和char
相同点:
存储的都是字符
不同点:
char长度固定,varchar长度可变;
字符长度小于定义长度时,char用空格填充,varchar按实际长度存储;
char存储速度比varchar快(varchar还得存储长度);
使用场景:
char适用于很短的固定长度的字符串,例如手机号、MD5值等;
varcahr适用于长度不定的字符串,例如地址、邮箱等。
注意:
varchar最大是65535,超过用text霍blog. - datetime和timestamp
不同点:
字节长度,datetime 8字节,timestamp 4字节;
时间范围,datetime 1000-01-01 00:00:00到9999-12-31 23:59:59,
timestamp 1970-01-01 00:00:00到2038-01-19 11:14:07(2^31-1转成北京时间的值);
是否随时区而变化:datetime与时区无关,timestamp随数据库的时区而变化。
五、mysql的大数据量分页查询
场景:日志记录
SQL:SELECT * FROM LOG LIMIT N,M;
SQL分析:mysql执行时先扫描到N行,然后再去取M行,N越大,mysql扫描的记录数越多,SQL性能越差。
优化方法:
- 缓存
- 使用ElasticSearch
- 考虑使用覆盖索引:SELECT field1,field2 FROM LOG LIMIT 1000000,100
- 使用自增id,定位偏移位置的id
SELECT * FROM LOG WHERE id >= ( SELECT id FROM LOG limit 1000000,1) limit 100
六、mysql的常见日志
- redo重做日志
- undo回滚日志
- slow query log慢查询日志
- binlog二进制日志
- relay log中继日志
- error log错误日志
- general log普通日志
七、mysql的主从复制
-
主从复制原理
主库的dump log线程和从库的IO线程交互,IO线程请求主库的binlog,写入到从库的relay log,从库的SQL线程读取中 继日志进行解析后写入到从库。 -
搭建主从的目的
容灾使用,用于故障切换;
业务需要,进行读写分离减少主库压力、 -
主从遇到的同步延迟问题
原因:
主从复制是单线程,主库TPS高,产生的超所从库SQL线程的执行能力;
从库执行了大的SQL操作,阻塞等待;
服务器硬件问题,如磁盘、CPU、网络延迟等。解决方法:
业务允许的话可以写入缓存,从缓存读;
读写分离,一主多从,分散主库从库的压力;
提高硬件配置,如SSD固态硬盘,更好的CPU、网络;
进行分库分表,减少单机压力。 -
主从遇到的数据不一致问题
原因:
本身复制延迟;
主库宕机或者从库宕机;
把一个从库提升为主库
解决方法:
定期进行主从复制数据一致性校验并修复,使用percona公司下的工具pt-table-checksum工具进行数据一致性校验,pt-table-sync工具对从库进行修复不一致的数据。
八、生产环境数据库性能监控和优化
-
业务性能
上线前审查新增SQL和分析SQL执行计划,建立的索引是否合理;
看起慢查询日志,定期分析慢查询日志;
监控CPU、内存利用率、网关IO、流量带宽随时间变化的统计图;
吞吐量QPS/TPS,一天内读写随时间变化的统计图。 -
数据安全
短期增量备份,定期全量备份;
检查是否存在非授权用户,检查是否存在弱口令,检查网络防火墙;
导出数据进行脱敏;
增加操作数据日志;
数据库账号密码独立,权限独立,防止多个库公用一个账号密码;
高可用,主从架构,多机房部署。