概述

MySQL的主要适用场景

1、Web网站系统

2、日志记录系统

3、数据仓库系统

4、嵌入式系统

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. 显示菜单时,通常会进行排序.

5. 第一个情况 就符合 ,创建复合索引的条件,在where中常常会一起出现,

   例如 m=home and c=index and a=login

6. 第二个情况 可以使用主键索引,主键自己就自带索引属性.

7. 第三个情况,在查询子级时 通常会使用到.

8. 第四个情况： 排序也常常使用到.

9. data 和 url 为何不加索引?

   data 和 url 属于详细内容, 一般只用于展示,不会加入到where条件查询中,所以不必要加索引.

10. 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张表中

分表后,如何统计数据?

所有统计数据都是根据业务需求而来的,原始数据存在的情况,我们可以进行自建索引,实现具体的业务需求.

好比根据添加时间自建索引,其结构如下：

|id|uuid|addtime|

|---|---|---|

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

分表后查询效率的问题?

根据自建索引表,获取uuid,再根据uuid获取数据每一行的数据. 只不过多了一个10次的for循环罢了,而php的10for循环可以说是微秒级的.结果集存储的是指针 在通过 mysql_fetch_row()读取磁盘文件

水平切分的优点

• 表关联基本能够在数据库端全部完成

• 不会存在某些超大型数据量和高负载的表遇到瓶颈的问题

• 应用法式端整体架构改动相对较少

• 事务处置相对简单

• 只要切分规则能够定义好,基本上较难遇到扩展性限制

程度切分的缺点

• 切分规则相对更为复杂,很难抽象出一个能够满足整个数据库的切分规则

• 后期数据的维护难度有所增加,人为手工定位数据更困难

• 应用系统各模块耦合度较高,可能会对后面数据的迁移拆分造成必定的困难

垂直与程度联合切分的使用 (联合切分)

如果大部门业务逻辑稍微复杂一点,系统负载大一些的系统,

都无法通过上面任何一种数据的切分办法来实现较好的扩展性,

而需要将上述两种切分办法结合使用,不同的场景使用不同的切分办法.

联合切分的长处

• 可以充分利用垂直切分和程度切分各自的优势而避免各自的缺陷

• 让系统扩展性得到最大化提升

联合切分的缺点

• 数据库系统架构比拟复杂,维护难度更大；

• 应用法式架构也相对更复杂.

总结

以上是对MySQL性能调优,分享给大家,希望大家可以了解什么是MySQL性能调优.觉得收获的话可以点个存眷收藏转发一波喔,谢谢大佬们支持.(吹一波,233~~)

Java小马哥,头条出品,每天一篇干货,喜欢就收藏＋存眷