南京cms模板建站,学院加强网站建设,潍坊知名网站建设怎么收费,自己怎么创建一个网站从多个维度优化#xff0c;这里的优化维度有四个#xff1a;硬件配置、参数配置、表结构设计和SQL语句及索引。
其中 SQL 语句相关的优化手段是最为重要的。
一、硬件配置 硬件方面的优化可以有 对磁盘进行扩容、将机械硬盘换为SSD#xff0c;或是把CPU的核数往上提升一些…从多个维度优化这里的优化维度有四个硬件配置、参数配置、表结构设计和SQL语句及索引。
其中 SQL 语句相关的优化手段是最为重要的。
一、硬件配置 硬件方面的优化可以有 对磁盘进行扩容、将机械硬盘换为SSD或是把CPU的核数往上提升一些增强数据库的计算能力或是把内存扩容了让Buffer Pool能吃进更多数据、等等。但这个优化手段成本最高但见效最快。
有句话说的好能通过硬件升级来解决的事情千万别碰代码。哈哈。
二、参数配置 保证从内存读取 MySQL 会在内存中保存一定的数据通过 LRU最近最少使用算法将不常访问的数据保存在硬盘文件中。尽可能的扩大内存中的数据量将数据保存在内存中从内存中读取数据可以提升 MySQL 性能。
MySQL 使用优化过后的 LRU 算法
普通LRU末尾淘汰法新数据从链表头部加入释放空间时从末尾淘汰 改进LRU 链表分为new和old两个部分加入元素时并不是从表头插入而是从中间 midpoint位置插入如果数据很快被访问那么page就会向new列表头部移动如果 数据没有被访问会逐步向old尾部移动等待淘汰。每当有新的page数据读取到 buffer pool时InnoDb引擎会判断是否有空闲页是否足够如果有就将free page从free list列表删除放入到LRU列表中。没有空闲页就会根据LRU算法淘汰LRU链表默认的页将内存空间释放分配给新的页。 LRU 算法针对的是 MySQL 内存中的结构这里有个区域叫 Buffer Pool缓冲池 作为数据读写的缓冲区域。把这个区域进行相应的扩大即可提升性能当然这个参数要针对服务器硬件的实际情况进行调整。
通过以下命令可以查看相应的BufferPool的相关参数
show global status like innodb_buffer_pool_pages_%
输入以下命令可以查看 BufferPool 的大小
show variables like %innodb_buffer_pool_size% 在这里我们可以修改这个参数的值如果该服务器是 MySQL 专用的服务器我们可以 修改为总内存的 60%~80% 当然不能影响系统程序的运行。
这个参数是只读的可以在 MySQL 的配置文件my.cnf 或 my.ini中进行修改。Linux 的配置文件为 my.cnf。
# 修改缓冲池大小为750M innodb_buffer_pool_size 750M 数据预热 数据预热相当于将磁盘中的数据提前放入 BufferPool 内存缓冲池内。一定程度提升了读取速度。
对于 InnoDB这里提供一份预热 SQL 脚本
#mysql5.7版本中如果DISTINCT和order by一起使用将会报3065错误sql语句无法执行。这是由于5.7版本语法比之前版本语法要求更加严格导致的。 #推荐在mysql的配置文件my.cnf文件(linux)/my.ini文件(window) 的mysqld中增加或者修改sql_model配置选项 #sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #重启后生效 SELECT DISTINCT CONCAT(SELECT ,rowlist, FROM ,db,.,tb, ORDER BY ,rowlist,;) selectSql FROM ( SELECT engine,table_schema db,table_name tb, index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) rowlist FROM ( SELECT B.engine,A.table_schema,A.table_name, A.index_name,A.column_name,A.seq_in_index FROM information_schema.statistics A INNER JOIN ( SELECT engine,table_schema,table_name FROM information_schema.tables WHERE engineInnoDB ) B USING (table_schema,table_name) WHERE B.table_schema NOT IN (information_schema,mysql) ORDER BY table_schema,table_name,index_name,seq_in_index ) A GROUP BY table_schema,table_name,index_name ) AA ORDER BY db,tb; 降低磁盘的写入次数 1增大 redo log减少落盘次数
redo log 是重做日志用于保证数据的一致减少落盘相当于减少了系统 IO 操作。
innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size
2通用查询日志、慢查询日志可以不开 binlog 可开启。
通用查询和慢查询日志也是要落盘的可以根据实际情况开启如果不需要使用的话就可以关掉。binlog 用于恢复和主从复制这个可以开启。
查看相关参数的命令
# 慢查询日志 show variables like slow_query_log% # 通用查询日志 show variables like %general%; # 错误日志 show variables like %log_error% # 二进制日志 show variables like %binlog%; 3写 redo log 策略 innodb_flush_log_at_trx_commit 设置为 0 或 2
对于不需要强一致性的业务可以设置为 0 或 2。
0每隔 1 秒写日志文件和刷盘操作写日志文件 LogBuffer -- OS cache刷盘 OS cache -- 磁盘文件最多丢失 1 秒数据 1事务提交立刻写日志文件和刷盘数据不丢失但是会频繁 IO 操作 2事务提交立刻写日志文件每隔 1 秒钟进行刷盘操作 系统调优参数 back_log
back_log值可以指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说如果MySQL的连接数据达到max_connections时新来的请求将会被存在堆栈中以等待某一连接释放资源该堆栈的数量即back_log如果等待连接的数量超过back_log将不被授予连接资源。可以从默认的50升至500。
wait_timeout
数据库连接闲置时间闲置连接会占用内存资源。可以从默认的8小时减到半小时。
max_user_connection
最大连接数默认为0无上限最好设一个合理上限。
thread_concurrency
并发线程数设为CPU核数的两倍。
skip_name_resolve
禁止对外部连接进行DNS解析消除DNS解析时间但需要所有远程主机用IP访问。
key_buffer_size
索引块的缓存大小增加会提升索引处理速度对MyISAM表性能影响最大。对于内存4G左右可设为256M或384M通过查询show status like key_read%保证key_reads / key_read_requests在0.1%以下最好。
innodb_buffer_pool_size
缓存数据块和索引块对InnoDB表性能影响最大。通过查询show status like Innodb_buffer_pool_read%保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好。
innodb_additional_mem_pool_size
InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小当数据库对象非常多的时候适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率当过小的时候MySQL会记录Warning信息到数据库的错误日志中这时就需要该调整这个参数大小。
innodb_log_buffer_size
InnoDB存储引擎的事务日志所使用的缓冲区一般来说不建议超过32MB。
query_cache_size
缓存MySQL中的ResultSet也就是一条SQL语句执行的结果集所以仅仅只能针对select语句。当某个表的数据有任何变化都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以当我们数据变化非常频繁的情况下使用Query Cache可能得不偿失。根据命中率(Qcache_hits/(Qcache_hitsQcache_inserts)*100))进行调整一般不建议太大256MB可能已经差不多了大型的配置型静态数据可适当调大。可以通过命令show status like Qcache_%查看目前系统Query catch使用大小。
read_buffer_size
MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区MySQL会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁可以通过增加该变量值以及内存缓冲区大小来提高其性能。
sort_buffer_size
MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能可以尝试增加sort_buffer_size变量的大小。
read_rnd_buffer_size
MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如按照排序顺序)将分配一个随机读缓存区。进行排序查询时MySQL会首先扫描一遍该缓冲以避免磁盘搜索提高查询速度如果需要排序大量数据可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间所以应尽量适当设置该值以避免内存开销过大。
record_buffer
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描可能想要增加该值。
thread_cache_size
保存当前没有与连接关联但是准备为后面新的连接服务的线程可以快速响应连接的线程请求而无需创建新的。
table_cache
类似于thread_cache _size但用来缓存表文件对InnoDB效果不大主要用于MyISAM。
三、表结构设计 设计聚合表 设计聚合表一般针对于统计分析功能或者实时性不高的需求报表统计数据分析等系统这是一种空间 时延性换时间的思想。
设计冗余字段 为减少关联查询创建合理的冗余字段创建冗余字段还需要注意数据一致性问题当然如果冗余字段过多对系统复杂度和插入性能会有影响。
分表 分表分为垂直拆分和水平拆分两种。
垂直拆分适用于字段太多的大表比如一个表有100多个字段那么可以把表中经常不被使用的字段或者存储数据比较多的字段拆出来。
水平拆分比如一个表有5千万数据那按照一定策略拆分成十个表每个表有500万数据。这种方式除了可以解决查询性能问题也可以解决数据写操作的热点征用问题。
字段的设计 数据库中的表越小在它上面执行的查询也就会越快。因此在创建表的时候为了获得更好的性能我们可以将表中字段的宽度设得尽可能小。
使用可以存下数据最小的数据类型合适即可 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT如果非负则加上UNSIGNED VARCHAR的长度只分配真正需要的空间 对于某些文本字段比如省份或者性别使用枚举或整数代替字符串类型在MySQL中 ENUM类型被当作数值型数据来处理而数值型数据被处理起来的速度要比文本类型快得多 尽量使用TIMESTAMP而非DATETIME 单表不要有太多字段建议在20以内 尽可能使用 not null 定义字段null 占用4字节空间这样在将来执行查询的时候数据库不用去比较NULL值。 用整型来存IP。 尽量少用 text 类型非用不可时最好考虑拆表。 四、SQL语句及索引 如果发现SQL查询比较慢可以开启慢查询日志进行排查。
# 开启全局慢查询日志 SET global slow_query_log ON; # 设置慢查询日志文件名 SET global slow_query_log_file slow-query.log; # 记录未使用索引的SQL SET global log_queries_not_using_indexes ON; # 慢查询的时间阈值默认10秒 SET long_query_time 10; 注索引并不是越多越好要根据查询有针对性的创建。
索引创建和使用原则 单表查询哪个列作查询条件就在该列创建索引 多表查询left join 时索引添加到右表关联字段right join 时索引添加到左表关联字段 不要对索引列进行任何操作计算、函数、类型转换 索引列中不要使用 ! 非等于 字符字段只建前缀索引最好不要做主键 尽量不用UNIQUE由程序保证约束 不用外键由程序保证约束 索引列不要为空且不要使用 is null 或 is not null 判断 索引字段是字符串类型查询条件的值要加单引号避免底层类型自动转换 使用 EXPLAIN 分析 SQL 这里对explain的结果进行简单说明
select_type查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 UNION union后续查询 SUBQUERY 子查询 type查询数据时采用的方式 ALL 全表性能最差 index 基于索引的全表 range 范围 in ref 非唯一索引单值查询 const 使用主键或者唯一索引等值查询 possible_keys可能用到的索引 key真正用到的索引 rows预估扫描多少行记录 key_len使用了索引的字节数 Extra额外信息 Using where 索引回表 Using index 索引直接满足条件 Using filesort 需要排序 Using temprorary 使用到临时表 对于以上的几个列我们重点关注的是type最直观的反映出SQL的性能。
SQL语句尽可能简单 一条sql只能在一个cpu运算大语句拆小语句减少锁时间一条大sql可以堵死整个库。
对于连续数值使用 BETWEEN 不用 IN SELECT id FROM t WHERE num BETWEEN 1 AND 5
SQL 语句中 IN 包含的值不应过多 MySQL对于IN做了相应的优化即将IN中的常量全部存储在一个数组里面而且这个数组是排好序的。如果数值较多需要在内存进行排序操作产生的消耗也是比较大的。
SELECT 语句必须指明字段名称 SELECT * 增加很多不必要的消耗CPU、IO、内存、网络带宽减少了使用覆盖索引的可能性。
当只需要一条数据的时候使用 limit 1 limit 相当于截断查询。
例如对于select * from user limit 1; 虽然进行了全表扫描但是limit截断了全表扫描从0开始取了1条数据。
排序字段加索引 排序的字段建立索引在排序的时候也会用到
如果限制条件中其他字段没有索引尽量少用or 尽量用 union all 代替 union union和union all的差别就在于union会对数据做一个distinct的动作而这个distanct动作的速度则取决于现有数据的数量数量越大则时间也越慢。而对于几个数据集要确保数据集之间的数据互相不重复基本是O(n)的算法复杂度。
区分 in 和 exists、not in 和 not exists 如果是exists那么以外层表为驱动表先被访问如果是IN那么先执行子查询。所以IN适合于外表大而内表小的情况EXISTS适合于外表小而内表大的情况。
使用合理的分页方式以提高分页的效率 limit m n其中的m偏移量尽量小。m越大查询越慢。
避免使用 % 前缀模糊查询 例如like %name或者like %name%这种查询会导致索引失效而进行全表扫描。但是可以使用like name%这种会使用到索引。
避免在 where 子句中对字段进行表达式操作 这种不会使用到索引
select user_id,user_project from user_base where age*236; 可以改为
select user_id,user_project from user_base where age36/2; 任何对列的操作都将导致表扫描它包括数据库函数、计算表达式等等查询时要尽可能将操作移至等号右边。
避免隐式类型转换 where 子句中出现的 column 字段要和数据库中的字段类型对应
必要时可以使用 force index 来强制查询走某个索引 有的时候 MySQL 优化器采取它认为合适的索引来检索 SQL 语句但是可能它所采用的索引并不是我们想要的。这时就可以采用 forceindex 来强制优化器使用我们制定的索引。
使用联合索引时注意范围查询 对于联合索引来说如果存在范围查询比如between、、等条件时会造成后面的索引字段失效。
某些情况下可以使用连接代替子查询 因为使用 joinMySQL 不会在内存中创建临时表。
使用JOIN的优化 使用小表驱动大表例如使用inner join时优化器会选择小表作为驱动表
小表驱动大表即小的数据集驱动大的数据集 如以 AB 两表为例两表通过 id 字段进行关联。
#当 B 表的数据集小于 A 表时用 in 优化 exist使用 in 两表执行顺序是先查 B 表再查 A 表 select * from A where id in (select id from B) #当 A 表的数据集小于 B 表时用 exist 优化 in使用 exists两表执行顺序是先查 A 表再查 B 表 select * from A where exists (select 1 from B where B.id A.id)
上面都是一些常规的优化方法我们还可以使用主从和分库。
主从 主从相对比较简单从运维层面搭建好从库后工程师要做的就是制定路由策略。
路由策略有如下两种
读写分离模式所有写操作和对实时性要求较高的by id查询走主库剩下的都走从库从库采用Round Robin模式。
链路隔离模式写操作和核心操作对应的SQL走主库耗时大、非核心操作的SQL走从库。
分库 分库策略需要根据业务场景制定最常见的有两种按照年月分库和按照角色分库。
按照角色分库最经典的就是淘宝基于订单的买家库和卖家库。 ———————————————— 版权声明本文为CSDN博主「摆烂的少年」的原创文章遵循CC 4.0 BY-SA版权协议转载请附上原文出处链接及本声明。 原文链接https://blog.csdn.net/weixin_57242877/article/details/134850733
