Python-study-42

Index Introduction

One: Why to have index index is used to optimize query efficiency (speed) of no index, for big data tables, can only be traversed each time, the larger the amount of data, the more time there are indexes, you can improve several orders of magnitude of the general application system, reading and writing ratio of about 10:1, And the insert operation and the general update operation rarely has the performance problem, in the production environment, we encountered the most, is also the most prone to the problem, or some complex query operation, so the optimization of the query statement is the top priority. Speaking of accelerating queries, you have to mention the index. Second: What is index index in MySQL is called key (key) is the storage engine used to quickly find records of a data structure index can be understood as a dictionary of the directory part is similar to the form of binary tree organized, General 2-4 layer three: Index error index Many: improve the query speed, But disk IO will explode with fewer indexes: It affects query speed, improves application performance and therefore needs to be specifically analyzed

View Code

Indexing principle

One index principle is to filter out the final desired result by shrinking the range of data that you want to get, while the sequence of event indexes that turn random events into sequential order is that the two disk IO with the read-ahead disk reading data from left to right is the mechanical motion read time = seek time + rotation delay + Transfer time =9ms seek time: 5ms rotation delay: 4ms transfer time: from memory to disk or from disk to memory, negligible so to minimize disk IO pre-reading: Considering that disk IO is a very expensive operation, the computer operating system has done some optimizations, when an IO, Not only the current disk address data, but also the adjacent data are read into the memory buffer, because the local pre-reading principle tells us that when the computer accesses the data of an address, the data adjacent to it will be quickly accessed. Each IO reads the data we call a page. The specific page of how big the data is related to the operating system, generally 4k or 8k, that is, when we read the data in a page, actually occurred once io, this theory is very helpful for the data structure design of the index. 

View Code

Data structure of the index

? This data organization structure is index b+ tree: The data structure is able to control the number of disk IO each time it is found in a small order of magnitude B+ tree Lookup io If it is a million-magnitude data: There is no index to have millions of Io with an index of only 3 Io, which shows the advantages of indexing Note:1. The minimum number of IO times for an indexed field depends onthe height of the B + tree, the smaller the index field, The more data items can be stored per disk block, the lower the height. 2. The leftmost matching attribute of an index

View Code

Clustered indexes and secondary indexes

A clustered index is the same as a secondary index: whether it is a clustered index or a secondary index, the inside is a B + tree, which is balanced in height, and the leaf node holds all the data. A clustered index differs from a secondary index in that the leaf node is stored as a whole row of information because the actual data page can only be sorted by a B+ tree, each table can have only one clustered index clustered index to be able to be in the B+  The leaf node of the tree index directly found on the leaf node of the data secondary index does not contain all the data for the row record. Leaf nodes In addition to the key values, each leaf node in the index row also contains a bookmark this bookmark is used to tell the InnoDB storage engine where to find the row data that corresponds to the index primary key: Clustered index Other key: Secondary index

View Code

Indexing function

Normal index: Accelerate find unique index:     -PRIMARY key index PRIMARY key: Accelerated Find + constraint (not empty, cannot repeat)    -Unique index unique: Accelerated find + constraint (cannot be duplicated) Federated index:    -PRIMARY KEY ( Id,name): Federated primary Key index     -Unique (id,name): Federated unique index     -index (id,name): Federated Common Index

View Code

syntax for creating and deleting indexes

#method One: When you create a tableCREATE Table table name (field name 1 data type [integrity constraint ...], field name 2 data type [integrity constraint ...], [UN IQUE| Fulltext | SPATIAL] INDEX |KEY [index name] (field name [(length)] [ASC|DESC]) );#method Two: Create an index on an existing tableCREATE [UNIQUE | Fulltext |SPATIAL] Index index name on table name (field name [(length)] [ASC|DESC]) ;#method Three: ALTER table to create an index on an existing tableALTER table name ADD [UNIQUE | Fulltext |SPATIAL] Index index name (field name [(length)] [ASC|DESC])                             ; #Drop Index: Dropping index name on table name;#Way OneCREATE TABLE t1 (id int, name char, age int, sex enum ('male','female'), unique key uni_id (ID), index ix_name (name)#index has no key);#Mode twoCREATE index Ix_age on T1 (age);#Way ThreeALTER TABLE T1 add index ix_sex (sex);

View Code

Summarize

Index http://www.cnblogs.com/linhaifeng/articles/7274563.html#top index: Speeds up query speed, Index does not optimize all SQL statements read and write ratio:10:1 Write generally no performance problems we mainly optimize the query speed client --- server 1 network latency problem 2  Server-side disk-to-memory problems the problem with the network is not our research. The problem with reducing disk IO is that the index problem index is the key in MySQL (primary key, index Key) Index: A data structure index used by the storage engine to quickly find records: Each write changes the index once, resulting in a low disk IO index: Affects query performance, the more indexes, The faster the query, the better the balance index principle: Shrinking the search is the range index of the data the index of the directory that is understood as a dictionary is a two-tree structural form PK: Acceleration + Constraint UK: acceleration + constraint index key: accelerated PS: Indexed field to accelerate B + Tree PRIMARY key: Clustered index Other key: Secondary index

View Code

Python-study-42