一、 Introduction
mysql优化可以体现在4个层面。存储层,存储引擎、字段类型选择、范式设计;设计层,索引、缓存、分区;架构层,多个mysql服务器设置,读写分离(一主多从,与redis的主从模式类似);sql语句层,多个sql语句都可以达到目的的情况下,选择性能高、速度快的sql语句。
二、 存储引擎
使用的数据是通过一定的技术存储在数据库中的,数据库的数据是以文件的形式存储在硬盘中的。存储的技术不止一种,每种技术有自己独特的性能和功能实现。存储技术和其功能的合并就称为存储引擎。在mysql中经常使用的存储引擎有Myiasm或Innodb。其他的引擎还有memory,内存引擎,将数据存入内存的;Archive,归档,只能读写,不能修改。
数据库的数据存储在不同的存储引擎里,所有的特性跟存储的存储引擎有一定关系。需要按照项目的需求和特点,选择不同的存储引擎。
在mysql中show engines;可以显示所有的存储引擎。
1. innodb
数据库每个数据表的数据涉及三方面信息,表结构、数据、索引。
通过mysql的show engines;查看innidb支持事务、行级索引、外键。
1) 表结构、数据、索引的物理存储
创建一个innodb数据表,
create table itemlist2(
id int not null auto_increment,
itemlist_name varchar(32),
primary key(id)
)engine=innodb charset=utf8;
在mysql的数据源文件目录data下,可以看到一个itemlist.frm文件,是一个表结构文件。
innodb中,数据文件和索引文件合并到一起,在mysql的存放innodb表的目录中,可以在安装时自定义这个目录,这个文件名是ibdata1。所有innodb表的数据都存储这个文件中。为了提高数据的安全性,可以给innodb类型表的数据和索引创建各自对应的存储空间,而不是默认地都存放在这个ibdata1文件中。show variables like ‘innodb_file_per_table’;可以看到这个变量默认情况下是off的,也就是innodb表的数据和索引不会创建单独的文件。set global innodb_file_per_table=1;设置变量,使得每个innodb表有独立的存储空间。然后,再创建一个innodb数据表,可以在data目录下看到创建了一个itemlist2.frm和一个itemlist2.idb,有单独的数据和索引存储空间itemlist2.idb。后期无论innodb_file_per_table如何变化,itemlist2表都有独立的数据和索引存储空间。
2) 数据存储顺序
innodb表数据的存储是按照主键的顺序排列每个写入的数据。
insert intoitemlist1 values
(11,'haha1'),
(21,'haha2'),
(13,'haha3'),
(1,'haha4');
然后,select * from itemlist1;可以看到查询的顺序是按照id从小到大排列得到的。
该特点决定了该数据表的写入数据比较慢。
3) 事务、外键
该类型数据表支持事务、外键。
事务,把许多写入(增、改、删)的sql语句捆绑在一起,要么执行,要么不执行。
事务经常用于与交易有关的方面。4个特性,原子、一致、持久、隔离。
具体操作,
start trsaction;
写入sql语句,比如insert
update
delete等
rollback;#回滚
commit;#提交
rollback和commit只能执行一个。
外键,两个数据表a和b,b表的主键是a表的普通字段,这个a表中的普通字段就是a表的外键。
外键的使用有约束,约束体现在,先写b表的数据,才能写a表的数据,否则a表的数据没有来源;在删除表中数据时,必须先删除a表的数据,才能删除b中的对应这个或这些外键的数据。一般,在真实生产中,一般不使用外键,因为有约束。
4) 并发性
该类型表的并发性非常高,因为其锁定类型为行锁。多人同时操作该数据表,为了操作数据表的时候,数据内容不会随便发生变化,要对信息进行锁定,该类型锁定级别为行锁,只锁定被操作的当前记录。
2. Myisam
该类型的数据表,表结构、数据、索引都有独立的存储文件。
创建一个myisam数据表,
create table itemlist3(
id int not null auto_increment,
itemlist_name varchar(32),
primary key(id)
)engine=myisam charset=utf8;
1) 结构、数据、索引独立存储
在data目录下,可以看到有3个itemlist3的文件,itemlist3.frm,表结构文件,itemlist3.MYD,表数据文件,itemlist3.MYI,表索引文件。做数据还原时,这3个文件可以直接拷贝,备份。也就是独立的表数据文件可以直接备份、还原。
2) 数据存储顺序
myisam表数据的存储是按照自然顺序排列每个写入的数据。
insert intoitemlist3 values
(11,'haha1'),
(21,'haha2'),
(13,'haha3'),
(1,'haha4');
然后,select * from itemlist3;可以看到查询的顺序是按照插入时的先后顺序排列得到的。
该特点决定了该数据表的写入数据比较快。
3) 并发性
该类型并发性较低,因为其锁定级别为表锁。
4) 压缩机制
如果一个数据表的数据非常多,为了节省存储空间,可以对该表进行压缩处理。
insert into itemlist3 select null,itemlist_name from itemlist3;
多执行几次这条sql,扩张itemlist3的表的数据。
使用压缩工具myisampack.exe 表名,进行压缩。在mysql/bin目录下,有一个myisampack.exe。比如:在cmd下,myisampack.exe C:\ProgramData\MySQL\MySQLServer5.5\data\php1\itemlist3
注意,文件的路径中不要出现空格,结尾没有任何符号,特别是习惯写;结尾的,否则会提示找不到文件的错误。压缩成功,看到压缩了70%左右。
压缩好后,重建索引,压缩后,索引的信息有所变化,需要重建,myisamchk.exe -rq 全路径表名
比如,myisamchk.exe -rq C:\ProgramData\MySQL\MySQLServer5.5\data\php1\itemlist3
注意,文件的路径中不要出现空格,结尾没有任何符号,特别是习惯写;结尾的,
然后刷新数据表,就可以使用表,在mysql中,flush table 表名。
注意,压缩的表编程只读表,不能写入数据。
压缩的数据表不能频繁的写入操作,只是内容固定的数据表可以做压缩数据,比如存储全国地区信息的数据表。
如果压缩的表必须要写入数据,就解压该数据表,写入数据,再压缩回去。
解压工具myisamchk.exe --unpack 表名。
比如,myisamchk.exe --unpack C:\ProgramData\MySQL\MySQLServer5.5\data\php1\itemlist3
解压过程中会自动生成新的索引。解压完毕后,在data目录下会生成解压的文件,并且备份压缩的文件,然后flush table 表名,会删除备份的压缩文件。然后,就可以写入数据了。
innodb存储引擎,适合做更新、删除。
myiasm存储引擎,适合做查询、写入。
3. archive
归档型存储引擎,该引擎只有写入、查询操作,没有修改、删除操作。
比较适合存储日志性质的信息。
4. memory
内存型存储引擎,操作速度非常快速,比较适合存储临时信息。服务器一旦断电,则内存中数据丢失,那么该存储引擎中的数据立即丢失。
5. 存储引擎的选择
myisam和innodb来说。
网站大多数情况下,读和写的操作非常多,适合选择myisam类型。比如,dedecms、phpcms内容管理系统(新闻网站内)、discuz论坛。
网站对业务逻辑有一定要求,特别是涉及到交易的,比如办公网站、商城,适合选择innodb存储引擎。
mysql5.5,默认存储引擎都是innodb的。
三、 字段类型选择的原则
1. 尽量少的占据存储空间
比如,int整型,存储年龄,选择tinyint(1),1个字节存储的范围是0-255。
smallint(2),表示范围0-65535,适合存储一个养殖场鸡的数量。
mediumint(3),0-16777215
int(4),0-4294967295
bigint(8),0-18446744073709551615
时间类型date
time(),时分秒
datetime(),年月日,时分秒
year(),年份
date(),年月日
timestamp(),时间戳从1970-1-1到现在经历的秒数
根据时间信息的范围,选取不同类型的使用。
2. 数据的整合最好固定长度
char(长度)
char是固定长度的,运行速度快。长度,255字符限制。
varchar(长度)
varchar不是固定长度的,内容比较少,要进行补位操作,该类型要保留1-2个字节保留字节的长度。长度是65535字节限制。存储汉字,比例字符集utf8的,最多可以存储65535/3-2个。约21843个。
存储手机号码,选择char(11),如果是varchar,就是varchar(12),要选择少的char(11)。
如果是邮箱,由于长度不固定,所以选择varchar。
能用char就不要用varchar,为了快。
3. 信息最好存储为整型的
时间信息可以存储为整型的,时间戳。select from_unixstamp(时间戳) from 表名;
set集合类型,多选的,set(‘篮球’,’足球’,’毛球’);
enum枚举类型,单选,enum(‘nan’,’nv’);
推荐使用set和enum类型,内部会通过整型信息参与具体计算、运行。
ip地址也可以变为整型信息进行存储。mysql内部有算法,把ip变为数字。通过函数,inet_aton(ip) inet_ntoa(数字)
比如,select inet_aton('192.168.0.0'); select inet_ntoa(3232235520);
在php中,ip2long(ip) long2ip(数字) 可以实现ip地址和数字的相互转换。
四、 逆范式
数据库设计需要遵守三范式。
逆范式,比如两个数据表,商品表Goods、分类表Category
Goods: id name cat_id price
123 haha 222 33
124 hehe 245 345
Category: cat_id name
222 笔记本
245 台式机
比如,要求计算每个分类下,商品的数量。
select c.cat_id,c.name,count(*) from category as c left join goods as g on g.cat_id=c.cat_id group by c.cat_id;
这个sql语句是多表查询,且还有count的聚合计算。如果这样的需求很多,类似的sql语句查询速度没有优势。如果需要查询速度提升,最好设置为单表查询,并且没有聚合计算。
可以在设计表的时候,给category表设计一个goods_num字段,计算商品数量。
经过优化后,对于同一个需求,sql语句,就是select cat_id,name,goods_num from category。
但是需要维护额外的工作,goods商品表要增加、减少数据都需要维护goods_num字段的信息。但是这样的设计就违反了三范式。因为有重复的数据。
以上对经常使用的需求做优化,增加一个goods_num字段,该字段的数据其实通过goods表做聚合计算也可以获得,这种设计不满足三范式,因此称为逆范式。
三范式
一范式,原子性,数据不可以再分隔的,比如一个info字段,其中有地址、电话、qq等,这个字段是可以再细分的,不符合原子性;
二范式,数据没有冗余,比如order表,order表中有id 编号 下单时间 商品信息 商品价格 商品描述,比如买了两次同一个商品,则这个商品的信息要在这两行中出现冗余,这是增加数据库的存储负担,crud时也会消耗不必要的资源,没有冗余很重要,通过关系型设计解决冗余问题。
三范式,数据表的每个字段与当前表的主键产生直接关联(非间接关联)。比如用户表,有字段userid name age sex orderid 编号 订单时间
优化
设计一个用户表,存放userid name age sex,一个用户订单表,存放userid orderid,一个订单表,存放orderid 编号 订单时间。相当于通过中间表,也就是一个虚表连接起这两个表。
五、 索引
索引是优化数据库设计,提升数据库性能非常显著的技术之一。
各个字段都可以设计为索引,经常使用的索引为主键索引primary key。
索引可以明显提升查询sql语句的速度。
为已经存在的表设置索引。
alter table emp add primary key(empno);
一旦设置索引,在做数据查询,时间提升是百倍至千倍的。
索引本身是一个独立的存储单位,在该单位里边记录着数据表的某个字段和某个字段对应的物理空间,字段与具体的物理空间有对应联系。因为索引本身的查找有某种快速查找的算法,比如,冒泡、二分、快速之类的,这样就大大缩短的查找索引的时间,只要快速找到索引,就可以快速找到索引对应的字段的物理空间,就找到了整个行记录。
1. 索引类型
四种
主键,primary key auto_increment属性必须设置给主键。要求信息内容不能为null,有唯一性。
唯一,unique index。要求字段的信息内容在表中不能重复。
普通,没有要求直接设置。
全文,fulltext index。myisam才支持全文索引,其他不支持。
复合索引,索引关联的字段是多个组成的,该索引就是复合索引。比如,一个索引关联了名称和年龄,那么查找是找到这个名称还要找到这个年龄的,才是找到。
2. 操作索引
1) 创建
创建表时,比如:
create table stu(
id int not null auto_increment comment '主键',
name varchar(32) not null default '' comment '名字',
age tinyint not null default 0 comment '年龄',
addr varchar(32) not null default ''comment '学校',
primary key(id),
unique index nm(name),
index (age),
fulltext index(addr)
)engine=myisam charset=utf8;
--创建索引(主键、唯一、普通、全文)
--myisam数据表可以设置fulltext index,其他不行
查看表结构,各种索引创建成功。
创建一个复合索引
alter table 表名 add index 索引名(字段1,字段2,…);
alter table 表名
表已经创建好创建索引
alter table 表名 add primary key(字段);
alter table 表名 add unique index 索引名 (字段);
alter table 表名 add index 索引名 (字段);
alter table 表名 add fulltext index 索引名 (字段);
2) 删除
alter table 表名 drop primary key;//删除主键索引
如果主键有自增属性,必须先删除自增属性,才可以删除主键索引。alter table 表名modify 主键 int not null comment’主键’;
alter table 表名 drop index 索引名称;//删除其他索引,唯一、普通、全文
3. 执行计划
是针对查询语句设置执行计划,当前数据库只有查询语句支持执行计划。
每个select查询sql语句执行之前,需要把该语句需要用到的各方面资源都计划好。例如,cpu资源、内存资源、索引支持、涉及到的数据量等等。
查询sql语句真实执行之前,所有的资源计划就是执行计划。
执行计划在索引方面,主要了解查询sql语句是否可以使用上索引。
操作
explain 查询sql语句\G;
通过这个语法可以在执行sql语句前,查看其执行计划。
4. 索引适合的场景
1) where查询条件
where之后设置的查询条件字段都适合做索引;
2) 排序查询适合使用
order by 字段 //排序字段适合做索引
3) 索引覆盖
给两个字段设置了符合索引。比如,alter table emp add index(ename,job);。然后select ename,job from emp;
这样查询的全部字段已经都在索引表中,直接获取即可,不用到数据表中再获取了。这就是索引覆盖。这种查询速度快,效率高。该索引也称为黄金索引。
索引本身需要消耗空间资源的,升级维护困难。
4) 连接查询
join on
比如,goods表,有id name cat_id …,category表,有cat_id name …。使用cat_id作为约束字段查询,在goods数据表中给cat_id设置索引,可以提高连表查询的速度。
5. 索引使用的原则
1) 字段独立原则
比如,select * from emp where empno=13343;//empno字段是独立的
select * from emp where empno+2=13343;//empno字段是不独立的
只有独立的条件字段才可以使用索引。
explain select * from emp where empno=1332343\G;
explain select * from emp where empno+2=1333443\G;
查看后可以看到原则的成立。
2) 左原则
模糊查询,使用like,%或者_,%关联多个字符内容,_管理一个字符内容。
比如,select * from 表名 like “haha%”;select * from 表名 like “%haha%”;
观察
explain select * from 表名 like “haha%”\G;//这种查询是左条件查询
explain select * from 表名 like “%haha%”\G;//这种查询是中间条件查询
explain select * from 表名 like “%haha”\G;//这种查询是右条件查询
其中只有左条件查询使用到了索引,其他没有使用到索引。
查询条件信息在左边出现,就可以使用索引。这就是左原则。
3) 复合索引
存在复合索引index(ename,age),那么,比如explain select * from emp where ename like “abc%”\G;//结果是存在索引的。explain select * from emp where age like “abc%”\G;//结果不存在索引。
复合索引中字段被单独使用时,排在第一个字段可以使用索引,后面的不可以。
4) OR原则
or左右的关联条件必须都具备索引,才使用索引。
比如
explain select * from emp where ename like “ahb%” or epassword like “adfd%”\G;
这两个字段都有单独的索引,使用到了索引。
explain select * from emp where ename like “ahb%” or age like “adfd%”\G;
ename有索引,age没有索引,整个查询没有索引使用。
6. 索引设计依据
要估算每个数据表全部的查询sql语句的类型;
分析、统计每个sql语句的特点,比如where/order by/or等;
原则,被频繁执行的sql语句要设置;有的sql语句执行时间比较长的(可以进行统计);业务逻辑比较重要的sql语句(比如限定时间内交易的业务);
7. 前缀索引
设计索引的字段不使用全部内容,而是只使用该字段前边一部分内容。
如果字段的前N位的信息已经可以足够标识当前记录信息,就可以把前边N位信息设置为索引内容,好处,索引占据的物理空间小,运行速度就非常快。
比如,手机品牌的表,手机名字字段,华为nxx,小米mxx,中兴lxx,vivoxxx,opposxx…取这个名字的前1个字,华、小、中、v、o可以创建出索引。
具体实现
操作,alter table 表名 add index (字段(位数));
前边到底取得多少位,才是记录的唯一标识。使用总记录数目/前n位记录数目=比值。
比如,select count(*)/distinct substring(字段,开始位置1开始,长度) from 表名;
select count(*)/count(distinct substring(epassword,1,1)) from emp;//长度从1开始多次执行这条语句,从1开始,往上加,直到这个值稍小,稳定时或等于1时,说明这个前长度位既能区分开这个字段,长度又不是太长。这个长度可以作为字段前缀的位数。
比如,得到的结果为8。那么,就可以添加这个字段前8位为前缀位数。alter table emp add index (epassword(8));
8. 索引设计原则
字段内容需要足够的类别丰富,比如地址这样的字段适合做,向性别这样的字段只有两个选项就不适合。
9. 全文索引
mysql5.5只有myisam存储引擎支持全文索引。
mysql5.6 myisam和innodb都支持。select version();查看版本。
全文索引只支持英文索引。
全文索引可以应用在like “%abc%” 的操作上。
设置全文索引,alter table 表名 add fulltext index [索引名] (字段1,字段2[,…]);
alter table articles add fulltext index `index_content` (title,body);
explain select * from articles where title like "%Mysql%" or body like "%database%"\G;
通过查看,观察到没有使用全文索引。
使用全文索引的语法match() against()
explain select * from articles where match(title,body) against('MySQL,DataBase')\G;
10. 索引结构
索引内部有算法,保证查询速度快。算法的基础,是数据结构。索引,其实就是数据结构。在mysql中,索引是存储引擎层面的技术。因为不同的存储引擎使用的数据结构是不一样的。
有两种常见的索引结构
非聚集索引结构(Myisam)
聚集索引结构(Innodb)
1) 非聚集索引结构
在数据表源文件中,myisam表的索引部分的文件和数据部分的文件是分离的。myisam存储引擎的索引结构为B+tree索引结构。
该B+Tree索引结构,索引结构内部分为索引节点,节点从左到右是节点的宽度,节点从上到下的层数是结构的宽度。结构的宽度、高度太大都不适合快速索引查找。宽度和高度的设计会根据数据量做适当的选择,是mysql底层的算法。该索引结构叶子节点存储关键字和物理地址,非叶子节点存储指针,用于数据的比较、判断和查找。
2) 聚集索引结构
索引结构名称也是B+Tree。包括主键索引结构和非主键索引结构。
主键索引结构,重要的是,叶子节点对应的关键字(主键id)对应整条记录信息。
非主键索引结构(唯一、普通等),叶子节点的关键字对应主键id值。
innodb表物理文件的索引和数据在一起的。*.idb集中存储了数据表的索引和数据。
六、 查询缓存设置
一条查询语句有可能获得很多数据,并且有一定的时间消耗,如果该sql语句被频繁执行获取数据(这些数据还不京城发生变化),为了使得每次获得信息数度较快,就可以把执行结果缓存起来,供后续的每次使用。
1. 查看并开启缓存
show variables like ‘query_cache%’;
缓存大小为0,不能缓存。
开启缓存,set global query_cache_size=64*1024*1024;
开启缓存后,查询速度有明显提升。
2. 缓存失效
数据表或数据有变动(增加、减少、修改),会引起缓存失效。
3. 不会使用缓存的情况
sql语句有变动的信息就不使用缓存。例如,有时间信息,有随机数。
4. 生成多个缓存
注意,可以获得相同结果的sql语句,如果有空格或者大小写等内容,也会分别进行缓存。
比如,select * from emp where empno=160000;会产生缓存,而select * from emp Where empno=1600000;也会产生一次缓存,而不用其实有相同结果的前一句产生的缓存。
5. 不进行缓存
针对特殊语句不需要缓存。
select sql_no_cache * from emp where empno=1500000;
6. 查看缓存空间状态
show status like ‘Qcache%’;
七、 分表/分区
一个数据表里边可以存储许多记录信息,如果一个数据表里存储的数据非常多,例如淘宝商城的商品表,这样该商品表的相关工作量就很多,负载工作量高到一定程度,会造成把表锁死的情况发生。
为了降低商品表的负载/工作量,可以给该表拆分为多个数据表,这样每个表的工作量就降低了。
mysql5.1之后就支持分表分区的设计。
数据表拆分以后,php通过手动或者mysql的算法操作这些数据表。手动算法需要php语句里边设计操作逻辑,增加php语言的代码量。mysql算法,不需要php做额外操作,是mysql分表推荐使用的方式。mysql分表对于php来说实际上是分区。
1. 分区设计
比如,设计一个有10个分区的商品表
create tablegoods(
id int auto_increment,
name varchar(32) not null default '',
price int not null default 0,
pubdate datetime not null default'0000-00-00',
primary key(id)
)engine=Myisamcharset=utf8
partition bykey(id) partitions 10;
查看数据表源文件,可以看到有10个goods表的分区文件。给该表存放数据,会平均存放到数据表里面。
2. 四种分表分区算法
各种分区设计关联的字段必须是主键的一部分,或者是主键本身、或者是复合主键索引的从属主键部分。
求余类型的算法
key 根据指定的字段进行分区设计。
hash 根据指定的表达式进行分区设计。
条件
range 字段/表达式 符合某个条件范围的分区设计
list 字段/表达式 符合某个列表范围的分区设计
1) key的分区算法
create tablegoods(
id int auto_increment,
name varchar(32) not null default '',
price int not null default 0,
pubdate datetime not null default'0000-00-00',
primary key(id)
)engine=Myisamcharset=utf8
partition bykey(id) partitions 10;
2) hash分区算法
根据指定的表达式进行分区设计。设计分区的时候,分区字段必须是主键的一部分。设计复合主键。primary key(id,xxx)
month()可以获得时间信息的月份信息
create table goods_m(
id int auto_increment,
name varchar(32) not null default '',
price int not null default 0,
pubdate datetime not null default'0000-00-00',
primary key(id,pubdate)
)engine=Myisamcharset=utf8
partition by hash(month(pubdate))partitions 12;
根据月份分区,可以分为12个区。
插入数据
insert into goods_mvalues(null,'haha',12,'2017-01-01');
insert intogoods_m values(null,'haha',12,'2017-05-01');
insert intogoods_m values(null,'haha',12,'2017-08-01');
查看分区的数据文件的大小,可以看到对应月份的分区编号的文件的大小的变化。
3) range条件范围分区
根据年代对数据表进行分区设计
range(字段/表达式)
partion by range(字段/表达式)(
partition 分区名字 values less than(常量),
)
比如:
create table goods_r(
id int auto_increment,
name varchar(32) not null default '',
price int not null default 0,
pubdate datetime not null default'0000-00-00',
primary key(id,pubdate)
)engine=Myisamcharset=utf8
partition by range(year(pubdate))(
partition hou70 values less than(1980),
partition hou80 values less than(1990),
partition hou90 values less than(2000)
);
插入数据,查看分区表文件变化
insert into goods_rvalues(null,'haha',12,'1978-01-01');
insert into goods_rvalues(null,'haha',12,'1987-05-01');
如果插入的数据,不在任意分区表达式范围内,将无法插入
insert into goods_rvalues(null,'haha',12,'2010-08-01');
4) list列表范围分区算法
根据月份所属季节分区设计
partition by list(字段/表达式)(
partition 分区名字 values in(n1,n2,n3…),
)
create table goods_l(
id int auto_increment,
name varchar(32) not null default '',
price int not null default 0,
pubdate datetime not null default'0000-00-00',
primary key(id,pubdate)
)engine=Myisamcharset=utf8
partition by list(month(pubdate))(
partition chun values in(3,4,5),
partition xia values in(6,7,8),
partition qiu values in(9,10,11),
partition dong values in(12,1,2)
);
插入数据,查看数据表文件变化。
insert into goods_lvalues(null,'haha',12,'1978-01-01');
insert into goods_lvalues(null,'haha',12,'1978-07-01');
key,该方式分区不明显,但是大致作用是明显的。
hash/range/list,会根据业务特点把数据写入到对应的分区表里边。
5) 分区管理
增加、减少分区
A. 求余(key、hash)算法管理
增加分区,alter table 表名 add partition partitions 数量;
减少分区,alter table 表名 coalesce partition 数量;
减少分区,会丢失对应分区的数据。
B. 条件(range、list)算法管理
range增加分区,
alter table 表名 add partition (
partition 分区名 values less than(常量),
partition 分区名 values less than(常量),
…
)
list增加分区,
alter table 表名 add partition (
partition 分区名 values in(列表),
partition 分区名 values in(列表),
…
)
range、list减少分区,
alter table 表名 drop partition 分区名称;
减少分区,会丢失对应分区的数据。
3. 旧版本的物理分表设计
mysql5.1之前的分表是纯物理分表。
比如,一个goods表手动分为goods#1,goods#2,goods#3,…
这种物理分区设计,需要后端语言,比如php,进行算法设计,将数据平均分配给每个表存储。
比如为一个表设计4个表,
create table goods_0(
id int auto_increment,
name varchar(32) not null default '',
price int not null default 0,
pubdate datetime not null default'0000-00-00',
primary key(id,pubdate)
)engine=Myisamcharset=utf8;
create table goods_1(
id int auto_increment,
name varchar(32) not null default '',
price int not null default 0,
pubdate datetime not null default'0000-00-00',
primary key(id,pubdate)
)engine=Myisamcharset=utf8;
create table goods_2(
id int auto_increment,
name varchar(32) not null default '',
price int not null default 0,
pubdate datetime not null default'0000-00-00',
primary key(id,pubdate)
)engine=Myisamcharset=utf8;
create table goods_3(
id int auto_increment,
name varchar(32) not null default '',
price int not null default 0,
pubdate datetime not null default'0000-00-00',
primary key(id,pubdate)
)engine=Myisamcharset=utf8;
create table goods_4(
id int auto_increment,
name varchar(32) not null default '',
price int not null default 0,
pubdate datetime not null default'0000-00-00',
primary key(id,pubdate)
)engine=Myisamcharset=utf8;
为数据表分配数据的原则就是平均,比如根据主键,主键%5,余数n就是操作的数据表的后缀,good_n。
根据id获得一条记录信息时,到指定的分表获得指定的记录信息。php语言
$yushu = $id%5;
$sql ="select * from goods_$yushu where id=$id";
给指定的分表写入指定的记录信息
首先,获得数据表内部最大的主键id。可以获得全部分表最大的主键id值,取其中最大的,这种做法比较繁琐。另一种解决方法时,维护一张辅助表,只有id一个字段,只要任何一个分表写入一条数据,该辅助表id都累加1。
这样操作时
$sql ="insert into goods_fu values(null)";//辅表维护最大id
$maxid ="select last_insert_id() from goods_fu " ;//获得刚刚插入到辅表生成的id
$yu = $maxid%5;
$sql ="insert into goods_$yu values($maxid,...)";
4. 垂直分表
对记录进行分割并存储到许多不同的表,称为水平分表,如上。
对字段进行分割并存储到许多不同的表,称为垂直分表。
一个数据表内部有许多字段,有的字段频繁被操作,有的字段很少被操作。这样当操作数据表中一些字段的时候,没有直接业务关系的字段也需要给其分配相应的资源。如果不分表,速度会稍慢,需要消耗额外的工作量。
比如用户表如下,
id 名称 密码 生日 身高 体重 手机号 qq号码 爱好 城市 简介
其中经常操作的字段是id 名称 密码 手机号
垂直分表设计,一个user表,id 名称 密码 手机号;一个user_fu,id 生日 身高 体重 qq号码 爱好 城市 简介。user表被频繁操作,后期数据需要同时维护两个数据表。
比如,insert into user values(…);生成一个主表的id$zhuid。然后,维护辅表insert into user_fu values ($zhuid,….);
八、 mysql架构设计
mysql服务器的操作主要是crud。其中,查询操作最为频繁。查询操作最消耗资源。
架构设计,就是将一个mysql服务器的工作,平摊给多个mysql服务器实现。多个数据库设计与redis的分布式设计类似,主从模式,也称为一主多从模式,也称为读写分离模式。
九、 慢查询日志
系统运行起来,内部需要执行许多sql语句。此时要把查询速度很慢的sql语句统计出来,并做优化。
设定一个时间阀值,超过这个事件,说明sql语句执行很慢。
show variables like 'slow_query%';
开启慢查询日志,set global slow_query_log=1;
查看慢查询的时间,show variables like 'long_query_time%';
设置慢查询时间,set long_query_time=1;
执行sql语句,查看效果。
select * from empwhere empno=(select empno from emp where empno=1700000);
执行结果超过1s。结果在P0EWYQOFFBJ5WV0-slow.log文件中,记录的相关内容为:
Time Id Command Argument
# Time: 17123015:00:31
# User@Host:root[root] @ localhost [127.0.0.1]
# Query_time:1.805103 Lock_time: 0.003000 Rows_sent:1 Rows_examined: 3600002
use php1;
SETtimestamp=1514617231;
select * from empwhere empno=(select empno from emp where empno=1700000);
十、 大量数据写入优化
大量写入数据,要保证数据非常快地写入到数据库中。使用insert into 表名 values(),(),()…
这个insert语句可以同时写入多条记录信息,但不适宜写入太多。为了避免意外发生,可以一次少写一些,例如每次写入1000条。100万,执行1000次就可以了。分批分时间把数据写入到数据库中。
这种设计写入大量数据的方法损耗的时间,每写入1000条,就为1000条数据维护索引,写1000次,就要维护索引1000次,有大量的时间用在了维护索引上。所以,这种设计的优化主要是优化索引的维护,达到整体运行时间的减少。
方法是,先把索引停掉,专门先把数据写入到数据库,最后再一次性维护索引。
1. myisam数据表
如果数据表中已经存在数据,也就是索引已经存在一部分。alter table 表名 disable keys;然后大量写入数据,写入完毕,alter table 表名 enable keys;//最后统一维护索引。
如果数据表中没有数据,也就是索引内部没有东西。先直接删除到索引,alter table 表名 drop primary key,drop index 索引名称(唯一/普通/全文);然后,大量写入数据,写入后,加入索引,alter table 表名 add primary key(id),(唯一/全文)index 索引名(字段);
2. innodb数据表
该存储引擎支持事务。事务的支持使得可以一次性写入大量sql语句。
具体操作:
start transaction;
大量数据写入(100万记录信息 insert 被执行1000次,这样索引只维护1次);
commit;
十一、 sql查询语句优化
1. 单表查询和多表查询
数据库操作有的时候涉及到连表查询操作。
复合查询一般要涉及到多个数据表。多个数据表一起查询的好处,sql语句逻辑清晰、简单;不好的地方,消耗资源大,时间长。不利于数据表的并发处理。
比如,a表和b表做连表查询,需要2秒,这2秒内,这两个表就被锁住了。那么这2秒就不能操作这两个表。select b.bd_id,b.name,count(g.*) from Brand b join Goods g on b.bd_id=g.bd_id group by b. bd _id; //比如这条执行需要4秒
如果业务要求数据库的并发性好,需要把连表查询的多表查询变为单表查询。
步骤,
select bd_id,count(*) from Goods group bd_id;//查询每个品牌的商品数量,比如执行需要2秒
select bd_id,name from Brand;//这个查询需要2秒
在后端语言中,比如php,通过逻辑代码整个上面两个sql查询的结果,需要1秒。
2. limit的使用
数据分页使用limit。
limit 偏移量((当前页码-1)*每页条数),长度(每页条数)
对比
select * from emp limit 1300000,10;//运行速度慢
select * from emp where empno>1300001 limit 10;//运行速度快
后者比前者速度快很多。
单纯运行limit运行时间,比较长,因为内部没有使用索引,是一页一页的跨越取的,类似查字典时候的翻阅查找的效果,效率低。使用where关键字,计算出当前页的首条数据的id,结合limit使用可以优化limit查询。
可以使用explain select * from emp limit 1300000,10\G; explain select * from emp where empno>1300001 limit 10\G;查看是否使用索引。
3. order by null
强制不排序,有的sql语句在执行时,本身会有排序效果。但是有时不需要排序效果,但是本身自带的排序动作会浪费资源,如果强制限制排序,就可以节省资源。
比如使用group by的时候默认会排序。如果后面跟上order by null,强制不排序,可以节省资源,提高效率。