Introduction aux stratégies d'optimisation des instructions SQL-tutoriel mysql-php.cn

Introduction aux stratégies d'optimisation des instructions SQL

王林

Libérer： 2020-01-28 22:05:33

avant

2296 Les gens l'ont consulté

Introduction aux stratégies d'optimisation des instructions SQL

1. Évitez l'analyse complète de la table

Pour optimiser la requête, essayez d'éviter l'analyse complète de la table. Tout d'abord, déterminez où et commandez par Créer. un index sur les colonnes concernées.

2. Évitez de juger les valeurs nulles

Essayez d'éviter de juger la valeur nulle des champs dans la clause Where, sinon le moteur abandonnera l'utilisation de l'index et effectuez l'analyse complète de la table, telle que :

select id from t where num is null

Copier après la connexion

Vous pouvez définir la valeur par défaut 0 sur num, vous assurer qu'il n'y a pas de valeur nulle dans la colonne num de la table, puis interroger comme ceci :

select id from t where num=0

Copier après la connexion

(Tutoriel vidéo d'apprentissage gratuit recommandé : Tutoriel vidéo mysql)

3. Évitez les jugements de valeur inégaux

<🎜. >

doit être évité autant que possible dans la clause Where != ou l'opérateur <>, sinon le moteur abandonnera l'utilisation de l'index et effectuera une analyse complète de la table.

4. Évitez d'utiliser ou de logique

Vous devriez essayer d'éviter d'utiliser ou dans la clause Where pour connecter des conditions, sinon cela entraînera Le moteur abandonne l'utilisation de l'index et effectue une analyse complète de la table, telle que :

select id from t where num=10 or num=20

Copier après la connexion

peut être interrogé comme ceci :

select id from t where num=10
union all
select id from t where num=20

Copier après la connexion

5. Utiliser dans et non dans la logique avec prudence

in et not in doit également être utilisée avec prudence, sinon cela entraînera une analyse complète de la table, telle que :

select id from t1 where num in(select id from t2 where id > 10)

Copier après la connexion

À ce stade, la requête externe analysera la table entière sans utiliser l'index. Il peut être modifié en :

select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id

Copier après la connexion

À ce stade, l'index est utilisé, ce qui peut améliorer considérablement l'efficacité des requêtes.

6. Faites attention aux requêtes floues

La requête suivante provoquera également une analyse complète de la table :

select id from t where name like &#39;%abc%&#39;

Copier après la connexion

Une requête floue peut être utilisée si elle est une condition nécessaire. Sélectionnez l'identifiant à partir de t où le nom est comme 'abc%' pour implémenter la requête floue, auquel cas l'index sera utilisé. Si la correspondance d'en-tête est une logique nécessaire, il est recommandé d'utiliser un moteur de recherche en texte intégral (Elastic search, Lucene, Solr, etc.).

7. Évitez les calculs de champs dans les conditions de requête

Vous devriez essayer d'éviter les opérations d'expression sur les champs de la clause Where, ce qui entraînera l'abandon du moteur d'utilisation de la clause Where. index. Analyse complète du tableau. Par exemple :

select id from t where num/2=100

Copier après la connexion

doit être remplacé par :

select id from t where num=100*2

Copier après la connexion

8. Évitez d'effectuer des opérations de fonction sur les champs dans les conditions de requête

doit être évité autant autant que possible dans la clause Where Les opérations de fonction sont effectuées sur les champs du champ, ce qui amènera le moteur à abandonner l'utilisation de l'index et à effectuer une analyse complète de la table. Par exemple :

select id from t where substring(name,1,3)=&#39;abc&#39;--name 以abc 开头的id

Copier après la connexion

doit être remplacé par :

select id from t where name like &#39;abc%&#39;

Copier après la connexion

9. Notez le point

sur le côté gauche de la clause WHERE "=". Ne le placez pas dans la sous-section où. N'effectuez pas de fonctions, d'opérations arithmétiques ou d'autres opérations d'expression sur le côté gauche de "=" dans la phrase, sinon le système pourrait ne pas être en mesure d'utiliser l'index correctement.

10. Utilisation de l'index composite

Lors de l'utilisation d'un champ d'index comme condition, si l'index est un index composite, le premier champ de l'index doit être utilisé Ce n'est que lorsqu'il est utilisé comme condition que le système peut utiliser l'index, sinon l'index ne sera pas utilisé et l'ordre des champs doit être autant que possible cohérent avec l'ordre de l'index.

11. Ne définissez pas de requêtes irréprochables

N'écrivez pas de requêtes dénuées de sens. Par exemple, si vous devez générer une structure de table vide :

select col1,col2 into #t from t where 1=0

Copier après la connexion

Ce type de code le fera. ne renvoie rien Le jeu de résultats, mais il consommera des ressources système, doit être modifié comme suit :

create table #t(...)

Copier après la connexion

12 existe

Dans de nombreux cas, c'est le cas. une bonne idée à utiliser existe à la place de dans Select :

select num from a where num in(select num from b)

Copier après la connexion

Remplacez par l'instruction suivante :

select num from a where exists(select 1 from b where num=a.num)

Copier après la connexion

13. L'index peut également échouer

并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段sex，male、female 几乎各一半，那么即使在sex 上建了索引也对查询效率起不了作用。

14、表格字段类型选择

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。尽可能的使用varchar 代替char ，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

15、查询语法中的字段

任何地方都不要使用select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

16、索引无关优化

不使用*、尽量不使用union，union all 等关键字、尽量不使用or 关键字、尽量使用等值判断。表连接建议不超过5 个。如果超过5 个，则考虑表格的设计。（互联网应用中）表连接方式使用外联优于内联。外连接有基础数据存在。

如：A left join B,基础数据是A。A inner join B，没有基础数据的，先使用笛卡尔积完成全连接，在根据连接条件得到内连接结果集。

大数据量级的表格做分页查询时，如果页码数量过大，则使用子查询配合完成分页逻辑。

Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit100000, 10)

Copier après la connexion