Mysql操作优化

 

       项目中常常会用到数据库，插入和查询是经常用到的操作，为了提高其效率，有很多地方是可以改进的，下面列举几个加以介绍。

1.         批量数据的插入

这样的操作一般是用Mysql的C接口来操作，一般逻辑是得到一条记录的内容，然后插入到数据库。这种逻辑会使得操作比较慢，就其原因有几个：

1.         插入过程中要建索引

2.         通过Sql插入数据本来就非高效操作

针对以上特点，可以采用如下逻辑：

1.         导出现有数据，如果现在表中数据可以删除就直接干掉

2.         删除表中的所有索引

3.         将新的数据采用文件的形式保存，格式为从该表中导出的数据格式

4.         将新的数据从文件中导入到数据库

5.         添加索引

该逻辑相比于以前的逻辑会快起码一个数量级。

       具体的Sql语句可参考Mysql 手册，例如：

bool yprdCreateSumTables::_dealEntt(int type)

{

    string querySql;

                     break;

                     break;

        default    : cerr<<"Type in incorrect : "<<type<<endl;

                     return false;

    }

 

    MYSQL_RES *result = NULL;

    if(DB::sendQuery(_dbinfo, querySql, &result) < 0)

    {

        cerr<<"SQL ERROR: "<<querySql<<endl;

        return false;

    }

 

    if(result)

    {

        MYSQL_ROW row = mysql_fetch_row(result);

        while(row)

        {

            string pn1 = _getNameById(atoi(row[0]), PERSON);

            string pn2 = _getNameById(atoi(row[1]), PERSON);

            string en = _getNameById(atoi(row[4]), type);

            string url1,sent1,url2,sent2;

            if(_getCentralSentInfo(pn1, en, atoi(row[2]), type, url1, sent1)

            && _getCentralSentInfo(pn2, en, atoi(row[3]), type, url2, sent2))

            {

                char insertData[CHAR_LENGTH];

                if(sent1 == sent2)

                    sprintf(insertData, "%s/t%d/t%s/t%d/t%s/t%f/t%s|%s",pn1.c_str(), atoi(row[0]), pn2.c_str(), atoi(row[1]),

 en.c_str(), atof(row[5]), url1.c_str(), sent1.c_str());

                else

                    sprintf(insertData, "%s/t%d/t%s/t%d/t%s/t%f/t%s|%s|%s|%s",pn1.c_str(), atoi(row[0]), pn2.c_str(), atoi(ro

w[1]), en.c_str(), atof(row[5]), url1.c_str(), sent1.c_str(), url2.c_str(), sent2.c_str());

                _insertDataInfoFile(insertData, type);

            }

            row = mysql_fetch_row(result);

        }

        mysql_free_result(result);

    }

 

    if(!_deleteData(type) || !_deleteIndex(type))

        return false;

 

    if(!_loadDataInfoMySql(type))

        return false;

 

    return _addIndex(type);

}

 

2.         组合查询

查询是比较关键的一个操作，为了提高查询速度，可以从如下几个方面进行考虑：

1.         索引

2.         查询逻辑组合

下图是pnpRelaSum的表结构：

+----------------+------------------+------+-----+---------+-------+

| Field          | Type             | Null | Key | Default | Extra |

+----------------+------------------+------+-----+---------+-------+

| entity1Id      | int(10) unsigned |      |     | 0       |       |

| entity2Id      | int(10) unsigned |      |     | 0       |       |

| sentenceAndUrl | text             |      |     |         |       |

| entity1        | varchar(50)      |      | MUL |         |       |

| entity2        | varchar(50)      |      | MUL |         |       |

| rela_name      | varchar(50)      |      | MUL |         |       |

| score          | float            |      |     | 0       |       |

| appear         | int(11)          |      |     | 1       |       |

+----------------+------------------+------+-----+---------+-------+

索引情况为：

 KEY `entity1` (`entity1`,`entity2`),

 KEY `entity1_0` (`entity1`),

 KEY `entity2_0` (`entity2`),

 KEY `rela_name` (`rela_name`)

 

如果我们执行查询“select * from pnpRelaSum where entity1=’刘德华’ or entity2=’刘德华’”，Mysql执行的查询逻辑为

mysql> explain select * from pnpRelaSum where entity1='刘德华' or entity2='刘德华';

+----+-------------+------------+------+-----------------------------+------+---------+------+--------+-------------+

| id | select_type | table      | type | possible_keys               | key | key_len | ref | rows   | Extra       |

+----+-------------+------------+------+-----------------------------+------+---------+------+--------+-------------+

| 1 | SIMPLE      | pnpRelaSum | ALL | entity1,entity1_0,entity2_0 | NULL |    NULL | NULL | 153576 | Using where |

+----+-------------+------------+------+-----------------------------+------+---------+------+--------+-------------+

可见，虽然我们为“entity1”和“entity2”均建立了索引，但是，该查询语句并没有用，也就是说“or”逻辑不会使用索引。

我们现在变换一下查询“(select entity1,entity2,score from pnpRelaSum where entity1=’刘德华’) union (select entity1,entity2,score from pnpRelaSum where entity2=’刘德华’)”

+----+--------------+------------+------+-------------------+-----------+---------+-------+------+-------------+

| id | select_type | table      | type | possible_keys     | key       | key_len | ref   | rows | Extra |

+----+--------------+------------+------+-------------------+-----------+---------+-------+------+-------------+

| 1 | PRIMARY      | pnpRelaSum | ref | entity1,entity1_0 | entity1   |      50 | const | 192 | Using where |

| 2 | UNION        | pnpRelaSum | ref | entity2_0         | entity2_0 |      50 | const |   26 | Using where |

|NULL | UNION RESULT | <union1,2> | ALL | NULL              | NULL      |    NULL | NULL | NULL | |

+----+--------------+------------+------+-------------------+-----------+---------+-------+------+-------------+

这下索引都用上了，因此，用union代替or是一个不错的选择。对于and，建立索引是很有效果的。

对了，忘记说如何查看我们的Sql执行情况了，就是关键词“explain”，具体的我就不说了网上多的是。