mysql优化总结

  • baagee 发布于 2018-04-25 13:56:53
  • 分类:MySQL
  • 808 人围观
  • 0 人喜欢

主要常见的两种存储引擎区别

innodb

技术特点:

  • 支持事务,
  • 该类型表的并发性非常高 多人同时操作该数据表 为了操作数据表的时候,数据内容不会随便发生变化,要对信息进行“锁定” 该类型锁定级别为:行锁。只锁定被操作的当前记录。 
  • 支持外键,表结构(.frm)和数据索引(.ibdata1)单独储存在一个文件中
  • 数据的存储是按照主键的顺序排列每个写入的数据,插入操作比较慢

  • 适合做修改、删除 

MyISAM

技术特点:

  • 数据表的表结构(frm)、数据(MYD)、索引(MYI) 都有独立的存储文件,该特点决定了该类型表的写入操作较快。 
  • 该类型并发性较低 该类型的锁定级别为:表锁 
  • 数据表可以被压缩,压缩的数据表有特点:不能频繁的写入操作,只是内容固定的数据表可以做压缩处理 ,比如存储全国地区信息的数据表  。 如果必须要写数据:就解压该数据表,写入数据,再压缩 

  • 适合做查询、写入 

两种存储引擎的选择:

网站大多数情况下“读和写”操作非常多,适合选择 Myisam 类型 

网站对业务逻辑有一定要求(办公网站、商城)适合做修改、删除选择 innodb。Mysql5.5 默认存储引擎都是 innodb 的 

索引

可以明显提升查询 sql 语句的速度 

索引四种类型:

  1. 主键索引:primary key(不能为null,要唯一)
  2. 唯一索引:unique index(内容不能重复)
  3. 普通索引: index
  4. 全文索引:fulltext inedx(myisam可以设置)
  5. 复合索引:索引关联的字段是多个

创建索引:

alter table TABLE_NAME add primary key field

alter table TABLE_NAME add unique index 'name' (field)

alter table TABLE_NAME add index 'name' (field)

alter table TABLE_NAME add fulltext index 'name' (field)

复合索引:索引没有名称,默认把第一个字段取出来当做名称使用

alter table TABLE_NAME add index_name (field1,field2)

删除其他索引(唯一、普通、全文) :

alter table TABLE_NAME frop index name;

删除主键:

首先删除auto_increment属性

alter table TABLE_NAME modify 主键 int not null comment '主键'

alter table TABLE_NAME drop primary key;

索引适合场景

where之后设置查询条件的字段都适合做索引

order by之后的字段最好也加索引

索引覆盖:

当查询到的字段在索引里面,查询速度非常快(直接查询索引,不查询数据表了),称为索引覆盖(黄金索引)

连接查询(join)的关联字段加索引,可以提高连表查询速度

索引原则

1.字段独立原则

select * from emp where  empno=1325467;  //empno 条件字段独立 

select * from emp where  empno+2=1325469; //empno 条件字段不独立 只有独立的条件字段才可以使用索引

2.左原则

like 

% 匹配多个模糊内容

_ 匹配一个模糊内容

查询条件信息在左边确定,右边模糊,就给使用索引 

XXX%    YYY_  使用索引 %AAA%   

_ABC_   %UUU %XXX% 不使用索引

3.复合索引

当有多个字段组成复合索引时,只有第一个字段作为条件时才能使用索引

如果第一个字段内容已经确定,第二个字段也能使用索引

4.OR原则

OR查询的两侧关联条件都必须为索引才可以使用索引,只有一个索引,导致整体都没用索引

前缀索引:

设计索引的字段,不使用全部内容,而只使用该字段前边一部分内容。 

如果字段的前边 N 位的信息已经可以足够标识当前记录信息,就可以把前边 N 位信息设置为索引内容,好处:索引 占据的物理空间小、运行速度就非常快。

举个例子: 

张XX

王XX

李XX

刘XX

以上 4 条记录信息,通过前边一个字就可以唯一标识当前记录信息,创建索引的时候就使用前边第一个字即可,节 省空间、运行速度快。 

具体实现:alter table  表名 add  index (字段(位数)) 

位数怎么确定呢?

总记录数目/前 n 位记录数目 = 比值;当比值稳定不变(接近1)时的n就是合适的位数

SELECT COUNT(*)/COUNT(DISTINCT SUBSTRING(`PASSWORD`,1,1)) FROM pass;
SELECT COUNT(*)/COUNT(DISTINCT SUBSTRING(`PASSWORD`,1,2)) FROM pass;
SELECT COUNT(*)/COUNT(DISTINCT SUBSTRING(`PASSWORD`,1,3)) FROM pass;

全文索引:

当设置全文索引时,使用like "%MySQL%"查询时是不能使用全文索引的,需要变形为:

单个全文索引:

where match(field) against('MySQL')才行

复合全文索引:

where match(field1,field2) against('mysql,php');

查询缓存设置

一条查询 sql 语句有可能获得很多数据,并且有一定的时间消耗。如果该 sql 语句被频繁执行获得数据(这些数据还不经常发生变化),为了使得每次获得的信息速度较快,就可以 把“执行结果”给缓存起来,供后续的每次使用。 

查看并开启:

show variables like "query_cache%"

当query_cache_size=0时表示关闭查询缓存

开启:set global query_cache_size=1024*1024*100(分配100m的缓存空间)

什么情况下不会使用缓存 

sql 语句有变动的信息,就不使用缓存  例如:时间信息、随机数 

注意:获得相同结果的 sql 语句,如果有空格、大小写等内容不同,也会分别进行缓存

不缓存:

针对特殊语句不需要缓存,例如:

select sql_no_cache * from user;

分表分区

数据表拆分为以后,需要考虑 php 如何操作这些数据表。 

php------------([手动/mysql]算法)--------------数据表(分表) 

 

手动算法:需要在 php 语言里边设计操作逻辑,增加 php 语言的代码工作量 mysql 算法:php 语言不需要做额外操作就可以像以往一样操作同一个数据表的不同分区,是 mysql 分表推荐的方式 

注意: 设计分区的字段,需要是主键的一部分

四种分表分区算法:

各种分区设计关联的字段必须是主键的一部分 或者是主键本身、或者是复合主键索引的从属主键部分 

求余:

key    根据指定的字段进行分区设计 

# 根据id分为10个区

create table goods(
	id int auto_increment,
	name varchar(32) not null,
	price int not null default 0,
	time datetime not null default '1015-09-08 12:23:12',
	primary key (id)
)engine=myisam charset=utf8
partition by key(id) partitions 10;

hash   根据指定的表达式进行分区设计 

#根据月份分为12区

create table goods(
	id int auto_increment,
	name varchar(32) not null,
	price int not null default 0,
	time datetime not null default '1015-09-08 12:23:12',
	primary key (id,time)
)engine=myisam charset=utf8
partition by hash(month(time)) partitions 12;

条件: 

range  字段/表达式 符合某个条件范围的分区设计 

#rang条件范围分区

#根据年代

create table goods(
	id int auto_increment,
	name varchar(32) not null,
	price int not null default 0,
	time datetime not null default '1015-09-08 12:23:12',
	primary key (id,time)
)engine=myisam charset=utf8
#				  字段,表达式
partition by range(year(time))(
	#    	  分区名称			条件
	partition hou70 values less than(1980),
	partition hou80 values less than(1990),
	partition hou90 values less than(2000),
	partition hou00 values less than(2010),
);

list   字段/表达式 符合某个列表范围的分区设计 

#列表范围分区

#例如根据季节分区

create table goods(
	id int auto_increment,
	name varchar(32) not null,
	price int not null default 0,
	time datetime not null default '2018-09-08 12:23:12',
	primary key (id,time)
)engine=myisam charset=utf8
partition by list(month(time))(
	#    	  分区名称			条件
	partition spring values in (3,4,5),
	partition summer values in (6,7,8),
	partition autumn values in (9,10,11),
	partition winter values in (12,1,2)
);

key:该方式区分不明显(不一定会严格平均给分区分配数据),但是大方向明显 

hash/range/list:会根据业务特点把数据写入到对应的分区表里边

求余(key、hash)算法管理 

增加分区:alter table 表名  add  partition  partitions  数量; 

减少分区:alter table  表名 coalesce  partition 数量;  

减少分区数据要丢失

条件(range、list)算法管理 

增加分区:

  alter table 表名 add partition(

  partition 分区名  values less than[in] (常量[列表]),   

  partition 分区名  values less than[in] (常量[列表]),   

  ....  

  ) 

  减少分区:  

  alter table 表名 drop  partition 分区名称;

  减少分区,会丢失对应分区的数据。 

垂直分表 

对记录进行分割并存储到许多不同的表,称为“水平分表” 对字段进行分割并存储到许多不同表,称为“垂直分表” 。

一个数据表,内部有许多字段,有的字段频繁被操作,有的字段很少被操作。 这样当操作数据表中一些字段的时候,没有直接业务关系的字段也需要给其分配相应的资源,这样速度会稍慢,还 要消耗系统额外的工作量

mysql优化

大量写入记录信息 

保证数据非常快地写入到数据库中 insert into 表名 values (),(),(),(); 

以上一个 insert 语句可以同时写入多条记录信息,但是不要写入太多 避免意外情况发生。 可以一次少写一些,例如每次写入 1000 条,这样 100 万的记录信息,执行 1000 次 insert 语句就可以了。分批 分时间把数据写入到数据库中。

以上设计写入大量数据的方法损耗的时间: 

写入数据(1000 条)----->为 1000 条数据维护索引 

写入数据(1000 条)----->为第 2 个 1000 条数据维护索引 

...... 

写入数据(1000 条)----->为第 1000 个 1000 条数据维护索引 

以上设计写入 100 万条记录信息,时间主要都被“维护索引”给占据了 如果做优化:就可以减少索引的维护,达到整体运行时间变少。 (索引维护不需要做 1000 次,就想做一次)  

解决: 先把索引给停掉,专门把数据先写入到数据库中,最后在一次性维护索引 

Myisam 数据表 

1) 数据表中已经存在数据(索引已经存在一部分) 

alter table 表名  disable  keys; 

大量写入数据 

alter table 表名  enable keys;  //最后统一维护索引 

2) 数据表中没有数据(索引内部没有东西) 

alter table 表名 drop  primary key ,drop index  索引名称(唯一/普通/全文); 

大量写入数据 

alter table 表名 add primary key(id),(唯一/全文)index  索引名 (字段); 

Innodb 数据表 

该存储引擎支持“事务” 该特性使得我们可以一次性写入大量 sql 语句

具体操作: 

start transaction; 

大量数据写入(100 万条记录信息  insert 被执行 1000 次) 事务内部执行的 insert 的时候,数据还没有写入到数据库 只有数据真实写入到数据库才会执行“索引”维护 

commit;

commit 执行完毕后最后会自动维护一次“索引”; 

单表、多表查询 

数据库操作有的时候设计到 连表查询、子查询操作。 复合查询一般要涉及到多个数据表, 多个数据表一起做查询好处:sql 语句逻辑清晰、简单

其中不妥当的地方是:消耗资源比较多、时间长 不利于数据表的并发处理,因为需要长时间锁住多个表 

例如: 

查询每个品牌下商品的总数量(Goods/Brand) 

Goods:id  name   bd_id 

Brand: bd_id  name  

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; 以上 sql 语句总运行时间是 5s 。但是业务要求是数据库的并发性要高,就需要把”多个查询” 变为 “单表查询” 

步骤: 

① select bd_id,count(*) from Goods group by bd_id; //查询每个品牌的商品数量 //3s 

② select bd_id,name  from Brand;  //3s 

③ 在 php 通过逻辑代码整合① 和 ②  //1s

大数据量时分页查询limit

limit 偏移量,长度这种分页越往后分页速度越满

解决:由单纯 limit 变为 where 和 limit 的组合: 执行速度明显加快,因为其有使用 where 条件字段的索引 

where id>10000000 limit 10;

order by null 

强制不排序 有的 sql 语句在执行的时候,本身默认会有排序效果 但是有的时候我们的业务不需要排序效果,就可以进行强制限制,进而“节省默认排序”的资源


转载请说明出处:baagee博客 » mysql优化总结
标签: mysql

评论

点击图片切换
还没有评论,快来抢沙发吧!