pt-duplicate-key-checker

pt-duplicate-key-checker is a command-line tool to find duplicate indexes and foreign keys on MySQL tables.

ref:
https://www.percona.com/doc/percona-toolkit/2.2/pt-duplicate-key-checker.html

# check all tables
$ pt-duplicate-key-checker --user=abc --password=123 --databases=streetvoice

# check specific tables
$ pt-duplicate-key-checker --user=abc --password=123 --databases=streetvoice --tables=svapp_like
MySQL schema design for large tables

MySQL schema design for large tables

把大字段拆出來

主要是指那些 text 類型的欄位
例如 Article 的 content 內文欄位
可以拆出來變成 ArticleContent 表
因為在大部分的 query 裡你其實都不會需要 content 欄位
通常只有顯示單一 article 的時候才需要

例如 Profile 表
phone_number, address, occupation 之類的很少被用來 query 的欄位
其實也可以拆出來到另外一個表
ExtendedProfile

大表的水平拆分:置頂訊息

假設你有一個存放論壇文章的表 Post
文章分成一般用戶的「普通文章」和管理者發布的「置頂文章」
你可能會在 Post 表新增一個 boolean 欄位
區分是不是置頂文章

但是置頂文章和普通文章的數量可能差很多
而且置頂文章的 query 次數也會多很多(每一頁都要顯示)
所以另一個可行的辦法是幫置頂文章獨立開一個 table

這種方式就是所謂的「水平拆分」

不是所有的 COUNT 都需要實時

很多 count 可以透過 crontab 定時跑
然後存進某個欄位裡
前端顯示時直接去讀那個欄位的值即可

把同一個 counter 的資料存成多筆 record

這樣可以減少同時有多個 request 修改了同一個 row 所引起的 lock 的問題
Deadlock found when trying to get lock; try restarting transaction

在你的 counter table 新增一個 slot 欄位(槽)
每次寫入的時候隨機選擇(透過 slot 欄位)某一個 record 寫入
要讀取的時候就把這幾筆 SUM 起來

song_id count slot
100 2 1
100 5 2
100 3 3
100 8 4
101 1 1
101 19 2
101 3 3
101 7 4

UPDATE song_counter SET count = count + 1 WHERE song_id = 100 AND slot = 1;
UPDATE song_counter SET count = count + 1 WHERE song_id = 100 AND slot = 2;
UPDATE song_counter SET count = count + 1 WHERE song_id = 100 AND slot = 3;

缺點就是 record 會變超多
可以用 crontab 定期整理數據
把 counter 資料寫到 slot 1
然後刪掉其他的 slot

ref:
High Performance MySQL, Chapter 4.4

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