为什么要进行数据库优化
- 避免网站页面出现访问错误(由于数据库连接timeout产生页面5XX错误;由于慢查询造成页面无法加载;由于阻塞造成数据无法提交)
- 增加数据库的稳定性(很多数据库问题都是由于低效的查询引起的)
- 优化用户体验(流畅页面的访问速度;良好的的网站功能体验)
MYSQL数据库优化
SQL及索引优化
根据需求写出良好的SQl,并创建有效的索引,实现某一种需求可以有多重写法,这时候我们就要选择一种效率最高的写法。这个时候就要了解sql优化
利用慢查询日志发现有问题的SQL
- 查看mysql的版本
select @@version
- 开启慢查询日志
show variables like 'slow_query_log';
//查看是否开启慢查询日志
show variables like %log%;
//找到慢查询日志的具体位置
set global log-queries not using indexes=on';
//开启慢查询日志
set global long query_time=1;
//大于1秒钟的数据记录到慢日志中,如果设置为默认0,则会有大量的信息存储在磁盘中,磁盘很容易满掉
3. 如何通过慢查询日志发现有问题的SQL
《1》查询次数多且每次查询占用时间长的sql(通过慢查询日志可以清楚的查看每个SQL执行的次数及百分比等信息,执行的次数多,占用比较大的SQL)
《2》IO大的sql(扫描的行数越多,IO越大)
《3》未命中的索引的SQL(SQL的索引命中率不高的话,对于这种SQL语句 我们需要重点关注)
通过expain查询分析SQL的执行计划
- 查看执行计划(在要执行的SQL语句前加上expain即可)
explain select count(*) from mytable
每个字段的说明:
《1》id:数字越大越先执行,如果说数字一样大,那么久从上往下依次执行,id列为null就表示这是一个结果集,不需要使用它来进行查询
《2》select_type:常见的有simple(表示不需要union操作或者不包含子查询的简单select查询。有连接查询时,外层的查询为simple,且只有一个);primary(表示一个需要union操作或者含有子查询的select,位于最外层的单位查询的select type即为primary。且只有一个);union(union连接的两个select查询,第一个查询是dervied派生表,除了第一个表外,第二个以后的表select type都是union);dependent union(与union一样,出现在union或union all语句中,但是这个查询要受到外部查询的影响);union result(包含union的结果集,在union和union all语句中,因为它不需要参与查询);subquery(除了from子句中包含的子查询外,其他地方出现的子查询都可能是subquery);dependent subquery:与dependent union类似,表示subquey的查询要受到外部查询的影响;derived from子句中出现的子查询,也叫做派生表,其他数据库可能叫做内联视图或嵌套select
《3》table:显示查询表名,如果查询使用了别名,那么这里显示的是别名,如果不涉及对数据表的操作,那么这显示为null。如果显示为尖括号括起来就表示这个是临时表,后边的N就是执行计划的id,表示结果来自于这个查询产生,如果是尖括号括起来的与类似,也是一个临时表,表示这个结果来自于union 查询的id为MN的结果集
《4》type:连接使用了何种类型依次从好到坏:system(一行数据或者空表),const(使用唯一索引或者主键),eq_ref(返回一行数据且第二个表的主键或者唯一索引),ref(使用相等的条件检索时就可能出现),fulltext(全文索引检索),ref_or_null(与ref方法类似),unique_subquery(用于where中的in形式子查询,子查询返回不重复唯一值),index_subquery(用于in形式子查询用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重),range(索引范围扫描),index_merge(查询使用了亮两个以上的索引),index(索引全表扫描),all(全表扫描),除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引。
《5》possible key:查询可能会使用到的索引
《6》key:查询真正用到的索引
《7》key len:处理查询的索引长度
《8》ref:显示索引的那一列被使用了,等值查询(const),连接查询会显示关联字段
《9》rows:执行计划中估算的扫描行数,不是精确值
《10》extra:
子查询优化
子查询是我们在开发过程中经常使用的一种方式,在通常情况下,需要把子查询优化为join查询但在优化是需要注意关联键是否有一对多的关系,要注意重复数据。
数据库表结构的优化
根据数据库的范式,设计表结构,表结构设计的好直接关系到写SQL语句
(适当的冗余,大量表生成会影响效率)
系统配置优化
大多数运行在LINUX机器上,如TCP连接数的限制,打开文件数的限制,安全性的限制,因此我们要对这些配置进行相应的优化。
硬件配置优化
选择适合数据库服务的cpu,更快的io,更高的内存;cpu并不是越多越好,某些数据库版本有最大的限制,io操作并不是减少阻塞。