Mysql实例几个常见的MySQL的可优化点归纳总结

《Mysql实例几个常见的MySQL的可优化点归纳总结》要点：
本文介绍了Mysql实例几个常见的MySQL的可优化点归纳总结，希望对您有用。如果有疑问，可以联系我们。

MYSQL应用索引相关

MYSQL应用1. 查询(或更新,删除,可以转换为查询)没有用到索引
这是最基础的步骤,需要对sql执行explain查看执行计划中是否用到了索引,需要重点关注type=ALL,key=NULL的字段.

MYSQL应用2.? 在索引字段上施加函数

MYSQL应用
to_char(gmt_created,‘mmdd') = '0101′

MYSQL应用正确的写法

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

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

MYSQL应用
member_id like ‘%alibab%'

MYSQL应用B树无法解决此类问题,可以考虑搜索引擎.
但是member_id like ‘alibab%'可以用到索引.
其实,对任何一个字段使用 like ‘%xxxx%'都是一种不规范的做法,需要能检查到这种错误用法.

MYSQL应用4.? 多列字段的索引,没有用到前导索引
索引：(memeber_id,group_id)
where group_id=9234,实际上,这个条件是没有办法用到上面的索引的.这是一个很常见的误用.要理解为什么不能用到这个索引,需要理解mysql如何构造多列索引的.
索引是一棵B树,问题是,对于多列索引,mysql将索引字段按照索引建立的顺序进行拼装,组成一个新的字符串,这个字符串被用来做为构建B树的键.所以,在查询条件里,如果没有用到前导列,就没办法访问多列索引的B树.
应该建立索引：(group_id,member_id)

MYSQL应用5. 访问到了索引之外的字段
索引(member_id,subject)

MYSQL应用
select subject from offer where member_id=234

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

MYSQL应用
select subject,gmt_created from offer where member_id=234

MYSQL应用原因是第二条sql会根据索引查找到的rowid访问表里的记录.第一条sql使用索引范围扫描就可以得到结果.
如果某个sql执行次数很多,但是读取的字段没有被索引覆盖,那么,可能需要建立覆盖性索引.

MYSQL应用6.? 计数count(id)有时比count(*)慢

MYSQL应用
count(id) === count(1) where id is not null

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

MYSQL应用7.? 正确使用stop机制
判断member_id在offer表中是否存在记录：

MYSQL应用
select count(*) from offer where member_id=234 limit 1

MYSQL应用优于

MYSQL应用
select count(*) from offer where member_id=234

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

MYSQL应用
高效分页
1.? 高效的分页
使用join技术,利用索引查找到符合条件的id,构造成临时表,用这个小的临时表与原表做join

MYSQL应用
select *
from
(
select t.*,rownum AS rn
from
(select * from blog.blog_article
where domain_id=1
and draft=0
order by domain_id,draft,gmt_created desc) t
where rownum >= 2
) a
where a.rn <= 3

MYSQL应用应该改写成

MYSQL应用
select blog_article.*
from
(
select rid,rownum as rn
from
(
select rowid as id from blog.blog_article
where domain_id=1
and draft=0
order by domain_id,gmt_created desc
) t
where rownum >= 2
) a,blog_article
where a.rn >= 3
and a.rid = blog_article.rowid

MYSQL应用2. order by没有用到索引
有索引(a,b,c )
混合排序规则

MYSQL应用
ORDER BY a ASC,b DESC,c DESC /* mixed sort direction */

MYSQL应用缺失了前导列

MYSQL应用
WHERE g = const ORDER BY b,c /* a prefix is missing */

MYSQL应用缺失了中间列

MYSQL应用
WHERE a = const ORDER BY c /* b is missing */

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

MYSQL应用
WHERE a = const ORDER BY a,d /* d is not part of index */

MYSQL应用高效地利用primary key
随机查询
一个错误的做法：

MYSQL应用
select * from title where kind_id=1 order by rand() limit 1;
create index k on title(kind_id);

MYSQL应用这个sql执行过程中需要全表扫描,并且将数据保存到临时表,这是一个非常耗时的操作.
改进的做法,利用偏移量.

MYSQL应用
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);

MYSQL应用相比上面的做法,这种写法能够利用到kind_id上的索引,减少了需要扫描的数据块.但是,如果offset非常大,那么需要扫描的数据块也非常大,极端情况是扫描索引k的所有数据块.
最优的做法,利用主键进行范围查找

MYSQL应用
select round(rand() * count(*)) from title where kind_id=1;
select * from title where kind_id = and id > $random limit 1;

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

MYSQL应用
高效join
1. 小表驱动大表进行join
2. 避免子查询

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

MYSQL应用
数据类型
1.? 避免隐式转换

MYSQL应用
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 (0.00 sec)

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

MYSQL应用2. 主键不是自增列
自增列的主键有多个好处：

• 插入性能高.
• 减小page的碎片.
• 提供二级索引的性能,降低二级索引的空间,因为二级索引存储的是主键的值,并不是page中的行id.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!