First, index fast Scan (index fast full scan)The index fast full scan, which is very similar to index full scan, is also suitable for all types of B-tree indexes, including unique index
Index range scan ):
1. for unique index, if the where condition is followed by
2. For none unique index, if the where condition is followed by =,>,
3. For composite indexes, if the bootstrap column of the composite index appears after the where condition, index range scan
Index full scan and index fast full scan look similar to each other. They are both common and personalized. The commonality between the two is that scanning is not required.You can directly return all the required data through the index. This is undoubtedly one of the rare data access methods to improve query performan
When the index full scan is made, Oracle navigates to the root block of the index, then to the branch block (if any), then navigates to the first leaf block and then reads it according to the Leaf block's doubly linked list order. The blocks it reads are sequential and sorted.The index fast full scan is different from
columns (combined indexes), the order of the columns is very important, and MySQL can only find the leftmost prefix of the index effectively. For example:The query statement select * from T1 where c1=1 and c2=2 can use this index, assuming there is a composite index IT1C1C2 (C1,C2). The query statement select * FROM T1 where c1=1 can also use the
SQL> select * from v $ version where rownum
BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
Create index I _test_owner on test (owner );
SQL> create index I _test_owner on test (owner );
Index created.
Index
speeds up the index query, reduces the size of the index file, and increases the update speed of the insert.If you set up a single-column index on Usernname,city,age, so that the table has 3 single-column indexes, the efficiency of the query and the combined index above is very different, much lower than our combined
collect limit 1000, 10; Very quickly, basically 0.01 seconds ok, and then look at the followingSelect Id,title from collect limit 90000, 10; Starting from 90,000 pages, results?8-9 seconds to complete, my God what's wrong???? In fact, to optimize this data, online find the answer. Look at the following statement:Select ID from collect order by ID limit 90000, 10; Soon, 0.04 seconds will be OK. Why? Because using the ID primary key to do the index is
Create Table yw (ID int unsigned NOT NULL auto_increment,c1 int NOT NULL default ' 0 ',C2 int NOT null default ' 0 ',Primary KEY (ID));Creating a stored procedure call insert_yw inserting data into a tableTwo parameters to improve insert performance when insertingInnodb_flush_log_at_trx_commit = 0Sync_binlog = 0Call Insert_yw (4500000);Select COUNT (*) from YW;+----------+| COUNT (*) |+----------+| 4500000 |+----------+1 row in Set (2.06 sec)It's 2.06 seconds.After the secondary
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.