Shulou(Shulou.com)06/01 Report--

I. the concept of index

The index in the database is similar to the catalog in the book.

In a book, you can quickly find the information you need without reading the whole book. The catalogue in the book is a list of words, indicating the page number of each word.

Database index in a database, an index enables a database program to find that the index in the desired data database is a column or columns worthy of collection without scanning the entire table. and a list of logical pointers that physically identify these worthy data pages

Advantages of indexing: data can be found quickly

Disadvantages: taking up hard disk resources

2. After the function of the index is set up, the database can use various fast positioning techniques to greatly speed up the query speed, especially when the table is very large, or when the query involves multiple tables, using the index can speed up the query thousands of times and reduce the IO read and write cost of the database. And the index can also reduce the sorting cost of the database by creating a unique index to ensure the uniqueness of the data in the data table can speed up the join between tables. When grouping and sorting are used, the grouping and sorting time can be greatly reduced.

III. Classification of indexes

General index this is the most basic index type, and it has no restrictions such as uniqueness.

Unique index this index is basically the same as the previous "ordinary index", but there is one difference: all values of the index column can only appear once, that is, the unique index must be allowed to be null, but only once.

Primary key primary key is a unique index, but it must be specified as "PRIMARY KEY"

Full-text indexing MySQL supports full-text indexing and full-text retrieval since version 3.23.23. In MySQL, the index type of the full-text index is FULLTEXT, and the full-text index can be created on columns of type VARCHAR or TEXT

Single-column indexes and multi-column indexes can be indexes created on a single column or on multiple columns

IV. The principle of creating an index according to the primary key and foreign key of a table, a table with an index of more than 300 rows should have an index on a table that is often connected with other tables, and the join fields (foreign keys) should be indexed on fields that often appear in the Where clause, especially fields in large tables, indexes should be built on fields with high selectivity and indexes should be built on small fields. For large text fields or even super-long fields, do not build indexes for large text fields or even super-long fields. Full-text indexes are established for large text fields or even super-long fields. Fields with poor fulltext uniqueness are not suitable for indexing. Fields that update too frequently are not suitable for creating indexes.

5. the method of creating an index after selecting the appropriate index according to the needs of the enterprise, you can use CREATE INDEX to create the index CREATE INDEX plus each index keyword to create each type of index.

Actual operation process

1. Create a normal index

Delete index

two。 Create a unique index

Delete index

"alter table" creates a unique index

Another way is to define the index when you create the table.

Create a hobby table

Add data to the hobby table

Associate two tables together

This is a typical multi-table join query.

Code that avoids redundancy can alias the table

View Overview

It is a virtual table, and the data does not exist in the view, but only the mapping data of the real table. A result set is generated by conditional filtering, grouping, sorting, etc., which is stored in memory and persisted. (save only mappings)

Views occupy less resources, which is equivalent to soft connections and shortcuts.

When the data in the real table changes, the view changes accordingly. Create view View name as query statement (select id,name,age) from Table name where (conditional) id=1 or id=5

The role of views: you can create different views according to different users. View different views according to permissions; strengthen security; facilitate sql operation.

Real fuck

Create a view

View view

Overview of stored procedures

Stored procedures: stored procedures are mostly used in software development

To prevent the code from being intercepted during the network transmission, and to ensure the security.

Original state: the sql statement needs to be embedded in the code: through the connection driver (java is jdbc)-pass the sql statement as a parameter to the mysql database for execution.

Stored procedures are written in the database, not in the program.

The program triggers the operation by calling the stored procedure name (similar to a function in shell).

Advantages: code quantity optimization, reducing code quantity

Transport security, hiding sql statements

Network optimization.

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.