Hello Geetanjali,
On 8/26/2014 1:11 AM, geetanjali mehra wrote:
Hello to all,
I want to know whether my innodb index is fragemented. Is it possible to
know?
Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Trainer and Database Security
Specialist
Just like every other piece of data stored in an InnoDB tablespace, the
index data is stored in pages. At maximum capacity, 15KB of the 16KB
assigned to each page can consist of data. At worst, about half of a
16K page will contain data. This is because each page is one leaf in a
BTREE structure.
If you add data to a page and you would exceed that 15K limit, we would
need to split that page. That means that each page (the existing page
and the one we just created) will now have a near-equal share of the
data that was on the original page.
That empty space is reused as much as possible to avoid another page
split. If removing data from a table makes it possible to combine two
adjacent leaves in the B-tree, we will. The page that once held the
extra information is marked as 'available' and it can be filled with
more index information later or filled with actual table data.
A page is a page is a page. InnoDB decides what goes on a page.
So...
Is an index ever fragmented? No (depending on your definition of
fragmentation)
Will there ever be some free space within the index tree? Always.
Can index pages be scattered (non-contiguous) within a tablespace file?
Yes.
Will rebuilding a table ensure that the index pages are made contiguous?
No.
Do these same answers apply to the actual data stored on a table? Yes.
http://dev.mysql.com/doc/refman/5.6/en/innodb-file-defragmenting.html
http://dev.mysql.com/doc/refman/5.6/en/innodb-file-space.html
Does that help?
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
