【MySQL】数据库优化

曾璇

一、优化数据类型

在MySQL中不同的数据类型长度不同，在磁盘上所需要的存储空间也不同，如果数据库中使用不合理的数据类型，会造成很大的空间浪费，并且在数据插入与读取时，也会造成MySQL的性能低下。

• 更小的数据类型更好
  

如果没有特殊情况，尽量使用可以正确保存数据的最小数据类型，因为更小的数据类型在插入和读取数据时更快，占用的内存更小，CPU处理的周期也会更短。

• 使用简单的数据类型
  

在设计数据表时，尽量为字段设计简单的数据类型。例如能使用整型就不要使用字符串类型，因为字符串类型的比较规则更复杂，需要将字符串转化为ANSI码后再进行比较。

• 避免使用NULL
  

在没有特殊情况下，尽量将字段的类型限制为NOT NULL。软功字段允许为NULL，会使得索引、插入与更新数据变得复杂。因为在可以为NULL的列建立索引时，在使用索引时，每个索引记录都会使用一个额外的空间来记录索引列是否为NULL，并且在InnoDB存储引擎中，需要单独使用一个字节的存储空间来存储NULL值。在实际情况中可以设置默认值，例如为“”、0等。
二、删除重复索引和冗余索引

重复索引：索引名称不同，索引字段相同
冗余索引：索引最左边的部分列是重复的

1. mysql> show create table t_goods \G;
2. *************************** 1. row ***************************
3.        Table: t_goods
4. Create Table: CREATE TABLE `t_goods` (
5.   `id` int NOT NULL AUTO_INCREMENT,
6.   `t_category_id` int DEFAULT NULL,
7.   `t_category` varchar(30) DEFAULT NULL,
8.   `t_name` varchar(50) DEFAULT NULL,
9.   `t_price` decimal(10,2) DEFAULT NULL,
10.   `t_stock` int DEFAULT NULL,
11.   `t_upper_time` datetime DEFAULT NULL,
12.   PRIMARY KEY (`id`),
13.   KEY `index_category_name` (`t_category_id`,`t_name`),
14.   KEY `category_part` (`t_category`(10)),
15.   KEY `stock_index` (`t_stock`),
16.   KEY `t_upper_time_index` (`t_upper_time`),
17.   KEY `name_index` (`t_name`),
18.   KEY `category_name_index` (`t_category`,`t_name`),
19.   KEY `category_name_index2` (`t_category`,`t_name`),
20.   KEY `name_stock_index` (`t_name`,`t_stock`),
21.   KEY `category_name_index3` (`t_category` DESC,`t_name`),
22.   CONSTRAINT `foreign_category` FOREIGN KEY (`t_category_id`) REFERENCES `t_goods_category` (`id`)
23. ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
24. 1 row in set (0.01 sec)

复制代码

例如在这张数据表中，category_name_index和category_name_index2索引是重复索引。这两个索引的字段完全相同。name_index和name_stock_index索引是冗余索引，因为name_stock_index索引中包含了name_index索引的字段。为什么category_name_index3不是重复索引呢，因为category_name_index3索引的t_category字段的顺序不同。
三、反范式设计

数据库设计中三大范式要求尽可能减少冗余字段，使数据库设计看起来更简单、优雅。
但是完全的遵循数据库的三大范式来设计数据库，会导致很多表之间产生很多的依赖关系，规范越高，表之间的依赖关系越多这样会导致在查询数据时，数据表之间的频繁连接，造成数据查询的性能低下。
在实际情况下，对于查询较多的夏天来说，应根据实际业务对数据库进行反范式化设计，适当的增加冗余字段，提高数据的查询效率。
需要注意的是，在增加冗余字段时，需要考虑数据的一致性问题，也就是说，当数据表A中的某个字段发生变化时，对应数据表B中也应该将相应的数据修改。
四、增加中间表

如果数据库中存在经常需要关联查询的数据表，则可以为关联查询的数据表建立一个中间表，中间表中存储多个数据表关联查询的结果数据，将对多个数据表的关联查询转化为对中间表的查询，提高查询效率。
例如创建部门表和员工表

1. create table t_department(
2. id int not null primary key auto_increment,
3. name varchar(30) not null default ""
4. );
6. create table t_employee(
7. id int not null primary key auto_increment,
8. name varchar(30) not null default "",
9. join_data DATE,
10. bobby varchar(100),
11. department int not null
12. )；

复制代码

t_employee数据表通过department字段与t_department数据表之间进行关联。
使用联表查询

1. mysql> explain select e.name as employee_name,d.name as department_name from t_employee e left join t_department d on e.department=d.id \G;
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.         table: e
6.    partitions: NULL
7.          type: ALL
8. possible_keys: NULL
9.           key: NULL
10.       key_len: NULL
11.           ref: NULL
12.          rows: 1
13.      filtered: 100.00
14.         Extra: NULL
15. *************************** 2. row ***************************
16.            id: 1
17.   select_type: SIMPLE
18.         table: d
19.    partitions: NULL
20.          type: eq_ref
21. possible_keys: PRIMARY
22.           key: PRIMARY
23.       key_len: 4
24.           ref: goods.e.department
25.          rows: 1
26.      filtered: 100.00
27.         Extra: NULL
28. 2 rows in set, 1 warning (0.01 sec)

复制代码

创建中间表，存储连接查询的信息

1. create table t_employee_tmp(
2. employee_id int not null,
3. employee_name varchar(30),
4. department_name varchar(30)
5. );

复制代码

将联表查询信息导入中间表

1. insert into t_employee_tmp
2. (employee_id,employee_name,department_name)
3. select e.id as employee_id,e.name as employee_name,d.name as department_name
4. from t_employee as e left join t_department as d
5. on e.department =d.id;

复制代码

查询中间表中的数据集

1. mysql> explain select * from t_employee_tmp \G;
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.         table: t_employee_tmp
6.    partitions: NULL
7.          type: ALL
8. possible_keys: NULL
9.           key: NULL
10.       key_len: NULL
11.           ref: NULL
12.          rows: 1
13.      filtered: 100.00
14.         Extra: NULL
15. 1 row in set, 1 warning (0.00 sec)

复制代码

这时候只需要查询中间表的数据就可以了，不需要再进行联表查询，并且如果在中间表的查询中，适当添加索引，会更明显的提升效率。
五、分析数据表

当使用ANALYZE TAVLE来分析数据表时，MYSQL会自动为数据表添加一个只读的锁，此时，只能对数据表中的数据进行读取操作而不能进行写入和更新操作。

1. mysql> analyze table  t_goods;
2. +---------------+---------+----------+----------+
3. | Table         | Op      | Msg_type | Msg_text |
4. +---------------+---------+----------+----------+
5. | goods.t_goods | analyze | status   | OK       |
6. +---------------+---------+----------+----------+
7. 1 row in set (0.05 sec)
9. mysql> analyze table  t_goods \G;
10. *************************** 1. row ***************************
11.    Table: goods.t_goods
12.       Op: analyze
13. Msg_type: status
14. Msg_text: OK
15. 1 row in set (0.01 sec)

复制代码

Table当前分析的数据表的名称Op当前执行的操作Msg_type输出结果信息的类型，包括status(状态)、info(信息)、note(注意)、warning(警告)、erroe(错误)Msg_test结果信息六、检查数据表

当使用CHECK TABLE语句检查数据表时，MySQL会自动为数据表添加读锁。

1. check table t_goods\G；
2. +-------------+-------+----------+-----------------------------------+
3. | Table       | Op    | Msg_type | Msg_text                          |
4. +-------------+-------+----------+-----------------------------------+
5. | goods.goods | check | Error    | Table 'goods.goods' doesn't exist |
6. | goods.goods | check | status   | Operation failed                  |
7. +-------------+-------+----------+-----------------------------------+
8. 2 rows in set (0.02 sec)
10. mysql> check table t_goods\G
11. *************************** 1. row ***************************
12.    Table: goods.t_goods
13.       Op: check
14. Msg_type: status
15. Msg_text: OK
16. 1 row in set (0.01 sec)

复制代码

七、优化数据表

OPTIMIZE TABLE语句主要用来优化删除和更新数据造成的文件碎片。使用时，会自动添加读锁。

1. mysql> optimize table t_goods \G;
2. *************************** 1. row ***************************
3.    Table: goods.t_goods
4.       Op: optimize
5. Msg_type: note
6. Msg_text: Table does not support optimize, doing recreate + analyze instead
7. *************************** 2. row ***************************
8.    Table: goods.t_goods
9.       Op: optimize
10. Msg_type: status
11. Msg_text: OK
12. 2 rows in set (0.13 sec)

复制代码

注意，只能优化数据表中的Varchar、Blob或Text类型字段。
八、拆分数据表

如果一个表的字段数量比较多，某些字段的查询效率非常低。这样的字段在数据量非常大时，会严重影响数据表的性能，可以将这些字段分离出来形成新的表。
1、垂直拆分

1. mysql> show create table t_user \G;
2. *************************** 1. row ***************************
3.        Table: t_user
4. Create Table: CREATE TABLE `t_user` (
5.   `id` int NOT NULL AUTO_INCREMENT,
6.   `username` varchar(30) DEFAULT NULL,
7.   `password` varchar(64) DEFAULT NULL,
8.   `phone` varchar(14) DEFAULT NULL,
9.   `address` varchar(200) DEFAULT NULL,
10.   `hobby` varchar(200) DEFAULT NULL,
11.   PRIMARY KEY (`id`)
12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
13. 1 row in set (0.00 sec)

复制代码

从分析可以看到，其中最常使用的是username和pasword，其他字段数据查询的频率非常低，此时可以将表拆分为两个表t_user、t_user_detail。

1. mysql> show create table t_user_puls \G;
2. *************************** 1. row ***************************
3.        Table: t_user_puls
4. Create Table: CREATE TABLE `t_user_puls` (
5.   `id` int NOT NULL AUTO_INCREMENT,
6.   `username` varchar(30) DEFAULT NULL,
7.   `password` varchar(64) DEFAULT NULL,
8.   PRIMARY KEY (`id`)
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
10. 1 row in set (0.01 sec)
13. mysql> show create table t_user_detail \G;
14. *************************** 1. row ***************************
15.        Table: t_user_detail
16. Create Table: CREATE TABLE `t_user_detail` (
17.   `user_id` int NOT NULL,
18.   `phone` varchar(14) DEFAULT NULL,
19.   `address` varchar(200) DEFAULT NULL,
20.   `hobby` varchar(200) DEFAULT NULL
21. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
22. 1 row in set (0.00 sec)

复制代码

这里使用索引字段将两个表进行关联，如果只需要查询用户名和密码，就可以大大提高效率。
2、水平拆分

主要拆分的数据。例如将10行数据拆分为5行5行。主要用于增加数据库的存储容量。例如，根据一定的规则将数据表中的一部分数据存储到一张数据表中，另一部分存储到其他数据表中。

来源:https://www.cnblogs.com/changwan/p/18224629
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！