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.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
第 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列
列的前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
２种情况可能出现：
１：索引覆盖的查询情况下, 能利用上索引,但是又必须全索引扫描.
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越大时,效率越低