几个常见的MySQL的可优化点归纳总结_MySQL

几个常见的MySQL的可优化点归纳总结_MySQL,第1张

索引相关

1 查询(或更新,删除,可以转换为查询)没有用到索引

这是最基础的步骤,需要对sql执行explain查看执行计划中是否用到了索引,需要重点关注type=ALL, key=NULL的字段

2 在索引字段上施加函数

to_char(gmt_created, ‘mmdd') = '0101′

正确的写法

gmt_created between to_date(“20090101″, “yyyymmdd”) and to_date(“20090102″, “yyyymmdd”)

3 在索引字段上使用全模糊

member_id like ‘%alibab%'

B树无法解决此类问题,可以考虑搜索引擎。

但是member_id like ‘alibab%'可以用到索引。

其实,对任何一个字段使用 like ‘%xxxx%'都是一种不规范的做法,需要能检查到这种错误用法。

4 多列字段的索引,没有用到前导索引

索引:(memeber_id, group_id)

where group_id=9234,实际上,这个条件是没有办法用到上面的索引的。这是一个很常见的误用。要理解为什么不能用到这个索引,需要理解mysql如何构造多列索引的。

索引是一棵B树,问题是,对于多列索引,mysql将索引字段按照索引建立的顺序进行拼装,组成一个新的字符串,这个字符串被用来做为构建B树的键。所以,在查询条件里,如果没有用到前导列,就没办法访问多列索引的B树。

应该建立索引:(group_id, member_id)

5 访问到了索引之外的字段

索引(member_id, subject)

select subject from offer where member_id=234

在member_id=234记录数很多的情况下,会优于

select subject, gmt_created from offer where member_id=234

原因是第二条sql会根据索引查找到的rowid访问表里的记录。第一条sql使用索引范围扫描就可以得到结果。

如果某个sql执行次数很多,但是读取的字段没有被索引覆盖,那么,可能需要建立覆盖性索引。

6 计数count(id)有时比count()慢

count(id) === count(1) where id is not null

如果没有(id)索引,那么会用全表扫描,而count()会使用最优的索引进行用索引快速全扫描

计数统一使用count()

7 正确使用stop机制

判断member_id在offer表中是否存在记录:

select count() from offer where member_id=234 limit 1

优于

select count() from offer where member_id=234

原因是第一条sql会在得到第一条符合条件的记录后停止。

高效分页

1 高效的分页

使用join技术,利用索引查找到符合条件的id,构造成临时表,用这个小的临时表与原表做join

select

from

(

select t, rownum AS rn

from

(select from blogblog_article

where domain_id=1

and draft=0

order by domain_id, draft, gmt_created desc) t

where rownum >= 2

) a

where arn

应该改写成

select blog_article

from

(

select rid, rownum as rn

from

(

select rowid as id from blogblog_article

where domain_id=1

and draft=0

order by domain_id, draft, gmt_created desc

) t

where rownum >= 2

) a, blog_article

where arn >= 3

and arid = blog_articlerowid

2 order by没有用到索引

有索引(a, b,c )

混合排序规则

ORDER BY a ASC, b DESC, c DESC / mixed sort direction /

缺失了前导列

WHERE g = const ORDER BY b, c / a prefix is missing /

缺失了中间列

WHERE a = const ORDER BY c / b is missing /

使用了不在索引中的列进行排序

WHERE a = const ORDER BY a, d / d is not part of index /

高效地利用primary key

随机查询

一个错误的做法:

select from title where kind_id=1 order by rand() limit 1;

create index k on title(kind_id);

这个sql执行过程中需要全表扫描,并且将数据保存到临时表,这是一个非常耗时的操作。

改进的做法,利用偏移量。

select round(rand() count()) from title where kind_id=1;

select from title where kind_id=1 limit 1 offset $random;

create index k on title(kind_id);

相比上面的做法,这种写法能够利用到kind_id上的索引,减少了需要扫描的数据块。但是,如果offset非常大,那么需要扫描的数据块也非常大,极端情况是扫描索引k的所有数据块。

最优的做法,利用主键进行范围查找

select round(rand() count()) from title where kind_id=1;

select from title where kind_id = and id > $random limit 1;

这个sql利用primary key进行范围查询,完全走索引,并且只读取一条记录,速度非常快。但是,这种用法的限制是primary key必须是int型,并且是连续自增长的。

高效join

1 小表驱动大表进行join

2 避免子查询

子查询是一个影响性能的隐患。应该使用join改写sql。

数据类型

1 避免隐式转换

CREATE TABLE `user` (

`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,

`account` char(11) NOT NULL COMMENT ”,

`email` varchar(128),

PRIMARY KEY (`id`),

UNIQUE KEY `username` (`account`)

) ENGINE=InnoDB CHARSET=utf8;

mysql> explain select from user where account=123 \G

1 row

id: 1

select_type: SIMPLE

table: user

type: ALL

possible_keys: username

key: NULL

key_len: NULL

ref: NULL

rows: 2

Extra: Using where

1 row in set (000 sec)

可以看到,account=123的条件并没有用到唯一索引`username`。mysql的server从storage engine中读取所有的记录,使用to_number()函数,将记录中的account转换成数字,被转换后的数字用来和参数比较。我们的测试表里有2条记录,而执行计划中rows的值也是2,并且type的值为ALL,这也说明索引`username`并没有被用到。

代码如下:

mysql> explain select from user where account='123′ \G

1 row

id: 1

select_type: SIMPLE

table: user

type: const

possible_keys: username

key: username

key_len: 33

ref: const

rows: 1

Extra:

1 row in set (000 sec)

参数为字符串类型,我们可以看到索引`username`,被使用到了。

这是一个经常被误用的做法。

2 主键不是自增列

自增列的主键有多个好处:

插入性能高。

减小page的碎片。

提供二级索引的性能,降低二级索引的空间,因为二级索引存储的是主键的值,并不是page中的行id。

mysql的高并发其实是基于硬件的

这个配置要和服务器的硬件配置和负载来慢慢调

没有统一配置的

简单的说一点 其他的你最好去查手册

然后根据你的业务需要来调整

default-storage-engine=INNODB //事务引擎,如果不用事务支持可以不用,速度稍慢于MYSIM

max_connections=20000 //这个需要看你的硬件是否足够牛

query_cache_size=440M //查询的缓存 如果内存够大可以再大点

table_cache=2028 //表的缓存 表如果很对的话可以大点

tmp_table_size=512M //临时表空间,看你的应用了,是否用了临时表

thread_cache_size=80 //线程缓存 看你的业务是否有很多重复的请求

myisam_max_sort_file_size=100G //排序或索引文件的最大值(看你的表友多少数据和有多少索引)

后面的查手册吧 这东西设置太高太低都不太好 ,从小到大按业务需要慢慢调整吧

在mysql安装目录下,比如:D:ProgramFilesMySQLMySQLServer51

里面有几个配置文件,只要修改名字成为myini即可,比如:

my-hugeini巨型服务器

my-largeini大型

my-mediumini中型

my-smallini小型

备份原来的,并重命名,重新启动即可。其中,[mysqld]这一节是mysql服务器的配置信息。

DABAN RP主题是一个优秀的主题,极致后台体验,无插件,集成会员系统
网站模板库 » 几个常见的MySQL的可优化点归纳总结_MySQL

0条评论

发表评论

提供最优质的资源集合

立即查看 了解详情