一、优化数据类型

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

• 更小的数据类型更好

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

• 使用简单的数据类型

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

• 避免使用null

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

二、删除重复索引和冗余索引

重复索引：索引名称不同，索引字段相同

冗余索引：索引最左边的部分列是重复的

mysql> show create table t_goods \g;
*************************** 1. row ***************************
       table: t_goods
create table: create table `t_goods` (
  `id` int not null auto_increment,
  `t_category_id` int default null,
  `t_category` varchar(30) default null,
  `t_name` varchar(50) default null,
  `t_price` decimal(10,2) default null,
  `t_stock` int default null,
  `t_upper_time` datetime default null,
  primary key (`id`),
  key `index_category_name` (`t_category_id`,`t_name`),
  key `category_part` (`t_category`(10)),
  key `stock_index` (`t_stock`),
  key `t_upper_time_index` (`t_upper_time`),
  key `name_index` (`t_name`),
  key `category_name_index` (`t_category`,`t_name`),
  key `category_name_index2` (`t_category`,`t_name`),
  key `name_stock_index` (`t_name`,`t_stock`),
  key `category_name_index3` (`t_category` desc,`t_name`),
  constraint `foreign_category` foreign key (`t_category_id`) references `t_goods_category` (`id`)
) engine=innodb auto_increment=36 default charset=utf8mb4 collate=utf8mb4_0900_ai_ci
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中也应该将相应的数据修改。

四、增加中间表

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

例如创建部门表和员工表

create table t_department(
id int not null primary key auto_increment,
name varchar(30) not null default ""
);

create table t_employee(
id int not null primary key auto_increment,
name varchar(30) not null default "",
join_data date,
bobby varchar(100),
department int not null
)；

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

使用联表查询

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;
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: e
   partitions: null
         type: all
possible_keys: null
          key: null
      key_len: null
          ref: null
         rows: 1
     filtered: 100.00
        extra: null
*************************** 2. row ***************************
           id: 1
  select_type: simple
        table: d
   partitions: null
         type: eq_ref
possible_keys: primary
          key: primary
      key_len: 4
          ref: goods.e.department
         rows: 1
     filtered: 100.00
        extra: null
2 rows in set, 1 warning (0.01 sec)

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

create table t_employee_tmp(
employee_id int not null,
employee_name varchar(30),
department_name varchar(30)
);

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

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

查询中间表中的数据集

mysql> explain select * from t_employee_tmp \g;
*************************** 1. row ***************************
           id: 1
  select_type: simple
        table: t_employee_tmp
   partitions: null
         type: all
possible_keys: null
          key: null
      key_len: null
          ref: null
         rows: 1
     filtered: 100.00
        extra: null
1 row in set, 1 warning (0.00 sec)

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

五、分析数据表

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

mysql> analyze table  t_goods;
+---------------+---------+----------+----------+
| table         | op      | msg_type | msg_text |
+---------------+---------+----------+----------+
| goods.t_goods | analyze | status   | ok       |
+---------------+---------+----------+----------+
1 row in set (0.05 sec)

mysql> analyze table  t_goods \g;
*************************** 1. row ***************************
   table: goods.t_goods
      op: analyze
msg_type: status
msg_text: ok
1 row in set (0.01 sec)

六、检查数据表

当使用check table语句检查数据表时，mysql会自动为数据表添加读锁。

 check table t_goods\g；
+-------------+-------+----------+-----------------------------------+
| table       | op    | msg_type | msg_text                          |
+-------------+-------+----------+-----------------------------------+
| goods.goods | check | error    | table 'goods.goods' doesn't exist |
| goods.goods | check | status   | operation failed                  |
+-------------+-------+----------+-----------------------------------+
2 rows in set (0.02 sec)

mysql> check table t_goods\g
*************************** 1. row ***************************
   table: goods.t_goods
      op: check
msg_type: status
msg_text: ok
1 row in set (0.01 sec)

七、优化数据表

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

mysql> optimize table t_goods \g;
*************************** 1. row ***************************
   table: goods.t_goods
      op: optimize
msg_type: note
msg_text: table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   table: goods.t_goods
      op: optimize
msg_type: status
msg_text: ok
2 rows in set (0.13 sec)

注意，只能优化数据表中的varchar、blob或text类型字段。

八、拆分数据表

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

1、垂直拆分

mysql> show create table t_user \g;
*************************** 1. row ***************************
       table: t_user
create table: create table `t_user` (
  `id` int not null auto_increment,
  `username` varchar(30) default null,
  `password` varchar(64) default null,
  `phone` varchar(14) default null,
  `address` varchar(200) default null,
  `hobby` varchar(200) default null,
  primary key (`id`)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

mysql> show create table t_user_puls \g;
*************************** 1. row ***************************
       table: t_user_puls
create table: create table `t_user_puls` (
  `id` int not null auto_increment,
  `username` varchar(30) default null,
  `password` varchar(64) default null,
  primary key (`id`)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)


mysql> show create table t_user_detail \g;
*************************** 1. row ***************************
       table: t_user_detail
create table: create table `t_user_detail` (
  `user_id` int not null,
  `phone` varchar(14) default null,
  `address` varchar(200) default null,
  `hobby` varchar(200) default null
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

2、水平拆分

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