mysql高性能优化方案

来源：互联网发布：山西省网络文学院编辑：程序博客网时间：2024/06/05 02:45

Show status;

Threads_connected:当前的客户端已经连接的数量，这个值会少于预设值，

Threads_running:记录了并发执行stmt/command的数量。正在运行。

Awk:

1.读取下一行，并把下一行赋给$0,各列赋给$1,$2,

2.用指定命令处理改行。

3.awk'/l/{printf("%s\n",$1)}' test.txt

mysqladmin -u root ext |

注意：有的服务器可能需要输入密码，需要加-p参数。

mysqladmin-u root ext|awk '/Queries/{printf("%d",$4)}';

mysqladmin -u root ext|awk'/Threads_connected/{printf("%d",$4)}';

mysqladmin -u root ext|awk'/Threads_running/{printf("%d",$4)}';

mysqladmin -u root ext|awk'/Queries/{printf("%d",$4)}/Threads_connected/{printf("%,$4)}/Threads_running/{printf("%d",$4)}';

创建测试表：

create table t_user(

id smallint(11) auto_increment,

username char(100) not null comment 'name',

address char(100) not null comment 'address',

primary key(id)

) engine=myisam defaultcharset=utf8;

PHP写测试数据插入100000条数据

<?php

$t=time();

set_time_limit(1000);

$myFile="/var/www/insert.sql";

$fhandler=fopen($myFile,'wb');

if($fhandler){

$sql="'zhangsan'\t'zhong guancun nan da jie'";

$i=0;

while($i<1000000)//1,000,000

{

$i++;

fwrite($fhandler,$sql."\r\n");

}

echo"写脠鲁晒娄,潞脛卤拢潞",mktime()-$t;

}

load data local infile '/var/www/insert.sql' into tablet_user(username,address);

    Threads_connected 当前打开的连接的数量

获取状态的脚本：

while true

./mysqladmin -u root 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

./ab -c 50 -n 200000 http://10.18.93.124/callback.php

Telnet 10.18.93.122 11211

请不要按ctrl+]

处理status.txt文件

每秒的请求数

awk '{q=last;last=$1}{printf("%d %d %d\n",$1,$2,$3)}'status.txt

MySQL>show processlist

./mysql -u root -e 'show processlist'

./mysql -u root -e 'show processlist\G'|grep State

./mysql -u root -e 'show processlist\G'|grep State|uniq|sort -rn

//查询processliststate的脚本。

#!/bin/bash

while true

./mysql -u root -e 'show processlist\G'|grepState|uniq|sort -rn >> proce.txt

usleep 100000

done

Sysbench 对硬盘io性能的测试：

测量CPU 和io能力。

./sysbench --test

./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include--with-mysql-libs=/usr/local/mysql/lib && make && make install

./sysbench: error while loading shared libraries:libmysqlclient.so.18: cannot open shared object file: No such file or directory

ln -s /usr/local/mysql/lib/libmysqlclient.so.18/lib64/libmysqlclient.so.18

Cpu性能测试：cpu测试使用64的整数，测试计算素数直到摸个最大值所需要的时间。

./sysbench --test=cpu--cpu-max-prime=20000 run

Test execution summary:

total time: 58.8928s

total number ofevents: 10000

total time taken by eventexecution: 58.8342

per-request statistics:

min: 2.02ms

avg: 5.88ms

max: 32.30ms

approx. 95 percentile: 7.33ms

Threads fairness:

events (avg/stddev): 10000.0000/0.00

execution time(avg/stddev): 58.8342/0.00

我们只关心total time即可。

测试thread测试：

sysbench --test=threads --num-threads=64 run

Test execution summary:

total time: 15.8065s

total number of events: 10000

total time taken by eventexecution: 1008.8293

per-request statistics:

min: 0.66ms

avg: 100.88ms

max: 385.39ms

approx. 95 percentile: 198.66ms

Threads fairness:

events (avg/stddev): 156.2500/4.99

execution time(avg/stddev): 15.7630/0.03

主要看total time 2.4值越低说明线程调度的性能越高。

内存测试：

sysbench --test=memory --memory-block-size=8K--memory-total-size=2G --num-threads=16run

上面的语句的含义为：传输2G数据，每个block的大小为8k，我们关心的是吞吐量：

2048.00 MB transferred (8030.45 MB/sec)

Number of threads: 16

Doing memory operations speed testMemory block size: 8K

Memory transfer size: 2048M

Memory operations type: writeMemory scope type: globalThreads started!Done.

Operations performed: 262144 (1027897.89 ops/sec)

2048.00 MB transferred (8030.45 MB/sec)

Test execution summary:

    total time:                          0.2550s

    total number of events:              262144

    total time taken by event execution: 3.1911

    per-request statistics:

         min:                                  0.00ms

         avg:                                  0.01ms

         max:                                 29.55ms

         approx.  95 percentile:               0.00ms

Threads fairness:

    events (avg/stddev):           16384.0000/926.14

    execution time (avg/stddev):   0.1994/0.02

重要参数：

每秒请求数是88.33 Requests/sec ，吞吐量是1.3802Mb/sec

文件io的测试：

1.生成测试数据，生成的测试数据文件要比内存大，如果文件的数据能够完全放倒内存中则操作系统缓存了大部分的数据，导致测试结果无法体现io密集型的工作负载。

首先先创建一个数据集：

sysbench --test=fileio --file-total-size=40G prepare

sysbench --test=fileio --file-total-size=40G --file-test-mode=rndrw\

                --init-rng=on --max-time=300 --max-requests=0 run

Extra file open flags: 0128 files, 240Mb each30Gb total file sizeBlock size 16KbNumber of random requests for random IO: 0Read/Write ratio for combined random IO test: 1.50Periodic FSYNC enabled, calling fsync() each 100 requests.Calling fsync() at the end of test, Enabled.Using synchronous I/O modeDoing random r/w testThreads started!Time limit exceeded, exiting...Done.

Operations performed:  15900 Read, 10600 Write, 33842 Other = 60342 TotalRead 248.44Mb  Written 165.62Mb  Total transferred 414.06Mb  (1.3802Mb/sec)

   88.33 Requests/sec executed

Test execution summary:

    total time:                          300.0074s

    total number of events:              26500

    total time taken by event execution: 164.1563

    per-request statistics:

         min:                                  0.01ms

         avg:                                  6.19ms

         max:                                315.51ms

         approx.  95 percentile:              15.83ms

Threads fairness:

    events (avg/stddev):           26500.0000/0.00

    execution time (avg/stddev):   164.1563/0.00

Otlp测试：

对mysql的oltp的测试，需要经历prepare，run，cleanup三个阶段，prepare阶段在数据库中产生一张指定行数的表，默认表在sbtest下，表名为sbtest

./sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root--db-driver=mysql --mysql-db=test --oltp-table-name=t1 --oltp-table-size=3000--mysql-socket=/tmp/mysql.sock

3.6 oltp

下面来看最重要也是最复杂的测试————oltp。oltp 基准测试模拟了一个简单的事物处理系统的工作负载。下面的例子使用的是一张超过百万行记录的表，第一步是先生成这张表：

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test \

                --mysql-user=root prepare

生成数据只需要上面这条简单的命令即可。这条命令在test 数据库中新建了一个表(sbtest)，并在表中插入了1000000条记录。

对于非默认安装的mysql，需要指定连接到msyql服务器的socket(my.cnf中的socket值)，如下所示：

sysbench --test=oltp --oltp-table-size=1000000 --mysql-user=root \--mysql-db=test --mysql-socket=/data/ntse/lmx/sysbench/var/mysqld.sock \

prepare

数据加载完成以后就可以开始测试了，这个例子采用了16个线程，测试时长为720秒：

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test \

                --mysql-user=root --max-time=720 --max-requests=0 \

                --num-threads=16 --oltp-test-mode=complex run

与插入记录时一样，如果mysql是非默认安装，还需要指定--mysql-socket的值。

Number of threads: 16

Doing OLTP test.Running mixed OLTP testUsing Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)Using "BEGIN" for starting transactionsUsing auto_inc on the id columnThreads started!Time limit exceeded, exiting...(last message repeated 15 times)Done.

OLTP test statistics:

    queries performed:

        read:                            26225724

        write:                           9366330

        other:                           3746532

        total:                           39338586

    transactions:                        1873266 (2601.71 per sec.)

    deadlocks:                           0      (0.00 per sec.)

    read/write requests:                 35592054 (49432.47 per sec.)

    other operations:                    3746532 (5203.42 per sec.)

Test execution summary:

    total time:                          720.0136s

    total number of events:              1873266

    total time taken by event execution: 11506.8251

    per-request statistics:

         min:                                  2.37ms

         avg:                                  6.14ms

         max:                                400.48ms

         approx.  95 percentile:              14.90ms

Threads fairness:

    events (avg/stddev):           117079.1250/275.62

    execution time (avg/stddev):   719.1766/0.01

如上所示，结果中包含了相当多的信息。其中最有价值的信息如下；

· 总的事务数

· 每秒事务数

· 时间统计信息（最小，平均，最大响应时间，以及95%百分比响应时间）

· 线程公平性统计信息

最最重要的当然是每秒事务数(2601.71per sec.)。

oltp 测试注意事项：

--max-requests --max-requests 默认值为10000 ，如果设置了--max-requests 或者使用默认值，分析结果的时候主要查看运行时间(total time)，一般情况下，都将--max-requests 赋值为0 ，即不限制请求数量，通过--max-time 来指定测试时长，然后查看系统的每秒事务数。

--oltp-test-mode

--oltp-test-mode用以指定测试模式，取值有(simeple,complex,nontrx)，默认是complex。不同模式会执行不同的语句。具体执行语句如下所示：

Simple 这种模式只是简单的执行selec语句。

SELECT c FROM sbtest WHERE id=N

complex(Advanced transactional) 在事务中，可能包含下列语句。

Point queries:

SELECT c FROM sbtest WHERE id=N

Range queries:

SELECT c FROM sbtest WHERE id BETWEEN N AND M

Range SUM() queries:

SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M

Range ORDER BY queries:

SELECT c FROM sbtest WHERE id between N and M ORDER BY c

Range DISTINCT queries:

SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c

UPDATEs on index column:

UPDATE sbtest SET k=k+1 WHERE id=N

UPDATEs on non-index column:

UPDATE sbtest SET c=N WHERE id=M

DELETE queries:

DELETE FROM sbtest WHERE id=N

INSERT queries:

INSERT INTO sbtest VALUES (...)

nontrx(Non-transactional) 这种模式包含下列SQL语句。

Point queries:

SELECT pad FROM sbtest WHERE id=N

UPDATEs on index column:

UPDATE sbtest SET k=k+1 WHERE id=N

UPDATEs on non-index column:

UPDATE sbtest SET c=N WHERE id=M

DELETE queries:

DELETE FROM sbtest WHERE id=N

INSERT queries:

INSERT INTO sbtest (k, c, pad) VALUES(N, M, S)

Run：对上面产生的表进行oltp测试：

./sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root--db-driver=mysql --mysql-db=test --oltp-table-name=t1 --oltp-table-size=3000--mysql-socket=/tmp/mysql.sock run;

State：

Null

Sendingdata：返回大数据时。

排序之后去掉重复的：

more pro.txt |sort|uniq -c|sort -rn

Null，不用管，statistics正在进行语法的分析。

Copying to tmp table

Sending data

Sorting result

Converting heap to myisam:查询结果太大，内存不够大，只好往磁盘上放

Show variables like ‘%size%’;

tmp_table_size:16777216

set global tmp_table_size=1024;

set session tmp_table_size=1024;

查看慢查询

show variables like 'long%';

设置慢查询

set long_query_time=5;

首先要开启profilling,

Showvariables like ‘%pro%’

setprofiling=1;

showprofiles;

setnames utf-8;

select * from t_user limit 10000;//查询10000条记录

show profile cpu for query 6;

Opening talbes //打开表

Table lock //锁上表

Init 初始化

Optimizing //优化语句

Preparing //准备

Sendin data

select * from t_user group by id%20;

慢日志查询可以知道哪些sql语句执行效率低下，通过explain 可以知道sql语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。

如果explain 的信息不够详细，可以使用profiling命令得到更准确的sql执行消耗资源的信息

使用连接（join）来代替子查询（sub-queries）

使用join之所以更有效率一些，是因为mysql不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

使用enum，char, 而不是varchar，使用合理的字段属性长度。

尽可能的使用not null

固定长度的表会更快

拆分大的delete 或insert

查询的列越小越好

show processlist详解：

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

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

|207|root |192.168.0.20:51718 |mytest | Sleep | 5 | |NULL

|208|root |192.168.0.20:51719 |mytest | Sleep | 5 | |NULL

|220|root |192.168.0.20:51731 |mytest |Query | 84 |Locked |

selectbookname,culture,value,type from book where id=001

Command列，显示当前连接执行的命令：一般为sleep，query，connect，time，

Time是这个状态持续的时间。State，只是语句执行的某一个状态，一个sql语句，以查询为例可能需要经过coping to tmp table,sorting result sending data 等状态才可以完成，info，显示这个sql语句

表的优化，和列类型的选择：

列选择优先级：整型>date,time>char varchar>blob

2.够用就行，不要慷慨，（smalllint,varchar(n)）

大的字段浪费内存影响速度。

以varchar（10），varchar（300）存储的内容相同，但是表联查时，varchar（300）要花很多的内存。

3.尽量避免用null

Null，不利于索引，要用特殊的字节来标注。

要用多余的字符来存储null。

在磁盘的占据的空间更大。

select * from t5 where name is null; 才能查出来。

如果允许为null，key len会多一个字节。

每一行多一个字节，如果上亿行就更大了。

Enum 是枚举类型，内部是整形。

Enum的列在内部是用整形存储的

create table t5(

sex enum('male','female') not null default 'male'

)engine myisam charset utf8;

Enum比varchar省空间。

性别，学历要用enum，省空间。

其实内部用整形来存的。

多列索引生效

Btree索引：

Mysiam,innodb 用b-tree.

Ndb 用t-tree

B-tree系统，可理解为排好序的快速查找结构。

Hash索引

在memory表里，默认的是hash索引，hash的理论的时间复杂度为

O(1):一次查询就能查到。

Hash函数计算的结果是随机的，随机放在磁盘中，

找数据的速度很快，但是随机拿数据很慢。

不能对范围进行优化。有利于范围查询。

Btree索引常见的误区：

1.where 条件常用的列上都加上索引

错，例如：wherecat_id=3 and price>100;

Cat_id上，和price上都加上索引只能用上一个，因为是独立索引。

在多列建立索引，如index(a,b,c);

多列索引，必须用到第一个，否则不生效，

在多列索引上，索引需要发挥作用需要满足左前缀要求。

要从到右使用。

Index（a，b，c）

Explain ：

Type ：range 指的是范围，

Key_len:12 一个字符等于3个字节，4个全用上了12；

Key_len:6 说明两个索引起了作用。

Using filesort；c5本身就是无序的所以要排一下序。

一般而言，分组统计先按分组字段有序排列。

用到临时表排列，在临时表中聚合运算。

与myisam重要的区别是：虽然都是b+tree，但是innodb表数据本身就是按照b

+ tree 组织的索引结构，这棵树的叶节点data保存了完整记录，这个索引的

Key就是主键。

myisam和innodb索引实现的不同

2012-11-30 0 个评论作者：wangjj20

收藏我要投稿

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为 InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

辅助索引需要检测两遍索引，辅助索引的叶子节点存放了数据，和主键的值，首先要

找到数据，然后根据数据的主键，查找主键索引，找到记录。

Myisam的辅助索引还是地址。

Myisam检索索引算法，按照b+tree算法搜索索引，如果指定的key存在，则取出data域的值（地址），读取相应的记录。

Myisam 和innodb索引的区别。

Innodb的（辅助索引）指向对主键的应用，

Myisam的次索引和主索引都指向物理行。

聚簇索引：

直接

聚簇索引分块特点：

节点会分裂。

对聚簇索引比较严重。

对于myisam，节点存储的对物理行的地址，又缓存在

内存中，分裂较快。

对于innodb，因为节点下存储了行数据，因此在分裂的时候，还需要移动行数据。

对于innodb而言，因为节点有数据文件，节点分裂比较慢，对于innodb主键，尽量

是整型，而且是递增的，如果是无规律的的数据，将会产生页的分裂。降低效率。

可以查看innodb_page_written1471

这个值在随机插入要比顺序插入要搞得的多。

Show status;

Innodb_insert_block;

索引覆盖：

由索引-》磁盘取数据

回行

索引的速度非常快，因为索引是一个特别快的数据结构，而且

要缓存在内存中。

查索引是快的，回行是慢的。

回行的时候，需要到磁盘上去。

//username 索引

Select username ,age from table whereusername=’li’;

Select username from table where username=’li’//不需要回行，查询速度非常快

这种叫做索引覆盖。

索引覆盖是指，如果查询的列恰好是索引的一部分，那么查询只需要在

索引文件上进行，不需要回行到磁盘上在找数据。

这种查询速度非常快。

清除查询缓存：resetquery cache;

Use index 出现了索引覆盖。

实例：

Create table A{

id varchar(64) primary key .

Ver int;

}

在id，ver上有联合索引

10000条数据

Select id from A order by id 特别慢

Select id from A order by id,ver 就非常快。

Innodb中，数据记录一定，innodb块大小1024，

如果叶子的数据大，1024/1024=1条数据，10000条数据，

就需要10000个块，查找时要横跨很多块。

是innodb引擎，

有多个比较长的列

1.聚簇索引，导致id排序时，要跨过很多块。

2.有比较长的列，导致块比较多。

1.如果是myisam不存在这个问题。

Id.ver 联合索引

Id.ver

$conn=mysql_connect('localhost','root');

mysql_query('use test',$conn);

mysql_query('set names utf8',$conn);

$str=str_repeat('m',3000);

for($i=0;$i<=10000;$i++)

{

$id=dechex($i);

$sql=sprintf("insert into t10values('%s',%d,'%s','%s','%s')",$i,$i,$str,$str,$str);

mysql_query($sql,$conn);

}

Show index from t10\G;//查看索引

alter table t10 add index idver(id,ver);//添加复合索引。

Using index 是表示索引覆盖。

Mysql 清除表的数据：

Truncate table teacher;

Primary key 主键索引必须是int型，如果是char型的

explain Select name from t12 where id=3 \G

用不到索引。

改成int型，就可以使用主键索引。

高效的索引：

1.频繁度

2.索引覆盖

3.区分度高

4.长度小

长度小，建立的块就少。

区分度，长度必须达到一种平衡。

//查到最长的记录

select * from t12 order by length(name)desc limit 1;

区分度：

Select distinct name from A

返回 A中不同的值

select left(name,2) from t12;

返回从左截取2个字符的值。

select count(distinct left(name,2))/count(*)from t12;

算出摸个字段重复的概率。

Varchar 类型会有变化，所以key_len 14// 3*4 +2 //一个utf8字符占3个字节。

alter table person add indexname(name(15));

alter table person modify name char(300);//改变列的长度。

对于前缀不易区分的列，

http://www.bai.du.com

http://www.zixue.it

1.可以倒过来查

2.违哈希。

create table p1(

id int primary key auto_increment not null,

name char(100) not null default ''

)engine innodb charset utf8;

insert into url (url)values("www.baidu.com"); //单独给摸一个列赋值。

alter table pp add crcurl int unsigned notnull defualt 0;

Select crc32(‘a’);//将a字符转化为2的32次方的整数。

alter table url add crcurl int unsigned notnull; //增加一个列

update url set crcurl=crc32(url);

selectcrc32('www.baidu.com');

explain select * from url wherecrcurl=387695885\G; //用crc32 构造哈希列

把字符串的列，构成整型，来降低索引的长度。

对于一般系统而言区别度到达0.1 ，索引的性能就可以接受。

所以建索引中指定索引长度。

alter table t12 add index name(name(4));//指定建立索引长度。

大数据量的分页效果

Select fileld from table limit 30,10;

比如每页显示perpage条，当前是n页

Limit（n-1）*perpage， N；

翻过了几十万条，数据很慢，

怎么优化：

1.从业务上去优化：

Select id ,name from t12 limit 10000,10;

0.02sec

Select id ,name from t12 limit 5000000,10

4.4sec

为什么limit m，n，m越大时查询越慢？

要逐行查找，用limitoffset 时不是先跳过在查询，

而是先查询在跳过。

就是要把100w行取出来，让后再跳过前100w行。

2个办法用索引查询：

Select id,name from t12 where id>100000 limit 10;

这次为什么快，因为id主键索引，迅速找到10000 ，让后去10

有个问题,要求前提数据没删过。

一般来说，大网站数据部物理删除，只做逻辑删除，

比如 is_delete=1做标记，显示是不要显示。

延迟关联：

1.让索引覆盖先快一些：

explain Select id from t12 limit5000000,10\G.

如果有name字段要回行，要在表中查。

Select id,name from t12 inner join (select id from t12 limit 50000,10) as tmpon t12.id=tmp.id;

1.34sec

select t12.id ,name from t12 inner join(select id from t12 limit 10000,10) as tmp on t12.id=tmp.id;

Reset query cache;

索引与排序：

1.对于覆盖索引，直接在索引上查询时，就是有顺序的。

2.先取出数据形成临时表，叫做filesort，（文件排序，但文件可能在磁盘上吗，也可能在内存中）。

我们争取目标--取出来数据就是有序的，利用索引来排序。

索引本身就是排序的。

Filesort 取出结果在排序，即取出结果在排序。

比如 good表中（cat_id,shop_price）组成联合索引。

Where cat_id =N order by shop_price 可以利用索引排上序。

Select good_id,cat_id shop_price from goodsorder by shop_price;

Using where 安装shop_price索引取出结果，本身就是有序的

重复索引

一个列上建了两个索引。（毫无意义，拖慢你的增删改的速度）

冗余索引：是可以的。

索引碎片的修复：

optimize table info;

Truncate table info；

reset query cache;

表大的时候非常耗资源。

多列索引。

复合索引：在两个以上的列建索引，就是复合索引。

多列索引可以理解成合并多列的值创建的有序的数组，当查询条件包含lastname 和fisetname

Select * from test where last_name=’ss’ andfirst_name=’li’

Sql 先会过滤lastname复合条件的记录，在基础上在过滤firstname符合条件的记录。

如果分别在lastname和firstname创建两个索引，mysql处理方式就不一样了。他只会选择一检索能力最强的检索，另外一个用不上。

主要因为，分别创建时创建了两个索引，不知道用那个

复合索引还是一个索引，

以下都能用上多列索引：

以下用不上复合索引

慢，等待时间：表被锁住了，看看连接数啊，看

执行时间：1.取了多少数据

2 扫描了多少行

Explain:

Simple(不含子查询) primary（含子查询）

Table ：表名，有可能是别名，

Derived 如from子查询时

Null

如 select1+2；

key_len:

Type: 是查询方式，

All，全表扫描，

Index 扫描索引节点。扫描的行数还是过多。

All实在磁盘上扫描所有行，index沿着索引文件扫描所有行

explain select id from info where id like '%'\G;

Range：范围

explain select id from info where id>3\G;

Range是摸个范围的扫描。

Ref：引用，索引确定摸个位置。

eq_ref;

Const :效率更高。

explain select id from info where id=3\G;

System，null最快。

explain select name from info where id in(1,2)\G;

误区： explainselect name from info where id in (select )

explain select name from info where id in(select id from good_id)\G;

Info表中的每一行取出id，去在good_id表对比，info表需要全表扫描。

explain select name from info where id in(1,2)\G;

不一样。

explain select info.id ,name from infoinner join (select id from good_id) as tmp on tmp.id=info.id\G;

Exsits应用：

select id,name from info as c where exists(select * from good_id as gwhere c.id=g.id);

explain select i.id,i.name from info as iinner join good_id as g on i.id =g.id group by id\G;

explain Select cat_id,cat_name fromcategrory as c where exists(select * from goods as g where g.cat_id=c.cat_id)\G;

不查，

少查，

快查。

1.关于Max，min优化。

explain select max(id) from info\G;

*************************** 1. row***************************

id: 1

select_type: SIMPLE

table: NULL

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

Select id from it_area use(primary) wherepid=1087 limit 1;

Select min(id) from it_area where pid=1087;

它的工作是先把整张表都找出来，在找到where1087的行，都找到最小的。

加了where就不快了，

给pid 加索引。

Count 优化。

Select count(*) from info;

Select count(*) from info id<=100;

Select (select count(*) from info)-(selectcount(*) from info where id<100) as cnt;

3.group by 用于统计，而不用筛选数据

以及用索引来避免临时表和文件排序

Union 优化，

Union all效率高。

数据库主从集群配置。

数据库的复制技术。

大致原理：

主服务器建立binlog，授权复制账号

从服务器建立relaylog 利用复制账号来监听服务器的日志。

#binarylog

log-bin=mysql-bin

#binlogformat

binlog_format=mixed

Slave server:

relay-log=mysql-relay

建立mysql服务器的id

#server-id

server-id=200

建立授权账号：

Mysql -u root

show grants;

Grant replication client,replication slaveon *.* to ‘repl’@’192.168.1.100’identified by ‘repl’

grant replication client,replication slave on*.* to 'repl'@'10.18.93.120' identified by 'repl';

问题：

The MySQL server is running with the --skip-grant-tablesoption so it cannot execute this statement？

解决：

flush privileges;

Slave:

给你一个账号，去你的master

Show master status;

Change master to

master_host=’192.168.1.199’

Master_user=’repl’

Master_password=’repl’

Master_log_file=’mysql-bin.00001’

Master_log_pos=268;

change master to

master_host='10.18.93.122',

master_log_file='mysql-bin.000029',

master_log_pos=7275,

master_user='repl',

master_password='repl' ;

Show slave status;

Reset slave;

Start slave;

问题：

Could not initialize master info structure; more errormessages can be found in the MySQL error log？

解决：

slave stop;

Reset slave;

change master to

master_host='10.18.93.121',

master_log_file='mysql-bin.000029',

master_log_pos=107,

master_user='repl',

master_password='repl' ;

Show slave status;

create table msss(

-> id intprimary key auto_increment not null,

-> namechar(10) not null default ''

-> )engine myisam charset utf8;

常用语句：

Show master status //查看master状态

Show slave status

Reset slave //重置slave

Start slave //启动slave一旦开启就监听master状态

Stop slave

日志格式：

Statement 语句级的

Row

Mixed

以insert into table values()为例，

影响1行，对于其他行没有影响，就用row格式

的比较合理。

直接复制上1行的新增变化。

以update table age=21 where name=sss

这个情况一般只是影响1行，用row也比较合适

Update table setsalary=salary+100

这个语句带来的影响是针对没一行的，因此磁盘上很多发生变化，

适用于statement格式日志。

2种日志，各有各的不同，mysql提供了mixed类型。

主主复制：

相当于分片加复制。

Master：

server-id=121

log-bin=mysql-bin

binlog_format=mixed

#添加relaylog

relay-log=mysql-relay

grant replication client,replication slave on *.* to'repl'@'10.18.93.120' identified by 'repl';

change master to

master_host='10.18.93.124',

master_log_file='mysql-bin.000001',

master_log_pos=361,

master_user='repl',

master_password='repl' ;

Start slave

Slave：

server-id=120

log-bin=mysql-bin

binlog-format=mixed

relay-log=mysql-relay

grant replication client,replication slave on *.* to'repl'@'10.18.93.121' identified by 'repl';

change master to

master_host='10.18.93.121',

master_log_file='mysql-bin.000032',

master_log_pos=3720,

master_user='repl',

master_password='repl' ;

Start slave

主主复制时，主键冲突问题解决？

让一台服务器 1，3,5,7

另一台服务器 2,4，6，8

Set session auto_increment_increment=2 //每步增长2

Set session auto_increment_offset=1 //从1开始增长

Set global auto_increment_increment=2 //每步增长2

Set global auto_increment_offset=1

Set session auto_increment_increment=2;

Set session auto_increment_offset=2;

Set global auto_increment_increment=2;

Set global auto_increment_offset=2;

如果后期加服务器，这个办法就有限制了，

我们可以再业务上来解决。

比如:

设计一个Oracle有sequnce序列。

序列每次访问生成递增或第减。

以redes 为例，我们构建一个global：userid

每次php插入mysql前，先incr-》global:userid 得到一个不重复的userid。

被动主主复制：

/etc/my.cnf

read-only=1

如果有问题可以可以快速切换。

路由sql语句：

Mysql-proxy

原理：

能读写分离，

还能做负载均衡

./bin/mysql-proxy --help-all

Proxy在那个端口，//默认4040

Proxy代理了那几台服务器：--proxy-backend-address

./bin/mysql-proxy -P 10.18.93.121:4040--proxy-backend-addresses=10.18.93.121:3306--proxy-backend-addresses=10.18.93.120:3306

mysql -h 10.18.93.120 -u root -p -P 4040

4条语句，没有给我们做均衡：

均衡不是语句带来的均衡，容易带来不一致。

Update

Delete

以连接为单位的均衡。

所以多建几个连接：

均衡是以连接为单位的。

读写分离：

A:mysql服务器：10.18.93.121

B mysql 服务器：10.18.93.120

C:mysql-proxy服务器：10.18.93.124

分别在 A,B服务器上建个账号：

grant all on *.* to 'root'@'10.18.93.120' identified by'';

建立10.18.93.124访问服务器的权限。

在客户端中：mysql -h 10.18.93.124 -u root -p-P 4040

Proxy服务器会直接拿10.18.93.121服务器写，

去那10.18.93.120去读

Mysql-proxy下载：

http://mirrors.sohu.com/mysql/MySQL-Proxy/

./bin/mysql-proxy--proxy-backend-addresses=10.18.93.120:3306 --

Proxy-read-only-backend-addresses=10.18.93.121：3306--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-spliting.lua

rw-spliting.lua这个脚本专门用来分析你的sql语句。分成不同的类型做判断。

简写：

./bin/mysql-proxy -b 10.18.93.121:3306 -r10.18.93.120:3306 -s/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

在mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua下找到：

if not proxy.global.config.rwsplit then

proxy.global.config.rwsplit = {

min_idle_connections = 1,

max_idle_connections = 2,

is_debug = false

}

End

表分区的概念：

create table top(

-> tid intprimary key auto_increment,

-> titlechar(20) not null default ''

-> )enginemyisam charset utf8

-> partitionby range(tid)(

-> partitiont0 values less than(10),

-> partitiont1 values less than(20),

-> partitiont2 values less than(MAXVALUE)

-> );

create table top( tid int primary key auto_increment, title char(20) notnull default '' )engine myisam charset utf8 partition by range(tid)( partitiont0 values less than(10), partition t1 values less than(20), partition t2 valuesless than(MAXVALUE) );

insert into top(tid,title) values(11,'a');

好处是：打开的线程数多，文件独立，不会死锁。

Create table user(

aid int，

uname char(6)

)engine myisam charset utf8;

partition by list(aid)(

Partition bj values in(1);

Partition hb values in(2),

Partition xs values in(3),

Partition ah values in(4)

);

Insert into user(uname,aid) values(‘poluy’,1);

Insert into user(uname,aid) values(‘lily’,2);

原子性：事情要不全做完，要不干脆不做，不可分割的。

即：张三减300 李四+300 insert银行流水，这3个操作，必须都完成，或者都不做。

一致性：钱的逻辑性是一致的，事物前后的数据保持业务的合理一致。

汇款前张三的余额+李四的余额==汇款后张三的余额+李四的余

如果用户有42亿，给42亿加100就会溢出了，那边减100没事

或者，就有200元，200-300=-100 是不行的

隔离性；

持久性：事务一旦发生，不能取消。

事务使用：

Start transcation

执行语句

Commit/callback

隔离性：

Start transaction;

Update account set money=money+100 where uname=’li’;

Commit;

Set session transaction isolation level read uncommitted;

Show variables like ‘%isolation%’

Set session transaction isolation level read committed

Session 1:start transaction session 2 start transaction

这是两个事务进行处理：

可重复读：

Set session transaction isolation level repeatable read;

查询系统的隔离级别：

select@@global.tx_isolation, @@tx_isolation;

设置隔离级别：

set global tx_isolation='read-uncommitted';

隔离级别的分类：

（read uncommitted）读取没有提交的内容，也叫脏读。

（read-committed）读取提交的内容

（repeatable-read）可重复读，会导致幻读。

Serializable(可串行化)

Mysql锁机制：

Myisam表锁：

可以通过show statuslike ‘table%’；

变量来分析系统上表锁定的争夺。

如果tableLOCKS_WAITED 比较高则说明存在着严重的表锁争用情况。

Session 1 session 2

Lock table person write;

Insert into person(name) values(‘guojia’); select * from person ;

阻塞，

Unlock tables; 等待

Session2 释放。

加读锁：

Lock table person read;

总结：

Session 加读锁时，session1 不能更新，只能查询，session 2 能查询，更新时需要等待session1释放锁。不能查询其他表。（读的时候，其他session能读，但是不能写）；

Session加写锁时，session1 能读，能写，session1，不能读，也不能写，需要session1，释放才可以。

并发插入：

Session1 加上读锁，该线程可以对表进行查询操作，但不能进行更新操作，其他表

虽然不能对表进行删除更新操作，却可以对表进行并发插入操作。

Myisam表调度：

写锁的优先级最高，即使读锁请求在写锁请求队列前。

当时可以降低写锁的优先级：

Set low_priority_updates=1;使连续发出的更新请求的优先级降低。

可以设置max_write_lock_count一个合适的值，当一个表的读锁达到这个值后，mysql

会暂时将写请求的优先级降低，给读进程机会。

Show variables like ‘max_write%’;

获得innodb系统来分析系统上行锁的争夺状态：

Show status like ‘innodb_row_lock%’

如果current_waits比较高，

Innodb_monitor查看状态：

Create table innodb_monitor(a int)engine=innodb;

Show innodb status\G;

Innodb的两种锁：

共享锁：lock inshare mode;

排他锁： for update；

共享锁：主要用来确认记录是否存在，并却确保没有人对这个记录update，delete。

排它锁：对于所定记录还需要更新操作应该加排他锁，forupdate；

Set autocommit=0;

共享锁：

Session1:加共享锁，

Session 1 可以select，

可以update。

Session2 可以select，update时，因为session1加锁了，等待

也可以加共享锁。因为session1，加锁了，session2加锁了，如果session1

Update 就会死锁。

Myisam 读锁是：session1可以读，不能update，session2 能读update。需要等待释放锁。

For update锁

Session1 加for update，session1 能够select，能够update，session2能select ,for update需要等到session1comit释放锁。

来自网上的总结：

Mysql优化之问题定位

2014-08-04 0 个评论来源：星空下的密码

收藏我要投稿

Mysql优化之问题定位

先扯淡下，很久没有来csdn写博客了，最近在学燕18的mysql优化，并且这位老师讲的高达上还接地气，今天刚好有空可以来总结这段时间学到的东西

先上一张流程图（这张图引自燕18的教程）

当遇到一台db服务器有问题的时候，首先不是去看代码哪里有问题，想sql语句是否写，表的结构是否合理之类的问题；而是需要从宏观的角度去看哪些地方有问题

第一步找出服务器问题所在，是否是硬件有瓶颈

如果一台服务器硬件本身就不好，只能承受100M的io读写，如果你非要它提供的io达到200M，那么就算你怎么优化也搞不定是吧，那么我们首先需要基准测试需要安装sysbench，它提供了cpu， Io， memory, mysql等性能的测试，；

1.cpu测试

sysbench--test=cpu --cpu-max-prime=2000000 run

2.io测试

sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw prepare

sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw run

sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw cleanup

3.OLTP测试

sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-socket=/tmp/mysql.sock --mysql-user=test --mysql-host=localhost --mysql-password=test prepare

通过这些测试之后差不过也能知道自己服务器的能力了， 如果发现服务器的性能不错， 但是依然不能满足用户的需求， 那么就只能是软件方面的问题了， 就需要定位到底是哪一块有问题

第二步，观察mysql在某段时间的连接状态，处理状态

如果硬件问题不大，那么我们就需要观察mysql的状态了，一般这个状态不是一时半会儿能搞定的，都是需要写个脚本在后台记录mysql在某一个周期的压力值记录，比如是一天，一周为一个周期；查看mysql的状态命令是showstatus;这个命令返回好几百行的东西，而我们只需要关注3行1.Queries，当前已经发生过的查询（可以用两个时间段的查询数量相减得到时间段内的查询数）2.Threads_connected ，当前有多少个连接连上mysql3. Threads_running，当前有几个线程正在运行通常是Threads_connected>= Threads_running, 因为连上mysql也不一定要工作，可能阻塞，挂起之类的

获取结果

1.我们写个脚本每隔一秒去读取这三个数追加到mysql.status文件里面2. 用ab工具模拟访问，用50个并发，发送20000个请求（这个页面的每一次请求会多次访问mysql），这样就能使上面那个脚本得到结果了ab -c 50 -n 2000http://59.69.128.203/JudgeOnline/nyistoj/index.php/Problem/index我们来查看这个mysql.status文件的内容我们用上一行的第一个值减去下一行的第一个值就可以得到每一秒的访问mysql数量，差不多是1000+，也可以看出基本上是有50个连接的，平均用两个线程在处理请求；可以再次写一个脚本做一下处理这样就得到每一秒的处理数量， 1000多一点儿，貌似不咋好的感觉

结果分析

1. 访问mysql的频率很稳定（如下图），那就从mysql的其它部分优化，比如表的结构， sql语句的优化， mysql的配置，引擎的选择，索引的优化等2.mysql 的访问频率呈周期性的变化（如下图），那么就是从峰值上优化；比如memcatch是否都是周期性失效，那么就可以用随机方式让失效地更加均匀，或者是让他在晚上3点左右失效，这个时候的访问量不大，到了第二天时memcatch的缓冲也基本上建立好了；或者是从业务角度优化，比如12306的放票，可以分省分时间段分批放票，这样就避免了全国各地大家集体抢票带来的超高峰值；也可以在高峰期的时候开启慢查询， processlist等工具分析到具体的sql语句；

三. 查看mysql进程的状态

如果需要知道mysql这个进程对处理sql语句的整体情况，那么我们需要用到show processlist 这个工具，这个工具主要是能够记录下来每一条sql执行的过程；我们写一个脚本抓取status，然后整体看看我们的mysql进程花的时间基本上都是在干什么；show processlist\G这里的Status状态可能情况比较多，不过我们主要是关注如下几个状态： 1. Create tmp table；创建临时表，比如用了右连接就会新建一张临时表 2. Sending Data ；发送数据，比如limit 1， 1000；那么这样就会传送大量的数据而花费时间，可以limit小一点儿 3. SortIng for Group；正在为分组排序，这个时候就优化一般是借助复合索引 4. Copying to tmp table on desk; 正在将内存的表拷贝的硬盘，主要是表太大，比如join一下就产生很大的表只能放硬盘了，避免join 5. Locked; 锁住数据，事务性方面优化，能不用事务就不用 6. Converting HEAP to MyISAM; 查询的结果太大，正在想硬盘存结果；优化就是尽量一次稍差点儿数据，比如新闻列表的读取一次少读点儿，读者很少一次性读到几百条以后；那么我们写一个脚本抓取这些status:

然后处理下mysql.process;

就能得到如下结果了：

从图中可以看出很多次都是花在了Copying to tmp table ，Sending data, Sort result 的次数不少, 可以大致知道是业务逻辑导致需要取出的数据比较多，可以变化业务或者做缓冲服务器挡在mysql前面；
看看 Copying to tmp table; 首先打开profiles;
打开监控，打开这个开关之后就能为sql的执行的每一个阶段拍快照，这样我们就能清楚得找知道sql的执行过程，具体花时间在哪个阶段了，再有针对性的优化

然后执行sql就会被记录了，

再用show profiles得到刚才语句的id;

就能知道该语句的id是27，花了6秒多，查看id为26的具体内容：

现在我们知道了这条sql花时间在拷贝到硬盘与排序，因为我们有了三次join，而这些join的同时用了title排序，导致无法索引覆盖，从而需要回行到硬盘中的数据这样就导致了一张非常大的表而无法放入到内存中，只能放到硬盘了；然后再有针对性的优化就行了这条sql；