文档章节

mysql调优二

泡海椒
 泡海椒
发布于 2016/04/05 17:25
字数 2206
阅读 69
收藏 8

【推荐】2019 Java 开发者跳槽指南.pdf(吐血整理) >>>

表的优化:
1: 定长与变长分离
如 id int, 占4个字节, char(4) 占4个字符长度,也是定长, time 即每一单元值占的字节是固定的.核心且常用字段,宜建成定长,放在一张表.而varchar, text,blob,这种变长字段,适合单放一张表,
 用主键与核心表关联起来.
2:常用字段和不常用字段要分离.需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来
3:合理添加冗余字段.

列选择原则
1:字段类型优先级 整型 > date,time > enum,char>varchar > blob
整型: 定长,没有国家/地区之分,没有字符集的差异 time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时
,内部要经历串与值的转化Char 定长, 考虑字符集和(排序)校对集varchar, 不定长 要考虑字符集的转换与排序时的校对集,速度慢.text/Blob 无法使用内存临时表
2: 够用就行,不要慷慨 (如smallint,varchar(N))
原因: 大的字段浪费内存,影响速度,以年龄为例 tinyint unsigned not null ,可以存储255岁,足够. 用int浪费了3个字节以varchar(10) ,varchar(300)存储的内容相同, 但在表联查时,varchar(300)要花更多内存
3: 尽量避免用NULL()
原因: NULL不利于索引,要用特殊的字节来标注.在磁盘上占据的空间其实更大.

索引优化策略
1:索引类型
  1.1 B-tree索引注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上, 各引擎稍有不同,比如,严格的说,NDB引擎,使用的是T-tree
 Myisam,innodb中,默认用B-tree索引
  1.2 hash索引
     在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)疑问: 既然hash的查找如此高效,为什么不都用hash索引?
     1:hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.
     2: 不法对范围查询进行优化.
     3: 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机
     4: 排序也无法优化.
     5: 必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据
   2: btree索引的常见误区
     2.1 在where条件常用的列上都加上索引
       例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
       误: cat_id上,和, price上都加上索引.
       错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.

 2.2 在多列上建立索引后,查询哪个列,索引都将发挥作用误: 多列索引上,索引发挥作用,需要满足左前缀要求

聚簇索引与非聚簇索引

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

注意: innodb来说,
1: 主键索引 既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”
聚簇索引
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.
C) 聚簇索引的页分裂过程
高性能索引策略
0:对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.
对于innodb的主键,尽量用整型,而且是递增的整型.
如果是无规律的数据,将会产生的页的分裂,影响速度.

索引覆盖:
索引覆盖是指 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.这种查询速度非常快,称为”索引覆盖”

理想的索引
1:查询频繁 2:区分度高  3:长度小  4: 尽量能覆盖常用查询字段.
1: 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
针对列中的值,从左往右截取部分,来建索引
1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.
所以, 我们要在  区分度 + 长度  两者上,取得一个平衡.


2:对于左前缀不易区分的列 ,建立索引的技巧
1: 把列内容倒过来存储,并建立索引
2: 伪hash索引效果同时存 url_hash列
 3.1 多列索引的考虑因素---  
列的查询频率 , 列的区分度,
mysql> select * from it_area where name like '%东山%';
分析: 这句话用到了索引覆盖没有?
答: 没有,1 查询了所有列, 没有哪个索引,覆盖了所有列.
   2  like %xx%”,左右都是模糊查询, name本身,都没用上索引

第2种做法:
select a.* from it_area as a inner join (select id from it_area where name like '%东山%') as t on a.id=t.id;

发现 第2种做法,虽然语句复杂,但速度却稍占优势.

第2种做法中, 内层查询,只沿着name索引层顺序走, name索引层包含了id值的.
所以,走完索引层之后,找到所有合适的id,
再通过join, 用id一次性查出所有列. 走完name列再取.

第1种做法: 沿着name的索引文件走, 走到满足的条件的索引,就取出其id,
并通过id去取数据, 边走边取.

通过id查找行的过程被延后了. --- 这种技巧,称为”延迟关联”.
索引与排序
排序可能发生2种情况:
1: 对于覆盖索引,直接在索引上查询时,就是有顺序的, using index
2: 先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)

比如: goods商品表, (cat_id,shop_price)组成联合索引,
where cat_id=N order by shop_price ,可以利用索引来排序,
select goods_id,cat_id,shop_price from goods order by shop_price;
// using where,按照shop_price索引取出的结果,本身就是有序的.
select goods_id,cat_id,shop_price from goods order by click_count;
// using filesort 用到了文件排序,即取出的结果再次排序
重复索引与冗余索引
重复索引: 是指 在同1个列(如age), 或者 顺序相同的几个列(age,school), 建立了多个索引,称为重复索引, 重复索引没有任何帮助,只会增大索引文件,拖慢更新速度, 去掉.
冗余索引:
冗余索引是指2个索引所覆盖的列有重叠, 称为冗余索引比如 x,m,列   , 加索引  index x(x),  index xm(x,m)x,xm索引, 两者的x列重叠了,  这种情况,称为冗余索引.
甚至可以把 index mx(m,x) 索引也建立, mx, xm 也不是重复的,因为列的顺序不一样.


索引碎片与维护
在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.比如: 表的引擎为innodb , 可以
 alter table xxx engine innodb optimize table 表名 ,也可以修复.

注意: 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.
这个过程,如果表的行数比较大,也是非常耗费资源的操作.
所以,不能频繁的修复.

如果表的Update操作很频率,可以按周/月,来修复.
如果不频繁,可以更长的周期来做修复.





© 著作权归作者所有

上一篇: Memory Analyzer安装
下一篇: MYSQL调优总结1
泡海椒
粉丝 11
博文 287
码字总数 289460
作品 0
成都
程序员
私信 提问
centos7+mysql5.7二进制安装

一、优化部分 1、操作系统参数调优 2、数据库参数调优 3、防火墙设置等 二、安装部分 1、创建用户和组 # groupadd mysql useradd -g mysql mysql 2、到安装目录,解压安装文件 #cd /data ta...

francisxys
2018/05/18
0
0
性能调优概述

大纲: 一、概述 二、什么是性能调优?(what) 三、为什么需要性能调优?(why) 四、什么时候需要性能调优?(when) 五、什么地方需要性能调优?(where) 六、什么人来进行性能调优?(who) 七、怎...

陈明乾
2014/07/14
0
0
基于 elasticsearch 的网站数据全文检索

【业务需求】 一、功能需求 网站需要向用户提供基于elasticsearch的全文检索功能支持,包括: 1、通过关键字词检索符合用户期望的数据,结合数据匹配度向用户数据 list; 2、良好实现中文、英...

network2019
2017/08/10
57
0
找Java技术经理的职位

毕业时间:08年; 学校:北京化工大学; 学历:大学本科; 籍贯:北京延庆; 目前居住地:西二旗附近; 工作时间:6年; 想找一份Java技术经理的职位,或者可以带人的高级Java开发. 1.有丰富的Java开发经验...

小小太阳
2014/06/19
1K
14
腾讯云CDB的AI技术实践:CDBTune

欢迎大家前往腾讯云+社区,获取更多腾讯海量技术实践干货哦~ 作者:邢家树,高级工程师,目前就职于腾讯TEG基础架构部数据库团队。腾讯数据库技术团队维护MySQL内核分支TXSQL,100%兼容原生M...

腾讯云加社区
2018/06/05
0
0

没有更多内容

加载失败,请刷新页面

加载更多

一篇文章教你轻松使用fastjson

前言 只有光头才能变强。 文本已收录至我的GitHub精选文章,欢迎Star:https://github.com/ZhongFuCheng3y/3y JSON相信大家对他也不陌生了,前后端交互中常常就以JSON来进行数据交换。而有的...

Java3y
35分钟前
4
1
分组功能(tapply,by,aggregate)和* apply系列

每当我想在R中做“ map” py任务时,我通常都会尝试在apply系列中使用一个函数。 但是,我从未完全理解它们之间的区别-{ sapply , lapply等}如何将函数应用于输入/分组输入,输出将是什么样...

javail
35分钟前
4
0
PHP环境搭建之单独安装

还在使用PHP集成环境吗?教你自定义搭建配置PHP开发环境,按照需求进行安装,安装的版本可以自己选择,灵活性更大。 目录: 1. 安装Apache 2. 安装PHP 3. 安装MySQL 4. 安装Composer 观看:h...

不冷的大叔
47分钟前
4
0
为什么JavaScript变量会以美元符号开头?

我经常看到JavaScript带有以美元符号开头的变量。 您何时/为什么选择以这种方式为变量添加前缀? (我不是在问您在jQuery和其他语言中看到的$('p.foo')语法,而是普通变量,例如$name和$orde...

技术盛宴
51分钟前
4
0
TCP 三次握手

https://my.oschina.net/u/4198159/blog/3141874

奋斗的小牛
今天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部