MySQL优化策略小记
来源:互联网 发布:阿里云连接管理终端 编辑:程序博客网 时间:2024/05/12 00:09
MySQL优化
表的优化与列类型选择
一、表的优化
- 定长与变长分离
如id int, 占4个字节,char(4)占4个字符长度,
即:每一个单元值占的字节是固定的。核心且常用的字段,宜建成定长,放在一张表中。
而varchar、text、blob等。这种变长的字段,适合单放一张表中,用主键与核心表关联起来。 - 常用字段和不常用字段要分离。
需要结合网站具体业务来分析字段的查询场景,查询频度低的字段,单拆出来。 - 在1对多,需要关联统计的字段上,添加冗余字段。
二、列的选择
- 字段类型优先级。整型>date、time>enum,char>varchar>blob、text
列特点分析:- 整型:定长,没有国家地区之分,没有字符集差异。比如:tinyint 1、2、3、4、5 <-char(1) a、b、c、d、e, 从空间上都是占一个字节,但是order by排序,前者更快。因为后者需要考虑字符集和校对集(就是排序规则)。
- time:定长,运算快,节省空间。考考虑时区,写sql时不方便,where ‘2017-09-11’。
- enum:能起约束值的目的,内部用整型来存储,但是与char联查时,内部要经历串与值的转化。
- char:定长,考虑字符集和(排序)校对集。
- varchar:不定长要考虑字符集的转换和排序时的校对集,速度慢。
- text/Blob:无法使用内存临时表(排序等操作只能在磁盘一进行)。
如:性别,以utf8为例
1. char(1) 3个这字长字节。
2. enum(‘男’,’女’) 内部转成数字来存,多了一个转换过程。
3. tinyint() 定长一个字节 0、1、2。
够用就行,不要慷慨(如smallint, varchar(N))
大的字段浪费内存影响速度。以年龄为例:tinyint unsigned not null,用存储到255岁足够用,用int会浪费三个字节。
varchar(10)和varchar(100)存储的内容相同,但在表联查时,后者会花更多内存。尽量避免用NULL()
NULL不利于索引,要用特殊字节来标注,在磁盘上占据的空间也更大,mysql5.5已对NULL做改进,但查询仍然不便例如:
- 可以建立两张字段相同的表,一个允许为NULL,一个不允许为NULL,各自加入一万条数据,查看索引文件的大小,可以发现为NULL的索引要大一些。
- 另外:NULL也不便于查询
- where 列名 = null;
- where 列名 != null; 都查不到值
- where 列名 is null 或 is not null 才能查到值
- Enum列的查询
- enum列在内部是用整型来储存的。
- enum列与enum列相关联速度最快。
- enum列比(var)char 的弱势:在碰到与char关联时,要转化,要花时间; 优势:当char非常长时,enum仍然是整型固定长度。当查询量越大时enum优势越明显。
- enum与char/varchar 关联,因为要转化,速度要比enum -> enum、char -> char要慢,但有时也这样用:数据量特别大时,可以节省IO
索引优化策略
一、索引类型
- B-tree 索引
名叫B-tree索引,大的方面看都用的是平衡树,但具体实现上,各引擎稍有不同,
比如:严格的说,NDB引擎使用的是T-tree,Myisam,InnoDB中默认的是B-tree索引,但抽象一下B-tree系统可理解为“排好序的快速查找结构”。
B-tree的常见误区
- 在where常用的列上都加上索引。例如:where id = 3 and price 100; 查询id为3价格大于100的
- id和price上都加上索引
- 只能用上id和price索引,因为是独立的索引,同时只能使用上1个
- 在多列上建立索引后,查询哪个列,索引都将发挥作用
- 多列索引上,索引发挥作用,需要满足左前缀要求,以index(a,b,c)为例(注意只和顺序有关),见下表
2. Hash 索引
在memory表中默认的是hash索引,hash的理论查询时间复杂度为O(1)。
hash 查询的缺点:
- 索引是hash函数计算后的随机结果,如果是在磁盘上放置数据,比如以id为例,那么随着id增长,id对应的行,在磁盘上随机放置。
- 无法对范围查询进行优化
- 无法利于前缀索引。比如,在B-tree中field列的值“helloworld”,并加索引查询 xx = helloworld,自然可以利用索引 xx = hello 也可以利用索引。因为hash(“helloworld”)和hash(“hello”),两者的关系为伪随机。
- 排序也无法优化
- 必须回行,就是说通过索引只能拿到数据位置,必须要再通过这个地址再回到表中拿数据。
面试题
商品表,有主键good_id,栏目 cat_id,价格price; 在价格上加了牵引,按价格查询时还是很慢,什么原因,怎么解决?
答:实际场景中一个电商网站商品分类很多,直接在所有商品中,按价格查询商品是极少的,一般客户都是来到分类下再查询
改正:去掉单独的price列的索引,加(cat_id, price)复合索引,再查询; 如果根据日志统计,发现好多人这样查:
电脑 –> 品牌 –> 价格 index(cat_id, brand_id, price)
二、非聚簇索引和聚簇索引
- 非聚簇索引
Myisam引擎是使用非聚簇索引 - 聚簇索引
innodb 的主索引文件上,直接存放该行数据,称为聚簇索引,次索引指向对主键的引用
myisam 中,主索引和次索引,都指向物理行(磁盘位置)
注意:
- 主键索引,即存储索引值,又在叶子中存储行的数据。
- 如果没有主键(primary key),则会 Unique key 做主键。
- 如果没有unique key,则系统生成一个内部的rowid 做主键。
- 像innodb中,主键的索引结构中,即存储了主键值,又存储了行数据,这种结构称为“聚簇索引”。
聚簇索引:
- 优势:根据主键查询条目较少时,不用回行(数据就在主键节点下面)
- 劣势:如果碰到不规则数据插入时,造成频繁的页分裂
索引覆盖
索引覆盖是指:如果查询的列恰好是索引的一部份,那么查询只需要在索引文件上进行,不需要再到磁盘上找数据。这种查询速度非常快,称为“索引覆盖”。
理想索引
- 查询频繁
- 区分度高:100万用户性别上基本男女各50万,区分度低
- 长度小:牵引长度直接影响索引文件大小,影响增删改的速度,并间接影响查询速度(占用内存多)
- 尽量能覆盖常用字段
建索引方法:针对列中的值,从左到右截取部分,来建索引
- 截的越短,重复度越高,区分度越低,索引效果越不好
- 截的越长,重复度越低,区分度越高,索引效果越好,带来影响也越大,增删改变慢,并间接影响查询速度。
所以:我们要在 区分度 + 长度 两者取得平衡
惯用手法:截取不同长度测试其区分度;
例如:
mysql> select count(distinct left(coulm, 6)) / count(*) from table;
索引和排序
排序可能发生的2种情况:
对于覆盖索引,直接在索引上查询时,就是有顺序的,using index,在 innodb引擎中,沿着索引字段排序,也有自然排序的,对于myisam引擎,如果按某索引字段排序。
如id,但取出的字段中,有未索引字段,如goods_name,myisam的做法,不是 索引 -> 回行…
而是先取出所有行,再进行排序。先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)
- using where:按照字段索引取出的结果,本身就是有序的
- using filesort:用到文件排序,即取出的结果两次排序
争取目标: 取出来的数据本身就是有序的,利用索引来排序。
重复索引和冗余索引
- 重复索引:是指在同一个列,或者顺序相同的,几个列,建立多个索引,称为重复索引。其没任何帮助,只会增大索引文件,拖慢更新速度,去掉。
- 冗余索引:冗余索引是指2个索引覆盖的列有重叠。比较常见。
索引碎片和维护
在长期的数据更改过程中,索引文件和数据文件,都将产生空洞,形成碎片。我们可以通过一个nop操作(不产生对数据影响的实质操作)来修改表。
比如:表的引擎为innodb,可以 alter table xxx engine innodb
optimize table 表名,也可以修复
注意: 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐,这个过程,如果表的行数比较大,也是非常耗费资源的操作。所以,不能频繁修复。如果表的update操作很频繁,可以按周、月来修复。如果不频繁,可以更长的周期来修复。
explain 列分析
in型子查询引出的陷阱
mysql的查询优化器,针对in做了优化,被改成了exists子查询的执行效果,当表越大,查询越慢。exists子查询和in子查询在mysql底层相互转换。
改进:用连接查询来代替子查询。
from子查询
注意:内层from语句查到的临时表,是没有索引的。所以,from的返回内容要尽量小,需要排序,在内层先排好序。
count优化
- 误区:myisam 的count()非常快!
答:是比较快,但仅限于查询表的“所有行”比较快,因为myisam对行数进行了存储。一旦有条件的查询,速度就不再快了,尤其是where条件的列上没有索引。 - 假如,id<100的商家都是我们内部测试的,我们想查查真实的商家有多少?
小技巧:
- select count(id) from table; 快
- select count(id) from table where id < 100; 快
- select( (select count(id)from table) - (select count(id) from table where id < 100) ); 快
group by
注意:
分组用于统计,而更是用于去重的
不重复的行,分组统计数据,而不要让查询产生N多重复数据。group by的列要有索引,可以防止临时表和文件排序。
order by 的列要和group by的列一致,否则也会引起临时表。以A、B表连接为例,主要查询A表的列
那么group by, order by的列尽量相同,而且列应该显示声明为A的列例:mysql> select A.id, A.cat_id from inner join B group by A.cat_id order by A.cat_id;
union 优化
union总是要产生临时表
注意:
- union的子句条件要尽量具体,即要查询更少的行
- 子句的结果在内存时并成结果集,需要去重,去重就要先排序,而加了all之后,不需要去重,union尽量加all
limit 及翻页优化
limit offset,N 当offset非常大时,效率及低。原因是因为mysql并不是跳过offset行,然后单取N行,而是取offset+N行,放弃前offset行,返回N行。效率及低,当offset越大,效率越低。
优化办法:
- 从业务上解决:不允许翻过100页,以百度为例,一般翻到70页左右。
不用offset,用条件查询
比如: mysql> select id, name from table limit 10000000, 10; 可以这样
mysql> select id, name from table where id > 10000000 limit 10;如果数据要作物理删除,无法用id作为条件查询,不要offset精确查询,还不限制用户分页,可以这样:
分析:优化思路:不查,少查,查索引,少取列,我们现在必须要查,则只查索引,不查数据,得到id,再用尖支查具体条目,这种技巧就是:延迟关联比如:mysql> select id,name from table inner join (select id from table limit 10000000, 10) as tmp on table.id = tmp.id;
- MySQL优化策略小记
- MySQL性能优化小记
- MySql优化小记
- MYSQL优化策略
- MySQL优化策略
- mysql优化策略
- 常用mysql优化策略
- mysql优化策略
- Mysql-索引优化策略
- mysql数据库优化策略
- mysql索引优化策略
- mysql优化策略
- MySQL优化策略
- mysql优化策略
- mysql优化小记(持续更新)
- Django+Mysql性能优化小记
- mysql优化 索引优化策略
- MySql的一些优化策略
- java 环形队列简单实现
- 简单的 六数码问题 终于解决了EOF的问题
- 基于Dragonboard401c的超声波探距
- Unity中模拟鼠标事件
- C++之什么是变量
- MySQL优化策略小记
- 链表的部分反转
- 让不懂编程的人爱上iPhone开发(2017秋iOS11+Swift4+Xcode9版)-第3篇
- /etc/exports参数解释
- javascript通过url向jsp页面传递中文参数乱码解决方法
- 部署 Graylog 日志系统
- fastDFS分布式文件系统与文件上传下载
- 3.1、类和方法
- 在Genymotion中安装apk报错:Failure [INSTALL_FAILED_NO_MATCHING_ABIS]