Mysql 优化与测试

2018-06-18 00:10:25来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

由于经常被抓取文章内容,在此附上博客文章网址:,偶尔会更新某些出错的数据或文字,建议到我博客地址 :  --> 点击这里

 

以下的测试数据根据环境的不同所耗费的时间有所不同,例如我在腾讯云上的测试,以及在本机的测试速度相差三倍。但当环境因素一样的时候,优化sql能够带来显著的提升。

测试表数据:3百万条数据,12个字段,存储引擎:myisam

1 确定搜索一条记录的时候,加上limit 1 ,以此让游标查到第一条结果时停止,不需要遍历下面的结果

image

使用这样子数据递增的varchar字段,在最优情况下,结果在第一条:

SQL : select * from tbl_game_user_test where channel= 'jieduan1';

TIME : 1.292 s

而当我们加上了limit 1 :

SQL : select * from tbl_game_user_test where channel= 'jieduan1' limit 1;

TIME : 0.0001 s 

对比上面,提高了很多。而在最坏情况下,结果在最后一条,那么两者使用时间是一致的。

而当我们explain的时候,两者是一致的:

+----+-------------+--------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table              | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | tbl_game_user_test | ALL  | NULL          | NULL | NULL    | NULL | 3006343 | Using where |
+----+-------------+--------------------+------+---------------+------+---------+------+---------+-------------+

2 like查询以%开头索引会失效,没有命中索引,查询效率差别是很大的。而%在结尾,可以命中索引

未优化的sql:

SQL:select * from tbl_game_user_test where deviceID LIKE '%deviceID1111111'

TIME : 2.342 s

而当我们将%放到末尾:

SQL : select * from tbl_game_user_test where deviceID LIKE 'deviceID1111111%';

TIME : 0.001 s

花费的时间相差挺大

3 对于分页的优化,可以采用覆盖索引的原理来优化。

在数据量不大的时候,limit 分页效率差别不大,当数据量大的时候,数据越大,效率差距越大,因此我们可以采用覆盖索引的原理,来优化sql。

未优化前SQL:

SELECT * FROM tbl_game_user_test ORDER BY id LIMIT 1000000,10

Times : 4.55 s

而优化的SQL:

select * from tbl_game_user_test JOIN (SELECT id FROM tbl_game_user_test ORDER BY id LIMIT 100000,10)a USING(id)

Times : 0.014s

我们把第二种检索方法叫做延迟关联,因为延迟了对列的访问。在查询的第一阶段MySQL可以使用覆盖索引,在FROM字句的查询中找到匹配的id,然后根据这些id值在外层查询匹配获取需要的列值

上述的语句,我们也可以这么写:

select * from tbl_game_user_test where id >=(SELECT id FROM tbl_game_user_test ORDER BY id LIMIT 100000,1) ORDER BY id limit 10

4 永远为每张表设置一个ID

  我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的 AUTO_INCREMENT标志。
就算是你 users 表有一个主键叫 “email”的字段,你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外,在你的程序中,你应该使用表的ID来构造你的数据结构。
而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区……

5 合适的存储引擎

  InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能。

同样的情况下,执行相同的查询,myisam 比innodb 快接近4倍;

存储引擎 : myisam

SQL :select * from tbl_game_user_test where channel = 'jieduan65';

time : 1.2 s

而 : innodb 是 4.6s  【平均】

6 Mysql SQL Mode 可以禁止截断保存操作

当我们对一个varchar(3)的字段进行更新操作:

update tbl_game_user_test set platform  = 'jieduanTest' where id = 1;

我们发现插入的字符被自动截断了,

存入的是:

jie

出现这种情况往往会发生一些意料之外的事情,等到发生了,再来找问题,再扩大字段长度就比较晚。我们可以在长度超过限制就报错,通过报错来提前报警。

可以通过:

set session sql_mode='STRICT_TRANS_TABLES'

就会提示:

[Err] 1406 - Data too long for column 'platform' at row 1

7 负向条件查询不能使用索引

select * from order where status!=0 and stauts!=1

not in/not exists都不是好习惯

可以优化为in查询:

select * from order where status in(2,3)

8 优化order by 语句

在某些情况中,MySQL 可以使用一个索引来满足ORDER BY 子句,而不需要额外的排序。where 条件和order by 使用相同的索引,并且order by 的顺序和索引顺序相同,并且order by 的字段都是升序或者都是降序。
例如:下列sql 可以使用索引。

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT
* FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT
* FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

但是以下情况不使用索引:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
--order by 的字段混合ASC 和DESC
SELECT
* FROM t1 WHERE key2=constant ORDER BY key1; --用于查询行的关键字与ORDER BY 中所使用的不相同
SELECT
* FROM t1 ORDER BY key1, key2; --对不同的关键字使用ORDER BY:

 其他优化小技巧:

1 字符字段只建前缀索引

2 导入大数据量时,先删除索引,导入数据,再创建索引

3 在设计表的时候,可以按照定长字段与变长字段相分离,分开两个表存放。例如论坛标题是我们经常获取的,而论坛个人创建时间等,几乎是不会去看,这种我们可以放于几乎不查的表中。同时,我们也可以把较长内容的字段独立存放于一个表中。

4 冗余字段。很多时候我们设计表都要遵守第一,二,三范式,但有时我们为了加快查询,而应该添加冗余字段。例如论坛栏目表中,一般而言,我们会有个栏目表,有文章表,而当我们获取该栏目有多少篇文章的时候,联表查询显得稍费时间,而我们可以在栏目表添加一个文章数量字段,每次添加或者删除都更新一下该字段。

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MySQL 8.0.2复制新特性(翻译)

下一篇:MySQL的约束、多表查询、子查询