Mysql数据库优化

一、索引
1.1 索引概述
Mysql官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序），在数据之外，数据库系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构实现高级查找算法，这种数据结构就是索引，如下面的示意图：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的），为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包括索引键值和一个纸箱对应数据记录的物理地址的指针，这样就可以运用二叉查找树快速获取到相应的数据。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上，索引是数据库中用来提高性能的最常用的工具。

1.2索引优势劣势
优势：

1. 类似于数据的目录索引，提高数据检索的效率，降低数据库的IO成本；
2. 通过索引列队数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势：

1. 实际上索引也是一张表，该表中保护了主键与索引字段，并指向实体类的记录，所以索引列也要占用空间；
2. 虽然索引大大提高了查询效率，同时却也降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

1.3索引结构
索引是在Mysql的存储引擎层中实现的，而不是在服务器层实现的，所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。Mysql目前提供了一下四种索引：

1. BTREE索引：最常见的索引类型，大部分索引都支持B树索引；

2. HASH索引：只有Memory引擎支持，使用场景简单；

3. R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍；

4. Full-text（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引

   						MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持
   

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉树）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树索引，统称为索引。

1.3.1BTREE索引结构
BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

• 树中每个节点最多包含m个孩子；
• 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子；（m/2向上取整）
• 若根节点不是叶子节点，则至少有两个孩子；
• 所有的叶子节点都在同一层；
• 每个非叶子节点有n个key与n+1个指针组成，其中[ceil(m/2)-1]<= n <= m-1

以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1]<=n<=m-1。所以2<=n<=4。当n>4时，中间节点分裂到父节点，两边节点分裂。
插入C N G A H E K Q M F W L T Z D P R X Y S 数据为例。
演变过程如下：
1）插入前四个字母C N G A

2）插入H，n>4，中间元素G字母向上分裂到新的节点

3）插入E、K、Q不需要分裂

4）插入M，中间元素M字母向上分裂到父节点G

5）插入F、W、L、T不需要分裂

6）插入Z，中间元素T向上分裂到父节点中

7）插入D，中间元素D向上分裂到父节点中，然后插入P、R、X、Y不需要分裂

8）最后插入S，NPQRS节点n>4，中间节点Q向上分裂，但分裂后父节点DGMQT的n>4，中间节点M向上分裂

到此，该BTree就已经构建完成了，BTree和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTree的层级结构比二叉树小，因此搜索速度快。

1.3.2 B+TREE结构
B+TREE为BTree的变种，B+TREE与BTree的区别为：

1. n叉B+TREE最多含有n个key，而Btree最多含有n-1个key（这里的key指的是同一节点中的元素个数）
2. B+TREE的叶子节点保存所有的key信息，依key大小顺序排列
3. 所有的非叶子节点都可以看做是key的索引部分
   
   由于B+TREE只有叶子节点保存key（元素信息），查询任何key都要从root走到叶子，所以B+TREE的查询效率更加稳定。

1.3.3 MySQL中的B+TREE
MySQL索引数据结构对经典的B+TREE进行了优化，在源B+TREE的基础上，增加一个纸箱相邻叶子节点的链表指针，就形成了导游顺序指针的B+TREE，提高区间访问的性能。
MySQL中B+TREE索引的结构示意图：（MySQL中的BTREE索引指的就是B+TREE）

1.4 索引分类

1. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
2. 唯一索引：索引列的值必须唯一，但允许有空值
3. 复合索引：即一个索引包含多个列

1.5 索引语法
索引在创建表的时候，可以同时创建，也可以随时增加新的索引。
准备环境

create database demo_01 default charset=utf8mb4;
use demo_01;
create table `city`(
`city_id` int(11) not null auto_increment,
`city_name` varchar(50) not null,
`country_id` int(11) not null,
primary key (`city_id`)
) engine=InnoDB default charset=utf8;

create table `country` (
`country_id` int(11) not null auto_increment,
`country_name` varchar(100) not null,
primary key (`country_id`)
) engine=InnoDB default charset=utf8;

insert into `city` (`city_id`, `city_name`, `country_id`) values (1, '西安', 1);
insert into `city` (`city_id`, `city_name`, `country_id`) values (2, 'NewYork', 2);
insert into `city` (`city_id`, `city_name`, `country_id`) values (3, '北京', 1);
insert into `city` (`city_id`, `city_name`, `country_id`) values (4, '上海', 1);

insert into `country` (`country_id`, `country_name`) values (1, `China`);
insert into `country` (`country_id`, `country_name`) values (2, `America`);
insert into `country` (`country_id`, `country_name`) values (3, `Japan`);
insert into `country` (`country_id`, `country_name`) values (4, `UK`);

1.5.1 创建索引
create [unique|fulltext|spatial] index index_name [using index_type] on table_name(index_col_name, …)
index_col_name:column_name[(length)][ASC|DESC]
实例：为city表中的city_name字段创建索引

create index idx_city_name on city (city_name);

1.5.2 查看索引
语法：show index from table_name;
示例：查看city表的索引信息

1.5.3 删除索引
语法： drop index index_name on table_name;
示例：删除city表的idx_city_name索引

1.5.4 alter命令

1. alter table tb_name add primary key(column_list);该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null
2. alter table tb_name add unique index_name (column_list); 该语句创建索引的值必须是唯一的（除了null外，null可能会出现多次）
3. alter table tb_name add index index_name(column_list); 添加普通索引，索引值可以出现多次
4. alter table tb_name add fulltext index_name(column_list); 该语句指定了索引为FULLTEXT，用于全文索引

1.6 索引设计原则
索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

1. 对查询频次较高，且数据量比较大的表建立索引
2. 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合
3. 使用唯一索引，区分度较高，使用索引的效率越高
4. 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难症，虽然最终仍会找到一个可用的索引，但无疑提高了选择的代价
5. 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率
6. 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询sql可以利用组合索引来提升查询效率

创建复合索引
create index index_name_email_status on table_seller(name, email, status);

就相当于
对name创建索引；
对name,email创建索引；
对name, email, status创建索引；

二、sql优化
在应用的开发过程中，由于初期数据量小，开发人员写sql语句时更重视功能的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多sql语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的sql语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化。
当面对一个有sql性能问题的数据库时，我们应该从何处入手来进行系统的分析，是的能够尽快定位问题sql并尽快解决问题。
1. 查看sql执行频率
MySQL客户端连接成功后，通过show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数session或者global来显示session级（当前连接）的统计结果和global级（自数据库上次启动至今）的统计结果，如果不写，默认使用参数session。
下面的命令显示了当前session中所有统计参数的值：

show status like 'Com_______';

备注：Com_delete（删除的次数），Com_insert（插入的次数），Com_select（查询的次数），Com_update（更新的次数），Com_commit（提交事务的次数）

针对InnoDB存储引擎
show status like 'Innodb_rows_%';

Com_xxx表示每个xxx语句执行的次数，我们通常比较关心的是以下几个统计参数

参数	含义
Com_select	执行select操作的次数，一次查询只累加1
Com_insert	执行insert操作的次数，对于批量插入的insert操作，只累加1
Com_update	执行update操作的次数
Com_delete	执行delete操作的次数
Innodb_rows_read	select查询返回的行数
Innodb_rows_inserted	执行insert操作插入的行数
Innodb_rows_updated	执行update操作更新的行数
Innodb_rows_deleted	执行delete操作删除的行数
Commections	试图连接MySQL服务器的次数
Uptime	服务器工作时间
Slow_queries	慢查询的次数

Com_***：这些参数对于所有的存储引擎的表操作都会进行累加
Innodb_***：这几个参数只是针对Innodb存储引擎的，累加的算法也略有不同

2. 定位低效率执行sql
   可以通过以下两种方式定位执行效率较低的sql语句。

   • 慢查询日志：通过慢查询日志定位那些执行效率较低的sql语句，用–log-show-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的sql语句的日志文件。

   • show processlist：慢查询日志在查询结束后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时的查看sql的执行情况，同时对一些锁表操作进行优化。
     
     

   • id列，用户登录mysql时，系统分配的connection_id，可以使用函数connection_id()查看

   • user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

   • host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

   • db列，显示这个进程目前连接的是哪个数据库

   • command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等

   • time列，显示这个状态持续的时间，单位是秒

   • state列，显示使用当前连接的sql语句的状态，很重要的列，state描述的是语句执行中的某一个状态，一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成

   • info列，显示这个sql语句，是判断问题语句的一个重要依据

3. explain分析执行计划
   通过以上的步骤查询到的效率低的sql语句后，可以通过EXPLAIN或者DESC命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
   查询sql语句执行计划：

explain select * from tb_item where id = 1;

explain select * from tb_item whre title = 'xxxxx';

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序
select_type	表示select的类型，常见的取值有SIMPLE（简单表，即不适用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个select）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为（system–>const–>eq_ref–>ref–>ref_or_null–>index_merge–>index_subquery–>range–>index–>all）
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

3.1 环境准备

create table t_tole (
id varchar(32) not null,
role_name varchar(255) default null,
role_code varchar(255) default null,
description varchar(255) default null,
primary key(id),
unique key unique_role_name (role_name)
) engine-InnoDB default charset=utf8;

create table t_user (
id varchar(32) not null,
username varchar(45) not null,
password varchar(96) not null,
name varchar(45) not null,
primary key(id),
unique key unique_user_name (username)
) engine-InnoDB default charset-utf8;

create table user_role(
id int(11) not null auto_increment,
user_id varchar(32) default null,
role_id varchar(32) default null,
primary key(id),
key fk_ur_user_id (user_id),
key fk_ur_role_id (role_id),
constraint fk_ur_role_id foreign key (role_id) references t_role (id) on delete no action on update no action,
constraint fk_ur_user_id foreign key (user_id) references t_user (id) on delete no action on update no action
) engine=InnoDB default charset=utf8;

3.2 explain的id
id字段是select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序，id情况有三种。
1） id相同表示加载表的顺序是从上到下。

explain select * from t_role r, t_user u, user_tole ur where r.id = ur.role_id and u.id = ur.user_id;

2） id不同，id值越大，优先级越高，越先被执行

explain select * from t_role where id = (select role_id from user_role where user_id = (select id from t_user where username = 'stu1'))

3） id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id值越大，优先级越高，越先执行

explain select * from t_role r, (select * from user_role ur where ur.user_id = '2') a where r.id = a.role_id

3.3 explain 的select_type
表示select的类型，常见的取值如下表：

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在select或where列表中包含了子查询
DERIVED	在from列表中包含了子查询，被标记为DERIVED（衍生）MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个select出现在union之后，则标记为UNION；若UNION包含在from子句的子查询中，外层select将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的select

3.4 explain的table
展示这一行数据是关于那张表的

3.5 explain的type
type显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引、直接返回结果
system	表只有一行记录（等于系统表），这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。const于将主键或唯一索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条，常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独之的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	值检索给定返回的行，使用一个索引来选择行。where之后出现between，< ，>，in等操作
index	index与all的区别为index类型只是遍历了索引树，通常比ALL快，ALL是遍历数据文件
all	将遍历权标以找到匹配的行

结果值从最好到最坏：
null > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
一般来说，我们需要保证查询至少达到range级别，最好达到ref





3.6 explain的key
possible_keys：显示可能应用在这张表的索引，一个或多个
key：实际使用的索引，如果为null，则没有使用索引
key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

3.7 explain的rows
扫描行的数量

3.8 explain的extra
其他的额外的执行计划信息

extra	含义
using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”，效率低
using temporary	使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于order by和group by，效率低
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错

4. show profile分析sql
MySQL从5.0.37版本开始增加了对show profiles和show profile语句的支持。show profiles能够在做sql优化时帮助我们了解时间都耗费到哪里去了。
通过have_profiling参数，能够看到当前MySQL是否支持profile：yes表示支持

默认profiling是关闭的，通过select @@profiling查看profiling是否开启（0表示未开启），可以通过set语句在session级别开启profiling：
set profiling=1; -->开启profiling开关

通过profile，我们能够更清楚地了解sql执行的过程。
首先，我们可以执行一系列的操作，如下图所示：

执行完上述命令，再执行show profiles指令，查看sql语句执行的耗时：

通过show profile for query query_id语句可以查看到该sql执行过程中每个线程的状态和消耗的时间：

注意：sending data状态表示mysql线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端。由于在sending data状态下，mysql线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

在获取到最消耗时间的线程状态后，mysql支持进一步选择all、cpu、block io、context switch、page faults等明细类型来查案mysql在使用什么资源上耗费了过高的时间。例如，选择查看cpu的耗费时间：

查看所有

5. trace分析优化器执行计划
MySQL5.6提供了对sql的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划。
打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;

执行sql语句：

select * from tb_item where id < 4;

最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行sql的：

select * from information_schema.optimizer_trace\G;

三、索引的使用
索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。
3.1 验证索引提成查询效率
表tb_item中有300万条数据

	A. 根据id查询

select * from tb_item where id=1888\G;

根据title查询，title上没有索引，查询比较慢

在title字段上建立索引后重新查询：

3.2 索引的使用
3.2.1 环境准备


3.2.2 避免索引失效
1） 全值匹配，对索引中所有列都指定具体指。
该情况下，索引生效，执行效率高

explain select * from tb_seller where name = '小米科技' and status = '1' and address = '西安市'\G;

看执行计划

2） 最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
匹配最左前缀法则，效率高（where子句中列的顺序和索引中列的顺序不同，索引不会失效）

顺序不同

违反最左前缀法则，索引失效

若包含索引中最左边的列，中间跳过了其它列，如有name和address，那么按照name走索引，若索引列有四列，where子句中包含第二个和第四个或者第二个和第三个，索引不会生效

3） 范围查询右边的列，不能使用索引
根据前面的两个字段name、status查询时走索引的，但是最后一个条件address没有用到索引

4） 不要在索引列上进行运算操作，索引将失效

5） 字符创不加单引号，造成索引失效

6） 尽量使用覆盖索引，避免select *
尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select *。


如果查询列超出索引列，性能也会降低

说明：

• using index：使用覆盖索引的时候就会出现
• using where：在查找使用索引的情况下，需要回表去查询所需的数据
• using index condition：查找使用了索引，但是需要回表查询数据
• using index; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

7） 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么设计的索引都不会被用到
实例，name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的。

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00';

8） 以%开头的like模糊查询，索引失效
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

解决方案，使用覆盖索引（下边例子中sellerid是主键，所以是索引列）

9） 如果MySQL评估使用索引比全表更慢，则不使用索引

数据中12条数据，有11条都是北京市，mysql认为全表扫描比走索引快，所以查询北京市的就不走索引

10） is null 、is not null 有时索引失效
数据中address都不为空，则mysql认为null是少量的，所以is null走索引，is not null不走索引（走全表扫描）


11） in走索引，not in 索引失效

12） 单列索引和复合索引
尽量使用复合索引，而少使用单列索引
创建复合索引
create index idx_name_sta_address on tb_seller(name, status, address);
相当于创建了三个索引name,name+status,name+status+address.

创建单列索引
create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);
数据库会选择一个最优的索引来使用，并不会使用全部索引
使用的索引看数据中索引列辨识度最高的

3.3 查看索引使用情况

show status like 'Handler_read%';

show global status like 'Handler_read%';

• Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）；
• Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）；
• Handler_read_next：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，改值增加；
• Handler_read_prev：按照键顺序读前一行的请求数，该读方法主要用于优化order by … desc；
• Handler_read_rnd：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键，这个值较高，意味着运行效率低，应该建立索引来补救；
• Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的全表扫描，该值较高，通常说明你的表索引不正确或写入的查询没有利用索引。