关于数据库: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) 之所以更有效率一些，是因为 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.OrderNo
FROM Persons p
INNER JOIN Orders o
ON 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.OrderNo
FROM Persons p
LEFT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName

查问后果如下：

能够看到，左表（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

查问后果如下：

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

查问后果如下：

查问后果是 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 事务隔离级别

事务管制语句：

• 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 索引的表中，执行过程将会十分慢。

9、优化 de 的查问语句

不应用子查问

例：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 子查问有效，固生产环境应防止应用子查问

防止函数索引

例：

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

因为 MySQL 不像 Oracle 那样反对函数索引，即便 d 字段有索引，也会间接全表扫描。

应改为—–>

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

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

LIKE 双百分号无奈应用到索引

SELECT * FROM t WHERE name LIKE‘%de%’;

—–>

SELECT * FROM t WHERE name LIKE‘de%’;

目前只有 MySQL5.7 反对全文索引（反对中文）

读取适当的记录 LIMIT M,N

SELECT * FROM t WHERE 1;

—–>

SELECT * FROM t WHERE 1 LIMIT 10;

防止数据类型不统一

SELECT * FROM t WHERE id =’19’;

—–>

SELECT * FROM t WHERE id = 19;

分组统计能够禁止排序

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

默认状况下，MySQL 对所有 GROUP BY col1，col2…的字段进行排序。如果查问包含 GROUP BY，想要防止排序后果的耗费，则能够指定 ORDER BY NULL 禁止排序。另外，MySQL 系列面试题和答案全副整顿好了，微信搜寻民工哥技术之路，能够查看 MySQL 企业面试题，在线浏览。

—–>

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

防止随机取记录

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

MySQL 不反对函数索引，会导致全表扫描 —–>

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

禁止不必要的 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;

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

链接：blog.csdn.net/weixin_42047611/article/details/81772149