Interview improvement and daily learning accumulation (ii.)--Database

This article mainly talk about some of MySQL's accumulation and learning experience.

One: Database BasicsInnoDB engine

The InnoDB engine provides support for database acid transactions , and implements four isolation levels for the SQL standard, with information about database transactions and their isolation levels in the article, database transactions and their isolation levels. The engine also provides row-level and foreign-key constraints, which are designed to handle a large-capacity database system, which is itself a complete database system based on the MySQL backend, and the MySQL runtime InnoDB creates buffer pools in memory for buffering data and indexes. However, the engine does not support an index of type Fulltext, and it does not save the number of rows in the table, and the full table needs to be scanned when Select COUNT (*) from table. The engine is of course preferred when it is necessary to use a database transaction. Because the lock granularity is smaller, the write operation does not lock the full table, so using the INNODB engine increases efficiency when concurrency is high. However, using row-level locks is not absolute, and if MySQL does not determine the scope to scan when executing an SQL statement, the INNODB table will also lock the full table.

Myiasm engine

Myiasm is the default engine for MySQL , but it does not provide support for database transactions or row-level and foreign keys, so it is less efficient to write operations that require the entire table to be locked when insert (insert) or update (updated) data. Unlike InnoDB, however, the number of rows in the table is stored in myiasm, so the Select COUNT (*) from table only needs to read the saved values directly and does not require a full table scan. Myiasm is also a good choice if the table reads much more than writes and does not require support for database transactions.

Unique index (clustered index)

A single table can have only one unique index, and the value of the indexed column must be unique, but a null value is allowed. If it is a composite index, the combination of column values must be unique. The data storage structure and index storage structure are in the same order. (General primary key is a unique index)

Creating a single-column index: Create UNIQUE index indexname on tableName (tablecolumns (length)); if it is a Char,varchar type, length can be less than the actual length of the field, if it is a blob and TEXT type, length must be specified.

Creating a composite index: Create UNIQUE index indexname on tableName (' Column1 ', ' column2 ', ' column3 ');

Delete indexes: Drop index indexname;

Nonclustered indexes

A single table can have multiple nonclustered indexes, either a single column or a composite index, regardless of the data storage structure and index storage structure order.

Creating a single-column index: CREATE INDEX IndexName on TableName (tablecolumns (length)); if it is a Char,varchar type, length can be less than the actual length of the field, if it is a blob and TEXT type , length must be specified.

Creating a composite Index: CREATE index IndexName on tableName (' Column1 ', ' column2 ', ' column3 ');

Delete indexes: Drop index indexname;

Combined index

Creating a composite Index: CREATE index IndexName on tableName (' Column1 ', ' column2 ', ' column3 ');

For composite indexes: MySQL left-to-right uses fields from the index, and a query can use only one part of the index, but only the leftmost section.

For example, the index is key index (A,B,C). can support a | a,b| a,b,c 3 combinations to find, but does not support b|c|b,c to find, when the leftmost field is a constant reference, the index is very effective. (combined index up to 2-3 columns)

views and stored Procedures

These two pieces of content can be more specific business needs to build, generally for the query more similar combination of tables, or need to hide the original table information can be combined to provide a query view, for the complex business and needs of special efficiency of the business can provide stored procedures.

Common functions

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -------------------

The following is from please go here to see more details http://www.jb51.net/article/40179.htm

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------------

1. Aggregate functions (in select queries commonly used in GROUP BY clauses)
Avg (COL) returns the average of the specified column
Count (COL) returns the number of non-null values in the specified column
Min (col) returns the minimum value of the specified column
Max (COL) returns the maximum value of the specified column
SUM (COL) returns the sum of all values of the specified column
Group_concat (COL) Returns the result of a combination of column value connections belonging to a group

2. String functions
CONCAT (S1,S2...,SN) to concatenate s1,s2...,sn into a string
Concat_ws (SEP,S1,S2...,SN) joins the S1,S2...,SN into a string and uses the Sep character interval
LCASE (str) or lower (str) Returns the result of changing all characters in the string str to lowercase
REVERSE (str) Returns the result of reversing the string str

3. Date and Time functions
Curdate () or current_date () returns the current date
Curtime () or Current_time () returns the current time
Date_add (date,interval int keyword) returns the date plus the result of the interval int (int must be formatted according to the keyword), such as: Selectdate_add (Current_date,interval 6 MONTH);
Date_format (DATE,FMT) formats date values according to the specified FMT format
date_sub (date,interval int keyword) returns the date plus the result of the interval int (int must be formatted according to the keyword), such as: Selectdate_sub (Current_date,interval 6 MONTH);
DAYOFWEEK (date) returns the day ordinal of the week represented by date (1~7)
DayOfMonth (date) Return date is the day of the one month (1~31)
DayOfYear (date) returns the day ordinal of a year (1~366)
Dayname (date) returns the weekday name of date, such as SELECT dayname (current_date);
From_unixtime (TS,FMT) formats UNIX timestamp TS According to the specified FMT format
HOUR (time) returns the hour value (0~23)
MINUTE (time) returns the minute value of time (0~59)
Month (date) returns the months value of date (1~12)
MONTHNAME (date) returns the month name of date, such as: SELECT MONTHNAME (current_date);
Now () returns the current date and time
QUARTER (date) Returns a date in the quarter of the year (1~4), such as Select QUARTER (current_date);
WEEK (date) Returns a date of the week ordinal of a year (0~53)
Year (date) returns the date of day (1000~9999)

4. Control Flow function
MySQL has 4 functions for conditional operation, which can implement the conditional logic of SQL, allowing the developer to transform some application business logic into the database background.

MySQL Control flow function:
Case When[test1] then [RESULT1] ... else [default] End If TESTN is true, returns RESULTN, otherwise returns the default
case [Test] when[val1) Then [result] ... else [Default]end returns RESULTN if test and valn are equal, otherwise returns the default
if (test,t,f) if test is true, returns T;
Ifnull (ARG1,ARG2) If arg1 is not empty, return arg1, otherwise return arg2
Nullif (ARG1,ARG2) If ARG1=ARG2 returns NULL, otherwise returns ARG1
The first of these functions is ifnull (), which has two parameters and is judged on the first parameter. If the first argument is not NULL, the function returns the first argument to the caller, and if it is null, the second argument is returned.

The code is as follows:

SELECT case ' green '
When the ' red ' then ' stop '
When the ' green ' then ' go ' END;
SELECT Case 9 If 1 then ' a ' is 2 Then ' B ' ELSE ' n/a ' END;
SELECT case When (=4) "OK" when (+/-) <>4 then ' not OK ' END asstatus;
SELECT name,if ((IsActive = 1), ' activated ', ' inactive ') as RESULT fromuserlogininfo;
SELECT Fname,lname, (math+sci+lit) as Total,
Case when (Math+sci+lit) < ' D '
When (Math+sci+lit) between and "C"
When (Math+sci+lit) between 151 and "B"
ELSE ' A ' END
As grade from Marks;
SELECT IF (ENCRYPT (' Sue ', ' ts ') =upass, ' Allow ', ' deny ') as Loginresultfrom users WHERE uname = ' Sue '; #一个登陆验证

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----------------------

Second: Database design

In the official study this part is to conform to the three-paradigm rule, but this rule for most of the data business is in theory is too complete, not very practical, so you can learn the idea of this design, but the applicability can be in accordance with their own business planning.

The process of designing a database can be referred to in the following directions (personal summary):

1. First analyze your business, abstract good data objects and the association between objects.

2. For some common data fields can be summed up, such as creation time, update time, the operator ... These are common fields.

3. For their own business to analyze the part of the query, for more repeated queries, fewer opportunities to modify the field can be appropriately redundant in more than one table, to improve query and simplify the complexity of SQL code.

4. When building a table, you can consider the extensibility of the late business, but do not have to force out the fields, etc., because the business changes more mundane, too many reservations will make the table looks very chaotic.

5. When business scalability and project requirements conflict, the project needs can be prioritized, as far as scalability can be seen in terms of business development and even modification. (personal opinion, can not be ignored)

6. Do the field description, notes.

Three: SQL optimization (just the direction and principle of optimization)

1. Cascade query Optimization, multiple single-standard query optimization, you can consider the multi-table Cascade query optimization.

2. Consider the optimization of index usage, for SQL that destroys the index and does not use the index, you can consider indexing or optimizing the existing index in effect.

3. Optimization of large data volume paging.

4. By analyzing the business, use more criteria to identify fewer columns.

Four: Database schema

1. For big data and read-write pressure business can be considered in several directions to design

table partition :http://www.cnblogs.com/freeton/p/4265228.html This site is well written and worth a visit.

Sub-table storage : According to business requirements, there are two different techniques, horizontal and vertical.

A horizontal table is a rule that divides a large table horizontally into different sub-tables, so that the columns of each table are the same.

A vertical table is a large table that divides different columns into different sub-tables according to different business requirements, associating them with an identical attribute (example ID).

A query after a table can be logically judged by the business and then positioned to a child table. For some statistical functions, all child tables are looped and merged.

Sub-Library technology : is to use a different database deployment database cluster, you can search Mycat middleware.

read/write separation : http://blog.csdn.net/zoomyj/article/details/50610349 This article wrote a lot of interested can learn more about, as an understanding and accumulation.

Summary: Fragmented wrote some, here is mainly a number of directional things, there may be no specific implementation or code. So just a guideline, hope to help everyone in these areas to learn, to accumulate. There are some things that the internet extracts, as long as it is good to learn it.

The above application for 1-3 years Java Development interview, personal opinion, worthy of reference, feel the use of the handy recommendation, let more people see

Finally share a good chicken soup for those who want to be a friend, wish everyone success! Http://www.iheima.com/zixun/2017/0628/163820.shtml

Interview improvement and daily learning accumulation (ii.)--Database