search

crazyjohn / crazyjohn.github.io

crazyjohn's blog
9 stars 3 forks source link

mysql innodb #17

Open crazyjohn opened 9 years ago

crazyjohn commented 9 years ago

这一篇说mysql的innodb。搞了这么久游戏，对于数据存储的db选择，几乎用的都是mysql，而对于mysql的存储引擎选的几乎都是innodb，这里对innodb的使用和学习，做一个总结。

说到innodb马上想到的是这几个词：事务，MVCC，行锁等，下面开始慢慢说。

说好的先上美图吸流量

1.mysql的存储引擎概述

  1. 看看当前mysql的存储引擎支持情况。

    mysql> show engines\G;
*************************** 1. row ***************************
 Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
 XA: NULL
 Savepoints: NULL
*************************** 2. row ***************************
 Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 3. row ***************************
 Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 4. row ***************************
 Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 5. row ***************************
 Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 6. row ***************************
 Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 7. row ***************************
 Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 8. row ***************************
 Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
 XA: NO
 Savepoints: NO
*************************** 9. row ***************************
 Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
 XA: YES
 Savepoints: YES
9 rows in set (0.00 sec)

ERROR: 
No query specified

    我们看到最后一行就是innodb，而且看介绍，它支持事务，行锁，外键，而且是默认的引擎。

    2. innodb的整体结构

2.1. Innodb engine status

通过命令：show engine innodb status来查看innodb引擎的情况。

    mysql> show engine innodb status\G;
    *************************** 1. row ***************************
      Type: InnoDB
      Name: 
    Status: 
    =====================================
    2015-08-29 18:05:28 7f9e9946c700 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 102 seconds
    -----------------
    BACKGROUND THREAD
    -----------------
    srv_master_thread loops: 42568 srv_active, 0 srv_shutdown, 1209634 srv_idle
    srv_master_thread log flush and writes: 110507
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 34549
    OS WAIT ARRAY INFO: signal count 34661
    Mutex spin waits 23601, rounds 181295, OS waits 3607
    RW-shared spins 34255, rounds 975657, OS waits 30450
    RW-excl spins 2429, rounds 35819, OS waits 390
    Spin rounds per wait: 7.68 mutex, 28.48 RW-shared, 14.75 RW-excl
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 23547971
    Purge done for trx's n:o < 23547971 undo n:o < 0 state: running but idle
    History list length 3340
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 23547969, not started
    MySQL thread id 6414, OS thread handle 0x7f9e98e54700, query id 11162062 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 0, not started
    MySQL thread id 6413, OS thread handle 0x7f9e9946c700, query id 11162063 localhost root init
    show engine innodb status
    ---TRANSACTION 23547711, not started
    MySQL thread id 6412, OS thread handle 0x7f9e994ee700, query id 11161590 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547963, not started
    MySQL thread id 6411, OS thread handle 0x7f9e9905c700, query id 11162046 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547951, not started
    MySQL thread id 6410, OS thread handle 0x7f9e991e2700, query id 11162028 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547405, not started
    MySQL thread id 6409, OS thread handle 0x7f9e99223700, query id 11161658 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547299, not started
    MySQL thread id 6407, OS thread handle 0x7f9ea0702700, query id 11161660 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547231, not started
    MySQL thread id 6400, OS thread handle 0x7f9ea2ad2700, query id 11161661 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547929, not started
    MySQL thread id 6399, OS thread handle 0x7f9e9952f700, query id 11161955 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547930, not started
    MySQL thread id 6397, OS thread handle 0x7f9ea2b54700, query id 11162033 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547719, not started
    MySQL thread id 6389, OS thread handle 0x7f9e996b5700, query id 11160775 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547751, not started
    MySQL thread id 6386, OS thread handle 0x7f9e994ad700, query id 11161045 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547863, not started
    MySQL thread id 6387, OS thread handle 0x7f9e993a9700, query id 11161611 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23546809, not started
    MySQL thread id 6388, OS thread handle 0x7f9e99327700, query id 11139936 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547783, not started
    MySQL thread id 6377, OS thread handle 0x7f9ea02a1700, query id 11161545 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23547437, not started
    MySQL thread id 6375, OS thread handle 0x7f9e99778700, query id 11161659 localhost 127.0.0.1 root cleaning up
    ---TRANSACTION 23544139, not started
    MySQL thread id 6303, OS thread handle 0x7f9e98f17700, query id 11123397 localhost 127.0.0.1 root cleaning up
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    I/O thread 1 state: waiting for completed aio requests (log thread)
    I/O thread 2 state: waiting for completed aio requests (read thread)
    I/O thread 3 state: waiting for completed aio requests (read thread)
    I/O thread 4 state: waiting for completed aio requests (read thread)
    I/O thread 5 state: waiting for completed aio requests (read thread)
    I/O thread 6 state: waiting for completed aio requests (write thread)
    I/O thread 7 state: waiting for completed aio requests (write thread)
    I/O thread 8 state: waiting for completed aio requests (write thread)
    I/O thread 9 state: waiting for completed aio requests (write thread)
    Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
     ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    2293913 OS file reads, 481164 OS file writes, 261579 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 3.42 writes/s, 1.69 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 2235, seg size 2237, 14473 merges
    merged operations:
     insert 27699, delete mark 16, delete 8
    discarded operations:
     insert 0, delete mark 0, delete 0
    Hash table size 276671, node heap has 8 buffer(s)
    0.07 hash searches/s, 0.83 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 41783384402
    Log flushed up to   41783384402
    Pages flushed up to 41783384114
    Last checkpoint at  41783384114
    0 pending log writes, 0 pending chkp writes
    159831 log i/o's done, 1.03 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 137363456; in additional pool allocated 0
    Dictionary memory allocated 1423670
    Buffer pool size   8191
    Free buffers   1024
    Database pages 7159
    Old database pages 2622
    Modified db pages  3
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 58466, not young 5809602
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 2292926, created 40339, written 289313
    0.00 reads/s, 0.00 creates/s, 2.21 writes/s
    Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 7159, unzip_LRU len: 0
    I/O sum[15]:cur[480547], unzip sum[0]:cur[0]
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    0 read views open inside InnoDB
    Main thread process no. 2165, id 140319307925248, state: sleeping
    Number of rows inserted 144625, updated 79705, deleted 39, read 13742967
    0.00 inserts/s, 0.90 updates/s, 0.00 deletes/s, 0.90 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================

下面我们说说其中的几个重要部分。

  1. Per second averages calculated from the last 102 seconds表明当前的状态信息不是及时的，而是对前102s的innodb引擎状态的一个统计。
  2. BACKGROUND THREAD部分。这部分是对innodb后台线程运行情况的一个总结。
  3. SEMAPHORES。TODO: crazyjohn信号量的介绍
  4. TRANSCATIONS。是事务部分。
  5. File IO。说的是innodb线程的读写状态。
  6. INSERT BUFFER AND ADAPTIVE HASH INDEX。插入缓存和自适应hash索引。
  7. BUFFER POOL AND MEMORY。缓存池的状态。 这是innodb缓存池的组成结构。其中log_buffer中放的是redo重做日志，然后会按照一定的频率被刷新到重做日志文件中去。

    2.2. Master Thread结构分析

    3. innodb特性概述

    • 插入缓存
    • 二次写
    • 自适应hash

      4. 文件

      5. 表

      6. 索引

  8. 使用索引的时机。我们使用索引的最好时机是在下面两个条件全满足的时候：1该列的值高度不重复，2目标的数量很小。

    7. 锁

    8. 事务

    9. 备份还原

    10. 优化

    11. 实战经验

element表的一次索引优化。

最近游戏日志发现有条查询比较耗时dbmanager.query timeout, hql: from ElementEntity where playerId = ? and invalid = 0 order by id asc, costtime: 451，耗时451毫秒，然后针对element表进行查询优化。首先看下表的索引情况：

mysql> show index from element\G;
*************************** 1. row ***************************
   Table: element
  Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
 Column_name: id
   Collation: A
 Cardinality: 135256
Sub_part: NULL
  Packed: NULL
Null: 
  Index_type: BTREE
 Comment: 
1 row in set (0.00 sec)

发现只有主键id一个索引。然后再看下explain:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: element
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 135256
        Extra: Using where

没有使用任何索引，直接全表扫描查询。下面添加一个playerId索引：ALTER TABLEelementADD INDEX playerId_index (playerId);，然后再看下explain:

+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys  | key            | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | element | ref  | playerId_index | playerId_index | 5       | const | 5564 | Using where |
+----+-------------+---------+------+----------------+----------------+---------+-------+------+-------------+

发现本次查询，使用的type为ref，也就是根据辅助索引关联的主键查找，而且会使用playerId_index这个索引。在看下同样sql的查询时间：只有30ms。

12. 踩过的坑

13. 总结