mysqlでインデックスを張っていないカラムと悲観的ロック

Question

mysqlでインデックスを張っていないカラムを条件として排他制御すると、関係ないすべての行に対してロックがかかってしまいますが、なぜインデックスを張らないとmysqlはロックの範囲を限定できないのですか？

Answer 1

InnoDBを使っているのかと思いますが、InnoDBのレコードロックはインデックスに対してロックをかける仕組みになっています。インデックスがない場合はインデックスにロックがかけられませんので、必然的にテーブルロックになってしまいます。

http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html

Answer 2

テーブルのレコード件数が少なければ全件走査してもさほどパフォーマンスへの影響少ないかもしれませんが、ある程度の件数があるテーブルに対しては、どんなSQLがどんな頻度で実行されているかで優先順を付けて、そのSQLのWHERE句で指定されている列で索引を作るのが基本です。お客様によって違いはありますが、1つのテーブルに対して作られる索引は3つ前後でしょうか？

索引を追加することで問合せのパフォーマンスが改善される分索引の更新の為に負荷が増えすが、索引がないためにテーブルロックが避けられるなどのメリット・デメリットを天秤にかけて索引を追加するか判断されては？

昨年、Oracleであるテーブルへのinsert, update, deleteが遅いから相談されて調べたら、そのテーブルに20個以上索引が付いていたので、索引が使われているか調べる方法を教えて、索引を整理するようにアドバイスしました。

Comments

[tixure55]

なるほど、やはりインデックスはカーディナリティや案件の性質によって適切に張るべきですよね。とはいうものの、私の場合、主キーとかにしか張ってないですけど（笑）

私の理解では、インデックスを張ってないテーブルだと全部テーブルロックになるという認識ですが、それで合ってますでしょうか？