数据库复习之数据库优化方法-第六天(2019/6/1)

数据库优化的几个方面:sql语句和索引、数据结构、系统配置、硬件
数据库复习之数据库优化方法-第六天(2019/6/1)

  • SQL以及索引的优化是最重要的。首先要根据需求写出结构良好的SQL,然后根据SQL在表中建立有效的索引。但是如果索引太多,不但会影响写入的效率,对查询也有一定的影响。
  • 要根据一些范式来进行表结构的设计。设计表结构时,就需要考虑如何设计才能够更有效的查询。
  • 系统配置的优化。MySQL数据库是基于文件的,如果打开的文件数达到一定的数量,无法打开之后就会进行频繁的IO操作。
  • 硬件优化。更快的IO、更多的内存。一般来说内存越大,对于数据库的操作越好。但是CPU多就不一定了,因为他并不会用到太多的CPU数量,有很多的查询都是单CPU。另外使用高的IO(SSD、RAID),但是IO并不能减少数据库锁的机制。所以说如果查询缓慢是因为数据库内部的一些锁引起的,那么硬件优化就没有什么意义。

1.sql语句优化

1、通过慢查询日志发现有效率问题的SQL
可以通过开启慢查询日志的方式进行定位有问题的SQL
(1)查看MySQL是否开启慢查询日志
show variables like ‘slow_query_log’;
数据库复习之数据库优化方法-第六天(2019/6/1)
(2)设置没有索引的记录到慢查询日志 set global log_queries_not_using_indexes=on;(3)查看超过多长时间的sql进行记录到慢查询日志 show variables like ‘long_query_time’(4)开启慢查询日志 set global slow_query_log=on(5)设置超时时间Set global long_query_time=5;–超过5s的语句才记录日志 (6)查看慢查询日志的位置 show variables like ‘slow%’。
2、慢查询日志内容分析慢查询日志主要分为5部分,第一部分是慢查询时间,第二部分是慢查询的来源主机和用户,第三部分是查询的执行时间、锁定时间、发送的行数、扫描的行数。最后是时间戳形式记录的命令以及该命令的执行的时间戳。 系统运行一段时间后,慢查询日志可能比较多,需要通过mysqldumpslow、pt-query-digest等工具分析慢查询日志。
3、通过explain查看SQL的执行计划
具体参考:https://www.cnblogs.com/songwenjie/p/9409852.html

2.索引优化

1、选择索引
(1)选择合适的索引列,选择在where,group by,order by,on从句中出现的列作为索引项,对于离散度不大的列没有必要创建索引。
(2)索引字段越小越好(因为数据库的存储单位是页,一页中能存下的数据越多越好 )
(3)离散度大得列放在联合索引前面判断离散程度的方法是: select count(distinct ziduan1),count(distinct ziduan2) from tablename越大越离散
2、索引优化方法 索引一般情况下都是高效的。不过凡是都有两面性,索引是以空间换时间的一种策略,索引本身在提高查询效率的同时会影响插入、更新、删除的效率。不当的使用索引不仅增加了写操作的负担,也会影响读取的效率。索引越多,数据库分析的越慢。注意点:(1)InnoDB 每个索引都会加上主键,联合索引不要加上主键,innodb会自动加,否则会冗余。
(2)索引存在的目的是为了加快查询的效率,不过不是索引越多越好,建立索引要适当才好。过多的索引会增加数据库判断使用什么索引来查询的开销,所以,有时候也会出现以去掉重复或者无效的索引为优化手段的优化方式。
(3)主键已经是索引了,所以primay key 的主键不用再设置unique唯一索引了。
3、索引的原理可以参考https://blog.csdn.net/iefreer/article/details/15815455