A complete set of MySQL tutorials - MySQL hot interview questions

Course content introduction

Install mysql on linux

Start and log in to mysql

Index overview

Advantages and disadvantages

data structure

Data structure-BTREE

Data structure-B+TREE

Index classification

Index syntax

Index design principles

Overview

Create and modify views

View and delete views

Stored procedures overview

Create call query delete syntax

variable

if judgment

Input parameters

Output parameters

case structure

while loop

repeat loop

loop loop

Cursor introduction

Basic cursor operations

Loop to get cursor

function

Introduction to triggers

Trigger creation and application

View and delete triggers

Content summary

Architecture

Storage engine overview

Storage engine features

InnoDB characteristics

MyISAM features

Memory and Merge features

Storage engine selection principles

SQL execution frequency

Locate inefficient SQL

Introduction to explain command

explain id

explain之select_type

explain之table type

key rows extra

show profile

trace tool

Verify index to improve query efficiency

Match all values

leftmost prefix rule

Failure conditions (range query, field operation)

covering index

Index failure

like fuzzy matching

Full table scan is faster

Judgment of NULL value

in和not in

Single column index and compound index selection

View index usage

Insert data in bulk

insert optimization

orderby optimization

group by optimization

subquery optimization

or optimize

limit optimization

index hint

Content summary

Application optimization

Query caching-overview and process

Query cache-configuration parameters

Query cache-enable query cache

Query cache—SELECT options

Query cache - failure scenario

Optimization principles

MyISAM memory optimization

InnoDB memory optimization

Concurrency parameter adjustment

Overview and classification of locks

mysql support for locks

MyISAM table lock-read lock

MyISAM table lock - write lock

Summary of MyISAM table locks

MyISAM table lock-view lock contention

InnoDB row lock-introduction and background knowledge

InnoDB row lock-type

InnoDB row lock-basic demo

InnoDB row lock-row lock upgraded to table lock

InnoDB row lock-gap lock hazards

InnoDB row lock-contention view

InnoDB row lock-summary

SQL execution order and regular expressions

Numeric and string functions

Date functions and aggregate functions

Course content

Commonly used tools mysql

Commonly used tools-mysqladmin

Commonly used tools-mysqlbinlog and mysqldump

Commonly used tools-mysqlimport and source

Commonly used tools mysqlshow

error log

Binary log

Binary log (row and log deletion)

Query log

Slow query log

Copy principle

Cluster construction

Requirements and environment preparation

Basic project import

AOP logging

Log query backend - mapper interface

Log query backend——Service

Log query - front end

System performance optimization analysis

System performance optimization - paging optimization

System performance optimization - index optimization

Read-Write Separation Overview

Data source configuration

System performance optimization - AOP switching data source

System performance optimization - AOP switching data source 2

System performance optimization - application optimization