SQL的必知必会

1. delete 加 limit ?

通常delete操作都会回一个int值，用于告知服务器在控制命令被返回到客户端前被删除的行的最大值
delete后面是支持limit关键字的，但仅支持单个参数，也就是[limit row_count]

2. truncate or delete ?

如果是清空表数据建议直接用truncate，效率上truncate远高于delete。因为truncate不走事务，不会锁表，也不会生产大量日志写入日志文件。
truncate table table_name 后立刻释放磁盘空间，并重置auto_increment的值
delete删除不释放磁盘空间，但后续insert会覆盖在之前删除的数据上

3. SQL语句优化思路

3.1 识别语句性能问题

物理系统资源遇到瓶颈这时候应该是想办法扩容，或者提升硬件设备性能
在硬件资源问题解决之后，需要将注意力转向MYSQL自身

3.1.1 寻找慢SQL

mysql> show full processlist;
+---------+------+---------------------------------------------------------------+------+---------+------+----------+-----------------------+
| Id      | User | Host                                                          | db   | Command | Time | State    | Info                  |
+---------+------+---------------------------------------------------------------+------+---------+------+----------+-----------------------+

在以上列中，INFO中能够看到执行的SQL,Time列中能够看到对应SQL执行的时间,能够直观的看出有哪些SQL执行实现是超过3秒的，进行进一步排查和优化

3.1.2 确认低效查询

通过上一节的操作，能够发现执行较慢的SQL语句，通过反复多次执行该SQL，查看是否每次否执行缓慢，以排查导致其缓慢的因素
1. 运行SQL语句，并记录执行时间使用客户端命令行重复执行SQL,仅限SELECT 语句
1. 生成一个查询执行计划 Query Explain Plan 使用explain语句，进行sql分析

EXPLAIN  select * from xxxx where ss =1 

+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | xxxx | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1478 |   100.00 | NULL  |
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------+

explain 在单表查询的时候，并不真正运行SQL，只是解析当前SQL覆盖的索引，来判定。当SQL涉及嵌套、派生表的时候（select_type = DERIVED），还是需要执行内部的sql查询派生表。此外，还可以根据key和filtered两列，简单判断该SQL是否覆盖索引和扫描的行数。

3.1.3 优化查询

不要胡乱添加索引，并不能够因为SQL执行缓慢，就随意给用到的列添加索引，添加索引也会相应给插入、更新等添加负担和开销。

DDL语句： 数据定义语言，定义了不同数据端、数据库、表、列、索引等数据库对象的定义。create\drop\alter

DML语句： 数据操纵语句，用于添加、删除、更新和查询数据库记录。insert\delete\update\select 等

DCL： 数据控制语句，grant revoke

一定情况下，部分DDL的操作会阻塞并发的DML操作[相关参考说明]https://www.linuxidc.com/Linux/2019-11/161583.htm)

在生产环境，不能够随意执行修改表结构、字段类型、索引等操作，部分DDL操作会阻塞并发DML操作，导致线上其他问题

3.1.4 确认优化

再次执行SQL，检查SQL耗时，通过explain查看SQL是否覆盖key 以及扫描的行数

3.1.5 一些操作流程

添加表索引前，需要确认现在的表结构，确认表大小，来预估添加索引会带来的影响

mysql> show create table xxxx ;

mysql> show table status like 'xxxx';
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name           | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| xxxx | InnoDB |      10 | Dynamic    |  406 |            322 |      131072 |               0 |        32768 |         0 |            497 | 2020-10-15 17:22:35 | 2020-11-02 11:07:23 | NULL       | utf8mb4_unicode_ci | NULL     |                |         |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.11 sec)

4. 基本的分析命令

（1） EXPLAIN

帮助我们了解当前的SQL ，是否覆盖索引、是否扫描过多行、可能覆盖的索引
我们尽量将需要用到的SQL都使用explain进行分析，update delete语句需要重写为select语句后以确保有效使用索引

EXPLAIN PARTITIONS : 查询特定分区提供附加信息
EXPLAIN EXTENDED + show warnings : 能够获取MySQL在执行语句时最终执行的SQL情况，能够进一步了解SQL执行器（也可能不是最终的语句，但具有参考意义），或者排查一些问题（存在索引却没有用到）

（2）SHOW CREATE TABLE

用于了解表结构、列属性、索引等信息
快速导出表结构： mysqldump -u user -p pass –no-data [schema] > schema.sql
INFORMATION_SCHEMA也能查询到相关信息

(3) SHOW INDEXES

mysql> show indexes from dmp_user_group;
+----------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+--------------------+
| Table          | Non_unique | Key_name         | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment      |
+----------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+--------------------+
| xxxx |          0 | PRIMARY          |            1 | id            | A         |         371 | NULL     | NULL   |      | BTREE      |         |                    |
| xxxx |          1 | idx_calculate_id |            1 | calculate_id  | A         |         369 |       32 | NULL   | YES  | BTREE      |         | calculateid索引  |
| xxxx |          1 | idx_business     |            1 | business_code | A         |           8 | NULL     | NULL   | YES  | BTREE      |         | 业务标识索引 |
+----------------+------------+------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+--------------------+
3 rows in set (0.08 sec)

Cardinality - 标识每一列唯一值的数量的估计值

相关索引信息也能够通过INFORMATION_SCHEMA去查询

mysql> select * from INFORMATION_SCHEMA.STATISTICS;

（4） SHOW TABLE STATUS

查看数据库表的底层大小以及表结构，包含存储引擎、版本、数据大小、索引大小、行平均长度、行数

mysql> SHOW TABLE STATUS like 'xxxx';
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name           | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| xxxx | InnoDB |      10 | Dynamic    |  406 |            322 |      131072 |               0 |        32768 |         0 |            497 | 2020-10-15 17:22:35 | 2020-11-02 11:07:23 | NULL       | utf8mb4_unicode_ci | NULL     |                |         |
+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.09 sec)

如果myISAM\memory\archive\backhole描述的行平均长度和行数都是准确值，InnoDB是预估值
也可通过INFORMATION_SCHEMA.TABLES查看详情

(5) SHOW STATUS

查看mysql服务器内部状态信息，了解服务器负载的各种指标

show status;

show global status like '%Created_tmp%';

或者通过INFORMATION_SCHEMA.GLOBAL_STATUS 和 INFORMATION_SCHEMA.SESSION_STATUS

mysql> show session status like '%handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 3     |  
| Handler_read_key      | 162   |  使用162个索引
| Handler_read_last     | 0     |
| Handler_read_next     | 108   |  这个索引被用来读取其他108行数据
| Handler_read_prev     | 0     |  这个索引被用来从反响读取的数据行
| Handler_read_rnd      | 52    |
| Handler_read_rnd_next | 189   |
+-----------------------+-------+
7 rows in set (0.08 sec)

（6） SHOW [ GLOBAL | SESSION ]VARIABLES

用来查看MYSQL的系统变量值

mysql> show session variables like '%tmp_table%';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| max_tmp_tables | 32       |
| tmp_table_size | 16777216 |
+----------------+----------+
2 rows in set (0.09 sec)

可以从INFORMATION_SCHEMA.GLOBAL_VARIABLES 和 INFORMATION_SCHEMA.SESSION_VARIABLES 获取相同的信息

（7） INFORMATION_SCHEMA

SHOW CREATE TABLE 
SHOW INDEXES
SHOW TABLE STATUS 
SHOW STATUS 
SHOW VARIABLES

5. MySQL索引

索引的作用？

保持数据完整性
优化数据访问性能
改进表的连接操作
对结果进行排序
简化聚合数据操作

如何保证数据的完整性

MySQL用主键和唯一键来执行每个表中存储数据的唯一性等级
主键和唯一键有什么区别？

每表只有一个主键，但是可以有多个唯一键
主键必须非null,唯一键可以为null

如何优化数据访问

索引可以让优化器在执行查询的时候不必检索表中所有数据，通过现只需要访问的行数，可以显著提升查询速度。
添加索引不是能够完全的提升SQL查询的性能，还需要结合SQL的执行计划，查看SQL执行的时候是否真正覆盖索引，有时执行器任务全表扫描反而更加高效，就不会利用到索引，也就是通过随机I/O会比通过索引查询有序I/O更加快捷

如何改进表连接

索引的另外作用就是快捷高效的在相关表之间做连接操作，在需要连接的列上使用索引能够显著提升性能

优化结果排序

mysql会将索引存储在一个有序表格中，如果需要select 语句结果有序，就可以适当使用索引，通过order by 关键字对select结果排序。
如果在需要排序的字段上没有索引，mysql一般会对表进行内部排序。如果系统对性能要求高，查询排序请求需求量大，就可以对排序字段添加索引，大大提升性能

聚合操作

索引可以作为一种更方便计算聚合结果的工具。

6.MySQL存储引擎

事务性与非事务性
持久性与非持久性
表和行级锁定
不同的索引方法，例如B-树、B+树、三列以及R-树
聚簇索引和非聚簇索引
主码索引和非主码索引
数据压缩
全文索引能力

（1）存储引擎

可以通过SHOW CREATE TABLE / SHOW TABLE STATUS / INFORMATION_SCHEMA.TABLES都能够查看当前的搜索引擎类型
索引技术：关于不同数据结构如何使用不同的方法访问底层信息的理论，例如：B+数、B-数、R-树以及散列
索引实现：MySQL以及各种存储引擎实现不同的数据结构技术的方法，例如：MyISAM实现的B-树和InnoDB实现的B-树
索引类型：主键、唯一键、非主码索引、全文索引以及空间类型，覆盖索引

MyISAM

非事务性的存储引擎，Mysql5.5版本之前的默认搜索引擎

InnoDB

最流行的事务性存储引擎，Mysql5.5开始的默认搜索引擎

Memory

基于内存的、非事务性、非持久性的存储引擎

其他搜索引擎

ARCHIVE / MERGE / BLACKHOLE / CSV
Federated / ExtraDB / TokuDB / NDB / Maria / InfinDB / Infobright

（2）索引类型

主键、唯一键、不唯一的非主码索引等多种类型的索引，特定存储引擎还支持纯文本和空间索引类型
支持不同的索引类型，面向不同的使用场景，散列实现对直接查找方式提供最优性能，对范围查找效率低下；B-树专门为范围查找设计

B-树(B树)

支持数据插入、控制操作、通过管理一系列树根庄结构的彼此联通的节点来选择
B-树（B树）中包含：索引节点和叶子节点。叶子节点存储数据、索引节点用来存储叶子节点数据的顺序，帮助用户路由到指定数据。相关知识补充

B+树

B+树是B树的增强版本，B+树支持所有B树特性，但是B+树底层数据是根据被提及的索引列进行排序的，B+树还通过叶子节点质检的附加引用来优化扫描。相关知识补充

散列

将一种算法应用到给定值中以在底层数据存储系统中返回一个唯一的指针或位置
优点：使用以线性时间复杂度找到需要读取的行的位置

通信 R-树

R-树数据结构支持基于数据类型对集合数据进行管理。
只有MyISAM 使用R-树支持空间索引

全文本

MyISAM存储引擎支持全文本
MySQL 5.6后InnoDB存储引擎中引入全文本功能

（3）MYSQL实现

MyISAM B-树

MyISAM存储引擎使用B-树数据结构来实现主码索引、唯一索引以及非主码索引。
数据库的索引信息存储在MYI文件中，该文件的块大小是1024字节（可通过myisam-block-size变量配置），MySQL的数据目录和数据库模式子目录中，用户可以找到和每个mysql表对应的.MYD .MYI文件

InnoDB B+树

在其主码索引中使用了B+树，也称聚簇主码
B+树的结构把数据和对应主码组织在一起，叶子节点这一层上添加额外的向前向后指针，这样就可以方便进行范围搜索（Oracle中，这种类型的机构被叫做索引组织的表）
可以通过SHOW TABLE STATUS 确定每个表的大小
用户可以通过innodb_file_per_table定义Innodb为每个表使用单独的表空间
由于InnoDB 使用聚簇主码存储数据，底层数据占用磁盘空间大小取决于页面的填充因子，对于按序排列的主码，InnoDB会使用16K页面的15/16作为填充银子，对于不按序排列的主码，默认情况下，InnoDB会插入初始数据的时候为每个页面分配50%作为填充因子
可以设置innodb_buffer_pool_size 定义InnoDB 的缓存池进行管理
innodb中数据页的存储是有序的，底层数据页内部的数据可以是有序和无序的

InnoDB B-树

Innodb中的非主码索引使用的是B-树结构，MyISAM和InnoDB中的B-树实现并不一样，InnoDB中，非主码索引存储的是主码的实际值，而MyISAM的非主码索引存储的是包含主码值的数据指针。另一个不同点，非主码索引可以包含主键的值，并且可以不是索引必须的部分

内存散列索引

在默认MYSQL引擎索引中，只有Memory引擎支持散列数据结构，散列也是主码索引和非主码索引的默认结构。散列结构的强度可以表示为直接键查找的简单性
适合单值搜索，不适于模糊和范围搜索

内存B-树索引

B-树索引的大小是散列的两倍多

InnoDB 内部单列索引

InnoDB存储引擎在聚簇B+树索引中存储主码，但在内部还是使用内存中的单列表来更高效地进行主码查找，这个机制有InnoDB存储引擎管理

7.MySQL 分区

5.1版本开始支持通过范围、列表、散列、键以及列等对表分区的方式
已分区的表不支持全文本索引、空间索引以及外键索引
分区表上的主索引和唯一索引必须包含分区表达式中用到的所有列
分区可以使表扫描只扫描部分，而不是全部的数据，提高效率

8.创建索引

（1）单列索引

alter table xx add primary key index-name column

alter table xx add [kind]index/key index-name column

主码索引仅能用key, 非主码索引使用index和key均可

利用索引限制查询读取的行数

MySQL并不限制创建索引的数量，相同的索引也能够重复创建，只要名称不同即可
用户并不需要为索引指定名称，MySQL会根据索引所在的狩猎名称自动为索引命名，并在名称后面添加可选的附加信息

使用索引连接表，提升性能

理解索引的基数

当一个查询使用到不止一个索引的时候，mysql会试图找到最高效的索引。通过分析每条索引内部数据分布的统计信息，来判断。
mysql在possible_keys中做出选择，优化器会选择最少扫描行数来选择索引，但是也和许多其他因素相关。某一列区分度越明显越会被优化器选中，区分度较低的有：性别、类型等，一个字段能够覆盖大量的人
一个有着优秀选择性的索引意味着有更少的相同值的行，性别这种区分度低的就是有较差的选择性

使用索引进行模式匹配

如果查找的词是以通配符开头，MySQL不会使用索引，like ‘%test%’。如果经常需要以一个通配符开头的查询，常用的方法是在数据库中保存反序值。 email like ‘%com’不能使用索引，email like reverse(‘%com’)能够使用索引
MySQL不支持基于索引的函数，如果想创建一个带有列函数的索引将会提示语法错误
MySQL使用大小写敏感的字符集存储文本信息

选择唯一的行

唯一索引，能够提供数据完整性以保证在列中任何值都出现一次，告知优化器对给定的记录最多只可能有一行结果返回（很好地避免多余的数据扫描，Handler_read_next=0 不用继续读取下面的数据）
当数据确定是唯一的情况，使用唯一索引是非常好的方式
为空的列也可以定义唯一索引

结果排序

索引可以对查询结果进行排序，如果没有索引，MySQL会使用内部文件排序算法对返回的行按照指定顺序进行排序。
MySQL内部使用sort_buffer对结果进行排序

9.多列索引

索引可以创建在两列或多列上，多列索引也被称为混合索引或连接索引
多列单独的索引可能会带来比较大的消耗，如果又可以合并的索引可以合并使用
MySQL的优化器是基于的开销的，会选择需要读取更少行数的索引来执行，会结合索引基数、有关唯一值的范围和容量的统计信息
优点：优化限制返回的行，索引最左列可以被当做单一列索引
where order by 的字段能够创建多列索引，加快查询

MySQL优化器特性

可以在where order_by group_by上添加索引，但一般只选择一个
第一种索引合并，两个索引取并集，对两个高基数的索引执行OR操作时可能出现(OR)
第二种索引合并，对两个有少量唯一值的索引取交集（AND）
第三种索引合并，与两个索引取并集相似(AND)

10.添加索引的影响

(1)DML的影响

添加索引会影响写操作的性能：删除重复索引，了解索引的使用（show index_statistics 发现低效的索引及时调整）

(2)DDL的影响

随着表大小不断增加，对性能的影响不断增加
InnoDB 5.5之后已经默认设置能够快速创建索引的特性，和以往版本中alter会阻塞其他语句不同
需要考虑对磁盘空间的影响。当执行alter的时候，MySQL会默认创建一份副本，因而如果表200G，就需要200G的磁盘空间用作副本，添加到公共表空间，命令执行完毕后不会被文件系统回收，只有当InnoDB需要额外磁盘空间时在内部被重复利用

(3)磁盘空间影响

对表添加索引后，索引空间使用量会增加，根据备份和恢复的配置，添加索引后对这两个阶段也会产生直接影响
页面填充因子：选择用现实中存在的属性做主码，而不是现实中无意义的编码会对默认页面的填充因子造成直接影响。如果采用默认自增的无意义做主码，页能够使用15/16的容量，如果具有现实意义，InnoDB会通过分裂页来降低数据组织的次数，因而使得使用率一般在50%左右，导致占用较大磁盘空间
对于非主码索引，InnoDB中非主码索引都保存了主码的值（与MyISAM不同），因而主码越长，占用的空间就越大

10. MySQL的限制与不足

(1)基于开销的优化器

MySQL用基于开销的优化器来调整可能的查询树以创建最优的SQL执行路径
MySQL生成的统计信息来辅助优化器的能力有限
支持数量有限的索引提示用于帮助优化器选择合适路径

(2)指定QEP-Query Execution Plan

不支持为指定查询指定QEP

(3)索引的统计信息

支持有限的索引统计信息

(4)基于函数的索引

不支持基于函数的索引
在已有索引上使用函数会导致性能下降

(5)一个表上多个索引

默认只会对一个表使用一个索引