/**
* @param string $table
* @param array $columns
* @param CPerfQuery $q
* @return boolean
*/
public static function GatherExpressStat($table, $columns, $q)
{
$arColumns = explode(",", $columns);
if (count($arColumns) != 1) {
return false;
}
$column = trim($arColumns[0], "`");
$value = trim($q->find_value($table, $arColumns[0]), "'");
if ($value == "") {
return false;
}
$tab = new CPerfomanceTable();
$tab->Init($table);
if ($tab->IsExists()) {
$arTableColumns = CPerfQueryStat::GetTableColumns($table);
if (!array_key_exists($column, $arTableColumns)) {
return false;
}
//May be it is worth to ban
if ($arTableColumns[$column]["Type"] === "char(1)") {
if (is_array(CPerfQueryStat::_get_stat($table, $arColumns[0]))) {
return true;
}
if (CPerfQueryStat::_gather_stat($table, $arColumns[0], $value, 10 * 1024 * 1024)) {
return true;
}
}
return false;
} else {
return false;
}
}
function parse($sql)
{
$this->sql = preg_replace("/([()=])/", " \\1 ", $sql);
$this->sql = CPerfQuery::removeSpaces($this->sql);
$match = array();
if (preg_match("/^(select) /i", $this->sql, $match)) {
$this->type = strtolower($match[1]);
} else {
$this->type = "unknown";
}
if ($this->type == "select") {
//0 TODO replace literals with placeholders
//1 remove subqueries from sql
if (!$this->parse_subqueries()) {
return false;
}
//2 parse from
$this->from = new CPerfQueryFrom();
if (!$this->from->parse($this->sql)) {
return false;
}
$tables_regex = "(?:" . implode("|", $this->from->getTableAliases()) . ")";
$this->where = new CPerfQueryWhere($tables_regex);
if (preg_match("/ where (.+?)(\$| group | having | order )/i", $this->sql, $match)) {
$this->where->parse($match[1]);
}
return true;
} else {
return false;
}
}
$sql = htmlspecialcharsEx($sql);
$sql = preg_replace("/(" . preg_quote($arSuggest["TABLE_NAME"]) . "\\s+(?i:as\\s+)*" . $arSuggest["TABLE_ALIAS"] . ")\\s+/", "<b>\\1</b> ", $sql);
foreach (explode(",", $arSuggest["COLUMN_NAMES"]) as $column_name) {
$sql = str_replace($arSuggest["TABLE_ALIAS"] . "." . $column_name, "<b>" . $arSuggest["TABLE_ALIAS"] . "." . $column_name . "</b>", $sql);
}
$arSuggest["FORMATTED_SQL_TEXT"] = $sql;
$arColumns = explode(",", $arSuggest["COLUMN_NAMES"]);
$arTableStat = CPerfQueryStat::GatherTableStat($arSuggest["TABLE_NAME"]);
$arIndexes = CPerfomanceTable::GetIndexes($arSuggest["TABLE_NAME"]);
$arQueries = array();
$rsQueries = CPerfomanceSQL::GetList(array("ID", "SQL_TEXT"), array("=SUGGEST_ID" => $ID), array("ID" => "ASC"), false, array("nTopCount" => 10));
while ($arQuery = $rsQueries->Fetch()) {
$arQuery["STAT"] = array();
$arQuery["WHERE"] = array();
$arQuery["JOIN"] = array();
$q = new CPerfQuery();
if ($q->parse($q->transform2select($arQuery["SQL_TEXT"]))) {
foreach ($arColumns as $column_name) {
$arQuery["WHERE"][$column_name] = $q->find_value($arSuggest["TABLE_NAME"], $column_name);
if ($arQuery["WHERE"][$column_name] == "") {
$arQuery["JOIN"][$column_name] = $q->find_join($arSuggest["TABLE_NAME"], $column_name);
} else {
$arQuery["JOIN"][$column_name] = "";
}
}
}
$sql = CPerfomanceSQL::Format($arQuery["SQL_TEXT"]);
$sql = htmlspecialcharsEx($sql);
$sql = preg_replace("/(" . preg_quote($arSuggest["TABLE_NAME"]) . "\\s+(?i:as\\s+)*" . $arSuggest["TABLE_ALIAS"] . ")\\s+/", "<b>\\1</b> ", $sql);
foreach (explode(",", $arSuggest["COLUMN_NAMES"]) as $column_name) {
$sql = str_replace($arSuggest["TABLE_ALIAS"] . "." . $column_name, "<b>" . $arSuggest["TABLE_ALIAS"] . "." . $column_name . "</b>", $sql);
$sql_cache = array();
while (time() < $etime) {
$rsSQL = CPerfomanceSQL::GetList(array("ID", "SQL_TEXT", "QUERY_TIME"), array(">ID" => $last_id), array("ID" => "ASC"), false, array("nTopCount" => 100));
while ($arSQL = $rsSQL->Fetch()) {
$_SESSION["queries"]++;
$go = true;
$sql_md5 = md5(CPerfQuery::remove_literals($arSQL["SQL_TEXT"]));
//Check if did it already on previous steps
if (!array_key_exists($sql_md5, $sql_cache)) {
$sql_cache[$sql_md5] = true;
$rsInd = CPerfomanceIndexSuggest::GetList(array("SQL_MD5"), array("=SQL_MD5" => $sql_md5), array());
if ($rsInd->Fetch()) {
CPerfomanceIndexSuggest::UpdateStat($sql_md5, 1, $arSQL["QUERY_TIME"], $arSQL["ID"]);
} else {
$arMissedKeys = array();
$q = new CPerfQuery();
$strSQL = $q->transform2select($arSQL["SQL_TEXT"]);
if ($strSQL && $q->parse($strSQL)) {
$i = 0;
$arExplain = array();
$rsData = $DB->Query("explain " . $strSQL, true);
if (is_object($rsData)) {
while ($arRes = $rsData->Fetch()) {
$i++;
$arExplain[] = $arRes;
if ($arRes["type"] === "ALL" && strlen($arRes["key"]) == 0 && is_object($q) && ($i > 1 || $q->has_where($arRes["table"]))) {
$missed_keys = $q->suggest_index($arRes["table"]);
if ($missed_keys) {
$arMissedKeys = array_merge($arMissedKeys, $missed_keys);
} elseif ($q->has_where()) {
//Check if it is possible to find missed keys on joined tables
$ID = IntVal($ID);
$sTableID = "tbl_perfmon_explain";
$lAdmin = new CAdminList($sTableID);
if ($DBType == "mysql") {
$arHeader = array(array("id" => "select_type", "content" => GetMessage("PERFMON_EXPLAIN_F_SELECT_TYPE"), "align" => "left", "default" => true), array("id" => "table", "content" => GetMessage("PERFMON_EXPLAIN_F_TABLE"), "align" => "left", "default" => true), array("id" => "type", "content" => GetMessage("PERFMON_EXPLAIN_F_TYPE"), "align" => "left", "default" => true), array("id" => "possible_keys", "content" => GetMessage("PERFMON_EXPLAIN_F_POSSIBLE_KEYS"), "align" => "left", "default" => true), array("id" => "key", "content" => GetMessage("PERFMON_EXPLAIN_F_KEY"), "align" => "left", "default" => true), array("id" => "key_len", "content" => GetMessage("PERFMON_EXPLAIN_F_KEY_LEN"), "align" => "right", "default" => true), array("id" => "ref", "content" => GetMessage("PERFMON_EXPLAIN_F_REF"), "align" => "left", "default" => true), array("id" => "rows", "content" => GetMessage("PERFMON_EXPLAIN_F_ROWS"), "align" => "right", "default" => true), array("id" => "Extra", "content" => GetMessage("PERFMON_EXPLAIN_F_EXTRA"), "align" => "left", "default" => true));
} elseif ($DBType == "oracle") {
$arHeader = array(array("id" => "OPERATION", "content" => GetMessage("PERFMON_EXPLAIN_F_OPERATION"), "align" => "left", "default" => true), array("id" => "OBJECT_NAME", "content" => GetMessage("PERFMON_EXPLAIN_F_OBJECT_NAME"), "align" => "left", "default" => true), array("id" => "OBJECT_TYPE", "content" => GetMessage("PERFMON_EXPLAIN_F_OBJECT_TYPE"), "align" => "left", "default" => true), array("id" => "OPTIONS", "content" => GetMessage("PERFMON_EXPLAIN_F_EXTRA"), "align" => "left", "default" => true), array("id" => "CARDINALITY", "content" => GetMessage("PERFMON_EXPLAIN_F_ROWS"), "align" => "right", "default" => true), array("id" => "COST", "content" => GetMessage("PERFMON_EXPLAIN_F_COST"), "align" => "right", "default" => true));
} else {
$arHeader = array();
}
$lAdmin->AddHeaders($arHeader);
$arPlan = false;
$cData = new CPerfomanceSQL();
$rsSQL = $cData->GetList(array("ID", "SQL_TEXT"), array("=ID" => $ID), array(), false);
$arSQL = $rsSQL->Fetch();
$strSQL = CPerfQuery::transform2select($arSQL["SQL_TEXT"]);
if ($strSQL) {
if ($DBType == "mysql") {
$rsData = $DB->Query("explain " . $strSQL, true);
} elseif ($DBType == "oracle") {
$rsData = $DB->Query("explain plan for " . $strSQL, true);
if ($rsData) {
$rsData = $DB->Query("select * from plan_table order by ID");
$arPlan = $rsData->Fetch();
}
} else {
$rsData = false;
}
} else {
$rsData = false;
}
$sql_md5 = md5(CPerfQuery::remove_literals($arSQL["SQL_TEXT"]));
//Check if did it already on previous steps
if (!array_key_exists($sql_md5, $sql_cache))
{
$sql_cache[$sql_md5] = true;
$rsInd = CPerfomanceIndexSuggest::GetList(array("SQL_MD5"), array("=SQL_MD5" => $sql_md5), array());
if ($rsInd->Fetch())
{
CPerfomanceIndexSuggest::UpdateStat($sql_md5, 1, $arSQL["QUERY_TIME"], $arSQL["ID"]);
}
else
{
$arMissedKeys = array();
$q = new CPerfQuery;
$strSQL = $q->transform2select($arSQL["SQL_TEXT"]);
if ($strSQL && $q->parse($strSQL))
{
$i = 0;
$arExplain = array();
$rsData = $DB->Query("explain ".$strSQL, true);
if (is_object($rsData))
{
while ($arRes = $rsData->Fetch())
{
$i++;
$arExplain[] = $arRes;
if (
$arRes["type"] === "ALL"
&& strlen($arRes["key"]) == 0