mysql优化总结-(二)设计层面的优化(索引,分区分表,sql语句,存储过程)

mysql优化总结-(一)存储层面的优化(引擎,字段,范式)
接上一篇
本篇重点介绍设计层面的优化

mysql优化,主要从哪些方面去考虑

1.存储层

	数据表的存储引擎选取,
	字段类型的选取,
	逆范式

2.设计层

	索引的使用,
	分区/分表,
	sql语句的优化,
	缓存的使用
	存储过程优化

3.架构层

	分布式部署(读写分离)

4.sql语句层

	使用高效的sql语句

2.1.1索引的使用

索引的概念

利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），
建立与记录位置的对应关系，就是索引。

索引的作用：是用于快速定位实际数据位置的一种机制。

索引的类型

4种类型：
主键索引，唯一索引，普通索引，全文索引

索引的使用:

1.创建索引
	a.建表时:
		create table 表名 (
		id int primary key auto_increment,
		name varchar(32) not null,
		age tinyint notnull,
		intro text,
		unique key (name),
		index (age),
		fulltext index(intro),
		index (name,age)
		)engine myisam charset utf8mb4;
		
name增加唯一索引,
age 增加普通索引,
intro 增加全文索引,
name,age 增加 复合索引

	b.修改表:
	alter table 表名 add unique key (name),add index(age),add fulltext index(intro),add index(name,age);

2.删除索引

	a.删除主键索引：alter table  table_name drop primary key 
	
	主键索引的删除，如果没有auto_increment 属性
	则使用 alter table  表名 drop primary key
	如果在删除主键索引时，该字段中有auto_increment则先去掉该属性再删除。
	
	去除主键的auto_inrement属性：
	alter table 表名 modify id int unsigned not null comment '主键'


	b.删除非主键索引，唯一索引，全文索引，复合索引；
	语法：
		alter table 表名  drop index 索引的名称；

3.查看索引
	show indexes from 表名;
	show index from t表名\G
	
	show create table 表名;
	show keys from 表名;
	desc 表名;

	

创建索引注意事项：

第一：较频繁的作为查询条件字段应该创建索引
	select * from emp where empno = 1

第二：唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
	select * from emp where sex = '男‘

第三：更新非常频繁的字段不适合创建索引
	select * from emp where logincount = 1

第四：不会出现在WHERE子句中字段不该创建索

注意:
索引具体在sql语句执行中,是在词法语法分析,优化器优化之后,形成执行计划过程时去进行分析用到的索引有哪些,
然后才是执行sql语句

2.1.2 索引的数据结构

查看索引的类型：

show keys from 表名;

1、myisam的存储引擎索引结构：

索引的节点中存储的是数据的物理地址（磁道和扇区）
在查找数据时，查找到索引后，根据索引节点中记录的物理地址，查找到具体的数据内容。

2、innodb的存储引擎的索引结构

innodb的主键索引文件上 直接存放该行数据,称为聚簇索引,
非主索引指向对主键的引用（非主键索引的节点存储是主键的id）

比如要通过nam创建的索引，查询name=’liubei’的,先根据name建立的索引，找出该条记录的主键id，
再根据主键的id通过主键索引找出该条记录。

innodb的主索引文件上 直接存放该行数据,称为聚簇索引,非主索引指向对主键的引用
myisam中, 主索引和非主索引,都指向物理行(磁盘位置).

注意:

 innodb来说
 
1: 主键索引 既存储索引值,又在节点中存储行的数据
2: 如果没有主键, 则会Unique key做主键 
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

聚簇索引

优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点上)
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂（索引的节点移动）.

2.1.3索引的使用原则

1.列独立
	只有参与条件表达式的字段独立在关系运算符的一侧，该字段才可能使用到索引。
	“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
	desc select * from user where age-1=10\G

2.like查询
	在使用like(模糊匹配)的时候，在左边没有通配符的情况下，才可以使用索引。
	在mysql里，以%开头的like查询，用不到索引。

3.复合索引
	最左原则：
		对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。 
	还要注意，如果左边查询条件是一个范围，则后面的字段有无法使用索引；
	注意：在多列索引里面，如果有多个查询条件，要想查询效率比较高，比如如下建立的索引，
	index(a,b,c,d)  要保证最左边的列用到索引。

4.mysql智能选择
	如果mysql认为，全表扫描不会慢于使用索引，则mysql会选择放弃索引，直接使用全表扫描。
	一般当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。

5.优化group by语句
	默认情况下， mysql对所有的group by col1,col2进行排序。
	这与在查询中指定order by col1,col2类型，如果查询中包括group by 但用户想要避免排序结果的消耗，
	则可以使用order by null禁止排序。 

2.1.4存储过程

1、概念
存储过程(procedure)

概念类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。
在封	装的语句体里面，可以同if/else ,case,while等控制结构。
可以进行sql编程。

查看现有的存储过程。

show procedure status 

2.存储过程的优点

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，
存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数
（如果该存储过程带有参数）来执行它。
存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

（1）存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，
而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
（2）当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，
可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
（3）存储过程可以重复使用,可减少数据库开发人员的工作量
（4）安全性高,可设定只有某些用户才具有对指定存储过程的使用权
（5）可以简化查询，把复杂的SQL语句创建一个存储过程。

3.创建存储过程

语法：
create procedure 存储过程名(参数1,num) 
	begin 
	//代码
	set num = num*num;
end 


参数的类型：
in（输入参数）： 表示该形参只能接受实参的数据——这是默认值，不写就是in；
out（输出参数）：表示该形参其实是用于将内部的数据“传出”到外部给实参；
inout（输入输出参数）：具有上述2个功能。

案例1

查询一个表里面某些语句 
create procedure p6()
	begin
		select * from goods;
	end$
call p6()

案例2：

第二个存储过程体会参数，使用参数
比如我们取出大于某个价格的商品数据

create procedure p8(price float)
begin
	select * from goods where shop_price>price;
end$

说明：
（1）存储过程中，可有各种编程元素：变量，流程控制，函数调用；
（2）还可以有：增删改查等各种mysql语句；
（3）其中select（或show，或desc）会作为存储过程执行后的“结果集”返回；
（4）形参可以设定数据的“进出方向”：
（5）存储过程是属于数据库，在哪个数据库里面定义的，就在哪个数据库里面调用。
，在别的数据库里面调用其他数据库里面定义的存储过程时，会报如下提示。

procedure php.p8 not exist

4.调用存储过程

语法：
call 存储过程名称(参数)
在php里面如何调用，
mysql_query(‘call p7(5)’);

2.2.1分区

1.分区介绍

基本概念，把一个表，从逻辑上分成多个区域，便于存储数据。
采用分区的前提，数据量非常大。
如果数据表的记录非常多，比如达到上亿条，数据表的活性就大大降低，
数据表的运行速度就比较慢、效率低下，影响mysql数据库的整体性能，就可以采用分区解决，
分区是mysql本身就支持的技术。

查看当前mysql软件是否支持分区；

show variables like '%partition%';

在创建（修改）表时，可以指定表，可以被分成几个区域。

利用表选项：partition 完成。 
create table  table_name(
字段信息，
索引，
)engine myisam charser utf8
partition by 分区算法（分区字段）（
分区选项
）；

2.分区算法：

条件分区：list (列表) range(范围)  取模轮询（hash,key）

（1）list分区

list :条件值为一个数据列表。
通过预定义的列表的值来对数据进行分割

例子：假如你创建一个如下的一个表，该表保存有全国20家分公司的职员记录，
这20家分公司的编号从1到20.而这20家分公司分布在全国4个区域，如下表所示：

职员表：emp
id  name   store_id(分公司的id)
12   小宝     1
14   二宝     6
北部    1,4,5,6,17,18
南部    2,7,9,10,11,13
东部    3,12,19,20
西部    8,14,15,16
insert into emp values(12,’xiaobao’,14)
insert into emp values(15,’二bao’,17)

具体创建分区的代码；
create table p_list(
	id int,
	name varchar(32),
	store_id int
	)engine myisam charset utf8
	partition by list (store_id)(
	partition p_north values in (1,4,5,6,17,18),
	partition p_east values in(2,7,9,10,11,13),
	partition p_south values in(3,12,19,20),
	partition p_west values in(8,14,15,16)
);

创建分区表后查看文件，

添加几条数据，测试是否用到了分区：


explain partitions select * from p_list where store_id=17\G

注意：

在使用分区时，where后面的字段必须是分区字段，才能使用到分区。
如下查询，没有分区条件，则会到所有的分区里面去查找，即便如此，查询效率也要比单表查询高。
explain partitions select * from p_list\G

（2）Range（范围）

这种模式允许将数据划分不同范围。
例如可以将一个表通过月份划分成若干个分区


create table p_range(
id int,
name varchar(32),
birthday date
)engine myisam charset utf8
partition by range (month(birthday))(
	partition p_1 values less than (4),
	partition p_2 values less than(7),
	partition p_3 values less than(10),
	partition p_4 values less than MAXVALUE
);

less than   小于;
MAXVALUE 可能的最大值 

insert into p_range values(1,’xiaobao’,’2016-09-09’);
insert into p_range values(1,’xiaobao’,’2016-11-09’);

（3）Hash（哈希）

对一个表执行HASH分区时候 MySQL会对分区键应用一个散列函数 确定数据应当放在N个分区中的哪个分区中.
create table  p_hash(
	id  int,
	name varchar(20),
	birthday date
)engine myisam charset utf8 
partition by hash(month(birthday)) partitions 5;

（4）Key（键值）

上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
create table p_key(
	id int,
	name varchar(32),
	birthday date
)engine myisam charset utf8
partition by key (id) partitions 5;

3、 分区管理

具体就是对已经存在的分区进行增加、减少操作。

（1）删除分区

删除分区： 
① 在key/hash领域不会造成数据丢失(删除分区后数据会重新整合到剩余的分区去)
② 在range/list领域会造成数据丢失

求余方式(key/hash):
>alter table 表名 coalesce partition 数量;

范围方式(range/list):
>alter table 表名 drop partition 分区名称;

1）删除hash类型分区

删除分区之前，数据如下
insert into p_hash values(1,’xiaobao’,’2018-09-02’)


执行删除分区的操作：alter table p_hash  coalesce partition 4

把5个分表中的4个都删除，只剩下一个

并且，数据没有减少：
剩余唯一一个分区的时候，就禁止删除了，但是可以drop掉整个数据表：

alter table p_hash  coalesce  partition 1;

2）删除list类型分表(数据有对应丢失)

alter table p_list drop partition   p_north;

（2）增加分区

求余方式： key/hash
> alter table 表名  add  partition partitions  数量;

范围方式： range/list
> alter table 表名 add partition(
       partition 名称 values  less than (常量)
       或
       partition 名称 values  in (n,n,n)
   );

1. 给p_hash 增加hash分表

   alter table p_hash add partition partitions 6;

增加后，一共有7个分表体现：
分表增加好后，又把数据平均地分配给各个分表存储。

4、特别注意；

create table p_range2(
	id int primary key auto_increment,
	name varchar(32),
	birthday date
)engine myisam charset utf8
partition by range (month(birthday))(
	partition p_1 values less than (4),
	partition p_2 values less than(7),
	partition p_3 values less than(10),
	partition p_4 values less than MAXVALUE
);

注意：

如果有主键或唯一索引，则创建分区的字段必须是主键或唯一索引的一部分

primary key(id,birthday)
不等价于如下量行代码；
primary key(id)
primary key(birthday)

create table p_range2(
	id  int auto_increment,
	name varchar(32),
	birthday date,
	primary key(id,birthday)
)engine myisam charset utf8
partition by range (month(birthday))(
	partition p_1 values less than (4),
	partition p_2 values less than (7),
	partition p_3 values less than(10),
	partition p_4 values less than MAXVALUE
);


create table p_range3(
	id  int auto_increment,
	name varchar(32),
	birthday date,
	unique key(id,birthday)
)engine myisam charset utf8
partition by range (month(birthday))(
	partition p_1 values less than (3),
	partition p_2 values less than (6),
	partition p_3 values less than(9),
	partition p_4 values less than MAXVALUE
);

2.2.2分表

1.分表设计

物理方式分表设计
自己手动创建多个数据表出来
php程序需要考虑分表算法：数据往哪个表写，从哪个表读  

QQ的登录表。假设QQ的用户有10亿，如果只有一张表，每个用户登录的时候数据库都要从这10亿中查找，会很慢很慢。如果将这一张表分成100份，每张表有1000万条，就小了很多，比如qq0,qq1,qq2…qq99表。

用户登录的时候，可以将用户的id%100，那么会得到0-99的数，查询表的时候，将表名qq跟取模的数连接起来，就构建了表名。比如123456789用户，取模的89，那么就到qq89表查询，查询的时间将会大大缩短。

用户注册时，如何把用户信息存储到多张表里面？假如我们分4个表；
接收表单提交的数据

$username = $_POST[‘username’];
//制作一个用户的id
$user_id = $redis->incr(‘user_id’);
$user_id%/4=(0,1,2,3)

根据取模的值，拼接表名称
比如$user_id = 12;
$user_id%4 = 0;
$table_name  =  ‘user_0’

在注册时，需要存储一个id和username的对应关系，借助redis也可以；
$redis->set(‘register_’.$username,$user_id);

登录时，如何知道查询那张表？
根据用户名，获取出用户的id，根据id%4取模，取模的值拼接成表名；

2.垂直分表（比较常用）

水平分表：是把一个表的全部记录信息分别存储到不同的分表之中。
垂直分表：是把一个表的全部字段分别存储到不同的表里边。

有的时候，一个数据表设计好了，里边有许多字段，但是这些字段有的是经常使用的，有的是不常用的。在进行正常数据表操作的时候，不常用的字段也会占据一定的资源，对整体操作的性能造成一定的干扰、影响。

为了减少资源的开销、提升运行效率，就可以把不常用的字段给创建到一个专门的辅表中去。
同一个业务表的不同字段分别存储到不同数据表的过程就是“垂直分表”。

例如：  

会员数据表有如下字段：
会员表： user_id  登录名  密码  邮箱  手机号码  身高  体重  性别  家庭地址  身份证号码

以上表，常用的，不常用的分开
为了使得常用字段运行速度更快、效率更高，把常用字段给调出来,因此数据表做以下垂直分表设计：

会员表(主)user字段：user_id  登录名  密码  邮箱  手机号码
会员表(辅)user_fu字段：user_id  身高  体重  性别  家庭地址  身份证号码

以上把会员表根据字段是否常用给分为两个表的过程就是垂直分表。

存储文章

经常查询的数据     title（标题）   author（作者）    
dedecms里面的分表设计，就是垂直分表设计


各种类型数据，电影数据，音乐数据   商品数据，软件数据
把各种类型数据的公共的字段，单独存储到一张表里面，比如名称，添加时间，封面图等等。

把各种类型数据的独有的字段，单独存储到一张表里面的；