https://www.cnblogs.com/bluebluesky/articles/6413831.html
其实MYSQL 数据库的优化流程大致如下:
分区 -> 分表 -> 库垂直拆分 -> 库水平拆分 ->库读写分离
注意:现在尽量使用InnoDB代替MyISAM,使用表级锁的代价是很大的。
简介
一般当我们的数据表存的数据越来越大,已经影响到了查询等操作,而且旧的数据访问比较少,这时候一般会使用分区来分散数据到不同的磁盘,分散I/O负担,查询数据时也只需要查询指定的分区即可。
但是每个表的分区不能超过1024,而实际情况下分区达到100以后基本就影响性能了,此时需要进一步对表进行划分,分表不影响单个表的分区,二者应该一起使用。
然而当单个数据库的请求并发实在多到影响性能时,这时候就要采用分库、读写分离的手段了,然而这时候可能就需要一些中间件来简化操作。
分区
mysql支持的分区类型包括Range、List、Hash、Key,其中Range比较常用:
- RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
- LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
- HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
- KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
分区的限制:
- 主键或者唯一索引必须包含分区字段,如primary key (id,username),不过innoDB的大组建性能不好。
- 很多时候,使用分区就不要在使用主键了,否则可能影响性能。
- 每个表最多1024个分区,而且多分区会大量消耗内存。一般小于100个为好
- 分区的表不支持外键,相关的逻辑约束需要使用程序来实现。
- 分区后,可能会造成索引失效,需要验证分区可行性。
create table user(
id int not null auto_increment,
username varchar(10),
primary key(id)
)engine = innodb charset=utf8
partition by range (id)(
partition user_1 values less than (10),
partition user_2 values less than (20)
);
建立后添加分区:
maxvalue 表示最大值 这样大于等于20的id 都出存储在user_3分区
alter table user add partition(
partition user_3 values less than maxvalue
);
删除分区
alter table user drop partition user_3;
由此可见,mysql通过分区把数据保存到不同的文件里,同时索引也是分区的。相对于未分区的表来说,分区后单独的数据库文件索引文件的大小都明显降低,效率则明显的提示了。可以插入一条数据然后分析查询语句验证一下:
insert into user values(null,'测试');
explain partitions select * from user where id =1;
可以将这些分区所在的物理磁盘分开完全独立,可以提高磁盘IO吞吐量。
CREATE TABLE users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
usersname VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (id) (
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data0/index',
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data1/data'
INDEX DIRECTORY = '/data1/index',
PARTITION p2 VALUES LESS THAN (9000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data2/index',
PARTITION p3 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '/data3/data'
INDEX DIRECTORY = '/data3/index'
);
分表
分表和分区的方式一样。最简单的方式就是对字段取模分表。
然后取模查询就可以,中间可能牵扯到联合查询,注意一下就行。
合并表 MERGE
用到这个的情况下,一般是维护旧的服务,性能很差,服务中的数据量又特别大,你又有可能没办法修改代码,数据表又是MyISAM引擎,这个时候拆分成多个表,然后将数据取模复制到对应的表中,然后使用MERGE合并能达到不修改服务优化的目的,此时原来的表就可以任意处置了。
分库
参考https://www.cnblogs.com/phpshen/p/6198375.html