[转] MySQL索引原理及慢查询优化

2015-05-20 08:50:37 查看评论 778 人阅读    

MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色,但所谓“好马配好鞍”,如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重。
本人从13年7月份起,一直在美团核心业务系统部做慢查询的优化工作,共计十余个系统,累计解决和积累了上百个慢查询案例。随着业务的复杂性提升,遇到的问题千奇百怪,五花八门,匪夷所思。本文旨在以开发工程师的角度来解释数据库索引的原理和如何优化慢查询。

一个慢查询引发的思考

select
   count(*) 
from
   task 
where
   status=2 
   and operator_id=20839 
   and operate_time>1371169729 
   and operate_time<1371174603 
   and type=2;

系统使用者反应有一个功能越来越慢,于是工程师找到了上面的SQL。
并且兴致冲冲的找到了我,“这个SQL需要优化,给我把每个字段都加上索引”
我很惊讶,问道“为什么需要每个字段都加上索引?”
“把查询的字段都加上索引会更快”工程师信心满满
“这种情况完全可以建一个联合索引,因为是最左前缀匹配,所以operate_time需要放到最后,而且还需要把其他相关的查询都拿来,需要做一个综合评估。”
“联合索引?最左前缀匹配?综合评估?”工程师不禁陷入了沉思。
多数情况下,我们知道索引能够提高查询效率,但应该如何建立索引?索引的顺序如何?许多人却只知道大概。其实理解这些概念并不难,而且索引的原理远没有想象的那么复杂。

分类: MySQL 标签: mysql 索引 优化 慢查询

[转]DBA专家门诊一期:索引与sql优化问题汇总

2015-01-06 15:52:00 查看评论 1117 人阅读    

              这是从阿里云论坛上转载的一篇文章,是阿里大牛在论坛上解答问题的帖子汇总。


              非常感谢大家踊跃参加DBA专家门诊一期:索引与sql优化,很多云友都提出了己的问题,门诊主任医师玄惭对大家提的问题一一作了解答。现已整理好这些问题,分享在此,欢迎来拿,绝对干货! 篇幅较长,耐心细看! 
我们将赠送每位提问者每人一本凌云杂志第四期,请各位以论坛短消息形式将姓名、电话、地址发送给管理员xiaofanqie。
 
  
啊里新人(Q1):索引我一般都是只有主键,这玩意儿,是不是越少越好? 
玄惭(A1):在日常的业务开发中,常见使用到索引的地方大概有两类:  
第一类.做业务约束需求,比如需要保证表中每行的单个字段或者某几个组合字段是唯一的,则可以在表中创建唯一索引;  
比如:需要保证test表中插入user_id字段的值不能出现重复,则在设计表的时候,就可以在表中user_id字段上创建一个唯一索引:  
CREATE TABLE `test` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `user_id` int(11) NOT NULL,  
  `gmt_create` datetime DEFAULT NULL,  
  PRIMARY KEY (`id`),  
  UNIQUE KEY `uk_userid` (`user_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;  
 

分类: MySQL 标签: mysql 优化 索引

MySQL启动没有加载Innodb

2014-03-04 11:19:22 查看评论 772 人阅读    

下午的时候把数据库传到测试服务器的MySQL中,因为是订单表,所以使用的是Innodb,但是创建表的时候自动把我的Innodb转换为myisam,提示Unknown strorage engine 'innodb'.

开始以为是my.cnf设置了skip-innodb,但是打开my.cnf并没有发现,default-storage-engine 的配置也是 INNODB。重启了mysql并没有发现错误信息。

于是进入mysql执行查询语句:SHOW ENGINES

并没有发现有Innodb,但是查看编译参数的时候发现Innodb是对的

分类: MySQL 标签:

[转] MySQL的InnoDB配置及优化

2014-03-04 10:24:59 查看评论 658 人阅读    
MyISAM和InnoDB是在使用MySQL最常用的两个表类型,各有优缺点,视具体应用而定。基本的差别为:
MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。
MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持已经外部键等高级数据库功能。
  MyIASM是IASM表的新版本,有如下扩展:
  1、二进制层次的可移植性。
  2、NULL列索引。
  3、对变长行比ISAM表有更少的碎片。
  4、支持大文件。
  5、更好的索引压缩。
  6、更好的键码统计分布。
  7、更好和更快的auto_increment处理。
 
  InnoDB 是 MySQL 第一个提供外键约束的引擎,除了提供事务处理外,InnoDB 还支持行锁,提供和 Oracle 一样的一致性的不加锁读取,能增加并发读的用户数量并提高性能,不会增加锁的数量。
  InnoDB 的设计目标是处理大容量数据时最大化性能,它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。
  InnoDB 是一套放在 MySQL 后台的完整数据库系统,InnoDB 有它自己的缓冲池,能缓冲数据和索引,InnoDB 还把数据和索引存放在表空间里面,可能包含好几个文件,这和 MyISAM 表完全不同,在 MyISAM 中,表被存放在单独的文件中,InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB。
 
  以下是一些细节和具体实现的差别:
  1、InnoDB不支持FULLTEXT类型的索引。
  2、InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count(*)语句包含 where条件时,两种表的操作是一样的。
  3、对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中,可以和其他字段一起建立联合索引。
  4、DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的删除。
  5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
  6、InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”
分类: MySQL 标签: