情况就是相当于学生的信息表,上千万条数据。
并发数还好,单次查询也是0.0006秒。
I/O差,但有足量内存。
使用的是INNODB
目前用着mysql 5.6.22,也做了优化和索引,奈何基本上所有查询都是select全表的。
用的最多的语句是
SELECT count(1) FROM (SELECT * FROM edu WHERE 1=1) as cnd;
SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 50 OFFSET 0;
还有insert、update、delete。
没有用到联合索引,就是排序比较多。
现在负载不大,0.21 0.23 0.17。
查询多的时候,1.11 1.10 1.03。
表:
CREATE TABLE edu
(
ID
bigint(20) unsigned NOT NULL AUTO_INCREMENT,
lastupdate
date NOT NULL,
pro
varchar(100) NOT NULL,
sdate
date NOT NULL,
sc
varchar(100) NOT NULL,
cid
varchar(100) NOT NULL,
st
varchar(100) NOT NULL,
session
varchar(100) NOT NULL,
pm
int(20) NOT NULL,
PRIMARY KEY (ID
),
KEY pro
(pro
),
KEY sdate
(sdate
),
KEY sc
(sc
),
KEY cid
(cid
),
KEY st
(st
),
KEY session
(session
),
KEY lastupdate
(lastupdate
),
KEY pm
(pm
)
) ENGINE=InnoDB AUTO_INCREMENT=12679325 DEFAULT CHARSET=utf8
想问下,如果类似这种,可以改成memcache或redis不。。。或者存放到/dev/shm下的其他数据库不?
不知有无意义。。。
请各位指点迷津,谢谢!
1
yangqi 2015-02-27 01:36:17 +08:00 1
SELECT count(1) FROM (SELECT * FROM edu WHERE 1=1) as cnd;
这个怎么想的,难道不直接select count(*) from edu? |
2
yangqi 2015-02-27 01:40:05 +08:00 1
另外你每个列都加索引, 相当于索引的大小和整个表的差不多了,估计是不可能全部放到内存里缓存的,这里需要优化,另外有足量内存的话增加innodb_buffer_pool_size
|
4
kn007 OP innodb_buffer_pool_size已经是足够大了,剩余空间还有很多。
因为每个列都可能被排序。所以都加上索引 |
5
kn007 OP @yangqi innodb_buffer_pool_size已经是足够大了,剩余空间还有很多。
因为每个列都可能被排序。所以都加上索引 |
7
ferock 2015-02-27 01:52:00 +08:00 1
主索引一次sql 执行一般只有一个是最有效的,多看看查询执行计划
|
9
kn007 OP @ferock 看了下,
SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 50 OFFSET 0; 用了filesort,囧。这句还能怎么提升不 |
12
kn007 OP 超过2个字段就use filesort。晕死
|
13
kn007 OP @ferock select 字段(NULL)比select *效率(use filesort)反而慢多了。。。。。
兄弟最这条语句有什么建议么?谢谢。 SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 50 OFFSET 0; lastupdate用的最多,其他可能也会用到,除了ID |
15
kn007 OP @ferock
联合索引ID: mysql> EXPLAIN SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 100 OFFSET 0; +----+-------------+------------+-------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | edu | index | NULL | ID | 1528 | NULL | 12679325 | Using index; Using filesort | +----+-------------+------------+-------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> show status like 'handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 241 | +-----------------------+-------+ 7 rows in set (0.00 sec) 无索引: mysql> EXPLAIN SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 100 OFFSET 0; +----+-------------+------------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | edu | ALL | NULL | NULL | NULL | NULL | 12679325 | Using filesort | +----+-------------+------------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) mysql> show status like 'handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 241 | +-----------------------+-------+ 7 rows in set (0.00 sec) |
16
yangqi 2015-02-27 09:17:25 +08:00 1
你这个要从表的设计上来优化了,先看看你average row length,ID为啥要用bigint不用int, unsigned int最大值42亿,难道还不够么?另外这张表还能normalize么?
|
17
kakaryan 2015-02-27 09:37:22 +08:00 1
动不动就是全表,何必硬来,上sphinx.
|
20
aru 2015-02-27 10:32:09 +08:00 1
SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 50 OFFSET 0;
对 lastupdate 做索引,不应该会是filesort,你的表到底做了什么? 另外,你的数据库文件含索引一共多大? |
21
aru 2015-02-27 10:34:39 +08:00 1
忽然想到一个可能,是不是 lastupdate 只有少数几个值?这样索引就是基本无效果了,mysql会忽略掉索引
|
22
cevincheung 2015-02-27 10:39:12 +08:00
稳稳的postgresql
|
24
kn007 OP @cevincheung 我也想啊,不过pgsql不大熟,用了pgsql我还要做优化,同服务器其他项目都用mysql,mysql从参数上已经是最优了,现在就是表和查询看能不能优化。
|
25
yangqi 2015-02-27 11:08:40 +08:00 1
ARL 219有点太长了,应该考虑拆分表
normalize就是数据库的标准化,我不太清楚你那些varchar列的含义,不好判断 |
26
jlnsqt 2015-02-27 11:10:37 +08:00 1
MySQL千万数据的表维护比较慢,可以考虑下MongoDB。本身建的表可能也有问题,字段大都VARCHAR(100),这个往内存放的是否都是按照最大的放的。你可以先用SET profiling = 1,然后执行SQL,在执行show profiles看一下到底是哪个部分最耗时间
|
27
kn007 OP @jlnsqt 我SET profiling = 1,但是SELECT @@profiling一直是0,我执行sql和SHOW PROFILES,返回空。
我用了phpmyadmin的性能分析: http://i2.tietuku.com/912bffcecf4ed6e6.png |
28
jsq2627 2015-02-27 11:30:59 +08:00 via iPhone 1
@kn007 http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html
In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: The key used to fetch the rows is not the same as the one used in the ORDER BY: SELECT * FROM t1 WHERE key2=constant ORDER BY key1; |
30
nine 2015-02-27 11:36:33 +08:00
innodb 要把“主键”和“排序字段”做联合索引,否则count()的时候。。呵呵
已转PG,可以存array, json(jsonb)的,查询起来爽得很,不用搞那么多字段(利益相关:Rails 4.2) |
31
kn007 OP @yangqi 不懂拆分表。。。求指教。
数据库如何标准化? varchar没啥意义的,我也不知道定义什么好。innodb又无所谓静态表 ID int(20) unsigned NOT NULL AUTO_INCREMENT, 数字 lastupdate date NOT NULL, 日期(2015-02-27这种) pro varchar(100) NOT NULL, 中文 sdate date NOT NULL, 日期 sc varchar(100) NOT NULL, 中英文(可能带符号) cid varchar(100) NOT NULL, 数字(可能会带中英文符号,目前只有数字) st varchar(100) NOT NULL, 中文数字 session varchar(100) NOT NULL, 数字(可能会带中英文符号,目前只有数字) pm int(20) NOT NULL, 数字(可能符号) 最长的应该是英文字符,60多个字母,位于sc |
32
kn007 OP @nine 表字段是不能再少的。目前count是using index的。索引字段应该也是够了,我加了个联合索引了。
pgsql确实有在考虑,毕竟我也算是利用json,但是整个php的db.class就要改了。还要给服务器部署个pgsql,我现在空余内存不多了,剩几百M。mysql是大户,问题服务器上的其他应用也是mysql。囧 |
33
mengskysama 2015-02-27 11:47:40 +08:00
贴一张show profile看看?
select全表不知道mysql会不会做cache。。 |
34
zhengkai 2015-02-27 12:18:23 +08:00
字段名全是缩写看不明白,你这种要么写全要么字段写注释
这么多 varchar(100) 看着好奇怪,是说有姓名或者学号之类的?感觉应该做几个关联表出去,把那几个 varchar(100) 做成自增id的,这样索引大小能小很多倍 |
35
kn007 OP @mengskysama 看27楼。原文:
我SET profiling = 1,但是SELECT @@profiling一直是0,我执行sql和SHOW PROFILES,返回空。 我用了phpmyadmin的性能分析: http://i2.tietuku.com/912bffcecf4ed6e6.png |
36
kn007 OP @mengskysama mysql的query_cache是disable的。我前面做了memcache,仅负责数据1分钟缓存
|
37
kn007 OP @zhengkai 看31楼
ID int(20) unsigned NOT NULL AUTO_INCREMENT, 数字 lastupdate date NOT NULL, 日期(2015-02-27这种) pro varchar(100) NOT NULL, 中文 sdate date NOT NULL, 日期 sc varchar(100) NOT NULL, 中英文(可能带符号) cid varchar(100) NOT NULL, 数字(可能会带中英文符号,目前只有数字) st varchar(100) NOT NULL, 中文数字 session varchar(100) NOT NULL, 数字(可能会带中英文符号,目前只有数字) pm int(20) NOT NULL, 数字(可能符号) |
38
kn007 OP 略微补充下,现在mysql临时文件位于/dev/shm了
|
39
mengskysama 2015-02-27 16:09:13 +08:00
@kn007 感觉挺快的,看楼下怎么说
|
40
kn007 OP @mengskysama 啥感觉挺快的?查询时间么?是挺快的,就是想看看,能不能发挥下各位v友的脑洞,帮忙进一步提升。。filesort长此久往不是事啊
|
41
aru 2015-02-27 16:19:39 +08:00
增加一个 lastupdate 和ID的联合索引
看看这个sql 会不会使用索引 SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 100 OFFSET 0; |
42
kn007 OP 不知你有没有看下补充?
我现在是有联合索引了,目前 EXPLAIN SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 100 OFFSET 0; 是Using index; Using filesort 。 如果我删除了联合索引KEY `ID` (`ID`,`lastupdate`,`pro`,`sdate`,`sc`,`cid`,`st`,`session`,`pm`),添加索引KEY `ID` (`ID`,`lastupdate`),执行sql: EXPLAIN SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 100 OFFSET 0; 是Using filesort |
43
yangyuan 2015-02-27 17:40:32 +08:00 1
我不是 mysql 专家,但是 条件+排序+offset,这种情况,就算有争对性 index,filesort 应该是无法避免的。offset 引发的大量 handler_read_rnd_next 更是一个问题,大并发下会拖垮整个数据库性能,你会感觉到随着并发量上升后,并发量和数据库查询耗时几乎是线性关系。
针对建议数据库逻辑,可考虑的方法如:使用缓存表、使用簇集(主键)索引代替 order by 和 offset。具体就看业务逻辑了,没有通用办法。 并发量足够大的话,使用缓存是必须的,但是不能因此回避数据库的优化。 |
44
kn007 OP @yangyuan 首先,非常感谢!谢谢。
嗯,我没有放弃对数据库的优化,单纯从mysql与服务器之间的参数调优,我已经做的非常好了。 对于你说道的,“使用缓存表、使用簇集(主键)索引代替 order by 和 offset”该如何做呢? 我也只是对mysql略知一二而已,只想尽自己能力做好。 并发量大的时候,曾经是24.1 23.7 23.5的负载,做了mc缓存60秒,就降下来了,但负责也是偶尔接近2,只是不是20多这样的数。 或许你能再给一些更深入的指导?谢谢 |
46
kn007 OP 现在还有个问题,就是count多,Handler_read_next非常大,转换成myisam的话,有需要经常优化表。
|
47
amlun 2015-02-27 18:49:23 +08:00
这么多索引,没有用到你想要的索引 “KEY lastupdate (lastupdate)”,所以需呀filesort
有两个办法解决: 1、force index 强制使用索引。 2、删掉无用的索引,有一些索引是没有意义的。 |
48
kn007 OP @lunweiwei 谢谢,怪我没说清楚情况。。。。force index没意义的。你可以看到下面的评论,如21楼,mysql忽略了,
另外每个索引都有意义。force index一样也是需要filesort。如43楼所说的原因。 我非常明白索引的创建。因为我在顶楼的语句只是其中一条,我需要用到各条字段排序或where,不可能不要索引的。所以照成每条都需要索引。 |
49
amlun 2015-02-27 19:01:26 +08:00
@kn007 这样啊。。没往下看。
应该还是需要根据业务场景去优化,如果对该表需要通过不同字段频繁的查询读取排序,那建议自己建“数据/索引”结构,比如用搜索等。。 |
51
iminto 2015-02-27 19:19:23 +08:00
除了mysql,PHP还能有其他选择吗?真没有。别说什么PG,ORACLE了
|
53
randyzhao 2015-02-27 20:12:51 +08:00 1
ALTER TABLE `edu` ADD INDEX `IDX` (`lastupdate`,`pro`,`sdate`,`sc`,`cid`,`st`,`session`,`pm`);
EXPLAIN SELECT * FROM edu WHERE 1 ORDER BY lastupdate desc LIMIT 50 OFFSET 0; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | edu | index | NULL | IDX2 | 260 | NULL | 1 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ ALTER TABLE `edu` ADD INDEX `IDX` (`sdate`,`lastupdate`,`pro`,`sc`,`cid`,`st`,`session`,`pm`); EXPLAIN SELECT * FROM edu WHERE 1 ORDER BY sdate desc LIMIT 50 OFFSET 0; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | edu | index | NULL | IDX2 | 260 | NULL | 1 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ |
54
yangyuan 2015-02-27 21:13:04 +08:00 1
@kn007 比如在 filesort 的情况下,你 limit 10 offset 5000,必然需要找到前 5000 个才能找到这 10 个,handler_read_rnd_next 就成了一个无法跨越的硬伤。
那问题应该是如何避免 filesort。至于索引,该不该用还要看具体情况,尤其海量数据要谨慎使用索引。 按你的说法,1G 数据,lastupdate 还不到 30 个,活生生触发 filesort。那这个排序在需求上是有必要的么?很有可能是可以规避的,要看具体需求。(如果30个数量比较均散,可以考虑弄个 update_id,先 where 一下,就算触发 filesort,handler_read_rnd_next 也会很小;另一种情况比如这 30 个是最近修改 30 个,那可以考虑弄个 modified 字段) 总之,就是各种手段规避 filesort,如果无法规避,那么应该尽量减少候选结果集。具体方案没有什么套路,有的地方会用timestamp做主键id,有的地方(尤其是地理数据)会把数据拆表存放。 count 的问题,没必要因为这个换 myiasm,精度要求不高可以缓存,要求实时准确,则考虑触发器。 先 sql 再 php 的话,除非数据很小,否则不建议。 |
55
cevincheung 2015-02-27 21:19:32 +08:00
@kn007 那你问跟什么数据库搭……
|
56
yangqi 2015-02-27 22:31:56 +08:00 1
@kn007 数据库的优化只能解决部分问题,最重要的还是表的设计。我是想知道你表里各列的关系,看有没有可能标准化。
比如每个ID对应的其他列pro, sdate, sc, cid是一对一的关系,还是可能会有重复的。你现在这些列名用的大家都不明白是什么意思。 |
59
kn007 OP @cevincheung 因为我确实对mysql感到失望
|
64
kn007 OP @randyzhao 对于大表提升非常多,因为没有硬盘交互的时间,虽然我用的是ramdisk,但是少了这项,就要快上2倍以上。而且最重要的是负载大大减少了
|
65
kn007 OP @randyzhao using index相当于你把东西给我,using filesort相当于你把东西整理好打包后给我。。。后者需要时间和精力
|