1、MySql简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
Mysq|是开源的，所以你不需要支付额外的费用。
Mysq|支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL使用标准的SQL数据语言形式。
Mysq|可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、 Java、 Perl、 PHP、Eiffel、 Ruby和Tc|等。
Mysq|对PHP有很好的支持，PHP是目前最流行的Web开发语言。
MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
Mysq|是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MysqI系统。
2、安装和使用
1、安装(用rpm安装)
1、下载mysql的repo源
其他方式安装
https://blog.csdn.net/weixin_39715834/article/details/111199906
还可以试试这个rmp包
8.0
```
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm
```
```
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
```
2、安装mysql-community-release-el7-5.noarch.rpm包
```
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
```
查询看是否已经安装过了mysql
```
rpm -aq | grep -i mysql
```
卸载已经安装过的包 yum -y remove [包名]
```
yum -y remove mysql-libs.x86_64
```
3、安装这个包后，
会获得两个mysql的yum repo源：/etc/yum.repos.d/mysql-community.repo，/etc/yum.repos.d/mysql-community-source.repo。安装mysql
```
sudo yum install mysql-server
```
根据步骤安装就可以了，不过安装完成后，没有密码，需要重置密码。
登录时有可能报这样的错：ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock‘ (2)，原因是/var/lib/mysql的访问权限问题。下面的命令把/var/lib/mysql的拥有者改为当前用户：修改权限 ll可以查看用户权限
chown -R mysql:mysql /var/lib/mysql
然后，重启服务：
service mysqld restart
4、修改默认密码（注意shell 环境得用双引号” 不能用单引号’）
```
/usr/bin/mysqladmin -u root password 123456
```
mysql -uroot -p /直接回车如果登录不了就去 / vi /etc/my.cnf /在[mysqld]下面加上一句：skip-grant-tables 保存并且退出vi。/ /重启数据库服务/ service mysqld restart /然后再登录数据库/ mysql -uroot -p 直接回车登录 use mysql; update user set password=password(“123456”) where user=“root“; exit;退出还可以使用这个命令修改看看 alter user ‘root’@’localhost’ identified with mysql_native_password by ‘password’
5、修改数据库端口号
然后，重启服务：
vi /etc/my.cnf 打开my.cnf文件然后加入数据 [mysqld] port=3307 保存退出重启服务器 service mysqld restart
6、修改数据库暴露给远程连接
mysql> use mysql; (此DB存放MySQL的各种配置信息) Database changed mysql> select host,user from user; (查看用户的权限情况) mysql> select host, user, password from user;

host	user	password
localhost	root	*4ACFE3202A5FF5CF467898FC58AAB1D615029441
127.0.0.1	root	*4ACFE3202A5FF5CF467898FC58AAB1D615029441
localhost

由此可以看出，只能以localhost的主机方式访问。

解决方法：
mysql> Grant all privileges on . to “root”@”%” identified by ‘数据库密码’ with grant option;
如果以上语句修改不了，就用sql语句直接更新
update user set host=’%’ where user = ‘root’;
(%表示是所有的外部机器，如果指定某一台机，就将%改为相应的机器名；‘root’则是指要使用的用户名，)
mysql> flush privileges; (运行此句才生效，或者重启MySQL) Query OK, 0 rows affected (0.03 sec)
再次查看。。
mysql> select host, user, password from user;

host	user	password
localhost	root	*4ACFE3202A5FF5CF467898FC58AAB1D615029441
127.0.0.1	root	*4ACFE3202A5FF5CF467898FC58AAB1D615029441
localhost
%	root	*4ACFE3202A5FF5CF467898FC58AAB1D615029441

7、开机启动

以上安装完是自动开机启动的

8、相关配置文件默认目录

可以使用ps -ef | grep mysql 查看相关目录在进程中的目录

9、安装过早期版本mysql无法再次安装最新版本的解决方案

https://blog.csdn.net/a719953132/article/details/108935114
核心关键是把之前的文件删掉

cd /var/lib/mysql

移走里面的ib_logfile0 ib_logfile1

2、修改默认配置文件编码

linux:

注意：数据库的版本要 5.5.3+ 才可以支持utf8mb4编码

改数据库配置（我的是linux的、windows的自己百度）
重启数据库
修改字段的编码
重新测试

找到配置（路径：/etc/my.cnf 、windows的是：my.ini 、mac 下默认没有自己创一个文件/etc/my.cnf 导入下面配置重启可生效）

[mysql]
default-character-set=utf8mb4 
[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='SET NAMES utf8mb4'

重启数据库

service mysqld restart service mysql restart (5.5.7版本命令)

修改字段的编码

二进制日志log-bin：主从复制
错误日志log-error:默认是关闭的，记录严重的警告和错误信息,每次启动和关闭的详细信息等
查询日志log:默认关闭，记录查询的sql语句，如果开启会减低mysqI的整体性能，因为记录日志也是需要消耗系统资源的

3、查看字符集

mysql> show variables like '%char%'
    -> ;
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

3、架构

分四层架构
- 连接层
- 优化解析处理层
- 存储引擎层
- 文件层

Optimizer：优化器
SQL interface:接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户。
Parser：首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理。
Management Services：提供对MySQL的集成管理，如备份(Backup),恢复(Recovery),安全管理(Security)等
Caches & Buffers:缓存和缓冲组件

存储引擎：

Pluggable Storage Engines 存储引擎：分层可拔插式

4、存储引擎

1、查看数据库支持的引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

2、默认存储引擎

mysql> show variables like '%storage_engine%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+
3 rows in set (0.00 sec)

3、引擎对比

4、阿里等用的引擎

5、SQL优化分析

1、性能下降SQL慢执行时间长，等待时间长

查询语句写的烂
索引失效
- 单值索引
  - create index idx_user_name on user(user_name)
- 复合索引
  - create index idx_user_name_id on user(user_name,id)
关联查询太多join（设计缺陷或不得已的需求）
服务器调优及各个参数设置（缓冲，线程数等）

2、通用常见的Join查询

1、SQL执行顺序
- 手写
- 机读：先从from开始
- 总结
- 2、Join图
  1、INNER JOIN（内部连接）
```
select * from TableA a inner join TableB b on a.key = b.key
```

2、LEFT JOIN（左连接）

select * from TableA a left join TableB b on a.key = b.key

3、RIGHT JOIN （右连接）

select * from TableA a right join TableB b on a.key = b.key

4、left join 和b.key is null

select * from TableA a left join TableB b on a.key=b.key where b.key is null

5、right join 和 a.key is null

select * from TableA a right join TableB b on a.key=b.key where a.key is null

6、outer join(全连接)

mysql 不支持outer join
oracle支持

oracle：

select * from tableA a outer join tableb b on a.key=b.key

mysql：union（合并去重）

select * from tableA a left join TableB b on a.key=b.key
union
select * from tableA a right join TableB b on a.key=b.key

7、outer join 中间不要

oracle:

select * from tableA a outer join tableb b on a.key=b.key where a.key is null or b.key is null

mysql:

select * from tableA a left join TableB b on a.key=b.key where b.key is null
union
select * from tableA a right join TableB b on a.key=b.key where a.key is null

3、笛卡尔乘积

表示两个
```
select * from tableA,tableB
```
4、索引

1、简介
MySQL官方对索引的定义为:索引( Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。
可以简单理解为，排好序的快速查找数据结构
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
- 下图就是一种可能的索引方式示例:
- 左边是数据表，一共有丙列七条記彖，最左边的是数据记录的物理地址
  - カ了加快Col2的査找，可以维护一个右边所示的ニ叉査找数，毎个节点分別包含索引键値和一个指向対座数据记录物理地址的指针，这样就可以送用二叉査找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
我们平常所说的索引，如果没有特别指明：都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+tree树索引，统称为索引。

2、优势
类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

3、劣势
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE 和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引优化查询

4、索引分类
单值索引
- 即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引
- 索引列的值必须唯一，但允许有空值
  - 比如我们的主键
复合索引
- 即一个索引包含多个列
  
  5、基本语法
- 创建
  - create [unique]index indexName on mytable(columnname(lenth))
  - alter table mytablename add [quique]index indexName (columnname(lenth))
- 查看
  - show index from mytable
- 删除
  - drop index [indexName] on mytable
- alert四种方式添加数据库表的索引
  - ALTER TABLE tblname ADD PRIMARY KEY (coltmn list). 该语句添加一一个主键，这意味着索引值必须是唯一的，且不能为NULL。
  - ALTER TABLE tblname ADD UNIQUE index name (column_ list): 这条语句创建索引的值必须是唯一的 (除了NULL外，NULL可能会出现多次)。
  - ALTER TABLE tblname ADD INDEX index name (column_ list) 添加普通索引，索引值可出现多次。
  - ALTER TABLE tblname ADD FULLTEXT index name (column list)该语句指定了索引为FULLTEXT，用于全文索引。
    6、mysql索引结构
    1、BTree索引
    2、Hash索引
    3、full-text全文索引
    4、R-Tree索引
    7、哪些情况需要创建索引
1.主键自动建立唯一索引2.频繁作为查询条件的字段应该创建索引
3.查询中与其它表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引因为每次更新不单单是更新了记录还会更新索引，加重了I0负担
1. Where条件里用不到的字段不创建索引
6.单键/组合索引的选择问题，who? (在高并发下倾向创建组合索引)
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组字段

8、哪些情况不要创建索引
1.表记录太少
2.经常增删改的表
- Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一-下索引文件
3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
- 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那公对这种表A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。
  5、性能分析
  1、Mysql Query Optimizer
1 Mysq|中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划(他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)
2当客户端向MySQL请求-条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQLQuery Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算,直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

2、MySQL常见瓶颈
CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
I0:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈:top,free,iostat和vmstat来查看系统的性能状态

3、Explain

1、简介
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是是什么(查看执行计划)如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

2、作用
表的读取顺序数据读取操作的操作类型
哪些索引可以使用
一哪些索引被实际使用
表之间的引用.
每张表有多少行被优化器查询

3、如何使用
Explain + SQL语句
执行计划包含的信息 ```shell mysql> explain select * from user where id=1; +——+——————-+———-+———+———————-+———+————-+———+———+——————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +——+——————-+———-+———+———————-+———+————-+———+———+——————-+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 3 | Using where | +——+——————-+———-+———+———————-+———+————-+———+———+——————-+ 1 row in set (0.00 sec)

<a name="oIrBM"></a>
#### 4、explain中名词释义：id

   - select查询的序列号,包含- - 组数字，表示查询中执行select子句或操作表的顺序
   - 三种情况：
      - id相同，执行顺序由上至下
         - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629354006285-58f90d42-eab0-4da5-97c3-e9de8c9da93a.png#clientId=u4d18e92f-8a40-4&from=paste&height=137&id=u44076967&margin=%5Bobject%20Object%5D&name=image.png&originHeight=274&originWidth=1260&originalType=binary&ratio=1&size=177975&status=done&style=none&taskId=u963e605f-e416-462c-a4c0-b9cef7766dd&width=630)    
      - id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
         - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629354041832-9917cff6-af1d-4496-a81a-4c81879c21b3.png#clientId=u4d18e92f-8a40-4&from=paste&height=168&id=u665f6520&margin=%5Bobject%20Object%5D&name=image.png&originHeight=335&originWidth=1147&originalType=binary&ratio=1&size=123226&status=done&style=none&taskId=u92f78c10-54ae-4d9c-bed9-c53333f24a2&width=573.5)
      - id相同不同，同时存在
         - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629354345626-64ca0155-56fd-4974-977b-e5fef5e0229d.png#clientId=u4d18e92f-8a40-4&from=paste&height=146&id=u8dfd3fb7&margin=%5Bobject%20Object%5D&name=image.png&originHeight=292&originWidth=1218&originalType=binary&ratio=1&size=101003&status=done&style=none&taskId=u96338fe8-9249-420c-a4b3-88f715911ef&width=609)
         - id如果相同，可以认为是一组，从上往下循序执行
         - 在所有组中，id值越大，优先级越高，越先执行
         - 衍生=DERIVED
<a name="HcyHV"></a>
#### 5、explain中名词释义：select_type

- 查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
- 1. SIMPLE:
   - 简单的select查询,查询中不包含子查询或者UNION
- 2. PRIMARY:
   - 查询中若包含任何复杂的子部分，最外层查询则被标记为
- 3. SUBQUERY
   - 在SELECT或WHERE列表中包含了子查询
- 4. DERIVED:
   - 在FROM列表中包含的子查询被标记为DERIVED(衍生)
   - MySQL会递归执行这些子查询，把结果放在临时表里。
- 5. UNION:
   - 若第二个SELECT出现在UNION之后，则被标记为UNION;
   - 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为: DERIVED
- 6. UNION RESULT
   - 从UNION表获取结果的SELECT
<a name="Qs5uY"></a>
#### 6、explain中名词释义：table
显示这一行数据是关于哪一张表
<a name="d1eIQ"></a>
#### 7、explain中名词释义：type
<a name="jQQRb"></a>
##### 1、简介

- ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629355558035-039c630c-ba4c-4513-a916-8f509ba04b39.png#clientId=u4d18e92f-8a40-4&from=paste&height=34&id=u4ae16c53&margin=%5Bobject%20Object%5D&name=image.png&originHeight=68&originWidth=942&originalType=binary&ratio=1&size=56403&status=done&style=none&taskId=uc48f66d0-0fa7-471b-90bc-09fa38c107b&width=471)
- 访问类型排列
- 显示查询使用了何种类型
   - 从最好到最差依次是
      - system>const>eq_ref>ref>range>index>all
<a name="P4Raz"></a>
##### 2、各参数解析

- system
   - 表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计
- const
   - 表示通过索引- .次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据， 所以很快
   - 如将主键置于where列表中，MySQL 就能将该查询转换为-一个常量
- eq_ref
   - 唯一性索引扫描，对于每个索引键，表中只有条记录 与之匹配。常见于 主键或唯一索引扫描
- ref
   - 非唯-性索引扫描，返回匹配某个单独值的所有行.
   - 本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体 
- range
   - 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
   - 一般就是在你的where语句中出现了between、<、>、in等的查询
   - 这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点， 而结束语另一点，不用扫描全部索引。
- index
   - Full Index Scan, index 与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
   - (也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的)
- all
   - Full Table Scan,将遍历全表以找到匹配的行

备注:<br />一般来说，得保证查询至少达到range级别， 最好能达到ref。
<a name="jyApw"></a>
#### 7、explain中名词释义：possible_keys

- 显示可能应用在这张表中的索引，一“个或多个。
- 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
<a name="QqrEz"></a>
#### 8、explain中名词释义：key

- 实际使用的索引。如果为NULL，则没有使用索引
- 查询中若使用了覆盖索引，则该索引和查询的select字段重叠口
- ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629358500651-40b5384a-2b0f-4f23-97be-661611f2b39e.png#clientId=u4d18e92f-8a40-4&from=paste&height=170&id=ExATc&margin=%5Bobject%20Object%5D&name=image.png&originHeight=340&originWidth=1213&originalType=binary&ratio=1&size=114545&status=done&style=none&taskId=u1790828b-1e06-4952-81a1-591ce370a88&width=606.5)
<a name="GgNNn"></a>
#### 9、explain中名词释义：key_len

- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
- key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的团
<a name="OlItX"></a>
#### 10、explain中名词释义：ref

- 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
   - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629359056989-1c02ae0b-448a-45d4-945d-875b34edd768.png#clientId=u4d18e92f-8a40-4&from=paste&height=215&id=uf4c29311&margin=%5Bobject%20Object%5D&name=image.png&originHeight=429&originWidth=1308&originalType=binary&ratio=1&size=256762&status=done&style=none&taskId=u2a582d40-6c5a-4263-b6ef-dc7b828166a&width=654)
<a name="kc5Ph"></a>
#### 11、explain中名词释义：rows

- 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
- ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629359602305-65313530-0aa7-46bc-8537-486d11fdf60d.png#clientId=u4d18e92f-8a40-4&from=paste&height=246&id=ubf2c8a21&margin=%5Bobject%20Object%5D&name=image.png&originHeight=491&originWidth=1245&originalType=binary&ratio=1&size=262682&status=done&style=none&taskId=uc098c761-59dc-4736-a71e-0023aa3a45f&width=622.5)
<a name="Qal6u"></a>
#### 12、explain中名词释义：Extra

- 包含不适合在其他列中显示但十分重要的额外信息
- 1. Using filesort
   - 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
   - MySQL中无法利用索引完成的排序操作称为“文件排序”
   - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629360167941-ec3b65b8-a16f-4fd2-bef3-0006a99edde2.png#clientId=u4d18e92f-8a40-4&from=paste&height=326&id=u0969c395&margin=%5Bobject%20Object%5D&name=image.png&originHeight=651&originWidth=850&originalType=binary&ratio=1&size=154955&status=done&style=none&taskId=uf5ee89a1-47b6-4c48-9d4e-3f8e97ad865&width=425)
- 2. Using temporary 
   - 使了用临时表保存中间结果MySQL在对查询结果排序时使用临时表。常见于排序orderby和分组查询groupby。
   - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629360527856-d7b52930-ee8e-4711-9d81-37160159d7fa.png#clientId=u4d18e92f-8a40-4&from=paste&height=323&id=uc7de05dc&margin=%5Bobject%20Object%5D&name=image.png&originHeight=645&originWidth=942&originalType=binary&ratio=1&size=161924&status=done&style=none&taskId=u92db6165-5b83-41ca-87fb-a33e191721e&width=471)
- 3. USING index
   - 表示相应的select操作中使用了覆盖索引(Covering Index)， 避免访问了表的数据行，效率不错!
   - 如果同时出现using where,表明索引被用来执行索引键值的查找;
   - 如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
   - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629363353980-6f259a86-563f-42df-88b5-7467210c3f92.png#clientId=u4d18e92f-8a40-4&from=paste&height=263&id=u9b14da17&margin=%5Bobject%20Object%5D&name=image.png&originHeight=526&originWidth=1197&originalType=binary&ratio=1&size=321862&status=done&style=none&taskId=u9fa72d3d-4029-4cf1-8335-022d386cdfc&width=598.5)
   - 覆盖索引(Covering Index)
      - 覆盖索引(Covering Index) ,一 说为索引覆盖。理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。理解方式二:索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竞索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
      - 注意:
      - 如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *: |因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
- 4. Using where
   - 表明使用了where过滤
- 5. using join buffer
   - 使用了连接缓存:
- 6. impossible where 
   - where子句的值总是false,不能用来获取任何元组
   - ![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629364139695-03d7785e-2d90-41ca-945e-093f31cd4824.png#clientId=u4d18e92f-8a40-4&from=paste&height=82&id=ubf1742c5&margin=%5Bobject%20Object%5D&name=image.png&originHeight=164&originWidth=1158&originalType=binary&ratio=1&size=150720&status=done&style=none&taskId=u7fed0a46-6cd4-4b0b-a6d7-b00bd50a1a9&width=579)
- 7. select tables optimized away
   - 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者
   - 对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，
   - 查询执行计划生成的阶段即完成优化。
- 8. distinct
   - 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
<a name="j3Ye6"></a>
#### 13、热身Case
![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629364635794-52bc973e-3cd6-4adc-93e1-af87c36e6ff7.png#clientId=u4d18e92f-8a40-4&from=paste&height=158&id=ub285822e&margin=%5Bobject%20Object%5D&name=image.png&originHeight=316&originWidth=1240&originalType=binary&ratio=1&size=219403&status=done&style=none&taskId=ub4d4d2c2-bccd-4975-b879-d2a44187bd3&width=620)<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/12971636/1629364622411-ebde44c8-6033-497f-ab25-7ce3ce44372b.png#clientId=u4d18e92f-8a40-4&from=paste&height=209&id=u6beff6bf&margin=%5Bobject%20Object%5D&name=image.png&originHeight=417&originWidth=1673&originalType=binary&ratio=1&size=841545&status=done&style=none&taskId=u940abbd5-160f-4637-8549-c238519fa68&width=836.5)
<a name="mO7Xs"></a>
## 6、索引优化
<a name="aZsZw"></a>
### 1、案例1
<a name="mZ1pZ"></a>
#### 1、创建表
```sql
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3');

SELECT * FROM ARTICLE;

2、查询

EXPLAIN SELECT id,author_id from article WHERE category_id = 1 and comments > 1 ORDER BY views LIMIT 1;

索引应该建立在 category_id和views的复合索引，因为comments是查范围的，会导致复合索引view的的失效。

2、案例二

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));

INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

A left join B 索引建立再B表效率更高
A right join B索引建立在A表效率更高
- 原则是索引在不查更多数据的表上性能会高一些

下面开始explain分析

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                       |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

结论: type 有All
添加索引优化
```
ALTER TABLE book ADD INDEXY ( 'card );
```

第2次explain

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------------+------+---------------+---------------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL          | NULL    | NULL         |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | ref  | idx_book_card | idx_book_card | 4       | learn.c.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

可以看到第二行的type变为了ref,rows也变成了优化比较明显。
这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，
所以右边是我们的关键点,一定需要建立索引。

3、案例三

1、在案例二的基础上再添加一张表

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

2、优化

ALTER TABLE 'phone' ADD INDEXz( ' card'); 
ALTER TABLE 'book' ADD INDEX Y ( 'card' );

分析

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card;
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL           | NULL           | NULL    | NULL             |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | idx_book_card  | idx_book_card  | 4       | learn.class.card |    1 |   100.00 | Using index |
|  1 | SIMPLE      | phone | NULL       | ref  | idx_phone_card | idx_phone_card | 4       | learn.book.card  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

后2行的type都是ref且总rows优化很好,效果不错。因此索引最好设置在需要经常查询的字段中。

4、结论

Join语句的优化

尽可能减少Join语句中的NestedLoop的循环总次数：
永远用小结果集驱动打的结果集
优先优化NedtedLoop的内层循环；
保证Join语句中被驱动表上Join条件字段已经被索引
当无法保证被驱动表的Join条件字段被索引且内层资源充足的前提下，不要太吝啬JoinBuffer的设置。
7、索引失效（应该避免）
1、建表
``sql CREATE TABLE staffs( id INT PRIMARY KEY AUTO_INCREMENT,nameVARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',ageINT NOT NULL DEFAULT 0 COMMENT'年龄',posVARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',add_timeTIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间' )CHARSET utf8 COMMENT'员工记录表'; INSERT INTO staffs(name,age,pos,add_time) VALUES('z3',22,'manager',NOW()); INSERT INTO staffs(name,age,pos,add_time) VALUES('July',23,'dev',NOW()); INSERT INTO staffs(name,age,pos,add_time`) VALUES(‘2000’,23,’dev’,NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

<a name="UQsJm"></a>
### 2、案例（索引失效）
```sql
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

（1）全值匹配我最爱

使用索引 ```sql mysql> explain select * from staffs where name=’z3’; +——+——————-+————+——————+———+————————————-+————————————-+————-+———-+———+—————+———-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +——+——————-+————+——————+———+————————————-+————————————-+————-+———-+———+—————+———-+ | 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 3 | 100.00 | NULL | +——+——————-+————+——————+———+————————————-+————————————-+————-+———-+———+—————+———-+ 1 row in set, 1 warning (0.01 sec)


<a name="BxPoI"></a>
#### （2）最佳左前缀法则

- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
- 创建的复合索引，带头字段不能丢，中间字段不能断。
- 失效了的sql
```sql
mysql> explain select * from staffs where pos = 'dev' and age = 23;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where  age = 22 and pos = 'dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

（3）不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描

mysql> explain select * from staffs where left(name,4) = 'zs';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

（4）存储引擎不能使用索引中范围条件右边的列

mysql> explain select * from staffs where  age > 22 and pos = 'dev' and name = 'z3';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | NULL |    1 |    11.11 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

（5）尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select*

mysql> explain select age,pos,name from staffs where name='zs' and  age = 25 and pos = 'dev';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

（6）mysql在使用不等于(!=或者<> )的时候无法使用索引会导致全表扫描

mysql> explain select * from staffs where name !='zs';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    9 |   100.00 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select age,pos,name from staffs where name !='zs';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | index | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | NULL |    9 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

（7）is null ,is not null也无法使用索引

Impossible WHERE 表示这种情况不存在 ```sql mysql> explain select * from staffs where name is null; +——+——————-+———-+——————+———+———————-+———+————-+———+———+—————+—————————+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +——+——————-+———-+——————+———+———————-+———+————-+———+———+—————+—————————+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +——+——————-+———-+——————+———+———————-+———+————-+———+———+—————+—————————+ 1 row in set, 1 warning (0.00 sec)

<a name="loql2"></a>
#### （8）like以通配符开头(‘%b...')mysq|索引失效会变成全表扫描的操作

- %最好在右边，左边和两边最好不要加
```sql
mysql> explain select * from staffs where name like '%zs%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name like '%zs';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name like 'zs%';
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

问题:解决like’%字符串%”时索引不被使用的方法
- 很简单用覆盖索引，
- 表的索引 ```sql mysql> show index from staffs; +————+——————+————————————-+———————+——————-+—————-+——————-+—————+————+———+——————+————-+———————-+————-+——————+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +————+——————+————————————-+———————+——————-+—————-+——————-+—————+————+———+——————+————-+———————-+————-+——————+ | staffs | 0 | PRIMARY | 1 | id | A | 9 | NULL | NULL | | BTREE | | | YES | NULL | | staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | | staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | | staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | | YES | NULL | +————+——————+————————————-+———————+——————-+—————-+——————-+—————+————+———+——————+————-+———————-+————-+——————+ 4 rows in set (0.01 sec)


   - 使用覆盖索引查询出来匹配出来like，
```sql
mysql> explain select id from staffs where name like '%zs%';
+----+-------------+--------+------------+-------+---------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key                     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | index | NULL          | index_staffs_nameAgePos | 140     | NULL |    9 |    11.11 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name,pos,id from staffs where name like '%zs%';
+----+-------------+--------+------------+-------+---------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key                     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | index | NULL          | index_staffs_nameAgePos | 140     | NULL |    9 |    11.11 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

但是只要你一用select * ,或者字段里加了不相关的数据肯定就失效了 ```sql mysql> explain select name,pos,id,add_time from staffs where name like ‘%zs%’; +——+——————-+————+——————+———+———————-+———+————-+———+———+—————+——————-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +——+——————-+————+——————+———+———————-+———+————-+———+———+—————+——————-+ | 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 11.11 | Using where | +——+——————-+————+——————+———+———————-+———+————-+———+———+—————+——————-+ 1 row in set, 1 warning (0.00 sec)


   - 综合出来如果我们想查这个表中的所有字段，就可以先查询出id，然后再查所有字段，通过一个子查询
```sql
mysql> explain select * from staffs where id in (select id from staffs where name like '%zs%');
+----+-------------+--------+------------+--------+---------------+-------------------------+---------+-----------------+------+----------+--------------------------+
| id | select_type | table  | partitions | type   | possible_keys | key                     | key_len | ref             | rows | filtered | Extra                    |
+----+-------------+--------+------------+--------+---------------+-------------------------+---------+-----------------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | index  | PRIMARY       | index_staffs_nameAgePos | 140     | NULL            |    9 |    11.11 | Using where; Using index |
|  1 | SIMPLE      | staffs | NULL       | eq_ref | PRIMARY       | PRIMARY                 | 4       | learn.staffs.id |    1 |   100.00 | NULL                     |
+----+-------------+--------+------------+--------+---------------+-------------------------+---------+-----------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

（9）字符串不加单引号索引失效

mysql> explain select * from staffs where name = 1;
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    9 |    11.11 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
mysql> explain select * from staffs where name = '1';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

（10）少用or,用它来连接时会索引失效

mysql> explain select * from staffs where name = 'zs' or age = 23;
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    9 |    20.99 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

（11）小总结

8、建议

group by 基本上都需要进行排序，可能会有临时表产生
对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

1、MySql高级优化

1、MySql简介

2、安装和使用

1、安装(用rpm安装)

1、下载mysql的repo源

2、安装mysql-community-release-el7-5.noarch.rpm包

3、安装这个包后，

4、修改默认密码（注意shell 环境得用双引号” 不能用单引号’）

5、修改数据库端口号

6、修改数据库暴露给远程连接

由此可以看出，只能以localhost的主机方式访问。

7、开机启动

8、相关配置文件默认目录

9、安装过早期版本mysql无法再次安装最新版本的解决方案

2、修改默认配置文件编码

3、查看字符集

3、架构

4、存储引擎

1、查看数据库支持的引擎

2、默认存储引擎

3、引擎对比

4、阿里等用的引擎

5、SQL优化分析

1、性能下降SQL慢执行时间长，等待时间长

2、通用常见的Join查询

1、SQL执行顺序

2、Join图

1、INNER JOIN（内部连接）

2、LEFT JOIN（左连接）

3、RIGHT JOIN （右连接）

4、left join 和b.key is null

5、right join 和 a.key is null

6、outer join(全连接)

7、outer join 中间不要

3、笛卡尔乘积

4、索引

1、简介

2、优势

3、劣势

4、索引分类

5、基本语法

6、mysql索引结构

1、BTree索引

2、Hash索引

3、full-text全文索引

4、R-Tree索引

7、哪些情况需要创建索引

8、哪些情况不要创建索引

5、性能分析

1、Mysql Query Optimizer

2、MySQL常见瓶颈

3、Explain