【mysql优化】大数据量分页优化

时间:2021-10-02 02:35:17

limit 翻页原理

  limit offset,N, 当offset非常大时, 效率极低,

    原因是mysql并不是跳过offset行,然后单取N行,

    而是取offset+N行,返回放弃前offset行,返回N行.

    效率较低,当offset越大时,效率越低

 

通过show profile可以查看:

mysql> show profiles;
+----------+------------+------------------------------------+
| Query_ID | Duration   | Query                              |
+----------+------------+------------------------------------+
|        1 | 0.04951675 | select * from options limit 0,10   |
|        2 | 0.04815775 | select * from options limit 100,10 |
+----------+------------+------------------------------------+
2 rows in set, 1 warning (0.00 sec)

 

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.003660 |
| checking permissions | 0.003231 |
| Opening tables       | 0.003166 |
| init                 | 0.003373 |
| System lock          | 0.003410 |
| optimizing           | 0.003004 |
| statistics           | 0.002879 |
| preparing            | 0.003038 |
| executing            | 0.002879 |
| Sending data         | 0.003369 |
| end                  | 0.003246 |
| query end            | 0.003174 |
| closing tables       | 0.003068 |
| freeing items        | 0.003226 |
| cleaning up          | 0.004796 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.003356 |
| checking permissions | 0.003070 |
| Opening tables       | 0.002895 |
| init                 | 0.003248 |
| System lock          | 0.003413 |
| optimizing           | 0.003000 |
| statistics           | 0.003022 |
| preparing            | 0.003215 |
| executing            | 0.003338 |
| Sending data         | 0.003186 |
| end                  | 0.003080 |
| query end            | 0.003152 |
| closing tables       | 0.002865 |
| freeing items        | 0.003444 |
| cleaning up          | 0.003877 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

 

 

优化办法:

1: 从业务上去解决

  办法: 不允许翻过100页(因为一般也就前几十页的数据有用,后面数据没啥用)

    以百度为例,一般翻页到70页左右.

 

 

 

 

2:不用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去查具体条目. 这种技巧就是延迟索引.

 

 

原来的查询方法如下:

select id,name from lx_com limit 5000000,10;

 

  查询的列是id与name,由于只有主键有索引,所以是扫描主键索引的同时去磁盘上读取数据(需要读取到name的值),需要读取500W次,最后舍掉前面的500W条数据。

 

 

 

 

 

改造的查询方法:

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)

 

  首先是查询到id,由于id在索引上就有所以用到索引覆盖,可以很快的查到10个id,然后用10个id去物理磁盘上读取数据,相当于跳过前面的500W次回行读取数据,所以查询速度比较快。这种利用主键的索引先查到主键,然后利用主键去关联查询其他数据的方式称为延迟关联。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

启动完成之后用ps -ef |grep mysql 命令查看是否启动

8、 登陆mysql

#mysql -uroot -p

 

 

5

 

自学it网 高端PHP培训 http://www.zixue.it

 

提示 在自行编译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来连接.

 

6

 

自学it网 高端PHP培训 http://www.zixue.it

 

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

 

7

 

自学it网 高端PHP培训 http://www.zixue.it

 

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性能.

 

8

 

自学it网 高端PHP培训 http://www.zixue.it

 

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:混合随机读写

 

9

 

自学it网 高端PHP培训 http://www.zixue.it

 

测试顺序读

[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

10

 

自学it网 高端PHP培训 http://www.zixue.it

 

测试随机读

[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语句会尽量达成顺序读.

作业:测试随机写与顺序写的速度差异

11

 

自学it网 高端PHP培训 http://www.zixue.it

 

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

 

12

 

自学it网 高端PHP培训 http://www.zixue.it

 

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

 

 

13

 

自学it网 高端PHP培训 http://www.zixue.it

 

mysql性能调优的思路

 

0: 最好的优化-----不查询!

这不是开玩笑.

     

 

如果一台服务器出现长时间负载过高 /周期性负载过大,或偶尔卡住

如何来处理?

答:大的思路--------

是周期性的变化还是偶尔问题?

是服务器整体性能的问题, 还是某单条语句的问题?

具体到单条语句, 这条语句是在等待上花的时间,还是查询上花的时间.

唯一的办法-----监测并观察服务器的状态.

1:观察服务器状态, 一般用如下2个命令

Show status; Show processlist;

例: mysql> show status;

#mysqladmin ext

 

14

 

自学it网 高端PHP培训 http://www.zixue.it

 

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连接数,每秒请求数的周期变化.

     

 

15

 

自学it网 高端PHP培训 http://www.zixue.it

 

看上图,mysql的每秒请求数,随着缓存失效,有短时间的高峰.

 

解决办法:

1: 减少无关请求(业务逻辑层面,暂不讨论,但其实是最有效的手段)

2: 如果请求数是一定的,不可减少的. 我们要尽量让请求数平稳,不要有剧烈波动.

很多时候,不是服务器撑不住总的查询量,而是在某个时间段撑不住高峰请求.

 

该实际问题最后的解决:

     

----夜间负载低时,集中失效.

短时间内会有波峰,但夜间访问量少,因此波峰并不剧烈,当到上午10点左右人多时,缓存已经建立了一部分. 白天时,波峰也不剧烈.

 

 

或者让缓存的生命周期在一定范围内随机,也可以减缓波峰剧烈的情况

我们把实验中的生命周期由80秒,改为[40-120秒],其他实验条件不变.

得到如下曲线

     

 

可以看出,稳定运行后,请求在[1000-1500]之间波动,

而固定缓存周期是,请求在[500-1700]之间波动.

 

16

 

自学it网 高端PHP培训 http://www.zixue.it

 

实验附件:

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

 

17

 

自学it网 高端PHP培训 http://www.zixue.it

 

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

 

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

可以用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 |

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

 

18

 

自学it网 高端PHP培训 http://www.zixue.it

 

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: 经验

19

 

自学it网 高端PHP培训 http://www.zixue.it

 

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

20

 

自学it网 高端PHP培训 http://www.zixue.it

 

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)

21

 

自学it网 高端PHP培训 http://www.zixue.it

 

);

 

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,拆出来单放一张表.

 

建索引: 合理的索引可以减少内部临时表(索引优化策略里详解)

 

写语句: 不合理的语句将导致大量数据传输以及内部临时表的使用.

22

 

自学it网 高端PHP培训 http://www.zixue.it

 

表的优化与列类型选择

 

表的优化:

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;

 

典型的空间换时间

 

23

 

自学it网 高端PHP培训 http://www.zixue.it

 

列选择原则:

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列的说明

24

 

自学it网 高端PHP培训 http://www.zixue.it

 

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

 

 

25

 

自学it网 高端PHP培训 http://www.zixue.it

 

索引优化策略

 

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列的索引不发挥作用.

 

26

 

自学it网 高端PHP培训 http://www.zixue.it

 

多列索引经典题目:

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

27

 

自学it网 高端PHP培训 http://www.zixue.it

 

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

28

 

自学it网 高端PHP培训 http://www.zixue.it

 

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)复合索引

再查询.

29

 

自学it网 高端PHP培训 http://www.zixue.it

 

聚簇索引与非聚簇索引

Myisam与innodb引擎,索引文件的异同

     ===============================================================

     

30

 

自学it网 高端PHP培训 http://www.zixue.it

 

innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用

myisam中, 主索引和次索引,都指向物理行(磁盘位置).

 

注意: innodb来说,

1: 主键索引 既存储索引值,又在叶子中存储行的数据

2: 如果没有主键, 则会Unique key做主键

3: 如果没有unique,则系统生成一个内部的rowid做主键.

4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为聚簇索引

 

31

 

自学it网 高端PHP培训 http://www.zixue.it

 

聚簇索引

优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)

劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.

 

C) 聚簇索引的页分裂过程

 

     

 

 

32

 

自学it网 高端PHP培训 http://www.zixue.it

 

实验: 聚簇索引使用随机值导致页频繁分裂影响速度

过程:建立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);

33

 

自学it网 高端PHP培训 http://www.zixue.it

 

}

 

字段数

混乱程度(步长)

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

否则会造成大量的页分裂与页移动.

34

 

自学it网 高端PHP培训 http://www.zixue.it

 

高性能索引策略

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,索引的性能就可以接受.

 

 

35

 

自学it网 高端PHP培训 http://www.zixue.it

 

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)来建立索引

36

 

自学it网 高端PHP培训 http://www.zixue.it

 

有如下表(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)

 

 

 

37

 

自学it网 高端PHP培训 http://www.zixue.it

 

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)

 

38

 

自学it网 高端PHP培训 http://www.zixue.it

 

延迟关联

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查找行的过程被延后了. --- 这种技巧,称为延迟关联.

 

39

 

自学it网 高端PHP培训 http://www.zixue.it

 

索引与排序

排序可能发生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操作很频率,可以按周/月,来修复.

如果不频繁,可以更长的周期来做修复.

40

 

自学it网 高端PHP培训 http://www.zixue.it

 

sql语句优化

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

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

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

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

 

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

答:

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

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

 

3: sql语句的优化思路?

答: 不查, 通过业务逻辑来计算,

比如论坛的注册会员数,我们可以根据前3个月统计的每天注册数, 用程序来估算.

 

少查, 尽量精准数据,少取行. 我们观察新闻网站,评论内容等,一般一次性取列表 10-30条左右.

 

必须要查,尽量走在索引上查询行.

 

取时, 取尽量少的列.

比如 select * from tableA, 就取出所有列, 不建议.

比如 select * from tableA,tableB, 取出A,B表的所有列.

 

 

 

 

 

 

 

 

 

41

 

自学it网 高端PHP培训 http://www.zixue.it

 

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)

 

42

 

自学it网 高端PHP培训 http://www.zixue.it

 

select_type: 查询类型

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

table: 查询针对的表

有可能是

实际的表名 如select * from t1;

表的别名 如 select * from t2 as tmp;

derived 如from型子查询时

null 直接计算得结果,不用走表

 

 

possible_key: 可能用到的索引

注意: 系统估计可能用的几个索引,但最终,只能用1个.

 

key : 最终用的索引.

key_len: 使用的索引的最大长度

 

43

 

自学it网 高端PHP培训 http://www.zixue.it

 

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

 

2种情况可能出现:

1:索引覆盖的查询情况下, 能利用上索引,但是又必须全索引扫描.

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条件, 就得取所有索引节点,同时,又没有回行,只取索引节点.

44

 

自学it网 高端PHP培训 http://www.zixue.it

 

再排序,经过所有索引节点.

 

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)

45

 

自学it网 高端PHP培训 http://www.zixue.it

 

 

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)

 

46

 

自学it网 高端PHP培训 http://www.zixue.it

 

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)

 

47

 

自学it网 高端PHP培训 http://www.zixue.it

 

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)

 

48

 

自学it网 高端PHP培训 http://www.zixue.it

 

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(???? 这句话,用到了临时表和文件排序)

 

49

 

自学it网 高端PHP培训 http://www.zixue.it

 

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

 

50

 

自学it网 高端PHP培训 http://www.zixue.it

 

*************************** 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:

51

 

自学it网 高端PHP培训 http://www.zixue.it

 

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的返回内容要尽量少.

 

52

 

自学it网 高端PHP培训 http://www.zixue.it

 

奇技淫巧!

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 |

 

改进后的速度虽然快,但语义已经非常不清晰,不建议这么做,仅仅是实验目的.

 

53

 

自学it网 高端PHP培训 http://www.zixue.it

 

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去重的代价非常高, 放在程序里去重.

 

54

 

自学it网 高端PHP培训 http://www.zixue.it

 

limit 及翻页优化

limit offset,N, 当offset非常大时, 效率极低,

原因是mysql并不是跳过offset行,然后单取N行,

而是取offset+N行,返回放弃前offset行,返回N行.

效率较低,当offset越大时,效率越低