记录

最新推荐文章于 2023-01-21 10:42:26 发布

sanguote

最新推荐文章于 2023-01-21 10:42:26 发布

阅读量1.7k

点赞数

错误！未找到目录项。

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-devellibmcrypt* libtool* cmake

# mkdir /usr/local/mysql

# mkdir -p /data/mysql

3、创建用户和用户组

# groupadd mysql

# useradd -g mysql mysql

4、赋予数据存放目录权限

# chown mysql.mysql –R /data/mysql

二、安装MySQL5.5.35

1、获取解压mysql-5.5.35.tar.gz

在mysql.com官网或国内镜像下载源码

# wgethttp://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 localMySQL 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

注:出现如下错误,则yum安装automake,libtoolize

automake 1.10.x (aclocal) wasn't found, exiting

libtoolize 1.4+ wasn't found, exiting

出现如下问题,则说明需要安装mysql-devel,即mysql的头文件

drv_mysql.c: 在函数‘mysql_drv_done’中:

drv_mysql.c:851: 警告：隐式声明函数‘mysql_library_end’

#../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 willbe 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 willbe 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 IOtest: 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

responsetime:

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.sockprepare

实测结果: 双核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 willbe ignored

Threads started!

OLTP test statistics:

queries performed:

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 = 'usebigdata';

mysql_query($sql,$conn);

$sql = 'setnames 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 'fromcache';

}

print_r($com);

status.sh

#!/bin/bash

while true

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

对于不规则的延迟现象的观察

不规则的延迟现象往往是由于效率低下的语句造成的,如何抓到这些效率低的语句.

可以用showprocesslist命令长期观察,或用慢查询.

Show processlist;

这个命令是显示当前所有连接的工作状态.

#!/bin/bash

while true

mysql -uroot -e 'show processlist\G'|grepState:|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 goodsgroup 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 indexcat_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 goodsgroup 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 goodsgroup 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 goodsorder 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 goodsorder 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 goodsorder 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 | thisis 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 byboard_id,得出每个版块下的帖子数.

如果有postnum字段,每发一个帖子时,对postnum字段+1;

再查询版块下的帖子数时, 只需要1条语句直接查boardid

select boradid, boardname,postnum fromboard;

典型的”空间换时间”

列选择原则:

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))

原因: 大的字段浪费内存,影响速度,

以年龄为例 tinyintunsigned 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,'') fromdict 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.1B-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米,

自己拼接一下,能否过河对岸,就知道索引能否利用上.

如上例中, wherea=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 byc2,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 andc2=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 andc2=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 andc2=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 byc3,c2的顺序无法利用c2,c3索引

mysql> explain select * from t4 where c1=1 andc4=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 andc4=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 andc5=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 andc2=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(distinctleft(word,6))/count(*) from dict;

+---------------------------------------+

| count(distinct left(word,6))/count(*) |

+---------------------------------------+

| 0.9992 |

+---------------------------------------+

1 row in set (0.30 sec)

对于一般的系统应用: 区别度能达到0.1,索引的性能就可以接受.

2:对于左前缀不易区分的列 ,建立索引的技巧

如 url列

http://www.baidu.com

http://www.zixue.it

列的前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 fromit_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 fromit_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 fromit_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 namelike '%东山%';

+------+-----------+------+

| 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 idfrom 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 fromgoods order by shop_price;

// using where,按照shop_price索引取出的结果,本身就是有序的.

select goods_id,cat_id,shop_price fromgoods order by click_count;

// using filesort 用到了文件排序,即取出的结果再次排序

重复索引与冗余索引

重复索引: 是指在同1个列(如age), 或者顺序相同的几个列(age,school), 建立了多个索引,

称为重复索引, 重复索引没有任何帮助,只会增大索引文件,拖慢更新速度, 去掉.

冗余索引:

冗余索引是指2个索引所覆盖的列有重叠, 称为冗余索引

比如 x,m,列 , 加索引 index x(x), index xm(x,m)

x,xm索引, 两者的x列重叠了, 这种情况,称为冗余索引.

甚至可以把 indexmx(m,x) 索引也建立, mx, xm 也不是重复的,因为列的顺序不一样.

索引碎片与维护

在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.

我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.

比如: 表的引擎为innodb , 可以 alter table xxx engine innodb

optimize table 表名 ,也可以修复.

注意: 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐.

这个过程,如果表的行数比较大,也是非常耗费资源的操作.

所以,不能频繁的修复.

如果表的Update操作很频率,可以按周/月,来修复.

如果不频繁,可以更长的周期来做修复.
sql语句优化

1: sql语句的时间花在哪儿?

答: 等待时间 , 执行时间.

这两个时间并非孤立的, 如果单条语句执行的快了,对其他语句的锁定的也就少了.

所以,我们来分析如何降低执行时间.

2: sql语句的执行时间,又花在哪儿了?

答：

a: 查 ----> 沿着索引查,甚至全表扫描

b: 取 ----> 查到行后,把数据取出来(sendingdata)

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 selectgoods_id,goods_name from goods wheregoods_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 fromgoods 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

*************************** 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 selectgoods_id,click_count from goods wheregoods_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 selectgoods_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 selectgoods_id,goods_name from goods wherecat_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 selectgoods_id,shop_price from goods innertjoin 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 selectgoods_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 selectgoods_id,cat_name,goods_name from goodsinner join ec

_category using(cat_id) whereecs_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 whereparent_id=6);

误区: 给我们的感觉是, 先查到内层的6号栏目的子栏目,如7,8,9,11

然后外层, cat_idin (7,8,9,11)

事实: 如下图, goods表全扫描, 并逐行与category表对照,看parent_id=6是否成立

原因: mysql的查询优化器,针对In型做优化,被改成了exists的执行效果.

当goods表越大时, 查询速度越慢.

改进: 用连接查询来代替子查询

explain select goods_id,g.cat_id,g.goods_namefrom goods as g

inner join (select cat_id from ecs_categorywhere parent_id=6) as t

using(cat_id) \G

内层 selectcat_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 fromecs_category as c inner join goods as g

onc.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_categorywhere exists(select *from goodswhere 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 bycat_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 bycat_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 byc.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 wherepid=69;

试想 id是有顺序的,(默认索引是升续排列), 因此,如果我们沿着id的索引方向走,

那么第1个 pid=69的索引结点,他的id就正好是最小的id.

select id from it_area useindex(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 whereid>=100; (1000多万行用了6.X秒)

小技巧:

select count(*) from lx_com; 快

select count(*) from lx_com whereid<100; 快

select count(*) frol lx_com -selectcount(*) from lx_com where id<100; 快

select (selectcount(*) 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 limit5000000,10;

+---------+--------------------------------------------+

| id | name |

+---------+--------------------------------------------+

| 5554609 | 温泉县人民政府供暖中心 |

..................

| 5554618 | 温泉县邮政鸿盛公司 |

+---------+--------------------------------------------+

10 rows in set (5.33 sec)

mysql> select id,name from lx_com whereid>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 innerjoin (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 fromshop. goods group by shop. goods.cat_id order by cnt desc) as tmp;

2:用变量计算真正影响的行数

当插入多条,当主键重复时,则自动更新,这种效果,可以用insert onduplication for update

要统计真正”新增”的条目, 如下图,我们想得到的值是”1”,即被更新的行数.

insert into user (uid,uname) values (4,’ds’),(5,'wanu'),(6,’safdsaf’)

onduplicate 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 wherenid=xxx

union

select nid,title from news where nid=xxx;

如何利用变量让后半句select不执行 ,

selectid,content,(@find := 1) from news where id=1

union

selectid,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 levelread 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 intoxxtable values (x,y,z)为例,

影响: 1行,且为新增1行, 对于其他行没有影响.

这个情况,用row格式,直接复制磁盘上1行的新增变化.

以updatexxtable 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 = 2;

set global auto_increment_offset = 2;

set session auto_increment_increment=2;

set session auto_increment_offset = 2;

注：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:

http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz

解压后, 该目录包含已经编译好的二进制文件 .

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 (

idint,

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 user (

uid int,

pid int,

uname

)engine myisam

partition by list(pid)(

partition bj values in(1),

partition ah values in(2),

partition xb values in(4,5,6)

);

注意: 在使用分区的时候,注意,分区的那个列,值不要为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　正在执行FLUSHTABLES，等待其他线程关闭数据表。
Killed　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
　被其他查询锁住了。
Sending data
　正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group　正在为GROUP BY做排序。
　Sorting fororder
　正在为ORDER BY做排序。
Opening tables
　这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates
　正在执行一个SELECTDISTINCT方式的查询，但是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
　INSERTDELAYED正在尝试取得一个锁表以插入新记录。
Updating
　正在搜索匹配的记录，并且修改它们。
User Lock
　正在等待GET_LOCK()。
Waiting for tables　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE,ANALYZE TABLE,或OPTIMIZETABLE。
waiting for handler insert
　INSERTDELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
　大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
　还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。

sanguote

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
记录

错误！未找到目录项。 Q: 为什么别人问你MySQL优化的知识总是没有底气.A: 因为你只是回答一些大而化之的调优原则, 比如:”建立合理索引”(什么样的索引合理?)“分表分库”(用什么策略分表分库?)“主从分离”(用什么中间件?)并没有从细化到定量的层面去分析.如qps提高了%N? 有没有减少文件排序?语句的扫描行数减少了多少? 没有大量的数据供
复制链接

扫一扫