MySQL 学习笔记数据库优化

最新推荐文章于 2024-07-09 11:09:43 发布

d224955

最新推荐文章于 2024-07-09 11:09:43 发布

阅读量128

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/d224955/article/details/104017664

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL 学习笔记数据库优化

一、数据准备
二、数据库查询优化
三、数据库结构优化
四、数据库服务器优化
- 1、服务器硬件优化
- 2、服务器参数优化

一、数据准备

mysql> create table fruits
    -> (
    -> f_id int not null primary key auto_increment,
    -> s_id char(10) not null,
    -> f_name char(255) not null,
    -> f_price decimal(8,2) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> DESC fruits;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| f_id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| s_id    | char(10)     | NO   |     | NULL    |                |
| f_name  | char(255)    | NO   |     | NULL    |                |
| f_price | decimal(8,2) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into fruits (s_id,f_name,f_price)
    -> values('a1','apple',5.2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into fruits (s_id,f_name,f_price)
    -> values ('b1','blackberry',10.2),
    -> ('bs2','melon',8.2),
    -> ('t1','banana',10.3),
    -> ('t2','grape',5.3),
    -> ('o2','apricot',2.2);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM fruits;
+------+------+------------+---------+
| f_id | s_id | f_name     | f_price |
+------+------+------------+---------+
|    1 | a1   | apple      |    5.20 |
|    2 | b1   | blackberry |   10.20 |
|    3 | bs2  | melon      |    8.20 |
|    4 | t1   | banana     |   10.30 |
|    5 | t2   | grape      |    5.30 |
|    6 | o2   | apricot    |    2.20 |
+------+------+------------+---------+
6 rows in set (0.00 sec)

二、数据库查询优化

1、查询语句分析

mysql> EXPLAIN SELECT * FROM fruits\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select 
`fruit`.`fruits`.`f_id` AS `f_id`,
`fruit`.`fruits`.`s_id` AS `s_id`,
`fruit`.`fruits`.`f_name` AS `f_name`,
`fruit`.`fruits`.`f_price` AS `f_price` 
from `fruit`.`fruits`
1 row in set (0.00 sec)

优化字段描述	字段值	字段解释
id	1	SELECT 识别符
select_type	SIMPLE	表示SELECT的类型
table	fruits	表示查询的表
partitions	NULL	查询可能的分区
type	ALL	表示表的连接类型
possible_keys	NULL	表示能使用哪个索引找到行
key	NULL	表示查询实际使用的索引
key_len	NULL	表示选择的索引字段按字节计算的长度
ref	NULL	表示使用哪个列或常数与索引一起来查询记录
rows	6	在查询时检查的行数
filtered	100.00	返回结果的行占需要读到的行(rows列的值)的百分比
Extra	NULL	处理查询时的详细信息

2、查询语句优化(索引)

mysql> EXPLAIN SELECT * FROM fruits WHERE f_id = 3\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

3、查询语句子查询优化

子查询操作会创建中间表，如果使用连接（join)查询效率会更高。
以下仅为举例。

mysql> explain select * from fruits where f_id = (select f_id from fruits where f_price < 5)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: fruits
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: fruits
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 33.33
        Extra: Using where
2 rows in set, 1 warning (0.00 sec)

mysql> explain select * from fruits a left join fruits b on a.f_id = b.f_id where b.f_price < 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 33.33
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: fruit.b.f_id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

三、数据库结构优化

1、分解表

如果表中有一个不常查询的表字段:产地 f_location varchar(100)
则可以将该字段从表中分离，使用两张表：
create table fruits_location
(
location_sid char(10) not null,
f_location varchar(100) not null,
primary key(location_id)
);

2、增加中间表

对于经常要使用的连接查询，且数据变化不频繁的表可以设置中间表来优化连接查询。
create table temp_fruits
(
s_id char(10) not null,
f_name char(255) not null,
f_price decimal(8,2) not null,
f_location varchar(100) not null,
primary key(s_id)
);

insert into temp_fruits (s_id,f_name,f_price,f_location)
values select s_id,f_name,f_price,f_location from fruits fruits_location
where s_id = location_sid;

3、增加冗余字段

合理的增加冗余字段可以提高查询速度，根据业务具体要求进行添加。

4、分析表、检查表、优化表

mysql> ANALYZE TABLE fruits;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| fruit.fruits | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> CHECK TABLE fruits;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| fruit.fruits | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)

对于InnoDB表， OPTIMIZE TABLE映射到 ALTER TABLE … FORCE，从而重建表以更新索引统计信息并释放聚集索引中的未使用空间。OPTIMIZE TABLE在InnoDB表上运行它时，它会显示在输出中，如下所示：

mysql> OPTIMIZE TABLE fruits;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                          |
+--------------+----------+----------+-------------------------------------------------------------------+
| fruit.fruits | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| fruit.fruits | optimize | status   | OK                                                                |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.06 sec)

四、数据库服务器优化

1、服务器硬件优化

硬盘
内存
CPU

2、服务器参数优化

配置参数	默认值	说明
back_log	默认-1，但不能被赋值-1	MySQL可以拥有的未完成连接请求数
innodb_buffer_poll_size	134217728	用于InnoDB缓存表和索引数据的内存区域的大小
innodb_flush_log_at_trx_commit	1	控制刷新日志到磁盘的时机
interactive_timeout	28800	服务器在关闭交互式连接之前等待活动的秒数
key_buffer_size	8388608	索引块的缓冲区的大小
max_connections	151	表示数据库的最大连接数
query_cache_size	1048576	分配用于缓存查询结果的内存(8.0弃用)
read_buffer_size	131072	线程扫描时为每个表分配的缓冲区的大小(Byte)
read_rnd_buffer_size	262144	表示为每个线程保留的缓冲区大小
sort_buffer_size	32768	表示排序缓存区的大小
sort_buffer_size
~~table_cache~~		~~表示同时打开的表的个数~~
thread_cache_size	默认-1，但不能赋值为-1	表示可以复用的线程数量
wait_timeout	28800	表示服务器在关闭一个连接时等待行动的秒数