mysql抄书4：索引、慢查询、优化

• 开启慢查询

set global slow_query_log=on;
[mysqld]
log-slow-queries = /data/mysql/var/db-Test2-slow.log #日志目录。 
long_query_time = 1   #记录下查询时间查过1秒。 
log-queries-not-using-indexes  #表示记录下没有使用索引的查询。

• mysqldumpslow分析日志

mysqldumpslow，使用很简单，参数可-help查看
-s：排序方式。
c ， t ， l ， r 表示记录次数、时间、查询时间的多少、返回的记录数排序；
# ac ， at ， al ， ar 表示相应的倒叙；
# -t：返回前面多少条的数据；
# -g：包含什么，大小写不敏感的；
mysqldumpslow -s r -t 10 /data/mysql/var/db-Test2-slow.log

高效优化：三分配置、七分sql语句优化

• 配置的优化
  • 系统内核优化
  • my.cnf配置文件
• sql语句的优化
• 表结构的优化
• 索引的优化

硬件的优化

• 增加内存和提高磁盘读写速度，都可以提高MySQL数据库的查询、更新速度
• 使用磁盘阵列

参数的优化

• 内存中会为MySQL保留部分的缓冲区
• 缓冲区可以提高MySQL的速度
• 缓冲区大小是在my.cnf配置文件中设置的

几个重要的内存参数

• key_buffer_size 表示索引缓存的大小。值越大，使用索引进行查询的速度就越快
• table_cache 表示同时打开的表的个数。值越大，能同时打开的表个数也就越多。不是越大越好，同时打开的过多的表会影响OS性能
• query_cache_size 表示查询缓冲区的大小。使用查询缓冲区可以提高查询的速度。这个方式只适用于修改操作少且经常执行相同的查询操作情况，默认值是0
• query_cache_type 表示查询缓冲区的开启状态。0表示关闭，1表示开启
• max_connections 表示数据库的最大连接数。不是越大越好，过多的连接数会浪费内存资源
• sort_buffer_size 排序缓冲区的大小，值越大，排序就越快
• innodb_buffer_pool_size 表示innodb类型的表和索引的最大缓存，值越大，速度越快，同时影响OS性能
• 一个真实的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

  my.cnf

   

mysql抄书4：索引、慢查询、优化

标签：use   文件中   设置   variable   定义   必须   类型   timeout   sql语句优化