MYSQL的九种优化姿势

1、选择适合的字段属性

        对于关系型数据库MYSQL来说，数据库中的表越小，执行的速度越快。因此为了获得更好、更快的性能，我们在创建数据库的时候应该将表中的字段的长度尽可能的减小。   

       (eg：在定义 性别 这个字段的时候，如果将其设置为char(255)，显然给数据库增加了很多不必要的空间，但是换成char(2)就可以充分表达这个字段表达的含义。)

2、字段尽量设置为NOT NULL

        NULL显而易见，就是什么都没有，连\O都没有，\O在字符串中是结束符，在物理内存是占空间的，相当于一个字节，而NULL连一个字节都没有。在数据库中是有严格区分的，任何跟NULL进行运算的都是NULL，所以判断是否等于NULL，不能简单的用=，应该用IS NULL关键字。 

        最主要的是：在MYSQL中，含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算跟家复杂。应该设置为NOT NULL或者使用一个特殊的值代替空值。 

        (eg：在创建数据库表时，有好多字段(例如: 性别 )可以定义为枚举(ENUM)类型。枚举类型为数据型数据，在MYSQL中，数值型数据处理的速度比文本快很多呢。 )

3、使用JOIN代替子查询

        首先来说一下子查询：MYSQL从4.1开始支持子查询。说白了就是使用select语句创建一个单例的查询结果，然后把这个结果当作一种条件放在另一个查询中。

        eg：要删除字典表中不用的数据，就可以利用子查询语句查出字典配置表中的所有在使用的ID，然后将查询的结果传递给主查询作为条件，如下图：

delete from cls.param_dict
    where type_id not in (select id from cls.param_dict_type);

          如果我们使用链接JOIN 来完成这个sql的话，速度会提升不少，尤其时子查询表中ID有索引，性能会更好，如下图：

select * from cls.param_dict pd 
    left join cls.param_dict_type dt on pd.type_id = dt.id;

         连接JOIN之所以会变得很快，是因为MYSQL不需要在内存中创建临时表来完成当前查询逻辑上需要得两个查询步骤。

         当然，如果在你的应用中需要有很多JOIN查询，想继续优化，那么就需要确认JOIN所关联的表中的字段是否已建立索引。这样在MYSQL内部会启动优化JOIN的sql语句的机制，与此同时也因注意，被用来JOIN的字段是否相同类型，如果类型不同进行JOIN，MYSQL无法使用它们的索引。

         left /right join 是外连接，inner join是内连接，也被称为等值连接。经证实，inner join性能比其他两种连接性能快，就是因为inner join是等值连接。除了出现明显的inner join关键字，下图中第四种情况也用到了等值连接。

-- 左连接
select * from cls.param_dict pd left join cls.param_dict_type dt on pd.type_id = dt.id;
-- 右连接
select * from cls.param_dict pd right join cls.param_dict_type dt on pd.type_id = dt.id;
-- 内连接
select * from cls.param_dict pd inner join cls.param_dict_type dt on pd.type_id = dt.id;
-- 内连接
select * from cls.param_dict pd , cls.param_dict_type dt where pd.type_id = dt.id;

         sql语句中连接查询分为四种：inner join(内连接)、left join(左连接)、right join(右连接)、    full join(全连接)四种方式，这四种查询的区别是查询的结果不同：

        inner join(内连接)：只保留表中完全匹配的结果集；

        left join(左连接)：返回左表所有的行数据，即使右表没有匹配的行数据；

        right join(右连接)：返回右表所有的行数据，即使左表没有匹配的行数据；

        full join(全连接)：返回左表和右表中所有没有匹配的行数据。

4、使用联合UNION代替手动创建的临时表

        从MYSQL4.0版本开始支持union查询，它可以把多个select查询合在一个查询中，避免了使用临时表。在客户端查询会话结束的时候，临时表会自动删除，可以保证数据库整齐、高效。需要注意的是所有的select语句中需要查询的字段数目要保持相同。

select id,name from cls.param_dict where id = '1'
union
select id,remark from cls.param_dict_type where id = '2';

        如果确认不会出现重复结果或者不在乎重复结果的时候，那我们尽量使用union all，因为union 和union all的差异在于前者需要将两个或者多个结果集合并后在进行唯一性过滤，sql中涉及到排序，增加大量的CPU运算，sql执行时间延迟以及资源消耗增大。

5、事务

        mysql事务主要用于处理操作量大、复杂度高的数据。例如在学校管理系统中，删除一个人员，除了删除人员的基本信息，还要删除所有与人员关联其它信息，这些数据库操作就构成了一个事务。

        事务具有四个特性：原子性、一致性、隔离性、持续性。事务的这四个特性简称为ACID特性，这种特性保证了一个事务或者成功提交，或者是吧回滚，二者必选其一。因此，它对事务的修改具有可恢复性。即当食物失败时，他对数据的修改都会恢复到该事务执行前的状态。

        原子性：事务中的操作要么全部执行，要不全都不执行，不能只完成部分操作。原子性在数据系统中是由恢复机制来实现。

        一致性 ：事务开始前，数据库处于一致性的状态；事务结束后，数据库依然处于一致性的状态。数据库的一致性是由用户负责的。例如，用户使用银行系统转账，用户可以定义转账前后两个账户金额之和保持不变。

        隔离性：系统保证事务不受其它并发执行事务的影响，即同时执行多个事务，每个事务都是独立的，不受其它事务的影响。事务再查看数据所处的状态只有两种，一是另一个并发事务修改它之前的状态；二是另一个事务修改它之后的状态，事务是不会查看中间状态的数据。隔离性是通过系统的并发控制机制实现。

        持久性：一个已完成的事务对数据所做的任何改动在系统中都是永久有效的，即使该事务产生的修改是错误的，也会一直保持。持久性通过恢复机制实现，发生故障时，可以通过日志等手段恢复数据库信息。

        开始事务：

        事务以BEGIN TRANSACTION开始，语法格式如下:

   BEGIN TRANSACTION <事务名称> |@ <事务变量名称>

• @<事务变量名称>是由用户定义的变量，必须用 char、varchar、nchar 或 nvarchar数据类型来声明该变量。

• BEGIN TRANSACTION 语句的执行使全局变量 @@TRANCOUNT 的值加 1。

        提交事务：

        事务是通过COMMIT提交所有的操作。具体来说，就是将该事务中国所有对数据库的更新写回到磁盘上的物理数据库中，事务正常结束。

        提交事务意味着将事务开始以来所有的数据修改成为数据库的永久部分，因此也标志着一个事务的结束。一旦执行该命令，将不能回滚事务。只有在所有修改都准备好提交数据库时，才执行这一操作。语法格式如下：

COMMIT TRANSACTION <事务名称> |@<事务变量名称>

COMMIT TRANSACTION语句的执行使全局变量 @@TRANCOUNT 的值减 1。

        撤销事务:

        ROLLBACK 表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态。这里的操作指对数据库的更新操作。

        当事务执行过程中遇到错误时，使用 ROLLBACK TRANSACTION 语句使事务回滚到起点或指定的保持点处。同时，系统将清除自事务起点或到某个保存点所做的所有的数据修改，并且释放由事务控制的资源。因此，这条语句也标志着事务的结束。语法格式如下：

ROLLBACK [TRANSACTION][<事务名称>| @<事务变量名称> | <存储点名称>| @ <含有存储点名称的变量名>

• 当条件回滚只影响事务的一部分时，事务不需要全部撤销已执行的操作。可以让事务回滚到指定位置，此时，需要在事务中设定保存点（SAVEPOINT）。保存点所在位置之前的事务语句不用回滚，即保存点之前的操作被视为有效的。保存点的创建通过“SAVING TRANSACTION<保存点名称>”语句来实现，再执行“ROLLBACK TRANSACTION<保存点名称>”语句回滚到该保存点。
• 若事务回滚到起点，则全局变量 @@TRANCOUNT 的值减 1；若事务回滚到指定的保存点，则全局变量 @@TRANCOUNT 的值不变。

6、使用外键

        外键是用于建立和加强两个表数据之前的连接的一列或多列。通过将保存表中主键的一列或多列添加到另外一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。同时使用外键也可以保证数据的参照完整性。

        eg：有两张表，一个是学生档案，另一个是成绩单

        档案：学号，姓名（学号为主键)

        成绩单：学期号、学号、成绩（学期号、学号同时为主键，学号同时为外键）

        为了保证成绩单上的数据有效，所以要求录入学号时，必需保证档案中有这个学号，否则就不能录入。 从而保证了成绩单上的成绩数据的引用完整，否则将会是垃圾数据。

7、锁定表

        事务是维护数据库完整性的一个非常好的方法，但是由于事务的独占性，有时会影响数据库的性能，尤其在大的应用系统中。由于在事务的执行过程中，数据库将会被锁定，因此其他的用户只能暂时等待直到事务结束。

        当数据库系统用户量少时，事务造成的影响不会成为太大的问题；但是假设有成千上万的用户同时访问一个数据库系统，就会产生很严重的延迟响应。其实，有些情况下我们可以通过锁定表的方式来获得更好的性能。下图的例子就是锁定表的方法来完成事务的功能：

LOCK TABLE param_dict WRITE SELECT type_id FROM param_dict WHERE id = '1';

...

UPDATE param_dict SET type_id = '1011' WHERE id = '1';UNLOCK TABLES

8、使用索引

        索引是提高数据库性能的常用方法，添加索引后比没有索引的性能提高更为明显，尤其是查询语句中包含MAX()，MIN()和ORDER这些命令的时候。

        当然也不是所有字段，我们都去进行索引维护，我们应该在那些用于join、where判断和order by排序的字段上。而且尽量不要对数据库中某个包含大量重复值的字段建立索引。

SELECT
pd.name                 <--不是备选列
FROM
param_dict pd LEFT JOIN param_dict_type dt
ON pd.type_id = dt.id   <-- 备选列
WHERE 
dt.type_code = '10011'; <-- 备选列

        如上图所示，仅仅出现在SELECT关键字后面的输出数据列不是很好的备选列；当然，显示的数据列与WHERE子句中使用的数据列也有可能相同。个人建议是输出列表中的数据列本质上不是用于索引的很好的备选列。

9、查询语句优化

1、不使用子查询

SELECT * FROM T1 WHERE id in（SELECT id FROM T2 WHERE name = '小明'）;

-- 应该为 -->

SELECT * FROM T1 LEFT JOIN T2 ON T1.id = T2.id ;

2、避免函数索引

SELECT * FROM TABLE WHERE YEAR(date_time) >= 2019 ;
-- 由于MYSQL不像Oracle一样支持函数索引，即使d字段有索引，任然会执行全表扫描

-- 应修改为-->

SELECT * FROM TABLE WHERE date_time >= '2019-12-01';

3、用IN 替换OR

-- 低速查询
SELECT * FROM TABLE WHERE type_id = 1 OR type_ID = 2;

-- 高效查询
SELECT * FROM TABLE WHERE type_id IN (1,2) ;

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

SELECT * FROM TABLE WHERE name LIKE '%小明%';

-- 应该为-->

SELECT * FROM TABLE WHERE name LIKE '小明%' ;

5、读取适量的记录 LIMIT(M,N)

SELECT * FROM TABLE WHERE 1;

-- 应改为 -->

SELECT * FROM TABLE WHERE 1 LIMIT 10;

6、避免数据类型不一致

SELECT * FROM TABLE WHERE age = '18';  -- STRING类型

SELECT * FROM TABLE WHERE age = 18 ;   -- INTEGER类型

7、分组统计禁止排序

SELECT  group_id, count(1) FROM TABLE GROUP BY group_id;

-- 默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序--- 结果的消耗，则可以指定ORDER BY NULL禁止排序。

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

8、避免随机取记录

SELECT * FROM TABLE 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 TABLE (id, name) VALUES(1,’Bea’);
INSERT INTO TABLE (id, name) VALUES(2,’Belle’);
INSERT INTO TABLE (id, name) VALUES(3,’Bernice’);

-->

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