Linux下Mysql 5.6.34tar包安装

环境:centos 6

先下载mysql安装包

打开 http://dev.mysql.com/downloads/mysql/ 

选择 linux – Generic

再选择

下载完毕后,得到安装包 mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz

此包不再需要make编译源代码(真是扛扛的),之前的mysql-5.6.4.tar.gz包是源码包,需要先cmake,再make & make install

上传到centos6.4的/software目录下(可以改为你需要的目录,这里只是演示)

ssh登录到centos6.4,开始执行以下命令

1.解压tar包


cd /software

tar -xzvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz

mv mysql-5.6.21-linux-glibc2.5-x86_64 mysql-5.6.21

2.添加用户与组


groupadd mysql

useradd -r -g mysql mysql

chown -R mysql:mysql mysql-5.6.21

3.安装数据库


su mysql

cd mysql-5.6.21/scripts

./mysql_install_db –user=mysql –basedir=/software/mysql-5.6.21 –datadir=/software/mysql-5.6.21/data

exit

4.配置文件


cd /software/mysql-5.6.21/support-files

cp my-default.cnf /etc/my.cnf

cp mysql.server /etc/init.d/mysql

vim /etc/init.d/mysql          #若mysql的安装目录是/usr/local/mysql,则可省略此步

修改文件中的两个变更值

basedir=/software/mysql-5.6.21
datadir=/software/mysql-5.6.21/data

5.配置环境变量


vim /etc/profile

export MYSQL_HOME="/software/mysql-5.6.21"

export PATH="$PATH:$MYSQL_HOME/bin"

保存退出

. /etc/profile

6.添加自启动服务


chkconfig –add mysql

chkconfig mysql on

7.启动mysql


service mysql start

8.登录mysql及改密码与配置远程访问


mysqladmin -u root password 'your_password'     #修改root用户密码

mysql -u root -p     #登录mysql,需要输入密码

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION;     #允许root用户远程访问

mysql>FLUSH PRIVILEGES;     #刷新权限

mysql>exit

mysql 安装错误记录

存放路径: /opt/mysql/xxx -> /usr/local/mysql

Datadir: /data/mysql/项目_prot/{data,logs,tmp}

配置文件:/etc/my.cnf

手动安装mysql真的遇到了很多问题

Starting MySQL....... ERROR! The server quit without updating PID file (/var/lib/mysql/localhost.loc

尤其是一些参数配置

主要有以下几个文件

/etc/my.cnf
/etc/my.cnf.d/mysql-clients.cnf
/etc/init.d/mysql

总结一下错误

主要还是配置好各个路径以及权限

mysql涉及到的目录都要chown给mysql:mysql

WYTJ9VV%PSI(GCM)$4B%7OG.png

HH]]{B38J_1N9B{FQ@706BH.png

7TST2U}[[O`OUZBTLFA}5JV.png

修改更新查找MySQL5.7.x的root用户的默认密码

最近新安装了wamp3.0.4里面附带的mysql已经升级到了5.7版本了。

MySQL5.7在性能方面有很大的提升。安装成功之后默认root的密码为空能登录。

但是正常情况下需要给root重新设置新的密码。

对于MySQL5.7版本来说和之前的5.6及以下版本的user表不一样了(user表里面没有了password这个字段了,mysql5.7 中保存密码的字段是 authentication_string)。

直接上命令行。

mysql5.7 中保存密码的字段是 authentication_string
 
//如果你用Navicat的话,先默认密码登录直接执行sql语句
update mysql.user set authentication_string=password("leokim.cn") where User="root" and Host="localhost";
 
flush privileges; 
 
 
//如果你用dos的话--进入到bin目录
cd D:\wamp\bin\mysql\mysql5.7.11\bin
 
//输入mysql 用户名和密码
mysql -u root -p
 
//新开DOS, 输入mysql,出现mysql> 命令标识符 
mysql>update mysql.user set authentication_string=password("www_dodobook_net") where user="root" and Host="localhost"; 
mysql>flush privileges; 
mysql>quit;

如果你忘了密码想找回密码的话,可以参考下面的方法

其实想要重置 5.7 的密码很简单,就一层窗户纸:
1、修改 /etc/my.cnf,在 [mysqld] 小节下添加一行:skip-grant-tables=1
这一行配置让 mysqld 启动时不对密码进行验证
2、重启 mysqld 服务:systemctl restart mysqld
3、使用 root 用户登录到 mysql:mysql -u root 
4、切换到mysql数据库,更新 user 表:
update user set authentication_string = password('root'), password_expired = 'N', password_last_changed = now() where user = 'root';
在之前的版本中,密码字段的字段名是 password,5.7版本改为了 authentication_string
5、退出 mysql,编辑 /etc/my.cnf 文件,删除 skip-grant-tables=1 的内容
6、重启 mysqld 服务,再用新密码登录即可.

另外,MySQL 5.7 在初始安装后(CentOS7 操作系统)会生成随机初始密码.

并在 /var/log/mysqld.log 中有记录,可以通过 cat 命令查看,找 password 关键字.

找到密码后,在本机以初始密码登录,并且(也只能)通过命令

alter user 'root'@'localhost' identified by 'root'

修改 root 用户的密码为 root,然后退出,重新以root用户和刚设置的密码进行登录即可。

 

也可以在安装的时候不生成随机密码,用 sudo mysqld –initialize-insecure

然后在自己设置密码 sudo /usr/bin/mysqladmin -uroot password 密码

如果你是之前的版本MySQL5.5 MySQL5.6的话请直接使用以下语句和方法

//先输入密码。默认为空就直接按Enter即可
mysql> use mysql
Database changed
mysql> update user set password=PASSWORD('leokim') where user='root';
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0
  
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
  
mysql> quit

mysql探究之null与not null

相信很多用了MySQL很久的人,对这两个字段属性的概念还不是很清楚,一般会有以下疑问:

1、我字段类型是not null,为什么我可以插入空值

2、为毛not null的效率比null高

3、判断字段不为空的时候,到底要  select * from table where column <> '' 还是要用 select * from table where column is not null 呢。

带着上面几个疑问,我们来深入研究一下null 和 not null 到底有什么不一样。

首先,我们要搞清楚“空值” 和 “NULL” 的概念:

1、空值是不占用空间的

2、mysql中的NULL其实是占用空间的,下面是来自于MYSQL官方的解释

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

打个比方来说,你有一个杯子,空值代表杯子是真空的,NULL代表杯子中装满了空气,虽然杯子看起来都是空的,但是区别是很大的。

搞清楚“空值”和“NULL”的概念之后,问题基本就明了了,我们搞个例子测试一下:

CREATE TABLE  `test` (  
 `col1` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,  
 `col2` VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL  
) ENGINE = MYISAM ;

插入数据:

INSERT INTO `test` VALUES (null,1);

mysql发生错误:Column 'col1' cannot be null   

再来一条

INSERT INTO `test` VALUES ('',1);

成功插入。

可见,NOT NULL 的字段是不能插入“NULL”的,只能插入“空值”,上面的问题1也就有答案了。

对于问题2,上面我们已经说过了,NULL 其实并不是空值,而是要占用空间,所以mysql在进行比较的时候,NULL 会参与字段比较,所以对效率有一部分影响。

而且B树索引时不会存储NULL值的,所以如果索引的字段可以为NULL,索引的效率会下降很多。

我们再向test的表中插入几条数据:

INSERT INTO `test` VALUES ('', NULL);  
INSERT INTO `test` VALUES ('1', '2');

现在表中数据:

现在根据需求,我要统计test表中col1不为空的所有数据,我是该用“<> ''” 还是 “IS NOT NULL” 呢,让我们来看一下结果的区别。

SELECT * FROM `test` WHERE col1 IS NOT NULL

SELECT * FROM `test` WHERE col1 <> ''


可以看到,结果迥然不同,所以我们一定要根据业务需求,搞清楚到底是要用那种搜索条件。

提高mysql千万级大数据SQL查询优化30条经验(Mysql索引优化注意)

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num=10 or num=20

可以这样查询:select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用(in其实能用到索引呀),否则会导致全表扫描,如:select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了:select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描(应该可以用到索引的前缀索引,具体要看索引引擎吧?):select id from t where name like '李%'若要提高效率,可以考虑全文检索。

7. 如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:select id from t where num=@num可以改为强制查询使用索引:select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:select id from t where num/2=100应改为:select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:select id from t where substring(name,1,3)='abc' ,name以abc开头的id

应改为:select id from t where name like 'abc%'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询,如需要生成一个空表结构:select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:create table #t(…)

13.很多时候用 exists 代替 in 是一个好的选择:select num from a where num in(select num from b)

用下面的语句替换:select num from a where exists(select 1 from b where num=a.num

14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

15. 索引并不是越多越好,索引固然可 以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16. 应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

21.避免频繁创建和删除临时表,以减少系统表资源的消耗。

22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

27. 与临时表一样,游标并不是不可使 用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

29.尽量避免大事务操作,提高系统并发能力。

30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

Mysql – 性能优化之子查询

记得在做项目的时候, 听到过一句话, 尽量不要使用子查询, 那么这一篇就来看一下, 这句话是否是正确的.

那在这之前, 需要介绍一些概念性东西和mysql对语句的大致处理.

当Mysql Server的连接线程接收到Client发送过来的SQL请求后, 会经过一系列的分解Parse, 进行相应的分析, 然后Mysql会通过查询优化器模块, 根据该Sql所涉及到的数据表的相关统计信息进行计算分析. 然后在得出一个Mysql自认为最合理最优化的数据访问方式, 也就是我们常说的"执行计划", 然后根据所得到的执行计划通过调用存储引擎接口来获取相应数据. 再对存储引擎返回的数据进行相关的处理, 并一Client端所要求的格式作为结果集, 返回给Client.

注 : 这里所说的统计数据, 是我们通过 Analyze table命令通知Mysql对表的相关数据作分析之后, 所获取到的一些数据统计量. 这些数据对Mysql优化器而言是非常重要的, 优化器所生成的执行计划的好坏, 主要是由这些统计数据所决定的.

1. 建表

create table User(
  Id int not null PRIMARY key auto_increment ,
  NickName varchar(50) comment '用户昵称',
  Sex int comment '性别',
  Sign varchar(50) comment '用户签名',
  Birthday datetime comment '用户生日',
  CreateTime datetime comment '创建时间'
) default charset=utf8 comment '用户表';
 
create table UserGroup(
  Id int not null PRIMARY key auto_increment ,
  UserId int not null comment 'user Id',
  GroupId int not null comment '用户组Id',
  CreateTime datetime comment '创建时间',
  -- key index_groupid(GroupId) using btree,
  key index_userid(groupid, UserId) using btree
) default charset=utf8 comment '用户组表';

2. 准备数据

var conStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
using (IDbConnection conn = new MySqlConnection(conStr))
{ Stopwatch watch = new Stopwatch();
 var sql = string.Empty;
 var names = new string[] { "非", "想", "红", "帝", "德", "看", "梅", "插", "兔" };
 Random ran = new Random(); 
 var insertSql = @" insert into User(NickName,Sex,Sign, Birthday, CreateTime) values(@NickName,@Sex,@Sign, @Birthday, @CreateTime); 
 INSERT INTO usergroup (UserId, GroupId, CreateTime ) VALUES (LAST_INSERT_ID() , @GroupId, @CreateTime);";
 watch.Start();
 if (conn.State == ConnectionState.Closed)
 {
  conn.Open();
 }
 var tran = conn.BeginTransaction();
 for (int i = 0; i < 100000; i++)
 {
  var param = new { NickName = names[ran.Next(9)] + names[ran.Next(9)] + i, Sign = names[ran.Next(9)] + names[ran.Next(9)], CreateTime = DateTime.Now, Birthday = DateTime.Now.AddYears(ran.Next(10, 30)), Sex = i % 2, GroupId = ran.Next(1, 100) };
  conn.Execute(insertSql, param, tran);
 }
 tran.Commit();
 conn.Dispose();
 watch.Stop();
 Console.WriteLine(watch.ElapsedMilliseconds);
}

这里我插入了5000条数据, group分了99个组, 随机的.

3. 查询sql

explain
select user.id, user.nickname from usergroup 
left join user on usergroup.UserId = user.Id
where usergroup.groupid = 1 
order by usergroup.UserId desc
limit 100, 20;


explain
select user.id, user.nickname
from (select id, userid from usergroup where groupid = 1 order by userid limit 100, 20) t
left join user on t.UserId = user.id ;


explain
select user.id, user.nickname
from (select id, userid from usergroup where groupid = 1 order by userid ) t
left join user on t.UserId = user.id 
limit 100, 20;

第二句和第三句都使用到了子查询, 不同之处再与, 第二句是先得到20条数据, 然后以此来与user表关联的

4. 分析

100000条数据情况下 :

先看第一句

再看第二句

第三句

从上面三幅图看, 好像能看出点什么了.

首先看他们的 rows, 第二句最多, 加起来有1000多了, 另两句加起来都是996. 但是我想说的是, 这里并不是看rows的和是多少. 正确的方式是, 从id大的语句开始看, id相同的语句, 从上到下依次执行.

那先看第二句的id=2的语句和第一句的id=1的语句, 一模一样的. 他们都是从usergroup表中筛选数据, 并且能得到相同的结果集A.

看来他们都是基于相同的结果集去进行操作, 接下来就有区别了.

先看第一句, 再结果集A的基础上, 去左连接表user, 并筛选出最后的数据, 返回给客户端.

那第二句呢, 是在A的基础上, 再次筛选数据, 得到需要的数据, 然后拿这些数据, 去与user表左连接, 得到最终结果.

从上面来看, 执行计划中, 第二种执行计划, 更加高效.

 如果能够通过子查询, 大幅度缩小查询范围, 可以考虑使用子查询语句.

mysql性能优化之索引优化

  作为免费又高效的数据库,mysql基本是首选。良好的安全连接,自带查询解析、sql语句优化,使用读写锁(细化到行)、事物隔离和多版本并发控制提高并发,完备的事务日志记录,强大的存储引擎提供高效查询(表记录可达百万级),如果是InnoDB,还可在崩溃后进行完整的恢复,优点非常多。即使有这么多优点,仍依赖人去做点优化,看书后写个总结巩固下,有错请指正。

  完整的mysql优化需要很深的功底,大公司甚至有专门写mysql内核的,sql优化攻城狮,mysql服务器的优化,各种参数常量设定,查询语句优化,主从复制,软硬件升级,容灾备份,sql编程,需要的不是一星半点的知识与时间来掌握,作为一名像俺这样的菜鸟开发,强吃这么多消化不了也没意义:没地儿用啊,况且还有运维和dba,还不如把手头的业务写好,也就是写好点的sql,而且很多sql语句优化跟索引还是有很大关系的。

  首先,mysql的查询流程大致是:mysql客户端通过协议与mysql服务器建立连接,发送查询语句,先检查查询缓存,如果命中,直接返回结果,否则进行语句解析,有一系列预处理,比如检查语句是否写正确了,然后是查询优化(比如是否使用索引扫描,如果是一个不可能的条件,则提前终止),生成查询计划,然后查询引擎启动,开始执行查询,从底层存储引擎调用API获取数据,最后返回给客户端。怎么存数据、怎么取数据,都与存储引擎有关。然后,mysql默认使用的BTREE索引,并且一个大方向是,无论怎么折腾sql,至少在目前来说,mysql最多只用到表中的一个索引。

  mysql通过存储引擎取数据,自然跟存储引擎有很大关系,不同的存储引擎索引也不一样,如MyISAM的全文索引,即便索引叫一个名字内部组织方式也不尽相同,最常用的当然就是InnoDB了(还有完全兼容mysql的MariaDB,它的默引擎是XtraDB,跟InnoDB很像),这里写的是InnoDB引擎。而索引的实现也跟存储引擎,按照实现方式分,InnoDB的索引目前只有两种:BTREE索引和HASH索引。通常我们说的索引不出意外指的就是B树索引,InnoDB的BTREE索引,实际是用B+树实现的,因为在查看表索引时,mysql一律打印BTREE,所以简称为B树索引。至于B树与B+树的区别,原谅的俺数据结构没好好学,也是需要补的地方。

  使用了BTREE索引,意味着所有的索引是按顺序排列存储的(升序),mysql就是这么干的,mysl中的BTREE索引抽象结构如下图(参考高性能mysql)。

  结构中,每一层节点均从左往右从小到大排列,key1 < key2 < … < keyN,对于小于key1或在[key1,key2)或其他的值的节点,在进入叶子节点查找,是一个范围分布,同时,同一层节点之间可直接访问,因为他们之间有指针指向联系(MyISAM的BTREE索引没有)。每次搜索是一个区间搜索,有的话就找到了,没有的话就是空。索引能加快访问速度,因为有了它无需全表扫描数据(不总是这样),根据查找的值,跟节点中的值比较,通常使用二分查找,对于排好序的数值来说,平均速度几乎是最快的。

  val指向了哪里,对于InnoDB,它指向的就是表数据,因为InnoDB的表数据本身就是索引文件,这是与MyISAM索引的显著区别,MyISAM的索引指向的是表数据的地址(val指向的是类似于0x7DFF..之类)。比如对于InnoDB一个主键索引来说,可能是这样

        

  InnoDB的索引节点val值直接指向表数据,即它的叶子节点就是表数据,它们连在一起,表记录行没有再单独放在其他地方,叶子节点(数据)之间可访问。

  前面在BTREE的抽象结构中,索引值的节点是放在页中的,这里有两个需注意的问题:

  1. 叶子页、页中的值(上上图),即所谓的页是啥,俺加了个节点注释,即这里的页最小可近似当做是单个节点。我们知道计算机的存储空间是一块一块的,通常一块用完了再用另一块,如果上一块只剩余5kb,但这里刚好要申请8kb的空间,就得在一个新的块上申请这个空间,然后以后的申请又接在这个8kb后面,只要这个块的空间足够,那么上一块的5kb通常就成了所谓的“碎片”,电脑用多了会有很多这样零散的碎片空间,因此有碎片整理。在mysql中,这里的页可理解为块存储空间,即索引的树节点是存放在页中的,每一页(称为逻辑页)有固定大小,InnoDB目前是16kb,一页用完了,当继续插入表生成新的索引节点时,就去新的页中存储这个节点,再有新的节点就继续放在这个新的页的节点后面。

  2. 页分裂问题,一页总要被存满,然后新开一页继续,这种行为被称作页分裂。何时开辟新的页,mysql规定了一个分裂因子,达到页存储空间的15/16则存到下一页。页分裂的存在可能极大影响性能维护索引的性能。通常提倡的是,设定一个无意义的整数自增索引,有利于索引存储

    

  如果非自增或不是整数索引,如非自增整数、类似MD5的字符串,以他们作为索引值时,因为待插入的下一条数据的值不一定比上一条大,甚至比当前页所有值都小,需要跑到前几页去比较而找到合适位置,InnoDB无法简单的把新行插入到上一行后面,而找到并插入索引后,可能导致该页达到分裂因子阀值,需要页分裂,进一步导致后面所有的索引页的分裂和排序,数据量小也许没什么问题,数据量大的话可能会浪费大量时间,产生许多碎片。

    

  主键总是唯一且非空,InnoDB自动对它建立了索引(primary key),对于非主键字段上建立的索引,又称辅助索引,索引排列也是顺序排列,只是它还附带一个本条记录的主键值的数据域,不是指向本数据行的指针,在使用辅助索引查找时,先找到对应这一列的索引值,再根据索引节点上的另一个数据域—主键值,来查找该行记录,即每次查找实际经过查找了两次。额外的数据域存储主键值的好处是,当页分裂发生时,无需修改数据域的值,因为即使页分裂,该行的主键值是不变的,而地址就变了。比如name字段的索引简示如下 

      

   包含一列的索引称为单列索引,多列的称为复合索引,因为BTREE索引是顺序排列的,所以比较适合范围查询,但是在复合索引中,还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。

  比如有这样一张表

create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default '' comment '姓名',
    age int not null default 0 comment '年龄',
    pos varchar(20) not null default '' comment '职位',
    add_time timestamp not null default current_timestamp comment '入职时间'
  ) charset utf8 comment '员工记录表';

  添加三列的复合索引

alter table staffs add index idx_nap(name, age, pos);

  在BTREE索引的使用上,以下几种情况可以用到该索引或索引的一部分(使用explain简单查看使用情况):

  1. 全值匹配

  如select * from staffs where name = 'July' and age = '23' and pos = 'dev' ,key字段显示使用了idx_nap索引

  2. 匹配最左列,对于复合索引来说,不总是匹配所有字段列,但是可以匹配索引中靠左的列

  如select * from staffs where name = 'July' and age = '23',key字段显示用到了索引,注意,key_len字段(表示本次语句使用的索引长度)数值比上一条小了,意思是它并未使用全部索引列(通常这个长度可估摸着用了哪些索引列,埋个坑),事实上只用到了name和age列

  再试试select * from staffs where name = 'July',它也用了索引,key_len值更小,实际只用到了索引中的name列

  3. 匹配列前缀,即一个索引中列的前一部分,主要用在模糊匹配,如select * fromstaffs where name like 'J%',explain信息的key字段表示使用了索引,但是mysql的B树索引不能非列前缀的模糊匹配,如select * from staffs where name like '%y' 或者 like '%u%',据说是由于底层存储引擎的API限制

  4. 匹配范围,如select * from staffs where name > 'Mary',但俺在测试时发现>可以,>=却不行,至少在字符串列上不行(测试mysql版本5.5.12),然而在时间类型(timestamp)上却可以,不测试下还真不能确定说就用到了索引==

  出于好奇测了下整型字段的索引(idx_cn(count, name),count为整型),发现整型受限制少很多,下面的都能用到索引,连前模糊匹配的都行

select * from indexTest1 where count > '10'
  select * from indexTest1 where count >= '10'
  select * from indexTest1 where count > '10%'
  select * from indexTest1 where count >= '10%'
  select * from indexTest1 where count > '%10%'
  select * from indexTest1 where count >= '%10%'

  5. 精确匹配一列并范围匹配右侧相邻列,即前一列是固定值,后一列是范围值,它用了name与age两个列的索引(key_len推测)

  如select * from staffs where name = 'July' and age > 25

  6. 只访问索引的查询,比如staffs表的情况,索引建立在(name,age,pos)上面,前面一直是读取的全部列,如果我们用到了哪些列的索引,查询时也只查这些列的数据,就是只访问索引的查询,如

select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev'  
select name,age from staffs where name = July and age > 25

  第一句用到了全部索引列,第二句只用了索引前两列,select的字段就最多只能是这两列,这种查询情况的索引,mysql称为覆盖索引,就是索引包含(覆盖)了查询的全部字段。是不是用到了索引查询,在explain中需要看最后一个Extra列的信息,Using index表明使用了覆盖索引,同时Using where表明也使用了where过滤

  7. 前缀索引

  区别于列前缀(类似like 'J%'形式的模糊匹配)和最左列索引(顺序取索引中靠左的列的查询),它只取某列的一部分作为索引。通常在说InnoDB跟MyISAM的区别时,一个明显的区别是:MyISAM支持全文索引,而InnoDB不行,甚至对于text、blob这种超长的字符串或二进制数据时,MyISAM会取前多少个字符作为索引,InnoDb的前缀索引跟这个类似,某些列,一般是字符串类型,很长,全部作为索引大大增加存储空间,索引也需要维护,对于长字符串,又想作为索引列,一个可取的办法就是取前一部分(前缀),代表一整列作为索引串,问题是:如何确保这个前缀能代表或大致代表这一列?所以mysql中有个概念是索引的选择性,是指索引中不重复的值的数目(也称基数)与整个表该列记录总数(#T)的比值,比如一个列表(1,2,2,3),总数是4,不重复值数目为3,选择性为3/4,因此选择性范围是[1/#T, 1],这个值越大,表示列中不重复值越多,越适合作为前缀索引,唯一索引(UNIQUE KEY)的选择性是1。

  比如有一列a varchar(255),以它作前缀索引,比如以7个测试,逐个增加看看选择性值增长到那个数基本不变,就表示可以代表整列了,再结合这个长度的索引列是否存储数据太多,做个权衡,基本就行了。但如果这个选择性本来就小的可怜还是算了

select count(distinct left(a, 7))/count(*) as non_repeat from tab;

  定好一个前缀数目,如9,添加索引时可以这样

alter table tab add index idx_pn(name(9)) --单独前缀索引  
alter table tab add index idx_cpn(count, name(9)) --复合前缀索引

  以上为常见的使用索引的方式,有这么些情况不能用或不能全用,有的就是上面情况的反例,以key(a, b, c)为例

  1. 跳过列,where a = 1 and c = 3,最多用到索引列a;where b = 2 and c = 3,一个也用不到,必须从最左列开始

  2. 前面是范围查询,where a = 1 and b > 2 and c = 3,最多用到 a, b两个索引列;

  3. 顺序颠倒,where c = 3 and b = 2 and a = 1,一个也用不到;

  4. 索引列上使用了表达式,如where substr(a, 1, 3) = 'hhh',where a = a + 1,表达式是一大忌讳,再简单mysql也不认。有时数据量不是大到严重影响速度时,一般可以先查出来,比如先查所有有订单记录的数据,再在程序中去筛选以'cp1001'开头的订单,而不是写sql过滤它;

  5. 模糊匹配时,尽量写 where a like 'J%',字符串放在左边,这样才可能用得到a列索引,甚至可能还用不到,当然这得看数据类型,最好测试一下。

  排序对索引的影响

  order by是经常用的语句,排序也遵循最左前缀列的原则,比如key(a, b),下面语句可以用到(测试为妙)

select * from tab where a > 1 order by b
select * from tab where a > 1 and b > '2015-12-01 00:00:00' order by b
select * from tab order by a, b

  以下情况用不到

  1. 非最左列,select * from tab order by b;

  2. 不按索引列顺序来的,select * from tab where b > '2015-12-01 00:00:00' order by a;

  3. 多列排序,但列的顺序方向不一致,select * from tab a asc, b desc。

  聚簇索引与覆盖索引

  前面说到,mysql索引从结构上只有两类,BTREE与HASH,覆盖索引只是在查询时,要查询的列刚好与使用的索引列完全一致,mysql直接扫描索引,然后就可返回数据,大大提高效率,因为不需再去原表查询、过滤,这种形式下的索引称作覆盖索引,比如key(a,b),查询时select a,b from tab where a = 1 and b > 2,本质原因:BTREE索引存储了原表数据。

  聚簇索引也不是单独的索引,前面简要写到,BTREE索引会把数据放在索引中,即索引的叶子页中,包括主键,主键是跟表数据紧挨着放在一起的,因为表数据只有一份,一列键值要跟每一行数据都紧挨在一起,所以一张表只有一个聚簇索引,对于mysql来说,就是主键列,它是默认的。

  聚簇索引将表数据组织到了一起(参考前面主键索引简略图),插入时严重依赖主键顺序,最好是连续自增,否则面临频繁页分裂问题,移动许多数据。

  哈希索引

  简要说下,类似于数据结构中简单实现的HASH表(散列表)一样,当我们在mysql中用哈希索引时,也是对索引列计算一个散列值(类似md5、sha1、crc32),然后对这个散列值以顺序(默认升序)排列,同时记录该散列值对应数据表中某行的指针,当然这只是简略模拟图

      

  比如对姓名列建立hash索引,生成hash值按顺序排列,但是顺序排列的hash值并不对应表中记录,从地址指针可反应出来,而且,hash索引可能建立在两列或者更多列上,取得是多列数据后的hash值,它不存储表中数据。它先计算列数据的hash值,与索引中的hash值比较,找到了然后比对列数据是否相等,可能涉及其他列条件,然后返回数据。hash当然会有冲突,即碰撞,除非有很多冲突,一般hash索引效率很高,否则hash维护成本较高,因此哈希索引通常用在选择性较高的列上面。哈希索引的结构决定了它的特点:

  1. hash索引只是hash值顺序排列,跟表数据没有关系,无法应用于order by;

  2. hash索引是对它的所有列计算哈希值,因此在查询时,必须带上所有列,比如有(a, b)哈希索引,查询时必须 where a = 1 and b = 2,少任何一个不行;

  3. hash索引只能用于比较查询 = 或 IN,其他范围查询无效,本质还是因不存储表数据;

  4. 一旦出现碰撞,hash索引必须遍历所有的hash值,将地址所指向数据一一比较,直到找到所有符合条件的行。

  填坑

  前面提到通过explain的key_len字段,可大致估计出用了哪些列,索引列的长度跟索引列的数据类型直接相关,一般,我们说int是4字节,bigint8字节,char是1字节,考虑到建表时要指定字符集,比如utf8,还跟选的字符集有关(==!),在utf8下边,一个char是3字节,但是知道这些仍不能说key_len就是将用到的索引列的数据类型代表字节数一加不就完啦?事实总有点区别,测试方法比较机械(以下基于mysql 5.5.2)

  建表,加索引,int型

--测试表
  create table keyLenTest1(
    id int primary key auto_increment,
    typeKey int default 0 ,
    add_time timestamp not null default current_timestamp
  ) charset utf8
  --添加索引
  alter table keyLenTest1 add index idx_k(typeKey);

  可知int型索引默认长度为5,在4字节基础上+1

  char型

--改为char型,1个字符
 alter table keyLenTest1 modify typeKey char(1);

--改为char型,2个字符
  alter table keyLenTest1 modify typeKey char(2);

  可知,char型初始是4字节(3+1 bytes),后续按照3字节递增

  varchar型

--改为varchar型,1个字符
  alter table keyLenTest1 modify typeKey varchar(1);

--改为varchar型,2个字符
  alter table keyLenTest1 modify typeKey varchar(2);

  可知,varchar型,1个字符时,key_len为6,以后以3字节递增

  所以,如果一个语句用到了int、char、varchar,key_len如何计算以及用了哪些索引列应该很清楚了。

  如果想了解的更详细点,explain各字段意义,索引的更多细节,除了explain,还有show profiles、慢查询日志等(没细看)。

MYSQL JOIN 的性能优化

单独从之前的文章里拿出来,方便以后查找。

 

1.显示(explicit) inner join VS 隐式(implicit) inner join

如:

select * fromtable a inner join table bon a.id = b.id;

VS

select a.*, b.*from table a, table bwhere a.id = b.id;

我在数据库中比较(10w数据)得之,它们用时几乎相同,第一个是显示的inner join,后一个是隐式的inner join。

参照:Explicit vs implicit SQL joins

2.left join/right join VS inner join

尽量用inner join.避免 LEFT JOIN 和 NULL.

在使用left join(或right join)时,应该清楚的知道以下几点:

(1). on与 where的执行顺序

ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

所以我们要注意:在使用Left (right) join的时候,一定要在先给出尽可能多的匹配满足条件,减少Where的执行。如:

PASS

select * from Ainner join B on B.name = A.nameleft join C on C.name = B.nameleft join D on D.id = C.idwhere C.status>1 and D.status=1;

Great

select * from Ainner join B on B.name = A.nameleft join C on C.name = B.name and C.status>1left join D on D.id = C.id and D.status=1

从上面例子可以看出,尽可能满足ON的条件,而少用Where的条件。从执行性能来看第二个显然更加省时。

(2).注意ON 子句和 WHERE 子句的不同

如作者举了一个列子:

mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       AND product_details.id=2;
+----+--------+------+--------+-------+
| id | amount | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  2 |    200 |    2 |     22 |     0 |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+4 rows in set (0.00 sec)
 
mysql> SELECT * FROM product LEFT JOIN product_details
       ON (product.id = product_details.id)
       WHERE product_details.id=2;
+----+--------+----+--------+-------+
| id | amount | id | weight | exist |
+----+--------+----+--------+-------+
|  2 |    200 |  2 |     22 |     0 |
+----+--------+----+--------+-------+1 row in set (0.01 sec)

从上可知,第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。第二条查询做了简单的LEFT JOIN,然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

(3).尽量避免子查询,而用join

往往性能这玩意儿,更多时候体现在数据量比较大的时候,此时,我们应该避免复杂的子查询。如下:

PASS

insert into t1(a1) select b1 from t2 where not exists(select 1 from t1 where t1.id = t2.r_id);

Great

insert into t1(a1)  
select b1 from t2  
left join (select distinct t1.id from t1 ) t1 on t1.id = t2.r_id   
where t1.id is null;

这个可以参考mysql的exists与inner join 和 not exists与 left join 性能差别惊人

补充:MySQL STRAIGHT_JOIN 与 NATURAL JOIN的使用

长话短说:straight_join实现强制多表的载入顺序,从左到右,如:

...A straight_join B on A.name = B.name

straight_join完全等同于inner join 只不过,join语法是根据“哪个表的结果集小,就以哪个表为驱动表”来决定谁先载入的,而straight_join 会强制选择其左边的表先载入。

往往我们在分析mysql处理性能时,如(Explain),如果发现mysql在载入顺序不合理的情况下,可以使用这个语句,但往往mysql能够自动的分析并处理好。

MySQL延迟关联性能优化方法

 代码如下:

SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20;

表的数据量大致有36w左右,该sql是一个非常典型的排序+分页查询:order by col limit N,OFFSET M , MySQL 执行此类sql时需要先扫描到N行,然后再去取 M行。对于此类大数据量的排序操作,取前面少数几行数据会很快,但是越靠后,sql的性能就会越差,因为N越大,MySQL 需要扫描不需要的数据然后在丢掉,这样耗费大量的时间。
【分析】针对limit 优化有很多种方式,
1 前端加缓存,减少落到库的查询操作
2 优化SQL
3 使用书签方式 ,记录上次查询最新/大的id值,向后追溯 M行记录。
4 使用Sphinx 搜索优化。
对于第二种方式 我们推荐使用"延迟关联"的方法来优化排序操作,何谓"延迟关联" :通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。【解决】根据延迟关联的思路,修改SQL 如下:优化前
复制代码代码如下:

root@xxx 12:33:48>explain SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type =\'0\' AND end_time >=\'2014-05-29\' ORDER BY id asc LIMIT 149420 ,20;

+—-+————-+————-+——-+—————+————-+———+——+——–+—————————–+
| id | select_type | table       | type  | possible_keys | key         | key_len | ref  | rows   | Extra                       |
+—-+————-+————-+——-+—————+————-+———+——+——–+—————————–+
| 1  | SIMPLE      | relation    | range | ind_endtime   | ind_endtime | 9       | NULL | 349622 | Using where; Using filesort |
+—-+————-+————-+——-+—————+————-+———+——+——–+—————————–+
1 row in set (0.00 sec)

其执行时间:优化后:
复制代码代码如下:

SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id

复制代码代码如下:

root@xxx 12:33:43>explain SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;

+—-+————-+————-+——–+—————+———+———+——+——–+——-+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref  | rows   | Extra |
+—-+————-+————-+——–+—————+———+———+——+——–+——-+
| 1  | PRIMARY     | <derived2>  | ALL    | NULL          | NULL    | NULL    | NULL | 20     |       |
| 1  | PRIMARY     | a           | eq_ref | PRIMARY       | PRIMARY | 8       | b.id | 1      |       |
| 2  | DERIVED     | relation    | index  | ind_endtime   | PRIMARY | 8       | NULL | 733552 |       |
+—-+————-+————-+——–+—————+———+———+——+——–+——-+
3 rows in set (0.36 sec)

MySql中常用的hint

对于经常使用Oracle的朋友可能知道,oracle的hint功能种类很多,对于优化sql语句提供了很多方法。同样,在MySQL里,也有类似的hint功能。下面介绍一些常用的。
强制索引 FORCE INDEX 
SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …
以上的SQL语句只使用建立在FIELD1上的索引,而不使用其它字段上的索引。
忽略索引 IGNORE INDEX 
SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …
在上面的SQL语句中,TABLE1表中FIELD1和FIELD2上的索引不被使用。 
关闭查询缓冲 SQL_NO_CACHE 
SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;
有一些SQL语句需要实时地查询数据,或者并不经常使用(可能一天就执行一两次),这样就需要把缓冲关了,不管这条SQL语句是否被执行过,服务器都不会在缓冲区中查找,每次都会执行它。
强制查询缓冲 SQL_CACHE
SELECT SQL_CALHE * FROM TABLE1;
如果在my.ini中的query_cache_type设成2,这样只有在使用了SQL_CACHE后,才使用查询缓冲。

优先操作 HIGH_PRIORITY
HIGH_PRIORITY可以使用在select和insert操作中,让MYSQL知道,这个操作优先进行。
SELECT HIGH_PRIORITY * FROM TABLE1;
滞后操作 LOW_PRIORITY
LOW_PRIORITY可以使用在insert和update操作中,让mysql知道,这个操作滞后。
update LOW_PRIORITY table1 set field1= where field1= …
延时插入 INSERT DELAYED
INSERT DELAYED INTO table1 set field1= …
INSERT DELAYED INTO,是客户端提交数据给MySQL,MySQL返回OK状态给客户端。而这是并不是已经将数据插入表,而是存储在内存里面等待排队。当mysql有空余时,再插入。另一个重要的好处是,来自许多客户端的插入被集中在一起,并被编写入一个块。这比执行许多独立的插入要快很多。坏处是,不能返回自动递增的ID,以及系统崩溃时,MySQL还没有来得及插入数据的话,这些数据将会丢失。

强制连接顺序 STRAIGHT_JOIN
SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
由上面的SQL语句可知,通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话,就可以通过STRAIGHT_JOIN来确定连接顺序。
强制使用临时表 SQL_BUFFER_RESULT
SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE … 
当我们查询的结果集中的数据比较多时,可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中,这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了),并且可以长时间地为客户端提供大记录集。