Index of mysql Optimization _ MySQL

The index of mysql optimization is a comprehensive technology for mysql optimization, mainly including

A: table design rationalization (in line with 3NF)

B: add an appropriate index. [four types: General index, primary key index, unique index unique, and full-text index]

C: table sharding technology (horizontal and vertical)

D: read/write [write: update/delete/add] separation

E: Stored Procedure [modular programming, which can increase the speed]

F: optimize mysql configuration [configure the maximum concurrency my. ini and adjust the cache size]

G: mysql server hardware upgrade

H: regularly clear unwanted data, and regularly perform fragment (MyISAM)

I: SQL statement optimization

This article mainly summarizes the index-related content

Index
Four indexes (primary key index, unique index, full-text index, and common index)

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name ,...) index_col_name: col_name [(length)] [ASC | DESC] (the column score is used as the index)

The column score is used as an index ::
For CHAR and VARCHAR columns, you can create an index using only one part of the column.
When creating an index, use the col_name (length) syntax to compile the index for the prefix.
The prefix contains the first length characters of each column value.

BLOB and TEXT columns can also be indexed, but the prefix length must be given.

The statements shown here are used to create an index. The index uses the first 10 characters of the column name.
Create index part_of_name ON customer (name (10 ));

Because the first 10 characters of most names are usually different, this index is not much slower than the index created using the column's full name.
In addition, using a part of the column to create an index can greatly reduce the index file, which saves a lot of disk space and may increase the INSERT operation speed.

The prefix cannot exceed 255 bytes. For MyISAM and InnoDB tables, the prefix is up to 1000 bytes.
Note that the prefix length is measured in bytes, while the prefix length in the create index statement indicates the number of characters.
This must be taken into consideration when specifying the prefix length of a column using the multi-byte character set.

In MySQL 5.1:
· You can add an index to a column with a NULL value only when you are using the MyISAM, InnoDB, or BDB table type.
· You can add an index to the BLOB or TEXT column only when you are using the MyISAM, BDB, or InnoDB table type.



Index_type ::
Some storage engines allow you to specify the index type when creating an index.
Index_type specifies that the syntax of the statement is USING type_name.
The type_name values supported by different storage engines are shown in the following table.
If the column has multiple index types, if index_type is not specified, the first type is the default value.

Storage Engine	Allowed index types
MyISAM	BTREE
InnoDB	BTREE
MEMORY/HEAP	HASH, BTREE

Primary key index ::
When a table sets a column as the primary key, the column is the primary key index.
1. create table testtable
(Id int unsigned primary key auto_increment,
Name varchar (32) not null defaul '');

2. alter table name add primary key (column name );


Common index ::
Generally, to create a common index, you must first create a table and then create a common index.
For example:
Create table ccc (
Id int unsigned,
Name varchar (32)
)

Create index name on table (Column 1, column name 2 );



Full text index ::
1. FULLTEXT indexes can only be indexed for CHAR, VARCHAR, and TEXT columns,
2. the fulltext index in mysql takes effect only for myisam.
3. mysql-provided fulltext takes effect in English-> sphek (coreseek) technology to process Chinese characters
4. use match (field name...) against ('key ')
5. a full-text index is called a stop word, because in a text, creating an index is an infinite number,
6. Therefore, some common words and characters are not created. these words are called Stop Words.
7. any word that is too short will be ignored. The default minimum length of words that can be found in full-text search is 4 characters.

8. the MATCH () column list must be exactly the same as the list of columns defined by some FULLTEXT indexes IN the table, unless MATCH () is in boolean mode (BOOLEAN full-text search ).
9. the parameter for AGAINST () must be a constant string.

Create:

CREATE TABLE articles (       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,       title VARCHAR(200),       body TEXT,       FULLTEXT (title,body)     )engine=myisam charset utf8;INSERT INTO articles (title,body) VALUES     ('MySQL Tutorial','DBMS stands for DataBase ...'),     ('How To Use MySQL Well','After you went through a ...'),     ('Optimizing MySQL','In this tutorial we will show ...'),     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),     ('MySQL vs. YourSQL','In the following database comparison ...'),     ('MySQL Security','When configured properly, MySQL ...');

How to use full-text index:
Incorrect usage:
Select * from articles where body like '% mysql %'; [full-text index not used]
Proof:
Explain select * from articles where body like '% mysql %'

The correct usage is: (the column value in match is the same as when fulltext is defined)
Select * from articles where match (title, body) against ('database'); [optional]

Unique index ::
① When a column in the table is specified as a unique constraint, this column is a unique index
Create table ddd (id int primary key auto_increment, name varchar (32) unique );
The name column is a unique index.

② Create a unique index after creating a table
Create table eee (id int primary key auto_increment, name varchar (32 ));
Create unique index name on table name (list ..);

The unique field can be NULL and can have multiple NULL values. However, if it is specific content (such as ''), it cannot be repeated.
Primary key field. it cannot be NULL or repeated.

Index Query, delete, modify ::

Desc table name [the disadvantage of this method is that the index name cannot be displayed .]
Show index (es) from table name
Show keys from table name

Alter table name drop index name;

Delete the file first, and then create a new one.

Notes for using indexes ::
1. disk space occupied
2. it has an impact on DML operations and slows down (such as adding, deleting, and modifying). Because select accounts for the vast majority of database operations, it is best to use indexes where indexes are available.


For example: alter table dept add index my_ind (dname, loc); // The column on the left of dname, loc is the column on the right

3. for the created multi-column index, the index is generally used as long as the leftmost column is used in the query condition.
Explain select * from dept where loc = 'AAA'/G
The index will not be used.


4. for like queries, if '% aaa' is used, indexes are not used, and 'AAA %' is used.
For example, explain select * from dept where dname like '% aaa'/G
Indexes cannot be used, that is, when you like a query, the key 'keyword', the beginning, cannot use characters such as % or .,
If the preceding value must be changed, use full-text index> sphindexing.


5. if the condition contains or, it will not be used even if the condition contains an index.
In other words, all fields required must be indexed. we recommend that you avoid using the or keyword whenever possible.

Select * from dept where dname = 'XXX' or loc = 'xx' or deptno = 45

6. if the column type is a string, you must quote the data using quotation marks in the condition.
Otherwise, no index is used. (When adding a column, the string must be ''), that is, if the column is of the string type, you must include it.

7. if mysql estimates that using full table scan is faster than using indexes, no indexes will be used. (For example, a table contains only one piece of data)


Columns suitable for index creation should meet the following requirements ::
A: It must be used frequently in the where clause.
B: the content of this field is not unique values (sex: Male or female 0 or 1)
C: The field content does not change frequently.


View the index usage ::
Show status like 'handler _ read % ';
Result
Handler_read_key: the higher the value, the better. the higher the value indicates the number of times the index is queried.
Handler_read_rnd_next: a higher value indicates inefficient query.