龙空技术网

最全的MySQL优化面试题

加瓦攻城狮 426

前言:

现时兄弟们对“mysql周一”大体比较关切,你们都想要学习一些“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 <table_name1>,<table_name2>... read/write,unlock tables <table_name1>,<table_name2>...。其中read是共享锁,一旦锁定任何客户端都不可读;write是独占/写锁,只有加锁的客户端可读可写,其他客户端既不可读也不可写。锁定的是一张表或几张表。

行级锁(row-level lock):锁定的是一行或几行记录。共享锁:select * from <table_name> where <条件> LOCK IN SHARE MODE;,对查询的记录增加共享锁;select * from <table_name> 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

链接:

来源:掘金

著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

标签: #mysql周一