mysql 优化
博客专区 > tystys 的博客 > 博客详情
mysql 优化
tystys 发表于5个月前
mysql 优化
  • 发表于 5个月前
  • 阅读 1
  • 收藏 1
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

表的优化:

    1:定长与变长分离:如 id int 占 4 个字节,char(4)占 4 个字符长度,也是定长==》即每一 单元值占的字节是固定的,查询非常快,这个放在一张表里 而 varchar,textblob 这样的变长字段,适合单放一张表,也就是放在附属表里,用主键和核 心表关联起来

2:常用字段和不常用字段要分离 例如:个人中心里的简介什么的不常用,就单独放个表,而每天都要查询到的数据放到 一张表

3:在 1 对多,需要关联同级的字段上,添加冗余字段

列选择原则: 1:字段类型优先级:整型>date,time>enum,char>varchar>blob,text

列的特点分析: 整型:定长,没有国家、地区之分,没有字符集的差异 比如:timyint 1,2,3,4,5 《==》 char(1) a,b,c,d 他们两个比:重空间上看,都占 1 个字节, 但是 order by 排序的话,前者快, 原因:后者需要考虑字符集与校对集(就 是排序规则)

 

Time 定长,运算快,节省空间,优先用时间戳来存整型,但是如果考虑时区的话写 sql 时不方便 例如: where>”2015-10-12”

Enum:定长,能起到约束值的目的。

Char 定长 但是 需要考虑字符集和(排序)校对集 Varchar 不定长 要考虑字符集的转换与排序时的校对集,速度慢 Text blob 无法使用内存临时表(如果要排序的话,只能拿到磁盘上进行操作,会更慢)

2:够用就行,不要慷慨:原因==》大的字段浪费内存,影响速度 以年龄威力 timyint unsigned not null ,可以存储 255 岁,足够了,但是用 int 浪费 3 个字节 以 varchar(10),varchar(300)存储的内容相同,但在表联查时,varchar(300) 要花费更多的内存

3:尽量避免用 NULL() 原因:NULL 不利于索引,要用特殊的字节来标注 在磁盘上占据的空间其实更大,虽然 mysql5.5 已经对 null 做了优化,但效果不明 显

索引优化策略 1:索引类型==》btree 索引、hash 索引

1.1 B-tree 索引

注:名叫 btree 索引,大的方面看, 都用的平衡树,但具体的实现上,各引擎稍 有不同。Btree 的内容可以理解为: 例如查询我要查询 5,则先查询到 4,然后大于 4 的在右边,然后在查询到 6,5 又 比 6 小,则在左边,这样查询 3 次就可以查新到,大大缩短了查询速度,查询到所在位 置后,再查询里面的内容就快的多了

 

1.2Hash 索引: 只能在 memory 表(内存引擎)用,因为默认的是 hash 索引 Hash 存储的原理: 存储的时候,先把 ID 给 hash,然后 hash 返回一个 address,放在固定的位置, 等下次要找的时候,在通过 hash 得到位置,直接就找到了 疑问:既然 hash 的查找如此搞笑,为什么不用 hash 索引? 答: 1、哈哈函数计算后的结果,是随机的,如果是在磁盘上放置数据,以主 键为 ID 为例,随着 ID 的增长,ID 对应的行在磁盘上就是随机放置,会造成 资源由 www.eimhe.com 美河学习在线收集分享 磁盘上没利用的“空洞” 2、无法对范围查询进行优化,例如 where id>4 无法确定,因为他们放置 的位置是随机的。 3、无法利用前缀索引,比如在 btree 中,field 列的值“helloworld”,并 加索引查询是可以查询到的,但是 hash 中(hello)和(helloworld)地址内容 不一样,所以无法查询。 4、排序无法优化 5、必须回行,也就是说通过索引拿到的数据位置,必须回到表中取数据 2:btree 索引的常见误区 2.1 在 where 条件常用的列上都加上索引 2.2 在多列上简历索引后,查询哪个列,索引都将发挥作用 索引的作用:提高查询速度,提高 排序速度,提高 分组统计速度 查询的话 前面加上 explain(解释)+查询语句,就可以,例如 possible_keys 是指可能会用到的索引,key 是当前 sql 使用到的索引,key_len 是 索引的使用字节数

 

聚簇索引和非聚簇索引

Myisam 引擎(非聚簇索引)innodb(聚簇索引)

Innodb 索引与数据的关系

Innodb 的主索引文件上,直接存放该行数据,称为聚簇索引,

次索引指向对主键的引 用 Myium 中,主索引和次索引,都指向物理行(磁盘位置) 资源由 www.eimhe.com 美河学习在线收集分享

注意:innodb 来说 1、主键索引 即存储索引值,又在叶子中存储行的数据

2、如果没有主键, 则会 Unique key 做主键

3、如果没有 unique,则系统生成一个内部的

 

4、像 innodb 中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结 构称为“聚簇索引”

聚簇索引:优势==》根据主键查询条目比较少时,不用回行(数据就在主键节点下)

劣势==》如果碰到不规则数据插入时,造成频繁的页分裂,影响速度

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

“set profiling=1”==>性能分析工具 show profiles;显示性能

理想的索引:

2:对于左前缀不易区分的列,建立索引的技巧

     2.1 : 倒 序 : 例 如 == 》 url 前 面 基 本 相 同 的 --http://www.baidu.com,http://www.sina.com,这样查询的话,添加 的索引字段太 多,这个时候我们可以倒序查询--》moc.udiab.www//:ptth,这样重复率就很低了

[

使用倒序索引(INDEX DESC),可以大幅提升带有order by desc子句的SQL语句性能。 

一、场景

1、表名:test_t,有一字段名为object_id

2、总数据量:580000行,segment_size:72MB

3、Where条件(Owner=’SYS’ and Object_id>50000)的行数:32472行

4、SQL语句:select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc

5、希望借助倒序索引,提升order by object_id desc的性能

二、B*树倒序索引(DESC)比 B*树索引(默认为ASC升序)带来的性能提升对比

1、采用B*树倒序索引(DESC),COST:4,执行计划走的INDEX RANGE SCAN

SQL语句:select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc

2、采用B*树索引(默认为ASC),COST:94103,执行计划走的INDEX RANGE SCAN DESENDING

三、实际测试过程

SQL语句:select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc

1、采用B*树倒序索引(DESC)测试过程与结果

(1)test_t表上创建(OWNER+OBJECT_ID DESC)的倒序索引

   SQL>create index idx_test_t_id_DESC on test_t(owner,object_id desc)

(2)重新收集统计信息

  SQL> execdbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'TEST_T',estimate_percent=> 20 , cascade=> TRUE );

(3)SQL执行计划与COST 

 Description

Object owner

Object name

Cost

Cardinality

Bytes

SELECT STATEMENT, GOAL = FIRST_ROWS

 

 

4

94298

9806992

 TABLE ACCESS BY INDEX ROWID

SYS

TEST_T

4

94298

9806992

  INDEX RANGE SCAN

SYS

IDX_TEST_T_ID_DESC

3

1

 

2、B*树索引(默认为ASC升序)测试过程与结果

(1)test_t表上创建(OWNER+OBJECT_ID)的普通默认ASC索引

  SQL>create index idx_test_t_id_ASC on test_t(owner,object_id)

(2)重新收集统计信息

  SQL> execdbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'TEST_T',estimate_percent=> 20 , cascade=> TRUE );

(3)SQL执行计划与COST

 Description

Object owner

Object name

Cost

Cardinality

Bytes

SELECT STATEMENT, GOAL = FIRST_ROWS

 

 

94103

93792

9097824

 TABLE ACCESS BY INDEX ROWID

SYS

TEST_T

94103

93792

9097824

  INDEX RANGE SCAN DESCENDING

SYS

IDX_TEST_T_ID_ASC

287

93792

 

]

 

2.2:伪 hash 技巧:

mysql优化之伪哈希索引

想法非常简单,在标准的B-Tree索引上创建一个伪哈希索引。它和真正的哈希索引不是一回事,因为它还是使用B-Tree索引进行查找。然而,它将会使用键的哈希值进行查找,而不是键自身。你所要做的事情就是在where子句中手动地定义哈希函数。

例子:URL查找。

URL通常会导致B-Tree索引变大,因为它们非常长。通常会按照下面的方式来查找URL表。

mysql>select id from url where url='http://www.mysql.com';

但是,如果移除掉url列上的索引并且给表添加一个被索引的url_src列,就可以按照下面的方式进行查询:

mysql>select id from url where url='http://www.mysql.com' and url_src=CRC32('http://www.mysql.com');

 

mysql查询优化器注意到url_src列上有很小的,选择性很高的索引,并且它会使用里面的值进行索引查找。即使有几列相同的url_src值,也很容易进行精确的对比来确定需要的行。替代方案是把完整的URL索引为字符串,它要慢很多。

 

这个办法的一个缺点就是要维护哈希值。你可以手工进行维护,在mysql5.0 以上版本中,可以使用触发器来进行维护。

1.创建一个表:

create table pseudohash(

  1.     id int unsigned NOT NULL auto_increment,  
  2.     url varchar(255) NOT NULL,  
  3.     url_src int unsigned NOIT NULL DEFAULT 0,  
  4.     PRIMARY KEY(id)  
  5. );  

 

接下来创建触发器。我们先暂时更新一下命令分隔符,这样就可以在触发器中使用分号:

DELIMITER |

  1. CREATE TRIGGER pseudohash_src_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN SET NEW.url_src = crc32(NEW.url);  
  2. END;  
  3. |  
  4. CREATE TRIGGER pseudohash_src_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN SET NEW.url_src = crc32(NEW.url);  
  5. END;  
  6. |  
  7. DELIMITER;  

 

剩下的工作就是验证触发器自动维护了哈希值。

如果使用这种方式,就不应该使用SHA1()和MD5()这此哈希函数。它们返回很长的字符串,会浪费大量的存储空间并且减慢比较速度。它们是强加密函数,被设计为不产生任务冲突。这并不是我们的目标。简单的哈希函数能在有较好性能的同时保证可接受的冲突率。当然,如果表有很多行并且CRC32()产生了很多冲突,就要实现自己的64位哈希函数,要确保自己的函数返回整数,而不是字符串。

mysql>select conv(right(md5('http://www.mysql.com/'),16),16,10) as hash64;

3:多列索引 3.1 多列索引的考虑因素==》列的查询频率,列的区分度,列的查询顺序 (注意一定要结合实际业务场景)

 

索引与排序 排序可能发生 2 种情况: 1==》对于覆盖索引,直接在索引上查询时,就是有顺序的 在 innodb 引擎中,沿着索引字段排序,也是自然有序的,对于 myium 引 擎,如果按某索引字段排序,如 ID,但取出的字段中,有未索引字段,如 goods_name,myium 的做法不是 索引--》回行、索引==》回行,而是先取 出所有行,再进行排序 2==》先取出数据,形成临时表,做 filesort(文件排序,但文件可能在磁盘上, 也可能在内存中) 对于排序,我们争取的目标是查询出来就是有序的。

 

重复索引与冗余索引 重复索引:是指在同 1 个列或者顺序相同的的几个列,建立了多个索引。称为重 复索引。重复索引没有任何帮助,只会增大索引文件,拖慢更新速度,去掉 冗余索引:指 2 个索引所覆盖的列有重叠,称为冗余索引

Artid-->查询文章的---tag Tag-->查询文章的 artid 例: Select tag from t1 where artid=2 Select artid from t1 where tag=”PHP” 思路: Artid --查--tag ==>index artid(artid) Tag --查 artid ==》index tag(tag)

继续优化索引,建立如下 2 个联合索引 Index arttag(artid,tag) index tagart(tag,artid) 再做测试,如下,使用了 using index 即索引覆盖

这两个索引,列有重叠,但顺序不一样,称为“冗余索引”,冗余索引常见

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

Sql 语句优化: 1:sql 语句的时间花在那里? 答:等待时间,执行时间 这两个时间并非鼓励的,如果单条语句执行的快了,对其他语句的锁定 的也就少了,所以,我们来分析如何降低执行时间 2:sql 语句的执行时间,又花在那里了? 答: A:查找---》沿着索引查找,或者可能全表扫描 B:取出----》查到行后,把数据取出来 如何查询快? A:查询快-----》联合索引的顺序,区分度,长度 B:取得快,索引覆盖 C:传输的少,更少的行和列 切分查询:按数据拆成多次 例:插入 10000 行数据,每 1000 条为单位,插入 分解查询:按逻辑把夺标链接查询分成多个简单的 sql

 

3:sql 语句的优化思路 答:不查---》少查---》高效的差

不查,通过业务逻辑来计算:比如论坛的注册会员数,我们可以根据 前三个月统计的每天注册数,用程序来估算

少查:尽量精准数据,少取行,我们观察新闻网站,评论内容等。

一 般一次性取列表 10-30 条左右

必须要查:尽量走在索引上查询行

取时,取尽量少的列:

比如 select * from table A, 就取出所有列,不建议

比如 select * from table A,table B 取出 A,B 表所有列

 

4:如果定量分析查的多少行,和是否沿着索引查? 答:用 explain 来分析

Type 列:是指查询的方式,非常重要,是分析“查数据过程”的重要依据 可能的值如下: All==》意味着从表的第一行,往后逐行做全表扫描,运气不好扫描到最后一行。 Inde==>比 all 性能稍好点 通俗的说:all 扫描所有的数据行,相当于 data_all(为了便于理解,造的 词) index 扫描所有的索引节点,相当于 index_all(为了便于理解,造 的词) 2 种情况可能出现: 2.1 索引覆盖的查询情况下,能利用上索引数据,但利用不上索引查找必须全 索引扫描 2.2 利用索引进行排序,但取出所有的节点 Select * from goods order by goos_id desc; 分析:没有加 where 条件,就得取所有索引节点,同时又没有回行,只 取索引节点,再拍序,经过所有索引节点 Range:意思是查询时,能根据索引做范围的扫描 Ref:通过索引,可以直接引用到某些数据行 Const,system,null 这三个分别指查询优化到常量级别,甚至不需要查找时间 从 all===>null 查询速度是越来越快 Ref 列:(指链接查询时,表之间的字段引用关系) Rows 列:是指估计要扫描多少行

Extra: Index:是指用到了索引覆盖。效率非常高 Using where:是指广靠索引定位不了,还得 where 判断一下 Using tempory 是指用上了临时表,group by 与 order by 不同列时,或者 group by ,order by 别的表的列(用上的话,就不太妙了) Using filesort:文件排序(文件可能在磁盘,也可能在内存)例如:select sum(shop_price) from goods group by cat_id(这句话用到了临时表和文件排序) In 型子查询 引出的陷阱 Mysql 会进行优化,针对 In 型做优化,被改成了 exists 子查询的执行效果,但是当 表越来越大时,查询速度就会越来越慢 改进:用链接查询来代替子查询

 

 

Count()优化 误区: 1:myisam 的 count()非常快 资源由 www.eimhe.com 美河学习在线收集分享 答:是比较快,但仅限于查询表的“所有行”比较快, 因为 myisam 对 行数进行了存储。一但有条件的查询,速度就不再快了,尤其是 where 条件 的列上没有索引。3:group by 注意: 1.分组用于统计,而不用于筛选重复数据 a) 不重复的行、分组统计数据用,而不要让查询产生 N 多重 复数据,比如 1-->N 连接时, 栏目 -- 左连接 -->商品表,将会产 生重复行,用 group by 去重 效率很低 比如:统计平均分,最高分,适合,但用于筛选重复数据,则不 适合,以及用索引来避免临时表和文件排序 Group by 的列要有索引,可以避免临时表及文件排序 Order by 的列 要和 group by 的一致,否则也会引起临时表 (原因是因为 group by 和 order by 都需要排序,所以如果两 者的列不一致, 那必须经过至少一次排序) 2:以 AB 表连接为例,主要查询 A 表的列 那么 group by ,order by 的列尽量相同,而且列应该显示声 明为 A 的列: Select A.id ,A.cat_id from A inner join B group by A.cat_id order by A.cat_id 4:union 优化 Union 总是要产生临时表 1:注意 unicon 的子句条件要尽量具体,即==》查询更少的行 2:子句的结果在内存里并成结果集,需要去重复,去重复就得先排 序而加上 all 之后,不需要去重,unicon 尽量加 all,可以在代码中去重复

 

Limit 及翻页优化 Limit offset N,随着 offset 的增大,查询翻页速度越慢 优化方法: 1:从业务上去解决 办法:不允许翻过 100 页,以百度为例,一般能翻页到 70 页左右 2:不用 offset,用条件查询

例:select id,name from its_com limit 5000000,10; 上面的查询用时要 5 秒多,但是改成下面的查询后: Select id ,name from its_com where id >500000 limit 10;这样的查询速 度基本是立即查到的。 问题:2 次的结果不一致 原因:数据被物理删除过,有空洞 解决:数据不进行物理删除(可以进行逻辑删除)

 

最终在页面上显示数据时,逻辑删除的条目不显示既可(一般来说, 大网站的数据都不是物理删除的,只做逻辑删除)

 

3:非要物理删除,还要用 offset 精确查询,还不限制用户分页,怎么办? 分析:优化思路==》不查,少查,查索引,少取列 我们现在必须要查,则值查索引,不查数据,得到 ID,在用 ID 去查具体条目, 这种技巧就是延迟关联 例如:select * from lx_com Inner join (select id from lx_com limit 1200000,3) as tmp On lx_com.id =tmp.id

 

 

【背景】

  某业务数据库load 报警异常,cpu usr 达到30-40 ,居高不下。使用工具查看数据库正在执行的sql ,排在前面的大部分是:

复制代码代码如下:


SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20;


表的数据量大致有36w左右,该sql是一个非常典型的排序+分页查询:order by col limit N,OFFSET M , MySQL 执行此类sql时需要先扫描到N行,然后再去取 M行。对于此类大数据量的排序操作,取前面少数几行数据会很快,但是越靠后,sql的性能就会越差,因为N越大,MySQL 需要扫描不需要的数据然后在丢掉,这样耗费大量的时间。

 

【分析】

针对limit 优化有很多种方式,
1 前端加缓存,减少落到库的查询操作
2 优化SQL
3 使用书签方式 ,记录上次查询最新/大的id值,向后追溯 M行记录。
4 使用Sphinx 搜索优化。
对于第二种方式 我们推荐使用"延迟关联"的方法来优化排序操作,何谓"延迟关联" :通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

【解决】

根据延迟关联的思路,修改SQL 如下:

优化前

复制代码代码如下:


root@xxx 12:33:48>explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =\'0\' AND end_time >=\'2014-05-29\' ORDER BY id asc LIMIT 149420 ,20;
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----------------------------+
| id | select_type | table       | type  | possible_keys | key         | key_len | ref  | rows   | Extra                       |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----------------------------+
| 1  | SIMPLE      | relation    | range | ind_endtime   | ind_endtime | 9       | NULL | 349622 | Using where; Using filesort |
+----+-------------+-------------+-------+---------------+-------------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

 

其执行时间:

优化后:

复制代码代码如下:


SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id

 

 

复制代码代码如下:


root@xxx 12:33:43>explain SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows   | Extra |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
| 1  | PRIMARY     | <derived2>  | ALL    | NULL          | NULL    | NULL    | NULL | 20     |       |
| 1  | PRIMARY     | a           | eq_ref | PRIMARY       | PRIMARY | 8       | b.id | 1      |       |
| 2  | DERIVED     | relation    | index  | ind_endtime   | PRIMARY | 8       | NULL | 733552 |       |
+----+-------------+-------------+--------+---------------+---------+---------+------+--------+-------+
3 rows in set (0.36 sec)

 

执行时间:

优化后 执行时间 为原来的1/3 。

 

 

共有 人打赏支持
粉丝 3
博文 72
码字总数 82272
×
tystys
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: