Mysql database optimization summary and mysql database Summary

Mysql database optimization summary and mysql database Summary

I. Common Database optimization operations

1. The table design must conform to the three paradigm.

2. Add an appropriate index, which has a great impact on the query speed. You must add an index. Primary Key Index, unique index, common index, full-text index

3. Add appropriate stored procedures, triggers, and transactions.

4. read/write splitting (Master/Slave database)

5. Optimize SQL statements (SQL statements with slow query execution speed)

6. Table sharding

Table sharding: divides a large table into multiple tables. Partition: allocate a table to different regions for storage,

7. Upgrade the mysql server hardware.

2. improve efficiency in a timely manner

First paradigm

Atomicity: fields in the table cannot be further divided. As long as they are relational databases, they automatically meet the first paradigm.

Relational Database: (Concepts of rows and columns) mysql, SQL server, oracle, db2, infomix, sybase, postgresql

When designing, you must first have a specific record (content) in database-table-field: design fields when storing data.

Non-relational databases (nosql databases): memcache/redis/momgodb/

Second paradigm

A table does not have identical records, which can be solved by a primary key.

Third paradigm

Redundant data cannot be stored in tables,

 

Anti-triplicate Design

 

 

Iii. Locate slow queries

Slow query: Find a slow query statement on a website. You can enable a log file to record slow SQL statements. By default, slow query logs are disabled, and the default record time is more than 10 seconds.

1. Start mysql by recording slow queries,

First, close mysql and go to the mysql installation directory.

To disable the mysql service, choose computer> Management> service> mysqld and click STOP.

{Mysql installation directory}> bin/Mysqld.exe -- safe-mode -- slow-query-log

Locate SQL statements with low execution efficiency through slow query logs. The slow query log records all SQL statements whose execution time exceeds long_query_time.

 

2. view the storage path of slow query logs.

After the slow query log is enabled, a slow query log file is created. The log file is saved in the database Directory, which can be viewed in the configuration file.

Slow query logs are stored in the mysql data DIRECTORY. The my. ini configuration file contains the data directory.

# Pathto the database root

Datadir = "C:/Documentsand Settings/All Users/Application Data/MySQL Server 5.5/Data/"

 

3. Process test:

View the slow query record time in the current database:

Show variables like 'long _ query_time ';

Modify the slow query time:

Set long_query_time = 2;

Perform the test using the following function:

The benchmark (count, expr) function can test the time required to execute the count expr operation.

In general, the query is slow, because no index is added.

Iv. Index

Test Table created:

create table user(   id int primary key auto_increment,   name varchar(32) not null default '',   age tinyint unsigned not null default 0,   email varchar(32) not null default '',   classid int not null default 1)engine myisam charset utf8;insert into uservalues(null,'xiaogang',12,'gang@sohu.com',4),(null,'xiaohong',13,'hong@sohu.com',2),(null,'xiaolong',31,'long@sohu.com',2),(null,'xiaofeng',22,'feng@sohu.com',3),(null,'xiaogui',42,'gui@sohu.com',3);

Create a class table:

create table class(   id int not null default 0,   classname varchar(32) not null default '')engine myisam charset utf8;insert into classvalues(1,'java'),(2,'.net'),(3,'php'),(4,'c++'),(5,'ios');

1. Primary Key Index

You can add createtable emp (id int primary key) to the created table)

After creating a table, you can add: altertable tablename add primary key (column 1, column 2)

Primary Key Index features:

(1) A table can have at most one primary key index.

(2) One primary key index can point to multiple columns

(3) primary key index Columns cannot have duplicate values or null values.

(4) primary key index efficiency is high.

 

2. Unique Index

You can add createtable emp (name varchar (32) unique) when creating a table)

After creating the table, add:

Alter table tablename add unique [index name] (column name)

Unique index features:

(1) A table can have multiple unique indexes.

(2) A unique index can point to multiple columns,

For example, alter tabletablename add unique [index name] (column 1, column 2)

(3) If not null is not specified for a unique index, the column can be empty and multiple null values are allowed,

(4) The unique index is more efficient.

 

3. Common Indexes

Using common indexes mainly improves query efficiency

 

Add alter table tablename add index [index name] (column 1, column 3)

 

4. Full-text index

Mysql's full-text index mysql5.5 does not support Chinese characters and English. The storage engine of the table must be myisam. If you want to support Chinese characters, there are two solutions,

(1) Use aphsf-chinese version coreseek (to replace full-text index)

(2) Plug-In mysqlcft.

5. View Indexes

(1) show index from Table Name

(2) show indexes from Table Name

(3) show keys from Table Name

(4) desc table name

6. delete an index

(1) primary key index deletion:

Alter table tablename drop primary key; Note: When deleting a primary key index, you must first remove the auto_increment attribute.

(2) Delete A unique index

Alter table tablename drop index unique index name

(3) Deletion of common indexes:

Alter table tablename drop index normal index name

 

7. major questions about indexing:

(1) Frequent index creation as a query condition Field

Select * from emp where empno = 1

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'

Fields with frequent updates are not suitable for index creation.

Select * from emp where logincount = 1

(2) fields that do not appear in the WHERE clause should not be created

The index is at the cost. Although the query speed is improved, the efficiency of adding and deleting will be affected. In addition, the index file occupies space.

 

 

V. Use of explain

 

This tool can analyze SQL Execution efficiency, but does not execute SQL statements. Check whether indexes are used in SQL statements.

Syntax: explain SQL statement \ G or desc SQL statement \ G

When using indexes:

When no index is used:

 

Description of parameters of the explain tool:

The following information is generated:

Select_type: indicates the query type.

Table: The table of the output result set.

Type: indicates the table connection type.

Possible_keys: indicates the index that may be used during query.

Key: indicates the index actually used

Key_len: Index Field Length

Rows: number of rows scanned (Estimated number of rows)

Extra: Description and description of execution

 

Vi. Index

1. For the created multi-column (composite) index, as long as the query condition uses the leftmost column, the index is generally used.

mysql> alter table user add index (name,email);Query OK, 5 rows affected (0.08 sec)Records: 5 Duplicates: 0  Warnings: 0

The query condition does not use the leftmost column, so the index is not used.

This condition uses the leftmost column of the composite index. Therefore, this query uses the index.

2. For queries using like, if the query is "% XXX", the index is not used, and 'xxx % 'is used.

 

 

 

Note:In some cases, like queries are still used. For example, you can use the lyrics to search for a song name or a story to search for a movie name. With the help of tools, coreseek software in sphinx.

 

3. If the condition contains or, the index fields of or must have an index. Otherwise, the index cannot be used.

After the index is added to this email, the index will be used in the test.

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

 

5. Optimize the group by statement

By default, mysql sorts all group by col1 and col2. This is the same as specifying order by col1 and col2 in the query. If the query contains group by but the user wants to avoid consumption of the sorting result, order by null can be used to disable sorting.

 

 

6. When the retrieved data volume exceeds 20% of the data in the table, the optimizer will not use the index, but scan the entire table. The number of scanned rows is too large, and the optimizer determines that the whole table scans the blocks that are attracted by the pesos.

 

7. View index usage

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.

8. insert data in large batches

For MyISAM:

Disable the index first:

Alter table table_name disable keys;

Loading data // insert statement; execute the insert statement

After the insert statement is executed, enable the index and add the index.

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.

VII. Lock Mechanism for concurrent processing

For example, perform the following operations:

(1) retrieve the id value from the database,

(2) Add 1.

(3) Save the modification to the database.

For example, the original id value is 100, =, 101

Perform the preceding steps 100 times and change to 200

 

If two users execute the command at the same time.

User:

100

101

 

User B:

100

101

 

Solve the problem by locking the machine

Lock Mechanism: During execution, only one user obtains the lock. Other users are blocked and need to wait for unlocking.

Mysql locks take the following forms:

Table-Level Lock: low overhead, fast locking, the highest probability of lock conflict, and the lowest concurrency. The myisam engine belongs to this type.

Row-Level Lock: high overhead, slow locking, the lowest probability of lock conflict, and the highest concurrency. Innodb belongs to this type.

1. Table lock demonstration;

Read operations on the myisam table (with a read lock) will not block read requests from other processes to the same table, but will block write requests to the same table. Only when the read lock is released will other processes be executed.

After a read lock is added to a table, other processes can only query the table, and the modification will be blocked.

The current process can perform query operations and cannot perform modification operations. You cannot operate tables that are not locked.

Lock table Syntax:

Lock table name read | write

You can also lock Multiple tables. Syntax: locktable Table 1 read | wirte, Table 2 read | wirte

Write operations on the myisam table (with a write lock) will block any operations by other processes on the locked table and cannot be read or written,

After a write lock is applied to a table, only the locked table by the current process can be operated. Operations of other processes will be blocked.

 

2. Demonstration of row locks

The innodb Storage engine is implemented by locking the index items on the index. This means that innodb uses row-level locks only when data is retrieved through the index conditions. Otherwise, innodb uses table locks.

Row lock Syntax:

Begin

SQL statement

Commit

After the row lock is enabled, when the current process executes an operation on a record, other processes cannot operate records with the same id as the current process.

Php has a file lock. Most of the files in the actual project use the file lock, because the table lock will block. When some tables are added with a write lock, other processes will not be able to operate. This will block the entire website and slow down the speed of the website.

 

Similar problems:

There is one item in stock and one item in stock. At this time, two users request to place an order at the same time. How can we prevent both orders from being placed successfully, but no goods are delivered.

8. Sub-tables

Table sharding: divides a large table into several small tables:

Vertical segmentation:

In dedecms, vertical segmentation:

In a database, you want to store various types of data, such as Article data, movie data, music data, and product data,

Content base table + additional table:

Content master table: stores some public information about various data, such as the data name and adding time,

You can use multiple additional tables to store unique data information.

Main reasons:Is that the data in the content primary table is frequently accessed.

 

Horizontal segmentation:

Id modulo

 

9. partitions

A table is stored in different areas of the disk and is still a table.

1. Basic concepts:

There are four partition types after mysql5.1:

(1) Range-this mode allows data to be divided into different ranges. For example, you can divide a table into Several partitions by year.

(2) List (pre-defined List)-This mode allows the system to split data by the pre-defined List Value

(3) Hash-this mode allows you to calculate the Hash Key of one or more columns of a table, and partition the data area corresponding to different values using this Hash code. For example, you can create a table that partitions the primary key of the table.

(4) Key (Key value)-an extension of the Hash mode above. The Hash Key here is generated by the MySQL system.

2 range partition:

Assume that you have created the following table, which stores records of staff of 20 supermarkets. The numbers of these 20 supermarkets are from 1 to 20. if you want to divide it into four small partitions, you can use the range partition. The created data table is as follows.

Syntax for creating a range partition:

Create table emp (id int not null, namevarchar (32) not null default ''comment' employee name', store_id int not null comment 'supermarket number range: 1-20 ') engine myisam charset utf8partition by range (store_id) (partition p0 values less than (6), // is the storage region where the store_id value is smaller than 6. Partition p1 values less than (11), // The storage region where the store_id value is greater than or equal to 6 and less than 11. Partition p2 values less than (16), partition p3 values less than (21) insert into emp values (1, 'haha', 1) -- à data is stored in the p0 partition insert into emp values (23, 'hehaha', 15) -- à data is stored in the p2 partition insert into emp values (100, 'gigiggle ', 11) = à data is stored in p2.

Test whether partition is used when data is retrieved:

When retrieving data, the condition must be partitionby range (store_id) and the field in range.

3. list partitionsSimilar to range partitions

Example: If you create a table that contains records of staff of 20 supermarkets, the numbers of the 20 supermarkets are from 1 to 20. the 20 supermarkets are distributed in 4 regions with the distribution right, as shown in the following table:

create table emp(   id int not null,   name varchar(32) not null default '',   store_id int not null)partition by list(store_id)(   partition p0 values in(5,6,7,8),   partition p1 values in(11,3,12,11),   partition p2 values in(16),   partition p3 values in(21))

Note: When using a partition, the field after where must be a partition field to use the partition.

 

4. Partition Table restrictions

(1) only integer columns in the data table can be partitioned, or data columns can be converted to integer columns through the partition function.

(2) The maximum number of partitions cannot exceed 1024

(3) If a partition column contains a unique index or primary key, the partition column must be included in all unique indexes or primary keys.

(4) partitioning by date is very suitable because many date functions can be used. However, there are not many suitable partition functions for strings.

10. Others

1. select an appropriate storage engine(Myisam innodb)

• 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. (Especially suitable for Forum posts)

• InnoDB: Provides transaction security with commit, rollback, and crash recovery capabilities. However, compared with MyISAM, writing is less efficient and takes up more disk space (innodb is used if security requirements are high ). [Account, points]

• Memory/heap [some data with frequent access, frequent changes, and no need to be written into the database: for example, the user's online status]

• Note: the data in the memory table is in the memory, so the operation speed is fast. However, when mysql is restarted, the data is lost, but the table structure is in.

• Note: From mysql5.5.x, the default storage engine is changed to innodb, which is designed to achieve maximum performance when processing a large amount of data. Its cpu efficiency may be unmatched by any other disk-based relational database engine.

2. Data Type Selection

(1) In applications with high precision requirements, it is recommended to use a fixed number of points to store values to ensure the accuracy of the results. Do not use float for decimal

(2) which type is suitable for storing mobile phone numbers. Assume that we want to use char (11) and how many bytes will be occupied if the character set is utf8. 11*3 = 33. If it is a gbk character set, it occupies 11*2 = 22 bytes,

If bigint type is used for storage, it occupies 8 bytes,

(3) If you want to store IP addresses. If char (15) is used to occupy many bytes, can integer data be used for storage?

You can use a function to convert an IP address to an integer. Int can be used for storage.

Inet_aton (): converts an IP address to an integer.

Inet_ntoa (): converts an integer to an IP address.

During retrieval:

(4) Select the smallest integer type as needed. For example, if a user's online status is offline, online, leaving, busy, or implicit, it can be represented by, or 5. It is not necessary to use char () or varchar () type to store strings.

3. Regular Maintenance of myisam tables

For the myisam storage engine, you need to regularly execute the optimize table name. The optimize table statement can eliminate disk fragments caused by deletion and update, thus reducing the waste of space.

Syntax format: optimizetable table name

Space waste is reduced after fragments are cleared!