[精华摘要] DBA专家门诊一期：索引与sql优化

摘要：现在的排序是根据数据表中的主键序号进行的排序，没有达到想要的效果。

答：

在日常的业务开发中，常见使用到索引的地方大概有两类：
（1）做业务约束需求，比如需要保证表中每行的单个字段或者某几个组合字段是唯一的，则可以在表中创建唯一索引
（2）提高SQL语句执行速度，可以根据SQL语句的查询条件在表中创建合适的索引，以此来提升SQL语句的执行速度

1、在图书数据库数据表的书名字段里，按图书书名进行关键字搜索，如何快速搜索相关的图书？ 现在由于数据不多，直接用的like模糊查找验证功能而已；
2、如何按匹配的关键度进行快速排序？比如搜索“算法”，有一本书是《算法》，另一本书是《算法设计》，要求前者排在更前面。现在的排序是根据数据表中的主键序号id进行的排序，没有达到想要的效果。

答：

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、select book_id,book_name from book_search where book_name like "%算%" order by book_name;  
+---------+--------------+  
| book_id | book_name    |  
+---------+--------------+  
|       2 | 算法       |  
|       1 | 算法设计 | 

答：

模糊查询分为半模糊和全模糊，也就是：

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.

答：

SQL优化需要了解优化器原理，索引的原理，表的存储结构，执行计划等，可以买一本书来系统的进行学习，多多实验；不同的数据库优化器的模型不一样，比如oracle支持NL，HJ，SMJ，但是mysql只支持NL，不通的连接方式适用于不同的应用场景；
NL：对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择
HJ：对于列连接是做大数据集连接时的常用方式
SMJ：通常情况下散列连接的效果都比排序合并连接要好，然而如果行源已经被排过序，在执行排序合并连接时不需要再排序了，这时排序合并连接的性能会优于散列连接。

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
问：在哪种业务应用场景下采用上面哪种方式性能比较好，为什么？

答：

方案 一 需要对 TQuestion 的 CateID字段 进行分组 ，可以在CateID上创建一个索引，这样就可以索引扫描来完成查询；
方案 二 需要对 TQueCategory 进行扫描就可以得出结果，但是必须在问题表有插入，删除的时候维护quenum数量；
分析：
单单从SQL的性能来看，分类表的数量应该是远远小于问题表的数量的，所以方案二的性能会比较好； 但是如果TQuestion 的插入非常频繁的话，会带来对TQueCategory的频繁更新，一次TQuestion 的insert或deleted就会带来一次TQueCategory 的update，这个代价其实是蛮高的； 如果这个分类统计的查询不是非常频繁的话，建议还是使用方案一； 同时还可能还会其他的业务逻辑统计需求（例如：CateID +时间），这个时候在把逻辑放到TQueCategory就不合适了。

答：

普通写法：
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 where 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);

案例：
慢查询：

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;
5000 rows in set (90 sec)

优化后：

selectt2.* from 
(select id from t_buyer where sellerId = 765922982 and
andgmt_modified >= "1970-01-01 08:00:00" and
andgmt_modified <= "2013-06-05 17:11:31"   
limit 255000, 5000）t1,t_buyer t2 where t1.id=t2.id  
index：seller_id,gmt_modified
5000 rows in set (4.25 sec)

答：

这里的不建议使用外键，主要考虑到 ：
第一.维护成本上，把一些业务逻辑交由数据库来保证，当业务需求发生改动的时候，需要同时考虑应用程序和数据库，有时候一些数据库变更或者bug，可能会导致外键的失效；同时也给数据库的管理人员带来维护的麻烦，不便于管理。
第二.性能上考虑，当大量数据写入的时候，外键肯定会带来一定的性能损耗，当出现这样的问题时候，再来改造去除外键，真的就不值得了；
最后，不在数据库中参与业务的计算（存储过程，函数，触发器，外键），是保证数据库运行稳定的一个好的最佳实践。

答：

1.可以将mysql的慢日志打开，就可以记录执行时间超过指定阀值的慢SQL到本地文件或者数据库的slow_log表中;在RDS中默认是打开了慢日志功能的：long_query_time=1，表示会记录执行时间>=1秒的慢sql;

2.如何快速找到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语句，消耗大量的临时空间，导致实例的空间消耗完。