MySQL Military (reprint)

Lead

• Practical experience from the frontline
• There's a bloody lesson behind every military rule.
• Do not be gorgeous, as long as practical
• If there's one that benefits you, you'll be comforting.
• Primarily for database developers

Always after the disaster, only remember the importance of disaster tolerance is always after the loss, only to recall that there have been reminders of the catalogue one, the core military (5) Two, field class military (6) Three, the Index class military (5) Four, the SQL class military (15) five, the Agreement class military (5) One, the core military as far

• Don't let your toes think about things.
• It's the responsibility of the brain seeds.
• Let the database do what she's good at.
• • Try not to do operations in the database
  • Complex operation moved to terminal CPU
  • Apply MySQL as simple as possible
• Example: MD5 ()/ORDER by RAND ()

Control the amount of single-table data

• Single-table data volume estimates in a year
• • Pure int no more than 1000w
  • No more than 500w with Char
• Reasonable sub-table not overloaded
• • Userid
  • DATE
  • Area
  • ...
• Recommended library no more than 300-400 tables

Keep your table slim

• table field number few but good
• • IO efficient, full table traversal, table repair fast, improve concurrency, ALTER TABLE fast
• How many fields are appropriate for a single table?
• Single table 1 g volume 500w row evaluation
• • Sequential read of 1G files takes n seconds
  • Single line not exceeding 200byte
  • No more than 50 pure int fields in a single table
  • No more than 20 char (10) fields in a single table
• The number of single table fields is controlled in 20-50

Balance Paradigm and redundancy

• Balance is the art of the door
• • Strictly follow the three main paradigms?
  • Efficiency first, improve performance
  • There's no absolute right or wrong.
  • Sacrifice paradigm when appropriate, add redundancy
  • But it increases the complexity of the code.

Reject 3B

• Database concurrency like city traffic
• • Nonlinear growth
• Reject 3B
• • Large SQL (Big SQL)
  • Large business (BIG transaction)
  • Mass (Big Batch)
• Detailed analysis See after

Core Military Military Summary

• Try not to do operations in the database
• Control the amount of single-table data
• Keep your table slim
• Balance Paradigm and redundancy
• Reject 3B

Second, field class military use good numeric field type

• Three kinds of numeric types
• • TINYINT (1 bytes)
  • SMALLINT (2B)
  • Mediumint (3B)
  • INT (4B), BIGINT (8B)
  • FLOAT (4B), DOUBLE (8B)
  • DECIMAL (M,D)

Convert a character to a number

• Numeric vs. String-type Index
• • More efficient
  • Faster queries
  • Takes up less space
• Example: Storing an IP with an unsigned int, not char (15)
• • INT UNSIGNED
  • Inet_aton ()
  • Inet_ntoa ()

Use Enum or set precedence

• Use Enum or set precedence
• • String
  • Possible values known and limited
• Store
• • Enum takes 1 bytes and is converted to numeric operations
  • Set view node, up to 8 bytes
  • Need to add ' single quotation mark (even numeric value) when comparing
• Example
• • ' Sex ' enum (' F ', ' M ') COMMENT ' gender '
  • ' C1 ' enum (' 0 ', ' 1 ', ' 2 ', ' 3 ') COMMENT ' Job reference review '

Avoid using null fields

• Avoid using null fields
• • Query optimization is difficult
  • NULL column index, additional space required
  • Invalid index with NULL compliance
• Example
• • ' A ' char (+) DEFAULT NULL
  • ' B ' int (ten) not NULL
  • ' C ' int (ten) not NULL DEFAULT 0

Use less and divide Text/blob

• Text type processing performance is much lower than varchar
• • Force generation of hard disk temporary tables
  • Waste more space
  • VARCHAR (65535) ==>64k (note UTF-8)
• Try not to TEXT/BLOB data types
• Split to a separate table if you must use it
• Example: CREATE TABLE T1 (id INT NOT NULL auto_increment, data text NOT NULL, PRIMARY KEY (ID)) Engine=innodb;

Not in the database stored Picture field class Military summary

• Use a good numeric field type
• Convert a character to a number
• Precedence using enumeration Enum/set
• Avoid using null fields
• Use less and divide Text/blob
• Do not save pictures in database

Third, the index class is prudent and reasonable to add an index

• Carefully and reasonably add an index
• • Improve query
  • Slowing down Updates
  • Not as many indexes as possible.
• Can not add the index as far as possible
• • Comprehensive assessment of data density and data distribution
  • It's best not to exceed the number of fields 20%
• overriding index with core SQL precedence
• Example
• • Do not create an index for the gender column

Character fields must be prefixed to index

• Degree of differentiation
• • Single letter degree of differentiation: 26
  • 4 Letter Sensitivity: 26*26*26*26=456,,976
  • 5 Letter Sensitivity: 26^5=11,881,376
  • 6 Letter Sensitivity: 26^6=308,915,776
• Character fields must be prefixed to index
• • ' Pinyin ' varchar (+) default null comment ' cell pinyin ', key ' idx_pinyin ' (' Pinyin ' (8))) Engine=innodb
• Do not perform mathematical operations or function operations on indexed columns
• • Unable to use index
  • Causes full table Scan
• Example
• • Bad:select * FROM table where To_days (current_date)-to_days (date_col) <=10
  • Good:select * FROM table where date_col>=date_sub (' 2011-10-22 ', interval)

Self-increment column or global ID do innodb primary key

• To create a clustered index on a primary key
• Instead, the index stores the primary key value
• Primary key should not update modifications
• Case Increment Insert Value
• Avoid using the string master key
• Clustered index Splitting
• A business-independent auto_increment column or global ID generator is recommended as the proxy primary key
• If you do not specify a primary key, InnoDB uses a unique and non-null value index instead

Try not to use foreign keys

• On-line OLTP system (offline system also)
• • Foreign key to save development amount
  • With extra overhead
  • Progressive operation
  • Can ' reach ' other tables, meaning locks
  • Easy deadlock when high concurrency
• Constraint guaranteed by program

Index Class Military Summary

• Carefully and reasonably add an index
• Character fields must be prefixed to index
• Do not perform operations on indexed columns
• Self-increment column or global ID do innodb primary key
• Try not to use foreign keys

Four, the SQL class is as simple as the SQL statement

• Big SQL vs multiple Simple SQL
• • Traditional design Ideas
  • But MySQL not
  • A SQL Grace can be operated on a CPU
  • 5000+qps high concurrency, 1 seconds big SQL means?
  • Maybe a big SL will block the entire database.
• Reject large SQL and disassemble into multiple simple SQL
• • Simple SQL cache Hit ratio higher
  • Reduced lock table time, especially MyISAM
  • Using multiple CPUs

Keep transactions (connections) short

• Keep the transaction/db connected and dapper
• • Transaction/Connection Usage principles: ready-to-use, out-of-the-box
  • Transaction-independent operations are placed outside the transaction to reduce the lock resource footprint
  • Use multiple short transactions instead of long transactions without compromising consistency
• Example
• • Upload a picture when posting a wait
  • A large number of sleep connections

Avoid using Sp/trig/func as much as possible

• On-line OLTP system (offline library separately)
• • Use as few stored procedures as possible
  • Use as few triggers as possible
  • Use the MySQL function to process the results
• Is the responsibility of the client program

Try not to use SELECT *

• When using SELECT *
• • Consume more CPU, memory, IO, network bandwidth
  • Request all columns first to the database, and then discard the unwanted columns?
• Try not to use select*, only take data columns
• • Safer design: Reduce the impact of table changes
  • Provides possibilities for using covering index
  • Select/join reduce the disk temporary table generation, especially when there is Text/blob
• Example
• • SELECT * FROM tag where id=999184
  • Select keyword from tag where id=999184

Overwrite or is in ()

• Same field, change or to In ()
• • or efficiency: O (n)
  • In efficiency: O (log n)
  • When n is large, or is much slower
• Note the number of controls in, recommended N less than 200
• Example
• • SELECT * from opp where phone = ' 123456 ' or hple = ' 1235516 '
  • SELECT * from OPP where phone in (' 123456 ', ' 1235516 ')

Overwrite or to Union

• Different fields, change or to Union
• • Reduce "or" queries on different fields
  • The merge index tends to be mentally retarded
  • If you have enough confidence: set global optimizer_switch= ' Index_merge=off '
• Example
• • SELECT * Frmo opp where phone= ' 010-88886666 ' or cellphone= ' 13800138000 '
  • SELECT * from opp where phone= ' 010-88886666 ' union select * from OPP where cellphone= ' 13800138000 '

Avoid negative queries and% prefixes for fuzzy queries

• Avoid negative queries
• • not,! =, <>,!<,!>, not exists, not in, no like, etc.
• Rice% prefix fuzzy query
• • B + Tree
  • Cannot use index
  • Causes full table Scan
• Example
• • Mysql> SELECT * from post where title like ' Beijing% ';
  • 298 rows in Set (0.01SEC)
  • Mysql> SELECT * from post where title like '% Beijing ';
  • 572 rows in Set (3.27SEC)

Several examples of count (*)

• A few interesting examples:
• • Count (Col) VS Count (*)
  • COUNT (*) VS count (1)
  • COUNT (1) vs count (0) vs count (100)
• Conclusion
• • COUNT (*) = count (1)
  • COUNT (0) = count (1)
  • COUNT (1) = count (100)
  • COUNT (*)! = count (col)
  • Why?

Reduce COUNT (*)

• MyISAM VS InnoDB
• • Without where count ()
  • with where COUNT ()
• The resource cost of COUNT (*) is large, try not to use less
• Count statistics
• • Real-time statistics: with Memcache, two-way update, the morning run benchmark
  • Non-real-time statistics: As far as possible with separate tables, periodic re-calculation

Limit efficient Paging

• Traditional paging
• • SELECT * FROM table limit 10000, 10;
• Limit principle:
• • Limit 10000,10
  • The larger the offset, the slower the
• Recommended Paging:
• • SELECT * FROM table where id>=23423 limit 11; #10 +1 (10 articles per page)
  • SELECT * FROM table where id>=23434 limit 11;

Efficient paging of limit

• Paging Method Two:
• • SELECT * FROM table where ID >= (select id from table limit 10000,1) limit 10;
• Paging Method Three:
• • SELECT * FROM table inner joins (SELECT ID from table limit 10000,1) using (ID);
• Paging mode four:
• • The program takes id:select ID from table limit 10000, 10;
  • SELECT * FROM table where ID in (123,456,...)
• You may want to analyze and reorganize the index by scene

Efficient paging of limit

• Example
• • Mysql> Select Sql_no_cache * from post limit 10, 10;
  • Ten row in Set (0.01SEC)
  • Mysql> Select Sql_no_cache * from post limit 20000, 10;
  • Ten row in Set (0.13SEC)
  • Mysql> Select Sql_no_cache * from post limit 80000, 10;
  • Rows in Set (0.58SEC)
  • Mysql> Select Sql_no_cache ID from post limit 80000, 10;
  • Rows in Set (0.02SEC)
  • Mysql> Select Sql_no_cache * from post where id>=323423 limit 10;
  • Rows in Set (0.01SEC)
  • Mysql> SELECT * from post where ID >= (select Sql_no_cache ID from post limit 80000,1) limit 10;
  • Rows in Set (0.02SEC)

Use UNION all rather than union

• If the result does not need to be de-weighed, the union all
• • The Union has a go-to-weight overhead
• Example
• • Mysql> SELECT * from detail20091128 UNION all
  • SELECT * FROM detail20110427 UNION ALL
  • SELECT * FROM detail20110426 UNION ALL
  • SELECT * FROM detail20110425 UNION ALL
  • SELECT * FROM detail20110424 UNION ALL
  • SELECT * from detail20110423;

Split connection guarantees high concurrency

• High concurrent DB is not recommended for joins with more than two tables
• Properly decomposed connections ensure high concurrency
• • Can be converted to large amounts of early data
  • Multiple MyISAM tables are used
  • Small ID in () for large tables
  • Connection references the same table multiple times
• Example
• • • Mysql> SELECT * from tag joins Tag_post N tag_post.tag_id=tag.id join post on tag_post.post_id=post.id where tag.tag= ' pre-owned Toys
    • -
    • Mysql> SELECT * from tab where tag= ' secondhand toys ';
    • Mysql> SELECT * from Tag_post where tag_id=1321;
    • Mysql> SELECT * from post where post.id in (123,456,314,141);

GROUP by removal sort

• GROUP by implementation
• • Group
  • Automatic sorting
• No sorting required: ORDER by null
• Specific sort: Group by DESC/ASC
• Example
• Mysql> Select Phone, COUNT (*) from the post group by phone limit 1;
• 1 row in Set (2.19SEC)
• Mysql> Select Phone,count (*) from the Post group by phone order by null limit 1;
• 1 row in Set (2.02SEC)

Comparison of data types with column planting

• Principle: Number to number, character to character
• Values are listed in the character type comparison
• • Simultaneous conversion to double precision
  • To do the comparison
• Comparison of character columns and numeric types
• • Character column integer column to value
  • Do not use index queries

Comparison of data types with column planting

• Example: Comparison of character columns and numeric types
• • Field: ' Remark ' varchar (NOT NULL comment ' remark, default is null ');
  • mysql> select ' id ', ' gift_code ' from Gift where ' deal_id ' = 640 and remark=115127;
  • 1 row in Set (0.14SEC)
  • mysql> select ' id ', ' gift_code ' from Pool_gift where ' deal_id ' =640 and remark= ' 115127 ';
  • 1 row in Set (0.005SEC)

Load Data Guide

• Fast import of bulk data:
• • Batch loading is faster than single-row loading, and does not require every cache refresh
  • Loading peso loading is faster without indexing
  • Insert values, values, values reduce index refresh
  • Load data is about 20 times times faster than insert
• Try not to Insert...select
• • Delay
  • Synchronization error

Break up Bulk update                                &NBS P                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp    

• Large volume update early morning operation, avoid peak
• No restrictions in the wee hours
• Daytime on-line default is 100 bar/second (special re-discussion)
• Example:
• • Update post set tag=1 where ID in (A/n);
  • Sleep 0.01;
  • Update post set tag=1 where ID in (4,5,6);
  • Sleep 0.01;
  • ...

Know every SQL

• Show profile
• Mysqlsla
• Mysqldumpslow
• Explain
• Show Slow Log
• Show Processlist
• Show Query_response_time (Percona)

SQL Class Military Summary

• SQL statements as simple as possible
• Keep transactions (connections) short
• Avoid using Sp/trig/func as much as possible
• Try not to select *
• Overwrite or statement
• Avoid negative queries and% prefixes for fuzzy queries
• Reduce COUNT (*)
• Efficient paging of limit
• Use UNION all rather than union
• Split connection guarantees high concurrency
• Group BY removal sort
• Comparison of system data types
• Load Data Guide
• Breaking up large batch updates
• Know every SQL

Five, agreed class military isolation line under

• The ecological environment of building database
• • Developing wireless on-Library operation permissions
• Principle: On-line connection, offline under line
• • Real-time Data library
  • SIM Library for simulated environments
  • QA Library for testing
  • Dev Library for development
• Case

Prohibit subqueries without DBA confirmation

• MySQL Sub-query
• • Poor optimization in most cases
  • Special where summarize subqueries using in ID
  • Generally available with join overwrite
• Example:
• Mysql> SELECT * FROM table 1 where ID in (select ID from table2); mysql> INSERT INTO table1 (SELECT * from table2); May cause replication exceptions

Never explicitly lock on the program side

• Never explicitly lock the database on the terminal
• • External lock to database is not controllable
  • High concurrency is a disaster
  • Extremely difficult to debug and troubleshoot
• Concurrent debit and other conformance issues
• • Adoption of transactions
  • Relative Value modification
  • Commit the first two check conflicts

Unified Character Set is UTF8

• Character
• • MySQL4.1 used to be only latin1.
  • Adding multiple character sets for multi-language support
  • Also brings more n problems
  • Keep it simple
• Unified Character Set: UTF8
• Proofing rules: Utf8_general_ci
• Garbled: Set names UTF8

Unified Naming conventions

• The name of the library table is uniform in lowercase
• • Linux VS windows
  • MySQL Library table case sensitive
  • Field name is not case sensitive
• Index naming defaults to "idx_ field name"
• The library name is abbreviated, try to 2~7 a letter
• • Datasharing==>ds
• Caution Avoid naming with reserved words
• ...

The Convention class is smaller.

• Isolation line Offline
• Prohibit subqueries on-line without DBA confirmation
• Never explicitly lock on the program side
• Unified Character Set is UTF8
• Unified Naming conventions

Reprinted from http://blog.csdn.net/heximing1991/article/details/46827679

MySQL Military (reprint)