Phpmysql database optimization (1)

1NF: indicates that the columns in a table are atomic and cannot be decomposed. that is, the column information cannot be decomposed ,. In general, as long as the database is a comprehensive technology for Mysql database optimization technology, it mainly includes a: table design rationalization (in line with 3NF) B: add an appropriate index (index) [four types: normal index, primary key index, unique index unique, full-text index] c: table sharding technology (horizontal and vertical segmentation) d: read/write [write: update/delete/add] separated e: Stored Procedure [modular programming, which can improve the speed] f: mysql configuration optimization [configure the maximum concurrency my. ini, adjust the cache size] g: mysql server hardware upgrade h: regularly clear unnecessary data, regularly perform fragment (MyISAM) u what kind of table is in line with 3NF (paradigm) the table's paradigm must first conform to 1NF to meet 2NF, and further meet 3NF1NF: that is, the table's columns are atomic and cannot be decomposed, that is, the column information cannot be further decomposed ,. Generally, as long as the database is a relational database (mysql/oracle/db2/informix/sysbase/SQL server), it automatically meets 1NF? Database classification relational database: mysql/oracle/db2/informix/sysbase/SQL server non-relational database: (features: object-oriented or set) NoSql database: MongoDB (features are document-oriented) 2NF: the record in the table is unique and 2NF is satisfied. generally, we design a primary key to implement 3NF: no redundant data exists in the table, that is, table information, if it can be deduced, a field should not be designed separately for storage. for example, the following design does not meet 3NF: Anti 3NF: However, non-redundant databases may not be the best databases. sometimes, to improve operational efficiency, we must lower the standard of paradigm, retain redundant data properly. Reducing the paradigm is to add fields and allow redundancy. We often see cases in one-to-multiple tables: Optimization of SQL statements: how to quickly locate slow statements in a large project. (locate slow queries) ① first, we need to know the commands for querying the running status of the mysql database (for example, how many select/update/delete statements are executed at the current mysql running time/total times .. /current connection) show status // display the mysql database status commonly used: show status like 'uptime '; // How long does the database run show stauts like 'com _ Select' // How many queries are executed by the database show stauts like 'com _ insert' // How Many insert statements are executed by the database... and so on, update delete? Show [session | global] status like .... if you do not write [session | global], it is a session by default, which refers to the execution of the current window. if you want to view all the results (from mysql to the present, it should be global) show status like 'ons ons'; // Display slow query times show status like 'slow _ queries '; ② how to locate slow query columns: build a large table (4 million) -> by default, mysql considers that 10 seconds is a slow query. l modify the slow query of mysql. show variables like 'Long _ query_time '; // you can display the current slow query time set long_query_time = 1; // you can modify the slow query time to build a large table.> large tables have requirements for records, it is useful only when the record is different. Otherwise, the test result is significantly different from the actual one.

Create:

Create table dept (/* Department TABLE */deptno mediumint unsigned not null default 0,/* No. */dname VARCHAR (20) not null default "", /* name */loc VARCHAR (13) not null default ""/* Location */) ENGINE = MyISAM default charset = utf8; create table emp (empno mediumint unsigned not null default 0,/* No. */ename VARCHAR (20) not null default "",/* name */job VARCHAR (9) not null default "",/* work */mgr mediumint unsigned not null default 0,/* superior number */hiredate date not null, /* start time */sal DECIMAL (7,2) not null,/* Salary */comm DECIMAL (7,2) not null, /* BONUS */deptno mediumint unsigned not null default 0/* Department No. */) ENGINE = MyISAM default charset = utf8; 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; test data insert into salgrade VALUES (1,700,120 0 ); insert into salgrade VALUES (1400, 2000); insert into salgrade VALUES (3000, 9999); insert into salgrade VALUES (,); insert into salgrade VALUES );

To ensure that the stored procedure can be normally executed, we need to modify the command execution Terminator.

Delimiter $ create function rand_string (n INT) returns varchar (255) # This function returns a string begin # chars_str defines a variable chars_str, type: varchar (100 ), default value: 'hangzhou'; declare chars_str varchar (100) default 'hangzhou'; 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 ii = I + 1; end while; return return_str; end $

Create a stored procedure

Create procedure insert_emp (in start int (10), in max_num int (10) begin declare I int default 0; # set autocommit = 0 set autocommit to 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 $

# Call the previously written function with 1800000 records starting from 100001 to call insert_emp (records). ③ if a statement is executed for more than 1 second, it will be counted. ④ if we record the slow query SQL into one of our logs

By default, mysql does not record slow queries. you must specify slow queries when starting mysql.

Bin \ mysqld.exe--safe-mode---slow-query-log [mysql5.5 can be found in my. ini specified] bin \ mysqld.exe-log-slow-queries = d:/abc. log [earlier versions of mysql5.0 can be found in my. ini specified]

Shut down mysql and start mysql. if the slow query log is enabled, this file is stored by default.

Location recorded in my. ini file

#Path to the database root datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/" 

⑤ Test, we can see that our mysql slow SQL statement is recorded in the log. optimization problems. the explain statement can be used to analyze how mysql executes your SQL statement. you can use this tool for a moment.

Add index

U mysql four indexes (primary key index/unique index/full-text index/Common index) 1. add 1.1 primary key index when a table is set as a primary key, this column is the primary key index create table aaa (id int unsigned primary key auto_increment, name varchar (32) not null defaul ''); this is the id column, which is the primary key index. if you do not specify a primary key index when creating a table, you can add the table after creating the table. Command: alter table name add primary key (column name); example: create table bbb (id int, name varchar (32) not null default ''); alter table bbb add primary key (id); // Append index 1.2 General index generally, to create a common index, create a table first 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 2); 1.3 create a full-text index, it is mainly used to search files and texts, such as articles and full-text indexes for MyISAM ., innodb does not support full-text indexing.

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 %': select * from articles where match (title, body) against ('database'); [yes ]? Note: 1. in mysql, fulltext indexes take effect only for myisam. 2. the fulltext provided by mysql is effective in English-> sphek (coreseek) technology to process Chinese characters 3. use match (field name ..) against ('keyword') 4. full-text index has a stop word, because in a text, creating an index is an infinite number. Therefore, some common words and characters are not created. these words are called Stop Words. 1.4 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. the unique field can be NULL and can have multiple NULL values. However, if it is specific content, it is not possible to repeat NULL data ,. primary key field. it cannot be NULL or repeated. ② Create table eee (id int primary key auto_increment, name varchar (32) after creating a table )); create unique index name on table name (list ..); // append a unique index. 2. query index desc table name [this method has the following disadvantages: the index name cannot be displayed.] show index (es) from table name show keys from table name 3. alter table name: drop index name; // if you delete a primary key index. Alter table name drop primary key [this is a small problem] 4. index modification is first deleted and then re-created.

# P # Why is the speed faster after an index is created # e #

Why is the speed faster after an index is created? Principle:. precautions for using u indexes: 1. disk space occupation 2. impact on dml operations. which columns are suitable for adding indexes for slow u? Conclusion: you must create an index only when the following conditions are met. a: Certainly, B is often used in the where clause. the content of this field is not the only value (sex) c: The content of this field is not frequently changed. note for using the index u: I have added several departments to the dept table: alter table dept add index my_ind (dname, loc); // The column on the left of dname, loc is the column description on the right. if our table has a composite index (the index applies to multiple columns), Note: 1. for the created multi-column index, the index is generally used only when the leftmost column is used for the query condition. Explain select * from dept where loc = 'AAA' \ G // The index will not be used because loc is column 2 on the right. for queries using like, if the query is '% aaa', the index 'AAA %' is not used. For example, the explain select * from dept where dname like '% aaa' \ G cannot use an index, that is, when you like a query, the key 'key', the beginning, cannot use characters such as % or ., if you must change the value above, use full-text index-> sphinx.3. if there is or in the condition, it will not be used even if there is a conditional index. In other words, all fields required must be indexed. we recommend that you avoid using the or keyword 4 whenever possible. if the column type is a string, you must quote the data in the condition using quotation marks. 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. 5. if mysql estimates that using full table scan is faster than using an index, no index is used. Explain can help us describe how to execute mysql without actually executing an SQL statement, so that we can analyze SQL commands. how to view the 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.