How? They essentially drag the Fractal Tree index down to the B-Tree’s level of performance. Unique indexes are a strange beast: they have no impact on standard databases that use B-Trees, such as MongoDB and MySQL, but may be horribly painful for databases that use write optimized data structures, like TokuMX’s Fractal Tree(R) indexes. Why? Because unique indexes may kill your write performance. 4.Before creating a unique index in TokuMX or TokuDB, ask yourself, “does my application really depend on the database enforcing uniqueness of this key?” If the answer is ANYTHING other than yes, do not declare the index to be unique. If a clustered index is to be established, the required space will be larger The index needs to occupy physical space, except that the data table occupies data In addition to the space, each index also occupies a certain amount of physical space. It takes time to create and maintain the index, and this time increases as the amount of data increasesģ. The index is created on the table, not on the viewĢ. When using grouping and sorting clauses for data retrieval, the time for grouping and sorting in the query can also be significantly reduced.ġ. It is especially meaningful to speed up the connection between tables and tables, especially in terms of realizing the referential integrity of data.Ĥ. Greatly speed up the retrieval of data, which is the main reason for creating indexes.ģ. Create a unique index to ensure the uniqueness of each row of data in the database tableĢ. The advantages and disadvantages of indexes:ġ.
Tables with frequent DML, do not build too many indexes ĭo not use those frequently modified columns as index columns If there is both a single-field index and a composite index on these fields, the composite index can generally be deleted. If the composite index contains more than 3 fields, then carefully consider its necessity and consider reducing the composite fields Į. If the fields contained in the composite index often appear in the Where clause alone, it will be decomposed into multiple single-field indexes ĭ. A composite index can be established in the sentence otherwise, a single-field index Ĭ. Several fields of the composite index often appear in the Where clause in an AND mode at the same time. Correctly select the first field in the composite index, which is generally more selective and commonly used in the where clause ī. The establishment of aĬompound index requires careful analysis try to consider using a single-field index Substitute:Ī. Keywords with low selectivity can be used, but when the value distribution of the data is very different, the index can still be used to improve the efficiency when the selective data is relatively small. The key to high selectivity Indexes should be built Often appear in the Where clause and are highly filterable, especially those of large tables, should be indexed Tables that are often connected to other tables should be indexed on the join field fields that It is recommended to establish NOT NULL constraints for index fields CREATE INDEX index name ON table name (column name) TABLESPACE table space name ĬREATE unique INDEX index name ON table name (column name) TABLESPACE table space name ĬREATE INDEX index name ON Table name (column name 1, column name 2) TABLESPACE table space name ĬREATE INDEX index name ON table name (column name) reverse TABLESPACE table space name
0 kommentar(er)
