Mysql Database performance Optimization two _mysql

In the last article to introduce the MySQL database performance optimization One, today continue to go on the article to introduce the database performance optimization related knowledge. The details are as follows:

Establish an appropriate index

Speaking of improving database performance, index is the most inexpensive things. No need to add memory, do not change the program, do not need to adjust the SQL, as long as the implementation of the correct ' create index ', query speed may be increased by hundreds of thousands of times, this can really be tempting. But the world does not have a free lunch, query speed is increased by inserting, updating, delete the speed of the cost, these write operations, adding a lot of I/O.

Is it possible to create an index to solve all the problems? What happens when there is no index on the ename?

---Test case commands are as follows (preferably with SELECT * from emp e,dept D where e.empno=123451)

* Add primary key

* Delete primary key

The principle of the index description

Why is it slow without an index?

Why is it faster to use an index?

The cost of indexing

1. Disk occupancy

2. Efficiency impact on DML (UPDATE delete insert) statements

Btree method of retrieval, algorithm complexity: log2n times

Which columns are appropriate for adding indexes

1, more frequent as a query criteria field should create an index

2, the uniqueness is too bad field is not suitable to create a separate index, even if frequently as a query condition

3, updated very frequently fields are not suitable for creating indexes

4, fields that do not appear in the WHERE clause should not create an index

Types of indexes

• Primary key index, PRIMARY key auto main index (type primary)

• Unique index (unique)

• General Index

• Full-text indexing (fulltext) [for myisam]--] Sphinx + Chinese word coreseek [Sphinx Chinese Version]

• Comprehensive use of => composite index

A brief description of the difference of MySQL four indexes

Lprimary index = "Automatically created on primary key

Lunique index => As long as it is unique is the unique index. (You can create a unique index only if the field contents are not duplicated)

Lindex index => is a normal index

The Lfulltext => is supported only in the MyISAM storage engine, and is intended for full-text indexing, which is used in content systems and is used in all English-language sites (independent of English words). Chinese data is not commonly used, meaning is small, the domestic Full-text index usually uses Sphinx to complete, the Full-text index can only be created in the Char varchar text field.

Full-text Indexing case

1. Create a table

2. Establish Full-text indexing

3. Inserting data

Note here that the common English fulltext will not match, and the inserted statement itself is correct.

' But it often happens that they are no above supporting themselves by dishonest Means.which should to more disreputable.c Ultivate poverty like a garden herb '

4. Look at the matching degree

Mysql> Select Match (con) against (' poverty ') from news;
+-------------------------------+
| match (Con) against (' poverty ') |
+-------------------------------+
| 0 |
| 0 |
| 0 |
| 0.9853024482727051 |

0 means no match, or your word is a stop word and is not indexed.

With Full-text indexing, you cannot use the like statement so that Full-text indexing is not used.

Composite Index

Use of Indexes

Establish an index

Create [unique| FULLTEXT] Index index_name on Tbl_name (col_name [(length)] [ASC | DESC], ...);
ALTER TABLE table_name ADD INDEX [index_name] (index_col_name,...) 
Add primary key (index) ALTER table name add PRIMARY key (column name,..); Union PRIMARY key

Delete Index

DROP INDEX index_name on Tbl_name;
ALTER TABLE table_name DROP INDEX index_name; 
Deleting a primary key (index) is special: ALTER TABLE t_b drop PRIMARY key;

Query index (all available)

Show index (ES) from table_name;
Show keys from table_name;

To modify the index, we generally delete it before recreating it.

The most important condition for a query to use an index is the need to use an index in the query criteria.

It is possible to use the index in the following situations:

1, for a multiple-column index that is created, the index is generally used as long as the query condition uses the leftmost column.

2, for queries that use like, if the query is '%AAA ' will not be used to the index, ' aaa% ' will use the index.

The following tables will not use indexes:

1, if there is an or in the condition, it will not be used even if there is a conditional index.

2, for a multiple-column index, not the first part of the use, the index is not used.

The 3,like query starts with a%

4, if the column type is a string, be sure to quote the data in quotes in the condition. Otherwise, the index is not used. (when added, the string must be ')

5, the index is not used if the MySQL estimate uses full table scanning faster than using the index.

Test case (Just do the demo on the previous Dept table.)

CREATE TABLE Dept (
deptno mediumint UNSIGNED NOT null default 0,
dname VARCHAR () NOT NULL default "",
Loc VA Rchar () Not NULL default ""
) Engine=myisam default Charset=utf8; 
--Put the data in the front should have been added, if not then you need to add
--Test started.

Add a primary key index

--Test statement

The result:

Mysql> explain select * from dept where deptno=1;
1. Row ***************************
id:1
select_type:simple
table:dept
type:const
Possible_keys : PRIMARY
key:primary
key_len:3
ref:const
rows:1
Extra:

--Create a multiple-column index

--Proof for multiple-column indexes that are created, indexes are typically used whenever the query condition uses the leftmost column

Explain select * FROM dept where Dname= ' Research and Development department '; Shows the use of the index myind
explain select * FROM dept where loc= ' Msbdpmrx '; does not display use to index Myind

--for queries that use like

Explain select * FROM dept where dname like '% Research and development department '; Does not show the use of the index myind
explain select * FROM dept where dname like ' research and development department% '; will show the use of the index Myind

--If a condition has an OR, even if a conditional index is not used

--For demonstration purposes, we delete the composite index and then only add the index to the dname.

ALTER TABLE Dept DROP INDEX myind
ALTER TABLE Dept Add index Myind (dname)

--If the column type is a string, be sure to quote the data in quotes in the condition. Otherwise do not use the index

SELECT * from dept to dname=1234; Will not be used to index
SELECT * FROM dept to dname= ' 1234 ';//will be used to index

Viewing the use of indexes

Show status like ' handler_read% ';

We can note that:

Handler_read_key: The higher the value the better, the higher the number of times that the index query is used.

Handler_read_rnd_next: The higher the value, the less efficient the query.

* At this point we will see that the Handler_read_rnd_next value is very high, why, this is because we did not index in front of the time to do a number of query reasons.

Common SQL Optimizations

Mass Insert data (MySQL Administrator) understand

For MyISAM:

ALTER TABLE table_name disable keys;
Loading Data//insert statement;

For InnoDB:

1, the data that will be imported is sorted by primary key

2,set unique_checks=0, turn off uniqueness checksum.

3,set autocommit=0, turn off automatic submission.

Optimize GROUP BY statement

By default, MySQL sorts all group by Col1,col2. This is similar to the col2 specified in the query by Col1. If the query includes group by but the user wants to avoid the sort result consumption, you can use the order by

Null prohibit sort

In some cases, you can use a connection instead of a subquery.

Because using Join,mysql, you do not need to create temporary tables in memory. Explain

If you want to use an index in a query that contains or, each condition column between or is required to be indexed, and if there are no indexes, you should consider adding an index (for environment-related explanations)

SELECT * FROM table name where Condition 1= ' or condition 2= ' TT '
explaine select * FROM Dept GROUP by Dname => extra:using Description will be sorted
explaine select * FROM Dept GROUP by Dname ordered by null => this time does not contain a display extra:using Filesort description will not be sorted

In some cases, you can use a connection instead of a subquery. Because there is no need to create a temporary table in memory using Join,mysql

Explain select * from EMP, dept where Emp.deptno=dept.deptno;

And the following comparison can explain the problem!!

Explain select * from EMP LEFT JOIN dept on Emp.deptno=dept.deptno;

Select the appropriate storage engine

MyISAM: The default MySQL storage engine. If the application is based on read and insert operations, only a very small number of updates and deletions, and the integrity of the transaction requirements are not very high. Its advantage is that the speed of access is fast.

InnoDB: Provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with MyISAM, write processing is less efficient and consumes more disk space.

Memory: Data is in memory, data is lost when service restarts

MyISAM: When inserting data, the default is the last. , the space is not recycled after data is deleted. (Transactions and foreign keys are not supported)

InnoDB supports transactions and foreign keys

According to our programmers, the most common storage engines are myisam/innodb/memory,heap tables.

If you choose a small principle:

1. If the pursuit of speed, do not care whether the data has been saved, also do not consider the transaction, please choose memory such as store users online status.

2. If the data of the table is to be persisted, the application is dominated by read and insert operations, with few updates and deletions, and the integrity requirements for the transaction are not very high. Choose MyISAM

3. If you need data to persist and provide transaction security with commit, rollback, and crash recovery capabilities, choose InnoDB

Select the appropriate data type

In applications with high precision, it is recommended to use fixed-point numbers to store values to ensure the accuracy of the results. Deciaml don't use float.

For the storage engine is a MyISAM database, if you frequently do delete and modify records, you will be scheduled to perform optimize table table_name, the function of the table defragmentation.

Date types Choose the early types that meet the minimum storage for the application based on actual needs

CREATE TABLE BBS (id int, con varchar (1024), pub_time int);
Date (' Ymd ', Time -3*24*60*60); 2038-1-19

Case description for using floating-point and fixed-point numbers

CREATE TABLE Temp1 (T1 float (10,2), T2 decimal (10,2));
INSERT into TEMP1 values (1000000.32,1000000,32); found that T1 became 1000000.31 so there was a problem.

Demo for optimize table name

CREATE TABLE TEMP2 (id int) Engine=myisam;
INSERT into TEMP2 values (1); INSERT into TEMP2 values (2); INSERT into TEMP2 values (3);
INSERT INTO TEMP2 SELECT * from temp2;--copy
delete from Temp2 where id=1 found that the data file for the table is not smaller

Regular execution of optimize table temp2 discovery of table size changes, defragmentation completed

&& for InnoDB its data will exist in the Data/ibdata1 directory, in the data/database/only one *.FRM table structure file.

About MySQL Database performance optimization two small series to introduce to everyone here, I hope to help you!