MySQL-数据库优化

 

数据库优化:

 

数据库设计:

　　1.字段选型:

　　　　数字类型: tinyint smalint mediumint int bigint

　　　　字符类型: char varchar

　　　　事件类型: date datetime timestamp

　　首先秉承的原则:

　　　　1.小就是美

　　　　2.简单就是美

　　　　3.先规范,必要时灵活

 

　　2. 表设计优化

　　　　时间字段防止时间类型datetime,选择int,为了查询和索引空间及程序层灵活处理

　　　　表字段的长度例如名字,个人描述等不需要很多字符的数据,尽量可能用固定类型 char 或者

　　　　varchar(n) ,但是n必须严格控制,而不再是之前随意的100 200 255等,这样严重浪费空间及接口数

 

　　据传输时的消耗

　　　　避免使用text, 比如使用,独立存放

　　　　不建议在数据库中保存图片,文档,视频对象,数据库是用来存储结构化数据,尽量保证它的简单

　　　　主键字段用于多表关联时,用于自增数据类型,不建议用字符类型做主键

　　　　最好不要用外键,比如一些可能涉及外键的更新, 查询 ,可以让程序层去处理

 

 

 

数据库性能优化:

 

　　1.存储引擎优化

　　　　MyISAM:MyISAM引擎查询性能优越,因为它的所有数据及索引都在一个节点上,好处是无需数据一致性, 高并发 堆表, 存储和查询是随机的 查询慢,写入快 因为无序,但是随便写入, MyISAM容易被损坏,MyISAM底层就是IO磁盘写入,所以容易崩盘

　　　　InnoDB: InnoDB 是专注高并发业务的存储引擎,拥有事物来保证数据的一致性和行锁机制 索引组织表 查询快 写入慢 因为有序,但是写入需要先找顺序 InnoDB相比较不容易被损坏,因为InnoDB有缓存区

　　　　TokuDB:海量数据,采集数据,高压缩数据,早期日志存储引擎选用,但是用户量及访问量增长,出现db写入卡顿,主从 waiting for ack等

　　　　　　但是拥有优秀的写入性能及数据压缩性能

　　　　Infobright：由于统计系统需要频繁汇总和分析多大至少 5 张业务大表，鉴于此特意调研了它，感觉有点跟数据仓库差不多，不过由于当时的数据库没有自带这个存储引擎就换 es 了

 

　　2.索引优化

　　　　由于 MySQL 索引是一棵平衡 b + 树，然而 b + 树最好的就是查找最小或最大很快，并且随着数据量的增长，树的高度不会很大，因此基于主键查找一条数据时也就是树高度 + 1 次 IO 扫描。

 

　　如果查询字段涉及到回表，可能就需要一次回表 IO，根据 MySQL 官方单次 IO 预计是 10ms，也就是说基于主键查询会超级快。

 

　　MySQL 更新操作尽量基于主键更新

 

　　谨慎合理添加索引，不是越多越好。需要平衡 select 和 dml，考虑索引的效率

　　数据排除 predicate 及数据过滤 fiter。

 

　　不在列上做运算，让程序去做运算，数据比较时类型一致。

 

　　索引列一般尽量不更新，频繁更新的列见索引，得慎重。

 

　　合理建立联合索引，避免冗余索引

 

 

　　第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

　　第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

　　第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

 

　　索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引，例如：

　　在经常需要搜索的列上，可以加快搜索的速度；

　　在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

　　在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度；

　　在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

　　在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

　　在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

 

　　缓存原理: 硬盘到内存(拷贝)

　　　　利用高效的内存机制. 把经常查询的磁盘资源按照某种规则放入内存通常是类似于KV结构,按照一定规则进行刷新和缓存.

　　　　索引原理: 硬盘+数据结构+内存(建立关联索引)

　　　　利用内存来存储数据的位置关联信息, 硬盘通过数据结构化存储配合索引快速定位到数据位置, 再通过io读取

　　　　与缓存的区别:缓存是全量内存备份, 索引是关键信息内存备份,其他信息进行硬盘读写

 

 

 

　　3.SQL优化

　　　　SQL尽量保持简单,MySQL优化器不足,处理负责SQL时容易选错执行计划

　　　　MySQL没有SQL级并行, hashjoin, 分析函数等特性, 处理复杂SQL能力不强

　　　　复杂SQL拆分成简单SQL

　　　　SQL where 条件中的变量都要使用绑定变量

　　　　绑定变量可以提升系统性能,并且提高安全性

　　　　查询避免扫描所有数据,例如 not in等,尽量缩小范围

　　　　减少数据库运算量,降低数据库压力

　　　　不用select * , 只查询需要的字段, 减少 cpu, 内存, 网络等消耗,减少由于表变更对应应用的影响; 使用覆盖索引提升性能

　　　　MySQL条件语句执行顺序是从左到右,所以,查询范围越 "大" 的可以先执行,让其后查询范围变小 ( oracle数据库是从右到左 )

 

 

　　4.数据 SQL 的cache

　　　　关闭query cache (不然会影响tps 2) redis缓存,减少数据库 ops,降低数据库压力

 

 

　　关于SQL总结:

　　　　SQL一定要避免全表扫描

　　　　SQL需要的资源是 cpu+men+io+net+lock

　　　　优化SQL本质上利用合理算法,平衡这些资源,更好的执行SQL,满足应用需求,解决吞吐量和响应时间

　　　　好的SQL是数据量增加或者并发增加, SQL运行时间不变, 后者影响不大

　　　　好的SQL基本来自于: 好的软降框架, 好的存储架构, 良好的SQL书写

　　　　SQL优化最重要的思想就是 减少IO

 

 

数据库性能监控及容灾:

　　1.MySQL常用性能监控信息

 

　　2.MySQL横向扩展

　　　　数据量扩大,单台数据库服务器性能已无法满足业务需求,该考虑增加服务器扩展架构,主要思想是分解单台数据库负载,突破磁盘I/O性能,热数据存放缓存中,降低磁盘I/O访问频率,但是要注意过程中的数据的安全性, 高可用性

 

　　3.增加缓存

　　　　数据库增加缓存系统,把热数据缓存到内存,如果缓存中有请求的数据就不再去请求MySQL,为了减少数据库负载,缓存实现包括本地缓存和分布式缓存.

　　　　本地缓存是将数据缓存到本地服务器内存中或文件中,分布式缓存可以缓存海量数据

　　　　扩展性好, 主流的分布式缓存系统包括: Memcached, Redis

　　　　Memcached性能稳定,数据缓存在内存中,速度很快,QPS理论可达8w左右

　　　　如果想数据持久化就选择用Redis, 性能不低于Memcached

 

　　工作过程: 请求数据 ==> redis 是否存在 ==>无( 去MySQL 数据库 )

 

　　4.主从复制与读写分离

　　　　系统一般来说读多写少,可部署一主多从架构, 主数据库负责写操作,并做双机热备,多台从数据库做负载均衡,负责读操作

　　　　读写分离:读写分离多数在代码层面实现读写分离,效率高. 另外一种方式通过代理程序实现读写分离, 企业中应用较少,会增加中间件消耗. 中间件代理有:MyCat,Atlas等

 

　　5.分库

　　　　分库是根据业务将数据库中相关的表分离到不同的数据库中. 例如业务量很大,还可以将分离后的数据库做主从复制架构,进一步避免单库压力过大

 

 

数据库维护:

　　1.开启慢查询日志

　　　　MySQL开启慢查询日志,分析出那条SQL语句比较慢,支持动态开启

　　　　在my.cnf文件中,可以指定慢查询多长时间,系统认定为慢SQL,标记,然后处理

　　2.数据库备份

　　　　使用XtraBackup凌晨定时备份数据