This is the 19th day of my participation in the Genwen Challenge

1. Index
Alter table XXX add index indexName/ Add index drop index on Table/Drop indexCopy the code

What is the index? For example, there are ten thousand books in the library, we can use the alphabetic first letter of each word in the title of each book as a sorting rule to build a catalog (index), so as to quickly find the location of the book.

Advantages: improve the query speed;

Disadvantages: Slow down the update speed, why so say, index itself is a table, so will take up storage space, generally, index table takes up 1.5 times the space of the data table; The maintenance and creation of index tables requires time cost, which increases with the increase of data volume. Building an index reduces the efficiency of table modification operations (delete, add, modify) because the table needs to be modified at the same time.

In actual development, we should consider whether to add indexes based on business scenarios and user habits. For example, fields such as date and user_id are frequently searched by users and easily ignored by development.

Two, slow query

Conquer slow query, improve query speed, can build index.

Setting slow query logs:

mysql -uroot -p

show variables like 'slow_query%';

show variables like 'long_query_time';

 slow_query_log   | OFF

set global slow_query_log='ON'; 

set global slow_query_log_file='/var/lib/mysql/test-10-226-slow.log';
Copy the code

Query more than 1 second records

set global long_query_time=1;
Copy the code

Edit the /etc/my.cnf configuration file and add the following content

[mysqld]

**slow_query_log = ON**

**slow_query_log_file = /var/lib/mysql/test-10-226-slow.log**

**long_query_time = 1
Copy the code

Modify the configuration and restart mysql

systemctl restart mysqld

mysql -uroot -p
Copy the code

Verify using the following command

Show variables like 'slow_query%';Copy the code

The slow log query function is permanently enabled in mysql

3. Common query methods

Group by group

Select column name from where group by/having/order by

The left outer join

select * from A left join B on xxx; A all data B table only partial data

Right connection

select * from A right join B on xxx; Table B shows all data, and table A has only partial data

In the connection

select * from A inner join B on xxx; Only the data that A and B exactly match is displayed

The subquery

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = ‘CLARK’)

The order by order

Pick out 10 pieces of data randomly

select top 10 * from tablename order by newid();

Four, paging query

1, not in/top

select top 50 * from pagetest

where id not in (select top 9900 id from pagetest order by id)

order by id

(2) Do not exist

select top 50 * from pagetest

where not exists

(select 1 from (select top 9900 id from pagetest order by id) a where a.id=pagetest.id)

order by id

Avg,sum, Max,min,count,count(*)

AVG: Calculate the average value

The SUM, SUM

MAX: Find the maximum

MIN: Calculates the minimum value

COUNT(*): Returns the number of all rows