MySQL: Getting Started with the database 5

1. Storage Engine

Differences between INNODB and MYIASM storage engines:
1.innodb is the default storage engine after the mysql5.5 version, and MyISAM is the default storage engine of the previous 5.5 version.
2.innodb supports things, and MyISAM doesn't support things
The 3.INNODB supports row-level locks. and myiasm it supports concurrent table-level locks.
4.INNODB supports foreign keys, while Myiasm does not support foreign keys
Both the 5.INNODB and Myiasm storage engines use B+tree to store data, but InnoDB's indexes and data are stored in a single file, which we call aggregate indexes.
Myiasm, in other words, creates an index file separately, that is, the data is separated from the index
6. MyISAM is higher in efficiency than InnoDB, but innodb better in terms of performance.
2. Index
1. Normal index accelerated query
Create:
CREATE TABLE T1 (
ID int NOT NULL,
Name varchar (50),
Index idx_id (ID)
)
Create by command
CREATE index idx_name on T1 (name);
View Index
Show index from T1;
Delete Index
Drop index ide_id on T1;


2. Unique index acceleration Query and UNIQUE constraint (can contain a null value)
CREATE TABLE TB2 (
ID int NOT NULL auto_increment primary key,
Name varchar (NOT NULL),
The age int is not NULL,
Unique index Idx_age (age)
)

Create unique index idx_age on TB2 (age);

3. Primary key index accelerated queries and UNIQUE constraints (non-nullable)

ALTER TABLE TB3 add primary key (ID);

ALTER TABLE TB3 drop PRIMARY key;

4. Combined Index
Create unique index idx_age on TB2 (age,name);

3. Aggregate indexes and secondary indexes
Summarize the differences:
The same is: whether it is a clustered index or a secondary index, its interior is a B + tree form, that is, the height is balanced, the leaf node holds all the data.
The difference is that the clustered index leaf node holds a whole line of information, while the secondary index leaf node holds a single index column information.

4 How to correctly use the index
#1. Scope query (>, >=, <, <=,! =, Between...and)
#1. = Equal sign
Select COUNT (*) from userinfo where I D = 1000-performs an index with high index efficiency

#2. > >= < <= between...and interval query
Select COUNT (*) from UserInfo where ID & lt;100; --performing an index, the smaller the interval range, the higher the index efficiency

Select COUNT (*) from userinfo where ID >100;--performing an index, the larger the interval range, the lower the index efficiency

Select count (*) from userinfo where ID between and 500000; --performing an index, the larger the range, the lower the efficiency of the index

#3.! = does not equal
Select count (*) from userinfo where id! = 1000;---Large index range, low index efficiency

br> #2. '%xx% '
#为 the Name field to add an index
the CREATE index idx_name on userinfo (name);

Select COUNT (*) from userinfo where name like '%xxxx% ';--full fuzzy query, low index efficiency
Select COUNT (*) from UserInfo where name lik E '%xxxx '; --At what end of the fuzzy query, index efficiency is low

#例外: When the like is used with what begins to index high usage
SELECT * from userinfo where name like ' xxxx% ';

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

#例外: When there are non-indexed columns in the OR condition fail, the following indexes are gone
Select COUNT (*) from userinfo where id = 12334 or name = ' alex3 '; --The OR condition also performs an index when both the ID and name are indexed fields

#4. Using functions
Select COUNT (*) from userinfo where reverse (name) = ' 5xela '; --Name index field, index invalidation when using function

#例外: The value of the indexed field can use the function, we can change the form
Select COUNT (*) from userinfo WHERE name = Reverse (' 5xela ');

#5. Inconsistent type
#如果列是字符串类型, the incoming condition must be enclosed in quotation marks, otherwise ...
Select COUNT (*) from userinfo where name = 454;

#类型一致
Select COUNT (*) from userinfo where name = ' 454 ';

#6. ORDER BY
#排序条件为索引, the Select field must also be an indexed field, otherwise it cannot be hit
Select email from userinfo ORDER by name DESC; --Unable to hit index

Select name from UserInfo ORDER by name DESC; --Hit Index

#特别的: If the primary key is sorted, it is still fast:
Select ID from userinfo ORDER by id DESC;

5. Combined Index

Composite index: Refers to the combination of multiple columns on a table to make an index.

The leftmost matching principle: the left to right to use in effect, if an intermediate index is not used, then the index part before the breakpoint function, the index after the breakpoint does not work;

SELECT * FROM MyTable where a=3 and b=5 and c=4;
#abc三个索引都在where条件里面用到了, and they all played a role.

SELECT * FROM MyTable where c=4 and b=6 and a=3;
#这条语句列出来只想说明 MySQL is not so stupid, where the order of the conditions in the query will be automatically optimized by MySQL, the effect is the same as the previous sentence

SELECT * FROM MyTable where a=3 and c=7;
#a用到索引, B is useless, so C is not used for the index effect

SELECT * FROM MyTable where a=3 and b>7 and c=3;
#a用到了, B is also used, C is not used, this place B is the range value, but also the breakpoint, but the use of the index itself

SELECT * FROM MyTable where b=3 and c=4;
#因为a索引没有使用, so BC doesn't have an index effect here.

SELECT * FROM MyTable where a>4 and b=7 and c=9;
#a用到了 B is not used, C is not used

SELECT * FROM MyTable where a=3 order by B;
#a用到了索引, B also uses the effect of the index in the result sort

SELECT * FROM MyTable where a=3 order by C;
#a用到了索引, but this place C did not play the sorting effect because of the intermittent point of

SELECT * FROM MyTable where b=3 order by A;
#b没有用到索引, sort a also does not have an index effect

6. Precautions
1. Avoid using SELECT *
2. Count (1) or Count (column) is used instead of COUNT (*) in other databases, and COUNT (*) in the MySQL database is optimized to be as efficient as the first two.
3. When creating a table char instead of varchar as far as possible
4. Table field Order fixed-Length field priority
5. Combining indexes instead of multiple single-column indexes (when multiple conditional queries are used frequently)
6. Use connection (join) instead of subquery (sub-queries)
7. Do not have more than 4 tables connected (join)
8. Prioritize those connections that can result in a significant reduction in results.
9. Note that the condition type must be consistent when connecting the table
10. Index hash value does not fit the index, for example: gender inappropriate

7. Query plan
Estimate the results of the query, not very accurate
Type: query plan for connection type, with multiple parameters, first from best type to worst type introduction

Performance: null > System/const > Eq_ref > Ref > Ref_or_null > Index_merge > Range > Index > All


8. Slow log Query
Logs related SQL statements that affect database performance in the MySQL server to a log file,
Through the analysis of these special SQL statements, improved to achieve the purpose of improving the performance of the database.

#. 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 ';
# Open Status
Set global log_queries_not_using_indexes = on;


#查看慢日志记录的方式
Show variables like '%log_output% ';

#设置慢日志在文件和表中同时记录
Set global log_output= ' file,table ';

#查询时间超过10秒就会记录到慢查询日志中
Select Sleep (3) from user;

#查看表中的日志
SELECT * from Mysql.slow_log;

MySQL: Getting Started with the database 5