Mysql高级

0.Linux服务器安装Mysql

1.在这个mysql链接下载mysql客户端和mysql服务端，我下载的是5.5.60，然后用xftp上传到服务器，我是放到新建的app文件夹下。

2.解压安装
在xshell中连接好服务器之后，用以下两个命令，先安装服务端，再安装客户端。

rpm -ivh MySQL-server-5.5.60-1.el6.x86_64.rpm

rpm -ivh MySQL-client-5.5.60-1.el6.x86_64.rpm

3.测试是否安装成功

命令行输入：

mysqladmin --version

出现：

表明安装成功！

4.启动

service mysql start//启动
service mysql stop//停止
service mysql restart//重启

reboot重启后登录Mysql：mysql

有可能登录不上，这里有两种情况，安装过程中给了随机密码(安装过程有提示)，还有就是没有密码，需要重新设置。这里以5.7以下为例：

第一步：跳过密码验证

#vim /etc/my.cnf

在[mysqld]后面任意一行添加skip-grant-tables用来跳过密码验证。

第二步：重启Mysql

从以下两个选一个

/etc/init.d/mysql restart
/etc/init.d/mysqld restart)

第三步：输入mysql进入Mysql重设密码

mysql> use mysql;
mysql> update mysql.user set authentication_string=password('root') where user='root';
mysql> flush privileges;
mysql> quit

第四步：删点第一步添加的skip-grant-tables

这时，就可以通过密码登录了：

建表：

create table tb(
id int(4) auto_increment,
name varchar(5),
dept varchar(5),
primary key(id)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1.存储引擎

Mysql逻辑分层：连接层、服务层、引擎层、存储层

InnoDB(默认)：支持事务，（适合高并发操作，行锁)，外键等，这种存储引擎数据的安全得到保障。

		表的结构存储在xxx.frm文件中
		数据存储在tablespace这样的表空间中（逻辑概念），无法被压缩，无法转换成只读。
		这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。
		InnoDB支持级联删除和级联更新。

MyISAM:性能优先，表锁

		MyISAM这种存储引擎不支持事务。
		MyISAM是mysql最常用的存储引擎，但是这种引擎不是默认的。
		MyISAM采用三个文件组织一张表：
			xxx.frm（存储格式的文件）
			xxx.MYD（存储表中数据的文件）
			xxx.MYI（存储表中索引的文件）
		优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。
		缺点：不支持事务。

查询数据库支持哪些引擎：show engines \G;

Sql编写过程：

select distinct ..from ..join ..on ..where .. group by ..having ..order by..

Sql解析过程：

from ..on ..join ..where ..group by ..having..select distinct order by..

2.B树

B树：又称为平衡多路查找树,小的在左，大的在右。

B+树：应文件系统所需而产生的一种B树的变形树（文件的目录一级一级索引，只有最底层的叶子节点（文件）保存数据）非叶子节点只保存索引，不保存实际的数据，数据都保存在叶子节点中。查询次数固定，都是N(树的深度)次。

3.索引

索引：帮助Mysql高效获取数据的数据结构。

优点：

1.提高查询效率，降低IO使用率‘

2.降低 CPU使用率

缺点：

1.索引本身很大

2.索引不是所有情况都适用：a.少量数据 b.频繁更新的字段 c.很少使用的字段

3.降低增删改的效率

索引分类：

主键索引 (Primary Key)： 某一个属性组能唯一标识一条记录，是特殊的单值索引

唯一索引 (Unique)：避免同一个表中某数据列中的值重复

常规索引 (Index)：快速定位特定数据，单值索引，可以有多个单值索引

复合索引(Index)：多个列构成的索引，遵循靠左原则

全文索引 (FullText)：快速定位特定数据

注意：如果一个字段是primary key,则该字段默认就是主键索引。

添加索引：
方式一：
create 索引类型 索引名 on 表(字段)

例：
单值索引：create index dept_index on tb(dept); 唯一索引：create unique index name_index on tb(name); 复合索引：create index dept_name_index on tb(dept,name);

方式二：
alter table 表名 add 索引类型 索引名(字段)
alter table tb add index dept_index(dept)

删除索引：
drop index 索引名 on 表名
drop index dept_index on db;

查询索引：
show index from 表名；
或者
show index from 表名 \G

性能问题：

a.分析SQL的执行计算 ：explain,可以模拟SQL优化器执行SQL语句
b.Mysql查询优化会干扰优化

explain select * from tb；

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

(1).id

id相同时，值越大越优先查询；
id不同时，从上到下按顺序查询。

(2)select_type(查询类型)

(3)type:索引类型、类型

system>const>eq_ref>ref>range>index>all

system、const只是理想情况，实际能达到ref、range

system:只有一条数据的系统表；或衍生表只有一条数据的主查询。

const:仅仅能查到一条数据的SQL，用于Primary key 或unique索引（类型与索引类型有关）

eq_ref:唯一性索引，对于每个索引键的查询，返回匹配唯一行数据，有且只能有一个。

ref:非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）

range:检索指定范围的行，where后面是一个范围查询(between,>,<,>=,特殊：in有时会失效)

index:查询全部索引中数据

all:查询全部表中的数据

总结：
system/const:结果只有一条数据
eq_ref:结果多条，但每条数据是唯一的
ref:结果多条，但每条数据是0或者多条

(4)possible_keys

可能用到的索引，是一种预测，不准确。

(5)key

实际使用到的索引。

如果possible_keys、key是NULL，说明没用到索引。

(6)key_len

索引的长度，用于判断复合索引是否被完全使用。

utf8中，一个字符占3字节；如果索引字段可以为NULL，则会使用一个字节用于标识；用两个字符标识可变长度(varchar)。

(7)ref

注意与type中的ref值区分，指明当前表所参照的字段。

(8)rows

被索引优化查询的数据个数(实际通过索引而查询到的数据个数)

(9)Extra

a.using filesort:性能消耗巨大，需要额外的一次排序(先查询，再排序)

对于单索引，如果排序和查找是同一个字段，则不会出现using filesort;如果排序和查找不是同一个字段，则会出现using filesort。

避免：where哪些字段，就order by 哪些字段。

对于复合索引，不能跨列(最佳左前缀）

避免：where和order by 按照复合索引的顺序使用，不要跨列或无序使用。

b.usingtemporary:性能损耗大，使用了临时表，一般出现在group by语句中。

避免：查询哪些列，就根据那些列group by

c.using index:性能提升，索引覆盖，不回表查询。不读取原文件，只从索引文件中获取数据。如果用到了索引覆盖，会对possible_key和key造成影响：(1)如果没有where，则索引只出现在key中;(2)如果有where，则索引出现在key和possible_key中。

d.using where:需要回表查询

e.impossible where:where子句永远为false

4.优化实例

单表优化、两表优化、多表优化

单表优化

建表

create table test03(
a1 int(4) not null,
a2 int(4) not null,
a3 int(4) not null,
a4 int(4) not null
);
alter table test03 add index index_a1_a2_a3_a4(a1,a2,a3,a4); 

explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4=4;

using index

using index

explain select a1,a2,a3,a4 from test03 where a4=4 and a3=3 and a2=2 and a1=1;

explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3;

explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2,a3;

using where;using index:a4跨列使用using where，此时a4索引无效，where和order by组合为a1,a2,a3不跨列因此为using index。

explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3;

using where;using index；using filesort

如果复合索引和使用的顺序全部一致(且不跨列使用)，则复合索引全部使用；如果部分一致，则使用部分索引。

两表优化、多表优化

建表

create table teacher2(
tid int(4) primary key,
cid int(4) not null
);
insert into teacher2 values(1,2);
insert into teacher2 values(2,1);
insert into teacher2 values(3,3);
create table course2(
cid int(4),
cname varchar(20)
);
insert into course2 values(1,'java');
insert into course2 values(1,'python');
insert into course2 values(1,'c');
cimmit;

索引加在哪张表？建在哪个位置？

小表驱动大表，数据量小的表，使用频繁(经常查询，不经常增删改)的字段。

避免索引失效的原则

(1)复合索引

a.复合索引，不要跨列或无序使用(最佳左前缀原则)
b.复合索引，尽量使用全索引匹配。

(2)不要在索引上进行任何操作(计算、函数、类型转换)，否则索引失效

如果是a,b,c是复合索引，b进行操作从而失效进一步会影响c。

(3)复合索引不能使用不等于(!= <>)或is null(is not null),否则自身及右侧索引全部失效

SQL优化是一种概率层面的优化，实际是否使用优化，需要explain推测。

(4)补救，尽量使用索引覆盖。

(5)like 尽量以常量开头，不要以‘%’开头，否则索引失效。

如果必须使用’%x%'进行模糊查询，可以使用索引覆盖，挽救一部分。

(6)尽量不要使用类型转换(显示、隐式)，否则索引失效。

(7）尽量不要使用or，否则引起失效

使用or甚至会导致or左侧的索引失效。

索引优化的方法

1.exist和in

如果主查询的数据集大，则用in;如果子查询的数据集大，则用exist。

exist语法：将主查询的结果，放到子查询结果中进行条件校验(看子查询是否有数据)，如果符合校验，则保留数据。

select tname from teacher where exists(select  * from teacher);

2.order by优化

using filesort 有两种算法(根据IO的次数)：

双路排序：扫描两次磁盘，第一次，从磁盘读取排序字段，对排序字段在buffer(mysql自带)中排序；第二次，读取其他字段。

单路排序：只读取一次(全部字段），在buffer中排序。但有一定的隐患，不一定是真的单路排序，有可能多次IO。原因：如果数据量特别大，无法将所有字段的数据一次性读取完毕，因此会进行“分片读取，多次读取”。

单路排序时，可以考虑调整buffer的大小：

set max_length_for_sort_date =1024;//单位byte

如果max_length_for_sort_dat定义的字节数太小，mysql会自动从单路调整到双路。

提高order by 查询的策略：

a.选择使用单路、双路排序；调整buffer的大小
b.避免使用select *,不利于索引覆盖
c.复合索引不要跨列使用
d.尽量保证全部的排序字段排序的一致性

SQL排序-慢查询日志

MYSQL提供的一种日志记录，用于记录MYSQL响应时间超过阈值的SQL语句。

检查是否开启了慢查询日志：

show variables like "%slow_query_log%";

可以临时开启、永久开启。

也可以更改阈值。

5.锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。

1.共享锁与排他锁

共享锁（读锁）：其他事务可以读，但不能写。
排他锁（写锁） ：其他事务不能读取，也不能写。

2.不同粒度锁的比较

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用。

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。最大程度的支持并发，同时也带来了最大的锁开销。
在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

3.实例

会话0给A表加了read锁，其他会话的操作：

a.可以对其他表(A表以外的表)进行操作；
b.对A表，读可以，写需要等待释放。

会话0给A表加了write锁，当前会话可以对加了写锁的表进行任何操作(增删改查);其他会话只有等当前会话释放写锁才可以进行增删改查操作。

4.死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁。

锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

5.MyISM锁模式

MyISM执行查询时会自动给相关表加读锁；执行增删改时会自动加写锁。

6.行锁

a.如果没有索引，则行锁会转为表锁
b.行锁的一种特殊情况：间隙锁。值在范围内，但却不存在。

7.乐观锁和悲观锁

乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。
乐观锁, 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
悲观锁，顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。