您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记
mysql优化-mysql优化笔记
发布时间：2017-11-23 10:33:44编辑：雪饮阅读（）
Q: 为什么别人问你MySQL优化的知识 总是没有底气.
A: 因为你只是回答一些大而化之的调优原则,
比如:”建立合理索引”(什么样的索引合理?)
“分表分库”(用什么策略分表分库?)
“主从分离”(用什么中间件?)
并没有从细化到定量的层面去分析.
如qps提高了%N? 有没有减少文件排序?语句的扫描行数减少了多少?
没有大量的数据供测试,一般在学习环境中,只是手工添加几百上万条数据,
数据量小,看不出语句之间的明确区别.
Q: 如何提高MySQL的性能?
A: 需要优化,则说明效率不够理想.
因此我们首先要做的,不是优化,而是---诊断.
治病的前提,是诊病,找出瓶颈所在. CPU,内存,IO? 峰值,单条语句?
准备环境
1、安装确保以下系统相关库文件
gcc gcc-c++ autoconf automake zlib* libxml* ncurses-devel libmcrypt* libtool*(libtool-ltdl-devel*)
# yum –y install gcc gcc-c++ autoconf automake zlib* libxml* ncurses-devel libmcrypt* libtool* cmake
2、 建立mysql安装目录及数据存放目录
# mkdir /usr/local/mysql
# mkdir -p /data/mysql
3、 创建用户和用户组
# groupadd mysql
# useradd -g mysql mysql
4、 赋予数据存放目录权限
# chown mysql.mysql –R /data/mysql
二、安装MySQL 5.5.35
1、 获取解压mysql-5.5.35.tar.gz
在mysql.com官网或国内镜像下载源码
# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.35.tar.gz
# tar zxvf mysql-5.5.35.tar.gz
# cd mysql-5.5.35
2、 编译mysql-5.5.35
# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/data/mysql \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306
# make
# make install
3、 复制配置文件
# cp support-files/my-medium.cnf /etc/my.cnf
4、 初始化数据库
执行前需赋给scripts/mysql_install_db文件执行权限
# chmod 755 scripts/mysql_install_db
# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ \
--datadir=/data/mysql/
注：basedir：mysql安装路径 datadir：数据库文件储存路径
5、 设置mysqld的开机启动
# cp support-files/mysql.server /etc/init.d/mysql
# chmod 755 /etc/init.d/mysql
# chkconfig mysql on
6、 为MySQL配置环境变量
将mysql的bin目录加到PATH中，有利于以后管理和维护，在/etc/profile中加入myslq/bin，同时增加两个别名方便操作：
# export PATH=/usr/local/mysql/bin:$PATH
# alias mysql_start="mysqld_safe &"
# alias mysql_stop="mysqladmin –u root -p shutdown"
7、 启动mysql服务
# /etc/init.d/mysql start
启动完成之后用ps -ef |grep mysql 命令查看是否启动
8、 登陆mysql
#mysql -uroot -p
提示 在自行编译mysql,并连接本地机时,常出现找不到mysqld.sock的错误.
[root@lfqb data]# mysql -uroot
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysqld.sock' (2)
错误原因: mysql客户端默认去找 /tmp/mysqld.sock 做连接,而mysqld.sock有可能不在此处.
比如在 /var/lib/mysql/mysql.sock
解决:
1: mysql -S /sock/path 指定真实的路径
mysql -S /var/lib/mysql/mysql.sock
2: 在/tmp下做一个链接,链接到真实sock文件.
# ln /var/lib/mysql/mysql.sock /tmp/mysqld.sock
3: sock文件在linux环境中连接本地机才能使用,速度比用本机IP要快.
你也可以强行指定用IP来连接.
Sysbench的编译
下载:http://bazaar.launchpad.net/~sysbench-developers/sysbench/0.5/files
解压:
#cd /path/to/sysbench
# ./autogen.sh
#../configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/include/mysql/ \
--with-mysql-libs=/usr/lib64/mysql/
#make && make install
Sysbench测试CPU性能
4线程查找20000以内的素数
[root@localhost ~]# /usr/bin/sysbench --test=cpu --cpu-max-prime=20000 --num-threads=4 run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 4
Doing CPU performance benchmark
Threads started!
Done.
Maximum prime number checked in CPU test: 20000
Test execution summary:
total time: 96.7210s
total number of events: 10000
total time taken by event execution: 114.5518
per-request statistics:
min: 2.17ms
avg: 11.46ms
max: 51.54ms
approx. 95 percentile: 17.27ms
Threads fairness:
events (avg/stddev): 2500.0000/6.96
execution time (avg/stddev): 28.6380/0.01
注意: 服务器类型,有偏运算型的,有偏存储, 所需要的指标不一样.
偏运算的(如视频转码服务器)要求CPU强,而存储则优先选择大容量和快速存储备.
测试的数据,孤立起来看,是没有意义的.
数据要有比较才有意义, 比如多台服务器的测试数据,比较CPU性能.
Sysbench测试磁盘IO性能
Sysbench --test=fileio --file-total-size=10G prepare
解释: 创建10G的内容,供测试用
Sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrw run
解释:针对10G文件,做随机读写,测试IO
--file-test-mode 还可以为
seqwr：顺序写入
seqrewq：顺序重写
seqrd：顺序读取
rndrd：随机读取
rndwr：随机写入
rndrw：混合随机读写
测试顺序读
[root@localhost sysbench]# ./bin/sysbench --test=fileio --file-total-size=10G --file-test-mode=seqrd run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Extra file open flags: 0
128 files, 80Mb each
10Gb total file size
Block size 16Kb
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing sequential read test
Threads started!
Operations performed: 10000 reads, 0 writes, 0 Other = 10000 Total
Read 156.25Mb Written 0b Total transferred 156.25Mb (201.09Mb/sec)
12869.92 Requests/sec executed
General statistics:
total time: 0.7770s
total number of events: 10000
total time taken by event execution: 0.7595s
response time:
min: 0.01ms
avg: 0.08ms
max: 15.58ms
approx. 95 percentile: 0.02ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 0.7595/0.00
测试随机读
[root@localhost sysbench]# ./bin/sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrd run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Extra file open flags: 0
128 files, 80Mb each
10Gb total file size
Block size 16Kb
Number of IO requests: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Operations performed: 10000 reads, 0 writes, 0 Other = 10000 Total
Read 156.25Mb Written 0b Total transferred 156.25Mb (5.5698Mb/sec)
356.47 Requests/sec executed
General statistics:
total time: 28.0530s
total number of events: 10000
total time taken by event execution: 28.0305s
response time:
min: 0.01ms
avg: 2.80ms
max: 76.69ms
approx. 95 percentile: 11.42ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 28.0305/0.00
通过上两例对比, 顺序读与随机读之间的速度,差了40倍!
合理的索引+where语句会尽量达成顺序读.
作业:测试随机写与顺序写的速度差异
Sysbench测试事务性能
#sysbench --test=/path/to/sysbench-source/tests/db/oltp.lua --mysql-table-engine=innodb \
--mysql-user=root --db-driver=mysql --mysql-db=test \
--oltp-table-size=3000
--mysql-socket=/var/lib/mysql/mysql.sock prepare
实测结果: 双核CPU,8G内存,7200转机械硬盘
[root@localhost sysbecn]# ./bin/sysbench --test=/path/to/sysbench-source/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --mysql-db=test --oltp-table-size=3000 --mysql-socket=/var/lib/mysql/mysql.sock run
sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Threads started!
OLTP test statistics:
queries performed:
read: 140000
write: 40000
other: 20000
total: 200000
transactions: 10000 (30.84 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 180000 (555.10 per sec.)
other operations: 20000 (61.68 per sec.)
General statistics:
total time: 324.2651s
total number of events: 10000
total time taken by event execution: 324.2226s
response time:
min: 22.56ms
avg: 32.42ms
max: 453.49ms
approx. 95 percentile: 34.75ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 324.2226/0.00
Awk脚本
Awk是一个简便的直译式的文本处理工具.
擅长处理--多行多列的数据
处理过程:
While(还有下一行) {
1:读取下一行,并把下一行赋给$0,各列赋给$1,$2...$N变量
2: 用指定的命令来处理该行
}
如何处理1行数据?
答: 分2部分, pattern (条件) + action(处理动作)
第1个简单awk脚本
awk ‘{printf “%s\n” , $1}’ xx.txt // 把xx.txt的每一行进行输出
第2个简单awk脚本 统计mysql服务器信息
mysqladmin -uroot ext|awk
'Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}'
mysql性能调优的思路
0: 最好的优化-----不查询!
这不是开玩笑.
如果一台服务器出现长时间负载过高 /周期性负载过大,或偶尔卡住
如何来处理?
答:大的思路--------
是周期性的变化还是偶尔问题?
是服务器整体性能的问题, 还是某单条语句的问题?
具体到单条语句, 这条语句是在等待上花的时间,还是查询上花的时间.
唯一的办法-----监测并观察服务器的状态.
1:观察服务器状态, 一般用如下2个命令
Show status; Show processlist;
例: mysql> show status;
#mysqladmin ext
MySQL周期性波动试验
实验目的: 模拟数据库高低峰时的压力波动,并会观察绘制波动曲线
实验思路: 反复查询数据库并缓存入memcached, 缓存定期失效,
观察记录服务器参数,并作图表.
实验准备: nginx+php+memcached+awk+ab
1: index.php (随机访问3W条热数据,并储存在memcached中
2: memcached (储存查询结果)
3: ab 压力测试工具
4: awk脚本
编译PHP-memcache扩展 (此步骤适合任意PHP扩展)
以memcache-2.2.7为例(注意,这是PHP连接memcached的一个扩展)
解压后,假设路径/path/to/memcache
step1 : /path/to/memcached/# /path/to/php/bin/phpize #作用是根据PHP的版本生成编译文件
此步骤后,memcache目录下,产生configure文件
step2: configure --with-php-config =/path/to/php/bin/php-config
step3: make && make install
此步骤编译出一个memcache.so文件
step4: 修改php.ini引入memcache.so
实验步骤:
总数据3W以上,50个并发,每秒请求500-1000次
请求结果缓存在memcache,生命周期为60秒,
(生命周期要结合请求周期来制定,比如3万条数据随机,每秒1000条,30秒能走一遍,生命周期可设为60秒)
观察mysql连接数,每秒请求数的周期变化.
看上图,mysql的每秒请求数,随着缓存失效,有短时间的高峰.
解决办法:
1: 减少无关请求(业务逻辑层面,暂不讨论,但其实是最有效的手段)
2: 如果请求数是一定的,不可减少的. 我们要尽量让请求数平稳,不要有剧烈波动.
很多时候,不是服务器撑不住总的查询量,而是在某个时间段撑不住高峰请求.
该实际问题最后的解决:
----夜间负载低时,集中失效.
短时间内会有波峰,但夜间访问量少,因此波峰并不剧烈,当到上午10点左右人多时,缓存已经建立了一部分. 白天时,波峰也不剧烈.
或者让缓存的生命周期在一定范围内随机,也可以减缓波峰剧烈的情况
我们把实验中的生命周期由80秒,改为[40-120秒],其他实验条件不变.
得到如下曲线
可以看出,稳定运行后,请求在[1000-1500]之间波动,
而固定缓存周期是,请求在[500-1700]之间波动.
实验附件:
bench.php
<?php
// 30K hot news
$rangeid = rand(1,30000)+13000000;
$mconn = memcache_connect('localhost',11211);
if( ($com = memcache_get($mconn,$rangeid)) === false) {
$conn = mysql_connect('localhost','root');
$sql = 'use bigdata';
mysql_query($sql,$conn);
$sql = 'set names utf8';
mysql_query($sql,$conn);
$sql = 'select id,name,brief from lx_com where id=' . $rangeid;
$rs = mysql_query($sql,$conn);
$com = mysql_fetch_assoc($rs);
memcache_add($mconn , $rangeid , $com , false, mt_rand(40,120));
} else {
echo 'from cache';
}
print_r($com);
status.sh
#!/bin/bash
while true
do
mysqladmin -uroot ext|awk '/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}' >> status.txt
sleep 1
done
对于不规则的延迟现象的观察
不规则的延迟现象往往是由于效率低下的语句造成的,如何抓到这些效率低的语句.
可以用show processlist命令长期观察,或用慢查询.
Show processlist;
这个命令是显示当前所有连接的工作状态.
#!/bin/bash
while true
do
mysql -uroot -e 'show processlist\G'|grep State:|uniq -c|sort -rn
echo '---'
sleep 1
Done
如果观察到以下状态,则需要注意
converting HEAP to MyISAM 查询结果太大时,把结果放在磁盘 (语句写的不好,取数据太多)
create tmp table 创建临时表(如group时储存中间结果,说明索引建的不好)
Copying to tmp table on disk 把内存临时表复制到磁盘 (索引不好,表字段选的不好)
locked 被其他查询锁住 (一般在使用事务时易发生,互联网应用不常发生)
logging slow query 记录慢查询
mysql 5.5 以后加了一个profile设置,可以观察到具体语句的执行步骤.
0:查看profile是否开启
> Show variables like ‘profiling’
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1:> set profiling=on;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | On |
+---------------+-------+
mysql> show profiles;
+----------+------------+----------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------+
| 1 | 0.00034225 | select cat_id,avg(shop_price) from goods group by cat_id |
+----------+------------+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000058 |
| checking permissions | 0.000008 |
...
...
| Sorting result | 0.000004 |
| Sending data | 0.000120 |
| end | 0.000005 |
| query end | 0.000006 |
| closing tables | 0.000008 |
| freeing items | 0.000023 |
| logging slow query | 0.000003 |
| cleaning up | 0.000004 |
+----------------------+----------+
疑问; 如何定位到有问题的语句?
答:
1: 开启服务器慢查询
2: 了解临时表的使用规则
3: 经验
MySQL如何使用内部临时表
官方:http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html
在处理请求的某些场景中,服务器创建内部临时表. 即表以MEMORY引擎在内存中处理,或以MyISAM引擎储存在磁盘上处理.如果表过大,服务器可能会把内存中的临时表转存在磁盘上.
用户不能直接控制服务器内部用内存还是磁盘存储临时表
临时表在如下几种情况被创建:
如果group by 的列没有索引,必产生内部临时表,
如果order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表(实验1)
mysql> explain select goods_id,cat_id from goods group by cat_id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)
mysql> alter table goods add index cat_id(cat_id);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select goods_id,cat_id from goods group by cat_id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: index
possible_keys: NULL
key: cat_id
key_len: 2
ref: NULL
rows: 20
Extra: Using index
mysql> explain select goods_id,cat_id from goods group by cat_id order by 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: index
possible_keys: NULL
key: PRIMARY
key_len: 3
ref: NULL
rows: 20
Extra: Using temporary
distinct 与order by 一起使用可能会产生临时表(实验2)
mysql> explain select distinct cat_id from goods order by 1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: index
possible_keys: NULL
key: cat_id
key_len: 2
ref: NULL
rows: 20
Extra: Using index
1 row in set (0.00 sec)
mysql> explain select distinct cat_id from goods order by goods_id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: index
possible_keys: NULL
key: PRIMARY
key_len: 3
ref: NULL
rows: 20
Extra: Using temporary
1 row in set (0.00 sec)
mysql> explain select distinct cat_id from goods order by click_count\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: index
possible_keys: NULL
key: cat_id
key_len: 2
ref: NULL
rows: 20
Extra: Using temporary; Using filesort
1 row in set (0.00 sec)
如果使用SQL_SMALL_RESULT,MySQL会使用内存临时表,除非查询中有一些必须要把临时表建立在磁盘上.
union合并查询时会用到临时表
某些视图会用到临时表,如使用temptable方式建立,或使用union或聚合查询的视图
想确定查询是否需要临时表,可以用EXPLAIN查询计划,并查看Extra列,看是否有Using temporary.
如果一开始在内存中产生的临时表变大,会自动转化为磁盘临时表. 内存中临时表的最大值为tmp_table_size和max_heap_size中较小值.
这和create table时显示指定的内存表不一样:这些表只受max_heap_table_size系统参数影响.
当服务器创建内部临时表(无论在内存还是在磁盘),create_tmp_tables变量都会增加.
如果创建了在磁盘上内部临时表(无论是初始创建还是由in-memory转化),
create_tmp_disk_tables 变量都会增加.
一些情况下限制了内存临时表的使用,而使用磁盘临时表:
(使用了内部临时表的前提下) 语句中存在BLOB或TEXT列(实验3)
在GROUP BY 或 DISTINCT子句中有大于512字节的string列
在UNION或UNION ALL时,SELECT语句里有大于512字节的string列.
mysql> create table t1 (
num int,
intro text(1000)
);
mysql>insert into t1 values (3,'this is USA' , 4,'China');
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 5 |
| Created_tmp_files | 9 |
| Created_tmp_tables | 74 |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql> select * from t1 group by num;
+------+-------------+
| num | intro |
+------+-------------+
| 3 | this is USA |
| 4 | China |
+------+-------------+
2 rows in set (0.00 sec)
mysql> show status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 6 |
| Created_tmp_files | 9 |
| Created_tmp_tables | 75 |
+-------------------------+-------+
3 rows in set (0.00 sec)
通过前面实验,可以看出,数据库的优化是一个系统工程:
建表: 表结构的拆分,如核心字段都用int,char,enum等定长结构
非核心字段,或用到text,超长的varchar,拆出来单放一张表.
建索引: 合理的索引可以减少内部临时表(索引优化策略里详解)
写语句: 不合理的语句将导致大量数据传输以及内部临时表的使用.
表的优化与列类型选择
表的优化:
1: 定长与变长分离
如 id int, 占4个字节, char(4) 占4个字符长度,也是定长, time
即每一单元值占的字节是固定的.
核心且常用字段,宜建成定长,放在一张表.
而varchar, text,blob,这种变长字段,适合单放一张表, 用主键与核心表关联起来.
2:常用字段和不常用字段要分离.
需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.
3:合理添加冗余字段.
看如下BBS的效果
每个版块里,有N条帖子, 在首页,显示了版块信息,和版块下的帖子数.
这是如何做的?
|
boardid |
boardname |
postnum |
|
8 |
开班信息 |
2 |
|
9 |
每日视频及代码 |
1 |
|
postid |
boardid |
title |
|
123 |
8 |
论坛开张了 |
|
129 |
8 |
灌水 |
|
133 |
9 |
来一帖 |
如果board表只有前2列,则需要取出版块后,
再查post表,select count(*) from post group by board_id,得出每个版块下的帖子数.
如果有postnum字段,每发一个帖子时,对postnum字段+1;
再查询版块下的帖子数时, 只需要1条语句直接查boardid
select boradid, boardname,postnum from board;
典型的”空间换时间”
列选择原则:
1:字段类型优先级 整型 > date,time > enum,char>varchar > blob
列的特点分析:
整型: 定长,没有国家/地区之分,没有字符集的差异
time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;
enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化
Char 定长, 考虑字符集和(排序)校对集
varchar, 不定长 要考虑字符集的转换与排序时的校对集,速度慢.
text/Blob 无法使用内存临时表
附: 关于date/time的选择,大师的明确意见
http://www.xaprb.com/blog/2014/01/30/timestamps-in-mysql/
性别: 以utf8为例
char(1) , 3个字长字节
enum(‘男’,’女’); // 内部转成数字来存,多了一个转换过程
tinyint() , // 0 1 2 // 定长1个字节.
2: 够用就行,不要慷慨 (如smallint,varchar(N))
原因: 大的字段浪费内存,影响速度,
以年龄为例 tinyint unsigned not null ,可以存储255岁,足够. 用int浪费了3个字节
以varchar(10) ,varchar(300)存储的内容相同, 但在表联查时,varchar(300)要花更多内存
3: 尽量避免用NULL()
原因: NULL不利于索引,要用特殊的字节来标注.
在磁盘上占据的空间其实更大.
实验:
可以建立2张字段相同的表,一个允许为null,一个不允许为Null,各加入1万条,查看索引文件的大小. 可以发现,为null的索引要大些.(mysql5.5里,关于null已经做了优化,大小区别已不明显)
另外: null也不便于查询,
where 列名=null;
where 列名!=null; 都查不到值,
where 列名 is null ,或is not null 才可以查询.
create table dictnn (
id int,
word varchar(14) not null default '',
key(word)
)engine myisam charset utf8;
create table dictyn (
id int,
word varchar(14),
key(word)
)engine myisam charset utf8;
alter table dictnn disable keys;
alter table dictyn disable keys;
insert into dictnn select id,if(id%2,word,'') from dict limit 10000;
insert into dictyn select id,if(id%2,word,null) from dict limit 10000;
alert table dictnn enable keys;
alter table dictyn enable keys;
Enum列的说明
1: enum列在内部是用整型来储存的
2: enum列与enum列相关联速度最快
3: enum列比(var)char 的弱势---在碰到与char关联时,要转化. 要花时间.
4: 优势在于,当char非常长时,enum依然是整型固定长度.
当查询的数据量越大时,enum的优势越明显.
5: enum与char/varchar关联 ,因为要转化,速度要比enum->enum,char->char要慢,
但有时也这样用-----就是在数据量特别大时,可以节省IO.
试验:
create table t2 (
id int,
gender enum('man','woman'),
key(gender)
)engine myisam charset utf8;
create table t3 (
id int,
gender char(5) not null default '',
key(gender)
)engine myisam charset utf8;
alter table t2 disable keys;
alter table t3 disable keys;
insert into t2 select id,if(id%2,'man','woman') from dict limit 10000;
insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;
alter table t2 enable keys;
alter table t3 enable keys;
mysql> select count(*) from t2 as ta,t2 as tb where ta.gender=tb.gender
mysql> select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender
|
列<---->列 |
时间 |
|
Enum<--->enum |
10.53 |
|
Char<---->char |
24.65 |
|
Enum<---->char |
18.22 |
如果t2表的优势不明显, 加大t3的gender列 ,char(15), char(20)...
随着t3 gender列的变大,t2表优势逐渐明显.
原因----无论enum(‘manmaman’,’womanwomanwoman’) 枚举的字符多长,内部都是用整型表示, 在内存中产生的数据大小不变,而char型,却在内存中产生的数据越来越多.
总结: enum 和enum类型关联速度比较快
Enum 类型 节省了IO
索引优化策略
1:索引类型
1.1 B-tree索引
注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上, 各引擎稍有不同,
比如,严格的说,NDB引擎,使用的是T-tree
Myisam,innodb中,默认用B-tree索引
但抽象一下---B-tree系统,可理解为”排好序的快速查找结构”.
1.2 hash索引
在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)
疑问: 既然hash的查找如此高效,为什么不都用hash索引?
答:
1:hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,
比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.
2: 不法对范围查询进行优化.
3: 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引
查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)
因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机
4: 排序也无法优化.
5: 必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据
2: btree索引的常见误区
2.1 在where条件常用的列上都加上索引
例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
误: cat_id上,和, price上都加上索引.
错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.
2.2 在多列上建立索引后,查询哪个列,索引都将发挥作用
误: 多列索引上,索引发挥作用,需要满足左前缀要求.
以 index(a,b,c) 为例,
|
语句 |
索引是否发挥作用 |
|
Where a=3 |
是,只使用了a列 |
|
Where a=3 and b=5 |
是,使用了a,b列 |
|
Where a=3 and b=5 and c=4 |
是,使用了abc |
|
Where b=3 / where c=4 |
否 |
|
Where a=3 and c=4 |
a列能发挥索引,c不能 |
|
Where a=3 and b>10 and c=7 |
A能利用,b能利用, C不能利用 |
|
同上,where a=3 and b like ‘xxxx%’ and c=7 |
A能用,B能用,C不能用 |
为便于理解, 假设ABC各10米长的木板, 河面宽30米.
全值索引是则木板长10米,
Like,左前缀及范围查询, 则木板长6米,
自己拼接一下,能否过河对岸,就知道索引能否利用上.
如上例中, where a=3 and b>10, and c=7,
A板长10米,A列索引发挥作用
A板正常接B板, B板索引发挥作用
B板短了,接不到C板, C列的索引不发挥作用.
多列索引经典题目:
http://www.zixue.it/thread-9218-1-4.html
假设某个表有一个联合索引（c1,c2,c3,c4）一下——只能使用该联合索引的c1,c2,c3部分
A where c1=x and c2=x and c4>x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=x and c5=x order by c2,c3
E where c1=x and c2=x and c5=? order by c2,c3
create table t4 (
c1 tinyint(1) not null default 0,
c2 tinyint(1) not null default 0,
c3 tinyint(1) not null default 0,
c4 tinyint(1) not null default 0,
c5 tinyint(1) not null default 0,
index c1234(c1,c2,c3,c4)
);
insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);
对于A:
c1=x and c2=x and c4>x and c3=x <==等价==> c1=x and c2=x and c3=x and c4>x
因此 c1,c2,c3,c4都能用上. 如下:
mysql> explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: range
possible_keys: c1234
key: c1234
key_len: 4 #可以看出c1,c2,c3,c4索引都用上
ref: NULL
rows: 1
Extra: Using where
对于B: select * from t4 where c1=1 and c2=2 and c4=3 order by c3
c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序.
而c4没发挥作用.
mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where; Using filesort
1 row in set (0.00 sec)
对于 C: 只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引
mysql> explain select * from t4 where c1=1 and c4=2 group by c3,c2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1 #只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)
mysql> explain select * from t4 where c1=1 and c4=2 group by c2,c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
D语句: C1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3发挥的排序的作用.
因此,没用到filesort
mysql> explain select * from t4 where c1=1 and c5=2 order by c2,c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
E: 这一句等价与 elect * from t4 where c1=1 and c2=3 and c5=2 order by c3;
因为c2的值既是固定的,参与排序时并不考虑
mysql> explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
一道面试题:
有商品表, 有主键,goods_id, 栏目列 cat_id, 价格price
说:在价格列上已经加了索引,但按价格查询还是很慢,
问可能是什么原因,怎么解决?
答: 在实际场景中,一个电商网站的商品分类很多,直接在所有商品中,按价格查商品,是极少的,一般客户都来到分类下,然后再查.
改正: 去掉单独的Price列的索引, 加 (cat_id,price)复合索引
再查询.
聚簇索引与非聚簇索引
Myisam与innodb引擎,索引文件的异同
===============================================================
innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用
myisam中, 主索引和次索引,都指向物理行(磁盘位置).
注意: innodb来说,
1: 主键索引 既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”
聚簇索引
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.
C) 聚簇索引的页分裂过程
实验: 聚簇索引使用随机值导致页频繁分裂影响速度
过程:建立innodb表, 利用php连接mysql,
分别规则插入10000条数据,不规则插入10000条数据
观察时间的差异,体会聚簇索引,页分裂的影响.
create table t5(
id int primary key,
c1 varchar(500),
c2 varchar(500),
c3 varchar(500),
c4 varchar(500),
c5 varchar(500),
c6 varchar(500)
) engine innodb charset utf8;
create table t6(
id int primary key,
c1 varchar(500),
c2 varchar(500),
c3 varchar(500),
c4 varchar(500),
c5 varchar(500),
c6 varchar(500)
) engine innodb charset utf8;
正常插入数据
// testinnodb.php
$time_start = microtime_float();
$str = str_repeat('hello',100);
for($i=1;$i<=10000;$i++) {
$sql = "insert into t5 values ($i,'$str' , '$str' , '$str' , '$str' , '$str' , '$str'
)";
//echo $sql;
mysql_query($sql , $conn);
}
$time_end = microtime_float();
echo 'seq insert cost' , ($time_end - $time_start) , "seconds\n";
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
随机插入数据
// rndinnodb.php
$base = range(1,10000);
shuffle($base);
$time_start = microtime_float();
$str = str_repeat('hello',100);
foreach($base as $i) {
$sql = "insert into t6 values ($i,'$str' , '$str' , '$str' , '$str' , '$str' , '$str'
)";
//echo $sql;
mysql_query($sql , $conn);
}
$time_end = microtime_float();
echo 'rand insert cost' , ($time_end - $time_start) , "seconds\n";
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
|
字段数 |
混乱程度(步长) |
顺序1000条(秒数) |
乱序1000条(秒数) |
顺序写入page页数 |
乱序写入page数 |
|
1 |
1 |
54.365 |
53.438 |
62 |
91 |
|
10 |
1 |
53.413 |
62.940 |
235 |
1301 |
|
10 |
100 |
|
64.18 |
|
1329 |
|
10 |
1000 |
|
67.512 |
|
1325 |
通过上面的规律可以看出-----
1: innodb的buffer_page 很强大.
2: 聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值,
(不要用随机字符串或UUID)
否则会造成大量的页分裂与页移动.
高性能索引策略
0:对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.
对于innodb的主键,尽量用整型,而且是递增的整型.
如果是无规律的数据,将会产生的页的分裂,影响速度.
索引覆盖:
索引覆盖是指 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.
这种查询速度非常快,称为”索引覆盖”
理想的索引
1:查询频繁 2:区分度高 3:长度小 4: 尽量能覆盖常用查询字段.
1: 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).
针对列中的值,从左往右截取部分,来建索引
1: 截的越短, 重复度越高,区分度越小, 索引效果越不好
2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.
所以, 我们要在 区分度 + 长度 两者上,取得一个平衡.
惯用手法: 截取不同长度,并测试其区分度,
mysql> select count(distinct left(word,6))/count(*) from dict;
+---------------------------------------+
| count(distinct left(word,6))/count(*) |
+---------------------------------------+
| 0.9992 |
+---------------------------------------+
1 row in set (0.30 sec)
对于一般的系统应用: 区别度能达到0.1,索引的性能就可以接受.
2:对于左前缀不易区分的列 ,建立索引的技巧
如 url列
列的前11个字符都是一样的,不易区分, 可以用如下2个办法来解决
1: 把列内容倒过来存储,并建立索引
Moc.udiab.www//:ptth
Ti.euxiz.www//://ptth
这样左前缀区分度大,
2: 伪hash索引效果
同时存 url_hash列
3:多列索引
3.1 多列索引的考虑因素---
列的查询频率 , 列的区分度,
以ecshop商城为例, goods表中的cat_id,brand_id,做多列索引
从区分度看,Brand_id区分度更高,
mysql> select count(distinct cat_id) / count(*) from goods;
+-----------------------------------+
| count(distinct cat_id) / count(*) |
+-----------------------------------+
| 0.2903 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select count(distinct brand_id) / count(*) from goods;
+-------------------------------------+
| count(distinct brand_id) / count(*) |
+-------------------------------------+
| 0.3871 |
+-------------------------------------+
1 row in set (0.00 sec)
但从 商城的实际业务业务看, 顾客一般先选大分类->小分类->品牌,
最终选择 index(cat_id,brand_id)来建立索引
有如下表(innodb引擎), sql语句在笔记中,
给定日照市,查询子地区, 且查询子地区的功能非常频繁,
如何优化索引及语句?
+------+-----------+------+
| id | name | pid |
+------+-----------+------+
| .... | .... | .... |
| 1584 | 日照市 | 1476 |
| 1586 | 东港区 | 1584 |
| 1587 | 五莲县 | 1584 |
| 1588 | 莒县 | 1584 |
+------+-----------+------+
1: 不加任何索引,自身连接查询
mysql> explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid where p.name='日照市' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3263
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3263
Extra: Using where; Using join buffer
2 rows in set (0.00 sec)
2: 给name加索引
mysql> explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid where p.name='日照市' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
type: ref
possible_keys: name
key: name
key_len: 93
ref: const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3243
Extra: Using where; Using join buffer
2 rows in set (0.00 sec)
3: 在Pid上也加索引
mysql> explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid where p.name='日照市' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
type: ref
possible_keys: name
key: name
key_len: 93
ref: const
rows: 1
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: s
type: ref
possible_keys: pid
key: pid
key_len: 5
ref: big_data.p.id
rows: 4
Extra: Using where
2 rows in set (0.00 sec)
延迟关联
mysql> select * from it_area where name like '%东山%';
+------+-----------+------+
| id | name | pid |
+------+-----------+------+
| 757 | 东山区 | 751 |
| 1322 | 东山县 | 1314 |
| 2118 | 东山区 | 2116 |
| 3358 | 东山区 | 3350 |
+------+-----------+------+
4 rows in set (0.00 sec)
分析: 这句话用到了索引覆盖没有?
答: 没有,1 查询了所有列, 没有哪个索引,覆盖了所有列.
2 like %xx%”,左右都是模糊查询, name本身,都没用上索引
第2种做法:
select a.* from it_area as a inner join (select id from it_area where name like '%东山%') as t on a.id=t.id;
Show profiles; 查看效率:
| 18 | 0.00183800 | select * from it_area where name like '%东山%'
| 20 | 0.00169300 | select a.* from it_area as a inner join (select id from it_area where name like '%东山%') as t on a.id=t.id |
发现 第2种做法,虽然语句复杂,但速度却稍占优势.
第2种做法中, 内层查询,只沿着name索引层顺序走, name索引层包含了id值的.
所以,走完索引层之后,找到所有合适的id,
再通过join, 用id一次性查出所有列. 走完name列再取.
第1种做法: 沿着name的索引文件走, 走到满足的条件的索引,就取出其id,
并通过id去取数据, 边走边取.
通过id查找行的过程被延后了. --- 这种技巧,称为”延迟关联”.
索引与排序
排序可能发生2种情况:
1: 对于覆盖索引,直接在索引上查询时,就是有顺序的, using index
2: 先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)
我们的争取目标-----取出来的数据本身就是有序的! 利用索引来排序.
比如: goods商品表, (cat_id,shop_price)组成联合索引,
where cat_id=N order by shop_price ,可以利用索引来排序,
select goods_id,cat_id,shop_price from goods order by shop_price;
// using where,按照shop_price索引取出的结果,本身就是有序的.
select goods_id,cat_id,shop_price from goods order by click_count;
// using filesort 用到了文件排序,即取出的结果再次排序
重复索引与冗余索引
重复索引: 是指 在同1个列(如age), 或者 顺序相同的几个列(age,school), 建立了多个索引,
称为重复索引, 重复索引没有任何帮助,只会增大索引文件,拖慢更新速度, 去掉.
冗余索引:
冗余索引是指2个索引所覆盖的列有重叠, 称为冗余索引
比如 x,m,列 , 加索引 index x(x), index xm(x,m)
x,xm索引, 两者的x列重叠了, 这种情况,称为冗余索引.
甚至可以把 index mx(m,x) 索引也建立, mx, xm 也不是重复的,因为列的顺序不一样.
索引碎片与维护
在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.
我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.
比如: 表的引擎为innodb , 可以 alter table xxx engine innodb
optimize table 表名 ,也可以修复.
注意: 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.
这个过程,如果表的行数比较大,也是非常耗费资源的操作.
所以,不能频繁的修复.
如果表的Update操作很频率,可以按周/月,来修复.
如果不频繁,可以更长的周期来做修复.
sql语句优化
1: sql语句的时间花在哪儿?
答: 等待时间 , 执行时间.
这两个时间并非孤立的, 如果单条语句执行的快了,对其他语句的锁定的也就少了.
所以,我们来分析如何降低执行时间.
2: sql语句的执行时间,又花在哪儿了?
答：
a: 查 ----> 沿着索引查,甚至全表扫描
b: 取 ----> 查到行后,把数据取出来(sending data)
3: sql语句的优化思路？
答: 不查, 通过业务逻辑来计算,
比如论坛的注册会员数,我们可以根据前3个月统计的每天注册数, 用程序来估算.
少查, 尽量精准数据,少取行. 我们观察新闻网站,评论内容等,一般一次性取列表 10-30条左右.
必须要查,尽量走在索引上查询行.
取时, 取尽量少的列.
比如 select * from tableA, 就取出所有列, 不建议.
比如 select * from tableA,tableB, 取出A,B表的所有列.
4: 如果定量分析查的多少行,和是否沿着索引查?
答: 用explain来分析
explain的列分析
id: 代表select 语句的编号, 如果是连接查询,表之间是平等关系, select 编号都是1,从1开始. 如果某select中有子查询,则编号递增.
mysql> explain select goods_id,goods_name from goods where goods_id in (sele
ct goods_id from goods where cat_id=4) \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: goods
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 31
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: goods
type: unique_subquery
possible_keys: PRIMARY,cat_id
key: PRIMARY
key_len: 3
ref: func
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
select_type: 查询类型
table: 查询针对的表
有可能是
实际的表名 如select * from t1;
表的别名 如 select * from t2 as tmp;
derived 如from型子查询时
null 直接计算得结果,不用走表
possible_key: 可能用到的索引
注意: 系统估计可能用的几个索引,但最终,只能用1个.
key : 最终用的索引.
key_len: 使用的索引的最大长度
type列: 是指查询的方式, 非常重要,是分析”查数据过程”的重要依据
可能的值
all: 意味着从表的第1行,往后,逐行做全表扫描.,运气不好扫描到最后一行.
例: 把goods_name列上的索引去掉, 并根据goods_name来查询
mysql> explain select goods_name from goods where goods_name='诺基亚N85' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 31
Extra: Using where
1 row in set (0.00 sec)
index: 比all性能稍好一点，
通俗的说: all 扫描所有的数据行,相当于data_all index 扫描所有的索引节点,相当于index_all
２种情况可能出现：
１：索引覆盖的查询情况下, 能利用上索引,但是又必须全索引扫描.
mysql> explain select goods_id from goods where goods_id=1 or goods_id+1>20
\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 31
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> explain select goods_id,click_count from goods where goods_id=1 or go
ods_id+1>20 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 31
Extra: Using where
1 row in set (0.00 sec)
2: 是利用索引来进行排序,但取出所有的节点
select goods_id from goods order by goods_id desc;
分析: 没有加where条件, 就得取所有索引节点,同时,又没有回行,只取索引节点.
再排序,经过所有索引节点.
mysql> explain select goods_id from goods order by goods_id asc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: index
possible_keys: NULL
key: PRIMARY
key_len: 3
ref: NULL
rows: 31
Extra: Using index
1 row in set (0.00 sec)
range: 意思是查询时,能根据索引做范围的扫描
mysql> explain select goods_id,goods_name,shop_price from goods where goods
id >25 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 8
Extra: Using where
1 row in set (0.00 sec)
ref 意思是指 通过索引列,可以直接引用到某些数据行
mysql> explain select goods_id,goods_name from goods where cat_id=4 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: ref
possible_keys: cat_id
key: cat_id
key_len: 2
ref: const
rows: 3
Extra:
1 row in set (0.00 sec)
在这个例子中,通过cat_id索引 指向N行goods数据,来查得结果.
eq_ref 是指,通过索引列,直接引用某1行数据
常见于连接查询中
mysql> explain select goods_id,shop_price from goods innert join ecs_catego
y using(cat_id) where goods_id> 25 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: innert
type: range
possible_keys: PRIMARY,cat_id
key: PRIMARY
key_len: 3
ref: NULL
rows: 8
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ecs_category
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: shop.innert.cat_id
rows: 1
Extra: Using index
2 rows in set (0.00 sec)
const, system, null 这3个分别指查询优化到常量级别, 甚至不需要查找时间.
一般按照主键来查询时,易出现const,system
或者直接查询某个表达式,不经过表时, 出现NULL
mysql> explain select goods_id,goods_name,click_count from goods wher
_id=4 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
mysql> explain select max(goods_id) from goods \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL myisam表的max,min,count在表中优化过,不需要\真正查找,为NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Select tables optimized away
1 row in set (0.00 sec)
ref列 指连接查询时, 表之间的字段引用关系.
mysql> explain select goods_id,cat_name,goods_name from goods inner join ec
_category using(cat_id) where ecs_category.cat_name='' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: goods
type: ALL
possible_keys: cat_id
key: NULL
key_len: NULL
ref: NULL
rows: 31
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ecs_category
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: shop. goods.cat_id
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
rows : 是指估计要扫描多少行.
extra:
index: 是指用到了索引覆盖,效率非常高
using where 是指光靠索引定位不了,还得where判断一下
using temporary 是指用上了临时表, group by 与order by 不同列时,或group by ,order by 别的表的列.
using filesort : 文件排序(文件可能在磁盘,也可能在内存), (?????
select sum(shop_price) from goods group by cat_id(???? 这句话,用到了临时表和文件排序)
in 型子查询引出的陷阱
题: 在ecshop商城表中,查询6号栏目的商品, (注,6号是一个大栏目)
最直观的: mysql> select goods_id,cat_id,goods_name from goods where cat_id in (select
cat_id from ecs_category where parent_id=6);
误区: 给我们的感觉是, 先查到内层的6号栏目的子栏目,如7,8,9,11
然后外层, cat_id in (7,8,9,11)
事实: 如下图, goods表全扫描, 并逐行与category表对照,看parent_id=6是否成立
原因: mysql的查询优化器,针对In型做优化,被改成了exists的执行效果.
当goods表越大时, 查询速度越慢.
改进: 用连接查询来代替子查询
explain select goods_id,g.cat_id,g.goods_name from goods as g
inner join (select cat_id from ecs_category where parent_id=6) as t
using(cat_id) \G
内层 select cat_id from ecs_category where parent_id=6 ; 用到Parent_id索引, 返回4行
+--------+
| cat_id |
+--------+
| 7 |
| 8 |
| 9 |
| 11 |
+--------+ 形成结果,设为t
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: ecs_category
type: ref
possible_keys: parent_id
key: parent_id
key_len: 2
ref:
rows: 4
Extra:
3 rows in set (0.00 sec)
第2次查询,
t和 goods 通过 cat_id 相连,
因为cat_id在 goods表中有索引, 所以相当于用7,8,911,快速匹配上 goods的行.
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: g
type: ref
possible_keys: cat_id
key: cat_id
key_len: 2
ref: t.cat_id
rows: 6
Extra:
第1次查询 :
是把上面2次的中间结果,直接取回.
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra:
exists子查询
题: 查询有商品的栏目.
按上面的理解,我们用join来操作,如下:
mysql> select c.cat_id,cat_name from ecs_category as c inner join goods as g
on c.cat_id=g.cat_id group by cat_name; (见36)
优化1: 在group时, 用带有索引的列来group, 速度会稍快一些,另外,
用int型 比 char型 分组,也要快一些.(见37)
优化2: 在group时, 我们假设只取了A表的内容,group by 的列,尽量用A表的列,
会比B表的列要快.(见38)
优化3: 从语义上去优化
select cat_id,cat_name from ecs_category where exists(select *from goods where goods.cat_id=ecs_category.cat_id) (见40)
| 36 | 0.00039075 | select c.cat_id,cat_name from ecs_category as c inner
join goods as g on c.cat_id=g.cat_id group by cat_name
|
| 37 | 0.00038675 | select c.cat_id,cat_name from ecs_category as c inner
join goods as g on c.cat_id=g.cat_id group by cat_id
|
| 38 | 0.00035650 | select c.cat_id,cat_name from ecs_category as c inner
join goods as g on c.cat_id=g.cat_id group by c.cat_id
|
| 40 | 0.00033500 | select cat_id,cat_name from ecs_category where exists
(select * from goods where goods.cat_id=ecs_category.cat_id)
|
from 型子查询:
注意::内层from语句查到的临时表, 是没有索引的.
所以: from的返回内容要尽量少.
奇技淫巧!
min/max优化 在表中,一般都是经过优化的. 如下地区表
|
id |
area |
pid |
|
1 |
中国 |
0 |
|
2 |
北京 |
1 |
|
... |
|
|
|
3115 |
|
3113 |
我们查min(id), id是主键,查Min(id)非常快.
但是,pid上没有索引, 现在要求查询3113地区的min(id);
select min(id) from it_area where pid=69;
试想 id是有顺序的,(默认索引是升续排列), 因此,如果我们沿着id的索引方向走,
那么 第1个 pid=69的索引结点,他的id就正好是最小的id.
select id from it_area use index(primary) where pid=69 limit 1;
| 12 | 0.00128100 | select min(id) from it_area where pid=69 |
| 13 | 0.00017000 | select id from it_area use index(primary) where pid=69 limit 1 |
改进后的速度虽然快,但语义已经非常不清晰,不建议这么做,仅仅是实验目的.
count() 优化
误区:
1:myisam的count()非常快
答: 是比较快,.但仅限于查询表的”所有行”比较快, 因为Myisam对行数进行了存储.
一旦有条件的查询, 速度就不再快了.尤其是where条件的列上没有索引.
2: 假如,id<100的商家都是我们内部测试的,我们想查查真实的商家有多少?
select count(*) from lx_com where id>=100; (1000多万行用了6.X秒)
小技巧:
select count(*) from lx_com; 快
select count(*) from lx_com where id<100; 快
select count(*) frol lx_com -select count(*) from lx_com where id<100; 快
select (select count(*) from lx_com) - (select count(*) from lx_com where id<100)
3: group by
注意:
1:分组用于统计,而不用于筛选数据.
比如: 统计平均分,最高分,适合, 但用于筛选重复数据,则不适合.
以及用索引来避免临时表和文件排序
2: 以A,B表连接为例 ,主要查询A表的列,
那么 group by ,order by 的列尽量相同,而且列应该显示声明为A的列
4: union优化
注意: union all 不过滤 效率提高,如非必须,请用union all
因为 union去重的代价非常高, 放在程序里去重.
limit 及翻页优化
limit offset,N, 当offset非常大时, 效率极低,
原因是mysql并不是跳过offset行,然后单取N行,
而是取offset+N行,返回放弃前offset行,返回N行.
效率较低,当offset越大时,效率越低
优化办法:
1: 从业务上去解决
办法: 不允许翻过100页
以百度为例,一般翻页到70页左右.
1:不用offset,用条件查询.
例:
mysql> select id,name from lx_com limit 5000000,10;
+---------+--------------------------------------------+
| id | name |
+---------+--------------------------------------------+
| 5554609 | 温泉县人民政府供暖中心 |
..................
| 5554618 | 温泉县邮政鸿盛公司 |
+---------+--------------------------------------------+
10 rows in set (5.33 sec)
mysql> select id,name from lx_com where id>5000000 limit 10;
+---------+--------------------------------------------------------+
| id | name |
+---------+--------------------------------------------------------+
| 5000001 | 南宁市嘉氏百货有限责任公司 |
.................
| 5000002 | 南宁市友达电线电缆有限公司 |
+---------+--------------------------------------------------------+
10 rows in set (0.00 sec)
问题: 2次的结果不一致
原因: 数据被物理删除过,有空洞.
解决: 数据不进行物理删除(可以逻辑删除).
最终在页面上显示数据时,逻辑删除的条目不显示即可.
(一般来说,大网站的数据都是不物理删除的,只做逻辑删除 ,比如 is_delete=1)
3: 非要物理删除,还要用offset精确查询,还不限制用户分页,怎么办?
分析: 优化思路是 不查,少查,查索引,少取.
我们现在必须要查,则只查索引,不查数据,得到id.
再用id去查具体条目. 这种技巧就是延迟索引.
mysql> select id,name from lx_com inner join (select id from lx_com limit 5000000,10) as tmp using(id);
+---------+-----------------------------------------------+
| id | name |
+---------+-----------------------------------------------+
| 5050425 | 陇县河北乡大谈湾小学 |
........
| 5050434 | 陇县堎底下镇水管站 |
+---------+-----------------------------------------------+
10 rows in set (1.35 sec)
巧用变量
1:用变量排名
例: 以ecshop中的商品表为例,计算每个栏目下的商品数,并按商品数排名.
select cat_id,count(*) as cnt from goods group by cat_id order by cnt desc;
并按商品数计算这些栏目的名次
set @curr_cnt := 0,@prev_cnt := 0, @rank := 0;
select cat_id, (@curr_cnt := cnt) as cnt,
(@rank := if(@curr_cnt <> @prev_cnt,@rank+1,@rank)) as rank,
@prev_cnt := @curr_cnt
from ( select cat_id,count(*) as cnt from shop. goods group by shop. goods.cat_id order by cnt desc) as tmp;
2:用变量计算真正影响的行数
当插入多条,当主键重复时,则自动更新,这种效果,可以用insert on duplication for update
要统计真正”新增”的条目, 如下图,我们想得到的值是”1”,即被更新的行数.
insert into user (uid,uname) values (4,’ds’),(5,'wanu'),(6,’safdsaf’)
on duplicate key update uid=values(uid)+(0*(@x:=@x+1)) , uname=values(uname);
mysql> set @x:=0;
Query OK, 0 rows affected (0.00 sec)
总影响行数-2*实际update数, 即新增的行数.
3: 简化union
比如有新闻表,news , news_hot,
new_hot是一张内存表,非常快,用来今天的热门新闻.
首页取新闻时,逻辑是这样的:
先取hot, 没有 再取news,为了省事,用一个union来完成.
select nid,title from news_hot where nid=xxx
union
select nid,title from news where nid=xxx;
如何利用变量让后半句select不执行 ,
select id,content,(@find := 1) from news where id=1
union
select id,content,(@find :=1) from news2 where id=1 and (@find <= 0)
union 1,1,1 where (@find :=null) is not null;
3:小心变量的顺序
如下图:变量先在where中发挥作用,然后再是select操作.
如果where不成立,select操作再不发生.
第2例:
在这个例子中, 1,2两行,先排好序, 在内存中,就是这样的顺序 [2] [1]
再逐行where条件判断,取值.
对比这2张图,分析:
1: where先发挥作用,把需要的行 都给找出
2: 然后再逐行 select
因此, 前者, 最终select时,select@num变量,都是一个值
后者,不断select,不断修改@num的值, 值不断变化.
同时: 使用变量,将会使sql语句的结果不缓存.
事务:
事务的4个特性:ACID
原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）
以银行汇款为例,张三给李四转款300元.
原子性: 是指某几句sql的影响,要么都发生,要么都不发生.
即:张三减300, 李四+300 , insert银行流水, 这3个操作,必须都完成,或都不产生效果.
一致性: 事务前后的数据,保持业务上的合理一致.
(汇款前)张三的余额+李四的余额 ====== (汇款后) 张三的余额+李四余额
比如: 张三只有280元, 280-300=-20,储蓄卡不是信用卡,不能为负,因此张三余0元.
将导致, 汇款后,2者余额,汇款前,差了20元.
隔离性: 在事务进行过程中, 其他事务,看不到此事务的任何效果.
持久性: 事务一旦发生,不能取消. 只能通过补偿性事务,来抵消效果.
事务与引擎:
myisam引擎不支持事务, innodb和BDB引擎支持.
因此我们的实验用innodb表来做
事务的使用流程:
比较简单:
开启事务 start transaction
执行查询 xxxx
提交事务/回滚事务. commit / rollback
set session transaction isolation level [read uncommitted | read committed | repeatable read |serializable]
read uncommitted: 读未提交的事务内容,显然不符原子性, 称为”脏读”. 在业务中,没人这么用.
read commited: 在一个事务进行过程中, 读不到另一个进行事务的操作,但是,可以读到另一个结束事务的操作影响.
repeatable read: 可重复读,即在一个事务过程中,所有信息都来自事务开始那一瞬间的信息,不受其他已提交事务的影响. (大多数的系统,用此隔离级别)
serializeable 串行化 , 所有的事务,必须编号,按顺序一个一个来执行,也就取消了冲突的可能.这样隔离级别最高,但事务相互等待的等待长. 在实用,也不是很多.
设置事务的级别:
set session transaction isolation level read uncommitted;
典型问题:
有一业务场景,经过测试,读写比为1:20,请根据读写比,合理设置优化方案.
读写比:
写数据/读数据的比例,
Insert/update/delelte / select
不管具体技术,从"读写分离"的概念出发来推导一下基本的要素.
通俗的说:读服务器就是指写服务器的数据镜像.
从服务端看:要有N台从服务器和主服务器保持数据一致.
从客户端看:比如有一条insert语句和一条select 语句,
则要区分读/写语句,并且分别请求从/主服务器.
服务器端读写分离的具体技术
1:数据库集群技术
集群由3个概念
1:sql节点 sql node
2:数据节点 data node
3:管理节点 ndb managerment
Sql语句发送"1sql节点", "1sql"节点发往"2数据节点",再由3管理节点完成数据节点的之间的同步.
集群技术相对复杂,至少有3种节点,4台服务器才能完成.
2:数据库复制 replication
数据库复制 replication 的实现原理
1:主服务器凡运行语句,都产生一个二进制日志 binlog
2:从服务器不断读取主服务器的binlog
3:从主服务读取到的binlog,转换为自身可执行的relaylog,
4:执行relaylog
实现步骤:
1:首先确保主服务器打开二进制日志功能.
这样,主服务器一旦有数据变化,立即产生二进制日志.
2:从服务器也需要开启二进制日志和relay日志功能.
这样可以从主服务器读取binlog,并产生relaylog
3:在主服务器建立一个从服务器的账号,并授予数得上权限.
4: 指定从服务对应的主服务器,开启从服务器.
具体实施
本人虚拟机下有两台linux,IP为199 200
1: 200做从服务器
2: 199mysql,做为主服务器.
3: 保证主从3306端口互通.
4: 配置主服务器,打开binlog
#给服务器起一个唯一的id
server-id=1
#开启二进制日志
log-bin=mysql-bin
#指定日志格式
binlog-format=mixd/row/statement
重启mysql
已经能够充当master服务器
5: 配置从服务器打开binlog和relaylog
重启从服务器
6: 在主服务器上创建相应的复制账号
7: 在从服务器通过语句指定要复制的主服务器(注意,可以一主多从,不可一从多主).
8:启动从服务器功能
->start slave;
8: 测试.
常用语句:
show master status ; 查看master的状态, 尤其是当前的日志及位置
show slave stattus; 查看slave的状态.
reset slave ; 重置slave状态.
start slave ; 启动slave 状态(开始监听msater的变化)
stop slave; 暂停slave状态;
主服务器的日志格式用哪种好?
有 statement,row, mixed3种,其中mixed是指前2种的混合.
以insert into xxtable values (x,y,z)为例,
影响: 1行,且为新增1行, 对于其他行没有影响.
这个情况,用row格式,直接复制磁盘上1行的新增变化.
以update xxtable set age=21 where name=’sss’;
这个情况,一般也只是影响1行. 用row也比较合适.
以过年发红包,全公司的人,都涨薪100元.
update xxtable set salary=salary+100;
这个语句带来的影响,是针对每一行的, 因此磁盘上很多row都发生了变化.
此处,适合就statment格式的日志.
2种日志,各有各的高效的地方,mysql提供了mixed类型.
可以根据语句的不同,而自动选择适合的日志格式.
主主复制
在上面的配置中,2台服务器地位有差别,一主一从.
从服务器一是起到备份作用,一是起到分担查询压力的作用.
接下来的配置,2台服务器之间,没有明显的地位差距, 两者可以同步对方的内容.
一般的格局如下图:
两台服务器相互复制
大致思路:
1: 2台服务器都设置上2进制日志和relay日志
2: 都设置上replcation账号
3: 都设置对方为自己的master
主主复制下一定要注意避免的问题---------同步冲突
例:
create table stu (
id int primary key auto_increment.
)......
2台mysql地位相等, 假如2个请求同时到达2台服务器,
请求的A节点, stu 的id为1
请求的B 节点, stu的id为1 ,
同步--->冲突
如何解决?
让1台服务器 1,3,5,7来增长
另1台服务器 2,4,6,8来增长
一台服务器：
set global auto_increment_increment = 2;
set global auto_increment_offset = 1;
set session auto_increment_increment = 2;
set session auto_increment_offset = 1;
另一台服务器：
set global auto_increment_increment = 20;
set global auto_increment_offset = 20;
set session auto_increment_increment=20;
set session auto_increment_offset = 20;
注：auto-increment-increment 和 auto-increment-offset 要写到配置文件 中，防止下次重启后失效．
操作后得到如下类似效果
如果后期需要加服务器,这个办法就有限制了.
我们可以在业务逻辑上来解决,
比如在racle 有sequnce,序列.
序列每次访问,生成递增/递减的数据.
以redis为例, 我们可以专门构建一个 global:userid
每次PHP插入Mysql前,先 incr->global:userid, 得到一个不重复的userid.
被动模式下的主主复制
是指 2台服务器地位一样, 但其中一台为只读,并且业务中也只写某1台服务器.
好处: 如果供写入的服务器出了故障,能迅速的切换到从服务器,
或者出于检修等目的,把写入功能切换到另一台服务器也比较方便.
拥有从服务器的主主复制,在实际应用中也很多.
环形结构 ()
即3台服务器,每台服务器均与其前/后的服务器形成master/slave的关系
不好之处: 任一台服务器坏了,影响其他2台. 不如2台形成的主主复制稳定.
在实际使用中,并不多.
如何在客户端应用的时候,路由语句.
一般用两种方法
1: 直接在PHP的mysql类做判断,最简单,不用额外加软件
比如discuz论坛.
2:用集群中间件
比如官方的mysql_proxy
,还有国产的中间件 amoeba
实战: mysql 读写分离 ,mysql_proxy实现
下载安装mysql_proxy:
解压后, 该目录包含已经编译好的二进制文件 .
1: 利用mysql_proxy实现负载均衡
执行mysql_proxy
./mysql-proxy-path/bin/mysql-proxy \
--proxy-backend-addresses=192.168.1.199:3306 \
--proxy-backend-addresses=192.168.1.200:3306
2: 连接mysql_proxy,用mysql客户就可以,因为proxy是mysql的前端代理
注意proxy的端口是4040
mysql -h “proxy的IP” -P 4040 -u username -p password
在连接上之后,做sql查询,却总是往某1台mysql server来发送------负载均衡没体现出来?
答: 不是没体现出来.
均衡不是体现在sql语句,一会请求mysqlA服,一会请求mysqlB服.
均衡是体现”连接”的均衡上.,
mysql_proxy会把连接mysql服务器的tcp/IP连接缓存进连接池,以提高性能.
在缓存池里, 缓存的连接大致是平均分配在每台mysql服务器上.
但具体的每一个连接,始终连某台服务器.
读写分离：
./bin/mysql-proxy \
--proxy-backend-addresses=192.168.1.199:3306 \
--proxy-read-only-backend-addresses=192.168.1.200:3306 \
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
读写分离简写:
./bin/mysql-proxy –b 192.168.0.199:3306 –r 192.168.0.200:3306 –s /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
表分区 partition
当一张表的数据非常多的时候,比如单个.myd文件都达到10G, 这时,必然读取起来效率降低.
可不可以把表的数据分开在几张表上?
1: 从业务角度可以解决. (分表)
比如, 通过id%10 , user0 , user1....user9, 这10张表
根据不同的余数,来插入或查询某张表.
2: 通过mysql的分区功能
mysql将会根据指定的规则,把数据放在不同的表文件上.
相当于在文件上,被拆成了小块.
但是,给客户的界面,还是1张表.
常用的规则:
根据某列的范围来分区, 也可以某列的散点值来分区.
示例: 按列的范围来分区
以用户表为例, uid
uid [1,10) ---> user partition u0
uid[10, 20) ---> user partition u1
uid [20, MAX] --> user partion u2
分区 按range分区
create table goods (
id int,
uname char(10)
)engine myisam
partition by range(id) (
partition p1 values less than (10),
partition p2 values less than (20),
partition p3 values less than MAXVALUE
);
按散点值分区
有一张省表
|
pid 主键 |
prov 省名 |
|
1 |
北京 |
|
2 |
安徽 |
|
... |
|
|
35 |
西藏 |
user 表,想按省来分区,
|
uid 主键 |
pid 省份 |
uname 用户名 |
|
1 |
1 |
张北京 |
|
15 |
2 |
赵安徽 |
|
|
|
|
create table user2 (
uid int,
pid int,
uname char(20)
)engine myisam charset utf8
partition by list(pid) (
partition bj values in (1),
partition ah values in (2),
partition xb values in (3,4,5)
);
注意: 在使用分区的时候,注意,分区的那个列,值不要为NULL
(如果不小心为NULL,mysql为理解为0,尽量执行之)
注 :分区甚至可以按照表达式的返回值,计算所属区.
但用表达式,不如直接用值来得快. 根据情况而定.
比如,用 partition by range (year(regtime)) 可以按注册年份来分区.
服务器参数
A B C D 4台服务器
局域网的IP分别是:
192.168.1.201
192.168.1.202
192.168.1.203
192.168.1.204
账号都是: root
密码都是: zixue.it
附录: mysql show processlist中的State的意义
Checking table 正在检查数据表（这是自动的）。
Closing tables
正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
复制从服务器正在连接主服务器。
Copying to tmp table on disk
由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
正在创建临时表以存放部分查询结果。
deleting from main table
服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables 服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables 正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed 发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
被其他查询锁住了。
Sending data
正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group 正在为GROUP BY做排序。
Sorting for order
正在为ORDER BY做排序。
Opening tables
这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
修复指令正在排序以创建索引。
Repair with keycache
修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update 正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping
正在等待客户端发送新请求.
System lock
正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgrading lock
INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating
正在搜索匹配的记录，并且修改它们。
User Lock
正在等待GET_LOCK()。
Waiting for tables 该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。
关键字词：mysql,优化
上一篇：mongodb
下一篇：mysql优化-附件