Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to understand how to automatically rebuild the fragmented index of MYSQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

1. The generation of index fragmentation?

The index page is split due to a large number of insertions, modifications, and deletions in the table. If the index has high fragmentation, there are two situations, one is that it takes a lot of time to scan the index, and the other is that the index does not use the index at all when querying, which can lead to performance degradation.

two。 The types of fragments are as follows:

2.1 Internal crushing

Due to the insertion or modification of the data in the index page, it ends with the distribution of the data in the form of a sparse matrix, which will lead to the increase of the data page and thus the query time.

2.2 external crushing

Due to data insertion or modification of index / data pages, ending with page number separation and the allocation of new index pages that are not coherent in the file system, the database server cannot take advantage of the read-ahead operation because the next associated data page is not approaching, and the following page numbers of these connections may be anywhere in the data file.

Automatically rebuild the fragmented index

Create a new defragmentation stored procedure in the data

The code is as follows:-- = =-- TEMPLATE GENERATED FROM TEMPLATE EXPLORER USING:-- CREATE PROCEDURE (NEW MENU) .SQL-USE THE SPECIFY VALUES FOR TEMPLATE PARAMETERS-- COMMAND (CTRL-SHIFT-M) TO FILL IN THE PARAMETER-- VALUES BELOW.---- THIS BLOCK OF COMMENTS WILL NOT BE INCLUDED IN-- THE DEFINITION OF THE PROCEDURE.-- = = SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =-- AUTHOR:-- CREATE DATE:-- DESCRIPTION:-- = ALTER PROCEDURE USP_IMS_DEFRAGMENT_INDEXESAS- -declare the variable SET NOCOUNT ONDECLARE @ TABLENAME VARCHAR-- Table name (index fragmentation has occurred) DECLARE @ EXECSTR VARCHAR-- execute the statement DECLARE @ INDEXNAME CHAR to rebuild the index-- Index name DECLARE @ DBNAME SYSNAME-- Database name DECLARE @ DBNAMECHAR VARCHAR (20)-- Database name DECLARE @ TABLEIDCHAR VARCHAR (255)-- Table name (used to traverse index fragmentation)

-- check whether to run SELECT @ DBNAME = DB_NAME () IF @ DBNAME IN ('master',' msdb', 'model',' tempdb') BEGINPRINT 'THIS PROCEDURE SHOULD NOT BE RUN IN SYSTEM DATABASES.'RETURNEND ELSEBEGINSET @ DBNAMECHAR =' DBNAME'END'in the user database

-- Phase 1: detect fragments-- declare cursors DECLARE TABLES CURSOR FORSELECT CONVERT (VARCHAR,SO.ID) FROM SYSOBJECTS SOJOIN SYSINDEXES SION SO.ID = SI.IDWHERE SO.TYPE = 'U'AND SI.INDID

0

-- create a temporary table to store fragment information CREATE TABLE # FRAGLIST (TABLENAME CHAR (255), INDEXNAME CHAR (255))

-- Open the cursor OPEN TABLES

-- execute the DBCC SHOWCONTIG command FETCH NEXTFROM TABLESINTO @ TABLEIDCHAR on all tables in the database

WHILE @ @ FETCH_STATUS = 0begin FRAGLIST-Statistics on all indexes of the table # FRAGLIST

EXEC ('SELECT OBJECT_NAME (DT.OBJECT_ID) AS TABLENAME,SI.NAME AS INDEXNAME FROM' +'(SELECT OBJECT_ID,INDEX_ID,AVG_FRAGMENTATION_IN_PERCENT,AVG_PAGE_SPACE_USED_IN_PERCENT'+ 'FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID (''+ @ DBNAMECHAR+'''), object_id (''+ @ TABLEIDCHAR+''')'+', NULL,NULL '' DETAILED'') WHERE INDEX_ID0) AS DT INNER JOIN SYS.INDEXES SI'+' ON SI.OBJECT_ID=DT.OBJECT_ID AND SI.INDEX_ID=DT.INDEX_ID AND'+ 'DT.AVG_FRAGMENTATION_IN_PERCENT > 10 million AND DT.AVG_PAGE_SPACE_USED_IN_PERCENT

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.