yyyyyyyyyyyyyyyyyyyy

博客园 首页 新随笔 联系 订阅 管理
MySQL 性能调优

 

MySQL 性能调优

 

索引

索引是什么

官方介绍索引是帮助MySQL高效获取数据的数据结构。
笔者理解索引相当于一本书的目录，通过目录就知道要的资料在哪里，不用一页一页查阅找出需要的资料。

索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。
如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。
它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。
数据库应该选择怎么样的方式来应对所有的问题呢？

我们回想字典的例子，能不能把数据分成段，然后分段查询呢？
最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。
但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。
但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，
数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，
因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

磁盘IO与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，
磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；
旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；
传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。
那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，
但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，
换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。
下图是计算机硬件延迟的对比图，供大家参考：

 

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，
当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，
因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。
每一次IO读取的数据我们称之为一页(page)。
具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

索引的数据结构

前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景。
我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

详解b+树

如上图，是一颗b+树，关于b+树的定义可以参见B+树。
这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），
如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，
如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项29，
那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，
在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，
通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，
29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，
同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，
如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；
而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。
这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。
这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。
当数据项等于1时将会退化成线性表。

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，
b+数是按照从左到右的顺序来建立搜索树的，
比如当(张三,20,F)这样的数据来检索的时候，
b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；
但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，
因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，
但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，
这个是非常重要的性质，即索引的最左匹配特性。

索引是不是越多越好？

索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据。
假设我们在更新表中有字段的同时，也更新索引数据，调整因为更新所带来键值变化后的索引信息。
而如果我们没有对字段进行索引的话,MySQL 所需要做的仅仅只是更新表中字段 的信息。
这样，所带来的最明显的资源消耗就是增加了更新所带来的IO量和调整索引所致的计算量。

此外，索引是需要占用存储空间的，而且随着数据量的增长，所占用的空间也会不断增长。
所以索引还会带来存储空间资源消耗的增长。

什么场景应该加索引？加索引的四个原则

1. 较频繁的作为查询条件的字段应该创建索引

提高数据查询检索的效率最有效的办法就是减少需要访问的数据量，从上面所了解到的索引的益处中我们知道了，索引正是我们减少通过索引键字段作为查询条件的Query 的IO 量的最有效手段。所以一般来说我们应该为较为频繁的查询条件字段创建索引。

2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

唯一性太差的字段主要是指哪些呢？
如状态字段，类型字段等等，这些字段中存方的数据可能总共就是那么几个几十个值重复使用，每个值都会存在于成千上万或是更多的记录中。
对于这类字段，我们完全没有必要创建单独的索引的。
因为即使我们创建了索引，MySQL Query Optimizer 大多数时候也不会去选择使用，
如果什么时候MySQL Query Optimizer 抽了一下风选择了这种索引，那么非常遗憾的告诉你，这可能会带来极大的性能问题。
由于索引字段中每个值都含有大量的记录，那么存储引擎在根据索引访问数据的时候会带来大量的随机IO，甚至有些时候可能还会出现大量的重复IO。

3. 更新非常频繁的字段不适合创建索引

上面在索引的弊端中我们已经分析过了，索引中的字段被更新的时候，不仅仅需要更新表中的数据，同时还要更新索引数据，以确保索引信息是准确的。
这个问题所带来的是IO 访问量的较大增加，不仅仅影响更新Query 的响应时间，还会影响整个存储系统的资源消耗，加大整个存储系统的负载。

4. 不会出现在WHERE子句中的字段不创建索引

查询时，不会命中索引。那么索引就没有存在的意义了。

创建索引的举例说明

CREATE TABLE `v9_member_menu` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,    # 主键标识
  `name` char(40) NOT NULL DEFAULT '',                  # 菜单名称
  `parentid` smallint(6) NOT NULL DEFAULT '0',          # 父级ID
  `m` char(20) NOT NULL DEFAULT '',                     # 模型名称
  `c` char(20) NOT NULL DEFAULT '',                     # 控制器名
  `a` char(20) NOT NULL DEFAULT '',                     # 方法名
  `data` char(100) NOT NULL DEFAULT '',                 # 附加数据
  `listorder` smallint(6) unsigned NOT NULL DEFAULT '0',# 排序值
  `display` enum('1','0') NOT NULL DEFAULT '1',         # 是否显示
  `isurl` enum('1','0') NOT NULL DEFAULT '0',           # 是否是一个链接
  `url` char(255) NOT NULL DEFAULT '',                  # 链接地址
  PRIMARY KEY (`id`),
  KEY `listorder` (`listorder`),     
  KEY `parentid` (`parentid`),
  KEY `module` (`m`,`c`,`a`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

关于 菜单的使用场景， 我做出如下整理

  1. 会根据 url分割出 m,c,a 然后进行查询菜单ID，再关联权限表，查询是否有权限。
  2. 根据 菜单ID 获取菜单信息，例如 删，改，查的应用场景
  3. 会根据菜单的父级ID 查询父级信息， 或者同自己的ID 查询子级信息。
  4. 显示菜单时，通常会进行排序。

  • 第一个情况 就符合 ，创建复合索引的条件，在where中经常会一起出现，
    例如 m=home and c=index and a=login

  • 第二个情况 可以使用主键索引，主键本身就自带索引属性。

  • 第三个情况，在查询子级时 通常会使用到。

  • 第四个情况： 排序也经常使用到。

  • data 和 url 为何不加索引？
    data 和 url 属于详细内容， 一般只用于展示，不会加入到where条件查询中，所以不需要加索引。

  • display 和 isurl 为何不加索引
    display 和 isurl 一样 他的数值很单一，不是1就是0，没必要加索引，而且符合条件的数据有很多，给mysql带来大量的随机IO。

索引的类型

聚簇索引和非聚簇索引

索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；
聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

聚簇索引是一种数据存储方式，它实际上是在同一个结构中保存了B+树索引和数据行，InnoDB表是按照聚簇索引组织的（类似于Oracle的索引组织表）。
InnoDB通过主键聚簇数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个主键作为聚簇索引。

下图形象说明了聚簇索引表(InnoDB)和非聚簇索引(MyISAM)的区别：

 

对于非聚簇索引表来说（右图），表数据和索引是分成存储的，主键索引和二级索引存储上没有任何区别。

而对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据，二级索引的叶结点存储行的主键值。

聚簇索引表最大限度地提高了I/O密集型应用的性能，但它也有以下几个限制：

1）插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。
因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。

2）更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

3）二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。

聚簇索引的叶节点就是数据节点，而非聚簇索引的页节点仍然是索引检点，并保留一个链接指向对应数据块。
**聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。 **

相比之下，聚簇索引适合排序，非聚簇索引不适合用在排序的场合。
因为聚簇索引本身已经是按照物理顺序放置的，排序很快。
非聚簇索引则没有按序存放，需要额外消耗资源来排序。
当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。

主键索引(PRIMARY KEY )

主键自带索引属性。 不管是 修改查询删除 基本都会用到它。

普通索引(Normal)

这是最基本的索引，它没有任何限制，比如上文中为listorder字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

实例

–直接创建索引
CREATE INDEX index_name ON table(column(length))

–修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

–创建表的时候同时创建索引
CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,
    
    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    
    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    
    `time` int(10) NULL DEFAULT NULL ,
    
    PRIMARY KEY (`id`),
    
    INDEX index_name (title(length))
)

–删除索引
DROP INDEX index_name ON table

唯一索引(Unique)

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。
如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

例如：用户表的 用户名 和 邮箱 都可以进行唯一索引

实例
–创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))

–修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

–创建表的时候直接指定
CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,
    
    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
    
    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
    
    `time` int(10) NULL DEFAULT NULL ,
    
    PRIMARY KEY (`id`),
    
    UNIQUE indexName (title(length))

);

全文索引(Full Text)

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；
他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，
或是随后使用ALTER TABLE 或CREATE INDEX被添加。

对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。
不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

在数据量不是很大的情况下 可以利用 全文索引做 站内搜索。
但是得先分词，才能进行全文检索。检索时 是通过 空格来分割词汇。
最好是 新建一个关联表（其中 存储分词的字段 用全文索引），把分词后的内容 用 空格分割 存储到 关联表，然后对应原始表。

查询流程如下

  1. 查询关联表
  2. 获取所有能查到的 文章ID
  3. 根据文章ID 获取文章数据

也可以配合第三方的检索插件 来进行全文检索
packagist.org 搜索中文分词

小项目可以使用 结巴分词

单列索引 和 复合索引

多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
即 mysql 底层自己会判断 使用那个索引 速度会更快

组合索引（最左前缀）

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。
例如上表中针对title和time建立一个组合索引：

    ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))

建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
–title,time
–title

为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。
简单的理解就是只从最左面的开始组合。
并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：

–使用到上面的索引
SELECT * FROM article WHREE title='测试' AND time=1234567890;
SELECT * FROM article WHREE utitle='测试';

–不使用上面的索引
SELECT * FROM article WHREE time=1234567890;

自创索引表

如果又需要可以自创 索引表（关联表）。

例如 现在有一个文章表， 需要做一个文章的站内搜索
那么 我们需要新建一个文章表

CREATE TABLE `article` (
  `id` int(11) unsigned NOT NULL COMMENT '主键',
  `title` varchar(255) NOT NULL COMMENT '标题',
  `author` varchar(255) NOT NULL DEFAULT '' COMMENT '作者',
  `content` text NOT NULL COMMENT '内容',
  `create_time` int(11) unsigned NOT NULL COMMENT '创建时间',
  `update_time` int(11) unsigned DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建一个 分词索引表

CREATE TABLE `article_participle` (
  `id` int(11) NOT NULL,
  `article_id` int(11) unsigned NOT NULL COMMENT '文章表ID ',
  `participle` varchar(1000) NOT NULL COMMENT '关键词 以空格分隔',
  PRIMARY KEY (`id`),
  UNIQUE KEY `article_id` (`article_id`) USING BTREE COMMENT '文章ID',
  FULLTEXT KEY `participle` (`participle`) COMMENT '中文分词存储'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

先根据 搜索的关键词 搜索 分词索引表
然后在根据搜索出的结果 （article_id 文章ID） 搜索文章表

索引方法

BTree 索引特征

BTree索引可以被用在像=,>,>=,<,<=和BETWEEN这些比较操作符上。而且还可以用于LIKE操作符，只要它的查询条件是一个不以通配符开头的常量。像下面的语句就可以使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

下面这两种情况不会使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

第一条是因为它以通配符开头，第二条是因为没有使用常量。

假如你使用... LIKE '%string%'而且string超过三个字符，MYSQL使用Turbo Boyer-Moore algorithm算法来初始化查询表达式，然后用这个表达式来让查询更迅速。

一个这样的查询col_name IS NULL是可以使用col_name的索引的。

任何一个没有覆盖所有WHERE中AND级别条件的索引是不会被使用的。也就是说，要使用一个索引，这个索引中的第一列需要在每个AND组中出现。

下面的WHERE条件会使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
    /* 优化成 "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
    /* 可以使用 index1 的索引但是不会使用 index2 和 index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面的WHERE条件不会使用索引：

    /* index_part1 没有被使用到 */
... WHERE index_part2=1 AND index_part3=2

    /* 索引 index 没有出现在每个 where 子句中 */
... WHERE index=1 OR A=10
    /* 没有索引覆盖所有列 */
... WHERE index_part1=1 OR index_part2=10

有时候mysql不会使用索引，即使这个在可用的情况下。
例如当mysql预估使用索引会读取大部分的行数据时。（在这种情况下，一次全表扫描可能比使用索引更快，因为它需要更少的检索）。
然而，假如语句中使用LIMIT来限定返回的行数，mysql则会使用索引。
因为当结果行数较少的情况下使用索引的效率会更高。

位图索引 (HASH)

Hash类型的索引有一些区别于以上所述的特征：

1.相对于BTree索引,占用的空间非常小,创建和使用非常快。
位图索引由于只存储键值的起止Rowid和位图,占用的空间非常少。

2.不适合键值较多的列。

3.不适合update、insert、delete频繁的列。

4.可以存储null值。
BTree索引由于不记录空值,当基于is null的查询时,会使用全表扫描。
而对位图索引列进行is null查询时,则可以使用索引。

5.当select count(XX) 时,可以直接访问索引中一个位图就快速得出统计数据。

6.当根据键值做and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算,快速得出结果行数据统计。

7.它们只能用于对等比较，例如=和<=>操作符（但是快很多）。它们不能被用于像<这样的范围查询条件。假如系统只需要使用像“键值对”的这样的存储结构，尽量使用hash类型索引。

8.优化器不能用hash索引来为ORDER BY操作符加速。（这类索引不能被用于搜索下一个次序的值）

9.mysql不能判断出两个值之间有多少条数据（这需要使用范围查询操作符来决定使用哪个索引）。假如你将一个MyISAM表转为一个依靠hash索引的MEMORY表，可能会影响一些语句（的性能）。

10.只有完整的键才能被用于搜索一行数据。（假如用B-tree索引，任何一个键的片段都可以用于查找）。

去索引化

为了更好的提高并发量，又产生了另一个思想！去索引化。
去索引化，并不是真正的去掉索引。只是通过异步操作把索引 像关系表那样存起来。
这样可以提升，高并发写入的性能，又可以提升数据查询的性能。

SQL语句优化

经常用到的需要条件字段 需要建立索引
避免在 where 子句中对字段进行 null 值判断（全表扫描）
避免 !=或<>操作
避免 in 和 not in 可用（between）
避免 '%c%' 考虑使用全文检索。
避免使用 参数，子句，函数操作
避免表达式操作 如 num/2=100; 优化后 num=100*2;
查询时 把条件中 有索引的 放在最左边 （最左前缀）
exists 代替 in

分布式架构 和集群架构的区别

简单说，分布式是以缩短单个任务的执行时间来提升效率的，而集群则是通过提高单位时间内执行的任务数来提升效率。

例如：
如果一个任务由10个子任务组成，每个子任务单独执行需1小时，则在一台服务器上执行改任务需10小时。

采用分布式方案，提供10台服务器，每台服务器只负责处理一个子任务，不考虑子任务间的依赖关系，执行完这个任务只需一个小时。(这种工作模式的一个典型代表就是Hadoop的Map/Reduce分布式计算模型）

而采用集群方案，同样提供10台服务器，每台服务器都能独立处理这个任务。假设有10个任务同时到达，10个服务器将同时工作，10小后，10个任务同时完成，这样，整身来看，还是1小时内完成一个任务！

分表

为什么要分表？

数据库中的数据量不一定是可控的，在未进行分库分表的情况下，随着时间和业务的发展，库中的表会越来越多，表中的数据量也会越来越大，相应地，数据操作，增删改查的开销也会越来越大；

另外，由于无法进行分布式式部署，而一台服务器的资源（CPU、磁盘、内存、IO等）是有限的，最终数据库所能承载的数据量、数据处理能力都将遭遇瓶颈。

分表的方式？

水平切分（横向切分）
垂直切分（纵向切分）
联合切分（横向切分 和纵向切分）

垂直分表

何为垂直分表？

即将表按照功能模块、关系密切程度划分出来，部署到不同的数据表上。
比如user（用户表 主要存用户名 和密码）表和user_details（用户详情 头像，地址等）表。
比如博客表中的title和content表。（大字段 拆到另外一个表里）

大字段垂直切分

什么样的字段适合于从表中拆分：
首先要肯定是大字段。为什么？原因很简单，就是因为他的大。
大字段一般都是存放着一些较长的Detail 信息，如文章的内容，帖子的内容，产品的介绍等等。

其次是和表中其他字段相比访问频率明显要少很多。
如果我们要查询某些记录的某几个字段，数据库并不是只需要访问我们需要查询的哪几个字段，而是需要读取其他所有字段这样，我们就不得不读取包括大字段在内的很多并不相干的数据。
而由于大字段所占的空间比例非常大，自然所浪费的IO 资源也就非常之大了。

实际上，在有些时候，我们甚至都不一定非要大字段才能进行垂直分拆。
在有些场景下，有的表中大部分字段平时都很少访问，而其中的某几个字段却是访问频率非常高。
对于这种表，也非常适合通过垂直分拆来达到优化性能的目的。

垂直切分的优点

  • 数据库的拆分简单明了，拆分规则明
  • 应用程序模块清晰明确，整合容易
  • 数据维护方便易行，容易定位

垂直切分的缺点

  • 部分表关联无法在数据库级别完成，需要在程序中完成
  • 对于访问极其频繁且数据量超大的表仍然存在性能瓶颈，不一定能满足要求
  • 事务处理相对更为复杂
  • 切分达到一定程度之后，扩展性会遇到限制
  • 过度切分可能会带来系统过渡复杂而难以维护

水平分表

何为水平切分？

当一个表中的数据量过大时，我们可以把该表的数据按照某种规则，进行划分，然后存储到多个结构相同的表，和不同的库上。
依据的条件可以是时间、地域、功能等比较清晰的条件

比如财务报表、薪资发放就可以用时间进行水平分割；

比如商品库存就可以用地域进行分割

比如用户表的普通用户、商户就可以用功能来进行划分

水平通用分表策略

以uuid作为全局唯一标识，为每一个新生成的用户生成uuid
将uuid进行md5加密，生成16进制随机字符串，取随机字符串前两位进行10进制转换，对分表数量的取余，获取插入的表后缀名。
比如建立8张表，对8取余，则会生成user_0...user_7，每个用户会随机插入这8张表中

分表后，如何统计数据？

iduuidaddtime

所有统计数据都是根据业务需求而来的，原始数据存在的情况，我们可以进行自建索引，实现具体的业务需求。
比如根据添加时间自建索引，其结构如下：

iduuidaddtime

那么根据addtime 我们就可以得出总数，最新个数。

分表后查询效率的问题？

根据自建索引表，获取uuid，再根据uuid获取数据每一行的数据。   
只不过多了一个10次的for循环而已，而php的10for循环可以说是微秒级的。

结果集存储的是指针 在通过 mysql_fetch_row()读取磁盘文件

水平切分的优点

  • 表关联基本能够在数据库端全部完成
  • 不会存在某些超大型数据量和高负载的表遇到瓶颈的问题
  • 应用程序端整体架构改动相对较少
  • 事务处理相对简单
  • 只要切分规则能够定义好，基本上较难遇到扩展性限制

水平切分的缺点

  • 切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则
  • 后期数据的维护难度有所增加，人为手工定位数据更困难
  • 应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难

垂直与水平联合切分的使用 (联合切分)

如果大部分业务逻辑稍微复杂一点，系统负载大一些的系统，
都无法通过上面任何一种数据的切分方法来实现较好的扩展性，
而需要将上述两种切分方法结合使用，不同的场景使用不同的切分方法。

 

联合切分的优点

  • 可以充分利用垂直切分和水平切分各自的优势而避免各自的缺陷
  • 让系统扩展性得到最大化提升

联合切分的缺点

  • 数据库系统架构比较复杂，维护难度更大；
  • 应用程序架构也相对更复杂；

MySQL 数据库锁定机制

数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制。

为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库系统的并发处理能力和性能，所以锁定机制的实现也就成为了各种数据库的核心技术之一。

总的来说，MySQL 各存储引擎使用了三种类型（级别）的锁定机制：**行级锁定，页级锁定和表级锁定。

行级锁定（row-level）

优点 ：并发优势 
缺点 ：耗费资源，容易造成死锁

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

表级锁定（table-level）

优点 ：简单， 避免死锁 
缺点 ：并发劣势

和行级锁定相反，表级别的锁定是MySQL 各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。

页级锁定（page-level）

BerkeleyDB 引擎使用
页级锁定是MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

使用场景

在MySQL 数据库中，
使用表级锁定的主要是MyISAM，Memory，CSV 等一些非事务性存储引擎，
而使用行级锁定的主要是Innodb 存储引擎和NDB Cluster 存储引擎，
页级锁定主要是BerkeleyDB 存储引擎的锁定方式。

MyISAM 表锁优化

缩短锁定时间，

短短几个字，说起来确实听容易的，但实际做起来恐怕就并不那么简单了。如何让锁定时间尽可能的短呢？唯一的办法就是让我们的Query 执行时间尽可能的短。

a) 尽量减少大的复杂Query，将复杂Query 分拆成几个小的Query 分步进行；
b) 尽可能的建立足够高效的索引，让数据检索更迅速；
c) 尽量让MyISAM 存储引擎的表只存放必要的信息，控制字段类型；

分离能并行的操作

MyISAM 的存储引擎还有一个非常有用的特性，Concurrent Insert（并发插入）的特性。MyISAM 存储引擎有一个控制是否打开Concurrent Insert 功能的参数选项：concurrent_insert，可以设置为0，1 或者2。三个值的具体说明如下：

a) concurrent_insert=2，无论MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都允许在数据文件尾部进行Concurrent Insert;

b) concurrent_insert=1，当MyISAM 存储引擎表数据文件中间不存在空闲空间的时候，可以从文件尾部进行Concurrent Insert;

c) concurrent_insert=0，无论MyISAM 存储引擎的表数据文件的中间部分是否存在因为删除数据而留下的空闲空间，都不允许Concurrent Insert。

Innodb 行锁优化

a) 尽可能让所有的数据检索都通过索引来完成，从而避免Innodb 因为无法通过索引键加锁而升级为表级锁定；

b) 合理设计索引，让Innodb 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query 的执行；

c) 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录；

d) 尽量控制事务的大小，减少锁定的资源量和锁定时间长度；

e) 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减MySQL 因为实现事务隔离级别所带来的附加成本；

减少死锁产生概率建议

a) 类似业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；

b) 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

c) 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率

死锁解决

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

数据类型优化

尽量选择占用空间小的类型

因为小的类型无论是在磁盘，还是在内存中占用的空间都是小的,在进行查询或者排序是临时表要求的空间也会相对较少。在数据量比较小的时候可能感觉不到，但是当数据量比较大时，这个原则的重要性可能就会得到显现。

尽量选择简单/恰当的类型

在对表进行选择以及排序的时候，对于简单的类型往往只需要消耗较少的CPU时钟周期。例如，对于MySql server而言，整数类型值的Compare往往会比字符串类型值的Compare简单且快，所以当你需要对特定的表进行排序时应该尽量选择整数类型作为排序的依据

尽量将字段设置为NOTNULL

一般情况下，如果你没有显示的制定一个字段为NULL，那么这个字段将会被数据库系统认为是NULLABLE, 系统的这种默认行为将会导致以下三个问题
(1) Mysql服务器自身的 查询优化功能将会受影响
(2) Mysql针对null值的字段需要额外的存储空间以及处理
(3) 如果一个null值是索引的一部分，那么索引的效果也会收到影响

由于这个原则对于数据库性能提升的作用不是很大，
所以对于已经存在的DB schema，其存在NULLABLE字段或者是索引为NULLABLE的，
也不用专门的去修改它，
但是对于新设计的DB或者索引需要尽量遵守这个原则。

 
posted on 2019-07-11 09:06  xxxxxxxx1x2xxxxxxx  阅读(279)  评论(0)  编辑  收藏  举报