Mysql事务特性及SQL性能优化

事务4个特性

ACID。A原子性，C一致性，I隔离性，D持久性。

事务的隔离级别

read-uncommitted 

读未提交，最简单的场景就是当我们A事务更新一条数据时，在未commit时，另一个事务B读取到commit之前的结果进行了业务数据处理，但是由于A事务出现了异常rollback了，此时B使用的A事务未提交的错误的脏数据处理的，此时就产生了脏读。

解决这个问题，可以修改事务隔离级别为读已提交。

tx_isolation

READ-COMMITTED

read-committed

读已提交就是会仅读取那些已经提交的变更，已经提交的变更都是有效的变更。所以不会出现读无效的脏数据的情况。但是随之而来的是另外一个问题，就是不可重复读的问题。意思很简单，就是A事务第一次读取变量X的值为100，第二次读取X的值变为200，这中途B事务提交了修改将X变为200.这种情况下，A事务对B事务有所感知，受到了B事务的影响，两次读到的值不一样，我们称之为不可重复读问题。

repeatable-read

可重复读。为了解决读已经提交级别中的不可重复读问题，我们升级到了可重复读级别。然后存在的问题就是幻读了，幻读指的是通过Insert后新增了记录，两次相同的查询条件而查询到的记录数不一样。常见的场景是在后台分页查询时，先后查询到记录数不一样。

serializable

所有操作都上排它锁。串行化执行。一般不使用

事例参考：https://blog.csdn.net/weixin_44366439/article/details/88622923

SQL优化

MySQL查询过程：

优化简介

在 MySQL 中，可以使用 SHOW STATUS 语句查询一些 MySQL 数据库的性能参数。 SHOW STATUS 语句语法如下：

  SHOW STATUS LIKE 'value';

  其中，value是要查询的参数值，一些常用的性能参数如下：

  Connections：连接MySQL服务器的次数(不管是否连接成功)；

  Uptime：MySQL服务器的上线时间（本次启动后的运行时间）；

  Slow_queries：慢查询的次数（查询时间超过long_query_time秒的查询的个数）；

  Com_select：查询操作的次数；

  Com_insert：插入操作的次数；

  Com_update：更新操作的次数；

  Com_delete：删除操作的次数。

优化查询

分析查询语句

  通过对查询语句的分析，可以了解查询语句执行情况，找出查询语句执行的瓶颈，从而优化查询语句。

  MySQL中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句。

  EXPLAIN语句的基本语法如下（DESCRIBE相同）：

  EXPLAIN [EXTENDED] SELECT select_options;

  使用EXTENDED关键字，EXPLAIN语句将产生附加信息。select_options是SELECT语句的查询选项，包括FROM WHERE子句等。

分析查询语句结果解释：

  type（重要）：表示表的连接类型，包括system，consts（最佳），eq_ref，ref（要求），ref_not_null，index_merge，unique_subquery，index_subquery，range（最低要求），index，ALL；

possible_keys（重要）：指出MySQL能使用哪个索引在该表中找到行；

  key（重要）：表示查询实际使用到的索引，如果没有选择索引，该列的值是NULL；

  key_len：表示MySQL选择的索引字段按字节计算的长度。

  rows：显示MySQL在表中进行查询时必须检查的行数；

  Extra：该列MySQL在处理查询时的详细信息。

使用索引查询

  数据库索引创建规则:

  1、表的主键、外键必须有索引；

  2、数据量超过300的表应该有索引；

  3、经常与其他表进行连接的表，在连接字段上应该建立索引；

  4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

  5、索引应该建在选择性高的字段上；

  6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替；

  8、频繁进行数据操作的表，不要建立太多的索引；

  9、删除无用的索引，避免对执行计划造成负面影响；

 

索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起作用。

  在以下情况下，有可能使用带有索引的字段查询时，索引并没有起作用：

  1.使用LIKE关键字的查询语句（左原则）

  在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。

  正例：select id from table where name like '123%';

  反例：select id from table where name like '%123';

2.使用多列索引的查询语句（左原则）

  MySQL可以为多个字段创建索引。对于多列索引，只有查询条件中使用了这些字段中第1个字段时，索引才会被使用，类电话簿。

3.使用OR关键字的查询语句

  查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

  4.全表扫描要比使用索引快,则不使用索引（智能选择）

  5.避免对列进行函数、计算等操作（列独立原则）

  如果需要在某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。

  正例：select * from table where id = 4

  反例：select * from table where id + 1 = 5

6.覆盖索引

  如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处；

  正例：select id,name from table

  索引：(id,name)

  7.删除冗余和重复索引

  冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。

  反例：有一个索引(A,B)，再创建索引(A)就是冗余索引。

8.使用索引扫描来排序

  MySQL有两种方式可以生产有序的结果集，其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。

  如果explain的结果中type列的值为index表示使用了索引扫描来做排序。

  正例：select  id,name from table where date = '2019-08-20' order by id,name

  索引：(date,id,name)

优化SQL

  1.优化COUNT查询

  最简单的就是当使用COUNT(*)时，并不是我们所想象的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

  正例：select count(*) from table

  反例：select count(id) from table

2.优化关联查询

  在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能。

3.优化LIMIT分页

  当需要分页操作时，通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY字句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

  正例：select id,name from product where id> 866612 limit 20 

  反例：select id,name from product limit 866613, 20

4.优化UNION

  MySQL处理UNION的策略是先创建临时表，然后再把各个查询结果插入到临时表中，最后再来做查询。

  除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。

  正例：select id from table a union all select name from table b

  反例：select id from table a union select name from table b

优化数据库结构

      分解字段很多的表

         对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个字段的数据量很大时，会由于使用频率低的字段的存在而变慢。

  示例：数据表拆分为概要信息表和详情表。

       增加冗余字段

  设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高查询速度。

  示例：数据表中同时包含地区编码和地区名称。

  提示：冗余字段会导致一些问题。比如，冗余字段的值在以一个表中被修改了，就要想办法在其它表中更新该字段。否则就会使原本一致的数据变得不一致。

优化插入记录的速度

  插入记录时，影响插入速度的主要是索引、唯一性检查、一次插入记录条数等。根据这些情况，可以分别进行优化。

  对于InnoDB引擎的表，常用的优化方法如下：

  1.禁用外键检查

  插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。

  禁用外键检查的语句如下：

  set foreign_key_checks = 0;

  恢复外键检查的语句如下：

  set foreign_key_checks = 1;

 2.禁用唯一性检查

  插入记录时，MySQL会对插入的记录进行唯一性校验。如果插入大量数据，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。

  禁止唯一性检查的语句如下：

  SET UNIQUE_CHECK = 0;

  开启唯一性检查的语句如下：

  SET UNIQUE_CHECK = 1;

3.禁用自动提交

  插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。

  禁止自动提交的语句如下：

  set autocommit = 0;

  恢复自动提交的语句如下：

  set autocommit = 1。

分析表、检查表和优化表

  MySQL提供了分析表、检查表和优化表的语句。分析表主要是分析关键字的分布；检查表主要是检查表是否存在错误；优化表主要是消除删除或者更新造成的空间浪费。

  1.分析表

  MySQL中提供了ANALYZE TABLE语句分析表，ANALYZE TABLE语句能够分析InnoDB、BDB和MyIsAM类型的表。

  ANALYZE TABLE语句的基本语法如下：

  ANALYZE TABLE table_name;

2.检查表

  MySQL中可以使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。

而且，CHECK TABLE也可以检查视图是否错误，比如在试图定义中被引用的表已不存在。

  CHECK TABLE语句的基本语法如下：

  CHECK TABLE table_name;

3.优化表

  MySQL中使用OPTIMIZE TABLE语句来优化表。该语句对InnoDB和MyISAM类型的表都有效。但是，OPTIMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段。

  通过OPTIMIZE TABLE语句可以消除删除和更新造成的文件碎片。

  OPTIMIZE TABLE语句的基本语法如下：

  OPTIMIZE TABLE table_name;

优化服务器硬件

服务器硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。针对性能瓶颈，提高硬件配置，可以提高MySQL数据库的查询、更新的速度。

  1.配置较大的内存；

  2.配置高速磁盘系统，以减少读盘的等待时间，提高相应速度；

  3.合理分布磁盘IO，被磁盘IO分散在多个设备上，以减少资源竞争，提高并行操作能力；

  4.配置多处理器，MySQL是多线程的数据库，多处理器可同同时执行多个线程。

优化MySQL参数

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。

  MySQL服务的配置参数都在my.cnf或者my.ini文件的[mysqld]组中。对性能影响较大的参数如下：

  max_connections：数据库的最大连接数（这个连接数不是越大越好，因为这些连接会浪费内存的资源，过多的连接可能会导致MySQL服务器僵死）；

  key_buffer_size：索引缓冲区的大小；

  table_cache：同时打开的表的个数；

  query_cache_size：查询缓冲区的大小；

  sort_buffer_size：排序缓冲区的大小； 

  read_buffer_size：每个线程连续扫描时为扫描的每个表分配的缓冲区的大小；

  read_rnd_buffer_size：每个线程保留的缓冲区的大小；

  innodb_buffer_pool_size：InnoDB类型的表和索引的最大缓存；

尽可能不要使用存储过程，存储过程非常不容易维护，也会增加使用成本，应该把业务逻辑放到客户端。既然客户端都能干这些事，那为什么还要存储过程？

JOIN本身也挺方便的，直接查询就好了，为什么还需要视图呢？