sql optimization book

the data object Partition_number Partition code Rows Approximate number of rows of data hobt_id Storage unit ID of the heap or B-tree where the data is stored Let's find a database to look at the fragmentation situation:You can see that the arrows refer to this row of data, the theory should be a zone on it, but in fact it establishes two partitions ... So this situation can be considered to defragment, to rebuild, to release deb

optimization scheme (http://www.cnblogs.com/huangye-dream/archive/2013/05/21/3091906.html) Viii. explain viewing index usage in SQLSince the index is important, but there is no index in the SQL execution process, this can be viewed using explain, using the method: Explain SELECT * FROM table_name; is to add explain before the SELECT statement.My local test explain SELECT * from w1000 where id = ' 0000c51e-

feature is set, SQL Server checks file usage every half hour. DBCC SHRINKFILE actions are automatically run if the free space is greater than 25%,sql server. So this feature prevents the data from requesting too much space and not using it. For a system with a very tight disk space, this setting is undoubtedly helpful. But from the database's own health and performance considerations, this setting is not r

SQL Optimization-logical Optimization-subquery optimization (MySQL) and sqlmysql 1) subquery concept:When a query is a subquery of another query, it is called a subquery (a query statement is nested in the query statement ). Subqueries are located: A) Target column position: If a subquery is located in the target

SQL Optimization-logical Optimization-external connections, nested connections, and elimination of connections, SQL nesting 1) eliminate external connections ① External connection Introduction 1) left join/left outer join: LEFT OUTER JOIN The result set of left outer Join includes all rows in the LEFT tabl

Original: SQL Server Enterprise Platform Management Practice book Notes--sql Server database file allocation method1, file allocation methods and file space Check methodThe most common commands for checking data files and table sizes are: sp_spaceusedThis command has three defects: 1, the use of each data file and log file is not visible. 2, this stored procedure

are most appropriate for the database business requirements. This figure shows 4 filegroups, one at a time, for each filegroup. Their backup order (a, C, B, a) reflects the business requirements of the database.Under the full recovery model, restoring a filegroup backup requires not only restoring the filegroup backup itself, but also restoring all log backups from the last full database backup to the target point in time of recovery. To ensure that the file remains consistent with the rest of

Database optimization-SELECT optimization for SQL optimization-mysql internal optimization policy bitsCN.com Database Bottlenecks often occur in query statements. when such problems occur, the general step is to check whether the correct index is used, You can use the explai

environments may run with the following error message. For SQL Monitor's active format report, the online approach is still recommended, and offline viewing allows the script to be downloaded locally. If the report shows an error as follows: For online viewing, it may be a question of the generated report format and see if these options are set beforehand. Set Trimspool on Trim on Set pages 0 Linesize 1000 Set Long 1000000 longchunksize 100000

Artificial Intelligence automatic SQL optimization tools-SQLTuning for SQL Server, SQL-sqltuning In response to this situation, the artificial intelligence automatic SQL optimization tool came into being. Now I will introduce this

rules: A) If the result of the upper query is not duplicated (that is, the main code is included in the SELECT clause), you can expand its subquery. And the distinct flag should be added before the SELECT clause of the expanded query. b) If there is a distinct flag in the SELECT statement for the upper query, the subquery can be expanded directly. If the inner query result does not have a repeating tuple, you can expand it. Specific st

Optimization of Max ()For example: Explain select Max (' Payment_date ') from payment; The number of rows that can be viewed to row is very much, which means that the entire table is scanned.Add an index to payment_date.CREATE INDEX Idx_payment on payment (' payment_date ');Execute SQL statement explain select MAX (' Payment_date ') from payment order by Payment_date; You can see that rows is null.Extra:sel

16. Simple SQL Optimization for mysql partitions 2. SQL Optimization for mysql partitions 1. index classification B-Tree supports HASH. Only memory supports R-Tree myisam and supports Full-text myisam (Full-text index. only the "=" condition in the Memory engine uses the index ======================== === Data Import

≤8) Ext_size Size of the extents, in page units object_id The ID of the database object index_id Represents the type of the data object Partition_number Partition code Rows Approximate number of rows of data hobt_id Storage unit ID of the heap or B-tree where the data is stored Let's find a database to look at the fragmentation situation:You can see that the arrows refer to