Shulou(Shulou.com)06/01 Report--

I. Optimization of MySQL

Preface to  :

Optimization module for     MySQL database:

 -Database Design-three Paradigm

 -Index of the database: unique index, primary key index, aggregate index, composite index, default index

 -SQL optimization

 -sub-library and sub-table

 -read-write separation: improve IO performance

 -stored procedure optimization

 -optimize MySQL configuration (my.ini)

 -regularly cleans up fragments

1. The design of database (1) what are the three paradigms?

In order to establish a database with less redundancy and reasonable structure,     must follow certain rules when designing the database. In a relational database, this rule is called a paradigm. The three paradigms include:

   -1NF: the atomicity of attributes, which requires them to be atomic and indecomposable.

   -2NF: for the uniqueness of a record, the record in the table is unique (usually through the primary key).

   -3NF: is a constraint on field redundancy, requiring that fields have no redundancy.

(2) 1NF color-case create table `user` (id int,name varchar (10), address varchar (10)); insert into `user`user` values. At this time, for the address field, the address can be divided into:-city-district. At this time, the design of the watch does not follow 1NF. (3) 2NF

-- case

Create table `emp` (id int,name varchar (10), age int (10), salary float (8) 4) where id is the employee's number, each number uniquely determines an employee, and the employee's recharge and position number are also determined by this id. At this time, it is reasonable to use id as the primary key of the emp table, because the primary key generally does not do business operations, and the primary key is used to uniquely identify a row.) ; (4) 3NFLI-case create table student (stu_id int (10), stu_name varchar (30), class_id int (10), class_name varchar (30)); Insert into student values (1recorder); Insert into student values (2LSJI); Insert into student values (3LINGH); and Insert into student values (4LINGWBJI) Here we find that there is a lot of redundancy in the class_id and class_name fields, which does not follow the 3NF. Here we need to split the table: student table and class table create table student (stu_id int (10), class_id int (10), stu_name varchar (30)); create table `class` (class_id int (10), class_name varchar (30)); then set the foreign key association for the two tables. two。 Introduction and location of slow query (1) location of slow query

Definition of     slow query: MySQL stipulates that as long as the result is not returned within 10 seconds, it is a slow query type, and then MySQL will store these statements in the slow query log.

You can view it through the command

-- use show status to view MySQL server status information mysql > show status

-- how long did mysql run show status like 'uptime'?

-- the number of queries to the current window database show status like 'com_select'

-- number of inserts to the current window database show status like 'com_insert';-- number of updates to the current window database show status like' com_update';-- current window, number of deletions to the database show status like 'com_delete';-- check the number of connections attempted to MySQL (regardless of whether the connection was successful or not) show status like' connections'

-- View the number of currently open connections. Show status like 'threads_connected'

-- displays the number of slow queries * * show status like 'slow_queries'

Note: the default is session, which indicates the current session. If you want to query the global situation, you need to:

Show global status like'; (2) slow query case demonstration

① slow query time setting

-- query slow query time show variables like 'long_query_time';-- modify slow query time (temporary) set long_query_time=1;-but after restarting mysql, long_query_time is still the value in my.ini.

Building tables and data by ②

/ * Department Table * / create table dept (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 / * employee form * / create table emp (empno mediumint unsigned not null default 0, / * number * / 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, / * entry time * / sal decimal (7,2) not null / * salary * / comm decimal (7,2) not null, / * bonus * / deptno mediumint unsigned not null default 0 / * Department number * /) ENGINE = MyISAM default CHARSET = utf8 / * salary * / 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 data INSERT INTO salgrade VALUES (1m 700jue 1200); INSERT INTO salgrade VALUES (2m 1201je 1400); INSERT INTO salgrade VALUES (3m 1401J 2000); INSERT INTO salgrade VALUES (4J 2001J 3000); INSERT INTO salgrade VALUES (5m 3001J 9999)

③ generates 100W pieces of data in batch

-- generate random characters create function rand_string (n int) returns varchar # this function returns a string begin # chars_str defines a variable chars_str of type varchar, default value 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare chars_str varchar (100) default' abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare return_str varchar (255) default'; declare i int default 0while I

< n dosetreturn_str = concat( return_str, substring( chars_str, floor( 1 + rand()* 52 ), 1 ));seti = i + 1;end while;return return_str;end--生成随机数create FUNCTION rand_num()RETURNS int(5)BEGIN DECLARE i int default 0; set i =floor(10+RAND()*500); return i;END--编写存储过程，插入数据delimiter // create procedure insert_emp(in start int(10),in max_num int(10))begindeclare i int default 0; #set autocommit =0 把autocommit设置成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 ;-- 执行call insert_emp (100001,10000000); ④设置MySQL记录慢查询日志 首先将MySQL服务关闭： 进入mysql/bin下执行一下命令： [mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复） $ mysqld.exe --safe-mode --slow-query-log 然后在my.ini配置文件中有这么一行：

In this directory, the corresponding slow query records are generated.

# 5.7 version is automatically enabled:

Then we set the slow query time to: 1s

Set long_query_time=1

Execute a slow query with the data you just created:

Select * from emp where ename = 'aDNehz'

View the slow query log:

Through the log, we can locate which statement is slow to query.

3. Overview of the index of MySQL

    indexes are used to quickly query records with specific values. All MySQL indexes are saved in the form of a B + tree. If there is no index, the MySQL must perform a full table scan from the first record until the appropriate record is found. The more records in the table, the more time-consuming this operation will be. If an index has been created on a column that is a search condition, MySQL can quickly find the location of the target record without scanning any records. If the table has 1000 records, finding records by index is at least 100 times faster than scanning records sequentially.

(1) Primary key index

The     primary key index is a unique index, but it must specify "PRIMARY KEY". The primary key is usually specified when the table is created, and a table can have only one primary key.

# create primary key index: by default, MySQL automatically adds a primary key index to the primary key. You can also add the primary key later: Alter table table_name add primary key (field_name); # remove the primary key: Alter table table_name drop primary key; # View index show index from table_name;show keys from table_name; (2) full-text index

    full-text indexes are generally used to query text or long content.

CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR, 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...'); # usage of full-text index-- wrong use method select * from articles where body like'% mysql%';-- to establish full-text index alter table articles add FULLTEXT index fulltext_article (body);-- correct use method select * from articles where match (body) against ('mysql')

Considerations for using full-text indexing:

The full-text index included with    -MySql can only be used for data tables whose database engine is MYISAM. If it is another data engine, the full-text index will not take effect.

   -MySQL cannot do full-text indexing in Chinese, only in English.

   -use full-text indexing, using only the fixed syntax: match (field name)... Against (keywords).

The default minimum length that can be found by the    -MySQL full-text index is 4 characters, and if the string of the query contains a stop word (common characters), the stop word will be ignored.

(3) unique index

All values of an index like     can only appear once, that is, they must be unique.

By default, when you specify that a field is unique when you create a table, a unique index is automatically created for it.

# create a unique index: CREATE UNIQUE INDEX ON tablename (list of columns); ALTER TABLE tablename ADD UNIQUE [name of index] (list of columns); CREATE TABLE tablename ([...], UNIQUE [name of index] (list of columns)) Example: create table ddd (id int primary key auto_increment, name varchar (32) unique); create unique index uniq_index_name on ddd (name); alter table ddd add unique uniq_index_name (name); Note: the unique field can be null and there can be multiple null, but only one can be''if it is a string. (4) General index

The only task of a     normal index (an index defined by the keyword KEY or INDEX) is to speed up access to data. Therefore, indexes should be created only for those data columns that appear most frequently in query criteria (WHEREcolumn=) or sort conditions (ORDERBYcolumn). Whenever possible, you should select the column with the most neat and compact data (such as a column of integer type) to create the index.

Creation method: create index index name on table (column 1, column name 2);-- case-- query 1.45sselect * from emp where ename = 'MFPkFv';-- to create a general index create index index_ename on emp (ename);-- query 4msselect * from emp where ename =' MFPkFv'; (5) Index implementation principle

The index of the     MySQL database is a sorted data structure in database management to facilitate quick query and update the data in the database table. The index implementation in MySQL is B+ tree index.

The figure above in     shows one possible indexing method. On the left is the data table, which has two columns and seven records, and the leftmost one is the physical address of the data record (note that logically adjacent records are not necessarily physically adjacent on disk). To speed up the Col2 lookup, you can maintain a binary lookup tree shown on the right, with each node containing an index key and a pointer to the physical address of the corresponding data record, so that you can use binary lookup to obtain the corresponding data within the complexity of O (log2n).

Indexes used by different storage engines:

Performance analysis of     bMurell + tree index: first, from the analysis of B-Tree, according to the definition of B-Tree, we can know that a search requires a maximum of h nodes. The designers of the database system skillfully make use of the principle of disk pre-reading, setting the size of a node to equal to one page, so that each node can be fully loaded only once. To achieve this, you need to use the following techniques in the actual implementation of B-Tree:

Every time     creates a new node, it directly applies for a page of space, which ensures that a node is physically stored in a page. Coupled with the fact that the computer storage allocation is page-aligned, it realizes that a node needs only one Icano. One search in B-Tree requires no more than one search in hmi (root node resident memory), and the asymptotic complexity is O (h) = O (logdN). In general practical applications, the output degree d is a very large number, usually more than 100, so h is very small (usually no more than 3).

The structure of     and red-black tree is much deeper than that of red-black tree. Because the logically close nodes (father and son) may be physically far away and can not make use of the locality, the asymptotic complexity of the red-black tree is also O (h), which is obviously much lower than that of B-Tree.

To sum up,     is very efficient to use B-Tree as an index structure.

The keys stored in the non-leaf node of the     B+ tree do not indicate the address pointer of the data object, and the non-leaf node is only the index part. All the leaf nodes are on the same layer and contain all the keys and the storage address pointers of the corresponding data objects, and the leaf nodes are linked in the order from the smallest to the largest. If the actual data objects are stored in the order in which they are added instead of the number of key codes, the index of the leaf node must be dense, and if the actual data storage is stored in the order of key codes, the index of the leaf node is sparse.

The     B + tree has two head pointers, one is the root node of the tree, and the other is the leaf node of the minimum key.

So there are two search methods for B+ trees:

    searches in the order of the linked list pulled up by the leaf node itself.

    searches from the root node, similar to the B-tree, except that if the key of the non-leaf node is equal to a given value, the search does not stop, but continues along the right pointer to the key on the leaf node. So no matter whether the search is successful or not, you will walk through all the layers of the tree.

In the B+ tree, the insertion and deletion of data objects take place only on the leaf node.

To sum up, the differences between the two sorts are as follows:

The same key value does not appear multiple times in the    -B tree, and it may appear in the leaf node or in the non-leaf node. The keys of the B + tree must appear in the leaf nodes, and may also be repeated in the non-leaf nodes in order to maintain the balance of the B + tree.

   -because the location of the B-tree key is variable and appears only once in the whole tree structure, although it can save storage space, it significantly increases the complexity of insert and delete operations. Compared with B + tree, it is a good compromise.

The query efficiency of    -B tree is related to the position of keys in the tree, the maximum time complexity is the same as that of B + tree (at the leaf node), and the minimum time complexity is 1 (at the root node). However, the complexity of the B+ tree is fixed to a built tree. You can scan the power of 2.

(6) the advantages and disadvantages of index.

  advantages:

   -creating indexes can greatly improve system performance

   -greatly speeds up the retrieval of data

   -connection between accelerometer and meter

   -when using grouping and sort clauses for data retrieval, you can also significantly reduce the time spent on grouping and sorting in a query

  disadvantages:

   -time to index: it takes time to create and maintain the index, which increases as the amount of data increases

   -takes up a certain amount of physical memory: indexes need to take up physical space. In addition to data tables taking up data space, each index also takes up a certain amount of physical space. If you want to build a clustered index, you will need more space.

   -DML operations become inefficient: when the data in the table is added, deleted, and modified, the index is also maintained dynamically, thus reducing the speed of data maintenance.

(7) timing of indexing

The fields that   should index:

   -the query should create an index as a query condition field

   -fields that are often used in connections can be indexed

   -create indexes on columns that often need to be searched by scope

   -create indexes on columns that often need to be sorted

Fields that   should not index:

   -columns that are rarely used or referenced in queries should not be indexed

   -columns that have very few data values should not be indexed, such as gender, whether they are married, etc.

   -columns defined as text, image, and bit data types should not be indexed

   -Indexes should not be created when modification performance is much greater than retrieval performance

(8) points for attention in the use of the index

Failure scenario of ① federated index

-- add data to dept: create PROCEDURE insert_dept (in start int (10), in max_num int (10)) BEGIN declare i int DEFAULT 0; set autocommit=0; REPEAT set iExtension1; insert into dept values ((start+i), rand_string (10), rand_string (8)); UNTIL I = max_num end REPEAT; commit;END-- execution call insert_dept (100d10);-- create primary key index alter table dept add primary key (deptno) -- create a federated index alter table dept add index my_ind (dname,loc); / / the left column of dname, and loc is the right column in the federated index. Take dept as an example:-- explain select * from dept where dname = 'fuHagHPcRc';-- index does not fail explain select * from dept where dname =' fuHagHPcRc' and loc = 'KtPSTRAc';-- index invalidation explain select * from dept where loc =' KtPSTRAc'

The conclusion of the case is that a multi-column index created will not be created if it does not use the first part.

② fuzzy query when like, the index will not fail, but if there is'% xxx%', in the like, the index will not fail, but the% after the index will not fail, for example "xxx%", but the% in front will fail, for example: "% xxx".

③ if there is an or in the condition, even if there is an indexed field in it, the index will not be used

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

⑤ if MySQL scans full tables faster than using indexes, indexes are not used.

# check the utilization of the index: show status like 'handler_read%'

Handler_read_key: this value is as high as possible, which indicates the number of times it has been queried using the index.

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

4. Optimization skills of SQL statements (1) default sorting of group by

    uses group by to group queries. After the default grouping, it will be sorted, which may slow down. Adding order by null after group by can prevent sorting.

-- case-- 8.188sselect * from emp group by deptno;-- 6.200sselect * from emp group by deptno order by null; this is because sorting is used by default after group by: explain select * from emp group by deptno

(2) in some cases, use joins instead of subqueries

Reason: using join,MySQL does not require the creation of temporary tables in memory.

-7.924sselect * from dept, emp where dept.deptno=emp.deptno;-- 8.8sselect * from dept left join emp on dept.deptno=emp.deptno (3) when optimizing queries, try to avoid full table scans. (4) try to avoid judging the null value of the field after the where clause, otherwise it will cause the engine to give up using the index and do the full table scan (solve, set the null value to default) (5) when judging the where clause, try not to use xx=. Example: > =, = 18 is written as > 17 (6) if you can use between, do not use in II, supplement content 1. MySQL storage engine (1) introduction to MySQL engine

There are three storage engines used by     MySQL: myisam / innodb/ memory.

    Myisam storage: if the table does not require high transactions and is mainly query and addition, we consider using the myisam storage engine. For example, the posting table and reply table in bbs

    innodb storage: high transaction requirements, saved data is important data, we recommend using INNODB, such as order table, account table.

    Memory: based on memory storage, similar to Redis.

(2) the difference between myisam engine and innodb engine

   -transaction security (MyISAM does not support transactions, INNODB supports transactions)

   -query and add speed (MyISAM bulk insert speed is fast)

   -supports full-text indexing (MyISAM supports full-text indexing, INNODB does not)

   -locking mechanism (table lock when MyISAM, innodb is row lock)

   -Foreign key mechanism (MyISAM does not support foreign keys, INNODB supports foreign keys)

Ps:Memory storage, for example, our data changes frequently, does not need to be stored, and frequently queried and modified at the same time, we consider using memory, the speed is very fast.

(3) precautions in the use of myisam

    if we specify the myisam engine when we create the table, then when we delete the table data, the default is only logical deletion, and the real physically stored data files will not be deleted.

Next,     will show you how to store MySQL data in the following figure:

    has datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data in the my.ini file: the configuration is the directory where the local system stores MySQL data files.

Enter the specific database: (test)

Among them:

The structure file that represents the table at the end of     .frm

Presentation data file at the end of     .MYD

Presentation index file at the end of     .MYI

# then we demonstrate how to delete data if you are using the myisam engine:

-- create a table and specify the engine as myisamcreate table test100 (id int unsigned, name varchar (22)) engine=myisam;-- to insert data insert into test100 values (1Magnum Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

At this point, we look at test100.MYD and find that the size of the file has not changed at all.

-- at this point we have to remove the debris: optimize table test100

Then the test100.MYD size will change and the data will be deleted in the real sense.

2. Scheduled backup of MySQL

Write a backup script and then execute it periodically through crontab.

-- Syntax: mysqldump-u-account-password database [Table name 1 Table name 2..] > file path-for example: mysqldump-u-root root test > d:\ temp.sql-- load data, go to MySQLmysql > source / path/temp.sql

In the next article, the editor will show you how to divide the database and table in MySQL and the read-write separation of MySQL load balancer.

Blog address: https://blog.51cto.com/14048416/2432938

There are absolutely no parallel imports!

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.