评价
(16) (11)

MySQL 优化问题

一 索引

索引在数据库中的作用类似于字典前面的拼音目录,目的是提高查找数据的速度。例如,在含10W条数据的

数据表中寻找name='张三'的数据信息,如果不建立索引,则Mysql需要逐条扫描10W条数据,如果在name列

中建立索引,则MySQL只需要扫描一行数据便可以找到这条数据。大大减少了查询时的I/O次数,提高了查询速度。

1.唯一索引:

创建唯一索引的目的是避免数据重复,例如我们的身份证号都是唯一的。唯一索引通过UNIQUE关键字进行标识,它

的索引列必须唯一,但允许有空值。容易混淆的是主键索引,主键索引是特殊的唯一索引,通过PRIMARY KEY关键字标识,

主键索引的索引列不允许有空值。

2.联合索引:

使用联合索引也可以提高查询效率。Mysql查询时只能使用一个索引,若查询age>20,sex=’male'的数据信息,若在age和sex

列上建立单列索引,则只能使用一个索引,查询另一字段信息就需要返回到主表中查询。需要注意的是,联合索引匹配最左优先

原则,若查询条件是联合索引列的后几列,也不会使用到索引。建立联合索引时,一般把Where查询使用最频繁的列放到最左边。

  若某表联合索引是(A,B,C)
  查询 where B=1   查询不使用索引
  查询 where A=1,B=2,D=4   用到(A,B)索引,但是索引表中没有D的值,所以需要回到主表查询数据。
  查询 where A=1,C=2  只能用到A的索引,索引表中有C的值,所以不用回主表查询就可以查询到符合条件的数据
  查询 where A=1,B=2  用到(A,B)索引,也不用回主表再查询

主表比较大,在查询主表时需要多次的I/O操作,所以在查询的过程中,要尽量避免扫描全表。

3.函数索引:

有时,我们需要对列进行函数运算作为查询条件。当索引不是某列,而是建立在列上的函数,则在搜索此查询条件时,通过

这种函数索引,提高查询效率。

例如:我们系统中从平台编码映射到系统商家编码是通过将平台上的货品编码和规格编码进行 截取/拼接 等规则然后形成系统中的

商家编码,而且通过商家编码查询我们经常用到。若将这个规则看成一个函数,函数结果即为商家编码,则相当于在商家编码上建立了索引。

但Mysql并不支持这种索引,Mysql 中对于这种需求解决方式为:将函数结果作为新的一列存储。即A,B为列,建新列C=function(A,B),

然后再在列C上建立索引。

4.条件索引:

条件索引是在索引列上根据WHERE条件进行一定过滤后产生的索引。对于不满足条件的不建立索引,这样条件索引比全部建立索引要节省空间。

例如:我们系统中订单有不同状态,在搜索时,可能更关心待审核的订单,而已取消的订单关注较少。并且他们订单的数量也是不平均的。

则我们希望在订单状态status=1,status=2情况下对订单建索引,而status=3时对订单不建立索引。这时,条件索引就可以使用了。

不过,同样 Mysql也不支持这种索引。对于这种情况,Mysql只能将status=1,status=2的订单单据建立一张表,在这张表上建立索引。

5.分页查询

Mysql中可使用Limit M,N 的方式进行分页,但若使用 Limit 10000,10,则Mysql会先进行排序找到前10010

条记录 然后再返回最后10条记录。这种方式会进行全表扫描,只适合数据量较小的表,若数据量很大,则排序和查找的代价都很高。

对此,我们可以在索引上完成排序和分页,然后和主表关联, 即通过 select * from a left join

( select id from a limit 10000,10) b on a.id=b.id 方式提高查询效率。 索引文件较小,占用磁盘少,对索引进行操作效率更高。

若按照排序字段id顺序或逆序查找时,可增设一个参数记录下每一页的id的最大值。在搜索某一页时,直接计算得出当前id的范围

进行查找。这样,搜索出来的结果就变成了 limit N 的形式,从头查找,效率更高。 例如。我们记录下第50页最大的id=13232,若

想查找第51页数据就直接可以使用 select * from a where id>13232 order by id limit 10进行查询。

注意

1.若查找的条件以通配符开头,即当查询条件类似于 WHERE name LIKE '%三' 时,MySQL不会使用索引进行查询。

2..索引不能无限制的增加:在对数据进行增删改时,创建和维护索引需要时间,而且索引也会占用物理空间。索引越多,速度不一定越快。

3.查询操作不要连太多表。

二 事务

事务是一组连续的数据库操作,具有原子性。若事务处理过程中出现错误,则数据库信息会回到事务执行前的起始状态。

若使用InnoDB存储引擎,Mysql默认通过变量autocommit=1自动提交事务,即对每个查询都作为一个事务进行提交。

若要显式的开始一个事务,要通过commit和rollback对事务进行手动提交和回滚。而当autocommit=0时,直到显式的执行commit

为止,所有的查询都是在一个事务中进行的。

事务在处理过程中会记录日志。rollback就是根据日志进行 反写操作的。

start transaction / commit / rollback

使用事务可以保证数据库的完整性。

1. 事务 注意 粒度的概念。commit 次数太多,或者内容太多,都不合适。速度不会快。

而且 事务 内容太多,相关表操作也会变多,越容易冲突。

2.事务 开始 之后会写日志,成功了日志才会取消,失败会按照日志rollback。

eg: insert into … select XXX 时, 数据量 特别大时(1.0迁2.0), 在事务过程中,

日志就会特别特别大。。整体速度比较慢。

解决方法: 每次处理500条,外层加循环执行事务。这样,由大事务变成小事务,速度提升了。

3. SQL语句的执行速度和数据量是有关系的,一行update语句也可能更新整个表。

对于一行SQL也有事务的概念,例如update 一张表,如果其中一行 有冲突或者错误, 整个语句也会返回错误,之

前update的数据也会rollback,如果正确则会立即执行。这说明普通的Sql语句默认是自动提交的。而对于事务 是通过

commit 手动提交的(自动提交是保存在一个autocommit的变量内,可以进行修改)。

三 锁-并发操作

当多用户访问同一数据库时,可能同时操作同一张表,同一行数据。这时就要用到锁机制来保证数据的安全正确读写。

1.排他锁

当一个用户在事务中要修改某个数据时,必须要保证其他用户不能对这个数据进行操作,否则就会造成数据覆盖,发生错误的读写。这时

就要用到 排他锁 来保证数据修改的原子性。排他锁 即 写锁,它的锁粒度是行/元组。 若事务T对数据行A 添加了 排他锁,则 事务T

既可以读A也可以修改A,而其他事务不能再对A加任何锁,直到T释放A上的锁。这样,其他事务在T释放A上的锁之前都不能再修改A。

不过,值得注意的是,其他事务不能再在A上加锁,但是可以读取A被修改之前的数据,这是因为普通的读是不加锁的。

排他锁的用法:SELECT … FOR UPDATE; 在查询语句后面增加FOR UPDATE,Mysql就会对查询结果中的每行都加上排他锁。如果其他线程

再试图对已加锁的数据加锁,则会被阻塞。

例如:采购单审核时要执行以下事务。不加锁的话,如果两个人同时对一个订单进行审核,则会有两个线程同时执行此事务,会造成重复

记录日志。

   start transaction
      select         //选择要审核的行
      update         // 更新采购单状态
      insert log     //记录日志
   commit

通过 select XX for update 排他锁机制,对要审核的行加上排他锁。则其中一个线程操作时,另一个线程会阻塞直到此线程释放锁。当释放

锁后另一个线程执行时,通过select读取状态时,采购单就是已审核的状态,不会再次执行审核。

2.共享锁

共享锁即读锁,它允许多个线程并发读数据,但是不允许线程修改数据,直到所有的读操作全部完成,释放共享锁。即若事务T对数据行A加上

共享锁,则事务T可以读A但不能修改A,其他事务也能再对A加共享锁,但是不能加排他锁,直到共享锁全部被释放。共享锁的用法:

SELECT … LOCK IN SHARE MODE 。在查询语句后面增加LOCK IN SHARE MODE,Mysql就会对查询结果中的每行都加上共享锁,若数

据没有被加上排他锁,就可成功申请到共享锁,允许多个线程读数据,若数据有排他锁,则此线程会被阻塞等待排他锁释放后才能加共享锁。

例如,若在一个事务T1中 select A,然后update A。 但是在另一个事务T2中 select A,update B=A, 若不加共享锁,两事务并发执行,

则B的值可以为原值A,也可能为修改后的A值。通过共享锁机制,在T2事务 数据A上加上共享锁,则事务T1中 update A 操作会被阻塞,

直到A上的共享锁被释放。

3. 死锁 死锁不可避免,但是死锁概率不能高

 eg:A:start transaction   B:start transaction
        update   a=1	        update  a=2
        update   a=2	        update  a=1 
        commit                 commit		

死锁: 如果程序没有逻辑顺序,统一顺序就不会死锁了,但是如果程序有逻辑顺序,尽可能规避。

 eg:update XX where A1     update XX where A2 

当 A1,A2 筛选出结果集有交集时,也 可能 死锁。 通过 update order by 解决。不过如果联表

不能用 order by。

insert into XX (select … ) on duplicate key update 也可能会造成死锁。

慢查询

eg:写事务了 没有结束掉事务,锁了没有释放。

show engine innodb status ; 可以看所有的事务id,连接id。

可以看到 事务的活跃时间,如果事务活跃时间过长,则可能是由于事务太大 执行内容太多或者没有commit。

四 代码优化

1. 看代码,分析优化

一个查询语句可能有多种实现方式,通过分析测试,选择较优的SQL语句实现其功能。

2. 压力测试

压力测试是采用专门的工具对系统进行评测。通过压力测试可以查出一些问题,在此基础上进行优化。

3. 产品本身功能取舍,放弃精准度进行优化

有些功能在实现时,要花费较大的代价。所以,在实现功能时,我们要权衡功能本身的设计,客户使用情况以及功能的具体实现。在一定范围内,

降低精确度,提高效率。例如系统中的订单同名未合并。我们没有必要精确到某个时间段内的订单进行合并。若有马上将发货的订单,同时又有

新订单,是否进行合并都要根据实际情况而定。而且,对于此功能,并不是每个客户所必须的。所以,可以通过功能的设计来提高产品的质量。

附:分布式领域CAP理论

Consistency(一致性),Availability(可用性),Partition tolerance(可靠性)

任何分布式系统 只能同时满足以上两点,没法三点同时满足。