https://www.cnblogs.com/jenvid/p/8411201.html
- MySQL indexes are used to quickly find records with specific values. All MySQL indexes are saved in the form of B-trees
- If there is no index, MySQL must scan the whole table from the first record when executing the query. It knows that qualified records are found. The larger the record, the higher the time cost
- If an index has been created on the column as the search criteria, MySQL can quickly obtain the location of the target record without scanning any records
- For example, if the table has 1000 records, finding records by index is at least 100 times faster than sequential scanning records
Common index types
- Normal normal index
- Unique unique index, which does not allow duplicate index, and the information is guaranteed to be repeated, such as the ID number.
- The full text index works best when it is used to search a long article; If it is used in short text, just one or two lines of words, and ordinary index can also be used
Summary:
The category of the index is determined by the content characteristics of the indexed field, and normal is usually the most common
Create 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
show variables like "%slow%"; | slow_launch_time | 2 | More than 2 seconds is defined as slow query. | slow_query_log | OFF | Slow query off status. | slow_query_log_file | /data/mysql/var/db-Test2-slow.log | Slow query log files.
- Enable slow query
set global slow_query_log=on; [mysqld] log-slow-queries = /data/mysql/var/db-Test2-slow.log #Log directory. long_query_time = 1 #Record the query time for 1 second. log-queries-not-using-indexes #Indicates that the query without index is recorded.
- mysqldumpslow analysis log
mysqldumpslow，It is easy to use and the parameters can be changed-help see -s: Sort method. c ， t ， l ， r Indicates the number of records, time, query time, and the number of returned records; # ac, at, al, ar denote the corresponding flashback; # -t: Return the previous data; # -g: What it contains, case insensitive; mysqldumpslow -s r -t 10 /data/mysql/var/db-Test2-slow.log
Efficient optimization: three point configuration and seven point sql statement optimization
- Configuration optimization
- System kernel optimization
- my.cnf profile
- Optimization of sql statements
- Table structure optimization
- Index optimization
Hardware optimization
- Increasing memory and disk read-write speed can improve the query and update speed of MySQL database
- Using disk arrays
Optimization of parameters
- Some buffers will be reserved for MySQL in memory
- Buffer can improve the speed of MySQL
- The buffer size is in my Set in CNF configuration file
Several important memory parameters
- key_buffer_size indicates the size of the index cache. The higher the value, the faster the query using the index
- table_cache indicates the number of tables opened at the same time. The larger the value, the more tables can be opened at the same time. Not the bigger the better. Opening too many tables at the same time will affect OS performance
- query_cache_size indicates the size of the query buffer. Using query buffer can improve the speed of query. This method is only applicable to the case where there are few modification operations and the same query operations are often performed. The default value is 0
- query_cache_type indicates the open status of the query buffer. 0 means off and 1 means on
- max_connections indicates the maximum number of connections to the database. Not the bigger the better. Too many connections will waste memory resources
- sort_buffer_size the size of the sorting buffer. The larger the value, the faster the sorting
- innodb_buffer_pool_size indicates the maximum cache size of InnoDB type tables and indexes. The larger the value, the faster the speed, and affects the OS performance
- A real my cnf
-
1 [client] 2 port = 3306 3 socket = /tmp/mysql.sock 4 [mysqld] 5 user = mysql 6 server_id = 10 7 port = 3306 8 socket = /tmp/mysql.sock 9 datadir = /data/mysql/data1 10 old_passwords = 1 11 lower_case_table_names = 1 12 character-set-server = utf8 13 default-storage-engine = MYISAM 14 log-bin = bin.log 15 log-error = error.log 16 pid-file = mysql.pid 17 long_query_time = 2 18 slow_query_log 19 slow_query_log_file = slow.log 20 binlog_cache_size = 4M 21 binlog_format = mixed 22 max_binlog_cache_size = 16M 23 max_binlog_size = 1G 24 expire_logs_days = 30 25 ft_min_word_len = 4 26 back_log = 512 27 max_allowed_packet = 64M 28 max_connections = 4096 29 max_connect_errors = 100 30 join_buffer_size = 2M 31 read_buffer_size = 2M 32 read_rnd_buffer_size = 2M 33 sort_buffer_size = 2M 34 query_cache_size = 64M 35 table_open_cache = 10000 36 thread_cache_size = 256 37 max_heap_table_size = 64M 38 tmp_table_size = 64M 39 thread_stack = 192K 40 thread_concurrency = 24 41 local-infile = 0 42 skip-show-database 43 skip-name-resolve 44 skip-external-locking 45 connect_timeout = 600 46 interactive_timeout = 600 47 wait_timeout = 600 48 #*** MyISAM 49 key_buffer_size = 512M 50 bulk_insert_buffer_size = 64M 51 myisam_sort_buffer_size = 64M 52 myisam_max_sort_file_size = 1G 53 myisam_repair_threads = 1 54 concurrent_insert = 2 55 myisam_recover 56 #*** INNODB 57 innodb_buffer_pool_size = 16G 58 innodb_additional_mem_pool_size = 32M 59 innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend 60 innodb_read_io_threads = 8 61 innodb_write_io_threads = 8 62 innodb_file_per_table = 1 63 innodb_flush_log_at_trx_commit = 2 64 innodb_lock_wait_timeout = 120 65 innodb_log_buffer_size = 8M 66 innodb_log_file_size = 256M 67 innodb_log_files_in_group = 3 68 innodb_max_dirty_pages_pct = 90 69 innodb_thread_concurrency = 16 70 innodb_open_files = 10000 71 #innodb_force_recovery = 4 72 #*** Replication Slave 73 read-only 74 #skip-slave-start 75 relay-log = relay.log 76 log-slave-updates