关于java:MySQL-性能优化的-9-种姿势面试再也不怕了

1、抉择最合适的字段属性

Mysql是一种关系型数据库，能够很好地反对大数据量的存储，然而一般来说，数据库中的表越小，在它下面执行的查问也就越快。因而，在创立表的时候，为了取得更好的性能，咱们能够将表中字段的宽度舍得尽可能小。

例如：在定义邮政编码这个字段时，如果将其设置为char(255)，显然给数据库减少了不必要的空间，甚至应用varchar这种类型也是多余的，因为char(6)就能够很好地实现了工作。同样的如果能够的话，咱们应该是用MEDIUMINT而不是BIGINT来定义整形字段。

2、尽量把字段设置为NOT NULL

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

对于某些文本字段来说，例如“省份”或者“性别”，咱们能够将他们定义为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数值型数据来解决，而数值型数据被解决起来的速度要比文本类型要快得多。这样咱们又能够进步数据库的性能。

3、应用连贯(JOIN)来代替子查问(Sub-Queries)

MySQL从4.1开始反对SQL的子查问。这个技术能够应用select语句来创立一个单例的查问后果，而后把这个后果作为过滤条件用在另一个查问中。

例如：咱们要将客户根本信息表中没有任何订单的客户删除掉，就能够利用子查问先从销售信息表中将所有收回订单的客户id取出来，而后将后果传递给主查问，如下图所示：

如果应用连贯(JOIN)来实现这个工作，速度将会快很多，尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查问如下：

连贯(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（全连贯）四种形式，它们之间其实并没有太大区别，仅仅是查问进去的后果有所不同。

例如咱们有两张表：

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

inner join（内连贯），在两张表进行连贯查问时，只保留两张表中齐全匹配的后果集。

咱们应用inner join对两张表进行连贯查问，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNoFROM Persons pINNER JOIN Orders oON p.Id_P=o.Id_P and 1=1  --用and连贯多个条件ORDER BY p.LastName

查问后果集：

此种连贯形式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.OrderNoFROM Persons pLEFT JOIN Orders oON p.Id_P=o.Id_PORDER BY p.LastName

查问后果如下：

能够看到，左表（Persons表）中LastName为Bush的行的Id_P字段在右表（Orders表）中没有匹配，但查问后果依然保留该行。

right join,在两张表进行连贯查问时，会返回右表所有的行，即便在左表中没有匹配的记录。

咱们应用right join对两张表进行连贯查问，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNoFROM Persons pRIGHT JOIN Orders oON p.Id_P=o.Id_PORDER BY p.LastName

查问后果如下：

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

full join,在两张表进行连贯查问时，返回左表和右表中所有没有匹配的行。

咱们应用full join对两张表进行连贯查问，sql如下：

SELECT p.LastName, p.FirstName, o.OrderNoFROM Persons pFULL JOIN Orders oON p.Id_P=o.Id_PORDER BY p.LastName

查问后果如下：

查问后果是left join和right join的并集。

4、应用联结(UNION)来代替手动创立的长期表

MySQL从4.0版本开始反对union查问，他能够把须要应用长期表的两条或更多的select查问合在一个查问中。在客户端查问会话完结的时候，长期表会被主动删除，从而保障数据库参差、高效。应用union来创立查问的时候，咱们只须要用union作为关键字把多个select语句连接起来就能够了，要留神的是所有select语句中的字段数目要雷同。上面一个例子就演示了一个应用union额查问。

当咱们能够确认不可能呈现反复后果集或者不在乎反复后果集的时候尽量应用union all而不是union，因为union和union all的差别次要是前者须要将两个或者多个后果汇合并后再进行唯一性过滤操作，这就会波及到排序，减少大量的CPU运算，增大资源耗费及提早。

5、事务

只管咱们能够应用子查问(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).
持久性：事务处理完结后，对数据的批改就是永恒的，即使系统故障也不会失落。

事务的并发问题：

1、脏读：事务A读取了事务B更新的数据，而后B回滚操作，那么A读取到的数据就是脏数据

2、不可反复读：事务A屡次读取同一事物，事务B在事务A屡次读取的过程中，对数据做了更新并提交，导致事务A屡次读取同一数据时，后果不统一。

3、幻读：系统管理员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。

6、应用外键

锁定表的办法能够保护数据的完整性，然而他却不能保证数据的关联性。这个时候咱们能够应用外键。例如：外键能够保障每一条销售记录都指向某一个存在的客户。

在这里，外键能够把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。

7、锁定表

只管事务是保护数据库完整性的一个十分好的办法，但却因为他的独占性，有时会影响数据库的性能，尤其是很大的利用零碎中。因为在事务执行的过程中，数据库将会被锁定，因而其余的用户申请只能临时期待直到该事务完结。

如果一个数据库系统只有少数几个用户来应用，事务造成的影响不会成为太大的问题；但假如有成千上万的用户同时拜访一个数据库系统，例如拜访一个电子商务网站，就会产生比较严重的响应提早。

其实，有些状况下咱们能够通过锁定表的形式来取得更好的性能。上面的例子就是锁定表的办法来实现后面一个例子中事务的性能。

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

8、应用索引

索引是进步数据库性能的罕用办法，他能够令数据库服务器比没有索引快得多的速度检索特定的行，尤其是在查问语句当中蕴含有MAX(),MIN()和ORDERBY这些命令的时候，性能进步更为显著。

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

一般来说，索引应该建设在那些将用于join，where判断和orderby排序的字段上。尽量不要对数据库中某个含有大量反复的值的字段建设索引，对于一个ENUM类型的字段来说，呈现大量反复值是很有可能的状况。

例如customerinfo中的“province”..字段，在这样的字段上建设索引将不会有什么帮忙；相同，还有可能升高数据库的性能。咱们在创立表的时候能够同时创立适合的索引，也能够应用ALTERTABLE或CREATEINDEX在当前创立索引。

此外，MySQL从版本3.23.23开始反对全文索引和搜寻。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，而后再应用ALTERTABLE或CREATEINDEX创立索引，将是十分快的。但如果将数据装载到一个曾经有FULLTEXT索引的表中，执行过程将会十分慢。举荐：Java进阶视频资源

9、优化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禁止排序。

—–>

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’);

原文链接：https://blog.csdn.net/weixin_...

版权申明：本文为CSDN博主「Bug_Stack」的原创文章，遵循CC 4.0 BY-SA版权协定，转载请附上原文出处链接及本申明。

近期热文举荐：

1.1,000+ 道 Java面试题及答案整顿(2021最新版)

2.别在再满屏的 if/ else 了，试试策略模式，真香！！

3.卧槽！Java 中的 xx ≠ null 是什么新语法？

4.Spring Boot 2.5 重磅公布，光明模式太炸了！

5.《Java开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞+转发哦！