LIMIT ve Covering Index + Subquery

Örnek

Şöyle yaparız

SELECT t1.name, t1.age, t1.gender, t1.create_time FROM student as t1 
  INNER JOIN 
   (SELECT id FROM student ORDER BY create_time DESC LIMIT 1000000,10) AS t2 ON t1.id = t2.id;

Açıklaması şöyle

The key lies in that the subquery only retrieves the primary key IDs. By taking advantage of the index coverage technique, it first accurately locates a small number of primary key IDs that meet the conditions, and then queries based on these primary key IDs later, which significantly improves the query efficiency and reduces the query cost.

LIMIT ve Seek sayfalama : WHERE koşulu içerir

Giriş

Seek sayfalama aynı zamanda Starting ID Positioning Method olarak ta bilinir. Burada amaç Primary Key için bir where koşulu vermek. Böylece atlanması gereken satır sayısı yöntemi yerine direkt sayfaya gitmek mümkün. Açıklaması şöyle

The Starting ID Positioning Method means specifying a starting ID when using the LIMIT query. And this starting ID is the last ID of the previous query. For example, if the ID of the last record in the previous query is 990000, then we start scanning the table from 990001 and directly skip the first 990000 records, so the query efficiency will be improved.

Örnek

Şöyle yaparız. 

SELECT name, age, grade FROM student where id > 990000 ORDER BY id LIMIT 10;

Örnek

Şöyle yaparız. Burada sayfadaki en son satırın özelliğinden daha büyük olan sonraki satır isteniyor.

SELECT * FROM objects WHERE id >= 100 ORDER BY id LIMIT 3;

EXPLAIN FORMAT

Örnek

Şöyle yaparız

> EXPLAIN FORMAT=JSON INTO @myselect   SELECT name FROM a WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)
​
> SELECT @myselect\G

mysql_native_password

Giriş

1. mysql_native_password

Açıklaması şöyle. mysql_native_password MySQL 9 ile kaldırıldı

Previously, MySQL 5.6/5.7 used mysql_native_password as the default password plugin. The mysql_native_password plugin does not require an encrypted connection.

This plugin is very fast in authentication but not secure enough because it uses the SHA1 algorithm, which has been recommended against by NIST (National Institute of Standards and Technology) due to its vulnerability to attacks.

2. sha256_password 

Daha sonra sha256_password geldi. Açıklaması şöyle

Since MySQL 5.6, a more secure authentication mechanism was introduced: the sha256_password authentication plugin. It uses a salted password for multiple rounds of SHA256 hashing (thousands of rounds), making brute-force attacks much harder and ensuring more secure hash transformations.

However, establishing a secure connection and multiple rounds of hash encryption are time-consuming, offering higher security but slower authentication speed.

3. caching_sha2_password 

Açıklaması şöyle

MySQL aimed to combine the advantages of both methods. In MySQL 8.0.3, a new authentication plugin caching_sha2_password was introduced as an alternative to sha256_password.

Açıklaması şöyle

Since MySQL 8.0.4, the default authentication plugin has been changed from mysql_native_password to caching_sha2_password. Accordingly, libmysqlclient also uses caching_sha2_password as the default authentication mechanism.

CREATE EVENT - Scheduled Task İçindir

Giriş

Açıklaması şöyle

Starting from MySQL 9.0.0, the following Event syntax is available:

CREATE EVENT (create event)

ALTER EVENT (modify event)

DROP EVENT (delete event)

EVENT statements do not support the use of placeholder parameters (?).

CREATE EVENT

İmzası şöyle

CREATE EVENT event_name
    ON SCHEDULE AT or EVERY + specified interval
    DO SQL_statement

Örnek

Şöyle yaparız

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

MySQL Sürümleri

Bazı bilgiler burada

MySQL 8 - April 19, 2018

Altı yıl aradan sonra 

MySQL 9 - July 2, 2024

Soft Delete

Giriş

Açıklaması şöyle

When using the soft delete mechanism on the database, you might run into a situation where a record with a unique constraint was deleted and you want to reuse the unique value.

Virtual Columns

Açıklaması şöyle

If your database does not have partial indexes, e.g. MySQL, you could use a different approach with virtual columns.

Considering that in most SQL databases, the UNIQUE constraint ignores NULL values, we can use a composite key in the UNIQUE constraint that includes a nullable column that indicates if a record was deleted.

To do so, we can add a virtual column not_archived that gets its value from the soft delete column.

Şöyle yaparız

ALTER TABLE users
  ADD not_archived BOOLEAN
  GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) VIRTUAL;

Açıklaması şöyle

This will result in a field that gets automatically updated according to the deleted_at column. It will have the value 1 when the record is not deleted (deleted_at=NULL), else it will be NULL. Now we just need to add it to the UNIQUE constraint. Remember to drop the old constraint.

Şöyle yaparız

ALTER TABLE users
ADD CONSTRAINT UNIQUE (email, not_archived);