2-3W字长文警告！！！！
索引概述
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 :
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。
就拿这张图来说，如果查找3这个节点，如果没有索引就需要遍历整张表，但是有了索引，我们只需要遍历三次，然后根据这个节点找到对应的数据。
索引的优劣
优势：
-
类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
-
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势：
-
实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
-
虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
索引的结构
MySQL中的索引
索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：
- BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
- HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
- R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
- Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持
| 索引 | InnoDB引擎 | MyISAM引擎 | Memory引擎 |
|---|---|---|---|
| BTREE索引 | 支持 | 支持 | 支持 |
| HASH 索引 | 不支持 | 不支持 | 支持 |
| R-tree 索引 | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本之后支持 | 支持 | 不支持 |
我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。
2-3树
首先来介绍一种2-3树，这种结构是B树（多路查找树）一种特例。
直接给出定义：2-3树的数字代表一个节点有2到3个子树。它也满足二分搜索树的基本性质，但它不属于二分搜索树。
一颗2-3树或为一颗空树，或有以下节点组成：
- 2-节点，含有一个元素和两个子树（左右子树），左子树所有元素的值均小于它父节点，右子树所有元素的值均大于它父节点；
- 3-节点，还有两个元素和三个子树（左中右子树），左子树所有元素的值均小于它父节点，中子树所有元素的值都位于父节点两个元素之间，右子树所有元素的值均大于它父节点；
- 子树也是空树、2-节点或者3-节点；
- 没有元素相等的节点。
这里有很多都会搞混，2 3 指的是子树的个数，而不是当前节点元素的个数。
这样查找元素的时候，根据元素的大小来决定查找的方向。要判断一个元素是否存在，我们先将待查找元素和根节点比较，如果它和其中任意一个相等，那查找命中；否则根据比较的结果来选择查找的方向。
就比如说查找5这个元素，和根节点比较，然后发现，比根节点小，就到左子树再次比较。然后发现介于4 6之间，再到中子树查找，最后得到这个结果。
这就是简单的2-3树
BTREE 结构
BTree又叫多路平衡搜索树（注意平衡二字），一颗m叉的BTree特性如下：（光看这些特点压根不知道好处在哪）
- 树中每个节点最多包含m个孩子。
- 除根节点与叶子节点外，每个节点至少有[(m/2)]个孩子。
- 若根节点不是叶子节点，则至少有两个孩子。
- 所有的叶子节点都在同一层。
- 每个非叶子节点由n个key与n+1个指针组成，其中[(m/2)-1] <= n <= m-1
以5叉BTree为例，key的数量：公式推导(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). 插入前4个字母 C N G A
2). 插入H，n>4，中间元素G字母向上分裂到新的节点(因为H大于G小于N，这个时候插入G处于中间)
3). 插入E，K，Q不需要分裂
4). 插入M，中间元素M字母向上分裂到父节点G
这里跳过几个插入，假设现在的结构如下：
5). 最后插入S，N P Q R节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂
到此，该BTREE树就已经构建完成了， BTREE树 和 二叉树 相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。
B+TREE结构
B+Tree为BTree的变种，B+Tree与BTree的区别为：
-
n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
-
B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
-
所有的非叶子节点都可以看作是key的索引部分。
由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。
这里需要区分一下，假设是B树，我们找一个节点，每一个节点都会存储对应的这个节点的数据，这个数据。所以这么一来，这个节点再同等大小情况下，B+树可以存放更多的KEY用来检索，这样也可以用来减少树高。
然后就是叶子节点，每个叶子节点都存放数据，并且这个叶子节点会引用下一个节点的地址，这样可以更加方便进行范围查找。
为什么数据库为什么使用B+树而不是B树
判断一种数据结构作为索引的优劣主要是看在查询过程中的磁盘IO渐进复杂度，一个好的索引应该是尽量减少磁盘IO操作次数
- B树只适合随机检索，而B+树同时支持随机检索和顺序检索
- B+树空间利用率更高。因为内部节点不存储数据，只存储索引值，因为相比较B树来说，B+树一个节点可以存储更多的索引值，从而使整颗B+树变得更矮，减少了I/O次数，磁盘读写代价更低，I/O读写次数是影响索引检索效率的最大因素
- B+树的查询效率更加稳定。B树搜索有可能会在非叶子节点阶数，约靠近根节点的记录查找时间越短，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径相同，导致每一个关键字的查询效率相当
- B树在在基于范围查询的操作上的性能没有B+树好，因为B+树的叶子节点使用了指针顺序的链接在一起，只要遍历叶子节点就可以实现整棵树的遍历，相比较B+树来说，由于B树的叶子节点是相互独立的，所以对于范围查询，需要从根节点再次出发查询，增加了磁盘I/O操作次数
- 增删文件（节点）时，效率更高，因为B+树的叶子节点包含了所有关键字，并以有序的链表结构存储，这样可很好提高增删效率
MySQL中的B+Tree
MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针(有木有像双指针循环链表)，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
MySQL中的 B+Tree 索引结构示意图:
MySQL中的索引语法
索引分类
-
单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引
-
唯一索引 ：索引列的值必须唯一，但允许有空值
-
复合索引 ：即一个索引包含多个列
索引语法
索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。
环境的准备：
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');
- 创建索引语法：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)
例如给city这张表中的city_name这个字段创建索引：CREATE INDEX index_name_city ON city(city_name);
- 查看索引语法：
查看所有（整张表的索引）：SHOW INDEX FROM city;
- 删除索引
语法：DROP INDEX index_name_city ON city;
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， 用于全文索引
索引设计的原则
索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。
- 对查询频次较高，且数据量比较大的表建立索引。
- 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
- 使用唯一索引，区分度越高，使用索引的效率越高。
- 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
- 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
- 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。
MySQL存储引擎
体系结构概述
英文看不懂，我也看不明白，来个中文的：
然后你会发现这些生硬的翻译你还是看不懂。。。
整个MySQL Server由以下组成
- Connection Pool : 连接池组件
- Management Services & Utilities : 管理服务和工具组件
- SQL Interface : SQL接口组件
- Parser : 查询分析器组件
- Optimizer : 优化器组件
- Caches & Buffers : 缓冲池组件
- Pluggable Storage Engines : 存储引擎
- File System : 文件系统
整个系统大致分为四层：连接层（Connection Pool），服务层，引擎层，存储层。
连接层
最上面是一些客户端和连接服务，包含本地sock通信和大多数客户端/服务端工具实现的类。类似TCP/IP的通信。主要完成一些类似的连接处理，授权认证相关的安全方案。
在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。
所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。
如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取（读写），服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
存储层
数据存储层， 主要是将数据存储在文件系统之上，并完成与存储引擎的交互。
和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
存储引擎概述
和大多数的数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。
Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。
MySQL5.0支持的存储引擎包含 ： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。
可以通过指定 show engines ， 来查询当前数据库支持的存储引擎：
创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。
查看MySQL数据库默认的存储引擎，指令 ：show variables like '%storage_engine%'；
各种存储引擎特性
| 特点 | InnoDB | MyISAM | MEMORY | MERGE | NDB |
|---|---|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 | 没有 | 有 |
| 事务安全 | 支持 | ||||
| 锁机制 | 行锁(适合高并发) | 表锁 | 表锁 | 表锁 | 行锁 |
| B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | ||||
| 全文索引 | 支持(5.6版本之后) | 支持 | |||
| 集群索引 | 支持 | ||||
| 数据索引 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据可压缩 | 支持 | ||||
| 空间使用 | 高 | 低 | N/A | 低 | 低 |
| 内存使用 | 高 | 低 | 中等 | 低 | 高 |
| 批量插入速度 | 低 | 高 | 高 | 高 | 高 |
| 支持外键 | 支持 |
InnoDB
InnoDB存储引擎是MySQL的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎不同于其他存储引擎的特点 ：
- 提供事务的控制，数据更加的安全可靠。
- 提供外键的约束。
[ PS ]：MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候， 要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。
存储方式
InnoDB 存储表和索引有以下两种方式 ：
-
使用共享表空间存储， 这种方式创建的表的表结构保存在.frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。
-
使用多表空间存储，这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。
MyISAM
MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。有以下两个比较重要的特点：不支持事务
每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是 ：
- .frm (存储表定义)
- .MYD(MYData , 存储数据)
- .MYI(MYIndex , 存储索引)
MEMORY和MEREG
Memory
Memory存储引擎将表的数据存放在内存中。
每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。
MEMORY 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引，但是服务一旦关闭，表中的数据就会丢失。
Mereg
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。
对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。
可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。
存储引擎的选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
以下是几种常用的存储引擎的使用环境：
-
InnoDB：如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
-
MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
-
MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
-
~~MERGE：~~用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。
SQL优化的步骤
在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化。
当面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题。
查看SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。
show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的统计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。
比如查看当前Session所有统计参数的值：
show status like 'Com_______'(后面有7个_)
SHOW STATUS LIKE 'Innodb_rows_%';
我们通常比较关心的是以下几个统计参数：
| 参数 | 含义 |
|---|---|
| Com_select | 执行 select 操作的次数，一次查询只累加 1。 |
| Com_insert | 执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。 |
| Com_update | 执行 UPDATE 操作的次数。 |
| Com_delete | 执行 DELETE 操作的次数。 |
| Innodb_rows_read | select 查询返回的行数。 |
| Innodb_rows_inserted | 执行 INSERT 操作插入的行数。 |
| Innodb_rows_updated | 执行 UPDATE 操作更新的行数。 |
| Innodb_rows_deleted | 执行 DELETE 操作删除的行数。 |
| Connections | 试图连接 MySQL 服务器的次数。 |
| Uptime | 服务器工作时间。 |
| Slow_queries | 慢查询的次数。 |
-
Com_***: 这些参数对于所有存储引擎的表操作都会进行累计。 -
Innodb_***: 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。
定位低效率执行SQL
上面的操作可以查看每种SQL执行的次数，这样方便我们定向的直到哪些地方可以优化。这个定位低效率执行SQL就可以知道是哪些SQL有问题。
可以通过以下两种方式定位执行效率较低的 SQL 语句：
-
慢查询日志 -
show processlist：慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
每一列的意思：
1）id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2）user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3）host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4）db列，显示这个进程目前连接的是哪个数据库
5）command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
6）time列，显示这个状态持续的时间，单位是秒
7）state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8）info列，显示这个sql语句，是判断问题语句的一个重要依据
explain分析执行计划
通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。这样就可以分析为什么这条SQL执行这么慢。
语法：explain + SQL。
举个例子：explain select * from tb_item where id = 1;
发现使用这个关键字之后会显示这条SQL语句一个基本情况，整个表的含义如下：
| 字段 | 含义 |
|---|---|
| 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 | 执行情况的说明和描述 |
数据准备：
CREATE TABLE `t_role` (
`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_username` (`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;
insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');
INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;
explain之id
id 字段是 select 查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况如下：
-
id 相同表示加载表的顺序是从上到下。
例如：
EXPLAIN SELECT * FROM t_role r, t_user u, user_role ur WHERE r.id = ur.role_id AND u.id = ur.user_id;这个就表示，这几张表先执行t_role,然后是t_user，最后才是user_role。
-
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'))子查询会优先执行，所以这条语句执行的顺序就是t_user，user_role，t_role。
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 |
这个例子参考上面的就行了
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
但是一般而言我们需要的几种结果如下：system > const > eq_ref > ref > range > index > ALL
一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。
type为NULL的情况
EXPLAIN SELECT NOW()：查询当前的时间，这个就没有查询任何的表。但是我们也不会这样去用。
type为system的情况
这种情况也是极少出现，哪有一张表只放一条数据的。那还不如不要这表。
例如：EXPLAIN SELECT * FROM (SELECT * FROM t_user WHERE id = '1') a;注意这个效果需要在命令行模式下才可以看到，使用SQLyong这种工具默认不显示子查询后的表的结果。
type为const的情况
这个是在有索引的字段生效，表示通过索引查询冰洁结果只有一条记录，这样的查询速度很快：
SELECT * FROM t_user WHERE id = '1'
type为eq_ref的情况
表示在多表关联查询的时候，使用的主键或者唯一索引查询，并且结果只有一条：
EXPLAIN SELECT * FROM t_user u,t_role r WHERE u.id = r.id;
type为ref的情况
这种就是上面的条件放宽的情况。（注意是非唯一性索引）
例如：EXPLAIN SELECT * FROM t_user WHERE NAME = 't1';
type为range的情况
例如：EXPLAIN SELECT * FROM t_user WHERE id BETWEEN '2' AND '33';
一般来说，走索引的范围查询后都是这个级别。
type为index的情况
查询的数据就是索引，比如说使用id作为索引，那么查询结果集中只有id时候就是这个级别。
例如：EXPLAIN SELECT id FROM t_user;
很好理解，如果id为索引，我查询的就是id这个字段，就没必要再去表中查询这个数据。
type为all的情况
全表遍历，还没有走索引。
其他字段
table
说明这一行数据来自哪张表。
key
- possible_keys : 显示可能应用在这张表的索引， 一个或多个。
- key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
- key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
rows
扫面的行数
extra
其他额外的信息
| extra | 含义 |
|---|---|
| using filesort | 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。 |
| using temporary | 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低 |
| using index | 表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。 |
show profile分析SQL
Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
通过 have_profiling 参数，能够看到当前MySQL是否支持profile：
SELECT @@have_profiling;查看当前数据库支不支持profile
默认profiling是关闭的，可以通过set语句在Session级别开启profiling：set profiling=1;
通过profile，我们能够更清楚地了解SQL执行的过程：show profiles
通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：
[ PS ]: Sending data(对应这张图中的executing) 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整各查询中耗时最长的状态。
在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：
SHOW PROFILE cpu FOR QUERY 153
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;
这个时候就可以看到一长串的玩意。
索引的使用
-- 数据的准备
CREATE TABLE `tb_item` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
`title` varchar(100) NOT NULL COMMENT '商品标题',
`price` decimal(20,2) NOT NULL COMMENT '商品价格，单位为：元',
`num` int(10) NOT NULL COMMENT '库存数量',
`categoryid` bigint(10) NOT NULL COMMENT '所属类目，叶子类目',
`status` varchar(1) DEFAULT NULL COMMENT '商品状态，1-正常，2-下架，3-删除',
`sellerid` varchar(50) DEFAULT NULL COMMENT '商家ID',
`createtime` datetime DEFAULT NULL COMMENT '创建时间',
`updatetime` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品表'
--存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `insert_tb_item`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tb_item`(num INT)
BEGIN
WHILE num <= 1000000 DO
INSERT INTO tb_item VALUES(num,CONCAT('货物',num,'号'),ROUND(RAND() * 100000,2),FLOOR(RAND() * 100000),FLOOR(RAND() * 10),'1','5435343235','2019-04-20 22:37:15','2019-04-20 22:37:15');
SET num = num + 1;
END WHILE;
END$$
DELIMITER ;
--调用存储过程
call insert_tb_item(1)
以上就可以插入100W条数据。
上面呢介绍了这个优化的基本步骤，主要的目的就是定位出了问题的地方，出了什么问题，那么，接下来的玩意就是怎么去解决这些问题。
索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。
索引提升的效率
这一点，可以通过show profiles来查看。
首先我自己数据库准备了13W条数据，虽然有点少，但是也可以对比出效果：
- 首先是走索引的查询
- 然后是不走索引查询
效果可以说是非常的明显了，快的可不止这一点点。
避免索引失效
首先准备测试数据：
create table `tb_seller` (
`sellerid` varchar (100),
`name` varchar (100),
`nickname` varchar (50),
`password` varchar (60),
`status` varchar (1),
`address` varchar (100),
`createtime` datetime,
primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
数据说明，这张表主要创建了两个索引，一个是主键自带索引，然后是一个复合索引。
全值匹配
对索引中所有列都指定具体值。如情况下，索引生效，执行效率高：
EXPLAIN SELECT * FROM tb_seller WHERE NAME = '小米科技' AND STATUS='1' AND address='北京市'
其实符合索引就好比上楼层，就比如这个符合索引，我们建立的顺序就是name->status->address，然后这个过程我们就可以把name当作一层，status就是第二层，address就是第三层。查询的时候只要有这三层都在，自然姐可以非常方便的找到目标。
最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
匹配最左前缀法则，走索引：
注意这个最左指的是只要where这种需要加条件的后面有这三个就算，并不是一定要按照顺序写，当然按顺序写更好，反正底层的优化器会给你优化。
违法最左前缀法则 ， 索引失效：
这个也很好理解，上面提到过这复合索引好比就是走楼梯，第一层你都没梯子了，第二层和第三你上的去嘛，对吧，底层是必须先上了才可能上第二层和第三层嘛。
如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
这一圈起来的，对比只用name作为条件查询的索引大小是一样的。这也侧面证明了没有走address这边。
范围查询右边的列，不能使用索引
根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。
不要在索引列上进行运算操作， 索引将失效。
这个就是查询的时候索引这个列不可以进行运算，因为索引本来就是给你建立好的，你用这个运算得到的结果索引表又没有记录，自然不会走索引：（截取字符串）
字符串不加单引号，造成索引失效
由于在查询是没有对字符串加单引号(status这个字段是varchar类型)，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。
尽量使用覆盖索引，避免select *
这个是什么意思呢，索引本身记录对应的数据，比如这个符合索引，你查询索引，本身就可以得到name,status,address这三个数据，所以，如果查询的字段只有这三个，自然就不用拿着找到的索引指向的地址再去数据库中查询，这是完全没有必要的。
但是如果查询的列不在索引范围，自然还是需要取指定的地方查找对应的数据：
说明，explain最后的这个字段是什么意思：
- using index：使用覆盖索引的时候就会出现
- using where：在查找使用索引的情况下，需要回表去查询所需的数据
- using index condition：查找使用了索引，但是需要回表查询数据
- using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
or 关键字导致失效
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：
发现使用and是可以的。
以%开头的Like模糊查询，索引失效
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
当然这种情况是有办法可以解决的。比如使用索引覆盖：
如果MySQL评估使用索引比全表更慢，则不使用索引。
MySQL是一个优秀的数据库，自己有一套玩意，可以检测你使用索引查询如果比全表扫描查询更慢，自动的会使用全表扫描，非常灵性！
查看数据，你会发现，北京市这个数据非常多，而西安市这个数据只有一个，所以这个时候查询，你用北京市作为条件，走索引每次都会查一次索引然后进行回表取数据，但是西安市只有一条数据，回表操作也就只需要执行一次，如果不走索引，直接全表扫描，还可以省略每次找索引这个步骤，自然这种情况就全表扫描比较高效！
单列索引和复合索引。
尽量使用复合索引，而少使用单列索引 。
创建复合索引
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);
数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。所以更多的时候单列索引只用到了一个，浪费空间。
索引的使用情况
-
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：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
语句的优化
前面说到的索引的使用，差不多就是select语句的优化，接下来会介绍各种优化细节。
insert优化
批量数据导入优化
首先是大笔批量的插入数据，数据最好是排好序（这个排好序指的是按照主键的顺序插入）的，这样插入相对于无序的数据会快很多。
其次可以关闭唯一性的数据检查，既然是大批量的插入数据，大多数时候这个是句都是合法的，所以这个时候擦汗如数据是可以选择关闭这个唯一性的检查的，这样也可以提升插入的效率。在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
最后就是手动的提交事务。如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
语句优化
当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。
-
如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
示例， 原始方式为：（这种方式连接了三次，关闭了三次）
insert into tb_test values(1,'Tom'); insert into tb_test values(2,'Cat'); insert into tb_test values(3,'Jerry');优化后可以这样写：（只连接一次，关闭一次）
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry'); -
在事务中插入数据，这个在批量插入时候也有体现：
start transaction; --开始事务 insert into tb_test values(1,'Tom'); insert into tb_test values(2,'Cat'); insert into tb_test values(3,'Jerry'); commit; --提交事务 -
数据有序的插入，这个也在批量插入中有体现
insert into tb_test values(4,'Tim'); insert into tb_test values(1,'Tom'); insert into tb_test values(3,'Jerry'); insert into tb_test values(5,'Rose'); insert into tb_test values(2,'Cat'); --优化后 insert into tb_test values(1,'Tom'); insert into tb_test values(2,'Cat'); insert into tb_test values(3,'Jerry'); insert into tb_test values(4,'Tim'); insert into tb_test values(5,'Rose');
order by 优化
数据准备：
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
create index idx_emp_age_salary on emp(age,salary);
两种排序方式
这里的排序不是指的按照升序降序这个！！！
-
第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
这个的意思就是如果我们需要根据某一个字段继续排序并且没有添加索引的话，那么使用explain对该SQL进行查询的话就会在Extra中看到filesort。这意味着，MySQL无法根据索引对数据进行排序(如果有索引的话直接取就好了，不需要排序操作)。只好对要排序字段的进行排序了，但是生产环境中数据量可能会非常大，如果全部加载到内存中，必然会引起内存不足进而导致数据库崩溃，因此必须划出一块专门的内存区域以供排序，而这块内存区域很可能装不下这巨大的数据量，必然要借助外部文件系统进行排序，这就是filesort的由来
比如这个例子，age是建立了索引的，但是我们查询的时候，取数据库本身的内容，依然需要对数据经行一个排序。
- 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
其实这个地方在索引提升效率这个地方说过，因为我们所以本身就是表中这些数据的冗余，所以查询的时候如果需要的数据刚好是这几个字段，压根就没必要再去原数据库文件中取。这样就可以提升效率！
多字段排序
多个字段排序的时候需要注意：
排序的时候最好是都按照升序或者降序排序，同时排序的字段最好是按照索引的建立顺序排序！！！
了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同， 并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。
filesort优化
通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort 的排序操作。对于 Filesort，MySQL 有两种排序算法：
-
两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
-
一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。
group by 优化
==由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。==当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：
这里我自己使用的MySQL8.0这个版本，所以没有索引的时候，即使不使用order by nul，优化器也进行了优化。有的版本可能就会出现use filesort
嵌套查询优化
Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。
示例 ，查找有角色的所有的用户信息 :（可以使用连接查询优化）
优化OR条件
对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。
建议使用 union 替换 or ：
来比较下重要指标，发现主要差别是 type 和 ref 这两项
type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
-
UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距
-
UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快
这两项的差距就说明了 UNION 要优于 OR 。
使用SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。
这个例子中就是有一个复合索引，还有一个单值索引，第一种就是默认使用单值，我们就可以改变这种情况，让数据库选择我们指定的索引查询。
如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index。和上面一个相反的功能，让数据库不要使用这个索引：
为强制MySQL使用一个特定的索引，可在查询中使用 force index。（和上面用法一样）
数据库系统层面优化
我们介绍了很多数据库的优化措施。但是在实际生产环境中，由于数据库本身的性能局限，就必须要对前台的应用进行一些优化，来降低数据库的访问压力。
应用优化
使用连接池
对于访问数据库来说，建立连接的代价是比较昂贵的，因为我们频繁的创建关闭连接，是比较耗费资源的，我们有必要建立数据库连接池，以提高访问的性能。
这个常用地数据库连接池一般都是使用阿里地Druid连接池。
减少对MySQL的访问
- 避免对数据进行重复的检测
在编写应用代码时，需要能够理清对数据库的访问逻辑。能够一次连接就获取到结果的，就不用两次连接，这样可以大大减少对数据库无用的重复请求。
比如：我需要id和name这个字段
select id, name from tb_book;
然后后面需要查询别的信息：
select id ,status from tb_book;
这样，就需要向数据库提交两次请求，数据库就要做两次查询操作。其实完全可以用一条SQL语句得到想要的结果。但是这两条SQL是可以合并一次查询得到结果的：
select id, name , status from tb_book;
增加cache
在应用中，我们可以在应用中增加 缓存 层来达到减轻数据库负担的目的。缓存层有很多种，也有很多实现方式，只要能达到降低数据库的负担又能满足应用需求就可以。
因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储， 或者使用框架(Mybatis, Hibernate)提供的一级缓存/二级缓存，或者使用redis数据库来缓存数据 。
负载均衡
负载均衡是应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上， 以此来降低单台服务器的负载，达到优化的效果。
- 利用MySQL 复制分流查询
通过MySQL主从复制，实现读写分离，使增删改操作走主节点，查询操作走从节点，这样就可以降低单台服务器的读写压力。
- 采用分布式数据库架构
分布式数据库架构适合大数据量，负载高的情况，他有良好的拓展性和高可用性。通过在多平台服务器之间分布数据，可以实现多台服务器之间的负载均衡，提升访问效率。
查询缓存优化
开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。
- 客户端发送一条查询给服务器；
- 服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
- 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划；
- MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
- 将结果返回给客户端。
MySQL8版本不支持这个查询缓存，跟多内容可查看别的博主。
内存优化
内存优化的原则
- 将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序留足够的内存。
- MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。
- 排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。
MyISAM内存优化
该存储引擎使用 key_buffer 缓存索引块，加速MyISAM索引的读写速度。对于MyISAM表的数据块，MySQL没有特别的缓存机制，完全依赖于操作系统的IO缓存。
几个参数的配置：
-
key_buffer_size 决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。可以在MySQL参数文件中设置key_buffer_size的值，==对于一般MyISAM数据库，建议至少将1/4可用内存分配给key_buffer_size。==在/usr/myf 中做如下配置：
key_buffer_size=512M -
read_buffer_size，如果需要经常顺序扫描MyISAM表，可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个session独占的，如果默认值设置太大，就会造成内存浪费。
-
read_rnd_buffer_size，对于需要做排序的myisam表的查询，如带有order by子句的sql，适当增加 read_rnd_buffer_size 的值，可以改善此类的sql性能。但需要注意的是 read_rnd_buffer_size 是每个session独占的，如果默认值设置太大，就会造成内存浪费。
InnoDB内存优化
innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。
几个重要的参数：
-
innodb_buffer_pool_size，该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。
innodb_buffer_pool_size=512M -
innodb_log_buffer_size，决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。
innodb_log_buffer_size=10M
并发参数调整
从实现上来说，MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在MySQL中，控制并发连接和线程的主要参数包括 max_connections、back_log、thread_cache_size、table_open_cahce。
max_connections
采用max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。
MySQL 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。
查看命令：show variables like 'max_connections'
back_log
back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ，之后的版本默认为 50 + （max_connections / 5）， 但最大不超过900。
这个东西像极了Java线程池中的任务队列这个玩意。
如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值。
table_open_cahce
该参数用来控制所有SQL语句执行线程可打开表缓存的数量， 而在执行SQL语句时，每一个SQL执行线程至少要打开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定 ：max_connections x N ；
thread_cache_size
为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size 可控制 MySQL 缓存客户服务线程的数量。
innodb_lock_wait_timeout
该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms ， 可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起； 对于后台运行的批量处理程序来说， 可以将行锁的等待时间调大， 以避免发生大的回滚操作。
锁问题
锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。
在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
锁分类
从对数据操作的粒度分 ：
-
表锁：操作时，会锁定整个表。
-
行锁：操作时，会锁定当前操作行。
从对数据操作的类型分：
-
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
-
写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。
MySQL中支持的锁
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：
| 存储引擎 | 表级锁 | 行级锁 | 页面锁 |
|---|---|---|---|
| MyISAM | 支持 | 不支持 | 不支持 |
| InnoDB | 支持 | 支持 | 不支持 |
| MEMORY | 支持 | 不支持 | 不支持 |
| BDB | 支持 | 不支持 | 支持 |
MySQL这3种锁的特性可大致归纳如下 ：
- 表级锁：偏向MyISAM 存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
- 行级锁：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
- 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。
MyISAM 表锁
MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。
如何添加表锁
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
显示加表锁语法：
加读锁 ： lock table table_name read;
加写锁 ： lock table table_name write；
read (读)锁的使用
客户端 一 ：
- 获得tb_book 表的读锁：
lock table tb_book read; - 执行查询操作：
SELECT * FROM tb_book;，可以正常执行 ， 查询出数据。 - 此时客户端二也可以查询出数据
但是如果客户端查询没有加锁的表：
但是客户端二是可以正常查询未加锁的表的。
此时的客户端一是不可以经行更行数据的操作的：
执行插入， 直接报错 ， 由于当前tb_book 获得的是 读锁， 不能执行更新操作。
此时的客户端二插入的时候是会进入阻塞状态的，直到客户端一释放这个读锁(unlock tables)这边就会立即执行。
write(写)锁
使用客户端一进行加写锁：LOCK TABLE tb_book WRITE;
此时客户端一进行查询，更新数据都是可以的。
但是此时的客户端二进行查询会进入阻塞：
当在客户端一中释放锁指令 unlock tables 后 ， 客户端二中的 select 语句 ， 立即执行 ；
锁模式的相互兼容性如下所示：
- 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
- 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。
此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
查看锁的使用情况
命令：show open tables
-
In_user : 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。
-
Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。
查看获取次数：SHOW STATUS LIKE 'Table_locks%';
-
Table_locks_immediate ： 指的是能够立即获得表级锁的次数，每立即获取锁，值加1。
-
Table_locks_waited ： 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。
InnoDB行锁
行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是 采用了行级锁。
这里需要搞明白事务这个东西！！！
Mysql 的数据库的默认隔离级别为 Repeatable read ， 查看方式：SHOW VARIABLES LIKE 'transaction_isolation';
行锁模式
InnoDB 实现了以下两种类型的行锁：
- 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
- 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
对于普通SELECT语句，InnoDB不会加任何锁；
可以通过以下语句显示给记录集加共享锁或排他锁 :
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE
一张图了解整个过程：
这个例子中，首先是需要有两个客户端连接，并且都关闭紫铜提交事务，此时查询是不会受影响的，但是假设客户端1更新数据不提交，此时按照InnoDB这个特性，就会对id为3这个数据加上行锁，所以这个时候如果客户端2也去更新就会处于阻塞状态，等到客户端1提交这个之后就会被执行。
无索引锁升级为表锁
如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。
查看当前表的索引：show index from test_innodb_lock ;
更新的这两条数据是不同的行，由于执行更新时 ，name字段本来为varchar类型， 我们是作为数组类型使用，存在类型转换，索引失效，最终行锁变为表锁。
InnoDB行锁竞争情况
命令如下：
show status like 'innodb_row_lock%';
每个参数的含义：
Innodb_row_lock_current_waits: 当前正在等待锁定的数量Innodb_row_lock_time: 从系统启动到现在锁定总时间长度Innodb_row_lock_time_avg：每次等待所花平均时长Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。
总结
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。
但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。（避免锁升级）
优化建议：
- 尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。
- 合理设计索引，尽量缩小锁的范围
- 尽可能减少索引条件，及索引范围，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可使用低级别事务隔离（但是需要业务层面满足需求）
MySQL中的正则表达式
正则表达式（Regular Expression）是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串。
| 符号 | 含义 |
|---|---|
| ^ | 在字符串开始处进行匹配 |
| $ | 在字符串末尾处进行匹配 |
| . | 匹配任意单个字符, 包括换行符 |
| […] | 匹配出括号内的任意字符 |
| [^…] | 匹配不出括号内的任意字符 |
| a* | 匹配零个或者多个a(包括空串) |
| a+ | 匹配一个或者多个a(不包括空串) |
| a? | 匹配零个或者一个a |
| a1|a2 | 匹配a1或a2 |
| a(m) | 匹配m个a |
| a(m,) | 至少匹配m个a |
| a(m,n) | 匹配m个a 到 n个a |
| a(,n) | 匹配0到n个a |
| (…) | 将模式元素组成单一元素 |
例如：
select * from emp where name regexp '^T'; -- 查询名字中用T开头的
select * from emp where name regexp '2$';
select * from emp where name regexp '[uvw]';
MySQL客户端工具
基本语法格式：
mysql [options] [database]
-- 连接的选项
参数 ：
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器IP或域名
-P, --port=[port] 指定连接端口
示例 ：
mysql -h 127.0.0.1 -P 3306 -u root -p
mysql -h127.0.0.1 -P3306 -uroot -p2143
执行选项：-e, --execute=name，执行SQL语句并退出
此选项可以在MySQL客户端执行SQL语句，而不用连接到MySQL数据库再执行，对于一些批处理脚本，这种方式尤其方便。
例如：mysql -uroot -p[密码] demo_03 -e "select * from tb_book";
mysqladmin
mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。
可以通过 ： mysqladmin –help 指令查看帮助文档
示例 ：
mysqladmin -uroot -p2143 create 'test01'; --创建数据库
mysqladmin -uroot -p2143 drop 'test01'; --删除数据库
mysqlbinlog
由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinlog 日志管理工具。
mysqlbinlog [options] log-files1 log-files2 ...
选项：
-d, --database=name : 指定数据库名称，只列出指定的数据库相关操作。
-o, --offset=# : 忽略掉日志中的前n行命令。
-r,--result-file=name : 将输出的文本格式日志输出到指定文件。
-s, --short-form : 显示简单格式， 省略掉一些信息。
--start-datatime=date1 --stop-datetime=date2 : 指定日期间隔内的所有日志。
--start-position=pos1 --stop-position=pos2 : 指定位置间隔内的所有日志。
mysqldump
mysqldump 客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。
mysqldump [options] db_name [tables]
mysqldump [options] --database/-B db1 [db2 db3...]
mysqldump [options] --all-databases/-A
-- 连接参数选项
参数 ：
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器IP或域名
-P, --port=# 指定连接端口
--输出内容选项
参数：
--add-drop-database 在每个数据库创建语句前加上 Drop database 语句
--add-drop-table 在每个表创建语句前加上 Drop table 语句 , 默认开启 ; 不开启 (--skip-add-drop-table)
-n, --no-create-db 不包含数据库的创建语句
-t, --no-create-info 不包含数据表的创建语句
-d --no-data 不包含数据
-T, --tab=name 自动生成两个文件：一个.sql文件，创建表结构的语句；
一个.txt文件，数据文件，相当于select into outfile
例如：
mysqldump -uroot -p2143 db01 tb_book --add-drop-database --add-drop-table > a
mysqldump -uroot -p2143 -T /tmp test city
MySQL日志
这个部分参考这位博主即可。
更多推荐
MySQL索引,SQL优化一网打尽
发布评论