MySQL知识及简单优化

 最近在公司一直在负责短信和邮件的发送中心. 数据量..比较大. 当业务上需要用sql语句进行支持的时候,稍不注意就会造成查询缓慢,占用mySQL服务器资源的情况.  结合之前开发时候的一些微不足道的经验   总结一下

 

首先说一下MySQL的存储引擎,这在很多人的博客里面都已经提到过了. 之前有同事在面试的时候被闻到过Mysql的默认搜索引擎,他当时给我的答案是MyISAM, 后来看了一下  是InnoDB.  可能是因为版本的原因吧. 但是据我了解mysql5.5之后 default 都是InnoDB.

可以在对应的Mysql版本中使用 show ENGINES 进行查看. 下图是我的结果

由图可见 默认存储引擎是InnoDB.  结合公司的库 发现用的最多的就是三种 InnoDB   MyISAM  MEMORY.  我们也可以从图中看出,只有INNODB是支持事务的  这也是他最大的优势. 下面来对比下三种存储引擎.

INNODB:

（1） innodb存储引擎的mysql表提供了事务，回滚以及系统崩溃修复能力和多版本迸发控制的事务的安全。

（2）innodb支持自增长列（auto_increment）,自增长列的值不能为空，如果在使用的时候为空的话怎会进行自动存现有的值开始增值，如果有但是比现在的还大，则就保存这个值。

（3）innodb存储引擎支持外键（foreign key） ,外键所在的表称为子表而所依赖的表称为父表。

（4）innodb存储引擎最重要的是支持事务，以及事务相关联功能。

（5）innodb存储引擎支持mvcc的行级锁。

（6）innodb存储引擎索引使用的是B+Tree

MyISAM   这种存储引擎不支持事务，不支持行级锁，只支持并发插入的表锁，主要用于高负载的select。

只支持表锁意味着  当执行某一条SQL进行修改的时候,该表在这一瞬间是禁止任何形式的操作的,即

2、MyISAM   类型的表支持三种不同的存储结构：静态型、动态型、压缩型。

在这里记录一下 锁 的概念    刚和同事交流了一下留点印象.  关于表锁和行锁  其实是分  X(排他锁)  和  S(共享锁) .当一个数据源加载了排它锁后,会对其资源进行占用,其他请求会在其操作时间内无法获取资源. 所以说,MyISAM 只支持表锁的话会造成更多的资源占用,而INNODB支持行级锁,也就会占用更少的资源. 这种问题在共享锁中是不存在的. 共享锁支持在写的同时允许他人进行读操作,但这在并发量大的情况下会导致 脏读的出现(即写操作的IO进行到一半,另外一端便执行了读操作) 

另外S  X 两种锁 还会细分意向锁  IX 和 IS   也就是说  两个线程各可能存在4种锁的情况  那他们就会用4*4 =16种情况.

 

（1）静态型：就是定义的表列的大小是固定（即不含有：xblob、xtext、varchar等长度可变的数据类型），这样mysql就会自动使用静态myisam格式。

        使用静态格式的表的性能比较高，因为在维护和访问的时候以预定格式存储数据时需要的开销很低。但是这高性能是有空间换来的，因为在定义的时候是固定的，所以不管列中的值有多大，都会以最大值为准，占据了整个空间。

（2）动态型：如果列（即使只有一列）定义为动态的（xblob, xtext, varchar等数据类型），这时myisam就自动使用动态型，虽然动态型的表占用了比静态型表较少的空间，但带来了性能的降低，因为如果某个字段的内容发生改变则其位置很可能需要移动，这样就会导致碎片的产生。随着数据变化的怎多，碎片就会增加，数据访问性能就会相应的降低。

      对于因为碎片的原因而降低数据访问性，有两种解决办法：

     @1、尽可能使用静态数据类型

     @2、经常使用optimize   table语句，他会整理表的碎片，恢复由于表的更新和删除导致的空间丢失。

        (如果存储引擎不支持 optimize  table    则可以转储并重新加载数据，这样也可以减少碎片)

（3）压缩型：如果在这个数据库中创建的是在整个生命周期内只读的表，则这种情况就是用myisam的压缩型表来减少空间的占用。

3、MyISAM也是使用B+tree索引但是和Innodb的在具体实现上有些不同。

 

 

下面是一些创建表的技巧:

1. 在建表的时候选取合适的字段类型.比如存储一个数字类型的列  尽量使用int 类型,值可能会非常大的话就使用bigint

如果使用varchar 来存储的话 会是字符串的形式   假如某个值是168   引擎在扫描int类型时 只需一次比较即可 , 而varchar 会逐个进行比较, 当值越大时 逐个比较会造成性能上的浪费更多

2. 选择合适的存储字段大小.  例如我们存储一个客户名称 大致能推测出最大是长度40的varchar  那么建表的时候最好去匹配这个值,过大会造成空间上的浪费. 而过小会导致存储异常 在代码中会报一个sqlException,描述为data too long (亲测)

3.尽量使用自增的主键与其他表做关联. MySQL的索引分为两种,聚簇索引和非聚簇索引.  在做查询的时候,聚簇索引会比非聚簇索引要快一些. 可以这么理解,当我们在翻字典的时候有两种方式,一种是用拼音,一种是偏旁. 拼音查询出来的结果基本上就在一起的,而偏旁是散布在很多页的. 但是相比于逐页翻看(不使用索引) 都是快很多的.  使用索引会极大程度的加快查询速度. 从数据结构上分析,

聚簇索引实现方式中B+Tree的叶子节点上的data就是数据本身，key为主键，如果是一般索引的话，data便会指向对应的主索引,聚簇索引主要存在与INNODB中

而非聚簇索引的叶子节点存放的并不是data本身,而是其data对应的地址,再通过地址去寻找data,相比聚簇索引会多一层操作.

个人关于B+  B- 树 的理解还不是太深 ,计划本周末会对数据结构进行梳理

4. 使用comment 关键字对列进行注释描述方便查看DDL对表结构进行维护    并对列建立约束  例如 主键(primary key)  非空(not null)  唯一(unique)     默认值(default) 

5.创建 creat_ time字段 和modify_time 字段. 一方面是因为业务上可能有需要,另一方面是可以在时间上添加索引,方便查询效率.

就比如我现在会负责短信和邮件,每天的量会非常大  也会对时间的精度有一定的要求,那么 在时间列上添加索引就会非常有意义.

6.使用is_delete 等字段对数据进行逻辑删除 而不是物理删除.  这样的优势在于方便对数据进行恢复,有很多情况会造成用户误操作的时候,如果物理删除的话,恢复数据会变得非常困难,很多时候需要回滚,这样的影响会远比修改一个字段更复杂.

7.在常用字段上设置索引,但是也不要过多的设置索引.  我们知道,索引也是一种存储,会造成一些空间上的消耗,设置索引的目的主要是为了方便查询,提高查询效率. 但是 当一个表中存在过多的索引,会导致增删改操作消耗更多的时间,每次进行这些操作不仅要在磁盘上对数据进行I/O 操作,还要额外去维护索引关系. 听到过一种说法,即在一个较大的表中 当存在十个以上的列需要维护索引关系时,那么这个表的 查询/修改 占比是得不偿失的  虽然查询会快很多,但是其他操作也会慢上很多

8.分库分表, 记得看过一次MySQL的官方文档,上面写到MySQL的最大支持数据量也就到 千万级别, 当数据量过大的时候,建议进行分表操作, 否则在特殊场景下需要进行全表扫描时 , MySQL很有可能崩溃. 而分库的意义在于 方便维护业务关系,也可以进行一些主从的配置 或者读写分离  ,主表和从表的数据同步及订阅机制,缓存的处理   以此来影响数据库的效率

 

工作中常用的SQL语句优化经验:

网上会有很多提高SQL效率的帖子,很多都是直接看看别人博客  转载就好了   其实在实际开发的很多时候,提高sql效率大部分是为了一个目的, 充分使用索引

1.尽量少的使用 SELECT *   , COUNT(*) 这样的语句, 会造成扫描更多的字段和进行跟多的I/O操作. 

   替代方案可以采用 SELECT  必要字段   和  count 必要字段  或者 count(1) 来实现

2.在进行查询时, 拼接and 条件 顺序要匹配多列索引的顺序,否则会导致放弃索引而进行全表扫描.  如果是单列索引,那么and条件应优先匹配  否则也会浪费查询效率. 如果存在主键索引,应优先使用主键索引.

3. in 和exist  这个在开发中经常会遇到,自己问过公司的DB进行过了解.  大家主要会关注于in 和exist哪个效率更高,哪个会放弃索引.   看过一些资料  自己也做了一些Explain的测试  在Mysql 5.5版本之后 ,至少INNODB 中的in 关键字是会使用索引的.  有博客说在5.5之前,MySQL执行 in 语句会先转化成exist 语句 ,具体可自行查证.   那么in 和exist 哪个效率会更高一点呢?  

很多时候用in 大概是这么几种情况 

 select xx from table WHERE id in (select id from xx where xx= xx);

 sele  xx from table where id in (1,2,3,4);

第二种情况下 在同等索引条件下 一定是in 的查询效率更高一点的    而第一种方式需要分情况,当外层查询的范围较大时,in的效率会高一些,当子查询范围较大是  exist 效率会较高.  简单说一下in 的逻辑 , 使用in的语句会将子查询会外层查询做一个笛卡尔积,以此来返回结果.(具体笛卡尔积是什么再了解吧)   也就是说,子查询范围大的时候 会增大运算量 导致效率低..  并不意味着in 关键字会使得语句直接放弃索引.

4.尽量少的去使用联表查询  特别是尽量少的去使用union 这个关键字

A.union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当 我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union.

5. 尽量少在索引列使用函数, 函数会导致查询语句放弃索引而进行全表扫描

6.在使用left join 和 right join 的时候  分清楚是以左表为主表还是以右表, 精确的返回所需值. 否则会导致查询结果不精确 甚至造成多余的资源浪费

7.尽量少的使用 OR 关键字

8. 使用LIMIT 来进行物理分页可以快速的查到结果

    例如我想通过某个 短信的模板号来查询发送给某个用户的id . 有可能一个模板发给这个用户很多条 

  当我使用 LIMIT 1 在语句结尾时, 引擎便会在扫描到一条结果后直接返回 ,而不会继续进行扫描,这样的情况下使用LIMIT的效率是很高的.

 

缓存.

使用数据库缓存会在一定程度上减小数据库的访问量. 但缺点是,如果对某个表修改频率很高,则需要不断的刷新缓存,而访问频率远没有维护缓存的成本高,这时便是一种浪费了

Innodb会对每个表设置一个事务计数器,里面存储当前最大的事务ID.当一个事务提交时,InnoDB会使用MVCC中系统事务ID最大的事务ID跟新当前表的计数器. 
只有比这个最大ID大的事务能使用查询缓存,其他比这个ID小的事务则不能使用查询缓存. 
另外,在InnoDB中,所有有加锁操作的事务都不使用任何查询缓存 
查询必须是完全相同的(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同 默认字符集的查询被认为是不同的查询并且分别进行缓存。