mysql怎么优化数据库 mysql 数据优化

MySQL数据库性能优化之分区分表分库

分表是分散数据库压力的好方法。

专注于为中小企业提供成都做网站、网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业巩义免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了近1000家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。

分表，最直白的意思，就是将一个表结构分为多个表，然后，可以再同一个库里，也可以放到不同的库。

当然，首先要知道什么情况下，才需要分表。个人觉得单表记录条数达到百万到千万级别时就要使用分表了。

分表的分类

**1、纵向分表**

将本来可以在同一个表的内容，人为划分为多个表。（所谓的本来，是指按照关系型数据库的第三范式要求，是应该在同一个表的。）

分表理由：根据数据的活跃度进行分离，（因为不同活跃的数据，处理方式是不同的）

案例：

对于一个博客系统，文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。而博客的浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。所以，在进行数据库结构设计的时候，就应该考虑分表，首先是纵向分表的处理。

这样纵向分表后：

首先存储引擎的使用不同，冷数据使用MyIsam 可以有更好的查询数据。活跃数据，可以使用Innodb ,可以有更好的更新速度。

其次，对冷数据进行更多的从库配置，因为更多的操作时查询，这样来加快查询速度。对热数据，可以相对有更多的主库的横向分表处理。

其实，对于一些特殊的活跃数据，也可以考虑使用memcache ,redis之类的缓存，等累计到一定量再去更新数据库。或者mongodb 一类的nosql 数据库，这里只是举例，就先不说这个。

**2、横向分表**

字面意思，就可以看出来，是把大的表结构，横向切割为同样结构的不同表，如，用户信息表，user_1,user_2等。表结构是完全一样，但是，根据某些特定的规则来划分的表，如根据用户ID来取模划分。

分表理由：根据数据量的规模来划分，保证单表的容量不会太大，从而来保证单表的查询等处理能力。

案例：同上面的例子，博客系统。当博客的量达到很大时候，就应该采取横向分割来降低每个单表的压力，来提升性能。例如博客的冷数据表，假如分为100个表，当同时有100万个用户在浏览时，如果是单表的话，会进行100万次请求，而现在分表后，就可能是每个表进行1万个数据的请求（因为，不可能绝对的平均，只是假设），这样压力就降低了很多很多。

延伸：为什么要分表和分区？

日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

什么是分表？

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

什么是分区？

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

**MySQL分表和分区有什么联系呢？**

1、都能提高mysql的性高，在高并发状态下都有一个良好的表现。

2、分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。

3、分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。

4、表分区相对于分表，操作方便，不需要创建子表。

我们知道对于大型的互联网应用，数据库单表的数据量可能达到千万甚至上亿级别，同时面临这高并发的压力。Master-Slave结构只能对数据库的读能力进行扩展，写操作还是集中在Master中，Master并不能无限制的挂接Slave库，如果需要对数据库的吞吐能力进行进一步的扩展，可以考虑采用分库分表的策略。

**1、分表**

在分表之前，首先要选中合适的分表策略（以哪个字典为分表字段，需要将数据分为多少张表），使数据能够均衡的分布在多张表中，并且不影响正常的查询。在企业级应用中，往往使用org_id(组织主键)做为分表字段，在互联网应用中往往是userid。在确定分表策略后，当数据进行存储及查询时，需要确定到哪张表里去查找数据，

数据存放的数据表 = 分表字段的内容 % 分表数量

**2、分库**

分表能够解决单表数据量过大带来的查询效率下降的问题，但是不能给数据库的并发访问带来质的提升，面对高并发的写访问，当Master无法承担高并发的写入请求时，不管如何扩展Slave服务器，都没有意义了。我们通过对数据库进行拆分，来提高数据库的写入能力，即所谓的分库。分库采用对关键字取模的方式，对数据库进行路由。

数据存放的数据库=分库字段的内容%数据库的数量

**3、即分表又分库**

数据库分表可以解决单表海量数据的查询性能问题，分库可以解决单台数据库的并发访问压力问题。

当数据库同时面临海量数据存储和高并发访问的时候，需要同时采取分表和分库策略。一般分表分库策略如下：

中间变量 = 关键字%（数据库数量*单库数据表数量）

库 = 取整（中间变量/单库数据表数量）

表 = （中间变量%单库数据表数量）

实例：

1、分库分表

很明显，一个主表（也就是很重要的表，例如用户表）无限制的增长势必严重影响性能，分库与分表是一个很不错的解决途径，也就是性能优化途径，现在的案例是我们有一个1000多万条记录的用户表members,查询起来非常之慢，同事的做法是将其散列到100个表中，分别从members0到members99，然后根据mid分发记录到这些表中，牛逼的代码大概是这样子：

复制代码 代码如下:

?php

for($i=0;$i 100; $i++ ){

//echo "CREATE TABLE db2.members{$i} LIKE db1.members

";

echo "INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}

";

}

?

2、不停机修改mysql表结构

同样还是members表，前期设计的表结构不尽合理，随着数据库不断运行，其冗余数据也是增长巨大，同事使用了下面的方法来处理：

先创建一个临时表：

/*创建临时表*/

CREATE TABLE members_tmp LIKE members

然后修改members_tmp的表结构为新结构，接着使用上面那个for循环来导出数据，因为1000万的数据一次性导出是不对的，mid是主键，一个区间一个区间的导，基本是一次导出5万条吧，这里略去了

接着重命名将新表替换上去：

/*这是个颇为经典的语句哈*/

RENAME TABLE members TO members_bak,members_tmp TO members;

就是这样，基本可以做到无损失，无需停机更新表结构，但实际上RENAME期间表是被锁死的，所以选择在线少的时候操作是一个技巧。经过这个操作，使得原先8G多的表，一下子变成了2G多。

MySQL数据库优化（七）

为了能最小化磁盘I/O MyISAM 存储引擎采用了很多数据库系统使用的一种策略 它采用一种机制将最经常访问的表保存在内存区块中

对索引区块来说 它维护着一个叫索引缓存(索引缓冲)的结构体 这个结构体中放著许多那些最常使用的索引区块的缓冲区块 对数据区块来说 MySQL没有使用特定的缓存 它依靠操作系统的本地文件系统缓存

本章首先描述了 MyISAM 索引缓存的基本操作 然后讨论在MySQL 中所做的改进 它提高了索引缓存性能 同时能更好地控制缓存操作

线程之间不再是串行地访问索引缓存 多个线程可以并行地访问索引缓存 可以设置多个索引缓存 同时也能指定数据表索引到特定的缓存中

索引缓存机制对 ISAM 表同样适用 不过 这种有效性正在减弱 自从MySQL 开始 MyISAM 表类型引进之后 ISAM 就不再建议使用了 MySQL 更是延续了这个趋势 ISAM 类型默认被禁用了

可以通过系统变量 key_buffer_size 来控制索引缓存区块的大小 如果这个值大小为 那么就不使用缓存 当这个值小得于不足以分配区块缓冲的最小数量( )时 也不会使用缓存

当索引缓存无法操作时 索引文件就只通过操作系统提供的本地文件系统缓冲来访问(换言之 表索引区块采用的访问策略和数据区块的一致)

一个索引区块在 MyISAM 索引文件中是一个连续访问的单元 通常这个索引区块的大小和B树索引节点大小一样(索引在磁盘中是以B树结构来表示的 这个树的底部时叶子节点 叶子节点之上则是非叶子节点)

在索引缓存结构中所有的区块大小都是一样的 这个值可能等于 大于 或小于表的索引区块大小 通常这两个值是不一样的

当必须访问来自任何表的索引区块时 服务器首先检查在索引缓存中是否有可用的缓冲区块 如果有 服务器就访问缓存中的数据 而非磁盘 就是说 它直接存取缓存 而不是存取磁盘 否则 服务器选择一个(多个)包含其它不同表索引区块的缓存缓冲区块 将它的内容替换成请求表的索引区块的拷贝 一旦新的索引区块在缓存中了 索引数据就可以存取了

当发生被选中要替换的区块内容修改了的情况时 这个区块就被认为 脏 了 那么 在替换之前 它的内容就必须先刷新到它指向的标索引

通常服务器遵循LRU(最近最少使用)策略 当要选择替换的区块时 它选择最近最少使用的索引区块 为了想要让选择变得更容易 索引缓存模块会维护一个包含所有使用区块特别的队列(LRU链) 当一个区块被访问了 就把它放到队列的最后位置 当区块要被替换时 在队列开始位置的区块就是最近最少使用的 它就是第一候选删除对象

共享访问索引缓存

在MySQL 以前 访问索引缓存是串行的 两个线程不能并行地访问索引缓存缓冲 服务器处理一个访问索引区块的请求只能等它之前的请求处理完 结果 新的请求所需的索引区块就不在任何索引缓存环冲区块中 因为其他线程把包含这个索引区块的缓冲给更新了

从MySQL 开始 服务器支持共享方式访问索引缓存

没有正在被更新的缓冲可以被多个线程访问

缓冲正被更新时 需要使用这个缓冲的线程只能等到更新完成之后

多个线程可以初始化需要替换缓存区块的请求 只要它们不干扰别的线程(也就是 它们请求不同的索引区块 因此不同的缓存区块被替换)

共享方式访问索引缓存令服务器明显改善了吞吐量

多重索引缓存

共享访问索引缓存改善了性能 却不能完全消除线程间的冲突 它们仍然争抢控制管理存取索引缓存缓冲的结构 为了更进一步减少索引缓存存取冲突 MySQL 提供了多重索引缓存特性 这能将不同的表索引指定到不同的索引缓存

当有多个索引缓存 服务器在处理指定的 MyISAM 表查询时必须知道该使用哪个 默认地 所有的 MyISAM 表索引都缓存在默认的索引缓存中 想要指定到特定的缓存中 可以使用 CACHE INDEX 语句

如下语句所示 指定表的索 t t 和 t 引缓存到名为 hot_cache 的缓存中

mysql CACHE INDEX t  t  t  IN hot_cache; + + + + + | Table | Op | Msg_type | Msg_text | + + + + + | test t  | assign_to_keycache | status | OK | | test t  | assign_to_keycache | status | OK | | test t  | assign_to_keycache | status | OK | + + + + +

注意 如果服务器编译支持存 ISAM 储引擎了 那么 ISAM 表也使用索引缓存机制 不过 ISAM 表索引只能使用默认的索引缓存而不能自定义

CACHE INDEX 语句中用到的索引缓存是根据用 SET GLOBAL 语句的参数设定的值或者服务器启动参数指定的值创建的 如下 mysql SET GLOBAL keycache key_buffer_size= * ;想要删除索引缓存 只需设置它的大小为 mysql SET GLOBAL keycache key_buffer_size= ;索引缓存变量是一个结构体变量 由名字和组件构成 例如 keycache key_buffer_size keycache 就是缓存名 key_buffer_size 是缓存组件 默认地 表索引在服务器启动时指定到主(默认的)索引缓存中 当一个索引缓存被删掉后 指定到这个缓存的所有索引都被重新指向到了默认索引缓存中去 对一个繁忙的系统来说 我们建议以下三条策略来使用索引缓存 热缓存占用 %的总缓存空间 用于繁重搜索但很少更新的表 冷缓存占用 %的总缓存空间 用于中等强度更新的表 如临时表 冷缓存占用 %的总缓存空间 作为默认的缓存 用于所有其他表 使用三个缓存的一个原因是好处在于 存取一个缓存结构时不会阻止对其他缓存的访问 访问一个表索引的查询不会跟指定到其他缓存的查询竞争 性能提高还表现在以下几点原因 热缓存只用于检索记录 因此它的内容总是不需要变化 所以 无论什么时候一个索引区块需要从磁盘中引入 被选中要替换的缓存区块的内容总是要先被刷新 索引被指向热缓存中后 如果没有需要扫描全部索引的查询 那么对应到B树中非叶子节点的索引区块极可能还保留在缓存中 在临时表里必须频繁执行一个更新操作是相当快的 如果要被更新的节点已经在缓存中了 它无需先从磁盘中读取出来 当临时表的索引大小和冷缓存大小一样时 那么在需要更新一个节点时它已经在缓存中存在的几率是相当高的

中点插入策略

默认地 MySQL 的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块 不过它也支持更完善的方法 叫做 中点插入策略

使用中点插入策略时 LRU链就被分割成两半 一个热子链 一个温子链 两半分割的点不是固定的 不过缓存管理系统会注意不让温子链部分 太短 总是至少包括全部缓存区块的 key_cache_division_limit 比率 key_cache_division_limit 是缓存结构体变量的组件部分 因此它是每个缓存都可以设置这个参数值

当一个索引区块从表中读入缓存时 它首先放在温子链的末尾 当达到一定的点击率(访问这个区块)后 它就提升到热子链中去 目前 要提升一个区块的点击率( )对每个区块来说都是一样的 将来 我们会让点击率依靠B树中对应的索引区块节点的级别 包含非叶子节点的索引区块所要求的提升点击率就低一点 包含叶子节点的B索引树的区块的值就高点

提升起来的区块首先放在热子链的末尾 这个区块在热子链内一直循环 如果这个区块在该子链开头位置停留时间足够长了 它就会被降级回温子链 这个时间是由索引缓存结构体变量的组件 key_cache_age_threshold 值来决定的

这个阀值是这么描述的 一个索引缓存包含了 N 个区块 热子链开头的区块在低于 N*key_cache_age_threshold/ 次访问后就被移动到温子链的开头位置 它又首先成为被删除的候选对象 因为要被替换的区块还是从温子链的开头位置开始的

中点插入策略就能在缓存中总能保持更有价值的区块 如果更喜欢采用LRU策略 只需让 key_cache_division_limit 的值低于默认值

中点插入策略能帮助改善在执行需要有效扫描索引 它会将所有对应到B树中高级别的有价值的节点推出的查询时的性能 为了避免这样 就必须设定 key_cache_division_limit 远远低于 以采用中点插入策略 则在扫描索引操作时那些有价值的频繁点击的节点就会保留在热子链中了

索引预载入

如果索引缓存中有足够的区块用来保存全部索引 或者至少足够保存全部非叶子节点 那么在使用前就载入索引缓存就很有意义了 将索引区块以十分有效的方法预载入索引缓存缓冲 从磁盘中顺序地读取索引区块

没有预载入 查询所需的索引区块仍然需要被放到缓存中去 虽然索引区块要保留在缓存中 因为有足够的缓冲 它们可以从磁盘中随机读取到 而非顺序地

想要预载入缓存 可以使用 LOAD INDEX INTO CACHE 语句 如下语句预载入了表 t 和 t 的索引节点(区块)

mysql LOAD INDEX INTO CACHE t  t  IGNORE LEAVES; + + + + + | Table | Op | Msg_type | Msg_text | + + + + + | test t  | preload_keys | status | OK | | test t  | preload_keys | status | OK | + + + + +

增加修饰语 IGNORE LEAVES 就只预载入非叶子节点的索引区块 因此 上述语句加载了 t 的全部索引区块 但是只加载 t 的非叶子节点区块

如果使用 CACHE INDEX 语句将索引指向一个索引缓存 将索引区块预先放到那个缓存中去 否则 索引区块只会加载到默认的缓存中去

索引缓存大小

MySQL 引进了对每个索引缓存的新变量 key_cache_block_size 这个变量可以指定每个索引缓存的区块大小 用它就可以来调整索引文件I/O操作的性能

当读缓冲的大小和本地操作系统的I/O缓冲大小一样时 就达到了I/O操作的最高性能了 但是设置索引节点的大小和I/O缓冲大小一样未必能达到最好的总体性能 读比较大的叶子节点时 服务器会读进来很多不必要的数据 这大大阻碍了读其他叶子节点

目前 还不能控制数据表的索引区块大小 这个大小在服务器创建索引文件 ` MYI 时已经设定好了 它根据数据表的索引大小的定义而定 在很多时候 它设置成和I/O缓冲大小一样 在将来 可以改变它的值 并且会全面采用变量 key_cache_block_size

重建索引缓存

索引缓存可以通过修改其参数值在任何时候重建它 例如

mysql SET GLOBAL cold_cache key_buffer_size= * * ;

如果设定索引缓存的结构体变量组件变量 key_buffer_size 或 key_cache_block_size 任何一个的值和它当前的值不一样 服务器就会清空原来的缓存 在新的变量值基础上重建缓存 如果缓存中有任何的 脏 索引块 服务器会先把它们保存起来然后才重建缓存 重新设定其他的索引缓存变量并不会重建缓存

lishixinzhi/Article/program/Oracle/201311/16615

北大青鸟设计培训：mysql数据库的优化方法？

我们都知道，服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的，而为了提高我们数据库的运行速度和效率，数据库优化也成为了我们每日的工作重点，今天，昌平IT培训就一起来了解一下mysql服务器数据库的优化方法。

为什么要了解索引真实案例案例一：大学有段时间学习爬虫，爬取了知乎300w用户答题数据，存储到mysql数据中。

那时不了解索引，一条简单的“根据用户名搜索全部回答的sql“需要执行半分钟左右，完全满足不了正常的使用。

案例二：近线上应用的数据库频频出现多条慢sql风险提示，而工作以来，对数据库优化方面所知甚少。

例如一个用户数据页面需要执行很多次数据库查询，性能很慢，通过增加超时时间勉强可以访问，但是性能上需要优化。

索引的优点合适的索引，可以大大减小mysql服务器扫描的数据量，避免内存排序和临时表，提高应用程序的查询性能。

索引的类型mysql数据中有多种索引类型，primarykey，unique，normal，但底层存储的数据结构都是BTREE;有些存储引擎还提供hash索引，全文索引。

BTREE是常见的优化要面对的索引结构，都是基于BTREE的讨论。

B-TREE查询数据简单暴力的方式是遍历所有记录;如果数据不重复，就可以通过组织成一颗排序二叉树，通过二分查找算法来查询，大大提高查询性能。

而BTREE是一种更强大的排序树，支持多个分支，高度更低，数据的插入、删除、更新更快。

现代数据库的索引文件和文件系统的文件块都被组织成BTREE。

btree的每个节点都包含有key，data和只想子节点指针。

btree有度的概念d=1。

假设btree的度为d，则每个内部节点可以有n=[d+1，2d+1)个key，n+1个子节点指针。

树的大高度为h=Logb[(N+1)/2]。

索引和文件系统中，B-TREE的节点常设计成接近一个内存页大小(也是磁盘扇区大小)，且树的度非常大。

这样磁盘I/O的次数，就等于树的高度h。

假设b=100，一百万个节点的树，h将只有3层。

即，只有3次磁盘I/O就可以查找完毕，性能非常高。

索引查询建立索引后，合适的查询语句才能大发挥索引的优势。

另外，由于查询优化器可以解析客户端的sql语句，会调整sql的查询语句的条件顺序去匹配合适的索引。