©2020-2024 ioDraw All rights reserved
<>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
Technology