事务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查询过程:
优化简介
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本身也挺方便的,直接查询就好了,为什么还需要视图呢?