[MySQL 5.0.21] Requête tourne dans le vide

**dpin33** · 28/10/2008, 16h52

Bonjour,

Je rencontre depuis quelques temps des blocages sur une requête SQL particulière.

La plupart du temps, cette requête se remet à fonctionner lorsque je fais une optimisation de mes tables (elle aboutit en général en moins d'une minute) mais depuis quelques jours, cela ne suffit plus. La requête se bloque en cours d'exécution (en fait, elle tourne dans le vide pendant des heures) et je ne comprends pas pourquoi.

Voici le résultat de show processlist sur le serveur montrant que la requête tourne toujours mais est bloquée :

mysql> show processlist \G;
*************************** 1. row ***************************
Id: 814
User: GSYS
Host: localhost:3961
db: gsysreport
Command: Query
Time: 24722
State: Sending data
Info: select articles.ClefFournisseur as 'ClefFournisseur',EXTRACT(YEAR_MONTH FROM caisse.DatePiece) as 'D

La requête est donc lancée depuis 24722 secondes, et le compteur continue de tourner mais elle n'aboutit pas et peut rester dans cet état pendant plusieurs jours...

Ma base de données est très grosse (plus de 4.5 Go en tout), et les tables concernées par cette requête ont beaucoup d'enregistrements.
table articles : 130 320 enreg
table caisse : 3 146 137 enreg
table caisselignes : 12 092 002 enreg

J'utilise MySQL 5.0.21. Mes tables sont toutes en InnoDB.
La requête en question me sert en fait à faire des consolidations la nuit dans une nouvelle table afin de prémâcher le travail et que les interrogations portent sur cette nouvelle table consolidée.

Voici la requête complète et la structure des tables utilisées.

Requête :
=======

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
select articles.ClefFournisseur as 'ClefFournisseur',
EXTRACT(YEAR_MONTH FROM caisse.DatePiece) as 'DatePiece',
SUM(caisselignes.PrixTotalHorsTaxe) as 'CAHT',
count(distinct(caisse.ClefCaisse)) as 'NbTickets',
SUM(caisselignes.Quantite) as 'Qte',
(SUM(caisselignes.PrixTotalHorsTaxe))-(SUM((articles.PrixUnitaireAchat)*(caisselignes.Quantite))) as 'Marge',
((SUM(caisselignes.PrixTotalHorsTaxe))-(SUM((articles.PrixUnitaireAchat)*(caisselignes.Quantite))))/(SUM((articles.PrixUnitaireAchat)*(caisselignes.Quantite)))*100 as 'MargePourc'
from caisse,caisselignes,articles
where (caisse.ClefCaisse=caisselignes.ClefCaisse) and
(caisselignes.ClefArticle=articles.ClefArticle) and
(caisse.NumeroSite='1') and
(EXTRACT(YEAR_MONTH FROM caisse.DatePiece)='200805')
group by articles.ClefFournisseur,EXTRACT(YEAR_MONTH FROM caisse.DatePiece)

Tables utilisées :
=============
structure des tables exportée via phpmyadmin :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE caisse (
  ClefCaisse varchar(22) NOT NULL default '',
  ClefClient varchar(15) NOT NULL default '',
  NomClient varchar(35) default NULL,
  NumeroSite varchar(5) default NULL,
  DatePiece date NOT NULL default '0000-00-00',
  PrixTotalHT decimal(19,4) default NULL,
  NetAPayer decimal(19,4) default NULL,
  ArticlesVendus decimal(19,4) default NULL,
  PRIMARY KEY  (ClefCaisse),
  KEY ClefClient (ClefClient),
  KEY DatePiece (DatePiece)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE articles (
  ClefArticle varchar(13) NOT NULL default '',
  ClefFournisseur varchar(8) NOT NULL default '',
  ClefFamille varchar(8) NOT NULL default '',
  DesignationArticle varchar(50) default NULL,
  CodeFamille varchar(8) default NULL,
  DateCreation date default NULL,
  DateModification date default NULL,
  CodeFournisseur varchar(8) default NULL,
  PrixUnitaireAchat decimal(19,4) default NULL,
  PrixUnitaireVente_1 decimal(19,4) default NULL,
  PRIMARY KEY  (ClefArticle),
  KEY ClefFournisseur (ClefFournisseur),
  KEY ClefFamille (ClefFamille)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE caisselignes (
  ClefPrimaire bigint(20) NOT NULL auto_increment,
  ClefCaisse varchar(22) NOT NULL default '',
  ClefArticle varchar(13) NOT NULL default '',
  Quantite decimal(19,4) default NULL,
  PrixUnitaireHorsTaxe decimal(19,4) default NULL,
  PrixTotalHorsTaxe decimal(19,4) default NULL,
  PRIMARY KEY  (ClefPrimaire),
  KEY ClefCaisse (ClefCaisse),
  KEY ClefArticle (ClefArticle)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Je me pose la question de savoir si ce ne sont pas les fonctions spéciales EXTRACT(YEAR_MONTH FROM ...) qui lui posent problème. Avez-vous déjà utilisé ces fonctions ? Sont-elles très gourmandes en mémoire ?

J'ai aussi essayé de modifier ma requête en remplaçant EXTRACT(YEAR_MONTH FROM .caisse.DatePiece) par DATE_FORMAT(caisse.DatePiece,'%Y%m'), mais cela ne change rien.

Avez-vous une autre idée sur ce qui pourrait bloquer cette requête ?

Merci d'avance pour votre aide car je ne sais vraiment pas comment résoudre ce problème récurrent.

Pour info :
=======

explain ajouté au début de ma requête indique :

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: caisse
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 2735156
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: caisselignes
type: ref
possible_keys: ClefCaisse,ClefArticle
key: ClefCaisse
key_len: 24
ref: gsysreport.caisse.ClefCaisse
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: articles
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 15
ref: gsysreport.caisselignes.ClefArticle
rows: 1
Extra:

donc apparemment ma requête n'est pas optimisée pour la table caisse puisque le type retourné est ALL mais je ne vois pas comment faire autrement...

mysql> show variables;
| Variable_name | Value
| auto_increment_increment | 1
| auto_increment_offset | 1
| automatic_sp_privileges |ON
| back_log | 50
| basedir | C:\Program Files\MySQL\MySQL Server 5.0
| binlog_cache_size | 32768
| bulk_insert_buffer_size | 8388608
| character_set_client | latin1
| character_set_connection | latin1
| character_set_database | latin1
| character_set_filesystem | binary
| character_set_results | latin1
| character_set_server | latin1
| character_set_system | utf8
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
| collation_connection | latin1_swedish_ci
| collation_database | latin1_swedish_ci
| collation_server | latin1_swedish_ci
| completion_type | 0
| concurrent_insert | 1
| connect_timeout | 5
| datadir | E:\MYSQL\V5-0\data\
| date_format | %Y-%m-%d
| datetime_format | %Y-%m-%d %H:%i:%s
| default_week_format | 0
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| div_precision_increment | 4
| engine_condition_pushdown | OFF
| expire_logs_days | 0
| flush | OFF
| flush_time | 1800
| ft_boolean_syntax | + -><()~*:""&|
| ft_max_word_len | 84
| ft_min_word_len | 4
| ft_query_expansion_limit | 20
| ft_stopword_file | (built-in)
| group_concat_max_len | 1024
| have_archive | YES
| have_bdb | NO
| have_blackhole_engine | NO
| have_compress | YES
| have_crypt | NO
| have_csv | NO
| have_example_engine | NO
| have_federated_engine | NO
| have_geometry | YES
| have_innodb | YES
| have_isam | NO
| have_ndbcluster | NO
| have_openssl | DISABLED
| have_query_cache | YES
| have_raid | NO
| have_rtree_keys | YES
| have_symlink | YES
| init_connect | | init_file |
| init_slave |
| innodb_additional_mem_pool_size | 2097152
| innodb_autoextend_increment | 8
| innodb_buffer_pool_awe_mem_mb | 0
| innodb_buffer_pool_size | 49283072
| innodb_checksums | ON
| innodb_commit_concurrency | 0
| innodb_concurrency_tickets | 500
| innodb_data_file_path | ibdata1:10M:autoextend
| innodb_data_home_dir |
| innodb_doublewrite | ON
| innodb_fast_shutdown | 1
| innodb_file_io_threads | 4
| innodb_file_per_table | ON
| innodb_flush_log_at_trx_commit | 1
| innodb_flush_method
| innodb_force_recovery | 0
| innodb_lock_wait_timeout | 50
| innodb_locks_unsafe_for_binlog | OFF
| innodb_log_arch_dir
| innodb_log_archive | OFF
| innodb_log_buffer_size | 1048576
| innodb_log_file_size | 25165824
| innodb_log_files_in_group | 2
| innodb_log_group_home_dir | .\
| innodb_max_dirty_pages_pct | 90
| innodb_max_purge_lag | 0
| innodb_mirrored_log_groups | 1
| innodb_open_files | 300
| innodb_support_xa | ON
| innodb_sync_spin_loops | 20
| innodb_table_locks | ON
| innodb_thread_concurrency | 10
| innodb_thread_sleep_delay | 10000
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 26214400
| key_cache_age_threshold | 300
| key_cache_block_size | 1024
| key_cache_division_limit | 100
| language C:\Program Files\MySQL\MySQL Server 5.0\share\english\
| large_files_support | ON
| large_page_size | 0
| large_pages | OFF
| license | GPL | local_infile | ON
| log | OFF
| log_bin | ON
| log_bin_trust_function_creators | OFF
| log_error | .\HTTPDIN2.err
| log_slave_updates | OFF
| log_slow_queries | OFF
| log_warnings | 1
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_file_system | OFF
| lower_case_table_names | 1
| max_allowed_packet | 1048576
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connect_errors | 10
| max_connections | 100
| max_delayed_threads | 20
| max_error_count | 64
| max_heap_table_size | 16777216
| max_insert_delayed_threads | 20
| max_join_size | 4294967295
| max_length_for_sort_data | 1024
| max_prepared_stmt_count | 16382
| max_relay_log_size | 0
| max_seeks_for_key | 4294967295
| max_sort_length | 1024
| max_sp_recursion_depth | 0
| max_tmp_tables | 32
| max_user_connections | 0
| max_write_lock_count | 4294967295
| multi_range_count | 256
| myisam_data_pointer_size | 6
| myisam_max_sort_file_size | 107374182400
| myisam_recover_options | OFF
| myisam_repair_threads | 1
| myisam_sort_buffer_size | 36700160
| myisam_stats_method | nulls_unequal
| named_pipe | OFF
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| new | OFF
| old_passwords | OFF
| open_files_limit | 622
| optimizer_prune_level | 1
| optimizer_search_depth | 62
| pid_file | E:\MYSQL\V5-0\data\HTTPDIN2.pid
| prepared_stmt_count | 0
| port | 3306
| preload_buffer_size | 32768
| protocol_version | 10
| query_alloc_block_size | 8192
| query_cache_limit | 1048576
| query_cache_min_res_unit | 4096
| query_cache_size | 0
| query_cache_type | ON
| query_cache_wlock_invalidate | OFF
| query_prealloc_size | 8192
| range_alloc_block_size | 2048
| read_buffer_size | 61440
| read_only | OFF
| read_rnd_buffer_size | 258048
| relay_log_purge | ON
| relay_log_space_limit | 0
| rpl_recovery_rank | 0
| secure_auth | OFF
| shared_memory | OFF
| shared_memory_base_name | MYSQL
| server_id | 1
| skip_external_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slave_compressed_protocol | OFF
| slave_load_tmpdir | F:\__Temp\
| slave_net_timeout | 3600
| slave_skip_errors | OFF
| slave_transaction_retries | 10
| slow_launch_time | 2
| sort_buffer_size | 262136
| sql_mode | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | sql_notes | ON
| sql_warnings | ON
| storage_engine | InnoDB
| sync_binlog | 0
| sync_frm | ON
| system_time_zone | Paris, Madrid (heure d'
| table_cache | 256
| table_lock_wait_timeout | 50
| table_type | InnoDB
| thread_cache_size | 8
| thread_stack | 196608
| time_format | %H:%i:%s
| time_zone | SYSTEM
| timed_mutexes | OFF
| tmp_table_size | 18874368
| tmpdir |
| transaction_alloc_block_size | 8192
| transaction_prealloc_size | 4096
| tx_isolation | REPEATABLE-READ
| updatable_views_with_limit | YES
| version | 5.0.21-community-nt-log
| version_comment | MySQL Community Edition (GPL)
| version_compile_machine | ia32
| version_compile_os | Win32
| wait_timeout | 28800
-------------------------+
211 rows in set (0.08 sec)

mysql> show status;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 16 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 460686 |
| Bytes_received | 1042 |
| Bytes_sent | 17796 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_kill | 3 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 14 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 6 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 2 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 960 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 3 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3 |
| Handler_read_key | 36 |
| Handler_read_next | 15368616 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 424 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 552 |
| Innodb_buffer_pool_pages_data | 3007 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 826745 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 4 |
| Innodb_buffer_pool_pages_misc | 1 |
| Innodb_buffer_pool_pages_total | 3008 |
| Innodb_buffer_pool_read_ahead_rnd | 130718 |
| Innodb_buffer_pool_read_ahead_seq | 29803 |
| Innodb_buffer_pool_read_requests | 588037026 |
| Innodb_buffer_pool_reads | 39475318 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 112983605 |
| Innodb_data_fsyncs | 967949 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 472190976 |
| Innodb_data_reads | 46690332 |
| Innodb_data_writes | 1792602 |
| Innodb_data_written | 2271550464 |
| Innodb_dblwr_pages_written | 826745 |
| Innodb_dblwr_writes | 10598 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 10208274 |
| Innodb_log_writes | 941779 |
| Innodb_os_log_fsyncs | 938277 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 949083648 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 251834 |
| Innodb_pages_read | 46690324 |
| Innodb_pages_written | 826745 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 15167 |
| Innodb_rows_inserted | 19028152 |
| Innodb_rows_read | 169490217 |
| Innodb_rows_updated | 361217 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 22484 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 10.499000 |
| Max_used_connections | 7 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 3 |
| Open_streams | 0 |
| Open_tables | 6 |
| Opened_tables | 4 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 928756 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 6 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 928700 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 4 |
| Threads_connected | 3 |
| Threads_created | 7 |
| Threads_running | 3 |
| Uptime | 308902 |
+-----------------------------------+------------+
245 rows in set (0.08 sec)

Merci pour votre aide.

Cordialement,

Delphine

**Waldar** · 28/10/2008, 17h01

Essayez dans votre where de modifier la condition sur la date :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(EXTRACT(YEAR_MONTH FROM caisse.DatePiece)='200805')

Par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
caisse.DatePiece >= cast('2008-05-01' as datetime)
AND caisse.DatePiece < cast('2008-06-01' as datetime)

Je ne connais pas les syntaxes MySQL donc mes formats sont très certainement invalides.

Mais en l'état actuel, vous n'utilisez pas d'index sur DatePiece ce qui est dommage. Enfin si vous avez un tel index !

**dpin33** · 28/10/2008, 17h25

Bonjour,

Merci pour cette astuce, la syntaxe est correcte et ma requête est plus rapide (l'index sur DatePiece est désormais utilisé!).

L'utilisation de la fonction explain sur ma requête indique aussi une forte amélioration en nombre de lignes traitées :

id: 1
select_type: SIMPLE
table: caisse
type: range
possible_keys: PRIMARY,DatePiece
key: DatePiece
key_len: 3
ref: NULL
rows: 230724
Extra: Using where; Using temporary; Using filesort

Je vais donc essayer de changer mon code d'intégration de la nuit pour voir si cela bloque toujours ou pas...

PS : avez-vous une explication sur le fait qu'une requête puisse tourner dans le vide comme ça ? Est-ce que c'est parce qu'il y a trop d'enregistrements à traiter et qu'elle prend donc beaucoup de temps ? Car j'ai l'impression que ce n'est pas seulement qu'elle est très longue, elle tourne vraiment dans le vide car je l'ai déjà laissée tourner 2 jours et elle n'était toujours pas arrivée au bout...

Cordialement,

Delphine

**Waldar** · 28/10/2008, 18h43

Envoyé par dpin33

PS : avez-vous une explication sur le fait qu'une requête puisse tourner dans le vide comme ça ? Est-ce que c'est parce qu'il y a trop d'enregistrements à traiter et qu'elle prend donc beaucoup de temps ? Car j'ai l'impression que ce n'est pas seulement qu'elle est très longue, elle tourne vraiment dans le vide car je l'ai déjà laissée tourner 2 jours et elle n'était toujours pas arrivée au bout...

Je ne peux malheureusement pas vous répondre car il y a énormément de paramètres en jeu, puissance machine, multithreading, puissance et utilisation du réseau, gestion des I/O sur les disques dur et cetera, sans parler de toutes les configurations logicielles au niveau du SGBD, de l'OS, des drivers de connexion...
Toutes ces variables crééent plusieurs points d'inflexion à partir duquel on commence à pédaler dans la choucroute.

jnore · 28/10/2008, 21h32

Il faut bien indéxer aussi les jointures ainsi que les colonnes qui sont l'objet de filtres.
Je n'ai pas regardé toute ta config mais il est possible de mettre un index sur ton champ date.

Cdlt

**dpin33** · 30/10/2008, 16h14

Bonjour,

Tous les champs utilisés dans mes jointures sont bien indexés.

En ce qui concerne les champs qui font l'objet de filtre, NumeroSite n'était pas indexé, je l'ai donc ajouté et j'ai encore gagné en temps d'exécution !

Mon intégration de cette nuit s'est correctement déroulée, je pense que ces modifications lui ont fait du bien !

Cependant, le résultat de la fonction explain donne désormais :

id: 1
select_type: SIMPLE
table: caisse
type: ref
possible_keys: PRIMARY,DatePiece,NumeroSite
key: NumeroSite
key_len: 8
ref: const
rows: 263444
Extra: Using where; Using temporary; Using filesort

Pour mémoire, avant d'avoir indexé NumeroSite, j'avais :

id: 1
select_type: SIMPLE
table: caisse
type: range
possible_keys: PRIMARY,DatePiece
key: DatePiece
key_len: 3
ref: NULL
rows: 230724
Extra: Using where; Using temporary; Using filesort

Je constate donc que le nombre de rows a finalement augmenté depuis que NumeroSite est indexé, donc a priori cela semble moins bon.
Mais pourtant mon temps d'exécution de la requête est plus rapide ! Et d'après la doc trouvée sur http://dev.mysql.com/doc/refman/5.0/fr/explain.html, le fait d'avoir un type "ref" est pourtant bien meilleur que le type "range".

Comment faut-il donc interpréter le résultat du explain ?

De plus, existe-t-il un moyen de connaitre la taille des tables temporaires qui seront utilisées pour voir si j'y gagne effectivement ?

Merci pour votre aide précieuse.

Cordialement,

Delphine

**dpin33** · 30/10/2008, 16h24

Rebonjour,

J'ai oublié de préciser à waldar que ce que je trouve étrange aussi c'est que le fait de lancer un optimize table extended sur mes tables résoud la situation la plupart du temps, et du coup la requête passe en moins d'1 minute alors qu'elle pouvait tourner 2 jours juste avant...

Je crois que le optimize table remet les index en place, ce qui signifierait que ma table n'était plus indexée correctement, mais comment cela se peut-il ? Est-ce qu'il peut s'agir d'un problème de limite de fichier tampon atteint ? Comment gérer ces fichiers tampons ou tables temporaires ?

Merci pour vos réponses.

Delphine

**CinePhil** · 30/10/2008, 16h32

Je ne sais pas s'il est possible de toucher à la structure des tables mais je constate des éléments qui peuvent pénaliser les performances :
- les clés primaires des tables 'caisse' et 'articles' sont des VARCHAR. Il vaudrait mieux qu'elles soient de type INT UNSIGNED AUTO_INCREMENT
- si la 'ClefClient' de la table 'caisse' est une clé étrangère vers la table client, elle devrait aussi être issue d'une clé primaire entière auto_incrémentée donc être de type entier. De plus dans ce cas, le nom du client est inutile dans la table caisse
- idem pour les autres clefs de la table articles
- pourquoi 4 décimales sur les prix ?

L'indexation sur des clés entières est beaucoup plus rapide que sur des CHAR ou VARCHAR. Et vu le volume de données, la différence doit être sensible.

**dpin33** · 31/10/2008, 09h21

Bonjour,

Effectivement, en remplaçant les varchar par des unsigned int, ce serait plus performant, mais je ne peux hélas pas toucher à la structure de la base...

Cordialement,

Delphine

**SQLpro** · 01/11/2008, 18h56

Quel nombre incroyable d'horreur dans cette requête !

1) les alias ne doivent pas avoir d'apostrophe
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SUM(CL.PrixTotalHorsTaxe) AS 'CAHT'

non ! Faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SUM(CL.PrixTotalHorsTaxe) AS CAHT,

2) utilisez des alias de table, ceci allège votre requête et la rend plus lisible :
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FROM caisse,caisselignes,articles

non ! faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FROM caisse AS C, caisselignes AS CL,articles AS A

et utilisez ces alias partout dans les autres clauses.

3) faites des jointures avec des JOIN / ON
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
FROM caisse,caisselignes,articles
WHERE (caisse.ClefCaisse=caisselignes.ClefCaisse) AND
(caisselignes.ClefArticle=articles.ClefArticle) AND
(caisse.NumeroSite='1') AND

non ! faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
FROM   caisse AS C
       INNER JOIN caisselignes AS CL
             ON C.ClefCaisse = CL.ClefCaisse
       INNER JOIN articles AS A
             ON CL.ClefArticle = A.ClefArticle

4) lorsqu'une valeur est passée en paramètre et qu'elle est numérique il ne faut pas d'apostrophe !
Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE  caisse .NumeroSite = '1'

non ! faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE  caisse .NumeroSite = 1

Sinon aucun index ne peut être utilisé, car transtypage implicite

Vouloir optimiser une requête commence par savoir écrire proprement les requêtes les plus classique !

Quelques règles basique sur l'optimisation maintenant :
1) vos clefs étrangères (a supposer que vous sachiez ce que cela est) doivent être indexées
2) vos colonnes recherchées avec un argument de recherche doivent être indexées
3) si vous avez les deux (clefs étrangères + colonnes recherchez) placez une index couvrant
4) n'utilisez AUCUNE fonction. Une fonction empêche l'utilisation des index
5) ne demandez pas des données lorsque vous les connaissez d'avance !

Et voici maintenant votre requête récrite :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT A.ClefFournisseur AS 'ClefFournisseur',
--       EXTRACT(YEAR_MONTH FROM C.DatePiece) AS 'DatePiece', --> STUPIDE CAR VOUS LE CONNAISSEZ, remplacez le par :
       '200805' AS DatePiece
       SUM(CL.PrixTotalHorsTaxe) AS 'CAHT',
       count(DISTINCT(C.ClefCaisse)) AS 'NbTickets',
       SUM(CL.Quantite) AS 'Qte',
       (SUM(CL.PrixTotalHorsTaxe)) - (SUM((A.PrixUnitaireAchat) * (CL.Quantite))) AS 'Marge',
       ((SUM(CL.PrixTotalHorsTaxe)) - (SUM((A.PrixUnitaireAchat) * (CL.Quantite)))) / (SUM((A.PrixUnitaireAchat) * (CL.Quantite))) * 100 AS 'MargePourc'
FROM   caisse AS C
       INNER JOIN caisselignes AS CL
             ON C.ClefCaisse = CL.ClefCaisse
       INNER JOIN articles AS A
             ON CL.ClefArticle = A.ClefArticle
-- WHERE  C.NumeroSite = '1' --> c'est un nombre, pas d'apostrophe !!! Sinon l'index ne peut être utilisé !!!, remplacez par :
WHERE  C.NumeroSite = 1
  -- AND  EXTRACT(YEAR_MONTH FROM C.DatePiece) = '200805' --> évitez les fonctions, on ne peut les optimiser, remplacez par :
  AND   C.DatePiece BETWEEN '20080501' AND '20080531'
GROUP BY A.ClefFournisseur

Bref, commencez par un cours sur SQL. Mon bouquin, comme mon site web peuvent vous y aider !

Et sur l'optimisation des requêtes, lisez ce que j'ai écrit :
http://sqlpro.developpez.com/optimisation/mediane/
http://sqlpro.developpez.com/optimisation/indexation/
http://blog.developpez.com/sqlpro?ti..._oui_mais_quoi

A +

**dpin33** · 06/11/2008, 09h38

Bonjour Frédéric,

Je vous remercie pour toutes ces précisions.

Je viens de reprendre le projet qui a été écrit par mon collègue précédent, ce n'est donc pas moi qui ait créé cette requête ;-)

D'autre part, comme je n'ai jamais eu de cours sur le langage SQL, je ne savais pas par exemple qu'il ne fallait pas d'apostrophe autour des alias, idem pour les requêtes inner join, et je pensais que ce qu'avait écrit mon collègue était correct...

Je vais donc suivre vos conseils.

Cordialement,

Delphine

[MySQL 5.0.21] Requête tourne dans le vide

Requêtes MySQL

Discussions similaires

Partager

Partager