Secrets unknown to mysql _ MySQL

Unknown mysql secret bitsCN.com

Create two tables first.

1. student table

2. grade table

I. mysql replication technology

1. copy tables and data-> synchronize table structures and data

Create table desttable select * from srctable;

(Desttable: Target table, srctable: Original table)

2. copy the table structure-> only synchronize the table structure

Create table desttable select * from srctable where 0> 1;

Think about why ???

The select statement generates both the table structure and the result set. if the following restriction conditions are not true, the result set is empty and only the table structure is copied.

3. copy the full table record-> Insert all records of one table into another table

Insert into desttable select * from srctable;

4. copy some fields

Insert into desttable (Field 1, field 2...) select (Field 1, field 2...) from srctable;

II. Index considerations

(1): You can use the FULLTEXT parameter to set the index to full-TEXT indexes. full-TEXT indexes can only be created on CHAR, VARCHAR, and TEXT fields. -> However, only the MyISAM storage engine supports full-text indexing.

(2): Multi-column index: an index is created for multiple column fields in the table. However, the index is used only when the first field of these fields is queried.

(3): the query statement uses the like keyword for query. if the first character of the match is "%", the index will not be used.

Select * from student where num like '% 4'; // The index will not be used

Select * from student where num like '4% '; // The index will be used.

(4): When the or keyword is used in a query statement, the index is used only when the columns with the or and both conditions are indexed.

(5): learn to use the explain command to check whether the index is used. We use the explain command to check (2 ).

Create an index first: create index index_nu_name on student (num, name );

#1. explain select * from student where num = 2;

The execution result is as follows:

#2. explain select * from student where name = 'lwy ';

The execution result is as follows:

Through comparison, we found that the second possible-key and key are empty, and Extra shows that the where clause does not use indexes for queries.

Let's take a look at the rows information. if the where clause is used, the number of rows to be queried is 2. if the index is used, the number of rows to be queried is 1 (this table has only 2 records, it can be imagined that if there are tens of thousands of records, the index is really fast)

Therefore, our (2) statement is verified.

III. permission management

Permission Assignment in mysql is based on the user table, db table, tables_priv table, and columns_priv table. In the database system,

First, judge whether the value in the user table is Y. If it is Y, you do not need to check the following table. If the user table is N, check db tables, tables_priv tables, and columns_priv tables in sequence.

Simply put, a user table stores permissions on all databases, a db table stores permissions on a database, and tables_privs stores permissions on database tables, columns_privs stores the column permissions in the table.

For example, we create a new user:

#1. create user 'test1' identified by 'admin'; // Note: the user name and password must be enclosed in quotation marks. why?-> string

Note the following: create user 'test2' @ localhost identified by 'admin ';

What are the differences between the two statements?

With the localhost ID, the user can only connect to the mysql database locally, but not remotely. You can remotely connect to the mysql database without localhsot.

The notable difference is that the Host field in the user table is localhost and the Host field is %.

In this case, we connected to mysql using the test1 user and found that the connection was successful. we used the command show databases to find that only the information_schema database was available. why is there this database ??

The information_schema database comes with MySQL, which provides a way to access database metadata. What is metadata? Metadata is data about data,

Such as database name or table name, column data type, or access permission. In some cases, other terms used to express this information include "data dictionary" and "system Directory ". In MySQL,

Regard information_schema as a database, specifically an information database. Information about all other databases maintained by the MySQL server is saved.

Such as the database name, database table, and data type and access permissions in the table column. There are several read-only tables in information_schema. They are actually views, not basic tables,

Therefore, you will not be able to see any related files.

We can use the root user to check that all permissions in the user table are N, which means nothing can be done.

Grant the user the following permissions: grant seelct, update on *. * to test1;

# *. *: The first * indicates the database, and the second * indicates the table. Therefore, *. * indicates all tables of all databases.

In this case, the values of the select_priv and update_priv fields in the user table are changed to Y.

Then, use the test1 user to connect to the mysql database and show database. all databases can be viewed and updated.

#2. authorize test2 again: grant select, update on mysql. * to test2; // You can only query and update all mysql tables.

In this case, we will find that all permissions of user test2 in the user table are N, while the value of user test2 in the db table for select_priv and update_priv of mysql database is Y.

The permissions of the tables_priv table and columns_priv table are analyzed by yourself.

After the above analysis, we have a deeper understanding of mysql permissions.

IV. mysql logs

There are four types of mysql logs:

#1. binary file: records database operations in binary format, but does not record query statements

#2. error log: records mysql startup, shutdown, and running errors. it is enabled by default and cannot be stopped.

#3. general query log: records user logon and query information

#4. slow query log: record the operation that exceeds the specified time

By default, only the error log function is enabled, and the others are set as needed by the administrator.

# Restore a database using binary logs

Because the binary file stores the statement used to update the database, it can be used to restore the database.

Mysqlbinlog filename. number | mysql-uroot-p // mysqlbinlog is used to open a binary file

When you use mysqlbinlog to restore a database, the database must be restored first when the number (number) is small.

V. Performance Optimization

#1. performance parameters:

Show status; // query mysql Performance parameters

Show status like 'value'; // view a parameter

Common parameters are described as follows:

Connections: number of times the mysql server is connected

Uptime: mysql server launch time

Slow_queries: number of slow queries

Com_select: Number of query operations

Com_insert: Number of Insert operations

Com_update: Number of update operations

Com_delete: number of delete operations

#2. optimize query

#2.1 use explain and describe to analyze query statements

Note: When using indexes to query records, pay attention to the usage of indexes-> See the notes for using indexes above.

We will analyze the explain we used above

Explain select * from student where num = 2;

The execution result is as follows:

1. id: Number of the select statement

2. select_type: select statement type

# Simple: simple query, excluding connection query and subquery

# Primary: The primary query or the outermost query statement.

# Union: the second or subsequent query statement of the connection query

2. table: the queried table

4. type: Table connection type

# System: there is only one record in the table.

# Const: The table contains multiple records, but only one record is queried from the table.

# All: Complete table scan

# Eq_ref: when multiple tables are connected, the following tables use the union or primary key.

# Ref: A common index is used later for multi-table join.

# Unique_subquery: the subquery uses the unique or primary key.

# Index_subquery: the subquery uses a common index.

# Range: the query statement provides the query range.

# Index: the index in the table is completely scanned.

5. possible_keys: possible indexes used in the query

6. key: index used for query

7. key_len: Index field length

8. ref: indicates the column or constant used to query records with the index.

9. rows: Number of queried rows

10: extra: query additional information

The describe statement is used in the same way as the explain statement.

#2.2 optimize subqueries

Use Connection queries instead of subqueries

Why?

During the subquery, mysql needs to create a temporary table for the inner-layer query results, and then query the outer-layer query in the temporary table. after the query, the temporary table needs to be revoked.

Connection queries do not need to create temporary tables, so they are faster than subqueries.

#2.3 optimize the insert record speed

When a record is inserted, the index and uniqueness check all affect the speed of the inserted record. In addition, it takes different times to insert multiple records at a time.

1. disable index

When data is inserted, mysql sorts the inserted records based on the table index to reduce the insertion speed. To solve this problem, you can disable the index before inserting the record, and enable the index after inserting the record.

Alter table name disable keys; // disable the index

Alter table name enable keys; // enable index

2. disable the uniqueness check.

When data is inserted, mysql checks the uniqueness of the inserted records, reducing the insertion speed.

Set unique_checks = 0; // disable uniqueness

Set unique_checks = 1; // enable uniqueness

3. optimize insert statements

Insert into table name values

(......),

(......);

Insert into table name values (......);

Insert into table name values (......);

In the preceding two insert methods, the first method is faster than the second method in database connection and other operations.

#2.3 Analysis Table, checklist, and optimization table

Analysis Table: analyze the distribution of keywords

Checklist: check whether an error exists.

Optimize tables: eliminate space waste caused by deletion or update.

When the following three operations are performed, the mysql database adds a read-only lock to the table. only records can be read, and records cannot be updated or inserted.

1. Analysis Table

Analyze table name 1 [, table name 2...];

2. Checklist

Check table name 1 [, table name 2...] [option];

Option has five parameters: quick, fast, changed, medium, extended-> lower execution efficiency

--> The option is only valid for tables of the MyISAM type.

3. optimize the table

Optimize table name 1 [, table name 2...];

--> Only the varchar, blob, and text fields in the table can be optimized.

If a table uses data types such as text or blob, updating, deleting, and so on will result in a waste of disk space. After the operation is completed, the previously allocated disk space will not be automatically reclaimed.

Optimize can be used to fragment the disk.

Below are some basic mysql usage commands.

I. View basic commands in mysql

(1): check which databases are currently available

Show databases;

(2): Use the mysql database

Use test;

(3): View tables in the current database

Show tables;

(4): view the commands created in the preceding grade table.

Show create table grade;

(5): view the structure of the student table

Desc student;

(6): view the storage engines supported by the database

Show engines;

Show engines/G; // G to make the results more beautiful

(7): view the default storage engine

Show variables like 'Storage _ engine ';

II. modify a table in mysql

(1) change the course field data type of the grade table to varchar (20)

Alter table grade modify course varchar (20 );

(2) change the s_num field to the front of course.

Alter table grade modify s_num int (10) after id;

(3) rename the grade field to score

Alter table grade change grade score varchar (10 );

(4) delete the foreign key constraint of grade

Alter table grade drop foreign key grade_fk;

(5) change the storage engine of grade to INnoDB

Alter grade engine = INnoDB;

(6) delete the student address field

Alter table student drop address;

(7) add the phone field in the student table

Alter table student add phone int (10 );

(8) change the table name of grade to gradeinfo.

Lter table grade rename gradeinfo;

(9): delete the student table

Drop table student; // The parent table studnet can be deleted only because the foreign key has been previously deleted.

BitsCN.com