标签: mysql

mysql大数据分页查询优化

mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下。
实验
1.直接使用用limit start, count分页语句:

select * from order limit start, count

当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下:

select * from order limit 10, 20 0.016秒
select * from order limit 100, 20 0.016秒
select * from order limit 1000, 20 0.047秒
select * from order limit 10000, 20 0.094秒

我们已经看出随着起始记录的增加,时间也随着增大, 这说明分页语句limit跟起始页码是有很大关系的,那么我们把起始记录改为40w看下

select * from order limit 400000, 20 3.229秒

再看我们取最后一页记录的时间

select * from order limit 800000, 20 37.44秒

显然这种时间是无法忍受的。

从中我们也能总结出两件事情:
1)limit语句的查询时间与起始记录的位置成正比
2)mysql的limit语句是很方便,但是对记录很多的表并不适合直接使用。

2.对limit分页问题的性能优化方法

利用表的覆盖索引来加速分页查询
我们都知道,利用了索引查询的语句中如果只包含了那个索引列(覆盖索引),那么这种情况会查询很快。

因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。

在我们的例子中,我们知道id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何:

这次我们之间查询最后一页的数据(利用覆盖索引,只包含id列),如下:
select id from order limit 800000, 20 0.2秒
相对于查询了所有列的37.44秒,提升了大概100多倍的速度

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

SELECT * FROM order WHERE ID > =(select id from order limit 800000, 1) limit 20
查询时间为0.2秒,简直是一个质的飞跃啊,哈哈

另一种写法
SELECT * FROM order a JOIN (select id from order limit 800000, 20) b ON a.ID = b.id
查询时间也很短

mysql Error Code: 1175. You are using safe update

在使用mysql执行update的时候,如果不是用主键当where语句,会报如下错误,使用主键用于where语句中正常。
异常内容:Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.

这是因为MySql运行在safe-updates模式下,该模式会导致非主键条件下无法执行update或者delete命令,执行命令SET SQL_SAFE_UPDATES = 0;修改下数据库模式

如果想要提高数据库安全等级,可以在恢复回原有的设置,执行命令:SET SQL_SAFE_UPDATES = 1;

MySql按月,按周,按日分组统计数据

[java]
select DATE_FORMAT(create_time,’%Y%m’) months,count(id) count from orders group by months;
select DATE_FORMAT(create_time,’%Y%u’) weeks,count(id) count from orders group by weeks;
select DATE_FORMAT(create_time,’%Y%m%d’) days,count(id) count from orders group by days;
[/java]
这里主要使用的是DATE_FORMAT(date,format) 函数
根据format字符串格式化date值。下面的修饰符可以被用在format字符串中(注意区分大小写):
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday )
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%% 一个文字“%”。

MySQL索引原理与慢查询优化

索引目的

索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的,如果我想找到m开头的单词呢?或者w开头的单词呢?是不是觉得如果没有索引,这个事情根本无法完成?

索引原理

除了词典,生活中随处可见索引的例子,如火车站的车次表、图书的目录等。它们的原理都是一样的,通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样,但显然要复杂许多,因为不仅面临着等值查询,还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?我们回想字典的例子,能不能把数据分成段,然后分段查询呢?最简单的如果1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段……这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。但如果是1千万的记录呢,分成几段比较好?稍有算法基础的同学会想到搜索树,其平均复杂度是lgN,具有不错的查询性能。但这里我们忽略了一个关键的问题,复杂度模型是基于每次相同的操作成本来考虑的,数据库实现比较复杂,数据保存在磁盘上,而为了提高性能,每次又可以把部分数据读入内存来计算,因为我们知道访问磁盘的成本大概是访问内存的十万倍左右,所以简单的搜索树难以满足复杂的应用场景。

磁盘IO与预读

前面提到了访问磁盘,那么这里先简单介绍一下磁盘IO和预读,磁盘读取数据靠的是机械运动,每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分,寻道时间指的是磁臂移动到指定磁道所需要的时间,主流磁盘一般在5ms以下;旋转延迟就是我们经常听说的磁盘转速,比如一个磁盘7200转,表示每分钟能转7200次,也就是说1秒钟能转120次,旋转延迟就是1/120/2 = 4.17ms;传输时间指的是从磁盘读出或将数据写入磁盘的时间,一般在零点几毫秒,相对于前两个时间可以忽略不计。那么访问一次磁盘的时间,即一次磁盘IO的时间约等于5+4.17 = 9ms左右,听起来还挺不错的,但要知道一台500 -MIPS的机器每秒可以执行5亿条指令,因为指令依靠的是电的性质,换句话说执行一次IO的时间可以执行40万条指令,数据库动辄十万百万乃至千万级数据,每次9毫秒的时间,显然是个灾难。下图是计算机硬件延迟的对比图,供大家参考:

various-system-software-hardware-latencies 阅读详细 »

linux上二进制部署mysql详细步骤(测试环境常用)

一、简介:

关于在linux上二进制部署mysql,我其实现在linux已经很熟练了,那是一年前的曲折之路。不过这篇文章还是有参考意义,毕竟测试环境可以再3分钟就弄好mysql服务器.还是很happy的一件事情.之前笔记是参考别人写的,现在重新整理.主要问题是centos和ubuntu上,ubuntu上需要注意的事项等说明。

二、需求描述:

搞了一天在linux下安装mysql,实在身心疲惫,不过最终还是成功安装上了,不知道为什么mysql 的rpm包安装起来不好使,rpm主要再redhat系列的linux发行版中,老是有问题。但是编译安装需要安装编译环境和编译比较耗时间.于是决定安装tarball(二进制包)的方式安装mysql。从网页上翻了好久找到了安装方法。
我用的mysql的版本的是:mysql-5.5.42-linux2.6-x86_64.tar.gz
在官网上是可以下载到的。但是我一般再国内镜像站下载:http://mirrors.sohu.com/mysql/MySQL-5.5/
提供一个百度云的下载地址:链接: http://pan.baidu.com/s/1c0qbyzI 密码: 6vt5

三、前置条件(不是必需):

0. 按照上面下载地址.准备mysql的二进制包.
1. 建立mysql用户组,mysql用户,并且把mysql用户加入到mysql用户组中。
2. 建立mysql用户组:groupadd mysql
3. 建立mysql用户并加入mysql用户组:useradd mysql -g mysql -p 1234 -s /sbin/nologin -M
说明:-g 是加入到mysql用户组,-p是设置密码,-s是设置shell,这里设置的是不让其登录,-M就是不建立用户目录。

四、下面是安装mysql的步骤

1、把安装文件拷贝到:/usr/local,

cp mysql-5.5.42-linux2.6-x86_64.tar.gz /usr/local/

然后到目录/usr/local/下面解压缩文件:

tar zxf mysql-5.5.42-linux2.6-x86_64.tar.gz

2、创建软连接:

ln -s mysql-5.5.42-linux2.6-x86_64 /usr/local/mysql

注意:链接有两种,一种被称为硬链接(Hard Link),另一种被称为符号链接(Symbolic Link),也称为软连接。建立硬链接时,链接文件和被链接文件必须位于同一个文件系统中,并且不能建立指向目录的硬链接。而对软链接,则不存在这个问题。默认情况下,ln产生硬链接。 如果创建软链接需要参数 -s
3、到软连接mysql目录下,初始化mysql表格,安装 mysql 默认数据库:

[root@localhost mysql]#cd /usr/local/mysql
[root@localhost mysql]# scripts/mysql_install_db --user=mysql

注意:在ubuntu 执行上面的命令时会遇到如下错误:

[root@localhost mysql]#/usr/local/mysql# scripts/mysql_install_db --user=mysql
Installing MySQL system tables...
./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

主要需要缺少libaio1库文件.解决办法 使用sudo apt-get install libaio1:

[root@localhost mysql]#/usr/local/mysql# apt-get install libaio1
正在读取软件包列表... 完成

然后重新执行步骤(最保险的办法.删除了重来)

[root @localhost mysql]# scripts/mysql_install_db --user=mysql

控制台打印出:
Installing MySQL system tables…
OK
Filling help tables…
OK
…..
即说明安装成功。
4、修改目录权限,如下:

[root@localhost mysql]# cd /usr/local
[root@localhost local]# chgrp -R mysql mysql-5.5.42-linux2.6-x86_64
[root@localhost local]# chgrp -R mysql mysql
[root@localhost local]# chown -R mysql mysql-5.5.42-linux2.6-x86_64/data
[root@localhost local]# chown -R mysql mysql/data

5、启动mysql
到mysql录下:

[root@localhost mysql]# bin/mysqld_safe --user=mysql --socket=/tmp/mysql.sock --port=3306 &

这种启动方式,看不出启动效果,因为要继续输入命令查看是否启动成功的话,就会停止数据库。所以赶紧进行第6步
6、添加到系统自启动,如下:

[root@localhost local]# cp mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
shell> cp support-files/mysql.server /etc/init.d/mysqld

上面是ubutu的自启动目录

[root@localhost local]# chkconfig --add mysqld

7、启动mysql服务进程,如下:

[root@localhost local]# /etc/init.d/mysqld start

启动完成赶紧查看mysql状态,命令: /etc/rc.d/init.d/mysqld status,看看MySQL是否已经启动.
还可以查看一下 3306 端口是否已经在监听:

[root@localhost local]#netstat -anp|grep LISTEN

8、给mysql的 root 用户指定密码为”123456″,host为localhost ,如下:
在目录 /user/local/mysql/下面

[root@localhost local]#bin/mysqladmin --socket=/tmp/mysql.sock --port=3306 -u root -h localhost password '123456'

注意:确定你的mysql.sock是不是在/tmp/mysql.sock这里,
可以vi /etc/my.cnf 查看一下(如果没有my.cnf,可以从mysql/support-files/my-medium.cnf拷贝):

sorket=/var/lib/mysql/mysql.sock

这行就指定了mysql.sock的位置。
在/var/lib中新建mysql(mkdir /var/lib/mysql),将/tmp/mysql.sock 移至/var/lib/mysql中,
并修改,命令为

[root@localhost mysql]# bin/mysqladmin --socket=/var/lib/mysql/mysql.sock --port=3306 -u root -h localhost password '123456'

ok,密码修改成功!
9、登陆

[root@localhost mysql]# bin/mysql --port=3306 -u root -p -S /var/lib/mysql/mysql.sock
Enter password:
输入 123456
登陆成功!
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.51a MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

注意:如果登录失败,报如下错误的话:
Can’t connect to local MySQL server through socket’=/var/lib/mysql/mysql.sock’
可以在启动命令中加入 -h127.0.0.1,即:

[root@localhost mysql]#bin/mysql -h127.0.0.1 --port=3306 -u root -p -S /var/lib/mysql/mysql.sock

如果不想使用相对路径登录mysql的话,则可以在/usr/local/bin中建立mysql的一个软连接:
ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
注意使用绝对路径。
至此,你应该可以正常使用mysql了。快去试试吧!

mysql explain详解

一.语法

explain  select …

例如: explain select * from user where name=’ranger’;

二.explain输出解释

mysql> explain select * from user where name=’ranger’\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: const
possible_keys: uniq_name
key: uniq_name
key_len: 152
ref: const
rows: 1
Extra:
1 row in set (0.01 sec)

1.id

SELECT语句的ID编号,优先执行编号较大的查询,如果编号相同,则从上向下执行

例如:

mysql> explain select * from (select * from ( select * from share where id =11) a) b;
+—-+————-+————+——–+—————+———+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——–+—————+———+———+——+——+——-+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | share | const | PRIMARY | PRIMARY | 4 | | 1 | |
+—-+————-+————+——–+—————+———+———+——+——+——-+

很显然这条SQL是从里向外的执行,就是从id=3 向上执行.

2. select_type

就是select类型,可以有以下几种

(1) SIMPLE

简单SELECT(一条没有UNION或子查询部分的SELECT语句) 例如:

mysql> explain select * from share where id =11;
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
| 1 | SIMPLE | share | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+

(2). PRIMARY

最外层或最左侧的SELECT语句.例如:

mysql> explain select * from ( select * from share where id =11) a;
+—-+————-+————+——–+—————+———+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——–+—————+———+———+——+——+——-+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | share | const | PRIMARY | PRIMARY | 4 | | 1 | |
+—-+————-+————+——–+—————+———+———+——+——+——-+

(3).UNION

UNION语句里的第二条或最后一条SELECT语句.例如

mysql> explain select * from share where id =11 union all select * from share;
+—-+————–+————+——-+—————+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————–+————+——-+—————+———+———+——-+——+——-+
| 1 | PRIMARY | share | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | UNION | share | ALL | NULL | NULL | NULL | NULL | 2 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+—-+————–+————+——-+—————+———+———+——-+——+——-+

(4).DEPENDENT UNION

和UNION类型的含义相似,但需要依赖于某个外层查询

mysql> explain select * from t3 where id in (select id from t3 where id=3952602 union all select id from t3) ;
+—-+——————–+————+——–+——————-+———+———+——-+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+————+——–+——————-+———+———+——-+——+————————–+
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
| 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index |
| 3 | DEPENDENT UNION | t3 | eq_ref | PRIMARY,idx_t3_id | PRIMARY | 4 | func | 1 | Using where; Using index |
|NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+—-+——————–+————+——–+——————-+———+———+——-+——+————————–+

(4).UNION RESULT

一条UNION语句的结果。

mysql> explain select * from share where id =11 union all select * from share;
+—-+————–+————+——-+—————+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————–+————+——-+—————+———+———+——-+——+——-+
| 1 | PRIMARY | share | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | UNION | share | ALL | NULL | NULL | NULL | NULL | 2 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+—-+————–+————+——-+—————+———+———+——-+——+——-+

(5).SUBQUERY

子查询中的第一个SELECT.

mysql> explain select * from share where id = (select id from share where id=11 ) ;

+—-+————-+——-+——-+—————+———+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+————-+
| 1 | PRIMARY | share | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | SUBQUERY | share | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |
+—-+————-+——-+——-+—————+———+———+——-+——+————-+

(6). DEPENDENT SUBQUERY

和SUBQUERY类型的含义相似,但需要依赖于某个外层查询

mysql> explain select id from share where id in (select id from share where id=11 ) ;

+—-+——————–+——-+——-+—————+——————+———+——-+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+——-+——-+—————+——————+———+——-+——+————————–+
| 1 | PRIMARY | share | index | NULL | use_index_UNIQUE | 5 | NULL | 2 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | share | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+—-+——————–+——-+——-+—————+——————+———+——-+——+————————–+

(7).DERIVED

FROM子句里的子查询

mysql> explain select * from ( select * from share where id =11) a;
+—-+————-+————+——–+—————+———+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——–+—————+———+———+——+——+——-+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | share | const | PRIMARY | PRIMARY | 4 | | 1 | |
+—-+————-+————+——–+—————+———+———+——+——+——-+

3.table

各输出行里的信息是关于哪个数据表的
有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

mysql> explain select * from (select * from ( select * from share where id=11) a) b;

+—-+————-+————+——–+—————+———+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——–+—————+———+———+——+——+——-+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | share | const | PRIMARY | PRIMARY | 4 | | 1 | |
+—-+————-+————+——–+—————+———+———+——+——+——-+

4.type

这列很重要,显示联接操作的类型,性能由好到差依次如下
system、const、eq_reg、ref、range、indexhe和ALL

(1).system

这是const联接类型的一个特例。表示表中仅有一行.如下(share表上的id是 primary key)

mysql> explain select * from (select * from share where id=11) a ;

+—-+————-+————+——–+—————+———+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——–+—————+———+———+——+——+——-+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | share | const | PRIMARY | PRIMARY | 4 | | 1 | |
+—-+————-+————+——–+—————+———+———+——+——+——-+

(2).const

单表中最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!

const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。在下面的查询中,tbl_name可以用于const表:
SELECT * from tbl_name WHERE primary_key=1;
SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2;

例如:
mysql> explain select * from share where id=11;

+—-+————-+——-+——-+—————+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
| 1 | SIMPLE | share | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+

(3). eq_ref

联接查询中,对于前表的每一行,在此表中只查询一条记录,使用了PRIMARY或UNIQUE。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。

在下面的例子中,MySQL可以使用eq_ref联接来处理ref_tables:

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

例如
mysql> create unique index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> explain select * from share,share2 where share.id=share2.id;

+—-+————-+——–+——–+—————+———+———+—————-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——–+——–+—————+———+———+—————-+——+——-+
| 1 | SIMPLE | share2 | ALL | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | share | eq_ref | PRIMARY | PRIMARY | 4 | test.share2.id | 1 | |
+—-+————-+——–+——–+—————+———+———+—————-+——+——-+

(4).ref

对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。

ref可以用于使用=或<=>操作符的带索引的列。

在下面的例子中,MySQL可以使用ref联接来处理ref_tables:

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;

例如:

mysql> drop index idx_t3_id on t3;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> create index idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.04 sec)
Records: 1000 Duplicates: 0 Warnings: 0

mysql> explain select * from share,share2 where share.id=share2.id;
+—-+————-+——–+——–+—————+———+———+—————-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——–+——–+—————+———+———+—————-+——+——-+
| 1 | SIMPLE | share2 | ALL | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | share | eq_ref | PRIMARY | PRIMARY | 4 | test.share2.id | 1 | |
+—-+————-+——–+——–+—————+———+———+—————-+——+——

(5). ref_or_null

该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

在下面的例子中,MySQL可以使用ref_or_null联接来处理ref_tables:

SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

(6). index_merge

该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。

例如:
mysql> explain select * from share where id=11 or uniq=4 ;
+—-+————-+——-+————-+—————————-+—————————-+———+——+——+——————————————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+—————————-+—————————-+———+——+——+——————————————————+
| 1 | SIMPLE | t4 | index_merge | idx_t4_id,idx_t4_accountid | idx_t4_id,idx_t4_accountid | 4,4 | NULL | 2 | Using union(idx_t4_id,idx_t4_accountid); Using where |
+—-+————-+——-+————-+—————————-+—————————-+———+——+——+——————————————————+
1 row in set (0.00 sec)

(7). unique_subquery

该类型替换了下面形式的IN子查询的ref:

value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

(8).index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:

value IN (SELECT key_column FROM single_table WHERE some_expr)

(9).range

只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。

当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range

mysql> explain select * from t3 where id=3952602 or id=3952603 ;
+—-+————-+——-+——-+——————-+———–+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+——————-+———–+———+——+——+————-+
| 1 | SIMPLE | t3 | range | PRIMARY,idx_t3_id | idx_t3_id | 4 | NULL | 2 | Using where |
+—-+————-+——-+——-+——————-+———–+———+——+——+————-+
1 row in set (0.02 sec)

(10).index

扫描索引树(略比ALL快,因为索引文件通常比数据文件小)

当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。

(11). ALL

前表的每一行数据都要跟此表匹配,全表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。
5.possible_keys

MySQL认为在可能会用到的索引.NULL表示没有找到索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询

6. key

检索时,实际用到的索引名称.如果用了index_merge联接类型,此时会列出多个索引名称,NULL表示没有找到索引。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7.key_len

实际使用的索引的长度.如果是复合索引,那么只显示使用的最左前缀的大小
在不损失精确性的情况下,长度越短越好

8. ref

MySQL用来与索引值比较的值, 如果是单词const,则表示比较对象是一个常数.如果是某个数据列的名称,则表示比较操作是逐个数据列进行的.NULL表示没有使用索引

9. rows

MySQL为完成查询而需要在数据表里检查的行数的估算值.这个输出列里所有的值的乘积就是必须检查的数据行的各种可能组合的估算值

10. Extra

该列包含MySQL解决查询的详细信息,下面详细.

(4).Using filesort
需要将索引值写到文件中并且排序,这样按顺序检索相关数据行。看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

(5).Using index
MySQL可以不必检查数据文件, 只使用索引信息就能检索数据表信息

(6).Using temporary
在使用 GROUP BY 或 ORDER BY 时,需要创建临时表,保存中间结果集。看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上

(7).Using where
利用SELECT语句中的WHERE子句里的条件进行检索操作。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题

mysql慢查询原因分析与解决(四)——配置优化

文章列出了对mysql性能优化影响较大的主要变量,分为连接请求的变量和缓冲区变量。

连接请求的变量

1) max_connections
MySQL的最大连接数,增加该值增加mysqld 要求的文件描述符的数量。如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,介于MySQL会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。

数值过小会经常出现ERROR 1040: Too many connections错误,可以通过’conn%’通配符查看当前状态的连接数量,以定夺该值的大小。

show variables like ‘max_connections’ 最大连接数

show status like ‘max_used_connections’ 响应的连接数

如图:
connections

max_used_connections / max_connections * 100% (理想值≈ 85%)

如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了,低于10%则设置过大。 阅读详细 »

MySQL对索引的使用

什么是索引

使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构,例如 order 表的订单号(orderNum)列。如果要按订单号查找特定订单,与必须搜索表中的所有行相比,索引会帮助您更快地获得该息。
索引是一个单独的、物理的数据库结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引提供指向存储在表的指定列中的数据值的指针,然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似:它搜索索引以找到特定值,然后顺指针找到包含该值的行。
注意:
并非所有的数据库都以相同的方式使用索引。作为通用规则,只有当经常查询索引列中的数据时,才需要在表上创建索引。索引占用磁盘空间,并且降低添加、删除和更新行的速度。在多数情况下,索引用于数据检索的速度优势大大超过它的不足之处。但是,如果应用程序非常频繁地更新数据或磁盘空间有限,则可能需要限制索引的数量。
可以基于数据库表中的单列或多列创建索引。多列索引使您可以区分其中一列可能有相同值的行。
如果经常同时搜索两列或多列或按两列或多列排序时,索引也很有帮助。例如,如果经常在同一查询中为订单号和日期两列设置判据,那么在这两列上创建多列索引将很有意义。
确定索引的有效性:
检查查询的 WHERE 和 JOIN 子句。在任一子句中包括的每一列都是索引可以选择的对象。
对新索引进行试验以检查它对运行查询性能的影响。
考虑已在表上创建的索引数量。最好避免在单个表上有很多索引。
检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引。
检查某列中唯一数据值的数量,并将该数量与表中的行数进行比较。比较的结果就是该列的可选择性,这有助于确定该列是否适合建立索引,如果适合,确定索引的类型。 阅读详细 »

mysql慢查询原因分析与解决(三)——索引及查询优化

索引的类型

Ø 普通索引:这是最基本的索引类型,没唯一性之类的限制。

Ø 唯一性索引:和普通索引基本相同,但所有的索引列值保持唯一性。

Ø 主键:主键是一种唯一索引,但必须指定为”PRIMARY KEY”。

Ø 全文索引:MYSQL从3.23.23开始支持全文索引和全文检索。在MYSQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)使用B树中存储。空间列类型的索引使用R-树,MEMORY表支持hash索引。

单列索引和多列索引(复合索引)

索引可以是单列索引,也可以是多列索引。对相关的列使用索引是提高SELECT操作性能的最佳途径之一。

多列索引:

MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型,可以索引列的左前缀,列的顺序非常重要。

多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组。一般来说,即使是限制最严格的单列索引,它的限制能力也远远低于多列索引。 阅读详细 »

mysql慢查询原因分析与解决(二)——explain分析查询

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:
– 表的读取顺序
– 数据读取操作的操作类型
– 哪些索引可以使用
– 哪些索引被实际使用
– 表之间的引用
– 每张表有多少行被优化器查询

EXPLAIN字段解释

ØTable:显示这一行的数据是关于哪张表的

Øpossible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

Økey:实际使用的索引。如果为NULL,则没有使用索引。MYSQL很少会选择优化不足的索引,此时可以在SELECT语句中使用USE INDEX(index)来强制使用一个索引或者用IGNORE INDEX(index)来强制忽略索引

Økey_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好 阅读详细 »