求教下，经常全表， php 跟什么数据库比较搭

This topic created in 4119 days ago, the information mentioned may be changed or developed.

情况就是相当于学生的信息表，上千万条数据。
并发数还好，单次查询也是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下的其他数据库不？
不知有无意义。。。
请各位指点迷津，谢谢！

Supplement 1 · Feb 27, 2015

补充说明：

目前想优化的语句，
SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 50 OFFSET 0;

====================================================

查询目前是Using index; Using filesort ，结果看15楼。

ARL是219。

我用了phpmyadmin的性能分析：
http://i2.tietuku.com/912bffcecf4ed6e6.png

最新表：
CREATE TABLE `edu` (
`ID` int(20) unsigned NOT NULL AUTO_INCREMENT,/* 数字 */
`lastupdate` date NOT NULL,/* 日期(2015-02-27这种) */
`pro` varchar(20) NOT NULL,/* 中英文 */
`sdate` date NOT NULL,/* 日期 */
`sc` varchar(20) NOT NULL,/* 中英文（可能带符号） */
`cid` varchar(10) NOT NULL,/* 数字（可能会带中英文符号，目前只有数字） */
`st` varchar(20) NOT NULL,/* 中文数字 */
`session` varchar(10) NOT NULL,/* 数字（可能会带中英文符号，目前只有数字） */
`pm` int(20) unsigned 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`),
KEY `ID` (`ID`,`lastupdate`,`pro`,`sdate`,`sc`,`cid`,`st`,`session`,`pm`)
) ENGINE=InnoDB AUTO_INCREMENT=12683167 DEFAULT CHARSET=utf8

Supplement 2 · Feb 27, 2015

我现在有个初步的想法。
就是php来做排序和搜索。mysql只管数据源（直接select table），这样mysql就是Using index了。
不知可不可行。json应该可以来排序和字段搜索吧？不知有没有熟悉php+json的。

Supplement 3 · Feb 27, 2015

经常count，我是否要用MyISAM呢。。。头疼

Supplement 4 · Feb 27, 2015

感觉还是很头大。没什么idea了。
因为表每分钟都可能有更新。头疼

Supplement 5 · Feb 27, 2015

又一个索引问题解决感谢@randyzhao 。

Supplement 6 · Mar 3, 2015

问题已经都解决了，感谢大家。

留下博客，欢迎来访：http://kn007.net

varchar

lastupdate

null

70 replies • 2015-02-27 23:25:54 +08:00

yangqi

Feb 27, 2015

SELECT count(1) FROM (SELECT * FROM edu WHERE 1=1) as cnd;

这个怎么想的，难道不直接select count(*) from edu?

yangqi

Feb 27, 2015

另外你每个列都加索引, 相当于索引的大小和整个表的差不多了，估计是不可能全部放到内存里缓存的，这里需要优化，另外有足量内存的话增加innodb_buffer_pool_size

kn007

Feb 27, 2015

@yangqi 这个我看速度一样没改，我用的w2grid其实，这是自带的命令。

kn007

Feb 27, 2015

innodb_buffer_pool_size已经是足够大了，剩余空间还有很多。
因为每个列都可能被排序。所以都加上索引

kn007

Feb 27, 2015

@yangqi innodb_buffer_pool_size已经是足够大了，剩余空间还有很多。
因为每个列都可能被排序。所以都加上索引

kn007

Feb 27, 2015

@yangqi 已改正，谢谢

ferock

PRO

Feb 27, 2015

主索引一次sql 执行一般只有一个是最有效的，多看看查询执行计划

kn007

Feb 27, 2015

@ferock 查询计划没问题，Using index

kn007

Feb 27, 2015

@ferock 看了下，
SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 50 OFFSET 0;
用了filesort，囧。这句还能怎么提升不

kn007

Feb 27, 2015

@ferock 看了select字段可以use index，但是太多类似表了，囧

kn007

Feb 27, 2015

@ferock select所有字段，一样use filesort。。

kn007

Feb 27, 2015

超过2个字段就use filesort。晕死

kn007

Feb 27, 2015

@ferock select 字段（NULL）比select *效率(use filesort)反而慢多了。。。。。
兄弟最这条语句有什么建议么？谢谢。
SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 50 OFFSET 0;

lastupdate用的最多，其他可能也会用到，除了ID

kn007

Feb 27, 2015

@ferock 做了联合索引 using index; using filesort，速度跟以前差不多

kn007

Feb 27, 2015

@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)

yangqi

Feb 27, 2015

你这个要从表的设计上来优化了，先看看你average row length，ID为啥要用bigint不用int, unsigned int最大值42亿，难道还不够么？另外这张表还能normalize么？

kakaryan

Feb 27, 2015

动不动就是全表,何必硬来,上sphinx.

kn007

Feb 27, 2015

@yangqi normalize？不懂。
默认bigint，我改下。
ARL是219

kn007

Feb 27, 2015

@kakaryan 谢谢，没用过这货，回头看看

aru

Feb 27, 2015

SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 50 OFFSET 0;
对 lastupdate 做索引，不应该会是filesort，你的表到底做了什么?
另外，你的数据库文件含索引一共多大?

aru

Feb 27, 2015

忽然想到一个可能，是不是 lastupdate 只有少数几个值？这样索引就是基本无效果了，mysql会忽略掉索引

cevincheung

Feb 27, 2015

稳稳的postgresql

kn007

Feb 27, 2015

@aru 不到1G，lastupdate的值确实还不够多，最多30个。

kn007

Feb 27, 2015

@cevincheung 我也想啊，不过pgsql不大熟，用了pgsql我还要做优化，同服务器其他项目都用mysql，mysql从参数上已经是最优了，现在就是表和查询看能不能优化。

yangqi

Feb 27, 2015

ARL 219有点太长了，应该考虑拆分表

normalize就是数据库的标准化，我不太清楚你那些varchar列的含义，不好判断

jlnsqt

Feb 27, 2015

MySQL千万数据的表维护比较慢，可以考虑下MongoDB。本身建的表可能也有问题，字段大都VARCHAR(100)，这个往内存放的是否都是按照最大的放的。你可以先用SET profiling = 1，然后执行SQL，在执行show profiles看一下到底是哪个部分最耗时间

kn007

Feb 27, 2015

@jlnsqt 我SET profiling = 1，但是SELECT @@profiling一直是0，我执行sql和SHOW PROFILES，返回空。
我用了phpmyadmin的性能分析：
http://i2.tietuku.com/912bffcecf4ed6e6.png

jsq2627

Feb 27, 2015 via iPhone

@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;

kn007

Feb 27, 2015

@jsq2627 嗯，看到了。。。有没有啥好办法呢。。。

nine

Feb 27, 2015

innodb 要把“主键”和“排序字段”做联合索引，否则count()的时候。。呵呵

已转PG，可以存array, json（jsonb）的，查询起来爽得很，不用搞那么多字段（利益相关：Rails 4.2）

kn007

Feb 27, 2015

@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

kn007

Feb 27, 2015

@nine 表字段是不能再少的。目前count是using index的。索引字段应该也是够了，我加了个联合索引了。
pgsql确实有在考虑，毕竟我也算是利用json，但是整个php的db.class就要改了。还要给服务器部署个pgsql，我现在空余内存不多了，剩几百M。mysql是大户，问题服务器上的其他应用也是mysql。囧

mengskysama

Feb 27, 2015

贴一张show profile看看？
select全表不知道mysql会不会做cache。。

zhengkai

Feb 27, 2015

字段名全是缩写看不明白，你这种要么写全要么字段写注释

这么多 varchar(100) 看着好奇怪，是说有姓名或者学号之类的？感觉应该做几个关联表出去，把那几个 varchar(100) 做成自增id的，这样索引大小能小很多倍

kn007

Feb 27, 2015

@mengskysama 看27楼。原文：
我SET profiling = 1，但是SELECT @@profiling一直是0，我执行sql和SHOW PROFILES，返回空。
我用了phpmyadmin的性能分析：
http://i2.tietuku.com/912bffcecf4ed6e6.png

kn007

Feb 27, 2015

@mengskysama mysql的query_cache是disable的。我前面做了memcache，仅负责数据1分钟缓存

kn007

Feb 27, 2015

@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, 数字（可能符号）

kn007

Feb 27, 2015

略微补充下，现在mysql临时文件位于/dev/shm了

mengskysama

Feb 27, 2015

@kn007 感觉挺快的,看楼下怎么说

kn007

Feb 27, 2015

@mengskysama 啥感觉挺快的？查询时间么？是挺快的，就是想看看，能不能发挥下各位v友的脑洞，帮忙进一步提升。。filesort长此久往不是事啊

aru

Feb 27, 2015

增加一个 lastupdate 和ID的联合索引
看看这个sql 会不会使用索引
SELECT * FROM edu WHERE 1=1 ORDER BY lastupdate desc LIMIT 100 OFFSET 0;

kn007

Feb 27, 2015

不知你有没有看下补充？
我现在是有联合索引了，目前
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

yangyuan

Feb 27, 2015

我不是 mysql 专家，但是条件+排序+offset，这种情况，就算有争对性 index，filesort 应该是无法避免的。offset 引发的大量 handler_read_rnd_next 更是一个问题，大并发下会拖垮整个数据库性能，你会感觉到随着并发量上升后，并发量和数据库查询耗时几乎是线性关系。
针对建议数据库逻辑，可考虑的方法如：使用缓存表、使用簇集（主键）索引代替 order by 和 offset。具体就看业务逻辑了，没有通用办法。
并发量足够大的话，使用缓存是必须的，但是不能因此回避数据库的优化。

kn007

Feb 27, 2015

@yangyuan 首先，非常感谢！谢谢。
嗯，我没有放弃对数据库的优化，单纯从mysql与服务器之间的参数调优，我已经做的非常好了。
对于你说道的，“使用缓存表、使用簇集（主键）索引代替 order by 和 offset”该如何做呢？
我也只是对mysql略知一二而已，只想尽自己能力做好。
并发量大的时候，曾经是24.1 23.7 23.5的负载，做了mc缓存60秒，就降下来了，但负责也是偶尔接近2，只是不是20多这样的数。
或许你能再给一些更深入的指导？谢谢

kn007

Feb 27, 2015

@yangyuan 或者php能不能做排序呢？mysql只管数据源。排序，检索通过php来

kn007

Feb 27, 2015

现在还有个问题，就是count多，Handler_read_next非常大，转换成myisam的话，有需要经常优化表。

amlun

Feb 27, 2015

这么多索引，没有用到你想要的索引 “KEY lastupdate (lastupdate)”，所以需呀filesort
有两个办法解决：
1、force index 强制使用索引。
2、删掉无用的索引，有一些索引是没有意义的。

kn007

Feb 27, 2015

@lunweiwei 谢谢，怪我没说清楚情况。。。。force index没意义的。你可以看到下面的评论，如21楼，mysql忽略了，
另外每个索引都有意义。force index一样也是需要filesort。如43楼所说的原因。
我非常明白索引的创建。因为我在顶楼的语句只是其中一条，我需要用到各条字段排序或where，不可能不要索引的。所以照成每条都需要索引。

amlun

Feb 27, 2015

@kn007 这样啊。。没往下看。
应该还是需要根据业务场景去优化，如果对该表需要通过不同字段频繁的查询读取排序，那建议自己建“数据/索引”结构，比如用搜索等。。

kn007

Feb 27, 2015

@amlun 额，你的意思是？

iminto

Feb 27, 2015

除了mysql，PHP还能有其他选择吗？真没有。别说什么PG,ORACLE了

kn007

Feb 27, 2015

@iminto 所以我现在就是想php提取mysql数据，排序和条件搜索，根据数据来做。正在尝试

randyzhao

Feb 27, 2015

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 |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

yangyuan

Feb 27, 2015

@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 的话，除非数据很小，否则不建议。

cevincheung

Feb 27, 2015

@kn007 那你问跟什么数据库搭……

yangqi

Feb 27, 2015

@kn007 数据库的优化只能解决部分问题，最重要的还是表的设计。我是想知道你表里各列的关系，看有没有可能标准化。

比如每个ID对应的其他列pro, sdate, sc, cid是一对一的关系，还是可能会有重复的。你现在这些列名用的大家都不明白是什么意思。

kn007

Feb 27, 2015

@randyzhao 居然是这样，为什么呢？去除ID，每个单独联合索引就可以呢

kn007

Feb 27, 2015

@yangyuan 53楼的伙计搞定了。。

kn007

Feb 27, 2015

@cevincheung 因为我确实对mysql感到失望

kn007

Feb 27, 2015

@yangqi 谢谢，其实意义我觉得意义并不大吧？现在53楼的伙计解决了这个问题

randyzhao

Feb 27, 2015

@kn007 应该是顺序问题我试了下 order by 的时候只认联合索引第一个

kn007

Feb 27, 2015

@randyzhao 哦哦，好的，非常感谢。

randyzhao

Feb 27, 2015

@kn007 我好奇一下, 我没测试性能.
虽然这样确实没有 Using filesort; 了, 但不知性能提升几何?

kn007

Feb 27, 2015

@randyzhao 对于大表提升非常多，因为没有硬盘交互的时间，虽然我用的是ramdisk，但是少了这项，就要快上2倍以上。而且最重要的是负载大大减少了

kn007

Feb 27, 2015

@randyzhao using index相当于你把东西给我，using filesort相当于你把东西整理好打包后给我。。。后者需要时间和精力

randyzhao

Feb 27, 2015

@kn007 nice!

kn007

Feb 27, 2015

@randyzhao 非常感谢，以前也经历过这个，但我居然搞了1个多星期，也没想到。非常感谢，真是走了不少弯路。我尝试过减少联合索引项目，但就是忽略了ID已经是主键这个关键。而其他联合索引，优先取第一个键值，我有想到，但却没深思。再次感谢

kn007

Feb 27, 2015

@yangyuan count触发器怎么搞呢？请指教，谢谢

randyzhao

Feb 27, 2015

@kn007 客气了哈我也学习了以前真没注意过这个

kn007

Feb 27, 2015

@randyzhao 我最得意的应该是有个300多W的表，我只用一个索引就可以快速取到值（算是根据业务），是MyISAM表。是取介于中间值的，我改为获取左值后倒序只取一个。以前老是用between根本没什么性能，大表mysql有时会忽略掉索引，好像是where跑索引跑了100W多万取不到就会放弃。以前就是做了前后索引，但中间部分经常是没有用到索引（FORCE也没用），导致全表。现在单个条件就会使用到索引。
你解决了大忙了，我已经在动手改php了呢，准备是mysql仅取数据，排序检索靠外部。。。看来现在不用继续了。。