Mysql performance optimization 2: mysql Performance Optimization

Mysql performance optimization 2: mysql Performance Optimization

Next, Mysql performance optimization 1

Create an appropriate index

To improve database performance, indexing is the best and inexpensive. There is no need to add memory, no need to change the program, no need to tune the SQL, as long as the correct 'create Index' is executed, the query speed may be improved by times, which is really tempting. However, there is no free lunch in the world, and the query speed is improved at the cost of the insert, update, and delete speeds. These write operations increase a lot of I/O.

Is creating an index able to solve all the problems? What if no index is created on ename?

Select * from emp where ename = 'r & D authority ';

--- Test case command (preferably select * from emp e, dept d where e. empno = 123451)

* Add a primary key

ALTER TABLE emp ADD PRIMARY KEY(empno);

* Deleting a primary key

alter table emp drop primary key;

Principles of Indexing

Why is no index slow?

Why is indexing faster?

B-tree indexes use the binary search method. The algorithm complexity is log2N. That is to say, 16 pieces of data are queried four times, and 32 pieces of data are queried five times, 64 data records 6 times .... and so on

Index cost

1. disk usage

2. Impact on efficiency of dml (update delete insert) Statements

Btree retrieval, algorithm complexity: log2N times

 

Which columns are suitable for indexing?

1. index should be created frequently as a query condition Field

select * from emp where empno = 1;

2. Fields with poor uniqueness are not suitable for independent index creation, even if they are frequently used as query Conditions

Select * from emp where sex = 'male'

3. Frequent Updates to fields are not suitable for index creation.

select * from emp where logincount = 1

4. fields that do not appear in the WHERE clause should not be indexed

Index type

• Primary Key index. The Primary key is automatically used as the Primary index (type Primary)
• UNIQUE Index)
• General INDEX)
• Full text index (FULLTEXT) [For MyISAM] -- "sphek + Chinese Word Segmentation coreseek [Chinese version of sphek]
• Comprehensive use => Compound Index

Differences between the four indexes of mysql

LPRIMARY Index = automatically created on the primary key

LUNIQUE Index => A UNiQUE index can be created only when the field content is Unique)

LINDEX Index => is a common index

LFULLTEXT => this parameter is only supported by the MYISAM storage engine. It is used for full-text indexing. It is used in many content systems and on many websites in full English (with independent English words ). chinese data is not commonly used and is of little significance. Domestic full-text indexes are usually completed using sphinx. Full-text indexes can only be created in the char varchar text field.

Full-text index case

1. Create a table

create table news(id int , title varchar(32),con varchar(1024)) engine=MyISAM;

2. Create a full-text index

create fulltext index ful_inx on news (con);

3. insert data

Note that the Common English fulltext does not match, and the inserted statement is correct.

'But it often happens that they are not abve supporting themselves by dishonest means. which shocould be more disreputable. Cultivate poverty like a garden herb'

4. Check the matching degree.

mysql> select match(con) against('poverty') from news;+-------------------------------+| match(con) against('poverty') |+-------------------------------+|                             0 ||                             0 ||                             0 ||            0.9853024482727051 |+-------------------------------+

0 indicates that no match is found, or your word is a stopword, so no index will be created.

You cannot use the like statement to use full-text indexes.

Composite Index

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

Use of Indexes

Create 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,...); join 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 indexes (all available)

show index(es) from table_name;show keys from table_name;desc table_Name;

Modify the index. We usually Delete the index first and recreate it.

The most important condition for an index to be used for a query is that the index must be used in the query condition.

Indexes may be used in the following situations:
1. For the created multi-column index, the index is generally used as long as the leftmost column is used in the query condition.
2. For like queries, if '% aaa' is used, indexes are not used, and 'aaa %' is used.

The following tables do not use indexes:
1. If there is or in the condition, it will not be used even if there is a condition with an index.
2. If multiple-column indexes are not the first part, they are not used.
3. The like Query starts with %.
4. 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, the string must be '')
5. If mysql estimates that full table scan is faster than indexing, no index is used.

Test Case (demonstration on the preceding dept table .)

CREATE TABLE dept(deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,dname VARCHAR(20)  NOT NULL  DEFAULT "",loc VARCHAR(13) NOT NULL DEFAULT "") ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

-- Put the data, which should have been added before. If not, add the data again.

-- Test starts.

Add a primary key index

alter table dept add primary key (deptno)

-- Test statement

explain select * from dept where deptno=1;

The result is:

mysql> explain select * from dept where deptno=1;*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: dept         type: constpossible_keys: PRIMARY          key: PRIMARY      key_len: 3          ref: const         rows: 1        Extra:1 row in set (0.00 sec)

-- Create multi-column Indexes

alter table dept add index myind (dname,loc);

-- It is proved that the index is generally used as long as the leftmost column is used for the created multi-column index.

Explain select * from dept where dname = 'r & D authorization'; the index myind is used.

Explain select * from dept where loc = 'msbdpmrx'; the index myind is not displayed

-- For queries using like

Explain select * from dept where dname like '% R & D department'; the index myind is not displayed.

Explain select * from dept where dname like 'r & D department % '; the index myind is used.

-- If there is or in the condition, it will not be used even if the condition has an index.

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

Alter table dept drop index myindalter table dept add index myind (dname) explain select * from dept where dname = 'r & D department 'or loc = 'aa'; -- The dname column is not used.

-- If the column type is a string, you must quote the data in quotation marks in the condition. Otherwise, no index is used.

Select * from dept from dname = 1234; // The index is not used.

Select * from dept from dname = '000000'; // The index is used.

View index usage
Show status like 'handler _ read % ';
Note:
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.

* At this time, we will see that handler_read_rnd_next has a high value. Why? This is because we performed multiple queries when we didn't add an index before.

Common SQL Optimization

About data insertion in large batches (MySql administrator)
For MyISAM:

Alter table table_name disable keys; loading data // insert statement; alter table table_name enable keys;

For Innodb:
1. Sort the data to be imported by primary key
2. setunique_checks = 0. Disable the uniqueness check.
3. setautocommit = 0. Disable Automatic submission.

Optimize group by statements
By default, MySQL sorts all group by col1 and col2. This is similar to specifying order by col1 and col2 in a query. If the query contains group by but you want to avoid consumption of sorting results, you can use order by null to disable sorting.

In some cases, you can use a connection to replace subqueries.
Because join is used, MySQL does not need to create a temporary table in memory. (Explanation)

If you want to use indexes in query statements containing or, indexes must be used for each condition column between or. If there is no index, you should consider adding indexes (related to the environment)

Select * from table name where Condition 1 = ''or condition 2 = 'TT'

Explaine select * from dept group by dname; => the extra: using filesort statement is displayed and sorted.

Explaine select * from dept group by dnameorder by null => extra: using filesort is not displayed.

* ** In some cases, you can use a connection to replace subqueries. Because join is used, MySQL does not need to create a temporary table in memory.

Explain select * from emp, dept whereemp. deptno = dept. deptno;

The problem can be explained after comparison with the following !!

Explain select * from emp left join dept onemp. deptno = dept. deptno;

Select an appropriate storage engine

MyISAM:The default MySQL storage engine. If the application is dominated by read and insert operations, there are only a few update and delete operations, and the transaction integrity requirements are not very high. The advantage is that the access speed is fast.

InnoDB:Provides transaction security with the ability to commit, roll back, and crash recovery. However, compared with MyISAM, the write processing efficiency is less efficient and will occupy more disk space.

Memory:Data is in memory, and data is lost when the service is restarted.

MyISAM:The data is inserted at the end by default. After the data is deleted, the space is not recycled. (transactions and Foreign keys are not supported)

InnoDB supports transactions and Foreign keys

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

If you choose small principles:

1. If you are pursuing speed and do not care whether the data is always saved or not, select memory to store the user's online status.

2. If the data in the table needs to be permanently saved, the application focuses on read operations and insert operations. There are only a few update and delete operations, and the transaction integrity requirements are not high. Choose MyISAM

3. If you need to persistently store data and provide transaction security with commit, rollback, and crash recovery capabilities, select Innodb

Select an appropriate data type

In applications with high precision requirements, we recommend that you use the number of points to store values to ensure the accuracy of the results. Do not use float for deciaml

For databases whose storage engine is MyISAM, If you often delete and modify records, you must regularly execute the optimize table table_name; function to fragment tables.

The date type should be selected based on actual needs to meet the minimum storage type of the application

Create table bbs (id int, convarchar (1024), pub_timeint );

Date ('ymmd', time-3*24*60*60); 2038-1-19

Case Study of Using Floating Point Number and fixed point number

Create table temp1 (t1 float (10, 2), t2 decimal (10, 2 ));

Insert into temp1 values (1000000.32, 1000000.31,); it is found that t1 is, so there is a problem.

Demo of 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; the data file for this table is not small

Regularly execute optimize table temp2 to find the table size change and complete the fragment.

& For InnoDB, its data will be stored in the data/ibdata1 directory, and there will be only one *. frm table structure file in data/database.