Qu'est-ce qu'un indice ? Mysql dispose actuellement de plusieurs types d'index principaux

1. Index

L'établissement de l'index MySQL est très important pour le fonctionnement efficace de MySQL. L'index peut grandement améliorer la vitesse de récupération de MySQL.

Par exemple, si MySQL qui est correctement conçu et utilise des index est une Lamborghini, alors MySQL qui n'est pas conçu et utilise des index est un tricycle humain.

Les index sont divisés en index à colonne unique et en index combinés. Un index à une seule colonne signifie qu'un index ne contient qu'une seule colonne. Une table peut avoir plusieurs index à une seule colonne, mais il ne s'agit pas d'un index combiné. Index combiné, c'est-à-dire qu'un index contient plusieurs colonnes.

Lors de la création d'un index, vous devez vous assurer que l'index est une condition appliquée à l'instruction de requête SQL (généralement comme condition de la clause WHERE).

En fait, l'index est aussi une table, qui stocke la clé primaire et les champs d'index et pointe vers les enregistrements de la table d'entité.

Ce qui précède mentionne les avantages de l'utilisation des index, mais une utilisation excessive des index entraînera des abus. Par conséquent, l'index aura également ses inconvénients : bien que l'index améliore considérablement la vitesse des requêtes, il réduira également la vitesse de mise à jour de la table, comme INSERT, UPDATE et DELETE sur la table. Parce que lors de la mise à jour de la table, MySQL doit non seulement enregistrer les données, mais également enregistrer le fichier d'index.

La création de fichiers d'index occupera de l'espace disque.

2. Types d'index

Mysql a actuellement les principaux types d'index suivants : FULLTEXT, HASH, BTREE, RTREE.

1. FULLTEXT

est un index de texte intégral, actuellement pris en charge uniquement par le moteur MyISAM. Il peut être utilisé dans CREATE TABLE, ALTER TABLE et CREATE INDEX, mais actuellement, seuls les index de texte intégral peuvent être créés sur les colonnes CHAR, VARCHAR et TEXT.

L'index de texte intégral n'est pas né avec MyISAM. Il semble résoudre le problème de la faible efficacité des requêtes basées sur du texte flou telles que WHERE name LIKE "%word%".

2. HASH

Parce que HASH est unique (presque 100 % unique) et a une forme semblable à une paire clé-valeur, il est très approprié comme index.

L'index HASH peut être localisé une seule fois et n'a pas besoin d'être recherché couche par couche comme un index arborescent, il est donc extrêmement efficace. Cependant, cette efficacité est conditionnelle, c'est-à-dire qu'elle n'est efficace que dans les conditions "=" et "in", et n'est toujours pas efficace pour les requêtes de plage, le tri et les index combinés.

3. BTREE

L'index BTREE est un moyen de stocker la valeur de l'index dans une structure de données arborescente (arbre binaire) selon un certain algorithme. Chaque requête provient de l'entrée du. arbre. En partant de la racine, parcourez les nœuds dans l’ordre pour obtenir la feuille. Il s'agit du type d'index par défaut et le plus couramment utilisé dans MySQL.

4. RTREE

RTREE est rarement utilisé dans MySQL et ne prend en charge que le type de données géométriques. Les seuls moteurs de stockage prenant en charge ce type sont MyISAM, BDb, InnoDb, NDb et Archive.

Par rapport au BTREE, l'avantage du RTREE est la recherche par plage.

ps. Veuillez consulter cet article de blog pour plus de détails : Différences et applicabilité de plusieurs types d'index Mysql

3 Types d'index

• Index général : uniquement. accélérer la requête

• Index unique : accélérer la requête + la valeur de la colonne est unique (peut avoir null)

• Index de clé primaire : accélérer la requête + colonne Valeur unique (ne peut pas avoir null) + Il n'y a qu'un seul

• index combiné dans le tableau : plusieurs valeurs de colonnes forment un index, spécialement utilisé pour la recherche combinée, et son efficacité est supérieur à la fusion d'index

• Index de texte intégral : segmentez le contenu du texte et recherchez

ps.

Fusion d'index , utilisez plusieurs combinaisons d'index à colonne unique pour rechercher
Couvrant l'index, la colonne de données sélectionnée ne peut être obtenue qu'à partir de l'index sans lire la ligne de données. La colonne doit être couverte par l'index construit

4. Opération d'index

1 Créer un index

--创建普通索引CREATE INDEX index_name ON table_name(col_name);--创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);--创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);--创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

2. structure de la table

ALTER TABLE table_name ADD INDEX index_name(col_name);

3. Spécifiez directement l'index lors de la création de la table

CREATE TABLE table_name (
    ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
);

4.

--直接删除索引DROP INDEX index_name ON table_name;--修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;

5 . Autres commandes associées

- 查看表结构
    desc table_name;
 - 查看生成表的SQL
    show create table table_name;
 - 查看索引
    show index from  table_name;
 - 查看执行时间
    set profiling = 1;
    SQL...
    show profiles;

5. Moment pour créer un index

Jusqu'à présent, nous avons appris comment créer un index, alors dans quelles circonstances devons-nous créer un index ? De manière générale, les colonnes apparaissant dans WHERE et JOIN doivent être indexées, mais ce n'est pas tout à fait vrai car MySQL indexe uniquement <, <=, =, >, >=, BETWEEN, IN et parfois LIKE utilisera le indice. Par exemple :

SELECT t.Name  FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;

A ce moment, il est nécessaire d'indexer la ville et l'âge. Puisque le nom d'utilisateur de la table mytable_m apparaît également dans la clause JOIN, il faut également indexer la ville et l'âge. indexez-le.

Je viens de mentionner que LIKE ne doit être indexé qu'à certains moments. Parce que MySQL n'utilisera pas l'index lors des requêtes commençant par les caractères génériques % et _.

6. Hit index

L'ajout d'un index à la table de la base de données fera effectivement décoller la vitesse des requêtes, mais le principe doit être que l'index est utilisé correctement pour l'interrogation s'il est utilisé. dans le mauvais sens, même s'il est créé, l'indexation ne fonctionnera pas non plus.

Même si l'index est créé, l'index ne prendra pas effet :

- like '%xx'    select * from tb1 where name like '%cn';- 使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';- or    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;- !=    select * from tb1 where name != 'alex'    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123- >    select * from tb1 where name > 'alex'    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123        select * from tb1 where num > 123- order by    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
 - 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引    name                 -- 使用索引    email                -- 不使用索引

7. Autres questions nécessitant une attention

- 避免使用select *- count(1)或count(列) 代替 count(*)- 创建表时尽量时 char 代替 varchar- 表的字段顺序固定长度的字段优先- 组合索引代替多个单列索引（经常使用多个条件查询时）- 尽量使用短索引- 使用连接（JOIN）来代替子查询(Sub-Queries)- 连表时注意条件类型需一致- 索引散列值（重复多）不适合建索引，例：性别不适合

八、LIMIT分页

若需求是每页显示10条数据，如何建立分页？

我们可以先使用LIMIT尝试：

--第一页SELECT * FROM table_name LIMIT 0,10;--第二页SELECT * FROM table_name LIMIT 10,10;--第三页SELECT * FROM table_name LIMIT 20,10;

但是这样做有如下弊端：

• 每一条select语句都会从1遍历至当前位置，若跳转到第100页，则会遍历1000条记录

• 若记录的id不连续，则会出错

改善：

若已知每页的max_id和min_id，则可以通过主键索引来快速定位:

--下一页SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10);--上一页SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10);--当前页之后的某一页SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (页数差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC);--当前页之前的某一页SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (页数差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P) ORDER BY id ASC;

九、执行计划

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化

mysql> explain select * from tb2;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set (0.00 sec)

id查询顺序标识
            如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        ||  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+        特别的：如果使用union连接其值可能为null


    select_type
        查询类型
            SIMPLE          简单查询PRIMARY         最外层查询
            SUBQUERY        映射为子查询
            DERIVED         子查询UNION           联合UNION RESULT    使用联合的结果
            ...table正在访问的表名


    type
        查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/constALL             全表扫描，对于数据表从头到尾找一遍select * from tb1;
                            特别的：如果有limit限制，则找到之后就不再继续向下扫描                                   select * from tb1 where email = &#39;seven@live.com&#39;   select * from tb1 where email = &#39;seven@live.com&#39; limit 1;
                                   虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。INDEX           全索引扫描，对索引从头到尾找一遍select nid from tb1;

            RANGE          对索引列进行范围查找select *  from tb1 where name < &#39;alex&#39;;
                            PS:between andin>   >=  <   <=  操作
                                注意：!= 和 > 符号


            INDEX_MERGE     合并索引，使用多个单列索引搜索select *  from tb1 where name = 'alex' or nid in (11,22,33);

            REF             根据索引查找一个或多个值select *  from tb1 where name = 'seven';

            EQ_REF          连接时使用primary key 或 unique类型select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;



            CONST           常量
                            表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。select nid from tb1 where nid = 2 ;

            SYSTEM          系统
                            表仅有一行(=系统表)。这是const联接类型的一个特例。select * from (select nid from tb1 where nid = 1) as A;
    possible_keys
        可能使用的索引key真实使用的

    key_len
        MySQL中使用索引字节长度

    rows
        mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值extra
        该列包含MySQL解决查询的详细信息
        “Using index”
            此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
        “Using where”
            这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
        “Using temporary”
            这意味着mysql在对查询结果排序时会使用一个临时表。
        “Using filesort”
            这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
        “Range checked for each record(index map: N)”
            这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

上表详解

十、慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，MySQLl数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。 

1. 查看慢日志参数：

--查询配置命令show variables like '%query%';--当前配置参数binlog_rows_query_log_events    OFFft_query_expansion_limit    20have_query_cache    YES--时间限制，超过此时间，则记录long_query_time    10.000000query_alloc_block_size    8192query_cache_limit    1048576query_cache_min_res_unit    4096query_cache_size    1048576query_cache_type    OFFquery_cache_wlock_invalidate    OFFquery_prealloc_size    8192--是否开启慢日志记录slow_query_log    OFF--日志文件slow_query_log_file    D:\Program Files (x86)\mysql-5.7.18-winx64\data\Jack-slow.log--

2. 修改当前配置

set global 变量名 = 值;--例如，修改时间限制为20slong_query_time = 20;

ps.也可以直接打开慢日志配置文件进行修改，但必须重启服务才能生效

3. 查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

 

"""--verbose    版本--debug      调试--help       帮助 -v           版本-d           调试模式-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default              al: average lock time
              ar: average rows sent
              at: average query time
               c: count               l: lock time
               r: rows sent
               t: query time-r           反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)-t NUM       显示前N条just show the top n queries-a           不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配；grep: only consider stmts that include this string
-h HOSTNAME  mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间；don't subtract lock time from total time
"""

 

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!