南宁江南区网站制作价格,西安有哪些网站,威海网站建设哪家的好,开公司网站创建费用1、SQL性能优化
1、如何分析SQL的性能#xff1f;
我们可以使用EXPLAIN命令来分析SQL的执行计划 #xff0c;执行计划是指一条SQL语句在经过MySQL查询优化器的选择后具体的执行方式
EXPLAIN并不会真的去执行相关的语句#xff0c;而是通过查询优化器 对语句进行分析…1、SQL性能优化
1、如何分析SQL的性能
我们可以使用EXPLAIN命令来分析SQL的执行计划 执行计划是指一条SQL语句在经过MySQL查询优化器的选择后具体的执行方式
EXPLAIN并不会真的去执行相关的语句而是通过查询优化器 对语句进行分析找出最优的查询方案并显示对应的信息。
2、**查询SQL尽量不要使用SELECT * ** 而是 select 具体字段
select*会消耗更多CPU无用字段增加网络带宽资源消耗增加数据传输时间可能导致索引失效
3、分页优化
普通的分页在数据量小的时候耗费时间还是比较短的但如果数据量变大达到百万甚至是千万级别普通的分页耗费的时间就非常长了
select score,namefrom cur_order order by score desc limit 1000000,10 ;如何优化可以将上述SQL语句修改为子查询 ,先子查询出大于1000000的数据然后从这里开始分页分页的数据量会少很多。先查询出limit第一个参数对应的主键值再根据这个主键值去过滤并分页效率更快
select socre,namefrom cur_order where id (select id from cus_orderlimit 1000000,1)limit 104、避免多表做join
超过三个表禁止join需要join的字段数据类型保持绝对一致多表关联查询时保证被关联的字段需要有索引
实际业务场景避免多表join的做法
单表查询后在内存中自己做关联对数据库做单表查询再根据查询结果进行二次查询以此类推最后再进行关联
5、建议不要使用使用外键与级联
外键对分库分表不友好维护起来很麻烦
6、选择合适的字段类型
可以将IP地址转换成整型数据存储性能更好占用空间也更小
MySQL提供了两个方法来处理IP地址
INET_ATON() : 把ip转为无符号整型4-8位
INET_NTOA()把整型的ip转为地址
插入数据前先用INET_ATON() 把ip地址转为整型显示数据时使用INET_NTOA() 把整型转换为ip地址.。
对于非负型的数据自增ID整型IP年龄来说要优先使用无符号整型来存储小数值类型比如年龄状态表示如0/1优先使用TINYINT 类型对于日期类型来说DateTime类型耗费空间更大且没有时区信息建议使用Timestamp金额字段用decimal 避免精度丢失尽量使用自增id作为主键
自增id插入数据时是有序的不需要重新排序如果是非自增id在数据插入到B树的叶子节点时会先进行排序性能非常低。所以主键id一定要自增。
不过像分库分表这类场景就不建议使用自增id作为主键应该使用分布式ID比如uuid
7、尽量用UNION ALL 代替 UNION
UNION会把两个结果集的所有数据放到临时表中后再进行去重操作 更耗时更消耗CPU资源
UNION ALL 不会再对结果集进行去重操作获取到的数据包含重复的项
8、批量操作
对于数据库中的数据更新如果能使用批量操作就要尽量使用减少请求数据库的次数提高性能例如插入数据时一次性插入多条而不是一条一条的插入
9、优化慢SQL
首要要找到哪些SQL语句执行速度比较慢
开启慢查询日志功能默认是关闭的
set global slow_query_log ON;找到了慢SQL语句之后通过EXPLAIN 命令分析对应的SELECT语句
MySQL慢查询如何优化
检查是否走了索引如果没有则优化SQL利用索引检查所利用的索引是否是最优索引检查所查字段是否都是必须的是否查询了过多字段查出来了多余数据检查表中数据是否过多是否应该进行分库分表检查数据库实例所在机器的性能配置是否太低是否可以适当增加资源
10、分页优化
如果知道查询结果只有一条或者只要最大/最小一条记录建议用limit 1加上limit 1 后只要找到对应的一条记录就不会继续往下扫描了效率会大大提高
-- 反例
selectidnamefrom employee wherenamejay;-- 正例
selectidnamefrom employee wherenamejaylimit1;11、联表查询
Inner join left join right join 优先使用Inner join 如果是left join左边表结果尽量小同理如果是right join,右表表结果尽量小
12、慎用distinct
慎用distinct关键字因为当查询很多字段时如果使用distinct数据库引擎就会对数据进行比较过滤掉重复数据这个比较、过滤的过程会占用系统资源
2、MySQL性能优化
1、数据库命名规范
所有数据库对象名称必须使用小写字母并用下划线分割所有数据库对象名称禁止使用MySQL关键字数据库对象的命名要能做到见名识意并且最好不要超过32个字符临时库表必须以temp_ 为前缀并以日期为后缀备份表必须以bak_ 为前缀并以日期为后缀所有存储相同数据的列名和列类型必须一致一般作为关联列如果查询时关联列类型不一致会自动进行数据类型隐式转换 会造成列上的索引失效导致查询效率降低
2、数据库基本设计规范
所有表必须使用InnoDB存储引擎数据库和表的字符集统一使用UTF8
如果数据库中有存储emoji表情的需要字符集需要采用utf8mb4字符集
所有表和字段都需要添加注释
使用comment从句添加表和列的备注维护数据字典
控制单表数据量的大小建议控制在500万以内
数据量过大会造成修改表结构备份恢复都会有很大问题
可以用历史数据归档应用于日志数据分库分表应用与业务数据等手段控制数据量大小 经常一起使用的列放到一个表中避免更多的关联操作 禁止在数据库中存储文件比如图片这类大的二进制数据
MySQL数据库可以存储文件图片是直接存储文件对应的二进制数据
不过还是建议不要在数据库中存储文件会严重影响数据库性能消耗过多存储空间
可以选择云服务厂商提供的开箱即用的文件存储服务如阿里云的OSS对象存储数据库只存储文件地址信息
3、数据库字段设计规范
优先选择符合存储需要的最小的数据类型避免使用TEXTBLOB数据类型最常见的TEXT类型可以存储64K的数据
MySQL内存临时表不支持TEXT、BLOB这样的大数据类型如果查询中包含这样的数据在排序等操作时就不能使用内存临时表必须使用磁盘临时表进行对于这样的数据MySQL还要进行二次查询会使sql性能变得很差
避免使用ENUM类型
修改ENUM类型需要使用ALTER语句
ENUM类型的ORDER BY操作效率低需要额外操作
ENUM数据类型存在一些限制比如建议不要使用数值作为ENUM的枚举类
尽可能把所有列定义为 NOT NULL
除非有特别的原因使用NULL值应该总是让字段保持NOT NULL
索引NULL列需要额外的空间来保存所以要占用更多的空间
进行比较和计算时要对NULL值做特别的处理
单表不要包含过多字段
4、索引设计规范
限制每张表上的索引数量建议单张表索引不超过5个禁止使用全文索引禁止给表中每一列 都建立单独的索引
如果要用到多个列可以建立联合索引
每个InnoDB表必须有个主键
InnoDB是一种索引组织表数据的存储的逻辑顺序和索引的顺序是相同的
InnoDB是按照主键索引的顺序来组织表的
a. 不要使用更新频繁的列作为主键不使用多列主键
b.不要使用UUIDMD5字符串列作为主键无法保证数据的顺序增长
c.主键建议使用自增id值
选择索引列的顺序
建立索引的目的是希望通过索引进行数据查找减少随机IO增加查询性能索引能过滤出越少的数据则从磁盘中读入的数据就越少查询效率越高
a. 区分度最高的放在联合索引的最左侧
b.尽量把字段长度小的列放在联合索引的最左侧字段长度越小一页能存储的数据量越大IO性能越好
c.使用最频繁的列放到联合索引的左侧比较少的建立一些索引
避免建立冗余索引和重复索引
因为查询优化器是根据所有索引去选择执行方案冗余索引或重复索引会增加优化器生成执行计划的时间影响查询性能
对于频繁的查询优先考虑使用覆盖索引 覆盖索引就是包含了所有查询字段 的索引 比如创建索引{a,b,c}查询语句select b from user where b1索引覆盖了查询字段会使用到覆盖索引即不用进行回表查询 覆盖索引的好处
a. 避免InnoDB表进行索引的二次查询 对于非聚簇索引叶子节点中保存的是索引字段和主键信息当用非聚簇索引查询数据时先找到对应的主键信息然后通过主键信息回表到主键索引表中查具体数据 二次查询会降低查询效率。而覆盖索引索引覆盖了查询字段在非聚簇索引的叶子节点就能查到需要的数据避免了对主键的二次查询减少了IO操作提升查询效率
b. 可以把随机IO变成顺序IO加快查询效率 由于覆盖索引是按键值的顺序存储的对于IO密集型的范围查找来说对比随机从磁盘读取每一行的数据IO要少的多因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO
5、数据库SQL开发规范
优化对性能影响较大的SQL语句
优化慢查询SQL语句
充分利用表上已经存在的索引禁止使用select *禁止使用不含字段列表的insert语句避免数据类型的隐式转换避免使用子查询可以把子查询优化为join操作避免使用join关联太多的表禁止使用order by rand进行随机排序where从句中禁止对列进行函数转换和计算拆分复杂的大SQL为多个小SQL程序连接不同的数据库使用不同的账号禁止跨库查询
6、数据库操作行为规范
超100万行的批量写操作要分批多次进行操作
大批量操作可能会造成严重的主从延迟
binlog日志为row格式时会产生大量的日志
会产生大事务操作
禁止为程序使用的账号赋予super权限
super权限只能留给DBA处理问题的账号使用
对于程序连接数据库账号遵循权限最小原则
程序使用数据库账号只能在一个DB下使用不准跨库
程序使用的账号原则上不准有drop权限
3、读写分离
1、读写分离是什么
读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上 大幅提升读性能 一般我们会选择一主多从也就是一台主数据库负责写其他的从数据库负责读。主库和从库之间会进行数据同步以保证从库中数据的准确性这样的架构实现起来比较简单符合系统的写少读多的特点。
2、读写分离会带来什么问题
读写分离对于提升数据库的并发非常有效但是同时会引来的问题主库和从库的数据存在延迟比如在写完主库之后主库的数据同步到从库是需要时间的这个时间差就导致了主库和从库的数据不一致的问题。这就是主从同步延迟
解决方法
强制读请求到主库处理
既然更改后从库的数据过期那就直接到主库中读取。通过Sharding-JDBC的HintManager分片键值管理器我们可以强制使用主库
对于这种方案我们可以将那些必须获取最新数据的读请求都交给主库处理
延迟读取
主库的数据同步到从库需要时间那我们就等待时间之后再去从库读取数据。
3、实现读写分离
部署多台数据库选择其中的一台作为主数据库一台或多台作为从数据库保证主数据库和从数据库之间的数据是实时同步的这个过程也就是主从复制系统将写请求交给主数据库处理读请求交给从数据库处理
如何分离读写请求
代理方式在应用和数据中间加一个代理层应用程序所有得到数据请求都交给代理层处理代理层负责分离读写请求将它们路由到对应的数据库中。类似功能的中间件有MySQL Router、Atlas等
组件方式通过引入第三方组件来帮助我们读写请求推荐使用sharding-jdbc直接引入jar包即可使用非常方便。
4、主从复制的原理
MySQL的binlog日志主要记录了MySQL数据库中数据的所有变化数据库执行的所有DDL和DML语句因此我们根据主库的binlog日志就能够将主库的数据同步到从库中
主库将数据库中数据的变化写入到binlog从库连接主库从库创建一个I/O线程向主库请求更新的binlog主库会创建一个binlog dump线程来发送binlog从库中的I/O线程负责接收从库的I/O线程将接收到的binlog写入到relay log中从库的SQL线程读取relay log 同步到数据本地再执行一遍SQL
4、分库分表
当数据库不足以支撑并发量当表无法装下再多的数据这时候采用分库分表来进行优化----前提是SQL优化都已经用过了还需要优化的情况下才选择实现分库分表
1、分库
分库就是将数据库的数据分散到不同的数据库上可以垂直分库也可以水平分库 垂直分库单一数据库按照业务进行划分不同的业务使用不同的数据库进而将一个数据库的压力分担到多个数据库。比如将一个数据库的用户表、订单表、商品表分别单独拆分为各自的数据库用户数据库、订单数据库、商品数据库 水平分库同一张表按一定规则拆分到不同的数据库中两个数据库中的这张表字段是一样的解决了单表的存储和性能瓶颈的问题。比如当订单表数据量太大可以对订单表水平分表然后将切分好的2张订单表分别放在两个不同的数据库
2、分表
分表就是对单表的数据进行拆分可以是垂直拆分也可以是水平拆分
垂直分表对数据表列的拆分把一张列比较多的表拆分为多张表不同表的结构不一样比如可以将用户表的一些字段单独抽出来作为一个表水平分表对数据表行的拆分解决单一表数据量过大的问题。比如可以将用户表拆分为多张用户表字段一样表结构一样。
水平拆分只能解决数据量大的问题表的结构并没有改变。为了提升性能我们通常会将拆分后的多张表放在不同的数据库中水平分表通常和水平分库同时出现
例如当一个表有几百万条数据有十几个字段水平拆分就是将几百万条数据分出一部分在另一个分库里分库和原库结构完全一样分表和原表结构也一样就是数据不同垂直拆分则是将表中的字段分出一部分到分表里这样分库和原库的结构和数据都不一样分表和原表的结构和数据也不一样。水平是按照行来拆分垂直是按照列来拆分
3、什么情况下需要分库分表
单表的数据达到千万级别以上数据库读写速度比较缓慢数据库中的数据占用的空间越来越大备份时间越来越长应用的并发量太大
4、分库分表会带来什么问题
join操作同一个数据库中的表分布在了不同的数据库导致无法使用join操作。这样导致我们需要手动进行数据的封装比如在一个数据库中查询到一个数据之后再根据这个数据去另外一个数据库中找对应的数据事务问题同一个数据库中的表在不同的数据库如果单个事务的操作涉及到多个数据库数据库自带的事务就无法满足需求了分布式id分库之后数据遍布在不同服务器上的数据库数据库的自增主键已经没办法满足生成的主键唯一了。为不同的数据节点生成去哪聚唯一主键这就需要引入分布式id了需要更多的数据库服务器成本很高
尽量不做分库分表会带来很多问题如查询等都要重新排序
5、分布式数据层中间件
1、分布式数据层中间件是什么
分布式数据访问层中间件旨在为一个通用数据访问层服务支持MySQL动态数据源、读写分离、分布式唯一主键生成器、分库分表、动态化配置 等功能。并且支持从客户端角度对数据源的各方面比如连接池、SQL等进行监控。
2、常见的数据层中间件
TDDL
淘宝根据自己的业务特点开发了TDDL框架主要解决了分库分表对应用的透明化以及异构数据库之间的数据复制它是一个基于集中式配置的JDBC datasource实现
实现动态数据源、读写分离、分库分表
缺点分库分表功能还未开源当前公布文档较少需要diamond淘宝内部使用的一个管理持久配置的系统
Atlas
Qihoo 360开发维护的一个基于MySQL协议的数据中间层项目。它实现了MySQL的客户端与服务端协议作为服务端与应用程序通信同时作为客户端与MySQL通信
实现读写分离、单库分表
缺点不支持分库分表
MTDDL
美团点评分布式数据访问层中间件
实现动态数据源、读写分离、分库分表与tddl类似
