【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!

2021年11月23日 阅读数:3
这篇文章主要向大家介绍【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

 

【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!_mysql

一、选择最合适的字段属性java

Mysql是一种关系型数据库,能够很好地支持大数据量的存储,可是通常来讲,数据库中的表越小,在它上面执行的查询也就越快。所以,在建立表的时候,为了得到更好的性能,咱们能够将表中字段的宽度舍得尽量小。mysql

例如:在定义邮政编码这个字段时,若是将其设置为char(255),显然给数据库增长了没必要要的空间,甚至使用varchar这种类型也是多余的,由于char(6)就能够很好地完成了任务。一样的若是能够的话,咱们应该是用MEDIUMINT而不是BIGINT来定义整形字段。面试

二、尽可能把字段设置为NOT NULLsql

在可能的状况下,尽可能把字段设置为NOT NULL,这样在未来执行查询的时候,数据库不用去比较NULL值。数据库

对于某些文本字段来讲,例如“省份”或者“性别”,咱们能够将他们定义为ENUM(枚举)类型。由于在MySQL中,ENUM类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型要快得多。这样咱们又能够提升数据库的性能。安全

三、使用链接(JOIN)来代替子查询(Sub-Queries)性能优化

MySQL从4.1开始支持SQL的子查询。这个技术可使用select语句来建立一个单例的查询结果,而后把这个结果做为过滤条件用在另外一个查询中。服务器

例如:咱们要将客户基本信息表中没有任何订单的客户删除掉,就能够利用子查询先从销售信息表中将全部发出订单的客户id取出来,而后将结果传递给主查询,以下图所示:微信

【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!_mysql_02

若是使用链接(JOIN)来完成这个工做,速度将会快不少,尤为是当salesinfo表中对CustomerID建有索引的话,性能将会更好,查询以下:并发

【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!_数据库_03

链接(JOIN)之因此更有效率一些,是由于MySQL不须要在内存中建立临时表来完成这个逻辑上 须要两个步骤的查询工做。

另外,若是你的应用程序有不少JOIN查询,你应该确认两个表中JOIN的字段是被创建过索引的。这样MySQL内部 会启动为你优化JOIN的SQL语句的机制。并且这些被用来JOIN的字段,应该是相同的类型的。

例如:若是你要把DECIMAL字段和一个INT字段JOIN在一块儿,MySQL就没法使用他们的索引。对于那些STRING类型,还须要有相同的字符集才行。(两个表的字符集可能不相同)。

inner join内链接也叫作等值链接,left/right join是外连接。

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id=B.id;
SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

通过多方面的证明inner join性能比较快,由于inner join是等值链接,或许返回的行数比较少。可是咱们要记得有些语句隐形的用到了等值链接,如:

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

sql中的链接查询有inner join(内链接)、left join(左链接)、right join(右链接)、full join(全链接)四种方式,它们之间其实并无太大区别,仅仅是查询出来的结果有所不一样。另外推荐公众号Java精选,回复Java面试,获取最新大厂面试题资料在线看。

例如咱们有两张表:

【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!_数据库_04

Orders表经过外键Id_P和Persons表进行关联。

inner join(内链接),在两张表进行链接查询时,只保留两张表中彻底匹配的结果集。

咱们使用inner join对两张表进行链接查询,sql以下:

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders o
ON p.Id_P=o.Id_P and 1=1 --用and链接多个条件
ORDER BY p.LastName

查询结果集:

【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!_面试_05

此种链接方式Orders表中Id_P字段在Persons表中找不到匹配的,则不会列出来。

注意:单纯的​​select * from a,b​​是笛卡尔乘积。好比a表有5条数据,b表有3条数据,那么最后的结果有​​5*3=15​​条数据。

可是若是对两个表进行关联:​​select * from a,b where a.id = b.id​​ 意思就变了,此时就等价于:

select * from a inner join b on a.id = b.id。 -- 即就是内链接。

可是这种写法并不符合规范,可能只对某些数据库管用,如sqlserver。推荐最好不要这样写。最好写成inner join的写法。

内链接查询 (​​select * from a join b on a.id = b.id​​) 与 关联查询 (​​select * from a , b where a.id = b.id​​)的区别

left join,在两张表进行链接查询时,会返回左表全部的行,即便在右表中没有匹配的记录。

咱们使用left join对两张表进行链接查询,sql以下:

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
LEFT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

查询结果以下:

【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!_sql_06

能够看到,左表(Persons表)中LastName为Bush的行的Id_P字段在右表(Orders表)中没有匹配,但查询结果仍然保留该行。

right join,在两张表进行链接查询时,会返回右表全部的行,即便在左表中没有匹配的记录。

咱们使用right join对两张表进行链接查询,sql以下:

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
RIGHT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

查询结果以下:

【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!_java_07

Orders表中最后一条记录Id_P字段值为65,在左表中没有记录与之匹配,但依然保留。

full join,在两张表进行链接查询时,返回左表和右表中全部没有匹配的行。

咱们使用full join对两张表进行链接查询,sql以下:

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
FULL JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

查询结果以下:

【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!_java_08

查询结果是left join和right join的并集。

四、使用联合(UNION)来代替手动建立的临时表

MySQL从4.0版本开始支持union查询,他能够把须要使用临时表的两条或更多的select查询合在一个查询中。在客户端查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用union来建立查询的时候,咱们只须要用union做为关键字把多个select语句链接起来就能够了,要注意的是全部select语句中的字段数目要相同。

下面一个例子就演示了一个使用union额查询。

【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!_mysql_09

当咱们能够确认不可能出现重复结果集或者不在意重复结果集的时候尽可能使用union all而不是union,由于union和union all的差别主要是前者须要将两个或者多个结果集合并后再进行惟一性过滤操做,这就会涉及到排序,增长大量的CPU运算,增大资源消耗及延迟。另外推荐公众号Java精选,回复Java面试,获取最新大厂面试题资料在线看。

五、事务

尽管咱们可使用子查询(Sub-Queries)、链接(JOIN)和联合(UNION)来建立各类各样的查询,但不是全部的数据库操做,均可以只用一条或少数几条就能够完成的。更多的时候是须要用一系列的语句来完成某种工做。可是在这种状况下,当这个语句块中的某一条语句运行出错的时候,整个语句块的操做就会变得不肯定起来。

设想一下,要把某个数据同时插入两个相关联的表中,可能会出现这样的状况:第一个表中成功更新后,数据库忽然出现意外情况,形成第二个表中的操做没有完成,这样就会形成数据的不完整,甚至会破坏数据库中的数据。要避免这种状况,就应该使用事务,它的做用是要么语句块中每条语句都操做成功,要么都失败。

换句话说,就是能够保持数据库中的数据的一致性和完整性。事务以BEGIN关键字开始,COMMIT关键字结束。在这之间的一条SQL语句操做失败,那么Rollback命令就能够把数据库恢复到begin开始以前的状态。

BEGIN; 
INSERTINTOsalesinfoSETCustomerID=14;
UPDATEinventorySETQuantity=11WHEREitem='book';
COMMIT;

事务的另外一个做用是当多个用户同时使用相同的数据源时,他可使用锁定数据库的方式来为用户提供一种安全的访问机制,这样能够保证用户的操做不被其它的用户所干扰。

通常来讲,事务必须知足四个条件(ACID):原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability).

  • 原子性:一个事物(transaction)中的全部操做,要么所有完成,要么所有不完成,不会结束在中间某个环节。事务在执行过程当中发生错误,会被回滚(Rollback)到事务开始的状态,就像这个事务历来没有执行过同样。
  • 一致性:在事务开始以前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须彻底符合全部的预设规则,这包含资料的精确度、串联性以及后续数据库能够自发性地完成预约的工做。
  • 隔离性:数据库容许多个事务同时对其数据进行读写和修改的能力,隔离性能够防止多个事务并发执行时因为交叉执行而致使数据的不一致。事务隔离分为不一样的级别,包括读未提交(Read uncommitted)、读已提交(Read committed)、可重复读(repeateable read)和串行化(Serializable).
  • 持久性:事务处理结束后,对数据的修改就是永久的,即使系统故障也不会丢失。

事务的并发问题:

一、​​脏读​​:事务A读取了事务B更新的数据,而后B回滚操做,那么A读取到的数据就是脏数据

二、​​不可重复读​​:事务A屡次读取同一事物,事务B在事务A屡次读取的过程当中,对数据作了更新并提交,致使事务A屡次读取同一数据时,结果不一致。

三、​​幻读​​:系统管理员A将数据库中的全部学生的成绩从具体分数改成ABCDE等级,可是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉同样,这就叫幻读。

小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住知足条件的行,解决幻读须要锁表

MySQL事务隔离级别:

事务隔离级别

脏读

不可重复读

幻读

读未提交(read-uncommitted)

不可重复读(read-committed)

可重复读(repeatable-read)

串行化(serializable)

事务控制语句:

  • BEGIN或START TRANSACTION:显式的开启一个事物。
  • COMMIT:也可使用COMMIT WORK,不过两者是等价的。COMMIT会提交事务,并使已对数据库进行的全部修改为为永久性的。
  • Rollback:也可使用Rollback work,不过两者是等价的。回滚会结束用户的事务,并撤销正在进行的全部未提交的修改。
  • SAVEPOINT identifier:SAVEPOINT容许在事务中建立一个保存点,一个事务中能够有不少个SAVEPOINT;
  • RELEASE SAVEPOINT identifier:删除一个事物的保存点,当没有指定的保存点时,执行该语句会抛出一个异常。
  • ROLLBACK TO inditifier:把事务回滚到标记点。
  • SET TRANSACTION:用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERLALIZABLE。

六、使用外键

锁定表的方法能够维护数据的完整性,可是他却不能保证数据的关联性。这个时候咱们可使用外键。例如:外键能够保证每一条销售记录都指向某一个存在的客户。

在这里,外键能够把customerinfo表中的customerid映射到salesinfo表中customerid,任何一条没有办法合法customerid的记录都不会被跟新或插入到salesinfo中.

CREATE TABLE customerinfo(customerid int primary key) engine = innodb;
CREATE TABLE salesinfo( salesid int not null,customerid int not null, primary key(customerid,salesid),foreign key(customerid) references customerinfo(customerid) on delete cascade)engine = innodb;

注意例子中的参数“on delete cascade”.该参数保证当customerinfo表中的一条客户记录也会被自动删除。若是要在mysql中使用外键,必定要记住在建立表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是mysql表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=innoDB。

你还在处处找面试题,点击这个程序Java精选面试题进行刷题,面试题太齐全了。

七、锁定表

尽管事务是维护数据库完整性的一个很是好的方法,但却由于他的独占性,有时会影响数据库的性能,尤为是很大的应用系统中。因为在事务执行的过程当中,数据库将会被锁定,所以其余的用户请求只能暂时等待直到该事务结束。

若是一个数据库系统只有少数几个用户来使用,事务形成的影响不会成为太大的问题;但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟。

其实,有些状况下咱们能够经过锁定表的方式来得到更好的性能。下面的例子就是锁定表的方法来完成前面一个例子中事务的功能。

【161期】MySQL 性能优化的 9 种姿式,面试不再怕了!_sql_10

这里,咱们用一个select语句取出初始数据,经过一些计算,用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句能够保证在UNLOCKTABLES命令被执行以前,不会有其余访问来对inventory进行插入、更新或者删除的操做。

八、使用索引

索引是提升数据库性能的经常使用方法,他能够令数据库服务器比没有索引快得多的速度检索特定的行,尤为是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候,性能提升更为明显。

那该对那些字段进行索引呢?

通常来讲,索引应该创建在那些将用于join,where判断和orderby排序的字段上。尽可能不要对数据库中某个含有大量重复的值的字段创建索引,对于一个ENUM类型的字段来讲,出现大量重复值是颇有可能的状况。

例如customerinfo中的“province”..字段,在这样的字段上创建索引将不会有什么帮助;相反,还有可能下降数据库的性能。咱们在建立表的时候能够同时建立合适的索引,也可使用ALTERTABLE或CREATEINDEX在之后建立索引。

此外,MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引,但仅能用于MyISAM类型的表。对于一个大的数据库,将数据装载到一个没有FULLTEXT索引的表中,而后再使用ALTERTABLE或CREATEINDEX建立索引,将是很是快的。但若是将数据装载到一个已经有FULLTEXT索引的表中,执行过程将会很是慢。

九、优化de的查询语句

1 不使用子查询

例:​​SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);​

子查询在MySQL5.5版本里,内部执行计划器是这样执行的:先查外表再匹配内表,而不是先查内表t2,当外表的数据很大时,查询速度会很是慢。

在MariaDB10/MySQL5.6版本里,采用join关联方式对其进行了优化,这条SQL会自动转换为

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

但请注意的是:优化只针对SELECT有效,对UPDATE/DELETE子查询无效,固生产环境应避免使用子查询

2 避免函数索引

例:

SELECT * FROM t WHERE YEAR(d) >= 2016;

因为MySQL不像Oracle那样支持函数索引,即便d字段有索引,也会直接全表扫描。

应改成—–>

SELECT * FROM t WHERE d >= ‘2016-01-01;

3 用IN来替换OR

低效查询

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

—–> 高效查询

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

4 LIKE双百分号没法使用到索引

SELECT * FROM t WHERE name LIKE %de%;

—–>

SELECT * FROM t WHERE name LIKE ‘de%;

目前只有MySQL5.7支持全文索引(支持中文)

5 读取适当的记录LIMIT M,N

SELECT * FROM t WHERE 1;

—–>

SELECT * FROM t WHERE 1 LIMIT 10;

6 避免数据类型不一致

SELECT * FROM t WHERE id = ’19’;

—–>

SELECT * FROM t WHERE id = 19;

7 分组统计能够禁止排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

默认状况下,MySQL对全部GROUP BY col1,col2…的字段进行排序。若是查询包括GROUP BY,想要避免排序结果的消耗,则能够指定ORDER BY NULL禁止排序。另外,MySQL 系列面试题和答案所有整理好了,微信搜索Java精选,在后台发送:Java面试,能够在线阅读。

—–>

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

8 避免随机取记录

SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;

MySQL不支持函数索引,会致使全表扫描 —–>

SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

9 禁止没必要要的ORDER BY排序

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;

—–>

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

10 批量INSERT插入

INSERT INTO t (id, name) VALUES(1,’Bea’);
INSERT INTO t (id, name) VALUES(2,’Belle’);
INSERT INTO t (id, name) VALUES(3,’Bernice’);

—–>

INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’);