Centos7-mysql- index optimization

Index optimization, optimize query speed

Count, count the total rows of a table

The myisam storage engine has its own counter, so it is fast to extract counter values directly when using count.

Innodb needs to scan the whole table when using count, and the efficiency of each row is poor.

Binary multimedia data should not be stored in the database

Large text data should not be stored in the database.

Different SQL statements also affect the efficiency of execution.

Indexes

Explain simulates the query status of statements, providing data [frequently used commands]

My condition is that stuname=gao prompts null because it doesn't create an index in stuname.

Index is a data structure that helps mysql to get data funny.

B-tree B-tree structure

Index reduces IO usage

To create a silver lock, you need to find those with high index value and relatively low index value, for example, gender has no value in creating an index, and there are too many duplicate values.

Index type

1, general index

The most basic index, without any restrictions.

2, unique index

A column of values must be unique, but can be empty null

3, composite index

A combinatorial index means that multiple column values become an index combination, but there is a leftmost prefix. If you want to use a combinatorial index, you must require that the leftmost value in the combinatorial index be included. Otherwise, it will not be used.

4, full-text index

Field types include char, varchar, text,

However, for large data tables, generating a full-text index is a very time-consuming approach to hard disk space.

Indexing commands use the

Create index indexname on table name [which column] General index

Create unique index indexname on table name [column value] unique index unique

Create index indexname on Table name [which column, which column] Combinatorial Index

Create fulltext index indexname on table name [which column] full text index

-

Check the index

Show index from table name

Show keys from table name

Check what the table name is.

-

Sometimes the performance degradation of mysql is the bottleneck of IO. There is no way to do this. Sometimes it can be solved by indexing, and sometimes the hardware configuration can only be updated.