MySQL transcription 4: Indexing, slow query, optimization

1. MySQL indexes are used to quickly find records with specific values, and all MySQL indexes are saved as B-trees
2. If the query is not indexed, MySQL must start the whole table scan from the first record, knowing that the query to the required records, the larger the record, the higher the cost of time
3. If an index has been created on a column as a search condition, MySQL can quickly get to the location of the target record without scanning any records.
4. For example, the table has 1000 records, the index lookup record is at least 100 times times faster than the sequential scan record

Common index types

• Normal General Index
• Unique unique index, does not allow duplicate indexes, the field information is guaranteed not to repeat, such as a social security number
• Full text index is used to search for a long article, the best effect, if used in relatively short text, one or two lines of words, the normal index can also

Summarize:

The category of the index is determined by the indexed field content attribute, which is usually the most common form of normal

Create an index

ALTER TABLE tabname Add index idxname (column)

ALTER TABLE tabname add unique (column)

ALTER TABLE TabName add primary key (column)

Create INDEX Idxname on tabname (column)

Create unique index idxname on tabname (name)

Delete Index

Drop Index idxname on tabname

ALTER TABLE tabname DROP INDEX Idxname

MySQL Slow query

" %slow% " ; 2 |   more than 2 seconds are defined as slow queries. | Slow_query_log | OFF |   slow query off state. | Slow_query_log_file | /data/mysql/var/db-test2-slow.log | The file that queries the log slowly.

• Turn on Slow query

Set global slow_query_log=on;[ Mysqld]log
1   
Log-queries-not-using-indexes  #表示记录下没有使用索引的查询.

• Mysqldumpslow Analysis Log

Mysqldumpslow, easy to use, parameters available-help view -ar--/data/ Mysql/var/db-test2-slow.log

Efficient optimization: Three-point configuration, seven-point SQL statement optimization

• Optimized for configuration
  • System kernel Optimization
  • MY.CNF configuration file
• Optimization of SQL statements
• Optimization of table structure
• Optimization of indexes

Optimization of hardware

• Increased memory and disk read and write speed, can improve the MySQL database query, update speed
• Using a disk array

Optimization of parameters

• Buffer in memory for MySQL reserved portions
• Buffers can increase the speed of MySQL
• The buffer size is set in the MY.CNF configuration file.

A few important memory parameters

• Key_buffer_size represents the size of the index cache. The higher the value, the faster the query is made using the index
• Table_cache represents the number of tables that are open at the same time. The larger the value, the more tables can be opened at the same time. Not as big as possible, too many tables open at the same time can affect OS performance
• Query_cache_size represents the size of the query buffer. Query buffers can be used to increase the speed of queries. This method only applies to fewer modifications and often performs the same query operation, the default value is 0
• Query_cache_type indicates the open state of the query buffer. 0 means off, 1 means open
• Max_connections represents the maximum number of connections for the database. Not as big as possible, too many connections will waste memory resources
• Sort_buffer_size the size of the sort buffer, the higher the value, the faster the sort
• Innodb_buffer_pool_size represents the maximum cache for InnoDB types of tables and indexes, and the higher the value, the faster it affects OS performance
• A real my.cnf.

• 1 [Client]2Port =33063Socket =/tmp/Mysql.sock4 [Mysqld]5user =MySQL6server_id =Ten7Port =33068Socket =/tmp/Mysql.sock9DataDir =/data/mysql/data1TenOld_passwords =1 OneLower_case_table_names =1 ACharacter-set-server =UTF8 -Default-storage-engine =MYISAM -Log-bin =Bin.log theLog-error =Error.log -pid-file=Mysql.pid -Long_query_time =2 - Slow_query_log +Slow_query_log_file =Slow.log -Binlog_cache_size =4M +Binlog_format =Mixed AMax_binlog_cache_size =16M atMax_binlog_size =1G -Expire_logs_days = - -Ft_min_word_len =4 -Back_log = + -Max_allowed_packet =64M -Max_connections =4096 inMax_connect_errors = - -Join_buffer_size =2M toRead_buffer_size =2M +Read_rnd_buffer_size =2M -Sort_buffer_size =2M theQuery_cache_size =64M *Table_open_cache =10000 $Thread_cache_size = thePanax NotoginsengMax_heap_table_size =64M -Tmp_table_size =64M theThread_stack =192K +Thread_concurrency = - ALocal-infile =0 theskip-show-Database +skip-name-Resolve -skip-external-Locking $Connect_timeout = - $Interactive_timeout = - -Wait_timeout = - -#***MyISAM theKey_buffer_size =512M -Bulk_insert_buffer_size =64MWuyiMyisam_sort_buffer_size =64M theMyisam_max_sort_file_size =1G -Myisam_repair_threads =1 WuConcurrent_insert =2 - Myisam_recover About#***INNODB $Innodb_buffer_pool_size =16G -Innodb_additional_mem_pool_size =32M -Innodb_data_file_path =Ibdata1:1g;ibdata2:1g:autoextend -Innodb_read_io_threads =8 AInnodb_write_io_threads =8 +Innodb_file_per_table =1 theInnodb_flush_log_at_trx_commit =2 -Innodb_lock_wait_timeout = - $Innodb_log_buffer_size =8M theInnodb_log_file_size =256M theInnodb_log_files_in_group =3 theinnodb_max_dirty_pages_pct = - theInnodb_thread_concurrency = - -Innodb_open_files =10000 in#innodb_force_recovery =4 the#***Replication Slave theread- only About#skip-slave-Start theRelay-log =Relay.log theLog-slave-updates

  my.cnf

MySQL transcription 4: Indexing, slow query, optimization