Mysql性能优化面试专题一

mysql是我们程序员最常用的数据库之一

• mysql在我们日常的使用和开发过程有着重要的作用，使用很方便，可当我们要处理大量的高并发，高可用数据时，就会遇到MySQL性能不足的问题，这个时候我们就需要对其进行优化了。

---

一、数据库设计的三大范式？

通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：

第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，它要求字段没有冗余。 没有冗余的数据库设计可以做到。

二、数据库的分类

最常见的数据库模型主要是两种，即关系型数据库和非关系型数据库。

关系型数据库: mysql/oracle/db2/informix/sysbase/sql server

非关系型数据库: (特点: 面向对象或者集合)

三.sql语句优化

先看看我们常见的一些sql语句

#创建数据库：
CREATE DATABASE person;

#选择数据库：
USE person;

#创建表
CREATE TABLE IF NOT EXISTS Person(
	ID INT PRIMARY KEY  AUTO_INCREMENT,
	NAME VARCHAR(50) NOT NULL,
	SEX VARCHAR(50) NOT NULL,
	AGE INT NOT NULL,
	RANK VARCHAR(50) NOT NULL,
	DEPARTMENT INT(100) NOT NULL
)ENGINE=INNODB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

#插入数据
INSERT INTO `person` VALUES(NULL,'李四','女',30,'1级',1);

-- 更新数据
update 表名 set 列名=值 where 条件
update mytable set name='哈哈',sid=7 where sid=1;

-- 删除数据
delete from mytable where 条件
truncate table mytable;-- 删除后重置自增列

-- 获取数据列
select * from mytable limit 3;
当前页 页索引 每页的页数
 第一页 limit 0,2

--添加字段
alter table student add stuId int(10) comment '身份证号';

--删除字段
alter table warehouseapplication drop column wnum;

-- 修改字段类型
alter  table materialdesign MODIFY COLUMN designid varchar(150); 

-- 建表之后建立外键约束
alter table Products(从表) add foreign key(外键字段) references 
关联表(关联字段)
alter table 表名 add constraint 外键名 foreign key(外键字段名) references
关联表(关联字段)
alter table orderdetail add foreign key(ProID) references 
products(ProID)

-- 建表之后添加主键约束
desc test1;
drop table test1;
create table test1
(
	id int,
	name varchar(20)
)
alter table test1 add primary key(id);-- 或
alter table test1 add constraint pk_id primary key test1(id);

-- 关闭自动提交事务
set autocommit=0;
update bank set money=money-500 where name='张三';
update bank set money=money+500 where name='李四';
-- 提交事务
commit;
update bank set money=money+500 where name='李四';
-- 回滚事务
rollback;
-- 开启自动提交事务
set autocommit=1;

-- 创建视图
create view view_teacher AS
select StudentName,s.StudentNo,phone,subjectName,studentResult from student s,`subject` su,result r 
where s.StudentNo=r.studentNo and r.subjectNo=su.subjectNo;

-- 创建索引
create index index_tnum on test(tNum);
-- 查看索引
show index from test;
select * from test where tNum=10000;-- 0.000s
-- 删除索引
drop index index_tnum on test;
#外键约束
ALTER TABLE `Orders` ADD CONSTRAINT `Orders_User` FOREIGN KEY(`uid`)
REFERENCES `b_User`(`userId`);

#多表查询
SELECT warehouseapplication.`wareid`,warereason,Registrationtime,Total,MoneySum,delivery_schedule.* FROM warehouseapplication
	LEFT JOIN delivery_schedule ON warehouseapplication.`wareid`=delivery_schedule.`wareid`
	WHERE OorI=0

接下来是sql优化

1.常规SQL语句优化
1.不用*来代替所有列名,尽量采用与访问表相关的实际列名; 2.用TRUNCATE 代替DELETE 图片发自简书App 3.在确保完整性的情况下,多使用COMMIT语句。 4.尽量减少表…
2.表连接优化
1.驱动表的选择: 驱动表是指被最先访问的表; 2.where子句连接顺序 表连接最好都在where条件以前
3.合理使用索引 从总行中查询2%-4%的表,可以考虑建立索引 建立索引的基本原则:
3.合理使用索引
从总行中查询2%-4%的表，可以考虑建立索引
建立索引的基本原则：
1.以查询关键字为基础，表中的行随机排列
2.包含的列数相对较少的表
3.表中大多数查询都包含相对简单的WHERE从句

SQL语句优化-定位慢查询

为了便于测试，我们构建一张大的数据表， 使用存储过程构建
默认情况下，mysql认为10秒才是一个慢查询.

修改mysql的慢查询:
show time like ‘long_query_time’ ; //可以显示当前慢查询时间
set long_query_time=1 ;//可以修改慢查询时间
修改mysql的索引
　　mysql查询优化器会根据具体情况自己判断走哪个索引，不一定是走主键（explain中的key可以看到走的哪个key）具体情况根据具体情况来定，当你要强制执行走某一个key时：在查询的最后加上 force index(primary);
　　当然在数据的表比较小或者数据比较少时，也可以不加索引。

四、sql优化面试题

1、Mysql 的技术特点是什么？
Mysql 数据库软件是一个客户端或服务器系统，其中包括：支持各种客户端程序和库的多线程 SQL 服务器、不同的后端、广泛的应用程序编程接口和管理工具。

2、与 Oracle 相比，Mysql 有什么优势？
Mysql 是开源软件，随时可用，无需付费。
Mysql 是便携式的
带有命令提示符的 GUI。
使用 Mysql 查询浏览器支持管理

3、区分 CHAR_LENGTH 和 LENGTH？
CHAR_LENGTH 是字符数，而 LENGTH 是字节数。Latin 字符的这两个数据是相同的，但是对于 Unicode 和其他编码，它们是不同的。

4.mysql的特性？
mysql8.0用户密码有变化：
在MySQL 8.0.11中，caching_sha2_password是默认的身份验证插件，而不是以往的mysql_native_password。如果客户端连接报授权验证出错，可使用以下命令调整。

alter user ‘root’@‘localhost’ identified with mysql_native_password by ‘密码’;
flush privileges;

5.实践中如何优化MySQL？

最好是按照以下顺序优化：

1. SQL语句及索引的优化
2. 数据库表结构的优化
3. 系统配置的优化
4. 硬件的优化

6、Mysql 服务器默认端口是什么？
Mysql 服务器的默认端口是 3306。

谢谢大家的阅读！更多sql面试题请查看下篇文章