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

This blog is about the function and use of MySQL index, as well as a basic introduction to the storage engine.

one。 Mysql index

Brief introduction and function of Index

Index, called key in MySQL, is a data structure used by storage engine to find records quickly. Index is very important for good performance, especially when the amount of data in the table becomes larger and larger, the influence of index on performance becomes more and more important.

Function: through a certain algorithm to group the records in the database according to certain rules, so that the search scope of the data can be narrowed when looking up information, thus improving the efficiency of the query.

In terms of living examples, an index is like a catalogue of books and a list on a list; for example, people go to eat hot pot, and when the menu is in the hands of the customer, the customer can find what he wants as soon as possible according to the classification on the menu (seafood, vegetables, meat, drinks, etc.).

Classification of indexes

Indexes can be divided into: general index, unique index, full-text index, single-column index, multi-column index, spatial index.

Syntax format:

CREATE TABLE table name (

Field name 1 data type [integrity constraints.]

Field name 2 data type [integrity constraints.]

[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

[index name] (field name [(length)] [ASC | DESC])

);

Example:

Create an INDEX generic index

CREATE TABLE dept (

Dept_id INT

Dept_name VARCHAR (30)

Comment VARCHAR (50)

Index (dept_name) # specifies the dept_name field in the table as a normal index field

);

Create a UNIQUE unique index:

CREATE TABLE dept2 (

Dept_id INT

Dept_name VARCHAR (30)

Comment VARCHAR (50)

UNIQUE INDEX (dept_name)

);

Compared to the normal index, there is one more unique option than the index option above.

Create a full-text index

CREATE TABLE dept3 (

Dept_id INT

Dept_name VARCHAR (30)

Comment VARCHAR (50)

Log text

FULLTEXT INDEX (log)

) engine=myisam

Note: only MYISAM storage engine supports full-text indexing. Innodb storage engine does not support full-text indexing.

Create a multi-column index

CREATE TABLE dept13 (

Dept_id INT

Dept_name VARCHAR (30)

Comment VARCHAR (50)

INDEX (dept_name, comment)

);

Compared with a normal index, it is to set multiple fields as indexes

Create an index on an existing table

Grammar one:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index name ON table name (field name [(length)] [ASC | DESC])

Create a normal index example: this method specifies the index name

CREATE INDEX index_dept_name ON dept6 (dept_name)

Example of creating a unique index:

CREATE UNIQUE INDEX index_dept_name ON dept6 (dept_name)

Example of creating a full-text index:

CREATE FULLTEXT INDEX index_dept_name ON dept6 (dept_name)

Example of creating a multi-column index:

CREATE INDEX index_dept_name_ comment ON dept6 (dept_name, comment)

Grammar 2:

ALTER TABLE creates an index on an existing table:

ALTER TABLE table name ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX

Index name (field name [(length)] [ASC | DESC])

Manage Index:

View index: show create table table name\ G

Test index: explain select * from table name where field name = 'xx'

Delete index: drop index index name on table name

Index detection example:

Requirements: create a school database, create a T2 table, insert 1000W pieces of data with stored procedure script T2 table, and then query T2 data to see how long it takes; then create an index for T2, and look at the data again to see how long it takes

Prepare:

Create database school # create school database

Create table school.t2 (id int,name varchar (30)); # create a T2 table with id number and name in it

Define a stored procedure that inserts 1000W pieces of data and call this stored procedure

Mysql > delimiter $$/ / set the delimiter of the command (also known as Terminator) mysql > create procedure autoinsert1 () / / create the stored procedure autoinsert1 (similar to a shell script)-> BEGIN-> declare i int default 1;-> while (i insert into school.t2 values (iGradeCcc');-> set iDeterminator 1;-> end while;-> END$$mysql > delimiter; call atuoinsert1 ()

Query the data twice and compare the time spent (it took 33 minutes to insert 1000W pieces of data in the middle of the experiment, and 100W is estimated to show the effect):

As can be seen from the above experiments, in a table with 1000W storage, there is a 4S difference between querying a piece of data and querying a piece of data after creating an index, and as the data becomes larger, the query time will continue to increase, so it is sufficient to prove that creating an index will greatly improve the query efficiency of MySQL!

two。 Introduction to MySQL Storage engine

It is the implementation method of how to store data, how to index the stored data and how to update and query data. Because data is stored in a relational database in the form of a table, the storage engine can also be called a table type (that is, the type that stores and manipulates the table)

There is only one storage engine in databases such as Oracle and SQL Server, and all data storage management mechanisms are the same. The MySql database provides a variety of storage engines. Users can choose different storage engines for data tables according to different needs, and users can also write their own storage engines according to their own needs.

1. View Storage Engin

SHOW ENGINES

SHOW ENGINES\ G View the storage engines supported by MYSQL

SHOW VARIABLES LIKE 'storage_engine%'; to view the current storage engine

SHOW VARIABLES LIKE 'auto_inc%'; to view the status of self-growing settings

Show global variables like'% connet%' view connet environment variable settings

Mysql > show variables\ G View all environment variables

Show variables's current session

Show global variables\ G Global

two。 Select Storage engine

Method 1.

Mysql > create table innodb1 (

-> id int

->) engine=innodb

Mysql > show create table innodb1

Create tables test100 (id init) engine=inodb

Method 2.

/ etc/my.cnf

[mysqld] add the following line under this line

Default-storage-engine=INNODB

Storage engine commonly used in MySQL

MyISAM storage engine

Because the storage engine does not support transactions and foreign keys, access is faster. Therefore, when there is no requirement for transaction integrity and access-based applications are suitable to use the storage engine.

InnoDB storage engine (this storage engine is used by default for MYSQL)

Because the storage engine has the advantage of transaction, that is, it supports transaction features such as commit, rollback and crash recovery, it takes up more disk space than MyISAM storage engine.

Therefore, when frequent updates and deletions are required, and the integrity of the transaction is required, concurrency control is needed.

MEMORY

The MEMORY storage engine stores data in memory, so it has the fastest access speed, but there is no guarantee of security. Suitable for quick access or temporary tables.

BLACKHOLE

Black hole storage engine, which can be applied to the distribution master library in active and standby replication.

Tables that use the BLACKHOLE storage engine do not store any data, but if mysql has binary logging enabled, the SQL statement is written to the log (and copied to the slave server). In this way, mysqld, which uses the BLACKHOLE storage engine, can act as a relay repeater in master-slave replication or add a filter mechanism to it.

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.