mysql运维入门4：索引、慢查询、优化

MySQL索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都是以B-树的形式保存
如果没有索引，执行查询时，MySQL必须从第一个记录开始整表扫描，知道查询到符合要求的记录，记录越大，花费时间成本越高
如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录，即可迅速取得目标记录所在的位置
比如表有1000条记录，通过索引查找记录至少比顺序扫描记录快100倍

常见索引类型

normal 普通索引
unique 唯一索引，不允许重复的索引，该字段信息保证不会重复，譬如身份证号
full text 全文索引用于搜索很长一篇文章的时候，效果最好；如果用在比较短的文本，就一两行字，普通的index也可以

总结：

索引的类别由建立索引的字段内容特性来决定，通常normal最常见

创建索引

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)

删除索引

drop index idxname on tabname

alter table tabname drop index idxname

MySQL慢查询

show variables like "%slow%";

| slow_launch_time | 2 |  超过2秒定义为慢查询。
| slow_query_log | OFF |  慢查询关闭状态。
| slow_query_log_file | /data/mysql/var/db-Test2-slow.log | 慢查询日志的文件。

开启慢查询

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