数据库优化

数据库优化

MYSQL数据库优化

SQL和索引优化

优化思路

1. 开启慢查询日志，设置超过几秒为慢SQL，抓取慢SQL
2. 通过explain对慢SQL分析（重点）
3. show profile查询SQL在Mysql服务器里的执行细节和生命周期情况（重点）
4. 对数据库服务器的参数调优

慢SQL查询日志

设置慢SQL查询

（1）设置开启：SET GLOBAL slow_query_log = 1;　　　#默认未开启，开启会影响性能，mysql重启会失效
（2）查看是否开启：SHOW VARIABLES LIKE '%slow_query_log%';
（3）设置阈值：SET GLOBAL long_query_time=3;
（4）查看阈值：SHOW 【GLOBAL】 VARIABLES LIKE 'long_query_time%';　　#重连或新开一个会话才能看到修改值
（5）通过修改配置文件my.cnf永久生效，在[mysqld]下配置：
　　[mysqld]
　　slow_query_log = 1;　　#开启
　　slow_query_log_file=/var/lib/mysql/atguigu-slow.log　　　#慢日志地址，缺省文件名host_name-slow.log
　　long_query_time=3;　　  #运行时间超过该值的SQL会被记录，默认值>10
　　log_output=FILE

获取慢SQL信息

查看慢查询日志记录数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';
模拟语句：select sleep(4);
查看日志：cat atguigu-slow.log

日志分析工具mysqldumpslow
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log     #得到返回记录集最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log     #得到访问次数最多的10个SQL
mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/atguigu-slow.log   #得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more      #结合| more使用，防止爆屏情况
s：表示按何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：返回前面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感

explain分析慢SQL

Explain是Mysql的自带查询优化器，负责select语句的优化器模块，可以模拟优化器执行SQL查询语句，从而知道Mysql是如何处理SQL的
1、Id:id相同，执行顺序是由上至下的;
id不同，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行;
id存在相同的，也存在不同的，所有组中，id越大越先执行，如果id相同的，从上往下顺序执行
2、select_type：反映的是Mysql理解的查询类型
3、table：反映这一行数据是关于哪张表的
4、type：访问类型排序。查询效率：system > const > eq_ref > ref > range > index > all
5、possible_keys、key、key_len：反映实际用到了哪个索引，索引是否失效
6、ref：反映哪些列或常量被用于查找索引列上的值
7、rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
8、Extra
（1）using filesort：mysql中无法利用索引完成的排序，这时会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。（order by的排序最好和所建索引的顺序和个数一致）
（2）using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by（要么不建索引，要么group by的顺序要和索引一致）
（3）using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率好。（没有同时出现using where，表明索引用来读取数据而非执行查找动作）
（4）using where：表明使用了where过滤
（5）using join buffer：使用了连接缓存
（6）impossible where：where子句的值是false
（7）select tables optimized away
（8）distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

Show Profile分析慢SQL

Show Profile也是分析慢SQL的一种手段，但它能获得比explain更详细的信息，能分析当前会话中语句执行的资源消耗情况，能获得这条SQL在整个生命周期的耗时，相当于执行时间的清单，也很重要。
1、默认关闭。开启后，会在后台保存最近15次的运行结果，然后通过Show Profile命令查看结果。
开启：set profiling = on;
查看：SHOW VARIABLES LIKE 'profiling%';
2、通过Show Profile能查看SQL的耗时
3、通过Query_ID可以得到具体SQL从连接 - 服务 - 引擎 - 存储四层结构完整生命周期的耗时
**可用参数type:**
ALL  　　#显示所有的开销信息
BLOCK IO　　#显示块IO相关开销
CONTEXT SWITCHES　　#上下文切换相关开销
CPU     #显示CPU相关开销信息
IPC     #显示发送和接收相关开销信息
MEMORY　#显示内存相关开销信息
PAGE FAULTS　　#显示页面错误相关开销信息
SOURCE　　#显示和Source_function，Source_file，Source_line相关的开销信息
SWAPS　　#显示交换次数相关开销的信息

全局查询日志

只在测试环境用，别在生产环境用，会记录所有使用过的SQL
1、开启：会将sql记录到mysql库的general_log表
    set global general_log=1;
    set global log_output='TABLE';
配置文件的方式：
    在my.cnf中配置
    general_log=1    #开启
    general_log_file=/path/logfile    #记录日志文件的路径
    log_output=FILE    #输出格式
2、查看    select * from mysql.general_log;

数据库表结构优化

1、选择合适的数据类型
 - 使用可以存下你的数据的最小的数据类型
 - 使用简单的数据类型。Int要比varchar类型在mysql处理上简单
 - 尽可能的使用not null定义字段
 - 尽量少用text类型，非用不可时最好考虑分表
2、表的范式化和反范式化
一定要结合查询效率和处理效率来进行建表方式选择，查询多的要结合实际情况以空间来换取时间的操作
3、表的垂直拆分
 - 把不常用的字段单独存放到一个表中
 - 把大字段独立存放到一个表中
 - 把经常一起使用的字段放到一起
4、水平拆分
 - 对业务主键进行hash运算
 - 针对不同的hashID把数据存到不同的表中

系统配置优化

详细见参数列表
https://www.cnblogs.com/linyouyi/p/10530175.html

SQL编写规范

 1. 坚持小表驱动大表的原则； 
 2. 使用索引遵循最佳左前缀法则；
 3. 索引列上尽量不做计算、函数、自动或手动类型转换，避免索引失效；
 4. 尽量使用覆盖索引，即索引列和查询列一致；
 5. like尽量不以通配符开头（‘SS%’索引可用，‘%SS’索引不可用）；
 6. 少用OR连接；
 7. in与exists使用场合，子查询数据量小用in，子查询数据量大用exists;