<>Mysql senior :

<> Indexes :
Index is help Mysql Data structure for efficient data acquisition ( Orderly ) BTREE Indexes : B+ tree ( Multiway balanced search tree )
structure :BTree Also called multiple balanced search tree , One m Forked BTree The characteristics are as follows : 1. Each node in the tree contains at most m Children
2. Root node and leaf node , Each node has at least [ceil(m/2)] Children 3. If the root node is not a leaf node , At least two children 4. All leaf nodes are on the same layer
5. Each non leaf node is represented by n individual key And n+1 Composed of pointers ,[ceil(m/2)-1] <= n <= m-1 ( When n greater than m-1 Hour , Split intermediate node into husband node , Node splitting on both sides )
B+TREE structure : 1.n fork B+Tree Up to n individual key, and BTree Up to n-1 individual key
2.B+Tree The leaf node of saves all key information , according to key Size order 3. All non leaf nodes can be considered as key Index part of
(B+Tree Only leaf nodes are saved key information , Query any key All from root Go to the leaf , High query stability ) Mysql Medium B+Tree:
Mysql The index data structure of B+Tree Optimized , In the original B+Tree Based on , Add a node pointing to an adjacent leaf
Linked list pointer of , With a sequential pointer B+Tree, Improve the performance of interval access Index classification : Single valued index : That is, an index contains only a single column , A table can have multiple single column indexes
unique index : Index column values must be unique , But null values are allowed Composite index : That is, an index contains multiple columns Index syntax : Create index :create [ Index type ] index Index name on
Table name ( field ) View index :show index from Table name (\G Toggle format ) Delete index :drop index Index name on Table name ALTER command :
alter table Table name add [ Index type ] Index name ( field ) Index design principles : 1. High query frequency , Index tables with large data volume
2. Selection of index fields , The best candidate column should be from where Clause 3. unique index , The higher the discrimination , Use indexes more efficiently 4. Index is not more is better 5. Use short index
6. Use leftmost prefix ,N Combined index composed of columns
<> view :View
A view is a virtual table , View does not actually exist in the database , Row and column data from the query that defines the view Tables used , And it is generated dynamically when using views
Advantages of views over normal tables : simple , security , Data independence Create view : create view View name as select sentence Modify view : alter view View name
as select sentence view a chart : show tables; show create view View name Delete view : drop view View name
<> Stored procedures and stored functions :
Stored procedures and functions are segments that have been compiled in advance and stored in a database SQL A collection of statements , The difference between a stored procedure and a function is that the function must have a return value , The stored procedure does not function : Is a process with a return value
process : Is a function that does not return a value Create stored procedure : cteate procedure Stored procedure name () begin SQL sentence end delimiter Delimiter declaration
Call stored procedure : call Stored procedure name () Viewing stored procedures : select name from mysql.proc where db = ' Database name '
Query all stored procedures in the database show procedure status Stored procedure status information show create procedure Stored procedure name
View definition of storage Delete stored procedure : drop procedure Stored procedure name grammar : variable : definition DECLARE declare Variable name int( type )
default ... assignment SET,INTO set Variable name = ... select ... into ... assignment if Conditional judgment : if
search_condition then ... else if ... then ... end if Transfer parameters : create procedure
Stored procedure name ([in/out/inout] Parameter name Parameter type ) ... IN: input parameter ( default ) OUT: Output parameters @ Variable name : Representing user session variables
@@ Variable name : System variables case structure : 1.case value when value then ... else ... end case 2.case when condition
then ... else ... end case while loop : while condition do ... end while repeat loop : repeat
... until condition end repeat loop loop : [label:]loop ... leave [label] end loop [label]
leave sentence : Used to exit from the annotated process construct cursor / cursor : The data type used to store the query result set statement :declare Tour label cursor for ... open
Tour label fetch Tour label into ... close Tour label declare exit ... for not found set ... End of definition
Storage function : create function Function name () returns type begin ... end call :select Function name ()
<> trigger :
Is a database object related to a table , Refers to insert/update/delete Before or after , Trigger and execute trigger
Defined in SQL Statement collection , It can help the application database side to ensure data integrity , Logging , Data verification and other operations Use alias OLD and NEW To reference the changed record contents in the trigger Create trigger :
create trigger Trigger Name before/after insert/update/delete on Table name [for each row] --
row-level trigger begin ... end View triggers :show triggers Delete trigger : drop trigger Trigger Name