Mysql 优化

优化 152 浏览

MYSQL优化主要分为以下四大方面:

架构:主从复制,读写分离,负载均衡。

设计:存储引擎,字段类型,范式与逆范式

功能:索引,缓存,分区分表。

SQL:慢查询,经验。

| 服务器架构

服务器的架构,通常是由多台服务器通过负载均衡组合成的服务器集群。

主从复制Mysql服务器内部支持复制功能,仅仅需要通过简单配置即可,通常是一主多从的典型结构:主服务器负责写数据,从服务器负责读数据。

读写分离、负载均衡开启主从复制后,PHP不再操作Mysql数据库服务器,而是去操作读写分离、负载均衡服务器,只要服务器安装了mysql proxy或Ameoba软件就可以实现读写分离和负载均衡,读写分离是指该服务器会判断客户端的操作是读还是写,从而选择操作mysql主服务器还是从服务器。负载均衡是指,客户端读操作时,该服务器会根据取余算法去选择一台从服务器。

这种结构具有高性能(高效性),但是需要保证高可用(稳定性)。所以服务器集群需要有各自备用服务器,时时心跳检测当前使用的服务器,如果宕机立马切换备用服务器。

主从复制延迟?

待补充

| 表设计

创建数据库时,我们需要设计好表结构,一般考虑以下几点:

一、存储引擎选择存储引擎是一种用来存储MySQL中对象(记录和索引)的一种特定的结构(文件结构),处于MySQL服务器的最底层,直接存储数据。一般使用的存储引擎是myisam和innodb。

1、InnoDB

事务安全型存储引擎,擅长事务数据的完整性和安全性及高并发处理,不擅长快速插入(插入前要排序,消耗时间)和检索。支持事务,外键约束,都是为了维护数据的完整性。

存储格式:数据,索引集中存储,存储于同一个表空间文件中。.frm是表结构文件,.ibd是表索引文件,包括了单个表的数据及索引内容。

存储顺序:主键顺序存储,插入时做排序工作,效率低。

行级锁定:实现了行级锁定,在一定情况下,可以选择行级锁来提升并发性。也支持表级锁定,Innodb会自带锁,不需要我们自己设置。多版本并发控制, MVCC,效果达到无阻塞读操作。

2、MyISAM

擅长处理高速读写。不支持事物,支持全文索引,支持数据压缩存储。

存储格式:数据和索引分别存储于不同的文件中。.frm是表结构文件,.MYD是表数据文件,.MYI是表索引文件。

存储顺序:插入顺序(没有经过排序),插入速度快,空间占用量小。

表级锁定:仅仅支持表级锁定,不支持高并发。支持并发插入,写操作中的插入操作,不会阻塞读操作(其他操作)。

锁的概念?

当客户端操作表(记录)时,为了保证操作的隔离性(多个客户端操作不能互相影响),通过加锁来处理。

操作方面:

读锁:读操作时增加的锁,也叫共享锁,S-lock。特征是阻塞其他客户端的写操作,不阻塞读操作。(并发读)

写锁:写操作时增加的锁,也叫独占锁或排他锁,X-lock。特征是阻塞其他客户端的读,写操作。

锁定粒度(范围):

行级:提升并发性,锁本身开销大

表级:不利于并发性,锁本身开销小。

如何选择存储引擎?

那么对于微博项目来看:

a.微博主要是插入微博和查询微博列表,较为适合MyISAM;

b.微博在更新微博和删除微博,要少的多,较为适合MyISAM;

c.对数据完整性的需求并没有那么强烈,比如用户删除微博,关联的转播和评论并不要求都做相应的行为,较为适合MyISAM;

那么对于记账财务系统:

a.财务系统除了读取和插入,经常要进行数据的修改和删除,较为适合InnoDB;

b.在进行财务变更的时候,如果失败需要回滚必须用到事务,较为适合InnoDB;

c.每个用户的财务数据完整性和同步性非常重要,需要外键支持,否则财务将会混乱,较为适合InnoDB。

二、字段类型选择字段类型应该要满足需求。尽可能小(占用存储空间少)、尽可能定长(占用存储空间固定)、尽可能使用整数。

1. 五种整型的适用场景:

TINYINT,年龄,包含在0~255之间;

SMALLINT,端口号,包含在0~65535之间;

MEDIUMINT,中小型网站注册会员,1600万够用;

INT,身份证编号,42亿可以用很久;

BIGINT,Twitter微博量,几百亿

2. 日期类型考虑:

TIMESTAMP(4字节)有几个特点:

a.当更新一条数据的时候,设置此类型根据当前系统更新可自动更新时间;

b.如果插入一条NULL,也会自动插入当前系统时间;

c.创建字段时,系统会自动给一个默认值;

d.会根据当前时区来存储和查询时间,存储时对当前时区进行转换,查询时再转换为当前的时区。

综合考虑:如果有时区等问题使用DATETIME,当然也可以使用INT来保存时间戳。

关于INT存放时间戳的优点如下:

a.INT占4个字节,DATETIME占8个字节;

b.INT存储索引的空间比DATETIME小,查询快,排序效率高;

c.在计算机时间差等范围问题,比较方便。

3. 字符类型考虑:

短文本定长用char,比如手机号(国家码可单独字段);

短文本变长用varchar,文章标题;

长文本用text,文章内容

为何手机号码使用char类型?

bigint占的字节数是8,而char占的字节数时11,为何不使用bigint?

a.手机号的本质是字符串而不是数字,只是恰巧长得像数字而已,而且也不需要手机号去做运算;

b.字符串可以通过like去匹配尾号或者首号,查询很方便;

c.手机号属于敏感信息,需要对字符串进行*处理,强类型语言每次格式转换也麻烦。

4. 列属性考虑:无符号(UNSIGNED)和填充零(ZEROFILL),还有是否为空、默认值、主键、自动编号。

三、范式与逆范式为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库,必须满足一定的范式。

第一范式1NF,原子性

第二范式2NF,消除主键部分依赖

第三范式3NF,消除主键传递依赖

1、范式

(1)第一范式:具有原子性,确保每列保持原子性。

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分,那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储,这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。

(2)第二范式:主键列与非主键列遵循完全函数依赖关系,确保表中的每列都和主键相关。

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。

(3)第三范式:非主键列之间没有传递函数依赖关系索引,确保每列都和主键列直接相关,而不是间接相关。

比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。

先满足第一范式,再满足第二范式,才能满足第三范式。

2、逆范式

逆范式是指打破范式,通过增加冗余或重复的数据来提高数据库的性能。

示例: 假如有一个商品表goods:

字段有goods_id(商品表), goods_name(商品名称), cat_id(所属类别的id)。

还有一个分类表category:

字段有cat_id(类别id), cat_name(类别名称)。

现在要查询类别id为3的商品的数量,例如分类列表查询:

可以使用下列sql语句:

select c.*, count(g.goods_id) as goods_count from category as c left join goods as g c.cat_id=g.cat_id group by c.cat_id;

但是,假如商品数量较大,那么就比较耗性能了。这时,我们可以考虑重新设计category表:增加存当前分类下商品数量的字段。

cat_id, cat_name, goods_count

每当商品改动时,修改对应分类的数量信息。

再查询分类列表时:select * from category;

此时额外的消耗,出现在维护该字段的正确性上,保证商品的任何更新都正确的处理该数量才可以。

| 功能

一、索引:利用关键字,就是记录的部分数据(某个字段,某些字段,某个字段的一部分),建立与记录位置的对应关系,就是索引。索引的关键字一定是排序的。索引本质上是表字段的有序子集,它是提高查询速度最有效的方法。一个没有建立任何索引的表,就相当于一本没有目录的书,在每次查询时就会进行全表扫描,这样会导致查询效率极低、速度也极慢。如果建立索引,那么就好比一本添加的目录,通过目录的指引,迅速翻阅到指定的章节,提升的查询性能,节约了查询资源。

索引类型

从索引的定义方式和用途中来看:主键索引,唯一索引,普通索引,全文索引。

无论任何类型,都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。

以上类型的差异:对索引关键字的要求不同。

关键字:记录的部分数据(某个字段,某些字段,某个字段的一部分)。

普通索引,index:对关键字没有要求。

唯一索引,unique index:要求关键字不能重复。同时增加唯一约束。

主键索引,primary key:要求关键字不能重复,也不能为NULL。同时增加主键约束。

全文索引,fulltext key:关键字的来源不是所有字段的数据,而是从字段中提取的特别关键词。(无法识别中文)

索引原则

1.列独立:如果需要某个字段上使用索引,则需要在字段参与的表达中,保证字段独立在一侧。如:

select * from emp where id + 1 = 5;  这种就无法使用索引。

2.左原则:索引遵循最左原则。

模糊查询:like匹配模式必须要左边确定不能以通配符开头。

select * from emp where name like '%abc';  这种无法使用索引。

复合索引:一个索引关联多个字段,仅仅针对左边字段有效果。

3.OR的使用:必须要保证 OR 两端的条件都存在可以用的索引,该查询才可以使用索引。

4.MySQL智能选择:即使满足了上面说原则,MySQL也能弃用索引。因为查询即使使用索引,也会出现大量的随机IO,相比全表开销还要大。如性别、支付状态等状态值字段往往只有极少的几种取值可能,这种字段即使建立索引,也往往利用不上。这是因为,一个状态值可能匹配大量的记录,这种情况MySQL会认为利用索引比全表扫描的效率低,从而弃用索引。索引是随机访问磁盘,而全表扫描是顺序访问磁盘。

总结:

a、不要过度索引。索引越多,占用空间越大,反而性能变慢;

b.只对WHERE子句中频繁使用的建立索引;

c.尽可能使用唯一索引,重复值越少,索引效果越强;

d.使用短索引,如果char(255)太大,应该给它指定一个前缀长度,大部分情况下前10位或20位值基本是唯一的,那么就不要对整个列进行索引;

e.充分利用左前缀,这是针对复合索引,因为WHERE语句如果有AND并列,只能识别一个索引(获取记录最少的那个),索引需要使用复合索引,那么应该将WHERE最频繁的放置在左边;

f.索引存在,如果没有满足使用原则,也会导致索引无效

索引使用场景

1.检索数据时使用,where

2.排序是使用,order by

3.连表时使用,join ... on,对join语句匹配关系(on)涉及的字段建立索引能够提高效率

4.索引覆盖,如果要查询的字段都建立过索引,那么引擎会直接在索引表中查询而不会访问原始数据(否则只要有一个字段没有建立索引就会做全表扫描),这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段,以增加索引覆盖的几率。

总结:

a.建立基础索引:在where、order by、join字段上建立索引;

b.如果条件经常性出现在一起,那么可以考虑将多字段索引升级为复合索引;

c.如果通过增加个别字段的索引,就可以出现索引覆盖,那么可以考虑为该字段建立索引;

d.这里值得注意的是不要想着为每个字段建立索引,因为优先使用索引的优势就在于其体积小。

二、查询缓存:query_cache,将select的结果,存取起来共二次使用的缓存区域。一旦开启查询缓存,MySQL会将所有可以被缓存的select语句都缓存。如果存在不想使用缓存的SQL执行,则可以使用 SQL_NO_CACHE语法提示达到目的。

注意事项:

a.查询缓存存在判断是严格依赖于select语句本身的:严格保证SQL一致,因为缓存是以sql语句为key存储的,多一个空格或者大小写不一样都不行。动态查询无法缓存,如select now()。

b.这里的缓存仅当数据表的记录改变时,缓存才会被删除,而不是依靠过期时间的。

三、分区分表:日常开发中我们经常会遇到大表的情况,所谓的大表是指存储了千万级条记录的表。这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕。分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率。

分区,partition,分区是将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了。客户端读写的时候操作的还是大表名字,db自动去组织分区的数据。

分表,是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表(垂直分割,水平分割),我们可以称为子表。这些子表可以分布在同一块磁盘上,也可以在不同的机器上。客户端读写的时候根据事先定义好的规则得到对应的子表名,然后去操作它。分表技术是比较麻烦的,需要手动去创建子表,客户端服务端读写时候需要计算子表名。采用merge好一些,但也要创建子表和配置子表间的union关系。(需要手动分表)

分区分表都能提高mysql的性能,在高并发的状态下都有一个良好的表现;分区和分表不矛盾,可以相互配合。对于那些大访问量,并且表数据比较多的表,可以采取分表或者分区分表结合的方式;访问量不大,但表数据很多的表,可以采取分区的方式。

| SQL优化

查询慢日志:开启慢日志,设置慢查询临界点,定位执行较慢的查询语句,用explain去分析慢查询原因,并根据实际去处理。

show variables like 'slow_query%';

show variables like 'long_query%';

slow_query_log = 0|1

long_query_time = N 超过该时间临界点,就为慢查询。

开启日志

set global slow_query_log=1; set long_query_time=0.5;

SQL经验:

1.对于并发性的SQL

少用(不用)多表操作(子查询,联合查询),而是将复杂的SQL拆分多次执行,因为多表查询也是单表处理,最后合并结果的。如果查询很原子(很小),还会增加查询缓存的利用率。

2.大量数据的插入

多条insert或者Load data into table(从文件里载入数据到表里)

建议,先关闭约束及索引,完成数据插入,再重新生成索引及约束。

针对于myisam,步骤:

Alter table table_name disable keys; 禁用索引约束

大量的插入

Alter table table_name enable keys; 启用

针对innodb,步骤:

Drop index, drop constraint 删除索引及约束,要保留主键

Begin transaction|set autocommit=0; 开启事务,不让他自动提交

[数据本身已经按照主键值排序]

大量的插入

Commit;

Add index, add constraint

3.分段查询

Limit 的使用,会大大提升无效数据的检索(被跳过),因为是先检索,检索会检索全部,再取得想要的。好的做法是使用条件等过滤方式,将检索到的数据尽可能精确定位到需要的数据上。

4.随机选一些数据,不要使用order by rand()

select * from emp order by rand(); 会导致每条记录都执行rand(),成本很高!

建议在应用程序中,先确定的随机主键,再从数据表中获取数据。

5.单条数据 limit 1

如果可以确定仅仅检索一条,建议加上limit 1,其实ORM框架帮我们做到了这一点(查询单条的操作都会自动加上limit 1)。

6.尽量少用 select *

尽量选择自己需要的字段去select,增加索引覆盖的几率;如果表字段较多,需要的字段较少,无疑降低了查询效率。


参考:https://m.2cto.com/database/201701/557910.html

|  版权声明:本文为博主原创文章,转载请注明出处。