数据库优化: 数据库设计: 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凌晨定时备份数据
标签：缓存,数据库,MySQL,查询,索引,SQL,优化 From： https://www.cnblogs.com/qiezi777/p/16877945.html