I have a big problem, I'm not very good with SQL... I have a database in mysql and when I do this query I have a response time around the 0.2s, so when i call it for a list of users (in a servlet) the response time goes around lots of seconds..

我有一个大问题，我不太擅长SQL……我在mysql中有一个数据库，当我做这个查询时，我的响应时间大约是0.2s，所以当我为一个用户列表(在servlet中)调用它时，响应时间大约是几秒。

QUERY:

SELECT visible,nlikes,nomecognome,profile_img,users_face.id 
FROM users_face 
LEFT OUTER JOIN `likes_face`  
on (users_face.fb_id = likes_face.fb_id) 
WHERE  users_face.fb_id =? and users_face.token_valid=1
ORDER BY date DESC limit 1

Is there any way to optimize this code or any good resource to study the optimization of queries?

有没有什么方法可以优化这个代码或任何好的资源来研究查询的优化?

CODE

   ArrayList<SocialMan> mans = new ArrayList<>();
    PreparedStatement ps;
    int nlikes, userid;
    String nomeCogn, prof;
    boolean visible;
    FacebookClient facebookClient = new DefaultFacebookClient(token, Version.VERSION_2_6);
    com.restfb.Connection<User> myFriends;
    myFriends = facebookClient.fetchConnection("me/friends",User.class, Parameter.with("limit", 999));
    for (User u : myFriends.getData()) {
        nlikes = -1;
        userid = -1;
        nomeCogn = "ERROR";
        prof = "ERROR";
        visible = false;
        try {

                ps = con.prepareStatement("SELECT visible,nlikes,nomecognome,profile_img,users_face.id FROM users_face LEFT OUTER JOIN `likes_face`  on (users_face.fb_id = likes_face.fb_id) WHERE  users_face.fb_id =? and users_face.token_valid=1 ORDER BY date DESC limit 1");

            ps.setString(1, "" + u.getId());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                nlikes = rs.getInt("nlikes");
                userid = rs.getInt("id");
                nomeCogn = rs.getString("nomecognome");
                prof = rs.getString("profile_img");
                visible = rs.getBoolean("visible");
            }
        } catch (SQLException ex) {
            Logger.getLogger(FaceLikes.class.getName()).log(Level.SEVERE, null, ex);
        }
        //   System.out.println("NOMECOGNOME: "+nomeCogn);
        if (userid != -1 && visible) {                
            mans.add(new SocialMan(nomeCogn, userid, prof, nlikes));
        }
    }
    nlikes = -1;
    userid = -1;
    nomeCogn = "ERROR";
    prof = "ERROR";

CREATE TABLE CODE

USERS

CREATE TABLE IF NOT EXISTS `users_face` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fb_id` varchar(45) NOT NULL,
  `fb_token` varchar(300) NOT NULL,
  `nomecognome` varchar(100) NOT NULL,
  `data_iscrizione` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `profile_img` varchar(255) NOT NULL,
  `visible` int(11) NOT NULL DEFAULT '1',
  `TOKEN` varchar(255) NOT NULL,
  `locale` varchar(255) NOT NULL,
  `token_valid` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`,`fb_id`),
  UNIQUE KEY `fb_id` (`fb_id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=173 ;

likes

CREATE TABLE IF NOT EXISTS `likes_face` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `nlikes` int(11) NOT NULL,
  `fb_id` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1182636 ;

3 个解决方案

#1

This is all I can come up with:

这就是我能想到的:

ALTER TABLE users_face ADD INDEX (fb_id, token_valid);

ALTER TABLE likes_face ADD INDEX (fb_id, nlikes, date);

You can't optimize the sorting, because the date column is in your non-primary table.

不能优化排序，因为日期列在非主表中。

If you want to optimize further, the next thing to do is to move the nlikes and date columns into the users_face table, then make the index on that table over the following columns in this order: (fb_id, token_valid, date, nlikes).

如果您想进一步优化，接下来要做的事情是将nlike和date列移动到users_face表中，然后将该表上的索引按以下顺序排列:(fb_id, token_valid, date, nlikes)。

Then you can skip the join in your query, and the sort order will be the order of the index, so that will be optimized.

然后可以跳过查询中的连接，排序顺序将是索引的顺序，因此将对其进行优化。

After that (or maybe before denormalizing), you should think about caching the data.

之后(或者在非规范化之前)，您应该考虑缓存数据。

#2

("Read this entire answer before starting to take action.")

(“在开始采取行动之前先阅读完整的答案。”)

This does not make sense:

这没有道理:

PRIMARY KEY (`id`,`fb_id`),
UNIQUE KEY `fb_id` (`fb_id`),
UNIQUE KEY `id` (`id`)

Tentatively, change to

暂时,改变

PRIMARY KEY(fb_id),
INDEX(id)

on the assumption that you usually look up the record by fb_id.

假设您通常使用fb_id查找记录。

If you say "list of users". Do you mean that WHERE fb_id IN ( ... )? Probably not since you have LIMIT 1. Or do you mean that you call that SELECT repeatedly?

如果你说“用户列表”。你的意思是fb_id在哪里?可能没有，因为有了极限1。或者你是说你反复调用SELECT ?

Gag! likes_face has an id that you are not using, plus fb_id that you are using but is not indexed. Use the same id for both tables: either get rid of AUTO_INCREMENT and change code logic for likes_face, or get rid of id and make fb_id the PK for both tables.

呕吐!likes_face有一个不使用的id，加上正在使用但没有索引的fb_id。对于两个表都使用相同的id:要么删除AUTO_INCREMENT和更改likes_face的代码逻辑，要么删除id并将fb_id设置为两个表的PK。

Are these tables 1:1?

这些表1:1吗?

Something is wrong. Why does likes_face haveAUTO_INCREMENT=1182636, while there seem to be only 173 users? If the tables are 1:1, are you usingREPLACE`? You will run out of ids!

什么是错的。为什么likes_face有auto_increment =1182636，而似乎只有173个用户?如果表格是1:1，你是用replace吗?你的身份证快用完了!

Restarting...

重新启动…

If the tables are 1:1:

如果表格是1:1:

totally get rid of id on both tables and have PRIMARY KEY(fb_id) on both tables,
完全删除两个表上的id，并在两个表上都有主键(fb_id)，
make fb_id CHARACTER SET ascii if Facebook limits ids to ascii,
如果Facebook将id限制为ascii，则创建fb_id字符集ascii，
no secondary indexes needed on either table.
两个表上都不需要辅助索引。

If likes_face is a count of likes for each day:

如果likes_face是每天的点赞数:

Get rid of id on both tables,
去掉两个表上的id，
make fb_id CHARACTER SET ascii if Facebook limits ids to ascii,
如果Facebook将id限制为ascii，则创建fb_id字符集ascii，
Give likes_face PRIMARY KEY(fb_id, date).
给出likes_face主键(fb_id, date)。
no secondary indexes needed on either table.
两个表上都不需要辅助索引。
Use IODKU to do the increment/insert into the likes-per-day table.
使用“碘”进行增量/插入到“每日模拟”表中。

If the tables are 1:many in some other way, I can't make sense of the schema.

如果表是1:以其他方式很多，我无法理解模式。

#3

You can use EXPLAIN of statement of MYSQL. Optimizing Queries with EXPLAIN

可以使用解释MYSQL语句。优化查询与解释

Someone suggested of creating indexes. Creating index is good option but avoid indexes if that column going to be modified frequently.

有人建议创建索引。创建索引是一个很好的选择，但是如果要经常修改该列，则要避免使用索引。

#1