一、索引我一般都是只有主键,这玩意儿,是不是越少越好?
答:
在日常的业务开发中,常见使用到索引的地方大概有两类:
(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优化一般从那几个方面入手?多表之间的连接方式:Nested Loops,Hash Join 和 Sort Merge Join,是不是Hash Join最优连接?
答:
SQL优化需要了解优化器原理,索引的原理,表的存储结构,执行计划等,可以买一本书来系统的进行学习,多多实验;不同的数据库优化器的模型不一样,比如oracle支持NL,HJ,SMJ,但是mysql只支持NL,不通的连接方式适用于不同的应用场景;
NL:对于被连接的数据子集较小的情况,嵌套循环连接是个较好的选择
HJ:对于列连接是做大数据集连接时的常用方式
SMJ:通常情况下散列连接的效果都比排序合并连接要好,然而如果行源已经被排过序,在执行排序合并连接时不需要再排序了,这时排序合并连接的性能会优于散列连接。
五、有个问题:分类表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
问:在哪种业务应用场景下采用上面哪种方式性能比较好,为什么?
答:
方案 一 需要对 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,可能会导致外键的失效;同时也给数据库的管理人员带来维护的麻烦,不便于管理。
第二.性能上考虑,当大量数据写入的时候,外键肯定会带来一定的性能损耗,当出现这样的问题时候,再来改造去除外键,真的就不值得了;
最后,不在数据库中参与业务的计算(存储过程,函数,触发器,外键),是保证数据库运行稳定的一个好的最佳实践。
八、mysql如何查询需要优化的语句,比如慢查询的步奏,如何找出需要通知程序员修改或者优化的sql语句,如何快速找到mysql瓶颈?
答:
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语句,消耗大量的临时空间,导致实例的空间消耗完。
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。