Several Methods for SQL optimization and SQL Optimization

Several Methods for SQL optimization and SQL Optimization

Database three paradigm

A general understanding of the three paradigms is of great benefit to database design. In database design, in order to better apply the three paradigms, the three paradigms must be understood in a general sense (the general understanding is sufficient, not the most scientific and accurate understanding ):

1 paradigm: 1NF is an atomic constraint on attributes. Attributes must be atomic and cannot be decomposed;

Second paradigm: 2NF is the uniqueness constraint on the record and requires that the record have a unique identifier, that is, the uniqueness of the object;

Third paradigm: 3NF is a constraint on field redundancy, that is, any field cannot be derived from other fields, and it requires that the fields are not redundant.

No redundant database can be designed. However, databases without redundancy may not be the best. Sometimes, to improve operational efficiency, we must lower the paradigm standard and properly retain redundant data. The specific approach is to follow the third paradigm in conceptual data model design, and to reduce the paradigm standard to be considered in physical data model design. Reducing the paradigm is to add fields and allow redundancy.

SQL statement type:

Ddl (Data Definition Language): create, alter, drop

Dml (data operation language): insert, delete, update

    select

Dtl (Data transaction language): rollback, commit, savepoint

Dcl (Data Control statement): grant, revoke

Show status command

This command displays the current status of your mysql database. We are mainly concerned with commands starting with "Com ".

Show status like 'com % '<=> show session status like 'com %' // displays the status of the current Console

Show global status like 'com % '; // displays the number of times the database is started to be queried.

Focus on Com_select, Com_insert, Com_delete, Com_update,

    show session status like 'Com_select';

Show the number of attempts to connect to the database show status like 'connections ';

Server working duration: show status like 'uptime'; (unit: Wonderful)

Slow Query Count: show status like 'slow _ queries '; (the default value is 10 show variables like 'long _ query_time ';)

Stress Test script

Create a DEPT/* Department table */

CREATE TABLE `dept` (  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',  `dname` varchar(20) NOT NULL DEFAULT '',  `loc` varchar(13) NOT NULL DEFAULT '') ENGINE=MyISAM DEFAULT CHARSET=utf8;

Create Table EMP/* employee table */

CREATE TABLE `emp` (  `empno` int(11) NOT NULL DEFAULT '0',  `ename` varchar(20) NOT NULL DEFAULT '""',  `job` varchar(20) NOT NULL DEFAULT '“”',  `mgr` int(11) NOT NULL DEFAULT '0',  `hiredate` date NOT NULL,  `sal` decimal(10,0) NOT NULL,  `comm` decimal(10,0) NOT NULL,  `deptno` int(11) NOT NULL DEFAULT '0') ENGINE=MyISAM DEFAULT CHARSET=utf8;

# Wage level table

create table salgrade(    grade mediumint unsigned not null default 0,    losal decimal(17,2) not null,    hisal decimal(17,2) not null)engine=MyISAM default charset=utf8;insert into salgrade values(1,700,1200);insert into salgrade values(2,1201,1400);insert into salgrade values(3,1401,2000);insert into salgrade values(4,2001,3000);insert into salgrade values(5,3001,9999);

// Randomly generate the string delimiter $ // define a new command Terminator drop function rand_string $ create function rand_string (n int) returns varchar (255) begin declare chars_str varchar (100) default 'duration'; declare return_str varchar (255) default ''; declare I int default 0; while I <n do set return_str = concat (return_str, substring (chars_str, floor (1 + rand () * 52), 1); set I = I + 1; end while; return return_str; end $ select rand_string (6 ); // randomly generate the Department number create function rand_num () returns int (5) begin declare return_num int (5) default 0; set return_num = floor (10 + rand () * 500 ); return return_num; end $ select rand_num (); // insert records (massive data) to the emp table create procedure insert_emp (in start int (10), in max_num int (10 )) begin declare I int default 0; set autocommit = 0; repeat set I = I + 1; insert into emp values (start + I), rand_string (6), 'salesman ', 0001, curdate (), 2000,400, rand_num (); until I = max_num end repeat; commit; end $ delimiter; // change the command terminator back to a semicolon // call the Stored procedure call insert_emp (0.18 million); // create employee data delimiter $ create procedure insert_dept (in start int (10 ), in max_num int (10) begin declare I int default 0; set autocommit = 0; repeat set I = I + 1; insert into dept values (start + I ), rand_string (10), rand_string (8); until I = max_num end repeat; commit; end $ delimiter; call insert_dept (); // create ten departments

How to find the select statement for slow query in a project, mysql database supports recording slow query statements into logs (not started by default)

1. Start the mysql Service

Go to the mysql installation directory

2. Start xx> bin \ mysqld.exe -- slow-query-log

Modify the slow query time set long_query_time = 0.5;

show status like 'slow%';

Creating indexes is the best way to optimize SQL statements.

(1) It can improve query efficiency, but at the cost of reducing insertion, deletion, and update efficiency, because the index adds a lot of I/O

(2) disk space occupied

Explain, which can analyze SQL statements and predict SQL Execution Efficiency

Explain select * from emp where empno = 123456 \ G; (\ G vertical display)

Pay attention to the following fields:

    type: 

All --> full table query to avoid this situation

System --> the table has only one row, which is a special case of the const join type.

Const --> A table can have a maximum of one matching row.

Possible_keys: indexes that may be used

Key: actually used Index

Rows: How many rows of data are retrieved?

Extra: (mainly for sorting)

No tables: the query statement uses from dual or does not contain any from clause.

Using filesort: When the query statement needs to be sorted (order by), and the index cannot be used to complete the sorting

Using temporary: some operations must use temporary tables, common group by, order

Using where: You can obtain data only through indexes without reading table information.

There are four types of indexes:

Primary Key Index (primary): Set a column as an index, that is, a primary key index.

Unique index: This column is unique and also an index.

Common index: Common index

Full text index (fulltext )://

Comprehensive use -- Composite Index

Which columns are suitable for adding indexes:

(1) If the query condition field is frequently used, the index select * from emp where emp. empno = '1' should be created ';

(2) fields with poor uniqueness are not suitable for creating indexes, even if they are frequently used as filter conditions select * from class where gender = 'male ';

(3) fields with frequent updates are not suitable for index creation.

(4) fields that do not appear in the where clause are not suitable for index creation.

Alter table emp add primary key (emp); // add a primary key index

Create [unique [FULLTEXT] index name on table name (column name ...)

Alter table name add index [index name]

Alter table emp drop primary key; // Delete the primary key index

Drop index name on table name;

Alter table emp drop index name;

How to query all indexes of a table:

    show indexes from tablename;

    show keys from tablename；

Use of indexes (in special cases ):

(1) For the created multi-column index, the index is generally used as long as the leftmost column is used in the query condition. This statement is for alter table dept add index myindex (dname, loc); in this case, the index will be created on dname, and there will be no index when querying with loc;

(2) % aaa does not need an index for like queries, and both aaa % and aa % a both use an index;

(3) If there is or in the condition, the index is not used.

(4) If the column type with an index is a string, you must quote it with quotation marks in the condition before using the index. Otherwise, you do not need to use the index.

(5) If the mysql algorithm calculates that full table scan will be faster, the index will not be used.

How to check whether the created index is valid:

Show status like 'handler _ read_key '; the higher the value, the more times the index is queried, and the index is valid.

Show status like 'handler _ read_rnd_next '; the higher the value, the lower the query efficiency