MySQL数据库工程师入门实战课程视频教程
4227 人在学
数据库对于一个企业来说是相当重要的,不管是电子交易、金融证券、企业资源规划系统,都需要用到数据库服务器。随着数据库技术在商业上的不断发展,现在数据库应用规模已经越来越大型化,这对于数据库服务器的要求也就越来越高。
举个很简单的例子,一个论坛日发帖量如果在1000左右,那么运营一年的话,所产生的数据记录就有40万条左右,如果要在这40万条数据里做查询、编辑等工作,那么这对于数据库服务器的硬件要求是相当高的。另外随着运营时间的增加,那些以数据库为主要支撑的应用例如ERP系统、论坛系统,在具备一定规模之后,对服务器硬件的设备要求就更加严苛,一旦服务器设备无法承受数据库的工作压力,将直接造成系统和各种业务的瘫痪,其损失也会是非常巨大的。那么,我们在数据库的运维上遵循哪些原则呢?
一、数据库运维工作总原则
1、能不给数据库做的事情不要给数据库,数据库只做数据容器。
2、对于数据库的变更必须有记录,可以回滚。
二、权限相关
总原则,以最低粒度控制权限。
SELECT权限:所有开发人员均可拥有自己业务范围内的表权限。
INSERT/UPDATE/DELETE权限:所有项目经理可以拥有自己业务范围内的表权限。
Structure权限:数据库管理员可以拥有。
Administration权限:系统管理员和数据库管理员可以拥有。
程序访问权限:根据IP和系统名建立用户名,只拥有必须表的访问权限。
全部权限:技术总监掌管,MySQL系统表mysql表权限除技术总监任何人不得拥有。
权限粒度:开发人员权限粒度到表一级,数据库管理员和系统管理员权限粒度到库一级。
访问限制:除DBA和系统管理员需要24小时保证可以操作数据库外,其他人均需要绑定IP。
密码强度:必须根据目标/来源/用户等信息设置密码,保证不用用户在不同主机从不同来源的密码不一样,每隔一段时间需要集中更换密码。
三、改表相关
总原则,以最少的开销按需记录数据,所有变更要有记录。
表结构修改流程:开发人员提交建/改/删表的SQL(必须有注释)给DBA,并且说明建/改/删表原因,DBA审查,与开发人员交流修正,由DBA执行SQL操作,并记录。
表结构改动注意:(越靠前优先级越高)
按需建立字段,不得采用远大于需求的类型。
尽量采用无符号型数据类型。
尽量采用固定长度类型。
尽量避免TEXT类型。
尽量用数字来代替字符。
可以用ENUM枚举数量不多的字符。
如果必须采用TEXT类型,如无特殊情况,必须拆表单独存放TEXT列。
存在必须用函数计算才能做条件的列,新建一存放函数计算结构,对新列进行索引,采用触发器自动更新。
记录修改时间尽量采用时间戳,不要采用时间类型列然后手动设置。
分离表中常用和不常用数据,分开存储。
历史数据要及时分离,按一定规则命名表或存放到数据仓库。
类似标记字段设计要合理,尽量避免在SQL中出现<>,尽量使用>/</=来完成条件查找。
类型选择细则参考已发的类型选择文档。
四、索引相关
总原则,以高并发SQL为核心建立索引,尽量复用索引。
除非某个字段经常单独作为查询字段,并且不会作为其他组合索引的第一个列,才为本字段建立单独的索引。
尽可能建组合索引,综合考量涉及的SQL,遵循尽量复用的原则建立索引。
优先处理高并发SQL的索引,很少使用的SQL或者只在闲时使用的SQL可以不为其索引。
小表可以不建立索引(如果参与关联需要为关联字段索引)。
索引后结果集仍较大可以增加排序字段进索引,若执行计划不使用,则删除。
关联字段除特殊情况都需要建索引。
要及时根据SQL的使用情况调整索引。
五、架构相关
①总原则,尽可能在内存中完成大部分操作。
②内存足够选择InnoDB(高并发数据与内存大小相当),内存不足用MyISAM,归档用Archive,事务相关用InnoDB,临时高并发数据用Memory。
③Master-Slave架构,适合明显读大于写的情况。
④Master-Master架构,无比使两个Master产生不同的主键,例如一台产生奇数主键,一台产生偶数主键,
⑤单机事务用InnoDB,集群事务用LVS+NDB,切不可直接用NDB,效率极低。
⑥分布式事务尽量避免,如果必须,则采用XA事务,分段提交,或者直接采用NDB集群引擎。
⑦原则上历史数据和生产数据不要放在同一数据库,如历史数据极少使用,则建立新库将历史数据保持表名不变存入,只要USE改变数据库就可以查询历史数据,比较常用的历史数据采用表名+日期来存放相应的历史数据,通过表明和日期拼接来做历史查询。
⑧主库写压力较大时采取级联复制,读库压力更大时采用一对多复制,以平衡压力为目标。
⑨历史数据拆分由DBA与开发人员商议拆分时间,每次拆分时间由DBA与开发人员确认则删除或转移至数据仓库。
六、SQL相关
①总原则,最小化查询结果集,不做不必要的操作,分散压力到平时。
②尽量不做全表查询,根据需要选择需要的字段需要的行。
③条件尽可能详细,不要让数据库扫描不必要的行。
④选择条件尽可能按照一致的顺序出现,最大化利用索引。
⑤尽量将大结果集操作拆分到多次执行,避免数据库长时间被锁。
七、工作流程
1.通过Cacti和mysqlreport脚本查看运行状态
2.通过mystat脚本监控实时状态
3.通过mysqlsla脚本分析昨日慢查,可作为计划任务每天定时执行,清空旧慢查
4.对每日慢查进行调优
5.通过mysqlidxchx脚本对无效索引进行检查
6.建表要询问哪些字段会用来查询和连接,建立索引
7.改字段要问清类型和范围,设立合适的数据类型
8.表结构的修改必须加注释,每个字段都要有注释,除了id
9.从数据库删除数据一定要先备份再删除。
10.每月末要执行历史数据清理,通过mysqldump–where来导出指定范围的数据,插入数据仓库。
11.无用表和字段要及时清理
小编结语:
更多内容尽在课课家教育~~