innodb结构解析工具---innodb_ruby
1.下载ruby并安装ruby:
ftp://ftp.ruby-lang.org/pub/ruby/
ftp://ftp.ruby-lang.org/pub/ruby/ruby-2.3-stable.tar.gz
tar -xvf ruby-2.3-stable.tar.gz
cd ruby-2.3.1
./configure
make
make install
2.下载rubygems：
https://rubygems.global.ssl.fastly.net/rubygems/rubygems-2.6.4.zip （到https://rubygems.org/ 首页找的rubygems下载地址）
cd rubygems-2.6.4
ruby setup.rb
3.下载并安装innodb_ruby
https://rubygems.org/gems/innodb_ruby （进入rubygems官网去找下载链接)
https://rubygems.global.ssl.fastly.net/gems/innodb_ruby-0.9.13.gem (浏览器下载）
wget https://rubygems.global.ssl.fastly.net/rubygems/rubygems-2.6.4.zip --no-check-certificate (wget下载)
gem install innodb_ruby-0.9.13.gem
[root@localhost ~]# gem -v 2.6.4
[root@localhost ~]# gem list |grep innodb
innodb_ruby (0.9.13)
4.innodb_ruby使用文档:
https://github.com/jeremycole/innodb_ruby/wiki
https://blog.jcole.us/2013/01/03/a-quick-introduction-to-innodb-ruby/
https://blog.jcole.us/2014/10/02/visualizing-the-impact-of-ordered-vs-random-index-insertion-in-innodb/
http://luodw.cc/2016/03/15/innodb03/
5.实例:
进入到mysql数据目录：/data
[root@localhost data]# innodb_space -s ibdata1 system-spaces name pages indexes (system) 4864 6 mysql/innodb_index_stats 6 1 mysql/innodb_table_stats 6 1 mysql/slave_master_info 6 1 mysql/slave_relay_log_info 6 1 mysql/slave_worker_info 6 1 test/t1 8 1 test/test1 6 1 test/test_auto_increment 6 1 test/test_char 6 1 test/test_ci 7 2 test/test_int_n 6 1 test/test_time 6 1 test/test_unsigned 6 1
查看表t1索引
[root@localhost data]# innodb_space -s ibdata1 -T test/t1 space-indexes id name root fseg used allocated fill_factor 58 PRIMARY 3 internal 1 1 100.00% 58 PRIMARY 3 leaf 3 3 100.00%
[root@localhost data]# innodb_space -s ibdata1 -T test/t1 space-indexes id name root fseg used allocated fill_factor 58 PRIMARY 3 internal 1 1 100.00% 58 PRIMARY 3 leaf 3 3 100.00% You have new mail in /var/spool/mail/root [root@localhost data]# innodb_space -s ibdata1 -T test/t1 space-lsn-age-illustrate Start Page ╭────────────────────────────────────────────────────────────────╮ 0 │███████ │ ╰────────────────────────────────────────────────────────────────╯ Legend (█ = 1 page): Min LSN <─────────────────────────────────────────> Max LSN 2059083 ███████████████████████████████████████████ 2119310
$ irb -r innodb > sys = Innodb::System.new("ibdata1") > idx = sys.index_by_name("test/t1", "PRIMARY") > rec = idx.binary_search([1])
[root@localhost data]# innodb_space -s ibdata1 -T test/test1 space-extents-illustrate Start Page ╭────────────────────────────────────────────────────────────────╮ 0 │███▁?? │ ╰────────────────────────────────────────────────────────────────╯ Legend (█ = 1 page): Page Type Pages Ratio █ System 3 50.00% █ Index 62 (test/test1.GEN_CLUST_INDEX) 1 16.67% ? Free space 2 33.33%
[root@localhost data]# innodb_space -f test/t1.ibd space-page-type-regions start end count type 0 0 1 FSP_HDR 1 1 1 IBUF_BITMAP 2 2 1 INODE 3 6 4 INDEX 7 7 1 FREE (ALLOCATED)
[root@localhost data]# innodb_space -f test/t1.ibd -p 3 page-dump #<Innodb::Page::Index:0x007f798307d970>: fil header: {:checksum=>2022539217, :offset=>3, :prev=>nil, :next=>nil, :lsn=>2119310, :type=>:INDEX, :flush_lsn=>0, :space_id=>35} page header: {:n_dir_slots=>2, :heap_top=>162, :garbage_offset=>0, :garbage_size=>0, :last_insert_offset=>154, :direction=>:right, :n_direction=>2, :n_recs=>3, :max_trx_id=>0, :level=>1, :index_id=>58, :n_heap=>5, :format=>:compact} fseg header: {:leaf=> <Innodb::Inode space=<Innodb::Space file="test/t1.ibd", page_size=16384, pages=8>, fseg=2>, :internal=> <Innodb::Inode space=<Innodb::Space file="test/t1.ibd", page_size=16384, pages=8>, fseg=1>} sizes: header 120 trailer 8 directory 4 free 16210 used 174 record 42 per record 14.00 page directory: [99, 112] system records: {:offset=>99, :header=> {:next=>126, :type=>:infimum, :heap_number=>0, :n_owned=>1, :min_rec=>false, :deleted=>false, :length=>5}, :next=>126, :data=>"infimum\x00", :length=>8} {:offset=>112, :header=> {:next=>112, :type=>:supremum, :heap_number=>1, :n_owned=>4, :min_rec=>false, :deleted=>false, :length=>5}, :next=>112, :data=>"supremum", :length=>8} garbage records: records: {:format=>:compact, :offset=>126, :header=> {:next=>140, :type=>:node_pointer, :heap_number=>2, :n_owned=>0, :min_rec=>true, :deleted=>false, :length=>5}, :next=>140} {:format=>:compact, :offset=>140, :header=> {:next=>154, :type=>:node_pointer, :heap_number=>3, :n_owned=>0, :min_rec=>false, :deleted=>false, :length=>5}, :next=>154} {:format=>:compact, :offset=>154, :header=> {:next=>112, :type=>:node_pointer, :heap_number=>4, :n_owned=>0, :min_rec=>false, :deleted=>false, :length=>5}, :next=>112}
[root@localhost data]# innodb_space -f test/t1.ibd space-index-pages-summary page index level data free records 3 58 1 42 16210 3 4 58 0 7025 9227 1 5 58 0 14050 2202 2 6 58 0 7025 9227 1 7 0 0 0 16384 0
