Mysql优化总结

Mysql优化主要方向

  • 表的设计合理化(符合3NF)
  • 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
  • 分表技术(水平分割、垂直分割)
  • 读写[写: update/delete/add]分离
  • 存储过程 [模块化编程,可以提高速度]
  • 对mysql配置优化 [配置最大并发数my.cnf, 调整缓存大小 ]
  • mysql服务器硬件升级
  • 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

什么样的表才是符合3NF (范式)

  • 1NF: 即表的列的具有原子性,不可再分解,即列的信息,不能分解, 只有数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server),就自动的满足1NF
  • 2NF: 表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现
  • 3NF: 即表中不要有冗余数据, 就是说,表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放. 比如下面的设计就是不满足3NF


    image.png

sql语句优化

一般步骤

  • 通过show status命令了解各种SQL的执行频率。
  • 定位执行效率较低的SQL语句-(定位慢sql)
  • 通过explain分析低效率的SQL语句的执行情况
  • 确定问题并采取相应的优化措施

show status

show [session|global] status like .... 

如果你不写 [session|global] 默认是session 会话,指取出当前窗口的执行,如果你想看所有(从mysql 启动到现在,则应该 global)

/*其中Com_XXX表示XXX语句所执行的次数
重点注意:
Com_select,Com_insert,Com_update,Com_delete通过这几个参数,
可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主,
以及各类的SQL大致的执行比例是多少。*/
show status like 'Com_XXX';
image.png
//服务器工作的时间(单位秒)
show status like 'uptime';
//试图连接MySQL服务器的次数
show status like 'connections';
//慢查询的次数 (默认是慢查询时间10s)
show status like 'slow_queries';
image.png

如何定位慢查询

默认情况下,mysql认为10秒才是一个慢查询.
//可以显示当前慢查询时间
show variables like 'long_query_time';
//可以修改慢查询时间为0.4s 
//或者修改my.cnf配置文件的[mysqld] 下long_query_time = 0.4
set long_query_time=0.4 ;
image.png

image.png
  • 慢查询日志配置
//默认情况下slow_query_log的值为OFF,表示慢查询日志是禁用的,
show variables like '%slow_query_log%';
//可以通过设置slow_query_log的值来开启
////或者修改my.cnf配置文件的[mysqld] 下slow_query_log = 1
set global slow_query_log=1;
image.png

image.png
  • 产生慢查询
select * from emp where empno=999999;
image.png

image.png

image.png

explain分析

explain工具能详细告诉你 Mysql 打算如何执行sql


image.png
  • id: SELECT识别符。这是SELECT的查询序列号
  • select_type:
    PRIMARY:子查询中最外层查询
    SUBQUERY:子查询内层第一个SELECT,结果不依赖于外部查询
    DEPENDENT SUBQUERY:子查询内层第一个SELECT,依赖于外部查询
    UNION:UNION语句中第二个SELECT开始后面所有SELECT,
    SIMPLE:简单的SELECT语句(不包括UNION操作或子查询操作)
    UNION RESULT:UNION 中合并结果
  • table
    显示这一步所访问数据库中表名称
  • type
    ALL:完整的表扫描
    system:表仅有一行(=系统表)。这是const联接类型的一个特例
    const:表最多有一个匹配行
  • possible_keys:可以利用的索引,如果没有任何索引显示null
  • key:Mysql 从 possible_keys 所选择使用索引
  • key_length:使用到的索引长度
  • ref:引用到的上一个表的列
  • rows:rows_examined,要得到最终记录索要扫描经过的记录数
  • Extra
      1.Using FileSort:说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,即MySQL无法使用索引完成的排序称为"文件排序"
      2.Using temporary:使用了临时表来保存中间结果,MYSQL在对查询结果进行排序的时候使用了临时表,常见于排序OrderBy 和分组查询GroupBy
    Using FileSort只是不能按照索引方法进行排序,但是Using temporary会创建一张临时表,将缓存数据存放在临时表中,然后再删除临时表,操作变得更凶险了
      3.Using Index:
    表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错
    如果同时出现Using Where,表明索引被用来执行索引键值的查找
    如果没有同时出现Using Where,表明索引用来读取数据而非执行查找工作
      4.Using Where:使用where过滤条件
      5.Using Join Buffer:使用了连接缓存(join太多个表,配置文件里面的JoinBuffer的值可以调大一点)
      6.Impossible Where:where子句的值总是false,不能获取任何元组
      7.Select tables optimized away:在没有GroupBy子句的情况下,基于索引优化Min/Max操作或者对于MyISAM存储引擎优化Count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化
      8.distinct:优化distinct操作,在找到第一个匹配的元组后即停止找同样值的动作
explain select * from emp where empno=999999;
image.png

建立适当的索引

注意点

  唯一索引和主键索引:唯一索引字段可以为NULL,并可以有多NULL, 但是如果是具体内容,则不能重复
  主键字段:不能为NULL,也不能重复
  修改索引:先删除,再重新创建.

增加索引

ALTER TABLE emp ADD INDEX index_empno (`empno`) ;
image.png

image.png

为什么创建索引后,速度就会变快?

show index from emp;
image.png

image.png

为了加快数据的查找,可以维护一个如上图所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据,比如检索5次就扫描2*2*2*2*2=32个数据。

索引使用

索引的代价

  • 占用磁盘空间
  • 对dmi(update delete insert)语句的效率降低

哪些列上适合添加索引

  • 较频繁的作为查询条件字段应该创建索引
//empno应该创建索引
select * from emp where empno = 999999
  • 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
/*索引算法为BTREE,所以唯一性太差的字段索引的二叉查找树太浅,
查找效率不好而且还要占用磁盘空间和降低dmi效率
比如:sex字段*/
select * from emp where sex = '男'
  • 更新非常频繁的字段不适合创建索引
  • 不会出现在WHERE子句中字段不该创建索引

使用索引的注意事项

  • 对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用
 //  dname 左边的列,loc就是右边的列
alter table dept add index my_ind (dname,loc);
//未使用最左边的列不会使用到索引
explain select * from dept where loc='aaa'\G
image.png
  • 对于使用like的查询,查询如果是 '%aaa' 不会使用到索引,'aaa%' 会使用到索引
//不能使用索引,即,在like查询时,关键的 ‘关键字’ , 最前面,不能使用 % 或者 _这样的字符., 
//如果一定要前面有变化的值,则考虑使用 全文索引
explain select * from dept where dname like '%aaa'\G;
image.png
  • 如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引
explain select * from dept where dname='aQeYCTKUum' or loc='dDJxrtSh' or deptno=101
image.png
  • 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
explain select * from dept where dname=111;
image.png

如何查看索引使用的情况

/*大家可以注意:
        handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
    handler_read_rnd_next:这个值越高,说明查询低效。
*/
show status like 'Handler_read%';
image.png

sql语句的小技巧

  • 在使用group by 分组查询是,默认分组后,还会排序,可能会降低速度.
explain select deptno from dept group by deptno\G;
explain select deptno from dept group by deptno order by null \G;
image.png

如何选择mysql的存储引擎

image.png

myisam 存储: 如果表对事务要求不高,同时是以查询和添加为主的,我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表,回复表.

INNODB 存储: 对事务要求高,保存的数据都是重要数据,我们建议使用INNODB,比如订单表,账号表.

如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理

create table test100(
  id int unsigned ,
  name varchar(32)
)engine=myisam;


insert into test100 values(1,'aaaaa');
insert into test100 values(2,'bbbb');
insert into test100 values(3,'ccccc');

//数据文件目录:/usr/local/mysql/data/temp
image.png

image.png

image.png
delete from test100 where id=3;
image.png

image.png
//我们应该定义对myisam进行整理
optimize table test100;
image.png

image.png

水平分割和垂直分割

  • 水平分割
      如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了。如果我拆成100个表,那么每个表只有10万条记录。当然这 需要数据在逻辑上可以划分。一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能,那么把表按月 拆分成12个,每个查询只查询一个表就够了。如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了。所以一个好的拆分依据是 最重要的。

  • 垂直分割
      有些表记录数并不多,可能也就2、3万条,但是字段却很长,表占用空间很大,检索表时需要执行大量I/O,严重降低了性能。这个时候需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 151,688评论 1 330
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 64,559评论 1 273
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 101,749评论 0 226
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 42,581评论 0 191
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 50,741评论 3 271
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 39,684评论 1 192
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,122评论 2 292
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 29,847评论 0 182
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,441评论 0 228
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 29,939评论 2 232
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,333评论 1 242
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 27,783评论 2 236
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,275评论 3 220
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,830评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,444评论 0 180
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 34,553评论 2 249
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 34,618评论 2 249

推荐阅读更多精彩内容