DBA专家门诊一期：索引与sql优化问题汇总

啊里新人（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 ; 第二类.提高SQL语句执行速度，可以根据SQL语句的查询条件在表中创建合适的索引，以此来提升SQL语句的执行速度；此过程好比是去图书找一本书，最慢的方法就是从图书馆的每一层楼每一个书架一本本的找过去；快捷一点的方法就是先通过图书检索来确认这一本书在几楼那个书架上，然后直接去找就可以了；当然创建这个索引也需要有一定的代价，需要存储空间来存放，需要在数据行插入，更新，删除的时候维护索引：例如： CREATE TABLE `test_record` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `gmt_create` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5635996 DEFAULT CHARSET=utf8 该表有500w的记录，我需要查询20:00后插入的记录有多少条记录： mysql> select count(*) from test_record where gmt_create>'2014-12-17 20:00:00'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (1.31 sec) 可以看到查询耗费了1.31秒返回了1行记录，如果我们在gmt_create字段上添加索引： mysql> alter table test_record add index ind_gmt_create(gmt_create); Query OK, 0 rows affected (21.87 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from test_record where gmt_create>'2014-12-17 20:00:00'; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.01 sec) 查询只消耗了0.01秒中就返回了记录. 总的来说，为SQL语句（select，update，delete）创建必要的索引是必须的，这样虽然有一定的性能和空间消耗，但是是值得，尤其是在大并发的请求下，大量的数据被扫描造成系统IO和CPU资源消耗完，进而导致整个数据库不可服务。 蓝雨麦浪（Q2）：我想问个问题，怎么学好数据库，如果简单的sql，我会写。但是复杂了，就不知道怎么写了。其实也算是知道怎么写，但是就是理不清楚。不知道怎么优化。写出来也不知道是不是对的，对性能有没有什么影响之内的。还有就是，数据库的约束相关的比如外键之内的是使用数据库管理好还是程序控制好点。 玄惭（A2）：怎么学好数据库是一个比较大题目，数据库不仅仅是写SQL那么简单，即使知道了SQL怎么写，还需要很清楚的知道这条SQL他大概扫描了多少数据，返回多少数据，是否需要创建索引。至于SQL优化是一个比较专业的技术活，但是可以通过学习是可以掌握的，你可以把一条sql从执行不出来优化到瞬间完成执行，这个过程的成就感是信心满满的。 学习的方法可以有以下一些过程： 1、自己查资料，包括书本，在线文档，google，别人的总结等等，试图自己解决 2、多做实验，证明自己的想法以及判断 3、如果实在不行，再去论坛问，或者问朋友 4、如果问题解决了，把该问题的整个解决方法记录下来，以备后来的需要 5、多关注别人的问题，或许以后自己就遇到了，并总是试图去多帮助别人 6、习惯从多个方面去考虑问题，并且养成良好的总结习惯 下面是一些国内顶级数据库专家学习数据库的经验分享给大家： http://www.eygle.com/archives/2005/08/ecinieoracleouo.html 其实学习任何东西都是一样，没有太多的捷径可走，必须打好了坚实的基础，才有可以在进一步学习中得到快速提高。王国维在他的《人间词话》中曾经概括了为学的三种境界，我在这里套用一下： 古今之成大事业、大学问者，罔不经过三种之境界。 "昨夜西风凋碧树。独上高楼，望尽天涯路。"此第一境界也。 "衣带渐宽终不悔，为伊消得人憔悴。"此第二境界也。 "众里寻他千百度，蓦然回首，那人却在灯火阑珊处。"此第三境界也。 学习Oracle，这也是你必须经历的三种境界。第一层境界是说，学习的路是漫漫的，你必须做好充分的思想准备，如果半途而废还不如不要开始。这里，注意一个"尽"字，在开始学习的过程中，你必须充分阅读Oracle的基础文档，概念手册、管理手册、备份恢复手册等（这些你都可以在http://tahiti.oracle.com 上找到）；OCP认证的教材也值得仔细阅读。打好基础之后你才具备了进一步提升的能力，万丈高楼都是由地而起。第二层境界是说，尽管经历挫折、打击、灰心、沮丧，也都要坚持不放弃，具备了基础知识之后，你可以对自己感兴趣或者工作中遇到的问题进行深入的思考，由浅入深从来都不是轻而易举的，甚至很多时候你会感到自己停滞不前了，但是不要动摇，学习及理解上的突破也需要时间。第三次境界是说，经历了那么多努力以后，你会发现，那苦苦思考的问题，那百思不得其解的算法原理，原来答案就在手边，你的思路豁然开朗，宛如拨云见月。这个时候，学习对你来说，不再是个难题，也许是种享受，也许成为艺术。所以如果你想问我如何速成，那我是没有答案的。 不经一番寒彻骨，哪得梅花扑鼻香。 当然这三种境界在实际中也许是交叉的，在不断的学习中，不断有蓦然回首的收获。我自己在学习的过程中，经常是采用"由点及面法"。当遇到一个问题后，一定是深入下去，穷究根本，这样你会发现，一个简单的问题也必定会带起一大片的知识点，如果你能对很多问题进行深入思考和研究，那么在深处，你会发现，这些面逐渐接合，慢慢的延伸到oracle的所有层面，逐渐的你就能融会贯通。这时候，你会主动的去尝试全面学习Oracle，扫除你的知识盲点，学习已经成为一种需要。由实践触发的学习才最有针对性，才更能让你深入的理解书本上的知识，正所谓："纸上得来终觉浅，绝知此事要躬行"。实践的经验于我们是至为宝贵的。如果说有，那么这，就是我的捷径。想想自己，经常是"每有所获，便欣然忘食"，兴趣才是我们最好的老师。 Oracle的优化是一门学问，也是一门艺术，理解透彻了，你会知道，优化不过是在各种条件之下做出的均衡与折中。内存、外存；CPU、IO...对这一切你都需要有充分的认识和相当的了解，管理数据库所需要的知识并不单纯。作为一个数据库管理人员，你需要做的就是能够根据自己的知识以及经验在各种复杂情况下做出快速正确的判断。当问题出现时，你需要知道使用怎样的手段发现问题的根本；找到问题之后，你需要运用你的知识找到解决问题的方法。这当然并不容易，举重若轻还是举轻若重，取决于你具备怎样的基础以及经验积累。在网络上，Howard J. Rogers最近创造了一个新词组:Voodoo Tuning，用以形容那些没有及时更新自己的知识技能的所谓的Oracle技术专家。由于知识的陈旧或者理解的肤浅，他们提供的很多调整建议是错误的、容易使人误解的，甚至是荒诞的。他们提供的某些建议在有些情况下也许是正确的，如果你愿意回到Oracle5版或者6版的年代；但是这些建议在Oracle7.0,8.0 或者 Oracle8i以后往往是完全错误的。后来基于类似问题触发了互联网内Oracle顶级高手的一系列深入讨论，TOM、Jonathan Lewis、HJR等人都参与其中，在我的网站上(www.eygle.com )上对这些内容及相关链接作了简要介绍，有兴趣的可以参考。 HJR给我们提了很好的一个提示:对你所需要调整的内容，你必须具有充分的认识，否则你做出的判断就有可能是错误的。这也是我想给自己和大家的一个建议:学习和研究Oracle，严谨和认真必不可少。当然你还需要勤奋，我所熟悉的在Oracle领域有所成就的技术人员，他们共同的特点就是勤奋。如果你觉得掌握的东西没有别人多，那么也许就是因为，你不如别人勤奋。要是你觉得这一切过于复杂了，那我还有一句简单的话送给大家:不积跬步，无以至千里。学习正是在逐渐积累过程中的提高。现在Itpub给我们提供了很好的交流场所，很多问题都可以在这里找到答案，互相讨论，互相学习。这是我们的幸运，我也因此非常感谢这个网络时代。 参考书籍：如果是一个新人可以先买一些基本的入门书籍，比如MySQL：《深入浅出MySQL——数据库开发、优化与管理维护》，在进阶一点的就是《高性能MySQL（第3版）》 oracle的参考书籍：http://www.eygle.com/archives/2006/08/oracle_fundbook_recommand.html 最后建议不要在数据库中使用外键，让应用程序来保证。 千鸟（Q3）：我有一个问题想问问，现在在做一个与图书有关的项目，其中有一个功能是按图书书名搜索相似图书列表，问题不难，但是想优化一下，有如下问题想请教一下： 1、在图书数据库数据表的书名字段里，按图书书名进行关键字搜索，如何快速搜索相关的图书？现在由于数据不多，直接用的like模糊查找验证功能而已； 2、如何按匹配的关键度进行快速排序？比如搜索“算法”，有一本书是《算法》，另一本书是《算法设计》，要求前者排在更前面。现在的排序是根据数据表中的主键序号id进行的排序，没有达到想要的效果。谢谢了~~~~ ^_^ 玄惭（A3）： 1、如果数据量不大，是可以在数据库中完成搜索的，可以在搜索字段上创建索引，然后进行搜索查询： CREATE TABLE `book` ( `book_id` int(11) NOT NULL AUTO_INCREMENT, `book_name` varchar(100) NOT NULL, ............................. PRIMARY KEY (`book_id`), KEY `ind_name` (`book_name`) ) ENGINE=InnoDB select book.* from book , (select book_id from book where book_name like '%算法%') book_search_id where book.book_id=book_search_id.book_id; 但是当数据量变得很大后，就不在适合了，可以采用一些其他的第三方搜索技术比如sphinx； 2、root@127.0.0.1 : test 15:57:12> select book_id,book_name from book_search where book_name like '%算%' order by book_name; +---------+--------------+ | book_id | book_name | +---------+--------------+ | 2 | 算法 | | 1 | 算法设计 | 大黑豆（Q4）：请教一下有关模糊查询的优化，有没有什么比较成熟的好的策略？ 玄惭（A4）：模糊查询分为半模糊和全模糊，也就是： select * from book where name like 'xxx%';(半模糊) select * from book where name like '%xxx%';(全模糊) 半模糊可以可以使用到索引，全模糊在上面场景是不能使用到索引的，但可以进行一些改进，比如： select book.* from book , (select book_id from book where book_name like '%算法%') book_search_id where book.book_id=book_search_id.book_id; 注意这里book_id是主键，同时在book_name上创建了索引 上面的sql语句可以利用全索引扫描来完成优化，但是性能不会太好；特别在数据量大，请求频繁的业务场景下不要在数据库进行模糊查询；非得使用数据库的话，建议不要在生产库进行查询，可以在只读节点进行查询，避免查询造成主业务数据库的资源消耗完，导致故障. 可以使用一些开源的搜索引擎技术，比如sphinx. 蓝色之鹰（Q5）：难得大师出现。我想问下,sql优化一般从那几个方面入手？多表之间的连接方式：Nested Loops，Hash Join 和 Sort Merge Join，是不是Hash Join最优连接？ 玄惭（A5）：SQL优化需要了解优化器原理，索引的原理，表的存储结构，执行计划等，可以买一本书来系统的进行学习，多多实验；不同的数据库优化器的模型不一样，比如oracle支持NL，HJ，SMJ，但是mysql只支持NL，不通的连接方式适用于不同的应用场景； NL：对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择 HJ：对于列连接是做大数据集连接时的常用方式 SMJ：通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。 原远（Q6）：有个问题：分类表TQueCategory，问题表TQuestion（T-SQL) CREATE TABLE TQueCategory ( ID INT IDENTITY(1,1) PRIMARY KEY, --问题分类ID NAME VARCHAR(20) --问题分类名称 ) CREATE TABLE TQuestion ( ID INT IDENTITY(1,1) PRIMARY KEY, --问题ID CateID INT NOT NULL, --问题分类ID TITLE VARCHAR(50), --问题标题 CONTENT VARCHAR(500) --问题内容 ) 当前要统计某个分类下的问题数，有两种方式： 1.每次统计，在TQuestion通过CateID进行分组统计 SELECT CateID,COUNT(1) AS QueNum FROM TQuestion GROUP BY CateID WHERE 1=1 2.在TQueCategory表增加字段QueNum，用于标识该分类下的问题数量 ALTER TABLE TQueCategory ADD QueNum INT SELECT CateID,QueNum FROM TQueCategory 问：在哪种业务应用场景下采用上面哪种方式性能比较好，为什么？ 玄惭（A6）：方案一需要对 TQuestion 的 CateID字段进行分组，可以在CateID上创建一个索引，这样就可以索引扫描来完成查询；方案二需要对 TQueCategory 进行扫描就可以得出结果，但是必须在问题表有插入，删除的时候维护quenum数量；单单从SQL的性能来看，分类表的数量应该是远远小于问题表的数量的，所以方案二的性能会比较好；但是如果TQuestion 的插入非常频繁的话，会带来对TQueCategory的频繁更新，一次TQuestion 的insert或deleted就会带来一次TQueCategory 的update，这个代价其实是蛮高的；如果这个分类统计的查询不是非常频繁的话，建议还是使用方案一；同时还可能还会其他的业务逻辑统计需求（例如：CateID +时间），这个时候在把逻辑放到TQueCategory就不合适了。 玩站网（Q7）：无关技术方面：咨询一下，现在mysql新的版本，5.5.45后貌似修改了开源协议。是否意味着今后我们商业化使用mysql将受到限制？如果甲骨文真周到那一步，rds是否会受到影响？一个疑惑：为什么很少见到有人用mysql正则匹配？性能不好还是什么原因？ 玄惭（A7）：MySQL有商业版和社区版，RDS的MySQL采用开源的社区版进行改进，由专门的RDS MySQL源码团队来维护，国内TOP 10的mysql源码贡献者大部分都在RDS，包括了@丁奇，@彭立勋，@印风等；不在数据库中做业务计算，是保证数据库运行稳定的一个好的设计经验；是否影响性能与你的sql的执行频率，需要参与的计算数据量相关，当然还包括数据库所在主机的IO，cpu，内存等资源，离开了这些谈性能是没有多大意义的。 比哥（Q8）：分页该怎么优化才行？？？ 玄惭（A8）：可以参考这个链接，里面有很多的最佳实践，其中就包括了分页语句的优化： http://bbs.aliyun.com/read/168647.html 普通写法： select * from t where sellerid=100 limit 100000，20 普通limit M，N的翻页写法，往往在越往后翻页的过程中速度越慢，原因 mysql会读取表中的前M+N条数据，M越大，性能就越差：优化写法： select t1.* from t t1, (select id from t sellerid=100 limit 100000，20) t2 where t1.id=t2.id; 优化后的翻页写法，先查询翻页中需要的N条数据的主键id，在根据主键id 回表查询所需要的N条数据，此过程中查询N条数据的主键ID在索引中完成注意：需要在t表的sellerid字段上创建索引 create index ind_sellerid on t(sellerid); 案例： user_A (21:42:31): 这个sql该怎么优化，执行非常的慢： | Query | 51 | Sending data | select id, ... from t_buyer where sellerId = 765922982 and gmt_modified >= '1970-01-01 08:00:00' and gmt_modified <= '2013-06-05 17:11:31' limit 255000, 5000 SQL改写：selectt2.* from (selectid from t_buyer where sellerId = 765922982 andgmt_modified >= '1970-01-01 08:00:00' andgmt_modified <= '2013-06-05 17:11:31' limit255000, 5000）t1,t_buyer t2 where t1.id=t2.id index：seller_id,gmt_modified user_A(21:58:43): 好像很快啊。神奇，这个原理是啥啊。牛！！！ user_A(21:59:55): 5000 rows in set (4.25 sec)，前面要90秒。 板砖大叔（Q9）：问个小白的问题，大师建议一般索引类型和索引方法使用哪种，还是说针对不同的逻辑用不同的》？ 玄惭（A9）：这里所说的索引都是普通的b-tree索引，mysql，sqlserver，oracle 的关系数据库都是默认支持的。 几几届（Q10）：我也是这样，简单的会，仔细写也会写出来，但是就是不知道有没有更快或者更好的 玄惭（A10）：多写写SQL，掌握SQL优化的方法，自然这些问题不在话下了。 veeeye（Q11）：可以详细说明一下“最后建议不要在数据库中使用外键，让应用程序来保证。 ”的原因吗？我们公司在项目中经常使用外键，用程序来保证不是相对而言更加复杂了吗？ 玄惭（A11）：这里的不建议使用外键，主要考虑到：第一.维护成本上，把一些业务逻辑交由数据库来保证，当业务需求发生改动的时候，需要同时考虑应用程序和数据库，有时候一些数据库变更或者bug，可能会导致外键的失效；同时也给数据库的管理人员带来维护的麻烦，不便于管理。第二.性能上考虑，当大量数据写入的时候，外键肯定会带来一定的性能损耗，当出现这样的问题时候，再来改造去除外键，真的就不值得了；最后，不在数据库中参与业务的计算（存储过程，函数，触发器，外键），是保证数据库运行稳定的一个好的最佳实践。 优雅的固执（Q12）：十分想请大师分享下建立索引的经验，我平时简历索引是这样的，比如订单信息的话，建立，订单号，唯一聚集索引，其他的比如客户编号供应商编号商品编号这些建立非聚集不唯一索引仅此而已想更深入的跟大师学学 玄惭（A12）：建立索引，需要根据你的SQL语句来进行创建，不是每一个字段都需要进行创建，也不是一个索引都不创建,，可以把你的SQL语句，应用场景发出来看看。索引的创建确实是一个非常专业的技术活，需要掌握：表的存储方式，索引的原理，数据库的优化器，统计信息，最后还需要能够读懂数据库的执行计划，以此来判断索引是否创建正确；所以需要进行系统的学习才能掌握，附件是我在2011年的时候的一次公开课的ppt（下载课件：

mysql索引与sql调优 (1).pdf (1457 K) 下载次数:1119 ），希望对你有帮助，同时可以把你平时遇到的索引创建的疑惑发到论坛上来，大家可以一起交流。 小林阿小林（Q13）：mysql如何查询需要优化的语句，比如慢查询的步奏，如何找出需要通知程序员修改或者优化的sql语句，如何快速找到mysql瓶颈 玄惭（A13）：可以将mysql的慢日志打开，就可以记录执行时间超过指定阀值的慢SQL到本地文件或者数据库的slow_log表中；在RDS中默认是打开了慢日志功能的：long_query_time=1，表示会记录执行时间>=1秒的慢sql；如何快速找到mysql瓶颈：简单一点的方法，可以通过监控mysql所在主机的性能（CPU，IO，load等），以及mysql本身的一些状态值（connections，thread running，qps，命中率等）； RDS提供了完善的数据库监控体系，包括了CPU，IOPS，Disk，Connections，QPS，可以重点关注cpu，IO，connections，disk 4个指标； cpu，io，connections主要体现在了性能瓶颈，disk主要体现了空间瓶颈；有时候一条慢sql语句的频繁调用，也可能导致整个实例的cpu，io，connections达到100%；也有可能一条排序的sql语句，消耗大量的临时空间，导致实例的空间消耗完。 dentrite（Q14）：我一直有一个疑问，记录创建时间字段使用datetime和int类型有什么区别吗？两者对索引优化是否存在影响？ 玄惭（A14）：datetime和int都是占用数据库4个字节，所以在空间上没有什么差别；但是为了可读性，建议还是使用datetime数据类型。

DBA专家门诊一期：索引与sql优化问题汇总

微信号：腾讯云折扣返现

技术讨论QQ群： 863102326

作者: catherine

搜索

标签