mysqlOptimize

一、           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，强制不排序，可以节省资源，提高效率。