插入多条数据时建议使用values批量插入 提交事务建议手动提交，否则执行一次sql语句就自动开启提交事务会影响性能 主键插入时顺序插入效率大于乱序插入 大批量插入数据 如果一次性需要插入大批量数据，使用insert语句插入性能较低，可以将本地文件加载如SQL数据库，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：

#客户端连接服务端时，除了加上-u指定用户，-p指定密码还需要加上参数--local-infile
--local-infile：当前客户端连接服务端时，需要加载本地文件
mysql --local-infile -u root -p
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global localinfile =1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile 'path' into table 'tb_user' fields terminated by ',' lines terminated by '\n'；

path加载文件的路径 tb_user加载入的表名 ，分格数据的符号，本例以csv文件为例 \n每一行数据使用什么分隔，大多为\n（回车）

主键优化

如果主键是顺序插入，一个页的数据满了就会自动顺序的增加，如果乱序插入就可能导致页分裂

• 页分裂：页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据多大，会行溢出)，根据主键排列。
• 页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。 当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。 MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则 满足业务需求的情况下，尽量降低主键的长度。 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。 业务操作时应尽量避免主键的修改 UUID是不重复但是无序的字符串

order by优化

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。 Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

#创建索引
create index idx_user_age_phone _aa on tb_user(age,phone);
#创建索引后，根据age，phone进行升序排序
explain select id,age,phone from tb_user order by age, phone;
#创建索引后，根据age,phone进行降序排序
explain select id,age,phone from tb_user order by age desc, phone desc ;

group by优化

在分组操作时，可以通过建立对应字段索引来提高效率， 分组操作时，索引的使用也是满足最左前缀法则的

limit优化

limit2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000－2000010的记录，其他记录丢弃，查询排序的代价非常大。优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 200ooo0,10) a wheret.id = a.id;

count优化

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count( * ）的时候会直接返回这个数，效率很高（仅限于后面没有where条件的情况） InnoDB引擎就麻烦了，它执行count( * ）的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。 优化思路：自己计数，设置一个程序每插入一个计数+1，删除一个计数-1

count的几种用法:

1. count（主键）：InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)。
2. count（字段）没有notnull约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有notnull约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
3. count (1)InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
4. count(*)InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序：count(字段)<count(主键id)<count(1)≈count(*)，所以尽量使用count(*)。

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

索引

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。 提高数据检索的效率，降低数据库的10成本 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。 索引列也是要占用空间的。 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。 MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash索引	底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询
R-tree(空间索引)	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，<，…..) 无法利用索引完成排序操作 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。 在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

1. 聚集索引(Clustered Index) 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。必须有,而且只有一个
2. 二级索引(Secondary Index) 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键。可以存在多个

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIOUE）索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。 聚集索引存储的是对应行的数据 二级索引存储的是对应的主键id

回表查询指的是从二级索引找到对应字段的id值再根据id在聚焦索引中查询到数据 主键查询速度是最快的

创建索引

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name，...);

查看索引

SHOW INDEX FROM table_name ;

删除索引

DROP INDEX index_name ON table_name;

执行频率

MySQL客户端连接成功后，通过

show [session | global] status

命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次 session是本次对话的访问频次 global是全局的访问频次

SHOW GLOBAL STATUS LIKE 'Com_______';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

show variables like 'slow_query_log';

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

开启MySQL慢日志查询开关
slow_query_log=1
设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。

命令重启MySQL数据库

systemctl restart mysqld

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持 如果是yes就是支持，no就是不支持 profile操作： 查询是否支持profile

SELECT @@have_profiling;

查询是否打开profile

select @@profiling；

0表示没有打开，1表示打开 默认profiling是关闭的，可以通过set语句在session/global级别开启profiling

SET profiling =1;

profile详情 执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时： 查看每一条SQL的耗时基本情况

show profiles;

查看指定query_id的SQL语句各个阶段的耗时情况

show profile for query query_id;

查看指定query_id的SQL语句CPU的使用情况

show profile cpu for query query_id;

explain执行计划

EXPLAIN或者 DESC命令获取MySQL如何执行SELECT 语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。 语法： 直接在select语句之前加上关键字 explain /desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件；

查看表的结构

desc 基本表名

索引失效情况

最左前缀法则

如果索引了多列（联合索引）要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引的列如果跳过某一列索引将部分失效（后面的字段索引失效） 范围查询 联合索引中，出现范围查询（>，<）范围查询右侧的列索引失效 但如果使用≥或≤可以规避

索引列运在索引列上进行运算操作索引会失效

如果仅仅是尾部的模糊匹配索引不会失效，如果有头部匹配索引会失效 用or分割开的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及的索引都不会被用到 数据分布的影响 如果MySQL评估使用索引比全表慢，则不使用索引

SQL提示

sql提示是优化数据库的一个重要手段，即在sql语句中加入一些认为提示来打到优化操作的目的

use index(索引名)告诉数据库使用哪个索引
ignore index(索引名)告诉数据库忽略哪个索引
force index(索引名)告诉数据库必须使用哪个索引

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *

using index condition：查找使用了索引，但是需要回表查询数据 using where，usingindex：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

如果查找的字段没有建立辅助索引，那么在查询时就会再查询一遍聚集索引，在这个索引中找到目的字段的值返回到辅助索引，这样就形成了回表查询，相比于建立了辅助索引的字段会多查询一个索引，因此效率会慢，而使用* 通配符囊括了所有字段，只要有一个字段没有建立辅助索引就会进行回表查询

前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘lO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。 建立前缀索引

create index idx_xxxx on table_name(column(n));

n是前缀的长度 前缀长度 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

可以通过以下语句获取索引选择性 获取指定字段去重后的选择性

select count(distinct email) / count(*) from tb_user;

截取email的1-5个字段的选择性

select count(distinct substring(email,1,5)) / count(*) from tb_user;

单列索引与联合索引

联合索引：即一个索引包含了多个列。 在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。 多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

视图

视图时一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的 视图只保存了查询SQL逻辑，不保存查询结果，所以我们在创建视图的时候，主要工作就在创建这条sql查询语句上。

创建

create [or replace] view 视图名称[(列名列表)] as select语句 [with[cascaded | local]check option];

or replace替换一个视图

查询

查看创建视图语句

show create view 视图名称;

查看视图数据 视图可以当作一个表来查询

select * from 视图名称;

修改

1. 方式1

create  [or replace] view 视图名称[(列名列表)] as select语句 [with[cascaded | local]check option];

2. 方式2

alter view 视图名称[(列明列表)] as select语句 [with[cascaded | local]check option]

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED 和LOCAL，默认值为CASCADED。

视图存储的是表的逻辑，而不是数据，往视图里插入数据实际上是往表中插入数据，在插入视图中也会显示出来，但插入的数据会根据创建视图的限制语句选择性插入，如果插入的数据不符合视图的创建条件，就会阻止插入

cascaded： 由于可以基于视图创建视图，在使用了cascaded检查后基于此视图插入数据时不仅检查本视图的条件，也会检查上一层视图的条件，也就是要同时符合两视图的条件才会插入，即向上检查一层

local： 在插入时递归的检查此视图依赖的所有视图，所依赖的视图也递归检查，如果设置的有检查选项就根据条件判断是否插入，如果没有检查选项则忽略继续递归

删除

drop view [if exists] 视图名称 [,视图名称]...;

视图的更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

1. 聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION或者UNION ALL

作用

• 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
• 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
• 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响。

贡献者

pinkdopeybug

版权所有

版权归属：PinkDopeyBug

许可证：署名 4.0 国际 (CC-BY-4.0)