mysql 按日期拆分成多条记录_最全的MySQL优化面试题

为什么要优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上

随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢

数据是存放在磁盘上的，读写速度无法和内存相比

如何优化

• 设计数据库时：数据库表、字段的设计，存储引擎
• 利用好MySQL自身提供的功能，如索引等
• 横向扩展：MySQL集群、负载均衡、读写分离
• SQL语句的优化(收效甚微)

字段设计

字段类型的选择，设计规范，范式，常见设计案例

原则：尽量使用整型表示字符串

存储IP

INET_ATON(str)，address to number

INET_NTOA(number)，number to address

MySQL内部的枚举类型(单选)和集合(多选)类型

但是因为维护成本较高因此不常使用，使用关联表的方式来替代enum

原则：定长和非定长数据类型的选择

decimal不会损失精度，存储空间会随数据的增大而增大。double占用固定空间，较大数的存储会损失精度。非定长的还有varchar、text

金额

对数据的精度要求较高，小数的运算和存储存在精度问题(不能将所有小数转换成二进制)

定点数decimal

price decimal(8,2)有2位小数的定点数，定点数支持很大的数(甚至是超过int,bigint存储范围的数)

小单位大数额避免出现小数

元->分

字符串存储

定长char，非定长varchar、text(上限65535，其中varchar还会消耗1-3字节记录长度，而text使用额外空间记录长度)

原则：尽可能选择小的数据类型和指定短的长度

原则：尽可能使用 not null

非null字段的处理要比null字段的处理高效些！且不需要判断是否为null。

null在MySQL中，不好处理，存储需要额外空间，运算也需要特殊的运算符。如select null = null和select null <> null(<>为不等号)有着同样的结果，只能通过is null和is not null来判断字段是否为null。

如何存储？MySQL中每条记录都需要额外的存储空间，表示每个字段是否为null。因此通常使用特殊的数据进行占位，比如int not null default 0、string not null default ‘’

原则：字段注释要完整，见名知意

原则：单表字段不宜过多

二三十个就极限了

原则：可以预留字段

在使用以上原则之前首先要满足业务需求

关联表的设计

外键foreign key只能实现一对一或一对多的映射

一对多

使用外键

多对多

单独新建一张表将多对多拆分成两个一对多

一对一

如商品的基本信息(item)和商品的详细信息(item_intro)，通常使用相同的主键或者增加一个外键字段(item_id)

范式 Normal Format

数据表的设计规范，一套越来越严格的规范体系(如果需要满足N范式，首先要满足N-1范式)。N

第一范式1NF：字段原子性

字段原子性，字段不可再分割。

关系型数据库，默认满足第一范式

注意比较容易出错的一点，在一对多的设计中使用逗号分隔多个外键，这种方法虽然存储方便，但不利于维护和索引(比如查找带标签java的文章)

第二范式：消除对主键的部分依赖

即在表中加上一个与业务逻辑无关的字段作为主键

主键：可以唯一标识记录的字段或者字段集合。

course_name course_class weekday(周几) course_teacher MySQL 教育大楼1525 周一 张三 Java 教育大楼1521 周三 李四 MySQL 教育大楼1521 周五 张三 依赖：A字段可以确定B字段，则B字段依赖A字段。比如知道了下一节课是数学课，就能确定任课老师是谁。于是周几和下一节课和就能构成复合主键，能够确定去哪个教室上课，任课老师是谁等。但我们常常增加一个id作为主键，而消除对主键的部分依赖。

对主键的部分依赖：某个字段依赖复合主键中的一部分。

解决方案：新增一个独立字段作为主键。

第三范式：消除对主键的传递依赖

传递依赖：B字段依赖于A，C字段又依赖于B。比如上例中，任课老师是谁取决于是什么课，是什么课又取决于主键id。因此需要将此表拆分为两张表日程表和课程表(独立数据独立建表)：

id weekday course_class course_id 1001 周一 教育大楼1521 3546 course_id course_name course_teacher 3546 Java 张三 这样就减少了数据的冗余(即使周一至周日每天都有Java课，也只是course_id:3546出现了7次)

存储引擎选择

早期问题：如何选择MyISAM和Innodb？

现在不存在这个问题了，Innodb不断完善，从各个方面赶超MyISAM，也是MySQL默认使用的。

存储引擎Storage engine：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

功能差异

show engines

Engine Support Comment InnoDB DEFAULT Supports transactions, row-level locking, and foreign keys MyISAM YES MyISAM storage engine 存储差异

MyISAM Innodb 文件格式 数据和索引是分别存储的，数据.MYD，索引.MYI 数据和索引是集中存储的，.ibd 文件能否移动 能，一张表就对应.frm、MYD、MYI3个文件 否，因为关联的还有data下的其它文件 记录存储顺序 按记录插入顺序保存 按主键大小有序插入 空间碎片(删除记录并flush table 表名之后，表文件大小不变) 产生。定时整理：使用命令optimize table 表名实现 不产生 事务 不支持 支持 外键 不支持 支持 锁支持(锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的) 表级锁定 行级锁定、表级锁定，锁定力度小并发能力高 锁扩展

表级锁(table-level lock)：lock tables ,... read/write，unlock tables ,...。其中read是共享锁，一旦锁定任何客户端都不可读；write是独占/写锁，只有加锁的客户端可读可写，其他客户端既不可读也不可写。锁定的是一张表或几张表。

行级锁(row-level lock)：锁定的是一行或几行记录。共享锁：select * from where LOCK IN SHARE MODE;，对查询的记录增加共享锁；select * from where FOR UPDATE;，对查询的记录增加排他锁。这里值得注意的是：innodb的行锁，其实是一个子范围锁，依据条件锁定部分范围，而不是就映射到具体的行上，因此还有一个学名：间隙锁。比如select * from stu where id < 20 LOCK IN SHARE MODE会锁定id在20左右以下的范围，你可能无法插入id为18或22的一条新纪录。

选择依据

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新(删除)操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统。

索引

关键字与数据的映射关系称为索引(==包含关键字和对应的记录在磁盘中的地址==)。关键字是从数据当中提取的用于标识、检索数据的特定内容。

索引检索为什么快？

• 关键字相对于数据本身，==数据量小==
• 关键字是==有序==的，二分查找可快速确定位置

图书馆为每本书都加了索引号(类别-楼层-书架)、字典为词语解释按字母顺序编写目录等都用到了索引。

MySQL中索引类型

普通索引(key)，唯一索引(unique key)，主键索引(primary key)，全文索引(fulltext key)

三种索引的索引方式是一样的，只不过对索引的关键字有不同的限制：

• 普通索引：对关键字没有限制
• 唯一索引：要求记录提供的关键字不能重复
• 主键索引：要求关键字唯一且不为null

索引管理语法

查看索引

show create table 表名：

desc 表名

创建索引

创建表之后建立索引

create TABLE user_index(id int auto_increment primary key,first_name varchar(16),last_name VARCHAR(16),id_card VARCHAR(18),information text);-- 更改表结构alter table user_index-- 创建一个first_name和last_name的复合索引，并命名为nameadd key name (first_name,last_name),-- 创建一个id_card的唯一索引，默认以字段名作为索引名add UNIQUE KEY (id_card),-- 鸡肋，全文索引不支持中文add FULLTEXT KEY (information);复制代码

show create table user_index：

创建表时指定索引

CREATE TABLE user_index2 (id INT auto_increment PRIMARY KEY,first_name VARCHAR (16),last_name VARCHAR (16),id_card VARCHAR (18),information text,KEY name (first_name, last_name),FULLTEXT KEY (information),UNIQUE KEY (id_card));复制代码

删除索引

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;alter table user_index drop KEY id_card;alter table user_index drop KEY information;复制代码

删除主键索引：alter table 表名 drop primary key(因为主键只有一个)。这里值得注意的是，如果主键自增长，那么不能直接执行此操作(自增长依赖于主键索引)：

需要取消自增长再行删除：

alter table user_index-- 重新定义字段MODIFY id int,drop PRIMARY KEY复制代码

但通常不会删除主键，因为设计主键一定与业务逻辑无关。

作者：zanwensicheng

链接：https://juejin.im/post/5c6b9c09f265da2d8a55a855