mysql 优化

最新推荐文章于 2024-10-05 14:16:38 发布

chenshi1874

最新推荐文章于 2024-10-05 14:16:38 发布

阅读量106

点赞数

文章标签：数据库前端 php ViewUI

原文链接：https://my.oschina.net/u/3255899/blog/1483716

版权

表的优化：

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(

id int unsigned NOT NULL auto_increment,
url varchar(255) NOT NULL,
url_src int unsigned NOIT NULL DEFAULT 0,
PRIMARY KEY(id)
);

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

DELIMITER |

CREATE TRIGGER pseudohash_src_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN SET NEW.url_src = crc32(NEW.url);
END;
|
CREATE TRIGGER pseudohash_src_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN SET NEW.url_src = crc32(NEW.url);
END;
|
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 如下：

优化前

复制代码代码如下:

其执行时间：

优化后：

复制代码代码如下:

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

复制代码代码如下:

执行时间：