How do I prevent SQL injection

  1. The most efficient way is to use a precompiled instruction set
  2. Use regular expressions to filter parameters
  3. String filtering or replacement
  4. The front end determines if there are illegal characters and looks for places where SQL can inject them

Mysql optimization methods

Index optimization (index using B+ tree)

  1. Columns with null values are not indexed
  2. Using short indexes
  3. Index the columns used by the WHERE clause

  4. Create a composite index for multiple columns WHERE or ORDER by
  5. Index = dictionary; index = dictionary; index = dictionary; index = dictionary; index = dictionary; index = dictionary

SQL optimization

  1. Use join statements, use inner join instead of outer join, and use small table to drive large table
  2. Analyze query statements using Explain
  3. Query logs to optimize SQL that is slow to execute
  4. Use limit for ten million pages
  5. Enable caching for frequently used queries
  6. The batch operation

The optimization of the table

  1. The field should be set to not NULL
  2. Fixed field length

Database optimization

  1. Will the table partitioning
  2. Read/write separation/Primary/secondary relationship establishment

The realization principle of Mybatis

According to the configuration -> create SQL sessionFactory -> sqlSession -> Execute the mapped SQL statement -> close the SQLSession.

Pessimistic locks and optimistic locks

The practical application of pessimistic lock and optimistic lock

Optimistic locking

The first assumption is that data conflicts are rare, and validation is performed only when data is submitted for modification, and if there are conflicts, no updates are made.

The usual way to do this is to add a version field, adding the version to each piece of data. Each update is version+1, and the update is accompanied by the version number.

Optimistic locking is relatively common in practice because it provides better concurrent access and less overhead to the database, but there may be dirty reads.

Pessimistic locking

When a transaction locks some data, other transactions can acquire the lock and perform operations only if the current lock commits the transaction and releases the lock.

Pessimistic locks are typically used when concurrency is not high and dirty reads are not allowed. However, it consumes a lot of database resources.

Horizontal split and vertical split database

Horizontal split and vertical split database