MySQL use to touch the way of the table

how MySQL optimizes

rationalization of the design of the table (3NF compliant)

add an appropriate index (index) [Four kinds: normal index, primary key index, unique index unique, full-text index]

SQL statement optimization

Sub-table technology (horizontal split, vertical split)

Read/write [Write: Update/delete/add] Separation

Stored Procedures [Modular programming, can improve speed]

Configuration optimization for mysql [configure max concurrency number My.ini, resize cache]

MySQL server hardware upgrade

timed removal of unwanted data , timed defragmentation (MyISAM)

level Split

It says that vertical slicing only divides tables into different databases by module, but does not solve the problem of single-table big data, and horizontal segmentation is to divide a table into different tables or databases according to some rules. For example, like a billing system, it is more appropriate to divide the table by time, because the system is processing data for a certain period. And like the SaaS application, by the user dimension to divide the data is appropriate, because the user and the user's isolation, generally does not exist to deal with multiple user data, simply by the user_id range of horizontal segmentation

Popular understanding: Split branches horizontally, rows of data are split into different tables, Split columns vertically, table data is split into different tables

Horizontal split Case

idea : In the large e-commerce system, the number of daily Membership is increasing. How to optimize the query after reaching a certain bottleneck .

may be people will think of the index, In the event that users Volume reached on billion level , how to optimize it?

Use split Horizontally Split database tables.

How to using a horizontal split database

Use split Horizontally Split Table , the specific according to Business requirements, and some follow Register time, take and touch, Account Number rules , year and so on.

Code Combat

Project Structure diagram

Package Cn.zhiwei.service;import Org.springframework.beans.factory.annotation.autowired;import Org.springframework.jdbc.core.jdbctemplate;import org.springframework.stereotype.service;/** * First create a new table in the database to store the self-increment ID * CREATE TABLE UUID (id INT UNSIGNED PRIMARY KEY auto_increment) engine=myisam CHARSET UTF8; * Created by Liu Zhiwei on 2018/4/26.    */@Servicepublic class UserService {@Autowired private jdbctemplate jdbctemplate; Divide the table by the primary key ID of the database to divide public boolean marktable (String name) {//1. Gets the custom growth id string idinsertsql = "INSERT I        NTO uuid VALUES (NULL); ";        Jdbctemplate.update (Idinsertsql);        Got three tables to share a self-increment id Long insertid = jdbctemplate.queryforobject ("Select last_insert_id ()", long.class);        Using the modulo algorithm to get the table name table as User0 user1 user2 String table = "User" +insertid% 3;        Add SQL String sql= "insert into" +table+ "values ('" +insertid+ "', '" +name+ "');";        SYSTEM.OUT.PRINTLN ("sql:" + SQL); Execute Add method int update = JDBCTEMPLATE.UPDATE (SQL);    Return update>0?true:false;        }//Sub-table query public string getusername (Long ID) {//Get table name string tableName = "user" + id% 3;        Concatenation query statement String sql = "SELECT name from" + TableName + "where id=" +ID;        SYSTEM.OUT.PRINTLN ("sql:" + SQL);        String name = Jdbctemplate.queryforobject (sql, String.class);    return name; }}

　　

Package Cn.zhiwei.controller;import Cn.zhiwei.service.userservice;import Org.springframework.beans.factory.annotation.autowired;import Org.springframework.web.bind.annotation.requestmapping;import org.springframework.web.bind.annotation.restcontroller;/** * Created by Liu Zhiwei on 2018/4/26. */@RestControllerpublic class Usercontroller {    @Autowired    private userservice userservice;    @RequestMapping ("/insertuser") public    Object Insertuser (String name) {        return userservice.marktable (name);    }    @RequestMapping ("/selectname") public    Object selectname (Long id) {        return userservice.getusername (ID);}    }

　　

Package Cn.zhiwei;import Org.springframework.boot.springapplication;import org.springframework.boot.autoconfigure.springbootapplication;/** * Startup class * Created by Liu Zhiwei on 2018/4/26. */@SpringBootApplicationpublic class APP {public    static void Main (string[] args) {        Springapplication.run ( App.class,args);}    }

　

Page effects

　

MySQL use to touch the way of the table