On the Database query Optimization of java programmers in interview

background:
1. I am a java programmer
2. Recently interviewed for a senior development position
3. The previous project mainly used less oracle,mysql, and the companies interviewed basically used mysql"s
4. I am weak in the database
5. Recently, I have been watching "High performance mysql"
6. Not only the question of the interview, but also want to improve in this respect
-split line-
question:
will ask questions related to database query optimization and database overall optimization in the interview.

my answer in the interview:
1.select use less * and only use the fields that need to be used
2. Try to use as few associated queries as possible, and make data redundancy for some fields appropriately
3. For some of the most commonly used query fields, you can consider using indexes (the interviewer will usually ask what types of indexes there are, the data structure of the index B-tree, index failure, etc.)
4.mysql can use explain, to check the query performance of sql, through key you can see the index used by the query, type can see whether it is a full table scan or index scan, etc., try to make sql query through the index

.

so I would like to ask you, what are the questions of my answer, from what aspects can I answer, and what can I learn from?

Feb.27,2021

overall optimization, you need to consider disk selection (mechanical, solid state), mysql key parameters such as (buffer pool, transaction log writing mode, storage engine, sub-database and sub-table, read-write separation, slow query log, and so on.

sql statements are mainly indexing, batch insertion, execution plan and HINTS, table association limit, equivalent rewriting, and so on. For
data of statistical type, you can consider calculating the results regularly and exchanging space for time.

most of the above contents have been mentioned in the book "High performance mysql", which is worth reading carefully and verified in practice.


1. Separate tables. Row and column tables.
2. Indexes Single index and combined index
3.sql statement optimization.
mysql optimization is optimized from these three aspects.


SQL query optimization, sub-database and sub-table

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1c1fbbf-327f2.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1c1fbbf-327f2.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?