SQL性能分析
@access protected
@param string $sql
@return array
354724
protected function getExplain($sql)
    {
        $pdo = $this->linkID->query("EXPLAIN " . $sql);
        $result = $pdo->fetch(PDO::FETCH_ASSOC);
        $result = array_change_key_case($result);
        return $result;
    }
1095061
public function query($sql)
    {
        return $this->getDisconnectRetry()->run(function () use ($sql) {
            $statement = $this->prepare($sql);
            $statement->execute();

            return $statement;
        });
    }
872440
protected function query_row ( $sql )
	{
		$sql = $this->sql_printf(func_get_args());
		if (!($res = mysql_query($sql, $this->conn)))
		{
			$this->sql_errcheck($sql);
		}
		if ($row = mysql_fetch_array($res))
		{
			$rs = $row;
		}
		else
		{
			$rs = false;
		}
		mysql_free_result($res);
		return $rs;
	}
130513
public final function query($sql)
    {
        if ($this->debug)
        {
            echo "$sql<br />\n<hr />";
        }
        $res = parent::query($sql) or \Swoole\Error::info(
            "SQL Error",
            implode(", ", $this->errorInfo()) . "<hr />$sql"
        );
        $this->lastStatement = $res;
        //非查询语句直接返回结果
        if ($sql[0] !== 's')
        {
            return !empty($res);
        }
        return $res;
    }
380321
protected function findOrdering($sql)
	{
		if(!preg_match('/ORDER BY/i', $sql))
			return array();
		$matches=array();
		$ordering=array();
		preg_match_all('/(ORDER BY)[\s"\[](.*)(ASC|DESC)?(?:[\s"\[]|$|COMPUTE|FOR)/i', $sql, $matches);
		if(count($matches)>1 && count($matches[2]) > 0)
		{
			$parts = explode(',', $matches[2][0]);
			foreach($parts as $part)
			{
				$subs=array();
				if(preg_match_all('/(.*)[\s"\]](ASC|DESC)$/i', trim($part), $subs))
				{
					if(count($subs) > 1 && count($subs[2]) > 0)
					{
						$name='';
						foreach(explode('.', $subs[1][0]) as $p)
						{
							if($name!=='')
								$name.='.';
							$name.='[' . trim($p, '[]') . ']';
						}
						$ordering[$name] = $subs[2][0];
					}
					//else what?
				}
				else
					$ordering[trim($part)] = 'ASC';
			}
		}

		// replacing column names with their alias names
		foreach($ordering as $name => $direction)
		{
			$matches = array();
			$pattern = '/\s+'.str_replace(array('[',']'), array('\[','\]'), $name).'\s+AS\s+(\[[^\]]+\])/i';
			preg_match($pattern, $sql, $matches);
			if(isset($matches[1]))
			{
				$ordering[$matches[1]] = $ordering[$name];
				unset($ordering[$name]);
			}
		}

		return $ordering;
	}
1097597
public static function splitSql($sql)
	{
		$start   = 0;
		$length  = strlen($sql);
		$queries = [];

		foreach (Str::findLiteral(';', $sql) as $queryEndPosition)
		{
			$queries[] = trim(substr($sql, $start, $queryEndPosition - $start + 1));
			$start     = $queryEndPosition + 1;
		}

		// Grab the last query in case it doesn't end with a ';'
		if ($end = trim(substr($sql, $start)))
		{
			$queries[] = $end;
		}

		return $queries;
	}
581791
protected function query($sql) {
		$this->sqlCnt++;
		$res = $this->db->query($sql);
		if (!$res) {
			$this->dbError = $this->db->error;
		}
		return $res;
	}
383003
public static function fetchAll($sql) {
		$return = array();
		$results = \Database::getInstance()->query($sql);
		while ($result = $results->fetchAssoc()) {
			$return[$result['id']] = $result;
		}
		return $return;
	}
500695
public function fetch_arrays($sql)
    {
        $result = $this->db->query($sql);
        $array  = null;
        while (true == ($row = $result->fetchArray(SQLITE3_ASSOC))) {
            $array[] = $row;
        }
        $GLOBALS['__$DB_QUERY_COUNT']++;

        $result->finalize();
        return $array;
    }
826061
public function fetch_array($sql)
    {
        $rs   = $this->query($sql);
        $data = mysql_fetch_assoc($rs);
        if (empty($data)) {
            $data = [];
        }

        mysql_free_result($rs);
        return $data;
    }
437691
public function read($sql)
    {
        $sql = (string)$sql;

        $conn = $this->getConnection();
        $this->Benchmark->start('sql-query');

        try {

//            if(!substr($sql, 0, 4) == 'SHOW') {
//
//                $explain = $conn->query("EXPLAIN ".$sql);
//
//                $explainresults = $explain->fetchAll(PDO::FETCH_ASSOC);
//            }

//            $start =  microtime(TRUE);

            $result = $conn->query($sql);

//            $end =  microtime(TRUE);
//            $totalms = ($end-$start)*1000;

//            if($totalms > 1 && !empty($explainresults)) {
//
//                foreach($explainresults as $qline)
//                {
//
//                    if(strtolower($qline['select_type']) != 'union result' && strtolower($qline['type']) == 'all')
//                    {
//                        // table scan
//                        $pr = ArrayUtils::arrayToStr($explainresults);
//                        error_log("[SQL]  BAD QUERY: TABLE SCAN {$qline['key']}\n"."SQL:\n\n{$sql}\n\nEXPLAIN:\n\n{$pr}\n\nTOTAL TIME:{$totalms}ms\n");
//
//                    }else if($qline['rows'] > 5000) {
//
//                        // poor index
//                        $pr = ArrayUtils::arrayToStr($explainresults);
//                        error_log("[SQL]  BAD QUERY: SCANNED OVER 5000 ROWS {$qline['key']}\n"."SQL:\n\n{$sql}\n\nEXPLAIN:\n\n{$pr}\n\nTOTAL TIME:{$totalms}ms\n");
//
//                    }
//                }
//
//            }
//
//            if($totalms > 1000) {
//
//                if(!empty($explainresults)) {
//                    $pr = ArrayUtils::arrayToStr($explainresults);
//                    $email = "SQL:\n\n{$sql}\n\nEXPLAIN:\n\n{$pr}\n\nTOTAL TIME:{$totalms}ms\n";
//                } else {
//                    $email = "SQL:\n\n{$sql}\n\nTOTAL TIME:{$totalms}ms\n";
//                }
//                error_log("[SQL]  BAD QUERY: TOOK OVER 1 SECOND\n".$email);
//
//            }




        } catch(PDOException $pe) {
            $errorMessage = $pe->getMessage();

            $this->Logger->debug(array(
                'Conn'                => $this->connDebugString,
                'SQL'                 => $sql,
                'Execution Time (ms)' => $this->Benchmark->end('sql-query'),
                'Error'               => $errorMessage,
            ));


            throw new SQLException($sql, $errorMessage, intVal($pe->getCode()));
        }

        $this->Logger->debug(array(
                'Conn'                => $this->connDebugString,
                'SQL'                 => $sql,
                'Execution Time (ms)' => $this->Benchmark->end('sql-query'),
                'Rows Returned'       => $result->rowCount(),
            ));

        unset($sql);
        unset($conn);

        return $result;
    }
535366
public function raw($sql)
    {
        $query = new Query\SqlQuery();

        if ($this->pdo) {
            $query->setPDO($this->pdo);
        }

        return $query->raw($sql);
    }
829512
function fetch_arrays($sql)
    {
        $rs   = $this->query($sql);
        $data = [];
        while (true == ($row = $rs->fetch_assoc())) {
            $data[] = $row;
        }
        $rs->free();
        return $data;
    }
845759
protected function getColumnsSql(array $sql): array
    {
        if (empty($this->columns)) {
            $sql[] = '*';

            return $sql;
        }
        $columns = [];
        foreach ($this->columns as $key => $column) {
            if ($column instanceof Closure) {
                // Sub Select
                $query = new SelectQuery($this->db);
                $column($query);
                $column = new RawExp($query->build(false));
            }
            $columns[] = $column;
        }
        $sql[] = implode(',', $this->quoter->quoteNames($columns));

        return $sql;
    }
477613
protected function getSelectSql(array $sql): array
    {
        $sql[] = trim('SELECT ' . trim(implode(' ', [
            $this->distinct,
            $this->highPriority,
            $this->straightJoin,
            $this->resultSize,
            $this->bufferResult,
            $this->calcFoundRows,
        ])));

        return $sql;
    }
204306
protected function parseSql($sql,$parse) {
        // 分析表达式
        if(true === $parse) {
            $options =  $this->_parseOptions();
            $sql    =   $this->db->parseSql($sql,$options);
        }elseif(is_array($parse)){ // SQL预处理
            $parse  =   array_map(array($this->db,'escapeString'),$parse);
            $sql    =   vsprintf($sql,$parse);
        }else{
            $sql    =   strtr($sql,array('__TABLE__'=>$this->getTableName(),'__PREFIX__'=>$this->tablePrefix));
            $prefix =   $this->tablePrefix;
            $sql    =   preg_replace_callback("/__([A-Z0-9_-]+)__/sU", function($match) use($prefix){ return $prefix.strtolower($match[1]);}, $sql);
        }
        $this->db->setModel($this->name);
        return $sql;
    }
19712
public static function format_sql( $sql ) {

		$sql = str_replace( array( "\r\n", "\r", "\n", "\t" ), ' ', $sql );
		$sql = esc_html( $sql );
		$sql = trim( $sql );

		$regex = 'ADD|AFTER|ALTER|AND|BEGIN|COMMIT|CREATE|DELETE|DESCRIBE|DO|DROP|ELSE|END|EXCEPT|EXPLAIN|FROM|GROUP|HAVING|INNER|INSERT|INTERSECT|LEFT|LIMIT|ON|OR|ORDER|OUTER|RENAME|REPLACE|RIGHT|ROLLBACK|SELECT|SET|SHOW|START|THEN|TRUNCATE|UNION|UPDATE|USE|USING|VALUES|WHEN|WHERE|XOR';
		$sql   = preg_replace( '# (' . $regex . ') #', '<br> $1 ', $sql );

		$keywords = '\b(?:ACTION|ADD|AFTER|ALTER|AND|ASC|AS|AUTO_INCREMENT|BEGIN|BETWEEN|BIGINT|BINARY|BIT|BLOB|BOOLEAN|BOOL|BREAK|BY|CASE|COLLATE|COLUMNS?|COMMIT|CONTINUE|CREATE|DATA(?:BASES?)?|DATE(?:TIME)?|DECIMAL|DECLARE|DEC|DEFAULT|DELAYED|DELETE|DESCRIBE|DESC|DISTINCT|DOUBLE|DO|DROP|DUPLICATE|ELSE|END|ENUM|EXCEPT|EXISTS|EXPLAIN|FIELDS|FLOAT|FOREIGN|FOR|FROM|FULL|FUNCTION|GROUP|HAVING|IF|IGNORE|INDEX|INNER|INSERT|INTEGER|INTERSECT|INTERVAL|INTO|INT|IN|IS|JOIN|KEYS?|LEFT|LIKE|LIMIT|LONG(?:BLOB|TEXT)|MEDIUM(?:BLOB|INT|TEXT)|MERGE|MIDDLEINT|NOT|NO|NULLIF|ON|ORDER|OR|OUTER|PRIMARY|PROC(?:EDURE)?|REGEXP|RENAME|REPLACE|RIGHT|RLIKE|ROLLBACK|SCHEMA|SELECT|SET|SHOW|SMALLINT|START|TABLES?|TEXT(?:SIZE)?|THEN|TIME(?:STAMP)?|TINY(?:BLOB|INT|TEXT)|TRUNCATE|UNION|UNIQUE|UNSIGNED|UPDATE|USE|USING|VALUES?|VAR(?:BINARY|CHAR)|WHEN|WHERE|WHILE|XOR)\b';
		$sql      = preg_replace( '#' . $keywords . '#', '<b>$0</b>', $sql );

		return '<code>' . $sql . '</code>';

	}