MySql优化那些事

MySql如何工作

Mysql客户端和服务器之间的通信协议是“半双工”的,意味着在任何一个时刻,要么是由服务器向客户端发送数据,要么是有客户端向服务器发送数据,这两个动作不能同时发生。当我们发送一条SQL命令引擎时,MySql执行过程如下图所示
图片描述

  1. 客户端发送一条查询给服务器。
  2. 服务器先会查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果。否则进入下一阶段。
  3. 服务务器端进行SQL解析、预处理,再由优化器生成对应的执行计划。
  4. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。
  5. 将结果返回给客户端。

查询缓存
在解析一个查询语句之前,如果查询缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据,如果命中缓存直接从缓存中拿到结果并返回给客户端。这种情况下,查询不会被解析,不用生成执行计划,不会被执行。

语法解析和预处理器
MySQL通过关键字将SQL语句进行解析,并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。

查询优化器
语法书被校验合法后由优化器转成查询计划,一条语句可以有很多种执行方式,最后返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

查询执行引擎
在解析和优化阶段,MySQL将生成查询对应的执行计划,MySQL的查询执行引擎则根据这个执行计划来完成整个查询。最常使用的也是比较最多的引擎是MyISAM引擎和InnoDB引擎。mysql5.5开始的默认存储引擎已经变更为innodb了。

下面简单的罗列几点差别:MyISAM类型不支持事务处理等高级处理,强调的是性能,InnoDB支持事物;MyISAM支持表级锁,InnoDB支持行级锁;MyISAM不支持外键,InnoDB支持外键(虽然不建议使用外键约束)。
查询当前默认的引擎可以通过SHOW VARIABLES LIKE '%storage_engine%'语句来查看。

InnoDB事物和锁

InnoDB是支持事务的,那么到底什么是事务呢?
数据库事务(Database Transaction) ,是指作为单个逻辑工作单元执行的一系列操作,要么完全地执行,要么完全地不执行。

事物的基本特性(ACID)
原子性(atomicity):
事务中的操作要么都发生,要么都不发生。
一致性 (consistent):
在事务开始和完成时,数据必须保持一致状态(读一致、写一致)。
隔离性 (isolation):
隔离性指并发的事务是相互隔离的。一个事物操作不能对另一个事物产生影响。
持久性(durable):
事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

事务的隔离级别
如果不考虑事物的隔离级别会出现以下三种异常情况

  1. 不提交读(脏读)
    不提交读是指在一个事务处理过程中读取了另一个未提交的事务中的数据。
  2. 提交读(不可重复读)
    这种情况是一个事务范围内多次查询却返回了不同的数据值,这是由于在查询间隔,被另一个事物修改并提交了。
  3. 幻读
    事物T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作,这是T2又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务T2中添加的,就好像产生幻觉一样,这就是发生了幻读。

幻读和提交读都是读取了另一条已经提交的事物(这一天与不提交读有区别),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体。

Mysql数据库为我们定义了四种隔离级别

  1. Serializable (串行化):它通过强制事物串行执行可避免不提交读、提交读、幻读的发生。
  2. Repeatable read (可重复读):可避免不提交读、提交读的发生。
    该级别在保证了在同一个事物中多次读取同样的记录结果是一致的。
  3. Read committed (已提交读):可避免脏读的发生。
    一个事物开始时,只能看见已提交的事物所做得修改。这个级别会出现已提交读的现象。
  4. Read uncommitted (未提交读):最低级别,任何情况都无法保证。
    事物中的修改,即使没有提交,对其他事物都是可见的,这种隔离级别会出现脏读。在实际的应用中一般很少使用。

Repeatable read是mysql默认的隔离级别

InnoDB的锁分类及加锁方法
一、锁分类
InnoDB的锁定模式可以分为四类

  1. 共享锁(S)又叫读锁
    对同一行数据可以共享一把锁,没有获得锁的事务只可以读,不可以修改
  2. 排他锁(X)又叫写锁
    对同一行数据,获得该锁的事务可读可写,未获得锁的事务不可读也不可写.
  3. 意向共享锁(IS)
    事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁
  4. 意向排他锁(IX)
    事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

按照锁定级别划分为
1.表锁
开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;适合于以查询为主,只有少量按索引条件更新数据的应用。
例如MyISAM引擎在执行查询语句的时候会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT)前,自动给涉及的表加写锁。
2.行锁
开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;适合有大量按索引条件并发更新情况。
InnoDB的行级锁定同样分为两种类型,共享锁和排他锁,而在锁定机制的实现过程中为了让行级锁定和表级锁定共存,InnoDB也同样使用了意向锁(表级锁定)的概念,也就有了意向共享锁和意向排他锁这两种。
当一个事务需要给自己需要的某个资源加锁的时候,如果遇到一个共享锁正锁定着自己需要的资源的时候,自己可以再加一个共享锁,不过不能加排他锁。但是,如果遇到自己需要锁定的资源已经被一个排他锁占有之后,则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。
3.页锁
开销和加锁时间界于表锁和行锁之间,我们很少场景下会使用。
锁定级别的选择是由存储引擎来自行选择的。
4.间隙锁
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。
例如:select * from emp where empid > 100 for update;
是一个范围条件的检索,InnoDB不仅会对符合条件的empid值为101的记录加锁,也会对empid大于101(这些记录并不存在)的“间隙”加锁。

二. 加锁方法
意向锁是InnoDB引擎自动加的,不需要用户干预。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁:SELECT … LOCK IN SHARE MODE
排他锁:SELECT … FOR UPDATE
InnoDB行锁是通过给索引上的索引项加锁来实现的,只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。以下几种情况需要避免:

  1. 在不通过检索条件检索的时候,InnoDB确实使用的是表锁,而不是行锁。
  2. 由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。
  3. 当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
  4. 即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。

三.死锁
Mysql MyISAM总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。但在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,当两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。
通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小,以及访问数据库的SQL语句,绝大部分死锁都可以避免。
需要注意:

  1. 不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。
  2. 在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。
  3. 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。

SQL优化技巧

数据类型优化
更短的列通常更好,例如varchar(5)和varhchar(200)存储hello的空间开销是一样的,事实证明更长的列会消耗更多的内存,因为MySQL通常会分配固定大小的内存块来保存内部值。

使用缓存表和汇总表
以网站为例,假设需要计算之前24小时内发送的消息数,在一个繁忙的网站不可能维护一个实时精确的计算器,可以每一小时统计一次,然后把24个小时访问数叠加。

创建高性能的索引

1.使用独立的列
如果不是独立的列,则MySQL就不会使用索引
例如:
Select actor_id from actor where actor_id + 1 = 5;无法使用actor_id列的索引

2.联合索引
很多人对多列索引的理解不够,常见的错误是为每个列创建独立的索引,或者按错误的顺序创建多列索引。当出现服务器对多个索引做相交操作时(通常是多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个单独的单列索引。
当服务器需要多个索引做联合操作时(通常有多个OR条件),通常需要耗费大量的CPU和内存资源。通常有两种做法:
①使用union all
Select film_id,actor_id from film_actor where actor_id
Union all
Select film_id,actor_id from film_actor where film_id =1 and actor_id <>1;
②分别对film_id 和 actor_id创建索引。

3.选择合适的索引列顺序
Select * from payment where staff_id = 2 AND customer_id = 584
应该创建一个(staff_id,customer_id)索引还是应该颠倒一下顺序?哪个字段选择性更高,选择哪个字段作为索引列的第一列。customer_id对应筛选掉的数据更多,则索引customer_id应该放在第一位。

4.冗余和重复索引
索引并不是越多越好,过多的索引会对系统产生负担,一直未使用的索引应该把它删掉,而不是留在我们的系统中。
例如有一张参保人表里面有100000行数据,每个state_id值大概有20000条记录。在state_id列有一个索引对下面查询有用,查询名为Q1:
Select count(*) from table where state_id = 5;
一个简单的测试查询的执行速度每秒115次(QPS)。还有一个查询
Select state_id,city,address from userinfo where state_id = 5;
对于这个查询,测试结果QPS小于10。提升性能最简单的办法就是扩展索引为(state_id,city,address),索引扩展后Q2变快了,但是Q1变慢了,如果我们想让两个查询都变得更快,就需要两个索引。多个索引的缺点是索引的成本更高。向表中插入100万数据,InnoDB引擎在一个索引情况下需要80秒,两个索引的情况下需要136秒。可以看出表中的索引越多插入速度越慢。

索引和锁

我们在写查询语句的时候,要利用索引锁住最小范围内的行。
例如:
Select actor_id from sakila.actor where actor_id < 5
And actor_id <> 1 for update
这条语句仅仅返回 2-4,但实际上获取了1-4之间的行的排他锁,InnoDB 会锁住第1行,这是因为MySQL为该查询选择的执行计划是索引范围扫描。
就像这个例子显示的,即使使用了索引,InnoDB也可能锁住一些不需要的数据,如果不使用索引查找和锁定行的话问题可能会更糟,mysql会做全表扫描并锁住所有的行,而不管是不是需要。避免使用多个范围条件:
例如:select actor_id from actor where actor_id > 45
如果能改成select actor_id from actor where actor_id in (46,99)
这两种范围条件查询,MySQL无法再使用范围列后面的索引了,但是对于“多个等值条件查询”则没有这个限制。如果多个范围查询则索引就无能为力了。

查询性能优化

  1. 避免查询不需要的记录,需要多少查多少,建议使用limit
  2. 不要总是取出所有的列,不要轻易使用select * ,要判断是不是自己需要的列。
  3. 将以此大的工作量拆分成多个小的工作量
    例如一个大的事物,可以分拆成几个小事物执行,这样也可以将服务器原本一次性的压力分散到一个很长的时间段中,可以大大降低对服务器的影响,还可以减少。

执行计划

执行计划可以查看数据软件是如何扫描表,如何使用索引的,因此明白MySql语句的执行计划,对我们优化mysql的性能非常有用。
查看执行计划可以使用如下语句:
EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176G; 这样的命令时,会给我们反馈如下信息:
图片描述
Key: 列指出优化器选择使用的索引
Rows:用于分析结果集中的行数估计值,最好估值是1,一般来说这种情况发生在当寻找的行在表中可以通过主键或者唯一键找到的时候。
Possible_keys:指出优化器为查询选定的索引
Key_len: 列定义了用于SQL语句的连接条件的键的长度。
Table:这个值可能是个是表明、表的别名或者一个为查询产生临时表的标识符
Select_type:
提供了表示table列引用的使用方式的类型。最常见的值包括simple、primary,derived和union

  1. simple对于不包含子查询和其他复杂语句的简单查询
  2. primary 这是为复杂的查询而创建的最外层表
  3. derived当一个表不是物理表时叫做derived,派生表一个子查询
  4. dependent subquery 这个代表是子查询
  5. union 这是union语句其中的一个SQL元素

extra
extra列提供了有关不同种类的MySQL 优化器路径的一系列
额外信息,下面给 出常用值的列表

  1. Using where
    这个值表示查询使用了where语句来处理结果
  2. Using temporary
    这个值表示使用了内部临时表
  3. Using index
    这个值重点强调了只需要使用索引就可以满足查询表的要求,不需要直接访问数据表数据。
  4. Distinct
    这个值意味着MySql在找到第一个匹配的行之后就会定制搜索其他行。

评论

暂无评论