一:性能监控
1)show profile :系统资源消耗查询,默认禁用,set profiling =1;执行查询语句后执行,show profile 或 show profile (all,block io,context switches,cpu,IPC,page faults,source,swaps) for query (行数);
2)performance_schema:系统整体监控,以后补充
3)show processlist:查看当前数据库连接用户信息
二:优化
1)选择合理的数据类型,可以减少存储空间,提高检索效率。
2)合理使用冗余字段减少关联,从而减少数据的查询。
3)使用自增无业务关联的主键。
4)建表的合理字符集,减少存储空间。纯英文数字使用拉丁,包含中文的表使用utf8b4
5)适当拆分数据,无需大表保存
6)索引优化:以最小的数据量检索到所需要的数据,将随机查询变成顺序查询。排序、分组可以使用索引,实现最小的数据完成排序、分组。
7)分区表指定范围查询,区分热数据和历史数据
8)采用分库,单机性能有限
9)mysql集群,分散压力
三:索引优化细节
1)索引分类:主键索引,唯一索引,普通索引,全文索引,组合索引
按照数据存储方式:聚簇索引和非聚簇索引,按照索引和数据是否存在一个文件中
2)索引名词
回表:根据非主键索引查询,索引不包含所想要的数据,需要根据当前索引所存储的主键索引,查询数据块
覆盖索引:索引就是自己所想查询的数据,当即可以返回数据。
最左原则:索引不支持左模糊,组合索引是按列排序,where必须要包含左边的列
索引下推:针对组合索引,当前where条件包含索引中两个及以上的列,会在索引中进行多条件过滤,而无需根据第一个列查询数据到数据库的service层,再做判断
页分裂:数据在磁盘中存储,是按照4k大小存储,当一个4k页已经存满,现在要插入一条数据,需要将4k页分成两份
页合并:当两个page不满足4k时,需要整合磁盘碎片化。
3)索引结构
哈希表:用hash算法根据列值计算所存储的位置,是数组加链表查询快,索引存储是无序的,无法进行范围查询,进行等值查询,memory采用这样方式,存在hash冲突。当大字段要做索引可以采用。
B+树:非叶子节点存储索引及下一级叶子起始位置,叶子节点存储数据。
4)如何使用索引
1.不对where条件中的索引进行表达式计算
2.尽量使用主键查询,不用回表,过多加载数据
3.大字段使用前缀索引,截取部分字段做索引
4.能使用就使用in,能用union all就使用union all,能使用左模糊,就不用全模糊
5.组合索引,前面列使用范围查询,后面列将失效
6.查询字段类型不匹配,索引会失效
7.更新频繁,区分度不大不易,不能存在空值列建索引
8.理论上关联表不易超过三张,关联列数据类型必须一致
9.能使用limit的时候尽量使用limit
10.组合索引的列不允许超过5个
11.不建立不必要的索引
5)索引监控
show status like '%Handler_read%';
四:分区
将一张表数据拆分成多个子文件,将单个文件数据减少,将逻辑上相关的数据放一起,更快检索出所数据。
1)优点
1.将大数据无法全部放入内存中,切分成若干份,用于区分热数据和历史数据
2.分区表中的数据更容易维护,直接删除整个分区,和对单个分区进行操作
3.可以分布在不同的物理机上,充分利用硬件优势
4.减少锁冲突概率
5.可以独立备份恢复
2)缺点
1.分区表无法使用外键
2.表包含主键或唯一索引,建立分区列必须包含主键或唯一索引
3.分区表个数有限
4.null值会使分区失效
5.开销变大,分区维护成本变高
6.分区列和索引列不匹配,会导致无法进行分区过滤
3)分类:范围分区,列表分区,列分区,hash分区,key分区,子分区
4)详细的后续补充
五:已运行的系统,排查问题
1)开启慢查询,
2)分析慢原因,资源使用情况:网络,cpu,io,上下文切换,系统调用,生成统计信息,锁等待
3)查看执行计划
1、缩减查询数据量,执行计划中row和filter比例
2、select 列中有多余的字段
3、排序分组是否使用的索引,如果使用排序算法,会涉及内存加载数据,io瓶颈
4、count(),max(),min()的使用。
5、复杂语句利用变量进行简化