環境

• MySQL 5.7

インデックスなき <derived2>

MySQLで遅いクエリがあり、EXPLAIN してみると、

table	type	key
<derived2>	ALL	NULL

と表示され、インデックスがあたっていないテーブルがありました。

そのテーブルの行数が多かったので、結果としてクエリ速度が遅くなっていました。

原因の把握

<derived2> とは

上の <derived2> は、FROM句のサブクエリで生成された派生テーブル (derived table) です。

下の例だと、t1 が派生テーブルになります。

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

インデックスがあたらない原因

サブクエリ内で GROUP BY が用いられた場合、生成される派生テーブルにはインデックがあたらないようです。

The subquery for the derived table t1 can not be merged because it has a GROUP BY clause. Hence, MySQL 5.7 will materialize the result of this subquery, scan the resulting temporary table, and do primary-keys looks-up directly on the orders table.

「MySQL 5.7: Improved Performance of Queries with Derived Tables - MySQL Server Blog」より引用

つまり次のような場合、 derived_t1 にはインデックスはあたらない(はず)です。

SELECT price_total FROM (
  SELECT SUM(price) price_total FROM t1 
  INNER JOIN t2 ON t2.t1_id = t1.id
  GROUP BY t1.id
) AS derived_t1;

解決方法: INNER JOIN 句で GROUP BY

「サブクエリ + GROUP BY」が問題なので、 GROUP BY を INNER JOIN 句に移動させ、サブクエリをなくします。

例えば、上と同じ例を使うと、次のようなSQL文になります。

SELECT SUM(price) price_total FROM t1 
INNER JOIN (
  SELECT price, t1_id FROM t2
  GROUP BY t1_id
) t2 ON t2.t1_id = t1.id

これでインデックスなき派生テーブルはなくなり、クエリ速度が改善されました。

まとめ

インデックスなき派生テーブルをなくすには、サブクエリはなくし、GROUP BY を INNER JOIN 句に寄せることが必要です。

References

• Derived Tables in MySQL 5.7 - MySQL Server Blog
• 8.2.2.4 Optimizing Derived Tables and View References with Merging or Materialization - MySQL
• MySQL 5.7: Improved Performance of Queries with Derived Tables - MySQL Server Blog
• Query Optimization - Type: ALL, Key = NULL - StackOverflow

エアコンをラズパイから操作できるようになったので、室温と湿度をから自動的にエアコンをつけたいと思った。

温湿度は前の記事を参考にBME280で取得する。
ついでにラズパイのCPU温度も取得してグラフにする。

まずは完成したグラフがこちら

コード

window.onload = function() {
  view();
  let sl = document.querySelector("select.date")
  sl.value = today();
}

function view(date) {
  if (!date) date = today();
  let room_temp = [];
  let room_hum = [];
  let cpu = [];
  let labels = []

  fetch('http://192.168.3.2/temperature/getData.php?date=' + date)
    .then(function(response) {
      return response.text();
    })
    .then(function(myJson) {
      // console.log(myJson)
      myJson = JSON.parse(myJson);
      // console.log(myJson)
      myJson.forEach((e) => {
        room_temp.push(parseFloat(e.room_temp));
        room_hum.push(parseFloat(e.room_hum));
        cpu.push(parseFloat(e.cpu));
        labels.push(getDatetime(new Date(Number(e.time * 1000))))
      })
      graf(labels, room_temp, room_hum, cpu, date)
    });
}

function change() {
  let sl = document.querySelector("select.date")
  view(sl.value);
}

function getDatetime(now) {
  let Hour = ("0" + now.getHours()).slice(-2);
  let Min = ("0" + now.getMinutes()).slice(-2);
  let Sec = ("0" + now.getSeconds()).slice(-2);
  return Hour + ":" + Min;
}

function today(date) {
  let now = new Date();
  let Y = now.getFullYear();
  let M = ("0" + (Number(now.getMonth()) + 1)).slice(-2);
  let D = ("0" + now.getDate()).slice(-2);
  return date = Y + '_' + M + '_' + D;
}

function graf(labels, room_temp, room_hum, cpu, date) {

  let ctx = document.getElementById('myChart').getContext('2d');

  let myChart = new Chart(ctx, {
    type: "line",
    data: {
      labels: labels,
      datasets: [{
        label: "室温",
        data: room_temp,
        borderColor: "rgb(255, 99, 132)",
        backgroundColor: "rgba(255, 99, 132, 0)"
      }, {
        label: "湿度",
        data: room_hum,
        borderColor: "rgb(54, 162, 235)",
        backgroundColor: "rgba(54, 162, 235, 0)"
      }, {
        label: "CPU",
        data: cpu,
        borderColor: "rgb(120, 120, 120)",
        backgroundColor: "rgba(54, 162, 235, 0)"
      }]
    },
    options: {
      tooltips: {
        callbacks: {}
      },
      title: {
        display: true,
        text: '温湿度とラズパイ温度 ' + date
      },
      elements: {
        line: {
          tension: 0
        }
      },
      scales: {
        xAxes: [{
          type: 'time',
          time: {
            parser: "mm:ss",
            unit: 'minute',
            unitStepSize: 1,
            displayFormats: {
              'minute': 'mm',
            },

          }
        }],
        yAxes: [{
          ticks: {
            beginAtZero: true
          },

        }]
      }
    }
  });
}

解説

DB

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| room_temp | float   | YES  |     | NULL    |                |
| room_hum  | float   | YES  |     | NULL    |                |
| cpu       | float   | YES  |     | NULL    |                |
| time      | int(11) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

timeはUnixタイムスタンプ(秒)

javascriptについて

DBからデータを取ってくる関数がこちら。

function view(date) {
  if (!date) date = today();
  let room_temp = [];
  let room_hum = [];
  let cpu = [];
  let labels = []

  fetch('http://192.168.3.2/temperature/getData.php?date=' + date)
    .then(function(response) {
      return response.text();
    })
    .then(function(myJson) {
      // console.log(myJson)
      myJson = JSON.parse(myJson);
      // console.log(myJson)
      myJson.forEach((e) => {
        room_temp.push(parseFloat(e.room_temp));
        room_hum.push(parseFloat(e.room_hum));
        cpu.push(parseFloat(e.cpu));
        labels.push(getDatetime(new Date(Number(e.time * 1000))))
      })
      graf(labels, room_temp, room_hum, cpu, date)
    });
}

撮ってきたデータをオブジェクトに格納するのだが、chart.jsが処理できるデータにする必要があるのでここでは以下のような操作をしている。

• 温度、湿度それぞれの配列へデータを入れていくがその際、DBからの値を文字列から数字に変換して格納している。
• X軸として配列labelsへ自覚情報を渡しているが、DBに登録しているUnixタイムスタンプは秒なのでミリ秒に直している。

そうしてできたデータをグラフを表示する関数grafへ渡している。

現時点でのMySQLバージョンを確認

$ mysql --version

旧バージョン（5.5）パッケージを削除

$ sudo yum -y remove mysql-config mysql55-server mysql55-libs mysql55

新バージョン（5.7）パッケージをインストール

$ sudo yum -y install mysql57-server mysql57

日本語文字化け対策

$ sed -e "/utf8/d" -e "/client/d" -e "/^\[mysqld_safe\]$/i character-set-server=utf8\n\n[client]\ndefault-character-set=utf8" /etc/my.cnf |sudo tee /etc/my.cnf

MySQLの起動

$ sudo service mysqld start

設定を読み込み直すために再起動

$ sudo service mysqld restart

MySQLのバージョンを確認

$ mysql --version

バージョンが5.7になっていれば成功です。

MySQLコンソールに接続して動作確認

$ mysql -u root

データベースに接続してみて問題がないか確認します。

MacでMysql使いたいけどうまくログインができない

インストールしてmysql -urootしても
エラー起きるううう！！！！何でだあぁぁあぁぁぁぁぁぁぁ（z会）

となりませんか？　私はなりました。

MACでのデータベース作成について

何はともあれインストールしてない人はインストールしましょう。
初めてのインストールの場合は以下の　--skip-grant-tables のオプションをつけて起動してください。

zsh.コマンド

brew install mysql
mysql.server start --skip-grant-tables

これで上手くいくはずです。

ERRORが起きる場合

もし、既にインストールされていた場合は上記のコマンドを打つと、下記のように「既に存在してるんじゃバカたれ」と怒られてしまいます

---

zsh.コマンド

2020-07-01T13:47:46.6NZ mysqld_safe A mysqld process already exists

と出た場合は

brew services stop mysql
mysql.server start --skip-grant-tables

を行ってください。　もしダメなら全て消して大丈夫な場合はbrew uninstall mysql　を行なってから一番上のものを試してください

このようなことに陥る理由としてはおそらく

mysql.server start --skip-grant-tables

をする前に

mysql.server start

を行なっている可能性があります。

最後に

データベース使いたいと思っても何らかのエラーが出てしまって困りますよね（経験談）