数据库优化/Linux安装Mysql/B+Tree详解

骑猪看日落 2022-04-11 10:23 377阅读 0赞

### 一.Linux安装MySQL ###

#### yum安装 ####

\#下载安装源  
wget [http://repo.mysql.com/mysql57-community-release-el6-8.noarch.rpm][http_repo.mysql.com_mysql57-community-release-el6-8.noarch.rpm]

\#安装rpm包  
rpm -ivh mysql57-community-release-el6-8.noarch.rpm

\#修改安装源  
vim /etc/yum.repos.d/mysql-community.repo  
将\[mysql55-community\]的enabled设置为１,\[mysql57-community\]的enabled设置为0

#### 安装mysql： ####

yum -y install mysql-server mysql  
\-----解决yum锁问题------  
rm -f /var/run/yum.pid

#### 配置 ####

数据库字符集设置  
mysql配置文件 /etc/my.cnf中加入  
character-set-server=utf8

#### 启动停止服务 ####

启动mysql服务：  
service mysqld start  
或者  
/etc/init.d/mysqld start

停止服务：  
service mysqld stop

开机启动：  
chkconfig mysqld on，查看开机启动设置是否成功chkconfig --list | grep mysql\*

#### 登录 ####

查询mysql第一次安装时的随机密码：  
grep ‘password’ /var/log/mysqld.log |head -n 1

创建root管理员：  
mysqladmin -u root password 123456

#### 远程访问 ####

开放防火墙的3306端口号  
/etc/sysconfig/iptabls 添加端口记录：

重启防火墙  
service iptables restart

创建远程访问用户：  
create user ‘root’@’%’ identified by ‘root’;

#### 授权 ####

grant all privileges on *.* to ‘root’@’%’;

### 二.安装后的相关路径 ###

#### 1./usr/bin : ####

存放mysql的相关命令文件

#### 2./usr/share/mysql : ####

mysql相关的配置文件信息

#### 3./etc/my.cnf : ####

mysql 核心配置文件,yum会自动生成,rpm安装需要从/usr/share/mysql中拷贝出来

#### 4./var/lib/mysql(重要) : ####

mysql数据存放的路径(数据库信息,表信息)

#### 有三类文件: ####

.frm:存放mysql的表结构信息(frm)
    .MYD:存放表的数据信息(Data)
    .MYI:存放表的索引信息(Index)

### 三.MySQL逻辑架构: ###

##### 连接处理层 #####

和客户端进行连接处理,授权认证等…,客户端可能是jdbc,navicat等

##### 核心处理层 #####

用于解析SQL语句,并且优化执行;  
通过分析器解析SQL语句,形成多种执行方案,交给优化器,优化器会对执行方案进行成本计算,最终选择一个优化器认为执行成本最优的SQL语句用于执行,存储过程和触发器都是执行在核心处理层

查询缓存-><-分析器  
分析器->优化器  
查询解析,分析,优化,缓存,所有的内建函数(日期,时间,数学和加密函数等)  
存储过程,触发器,视图

##### 存储引擎层(InnoDB) #####

该层主要是存放存储引擎,mysql中存储引擎被设计成可插拔式的,也就是随时可以更换存储引擎.  
存储引擎只是对上层提供相同的API,内部实现对上层是完全透明的,并且不同的存储引擎之间也是完全隔离的,存储引擎只需要响应数据就即可  
存储和提取数据,有很多的不同的存储引擎,可以自定义

##### 物理磁盘层 #####

真正存储数据的磁盘空间

### 四.MySQL的存储引擎 ###

#### 相关命令 ####

##### 1.查看当前数据库所支持的存储引擎 #####

show engines;

###### 常用的存储引擎InnoDB: ######

5.5版本之前默认为MyISAM  
5.5版本之后默认为InnoDB

##### 2.查询默认编码 #####

show variables like ‘%char%’;

#### MyISAM与InnoDB存储引擎的比较 ####

#### 1)MyISAM ####

**1.MylSAM不支持事务**,也就是说就是在MySQL5.5之前,都是无事务的机制  
**2.MylSAM只支持表锁**  
**3.没有聚簇索引**(聚簇:索引和数据分开存放)  
**4.支持全文索引**  
**5.没有外键**  
**6.SQL执行效率高**

#### 2)InnoDB ####

**1.InnoDB支持事务**  
**2.InnoDB支持表锁和行锁**  
**3.有聚簇索引**  
**4.不支持全文索引**(5.6之后版本支持)  
**5.引入了外键的概念**  
**6.相对来说SQL执行效率低**

#### 3)如何选择存储引擎: ####

如果不知道如何选择就选择InnoDB  
MylSAM可以用于不需要事务的场景,性能比InnoDB高  
比如日志记录,主从复制,读写分离时从机可以使用MylSAM,但是需要热备份需要使用InnoDB

### 五.SQL性能下降的可能性 ###

1.查询语句编写不合理  
2.索引失效  
3.关联查询太多  
只能从业务逻辑和表结构设计进行优化  
4.服务器的参数设置问题  
比如配置内存池的大小,解决硬盘排序的问题

### 六.数据类型的优化 ###

#### 选择最优的数据类型 ####

##### 1.原因 #####

MySQL支持的数据类型非常多,选择正确的数据类型对于获取高性能至关重要

##### 2.数据类型选择的原则 #####

###### 1.更小的通常更好 ######

通常情况下,更小的数据类型通常更快,占用更小的磁盘内存和CPU缓存但是必须确保没有低估数据长度

###### 2.简单就好 ######

简单数据类型的操作需要更少的CPU消耗,比如date,整型比字符操作代价更低  
比如:  
MySQL内建的类型(datetime,timestamp等)存储时间,而不是字符串  
应该用整型IP地址

###### 3.尽量避免NULL ######

通常情况下最好执行列为NOT NULL,除非真正需要存储NULL值,如果查询总包含为NULL的列,对于MySQL来说更难优化,因为为NULL的列使得索引,索引统计等都变得更复杂.通常把字段设置为NOT NULL提升比较小,但如果需要构建索引那提升就比较大了

首先,数据类型的优化应该在表结构的设计的时候,就需要考虑进去,因为在表数据量已经很大的时候去修改一个字段的数据类型或者是长度是一个成本特别高的行为

###### 基本原则: ######

尽可能小并且简单,当然也不能低估了存储值得最大范围

#### 具体的数据类型 ####

##### 数据类型 #####

###### 整数类型 ######

1.tinyint 8位 – byte -128~127 适合存储状态,年龄等  
2.smallint 16位 – short -3w~3w+  
3.mediumint 24位 – 无  
4.int 32位 – int -20亿~20亿+  
5.bigint 64位 – long  
6.unsigned属性 – 无符号属性  
添加unsigined属性表示该字段不允许负数,正数的上限大致提高一倍  
比如:tinyint unsigned可以存储0-255的返回而tinyint是-128~127范围  
有符号和无符号占用空间大小相同,具有相同的性能

###### 实数类型(浮点数) ######

1.float 32位  
2.double 64位  
3.decimal --精准实数  
decaimal需要额外的空间和计算开销,所以应该尽量在对数据精度有高度要求的时候使用,比如存储金额

###### 字符类型 ######

1.varchar  
用于处理可变长的字符串,是最常见的字符串数据类型  
它比定长更省空间

varchar需要使用1~2个额外的字节记录字符串的长度  
varchar节省了存储空间所以对性能也有帮助

但是由于长度可变,在update时可能使行比原来更长  
这就导致需要进行额外的工作,至于如何进行空间增长取决于不同的存储引擎

当字符串列最大长度比平均长度大很多,并且列的更新很少的时候比较时候使用varchar

2.char  
用于处理定长字符串,mysql总是根据定义的字符长度分配足够的空间

char非常适合存储很短的字符,或者长度都是很接近的字段

对于经常变更的字段,使用char更合适,因为定长的char类型不容易产生碎片  
对于非常短的列,存储空间也更有优势,因为varchar需要多两个字节

比如:手机号码或者md5加密的密码  
3.blob和text  
二进制和大文本类型

#### 注意 ####

varchar(5)和varcha(200)存储’hello’空间开销是一样的  
但是varchar(5)对性能提升有很大的优势,更长的列会消耗更多的内存

因为Mysql通常分配固定大小的内存块来保存内存  
尤其是使用内存临时表进行排序等操作时会特别糟糕  
所以最好的策略是只分配真正需要的空间

###### 时间类型 ######

1.datetime --8个字节  
能保存大范围的值,从1001到9999年,精度为秒,他是把日期和日期封装到YYYYMMDDHHMMSS的整数中,使用了8个字节的存储空间  
2.timestamp --4个字节  
保存了从1970.1.1到现在时间的毫秒数  
timestamp只使用了4个字节的存储空间,因此它的范围比datetime小很多,但是只能表示从1970年到2038年,另外timestamp也依赖于时区  
在一般情况下一般选择timestamp,因为只占了4个字节

### 一.索引优化 ###

#### 索引基础 ####

##### 什么是索引 #####

索引类似于一本书的目录,通过目录可以快速定位到书中的内容,通过索引可以快速定位到需要查询的数据

索引是一个帮助我们快速查询数据的**数据结构**

索引是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键。  
尤其当表中的数据量越来越大时，索引对性能的影响越发重要。

索引应该是对查询性能优化最有效的手段了。  
索引能够轻易将查询性能提高几个数量级。

但是索引经常被忽略，不恰当的索引对性能可能还会带来负面效果

##### 什么时候该添加索引 #####

1、主键自动建立主键索引(唯一索引)  
2、where子句中的列，频繁作为查询字段的列  
(与sql的执行频率和数据量的大小还有整个sql语句的结构有关)  
3、表连接关联的列(on 后面的条件字段)  
4、排序,分组用到的列  
5，索引的基数越大(**选择性大**)，索引的效率越高,  
什么叫基数越大，比如手机号，每个列都具有不同的值，非常好区别，这个就适合建立索引，  
而性别这样的字段，因为只有两个值，以不适合建立索引，就是区分度高低的问题

##### 什么时候不该加索引 #####

1.无意义的情况:  
表记录少  
2.需要额外维护索引:  
频繁修改的字段  
3.选择性低:  
数据重复且分布平均的字段

#### 索引分类 ####

##### 单值索引(单列) #####

即一个索引只包含单个列

创建单列索引:  
create index 索引名 on 表名(需要索引的列)

eg:  
create index idx\_title on t\_goods\_info(title)

##### 复合索引(多列) #####

即一个索引包含多个列

创建多列索引:  
create index 索引名 on 表名(需要索引的列1,需要索引的列2…)

eg:  
create index idx\_title\_gcount on t\_goods\_info(title,gcount)

注意:  
创建复合索引的时候,列的顺序很重要,不同的顺,序效果会大不相同

##### 唯一索引 #####

索引列的值必须唯一,但是允许有空值

创建唯一索引:  
create unique index 索引名 on 表名(需要索引的列)

eg:  
create unique index idx\_uq\_title on t\_title(title,gcount)

##### 全文索引 #####

InnoDB5.5不支持,5.6之后才支持的一种模糊查询的方式

创建全文索引:  
create fulltext index 索引名 on 表名 (需要索引的列名);

##### 索引基本语法 #####

###### 创建索引 ######

create unique|fulltext index 索引名 on 表名 (属性名\[长度\] \{asc|desc\});

###### 删除索引 ######

drop index 索引名 on 表名;

###### 查看当前表的所有索引 ######

show index from 表名;

### 二.B-tree索引详解(重点) ###

MySQL中的InnoDB和MyISAM两个最常见的存储引擎,索引底层都是使用B-Tree这种数据结构实现的

对索引来说,还有很多不同的数据结构实现的索引,比如哈希索引

#### 为什么MySQL没采用哈希索引(底层哈希表) ####

哈希索引定位单条数据的性能肯定大于B-Tree索引,但是哈希表**不支持范围查询**  
如果执行类似select \* from t\_good\_info where id < 9,哈希索引无法实现

又需要定位快,又要支持范围查询,树形结构就是一个很好的数据结构

#### 为什么MySQL没采用二叉树索引 ####

二叉搜索树的缺点在于**不平衡**,一个顺序插入的二叉搜索树就类似于一个链表了,查询的高效性就变成遍历整个链表了,高效性得不到体现

为了解决平衡问题,一个平衡的二叉树就是很好的选择,常见的平衡二叉树有AVL树,红黑树

#### 为什么MySQL没采用红黑二叉树索引 ####

通过变色和旋转两种方式维系整棵树的平衡,所以红黑树本身是一个搜索数据结构(一定平衡,定位效率高,支持范围查询)

红黑树规则:  
性质1. 节点是红色或黑色。

性质2. 根是黑色。

性质3. 所有叶子都是黑色。

性质4. 从任一节点到其每个叶子的所有简单路径都包含相同数目的黑色节点。

性质5.从每个叶子到根的所有路径上不能有两个连续的红色节点。

MySQL没有选择红黑树的原因在于,当一张表的记录足够大的时候,索引本身也会很大,当索引足够大了之后,内存有可能放不下,这个时候索引只能存储到硬盘上

此时内存只会存放根节点然后每次比较都会通过磁盘IO加载下面的子节点,**树的层级越大,磁盘IO次数会更多**,势必会导致查询性能的下降

为了减少磁盘IO次数,这个时候就可以选择多路数(层级<=二叉树)

#### 什么是B-Tree ####

B-Tree(Balance-Tree 一个平衡的多路树)

度:  
B-Tree中每个节点的元素的最大值  
分裂:  
当B-Tree某一个节点的元素个数达到了元素最大值,该节点需要进行分裂  
![Alt text][]

#### 什么是B+Tree ####

在B-Tree的基础上升级了  
相对的特点:  
1.所有相邻的叶子结点通过一个**双向指针**相互连接  
2.所有的非叶子结点,在叶子结点上也会存储  
![Alt text][]

#### 为什么要将B-Tree升级成B+Tree ####

1.所有的非叶子结点并不保存指针指向物理磁盘,可以使得相同的内存大小,度可以更大  
2.双向指针用来方便范围查询

#### 一个复合索引是有一个B+Tree还是多个 ####

一个,这棵树会以第一个字段开始排序,如果字段相同逐级排序

#### 什么是聚簇索引? ####

MyISAM没有聚簇索引,InnoDB一定会有聚簇索引,InnoDB聚簇索引就是主键索引  
聚簇索引就是包括了主键索引+数据行,在InnoDB中没有实际的表数据了,表数据就在聚簇索引中

因此每张表都必须给一个和业务无关的id作为主键,并且自动增长,避免无序导致需要花更大的成本去维护索引

如果一个表中没有任何主键,MySQL就会就会将一个唯一非空的字段作为聚簇索引,如果也没有字段是唯一非空的那么MySQL会自己维护一个**隐藏字段**作为聚簇字段

#### 覆盖索引 ####

索引树中直接**存储了需要查询的列数据**,这样就不用去聚簇索引中再查询一次,提高了查询性能

注意:写select后面一定不要写\* , \*一定用不上覆盖索引,正确的做法是要什么字段就写什么字段,哪怕全部都要也应该全部都写出来

#### explain-执行计划 ####

##### 什么是执行计划？ #####

在优化器确定了执行sql语句的方案后，会生产一个执行计划，将该执行计划交给执行器去调用存储引擎，执行sql语句，开发者通过查看优化器的最终执行计划，可以帮助我们了解MySQL是如何执行这一条sql语句的，通常执行计划就是我们优化sql语句的切入点;

就类似于我们Java中的日志记录帮助我们查看Java程序的执行过程

###### 注意： ######

执行计划有可能和很多因素有关系，结果并不是特别稳定的

##### 如何查看执行计划 #####

explain select语句

##### 字段含义 #####

1.**id**:用于标识sql的执行顺序  
当有连接查询或者子查询时，会生成多条执行计划  
**四种情况:**  
a.id相同:表示执行顺序是从上到下的，连接查询中会出现  
**注意:**

###### 小表驱动大表原则: ######

MySQL加载表的行为，通常遵循小表驱动大表  
如何确定驱动表(小表):  
如果有连接条件,符合连接条件行数比例小的表为驱动表;  
如果没有连接条件,表记录少的表就是驱动表

###### 为什么要有小表驱动大表原则 ######

减少表的加载次数

###### 字段属性 ######

**[1.id][](重要)**:标识荡秋千查询语句的执行顺序  
a.id相同:  
按顺序执行,连接查询中会出现  
b.id不同:  
id越大优先级越高，子查询中会出现  
c.id相同和不同同时存在:  
id越大优先级越高，id相同则顺序执行，既有子查询又有连接查询  
d.id为null:  
优先级最低，该部分最后执行  
**2.select\_type**:标识当前查询语句的类型  
MySQL对所有查询语句分为两类  
**a.简单查询**  
没有子查询和union子句的查询  
**b.复杂查询**  
有子查询或者union子句的查询

###### 属性值: ######

**Simple**:  
简单查询,查询中不包含任何子查询或者union子句  
**Primary**:  
在复杂查询中,包含了子查询或union的外层查询语句就是Primary,通常也是最后执行的部分  
**SubQuery**:  
在select/where后面跟着的子查询  
**Derived(衍生)**:  
在from后面跟着的子查询,会产生衍生表的查询语句  
**Union**:  
如果第二个select在union之后,则会标记为Union;如果union包含在from之后的话会被标记为Derived  
**union | union all**  
并集，只要字段数量一致就可以

###### 区别:union all 不会去重复(首选,性能会更好) ######

**Union Result**:  
从Union表获取结果的select  
**3.type(重要)**:  
表示当前的sql语句访问记录行的方式,换而言之就是Mysql查找表中行的方式,下面的访问方式,从最差到最优:  
null > system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > ALL

###### 常见的类型 ######

**ALL**:全表扫描  
表示当前sql语句是按照全表扫描的方式访问记行，通常看到这个type，就表示sql语句需要被优化了,结合rows(当前sql有可能要查询的记录行数)判断是否需要优化  
**index**:全索引扫描  
这个跟全表扫描一样，只是MySQL扫描表,按索引次序进行而不是行;表示当前sql语句会按照全索引的方式整个表,和ALL性能差不多,结合Extra(Using index)判断是否使用了覆盖索引  
**range**:范围扫描  
就是一个有限制的索引扫描,它开始于索引里的某一点,返回匹配这个值域的行,rows越少越好  
**ref**:精确的范围查询  
这个类型会出现在非唯一性索引或者唯一索引的非唯一性前缀时上,它会返回匹配某个值的所有行。  
**eq\_ref**:精准查询  
使用这种搜索查找,Mysql知道最多只返回一条符合条件的记录;这种访问方法可以在Mysql使用主键或者唯一性索引连接查询时看到  
**const**:常量查询  
当Mysql能对查询的某部分进行优化并将其转换成一个常量时,它就会使用这些访问类型。  
举例来说,当你通过某一行的主键放入where字句里的方式来查询时,MySQL会把这部分操作转换为一个常量;就是直接取数据  
**system**:  
表只有一行记录,这是const类型的特例,通常不会出现  
**NULL**:  
这种方式意味着MySQL能在优化阶段分解查询语句,在执行阶段甚至用不着再访问表或索引;这是最优的效果

###### 注意： ######

通常在生产环境中,得保证查询至少达到range级别,最好能达到ref级别以上,同样也要和rows结合判断  
**4.possible\_keys**:  
可能应用到这个执行计划上的索引,会返回一个索引列表  
**5.key(重要)**:  
表示当前执行计划用上了哪个索引  
如果为NULL,则没有使用任何索引;

possible\_keys表示哪个索引能有助于高效的行查找，  
而key显示的是优化采用哪个索引可以最小化查询成本

###### 注意: ######

###### a.每个执行计划最多可能用上一个索引 ######

###### b.possible\_keys并不为NULL时候,key有可能为空 ######

###### c.possible\_keys为NULL,key有可能不为空 ######

###### d.force index(索引名) 方式可以强制MySQL使用某个索引,但是通常最好不要干预MySQL索引的选择 ######

**6.key\_len**:  
使用索引的长度,在不损失精准性的情况下,长度越短越好;  
该字段值越大表示用上了更多的索引  
**7.ref**:  
表示查询记录时，所用到列名或常量  
**8.rows(重要)**:  
表示MySQL预估需要读取的纪录数,也就是大概要扫描多少行,并不能理解为返回的数据行,并不是精准值;  
总之这个字段越小越好  
**9.Extra(重要)**:  
关于MySQL如何解析查询的额外信息;

###### 常见属性值: ######

a.Using index:效率高  
表示该执行计划用上了覆盖索引,避免了全表扫描  
b.Using temporary:分组排序临时表  
以为这MySQL对查询结果排序时候,会使用一个临时表(尽量杜绝)  
c.Using filesort:  
表示执行sql语句时,mysql使用了内存排序或者硬盘排序  
如果是内存排序,通常就无需优化;如果是硬盘排序,表示数据量很大,尽量避免;

###### 注意: ######

###### 对于排序和分组的优化方案,可以给排序和分组的字段添加索引,帮助MySQL进行排序;对于简单的sql语句来说MySQL不会采用索引进行排序,宁可全表扫描,再用内存排序; ######

###### 在比较简单的sql语句的情况下,MySQL会认为通过全表扫描会比使用索引扫描性能更好; ######

###### 通过全表扫描,再排序,坏处是需要额外的排序操作; ######

###### 通过索引扫描,无需再排序,获取数据行是随机io,耗时; ######

d.Using where:  
意味着MySQL将在存储引擎检索行后再进行过滤。

#### 高性能的索引策略 ####

##### 正确的创建和使用索引是实现高性能查询的基础 #####

##### 如何创建一个高效的索引 #####

###### 1.多列索引 ######

在多数情况下，在多个列上建立独立的索引并不能提高查询性能。理由非常简单，MySQL不知道选择哪个索引的查询效率更好，所以在老版本，比如MySQL5.0之前就会随便选择一个列的索引，而新的版本会采用合并索引的策略。

在多个条件相交(and)的时候,一个多列索引的性能会优于多个独立索引;同时我们需要选择合适的索引顺序,把选择性更高的列放在前面。

当多个索引做联合操作时(or),对结果集的合并,排序等操作需要耗费大量的CPU和内存资源特别是当其中的某些索引的选择性不高,需要返回合并大量数据时,查询成本更高;所以这种情况下还不如走全表扫描。

###### 2.独立索引 ######

是指索引列不能是表达式的一部分，也不能是函数的参数。  
应该养成简化where条件的习惯，始终将索引列单独放在运算符的一侧。

例如：  
select \* from … where id + 1 = 5;  
这是一个错误的用法，mysql无法解析id + 1 = 5 这个方程式，故不会使用到id列上的索引。

###### 3.前缀索引 ######

###### 3.1什么是前缀索引 ######

有时候需要索引很长的字符列,这会让索引变得很大且慢  
通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率;但是这样也会降低索引的选择性

###### 3.2创建前缀索引 ######

create index idx\_address on t\_address(address(2))

###### 3.3索引的选择性 ######

索引的选择性是指，不重复的索引值和数据表的总记录的比值。  
索引选择性越高则查询效率越高，  
索引选择性可以让mysql在查询时过滤掉更多的行。

###### 3.4计算完整列的选择性 ######

select count(distinct 列名)/count(\*) from 表名

###### 3.5选择合适的前缀索引长度 ######

前缀索引的长度选择应该在一个合适的范围，不能太长同时要保证较高的选择性。  
前缀索引的选择性应该接近于完整列的选择性。

###### 3.6计算前缀索引的选择性 ######

select count(distinct left(列名, 长度))/count(1) from 表名

###### 注意:前缀索引也有缺点,mysql无法使用前缀索引做order by和group by ######

##### 全列匹配: #####

和索引中的所有列进行匹配

##### 最左前缀匹配: #####

和索引中的第一列进行匹配

##### 匹配列前缀: #####

针对模糊查询应该尽可能的使用 like ‘keyword%’,这种匹配列的前缀是有可能使用索引的

##### 匹配范围值: #####

和索引中的第一列的范围匹配

##### 精确匹配某一列并范围匹配另外一列 #####

##### 聚簇索引 #####

###### 什么是聚簇索引 ######

聚簇索引实际上就是在同一个结构中保存了B-tree索引和数据行

###### 如何创建聚簇索引 ######

MySQL无法主动创建聚簇索引，InnoDB是将我们的主键作为聚簇索引。如果没有定义主键，则InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，则InnoDB会隐式的定义一个主键来作为聚簇索引。

###### 二级索引 ######

二级索引即普通索引，和聚簇索引不同。二级索引的叶子节点中存储的不是行指针，而是主键值。即二级索引的叶节点都指向聚簇索引对应的主键

###### 注意 ######

在使用InnoDB表时，应该提供一个自动增长的列作为主键，这种主键和业务应该无关，这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好

##### 覆盖索引 #####

###### 什么是覆盖索引 ######

如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。

###### 优势 ######

索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量  
索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

##### 索引的限制（索引失效） #####

1.如果以通配符开头的条件(’%aaa’),mysql索引会失效  
2.or会导致索引失效  
3.如果使用 is null, is not null 不能使用索引  
4.如果在索引列上使用了!=、<>时会使索引失效  
5.字符串不加单引号为导致索引失效  
6.如果在索引列上做任何操作（计算、函数、(自动或者手动)类型转换），会导致索引失效进而变成全表扫描  
7.查询中某个列有范围查询，则其右边的所有列都无法使用索引优化查询。  
8.不能跳过索引中的列，比如查询第一列和第三列，而不指定第二列的话，则只能使用索引的第一列  
9.如果不按照最左前缀原则，则无法使用索引

###### 注意,心中有树即可 ######

### 查询优化 ###

#### SQL语句的执行顺序 ####

##### 编写 #####

select->distinct->  
from->join on->where->group by->having->order by->limit

##### 执行顺序 #####

from->join on->where->group by->having->  
select->distinct->order by ->limit

###### 面试题:on和where过滤条件的区别 ######

on筛选的记录有可能还会回到结果行,但是where过滤的记录行一定不会回到结果行

##### MySQL查询的过程 #####

1.客户端发送一条查询给服务器  
2.服务器先检查查询缓存，如果命中缓存，则立即返回存储在缓存中的结果。否则进入下一阶段  
3.服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划  
4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询  
5.将结果返回给客户端，同时缓存查询结果

##### 慢查询日志： #####

###### 什么是慢查询日志？ ######

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，  
具体指运行时间超过long\_query\_time值的SQL，则会被记录到慢查询日志中。  
long\_query\_time的默认值为10，意思是运行10S以上的语句。  
默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，  
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。  
慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

###### 开启慢查询日志 ######

1.查看慢查询日志：  
select @@slow\_query\_log;  
2.开启：  
SET GLOBAL slow\_query\_log=1;  
临时开启慢查询日志,如果Mysql服务重启,则慢查询日志会关闭,如果需要开启慢查询日志,需要修改my.ini(my.conf)  
3.查询慢查询次数：  
show status like ‘slow\_queries’;  
4.设置慢查询的阈值：  
查看阈值：  
show variables like ‘long%’  
设置阈值：  
set long\_query\_time=0.001;  
5.查看慢查询日志的位置  
select @@slow\_query\_log\_file;

注意：每次删除慢查询日志，需要重新开启慢查询，日志才会自动生成

##### Show Profile #####

###### 什么是Show Profile? ######

mysql提供的可以用来分析当前会话中语句执行的资源消耗情况

###### 查询当前Mysql版本是否支持Show Profile ######

show variables like ‘profiling’;

###### 开启Show Profiling ######

set profiling = on;

###### 查看Show Profiling记录 ######

show profiles;

###### 诊断SQL ######

show profile cpu,block io for query ‘N’  
其中N是记录下来的sql语句id

###### 需要优化的步骤 ######

1.converting HEAP to MyISAM 查询结果太大，内存不够，写入磁盘中  
2.create tmp table 创建了临时表  
3.copying to tmp table on disk 把内存中临时表复制到磁盘  
测试

#MySQL的逻辑架构
    #第一层：物理连接层，处理客户端的链接的，授权认证...
    #第二层：核心服务层，查询缓存 -> 分析器 -> 解析sql语句 -> 优化器 -> 执行计划 -> 执行器
    #第三层：存储引擎层，存储引擎就是单纯存储和提取数据，响应上层的操作。存储引擎之间是互相隔离的
    
    #InnoDB和MyISAM存储引擎区别
    #1、事务的区别
    #2、InnoDB有行锁和表锁，MyISAM只有表锁
    #3、InnoDB有外键，MyISAM没有外键
    #4、InnoDB有聚簇索引，MyISAM没有聚簇索引
    #5、InnoDB没有全文索引（5.6以后有），MyISAM有全文索引
    #6、MyISAM执行SQL的性能要优于InnoDB
    
    #数据类型的优化
    #简单而小巧的类型
    
    
    #索引优化
    #什么是索引？数据结构
    #哈希表？不支持范围查询
    #搜索二叉树？优势，定位查询快(比哈希表要慢)，同时支持范围查询  劣势，有可能不平衡
    #红-黑树？优势，平衡的二叉搜索树 劣势，如果数据量很大的时候，层级太多
    #B-Tree？平衡的多路树
    #B+Tree
    
    #聚簇索引 - 在InnoDB中一定会有一个聚簇索引。主键索引树的叶子节点就是记录行
    #覆盖索引 - 索引树中已经包含了需要查询的数据，这样就不用再去“回表”查询，提高搜索效率
    
    
    #班级表
    create table class(
    id int primary key auto_increment,
    cname varchar(20),
    cnum int
    );
    
    #学生表
    create table student(
    id int primary key auto_increment,
    name varchar(20) not null,
    age int,
    score float,
    birthday timestamp,
    cid int
    );
    
    #
    insert into class values(null, '一年一班', 0);
    insert into class values(null, '一年二班', 0);
    insert into class values(null, '一年三班', 0);
    insert into class values(null, '一年四班', 0);
    insert into class values(null, '一年五班', 0);
    insert into class values(null, '一年六班', 0);
    insert into class values(null, '一年七班', 0);
    insert into class values(null, '一年八班', 0);
    insert into class values(null, '一年九班', 0);
    #
    insert into student values(null, '小明', 6, 1.0, '1998-09-08', 1);
    insert into student values(null, '小红', 7, 1.1, '1997-05-26', 1);
    insert into student values(null, '小刚', 7, 1.2, '1997-03-04', 2);
    insert into student values(null, '小白', 6, 1.0, '1998-02-01', 3);
    insert into student values(null, '小黑', 6, 1.5, '1998-07-16', 3);
    insert into student values(null, '小黄', 5, 2.0, '1999-12-1', 3);
    
    
    #执行计划
    #如何查询执行计划：
    explain select * from student
    
    #当有连接查询或者子查询时，会生成多条执行计划
    #id相同的情况：
    #驱动表，
    #1）如果有连接条件，符合连接条件的行数少的表就是驱动表
    #2）如果没有连接条件，表记录少的表就是驱动表		
    explain select * from student s join class c on s.cid = c.id
    #id不相同的情况：
    explain select * from student s where s.cid = 
    	(select id from class c where c.cname = '一年一班')
    #id相同不同同时存在：
    explain select * from (select * from student) s join class c on s.cid = c.id
    
    
    #select_type
    #simple
    explain select * from student;
    explain select * from student s join class c on s.cid = c.id
    
    #primary
    explain select * from student s where s.cid = 
    	(select id from class c where c.cname = '一年一班')
    
    #subquery
    explain select * from student s where s.cid = 
    	(select id from class c where c.cname = '一年一班')
    	
    #derived
    explain select * from (select * from student) s join class c on s.cid = c.id
    
    #union | union all	
    #union - 合并两条sql语句的结果，只需要字段列数一样，并且会去重
    #union all - 合并两条sql语句的结果，只需要字段列数一样，不会去重（首选下面这种）
    explain
    select * from student s1 where id < 2
    union
    select * from student s2 where id < 3
    
    #type字段：
    #all：全表扫描
    explain select * from student where name = "小明";
    #index：全索引扫描
    explain select id from student
    #range：查询索引范围
    explain select * from student where id < 3
    #ref：准确的范围查询，这个类型会出现在非唯一性索引上
    create index idx_name on student(name)
    explain select * from student where name = "小明"
    #eq_ref：表示精准查询，mysql知道这个查询只会返回一个结果。必须有主键或者唯一性索引的联合查询才会出现
    explain select * from student s join class c on s.cid = c.id
    #const
    #system:
    explain select * from (select * from student where id = 1) s;
    #null
    explain select now();
    
    
    #possible_keys
    create index idx_id_name on student(id,name)
    explain select * from student force index(idx_id_name) where id = 1;
    
    #key_len
    create index idx_name_age_score on student(name,age,score)
    explain select * from student where name < "小明" and age > 4 and score > 1.0
    
    #ref
    explain select id,name,age,score from student where name = "小明" and age = 4;
    explain select * from student s join class c on s.cid = c.id
    
    explain
    select c.cname, count(*) 
    from student s join class c on s.cid = c.id 
    group by c.cname
    
    create index idx_age on student(age)
    explain select * from student force index(idx_age) order by age
    
    
    explain select * from student where age > xxxx
    
    idx_address
    idx_name
    
    create index idx_address on student(address(2))
    
    address
    中华人民共和国广东省深圳市xxxx区xxxx街道xxxx小区xxx栋xxx单元101号
    中华人民共和国广东省深圳市xxxx区xxxx街道xxxx小区xxx栋xxx单元102号
    中华人民共和国广东省深圳市xxxx区xxxx街道xxxx小区xxx栋xxx单元xxx号
    中华人民共和国广东省深圳市xxxx区xxxx街道xxxx小区xxx栋xxx单元xxx号
    中华人民共和国广东省深圳市xxxx区xxxx街道xxxx小区xxx栋xxx单元xxx号
    中华人民共和国广东省深圳市xxxx区xxxx街道xxxx小区xxx栋xxx单元xxx号
    中华人民共和国广东省深圳市xxxx区xxxx街道xxxx小区xxx栋xxx单元xxx号
    
    select count(distinct 列名)/count(*) from 表名
    select count(distinct name)/count(*) from student;
    select count(distinct left(name, 2))/count(*) from student;
    
    create index idx_name_pre on student(name(2))
    
    idx_name_age
    explain select * from student where name = 1 
    
    
    select * from student s left join class c on s.cid = c.id
    
    
    select @@slow_query_log; 
    set global slow_query_log = 1;
    show status like 'slow_queries';
    show variables like 'long%'
    set long_query_time=0.00001;
    
    select * from student
    
    select @@slow_query_log_file; 
    
    show variables like 'profiling';
    set profiling = on;
    
    
    show profiles;
    show profile cpu for query 682
    
    select * from student;
    select * from student s join  class c on s.cid = c.id
    
    
    
    
    #学生表
    create table student(
    id int primary key auto_increment,
    name varchar(20) not null,
    age int,
    score float,
    birthday timestamp,
    cid int
    );
    
    #课程表
    create table course(
    id int primary key auto_increment,
    cname varchar(10)
    );
    
    #成绩表
    create table score(
    sid int,
    cid int,
    score int default 0
    );
    
    insert into course 
    	values
    (null, "高等数学"),
    (null, "线性代数"),
    (null, "毛泽东思想"),
    (null, "邓小平理论"),
    (null, "马克思主义"),
    (null, "计算机电路基础"),
    (null, "操作系统"),
    (null, "Mysql数据库"),
    (null, "Oracle数据库"),
    (null, "Java编程"),
    (null, "C语言基础"),
    (null, "二进制"),
    (null, "概率学"),
    (null, "大学英语"),
    (null, "专业英语"),
    (null, "PHP编程"),
    (null, "C++"),
    (null, "Pythod编程"),
    (null, "云计算"),
    (null, "大数据");
    
    
    #批量插入学生记录
    drop procedure if exists insert_stu;
    delimiter &&
    create procedure insert_stu()
    begin
    
       declare i int default 0;
       A:loop
    	insert into student value(null, concat("小明",i), i, rand(), now(), rand()*20);
    	set i = i + 1;
    	if i >= 200000 then
    	    leave A;
    	end if;
       end loop;
    end &&
    delimiter ;
    
    call insert_stu();
    
    #批量插入学生成绩
    drop procedure if exists insert_score;
    delimiter &&
    create procedure insert_score()
    begin
    
       declare i int default 1;
       declare j int default 1;
    
    
       A:loop
    	set j = rand() * 20 + 1;
    	B:loop
    		insert into score value(i, j, rand() * 100);
    		set j = j + rand() * 5 + 1;
    		if j > 20 then
    			leave B;
    		end if;
    	end loop B;
    	set i = i + 1;
    	if i > 200000 then 
    		leave A;
    	end if;
       end loop A;
    end &&
    delimiter ;
    
    call insert_score();
    
    #课程表20条
    select count(*) from course;
    #成绩表 70W+条
    select count(*) from score;
    #学生表 20W条
    select count(*) from student;
    
    #关闭查询缓存
    show variables like '%cache%';
    set global query_cache_size = 0;
    set query_cache_type = OFF;
    
    #查询成绩表的总条数
    #count(id)
    select count(*) from score;
    
    #查询学生姓名中包含'生'字的学生信息
    #模糊查询的优化
    #采用覆盖索引
    #fulltext,solr等第三方的全文检索服务
    create index idx_name_age on student(name,age)
    explain select name,age from student where name like '%明%'
    
    
    #查询所有学生按年龄从小到大排列
    create index idx_age on student(age)
    explain select * from student force index(idx_age) order by age
    
    
    #查询id小于50或者年龄大于20的学生信息
    create index idx_age on student(age);
    explain 
    select * from student where id < 50
    union
    select * from student where age > 199000;
    
    #查询选了每门课的学生数量 course score
    explain select c.cname, count(*) 
    	from course c 
    	join score sc 
    	on c.id = sc.cid 
    	group by c.id order by null;
    	
    create index idx_cid on score(cid)	
    
    #查询高等数学考试考了100分的考生信息
    explain select * from student s join(
    select sid from score where cid = (
    select id from course where cname = "高等数学") and score = 100
    ) t on s.id = t.sid
    
    
    create index idx_cname on course(cname)
    create index idx_cid_score_sid on score(cid,score,sid)
    
    
    #分页查询学生第10w页记录 - 分页的优化 
    #mysql分页越靠后的页码 越慢
    select * from student limit 0, 2
    select * from student limit 199998, 2
    #覆盖索引+延迟关联

### 库表结果优化 ###

### 硬件层面 ###

[http_repo.mysql.com_mysql57-community-release-el6-8.noarch.rpm]: http://repo.mysql.com/mysql57-community-release-el6-8.noarch.rpm
[Alt text]: 
[1.id]: http://1.id