Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the optimization of mysql sentences". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

After the MySQL client connects successfully, you can provide server status information by using the show [session | global] status command.

The session represents the statistical results of the current connection, and the global represents the statistical results since the last time the database was started. The default is session.

Here is an example:

Show status like 'Com_%'; where Com_XXX represents the number of times the XXX statement was executed. Important note: Com_select,Com_insert,Com_update,Com_delete passes these parameters

It is easy to know whether the current database applications are mainly insert updates or query operations, as well as the approximate execution proportion of all kinds of SQL.

There are also several commonly used parameters that make it easy for users to understand the basic situation of the database.

Connections: the number of attempts to connect to the MySQL server show status like 'Connections'

Uptime: the time the server works (in seconds) show status like 'Uptime'

Slow_queries: the number of slow queries (default is 10) show status like 'Slow_queries'

How to query the slow query time of mysql

Show variables like 'long_query_time'

Modify mysql slow query time

Set long_query_time=2

* * how to locate slow queries

Show variables like 'long_query_time'

You can reset set long_query_time=2

* * Test statement * *

Select * from emp efocus dept d where e.empno=123451 and e.deptno=d.deptno

If you bring order by e.empno, the speed will be slower, sometimes as much as 1min.

* by default, mysql does not record slow query logs. You need to specify bin\ mysqld.exe-- slow-query-log when starting.

The slow log will be placed in the data directory [in the mysql5.0 version under the mysql installation directory / data/], and you need to check it under mysql5.5.19

My.ini 's datadir= "C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

To make sure.

Explain select * from emp where ename= "zrlcHd" produces the following message:

Select_type: indicates the type of query.

Table: table that outputs the result set

Type: indicates the connection type of the table

Possible_keys: indicates the index that may be used when querying

Key: represents the index actually used

Key_len: the length of the index field

Rows: number of rows scanned www.2cto.com

Extra: description and description of implementation

When it comes to improving database performance, indexes are the cheapest thing. No need to add memory, no need to change the program, no need to call sql, just execute a correct 'create index'

It is tempting that the query speed can be increased a hundredfold and a thousand times. But there is no free lunch in the world, and the improvement of query speed is at the expense of the speed of insert, update and delete.

These write operations have added a large number of Icano.

Can building an index solve all the problems? ename

What happens if there is no index on the?

Select * from emp where ename='axJxC'

The cost of the index

Disk occupancy

Impact on the efficiency of dml (update delete insert) statements

* * four types of indexes can be seen when creating an index through myadmin

* * briefly describe the differences between the four indexes of mysql

PRIMARY index = "automatically create on primary key

UNIQUE index = > equivalent to INDEX + Unique

INDEX index = > is a normal index

FULLTEXT = > is only supported in the MYISAM storage engine for full-text indexing. It is often used in content systems and in all English websites (English word independent). Chinese data is not commonly used and has little significance. Domestic full-text index is usually completed by sphinx.

* * Composite index

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

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 table name ADD PRIMARY KEY (column name,..); federated primary key

Delete index DROP INDEX index_name ON tbl_name; alter table table_name drop index index_name; www.2cto.com

Deleting the primary key (index) is special: alter table tweeb drop primary key

Query index (all available) show index from table_name; show keys from table_name; desc table_Name

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 in it.

2, for a multi-column index, which is not the first part of the use, the index will not be used.

3Gore like query starts with%

4, if the column type is a string, be sure to quote the data in quotation marks in the condition. Otherwise, the index is not used.

5, if mysql estimates that using a full table scan is faster than using an index, the index is not used.

Add a primary key index

Alter table dept add primary key (deptno)

-- Test statement

Explain select * from dept where deptno=105\ G

The result is:

Mysql > explain select * from dept where deptno=105\ G

* * 1. Row *

Id: 1 www.2cto.com

Select_type: SIMPLE

Table: dept

Type: const

Possible_keys: PRIMARY

Key: PRIMARY

Key_len: 3

Ref: const

Rows: 1

Extra:

1 row in set (0.00 sec)

-- create a multi-column index

Alter table dept add index myind (dname,loc)

Prove that for a created multi-column index, as long as the query condition uses the leftmost column, the index is generally used

Explain select * from dept where dname='rjTUPqjZvf'\ G; will show that index myind is used

Explain select * from dept where loc='MsBDpMRX'\ G; will not show that index myind is used

For queries that use like

Explain select * from dept where dname like'% rjTUPqjZvf'\ G; will not show that index myind is used

Explain select * from dept where dname like 'rjTUPqjZvf%'\ G; will show that index myind is used

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

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

Alter table dept drop index myind www.2cto.com

Alter table dept add index myind (dname)

Explain select * from dept where dname='aaa' or loc='aa'\ Gbank / will not be used on the dname column

If the column type is a string, be sure to quote the data in quotation marks in the condition. Otherwise, no index is used.

Select * from dept from dname=1234\ G / / will not use the index

Select * from dept from dname='1234'\ G / / will use the index

Check the usage of the index show status like 'Handler_read%'; handler_read_key: the higher the value, the better, which indicates the number of times the index has been queried.

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

In some cases, you can use joins instead of subqueries. Because you don't need to create temporary tables in memory to use join,MySQL.

If you want to use an index in a query that contains or, you must use an index for every conditional column between or. If there is no index, you should consider increasing the index.

When MyISAM inserts data, it is placed at the end by default. After deleting the data, the space is not reclaimed. (transactions and foreign keys are not supported)

InnoDB supports transactions and foreign keys

In applications requiring high precision, it is recommended to use fixed-point numbers to store values to ensure the accuracy of the results.

Create table temp1 (T1 float (10jue 2), T2 decimal (10jin2))

Insert into temp1 values (1000000.32); found that T1 became 1000000.31, so there was a problem.

For the storage engine is MyISAM, if you often delete and modify records, you should regularly perform the optimize table table_name; function to defragment the table.

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;-- replication

Delete from temp2 where id=1; found that the table was not smaller for the data file.

Regularly execute optimize table temp2 to find table size changes and defragment.

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

If there are too many records in a table, if I split it into 100 tables, then each table has only 100000 records. A good basis for separation is the most important. UNION

Some tables have only a small number of records, perhaps only 20,000 or 30,000, but the fields are very long, the table takes up a lot of space, and a large number of Icano is needed to retrieve the table, which seriously degrades the performance. At this point, you need to split the large fields into another table, and the table has an one-to-one relationship with the original table. (JOIN)

This is the end of the content of "Optimization of mysql sentences". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.