数据库优化

说明：本文是

目录

目录

SQL优化

数据库准备

数据库文件地址http://dev.mysql.com/doc/index-other.html

如何查找慢sql

  1. 设置慢查询日志
  • 开启慢查询日志：set global slow_query_log=on;
  • 设置慢查询日志位置：set global slow_query_log_file=' /usr/local/mysql/data/bogon-slow.log ';
  • 开启记录没有使用索引的语句：set global log_queries_not_using_indexes=on;
  • 设置慢查询语句的时间：set global long_query_time = 0;
  1. 查看变量值
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'slow_query_log_file';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/bogon-slow.log |
+---------------------+--------------------------------------+
1 row in set (0.00 sec)
  1. 查看慢查询日志
记录
sudo more /usr/local/mysql/data/bogon-slow.log
# Time: 2020-05-17T04:20:59.584979Z
# User@Host: root[root] @ localhost []  Id:     7
# Query_time: 0.000645  Lock_time: 0.000103 Rows_sent: 1  Rows_examined: 501
SET timestamp=1589689259;
show variables like 'long_query_time';
说明
  • User@Host: root[root] @ localhost []：查询的用户和主机
  • Query_time: 0.000645 Lock_time: 0.000103：查询的时间和锁时间
  • SET timestamp=1589689259：也是sql执行的时间
  • show variables like 'long_query_time'：查询语句

如何分析慢sql

慢查询日志分析工具

mysqldumpslow

可以汇总归类慢查询的语句，便于分析

记录
sudo mysqldumpslow -t 3 /usr/local/mysql/data/bogon-slow.log | more
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  Time: N-N-17T04:N:N.480445Z
  # User@Host: root[root] @ localhost []  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select N from actor
说明
  • Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0)：总条数，扫描行数
  • SET timestamp=N：执行时间
  • select N from actor：查询时间

pt-query-digest

安装

https://blog.csdn.net/wireless_com/article/details/51615627

记录
fangchengdeMacBook-Pro:~ fangcheng$ sudo pt-query-digest /usr/local/mysql/data/bogon-slow.log

# 130ms user time, 10ms system time, 27.36M rss, 4.11G vsz
# Current date: Mon May 18 08:40:59 2020
# Hostname: fangchengdeMacBook-Pro.local
# Files: /usr/local/mysql/data/bogon-slow.log
# Overall: 12 total, 8 unique, 0 QPS, 0x concurrency _____________________
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time            8ms    45us     2ms   634us     2ms   600us   467us
# Lock time          643us       0   142us    53us   113us    50us    60us
# Rows sent            262       0     200   21.83   22.53   52.36    0.99
# Rows examine       1.23k       0     501     105  487.09  179.82    5.75
# Query size           250      11      41   20.83   36.69   10.42   16.81

explain

记录
mysql> explain select * from nicer_but_slower_film_list;
+----+-------------+---------------+------------+--------+-----------------------------------+---------------------------+---------+------------------------------+------+----------+---------------------------------+
| id | select_type | table         | partitions | type   | possible_keys                     | key                       | key_len | ref                          | rows | filtered | Extra                           |
+----+-------------+---------------+------------+--------+-----------------------------------+---------------------------+---------+------------------------------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2>    | NULL       | ALL    | NULL                              | NULL                      | NULL    | NULL                         | 5478 |   100.00 | NULL                            |
|  2 | DERIVED     | category      | NULL       | ALL    | PRIMARY                           | NULL                      | NULL    | NULL                         |   16 |   100.00 | Using temporary; Using filesort |
|  2 | DERIVED     | film_category | NULL       | ref    | PRIMARY,fk_film_category_category | fk_film_category_category | 1       | sakila.category.category_id  |   62 |   100.00 | Using where; Using index        |
|  2 | DERIVED     | film          | NULL       | eq_ref | PRIMARY                           | PRIMARY                   | 2       | sakila.film_category.film_id |    1 |   100.00 | NULL                            |
|  2 | DERIVED     | film_actor    | NULL       | ref    | PRIMARY,idx_fk_film_id            | idx_fk_film_id            | 2       | sakila.film_category.film_id |    5 |   100.00 | Using index                     |
|  2 | DERIVED     | actor         | NULL       | eq_ref | PRIMARY                           | PRIMARY                   | 2       | sakila.film_actor.actor_id   |    1 |   100.00 | NULL                            |
+----+-------------+---------------+------------+--------+-----------------------------------+---------------------------+---------+------------------------------+------+----------+---------------------------------+
6 rows in set, 1 warning (0.00 sec)
说明
  • table：当前这一行的数据关于哪张表
  • type：const、eq_reg、ref、range、index和ALL 效果依次减少
  • possible_keys：可能应用到这张表的索引
  • key：实际用到的索引
  • key_len：使用索引的长度
  • ref：显示索引的哪一列被使用了
  • rows：扫描的行数
  1. 通过日志如何确定有问题的日志
  • 查询次数多且每次查询占用的时间长的sql：通常为pt-query-digest分析的前几个查询
  • IO大的SQL:通常为pt-query-digest分析的Rows examine大的
  • 未命中索引的sql：通常为pt-query-digest分析的Rows examine和Rows Send的对比
    explain

索引优化

建立索引的策略

  1. 在where从句、group by从句、order by从句、on从句中出现的列
  2. 索引字段长度越小越好
  3. 离散度大的列放在联合索引的前面
    比如年龄的离散度要好于性别的离散度

索引的维护及优化--重复及冗余索引

说明

重复索引是指相同的列建立相同类型的索引

工具

pt-duplicate-key-checker

记录
fangchengdeMacBook-Pro:~ fangcheng$ pt-duplicate-key-checker -uroot -proot -h 127.0.0.1 --database=sakila --table=film_category
# ########################################################################
# sakila.film_category                                                    
# ########################################################################

# fk_film_category_category_1 is a duplicate of fk_film_category_category
# Key definitions:
#   KEY `fk_film_category_category_1` (`category_id`) USING BTREE,
#   KEY `fk_film_category_category` (`category_id`) USING BTREE,
# Column types:
#	  `category_id` tinyint(3) unsigned not null
# To remove this duplicate index, execute:
ALTER TABLE `sakila`.`film_category` DROP INDEX `fk_film_category_category_1`;

# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Size Duplicate Indexes   1000
# Total Duplicate Indexes  1
# Total Indexes            5

数据库结构优化

数据库字段类型选取策略

  1. 使用满足业务的最小数据类型
  2. 使用简单的数据类型，int比varchar在mysql处理上简单
  3. 尽可能使用not null定义字段
  4. 尽量少使用text
posted @ 2020-05-18 09:59  蒙多~想去哪就去哪  阅读(94)  评论(0)    收藏  举报