MySQL: database entry 5, mysql database entry

MySQL: database entry 5, mysql database entry

1. Storage Engine

Differences between innodb and MyIASM storage engines:
1. innodb is the default storage engine of MySQL 5.5 and later versions, while MyISAM is the default storage engine before MySQL.
2. innodb supports transactions, while MyISAM does not.
3. innodb supports row-level locks, while MyIASM supports concurrent table-level locks.
4. innodb supports foreign keys, while MyIASM does not.
5. innodb and MyIASM storage engines both use B + TREE to store data, but innodb indexes and data are stored in a file. This method is called an aggregate index.
MyIASM creates an index file separately. That is to say, data and indexes are separated.
6. In terms of efficiency, MyISAM is higher than innodb, but innodb is better in performance.
2. Index
1. Accelerated query of common indexes
Create:
Create table t1 (
Id int not null,
Name varchar (50 ),
Index idx_id (id)
)
Use commands to create
CREATE index idx_name on t1 (name );
View Indexes
Show index from t1;
Delete Index
Drop index ide_id on t1;


2. Unique index accelerated query and unique constraints (a null value can be included)
Create table tb2 (
Id int not null auto_increment primary key,
Name varchar (50) not null,
Age int not null,
Unique index idx_age (age)
)

Create unique index idx_age on tb2 (age );

3. primary key index accelerated query and unique constraints (cannot include null)

Alter table fig add primary key (id );

Alter table fig drop primary key;

4. combined index
Create unique index idx_age on tb2 (age, name );

3. aggregate indexes and secondary Indexes
Summarize the differences between the two:
The same is true: Both clustered indexes and secondary indexes are in the form of B + trees, that is, the height is balanced, and the leaf node stores all the data.
The difference is that clustering index leaf nodes store a whole row of information, while secondary index leaf nodes store the information of a single index column.

 

4. How to Use indexes correctly
#1. Range Query (>, >=, <, <= ,! =, Between... and)
#1. = equal sign
Select count (*) from userinfo where id = 1000 -- executes the index, which is highly efficient.

#2.> = <= between... and interval Query
Select count (*) from userinfo where id <100; -- executes the index. The smaller the range, the higher the index efficiency.

Select count (*) from userinfo where id> 100; -- execute the index. The larger the range, the lower the index efficiency.

Select count (*) from userinfo where id between 10 and 500000; -- execute the index. The larger the range, the lower the index efficiency.

#3 .! = Not equal
Select count (*) from userinfo where id! = 1000; -- large index range, low indexing Efficiency


#2. like '% xx %'
# Add an index for the name field
Create index idx_name on userinfo (name );

Select count (*) from userinfo where name like '% xxxx %'; -- Full fuzzy query, low indexing Efficiency
Select count (*) from userinfo where name like '% xxxx'; -- describe the end of a fuzzy query with low indexing Efficiency

# Exception: High indexing usage when like starts
Select * from userinfo where name like 'xxxx % ';

#3. or
Select count (*) from userinfo where id = 12334 or email = 'xxxx'; -- email is not an index field. index this query performs full table scan.

# Exception: If the or condition contains columns with no indexes, the following indexes are used.
Select count (*) from userinfo where id = 12334 or name = 'alex3'; -- when both id and name are indexed fields, the or condition also performs an index.

#4. Use Functions
Select count (*) from userinfo where reverse (name) = '5xela '; -- name index field. When using a function, the index fails.

# Exception: the function can be used for the value corresponding to the index field. We can change it to the following format:
Select count (*) from userinfo where name = reverse ('5xela ');

#5. Inconsistent types
# If the column is of the string type, the input condition must be enclosed by quotation marks. Otherwise...
Select count (*) from userinfo where name = 454;

# Consistent type
Select count (*) from userinfo where name = '201312 ';

#6. order
# If the sorting condition is an index, the select field must also be an index field; otherwise, it cannot hit
Select email from userinfo order by name DESC; -- index hit failure

Select name from userinfo order by name DESC; -- hit Index

# Special: if the primary key is sorted, the speed is still very fast:
Select id from userinfo order by id desc;

5. combined index

Composite Index: it refers to the combination of multiple columns on the table to create an index.

Leftmost matching principle: It takes effect from left to right. If an index is not used in the middle, the index in front of the breakpoint takes effect, and the index after the breakpoint does not;

Select * from mytable where a = 3 and B = 5 and c = 4;
# The three indexes of abc are used in the where condition and play a role.

Select * from mytable where c = 4 and B = 6 and a = 3;
# This statement only indicates that mysql is not so stupid. The condition sequence in the where statement is automatically optimized by mysql before query. The result is the same as that in the previous statement.

Select * from mytable where a = 3 and c = 7;
# A uses indexes and B does not, so c does not use indexes.

Select * from mytable where a = 3 and B> 7 and c = 3;
# A is used, B is used, and c is not used. In this case, B is a range value and a breakpoint, but it uses an index.

Select * from mytable where B = 3 and c = 4;
# Because the index is not used, the bc index is not used here.

Select * from mytable where a> 4 and B = 7 and c = 9;
# A is not used when B is used, and c is not used

Select * from mytable where a = 3 order by B;
# A uses the index, and B also uses the index effect in result sorting.

Select * from mytable where a = 3 order by c;
# A used the index, but c didn't play the sorting effect in this place, because the intermediate breakpoint is

Select * from mytable where B = 3 order by;
# B does not use an index, and a does not play an index in sorting.

6. Notes
1. Avoid using select *
2. in other databases, count (1) or count (column) is used instead of count (*). After the optimization of count (*) in mysql, the efficiency is basically the same as that of the first two.
3. Replace varchar with char whenever possible during table Creation
4. Priority of fields with Fixed sequence length in the table
5. Compound indexes replace Multiple Single-Column indexes (when multiple condition queries are frequently used)
6. Use JOIN instead of subquery (Sub-Queries)
7. Do not JOIN more than four tables)
8. Prioritize connections that can greatly reduce results.
9. Note that the condition types must be consistent during table connection.
10. Index hash values are not suitable for indexing. For example, gender is not suitable.

7. query a plan
The estimated query results are not accurate.
Type: the connection type of the query plan. There are multiple parameters. First, the best type to the worst type is introduced.

Performance: null> system/const> eq_ref> ref> ref_or_null> index_merge> range> index> all


8. Slow log query
Log the SQL statements that affect the database performance on the mysql server to the log file,
By analyzing these special SQL statements, we can improve the database performance.

#. Query slow log configuration information:
Show variables like '% query % ';
#. Modify configuration information
Set global slow_query_log = on;

# Display parameters
Show variables like '% log_queries_not_using_indexes ';
# Activation status
Set global log_queries_not_using_indexes = on;


# Viewing slow log records
Show variables like '% log_output % ';

# Set slow log records in both files and tables
Set global log_output = 'file, table ';

# The query time exceeds 10 seconds and will be recorded in the slow query log.
Select sleep (3) FROM user;

# View logs in a table
Select * from mysql. slow_log;