MySQL index 索引

index 和 key 在 MySQL 裡面都是指索引

Which columns should be indexed?

唯一性太差的欄位不適合 "單獨" 建立 index
應該盡量選那些能夠「最大地」區分資料的欄位(id 就是一個最好的例子)
也就是說用這個欄位當 WHERE 條件去跑
出來的資料筆數越少則「唯一性」越高

ref:
http://talentluke.iteye.com/blog/1843868

ADD INDEX

建議使用 pt-online-schema-change

# 建立索引
mysql> ALTER TABLE writing_article ADD INDEX writing_article_last_modified (last_modified);
# or
mysql> CREATE INDEX writing_article_last_modified ON writing_article (last_modified);

time pt-online-schema-change \
--user=root --ask-pass --host=127.0.0.1 \
--print \
--execute \
--alter "ADD INDEX relationships_relationship_0e7efed3 (from_user_id), ADD INDEX relationships_relationship_bc172800 (to_user_id)" \
D=streetvoice,t=relationships_relationship

# 24 cores, 8G RAM
real 21m24.237s
user 0m2.532s
sys 0m0.392s

# 列出索引,Key_name 就是索引的名字
mysql> SHOW INDEX FROM relationships_relationship;
mysql> SHOW KEYS FROM relationships_relationship;
mysql> SHOW CREATE TABLE relationships_relationship;

# 刪除索引
mysql> DROP INDEX relationships_relationship_0e7efed3 ON relationships_relationship;
mysql> DROP INDEX relationships_relationship_bc172800 ON relationships_relationship;
mysql> DROP INDEX writing_article_953cd7bc ON writing_article;
mysql> DROP INDEX writing_article_last_modified ON writing_article;

ref:
https://dev.mysql.com/doc/refman/5.5/en/show-index.html
http://blog.xuite.net/hsiung03/blog/64217097
http://stackoverflow.com/questions/5213339/how-to-see-indexes-for-a-database-or-table

ForeignKey creates an index automatically in MySQL

所以你其實不需要特地幫 models.ForeignKey(User) 欄位加上 db_index=True

$ ./manage.py sqlindexes relationships

ref:
http://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically

EXPLAIN

注意結尾的 \Q 是指 output 的格式使用條列式,而不是表格

mysql> EXPLAIN SELECT COUNT(*) FROM `relationships_relationship` WHERE `relationships_relationship`.`to_user_id` = 910188;
mysql> EXPLAIN SELECT COUNT(*) FROM `relationships_relationship` WHERE `relationships_relationship`.`to_user_id` = 841766;
mysql> EXPLAIN SELECT COUNT(*) FROM `relationships_relationship` WHERE `relationships_relationship`.`from_user_id` = 841766;

mysql> EXPLAIN SELECT COUNT(*) FROM `relationships_relationship` WHERE `relationships_relationship`.`to_user_id` = 841766\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: relationships_relationship
         type: index
possible_keys: relationships_relationship_to_user_id
          key: relationships_relationship_to_user_id
      key_len: 4
          ref: const
         rows: 20
        Extra: Using index
1 row in set (0.00 sec)

The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table.
The key column indicates the key (index) that MySQL actually decided to use.
The rows column indicates the number of rows MySQL believes it must examine to execute the query.

type: 優 > 劣
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

rows 的值越小越好,表示 MySQL 在越小的資料集中查找資料

Extra: Using index 表示使用了索引,但是仍舊有可能是使用了錯誤的索引

ref:
https://blog.hinablue.me/entry/mysql-note-mysql-query-explain-optimization/ << 值得一讀
http://baike.baidu.com/view/9416916.htm

Query Profiler

mysql> SET profiling=1;
mysql> DO YOUR QUERY;
mysql> SHOW profiles;
mysql> SHOW profile cpu, block io for query 6;

The Slow Query Log

[mysqld]
...
slow_query_log
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 10
log_slow_verbosity = query_plan
log_queries_not_using_indexes
...
$ sudo su root
$ pt-query-digest /var/log/mysql/mariadb-slow.log

ref:
http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html