优化原因
性能低
执行时间太长
等待时间太长
SQL语句欠佳（连接查询）
索引失效
服务器参数设置不合理（缓冲、线程数）
Mysql表引擎
MySQL数据库支持多种存储引擎，每种引擎都有其自身的特点、优点和缺点。以下是几种常见的MySQL数据库引擎及其对应的优缺点：
InnoDB引擎
-
优点：
支持事务（ACID兼容）：具有事务处理的能力，支持提交、回滚、并发控制和恢复能力。
行级锁定：支持行级锁定，可提高并发性能。
外键约束：支持外键约束，保证数据的完整性。
支持热备份：可通过InnoDB的在线备份功能进行热备份。
-
缺点：
内存消耗较高：相对于其他引擎，InnoDB需要更多的内存和存储空间。
性能开销：由于支持事务和行级锁定，性能开销相对较高。
MyISAM引擎
-
优点：
较低的内存消耗：相比InnoDB，MyISAM使用更少的内存和系统资源。
速度较快：对于读密集型的应用，MyISAM通常具有更高的性能。
全文搜索索引：支持全文搜索索引，适用于需要进行全文搜索的场景。
-
缺点：
不支持事务：不支持事务处理，因此在并发写入较高的情况下可能会出现数据不一致。
表级锁定：只支持表级锁定，对于高并发写入的应用可能会导致性能瓶颈。
不支持外键约束：不支持外键约束，容易导致数据完整性问题。
Memory引擎（也称为Heap引擎）
-
优点：
高速读写：数据存储在内存中，读写速度非常快。
无磁盘I/O：由于数据完全存储在内存中，不涉及磁盘I/O操作，因此性能非常高。
简单轻便：不需要进行复杂的磁盘操作，非常适合用作临时表或者缓存。
-
缺点：
数据易丢失：数据库重启或者服务器宕机时，数据将丢失，不适合持久化存储。
内存限制：由于数据存储在内存中，受限于服务器内存大小，适用于小型数据集。
Archive引擎
-
优点：
压缩存储：数据存储采用压缩算法，占用空间较小。
高速插入：适用于大量数据的批量插入操作，速度较快。
-
缺点：
读取性能较差：不支持索引，只能进行全表扫描，因此查询性能较低。
不支持事务和外键：不支持事务处理和外键约束，数据完整性受限。
选择合适的存储引擎取决于应用的具体需求，如对事务支持的需求、并发读写的情况、数据量大小等。
主要使用
主要使用前两种
InnoDB(默认) ：事务优先 （适合高并发操作；行锁）
MyISAM ：性能优先 （表锁）
常用命令
查询数据库引擎： 支持哪些引擎？
show engines ;
查看当前使用的引擎
show variables like '%storage_engine%' ;
MYSQL逻辑分层
连接层 服务层 引擎层 存储层
SQL编写过程
select dinstinct ..from ..join ..on ..where ..group by ...having ..order by ..limit ..
SQL解析过程
这才是实际的sql解析过程,创建合适的复合索引应该按照这个顺序
from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...
索引介绍
定义与优劣势
索引： 相当于书的目录
-
索引： index是帮助MYSQL高效获取数据的数据结构。索引是数据结构（树：B+树(默认)、Hash树...）
B+ B数 LAM
-
弊端：
1.索引本身很大， 可以存放在内存/硬盘（通常为 硬盘）
2.索引不是所有情况均适用：
a.少量数据
b.频繁更新的字段
c.很少使用的字段
3.索引会降低增删改的效率（增删改 查）
-
优势：
1提高查询效率（降低IO使用率）
2.降低CPU使用率
分类
主键索引： 不能重复。id 不能是null
唯一索引 ：不能重复。id 可以是null
单值索引 ： 单列， age ;一个表可以多个单值索引,name。
复合索引 ：多个列构成的索引 （相当于 二级目录 ： z: zhao） (name,age) (a,b,c,d,...,n)
注意主键索引和唯一索引的区别
注意：如果一个字段是primary key，则改字段默认就是 主键索引 不用创建
索引的CRUD
创建索引
方式一
create 索引类型 索引名 on 表(字段)
单值
create index dept_index on tb(dept);
唯一
create unique index name_index on tb(name) ;
复合索引
create index dept_name_index on tb(dept,name);
方式二
alter table 表名 索引类型 索引名（字段）
单值
alter table tb add index dept_index(dept) ;
唯一
alter table tb add unique index name_index(name);
复合索引
alter table tb add index dept_name_index(dept,name);
删除索引
drop index 索引名 on 表名 ;
drop index name_index on tb ;
查询索引
下面一个linux环境下查看排版更好一点
show index from 表名 ;
show index from 表名 \G
分析SQL的执行计划
分析语句
Explain +sql
执行计划返回结果
id : 编号
select_type ： 查询类型
table ： 表
type ： 类型
possible_keys ： 预测用到的索引
key ： 实际使用的索引
key_len ： 实际使用索引的长度
ref : 表之间的引用
rows ： 通过索引查询到的数据量
Extra : 额外的信息
id 编号
可以理解为sql的执行顺序
id值有相同，又有不同： id值越大越优先；id值相同，从上往下 顺序执行
准备sql
create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);
create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);
insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);
insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;
查询教授SQL课程的老师的描述（desc）
通过不同的方式查询,分别id不同情况下,底层sql的执行顺序即id
EXPLAIN SELECT
tc.tcdesc
FROM
teacherCard tc,
course c,
teacher t
WHERE
c.tid = t.tid
AND t.tcid = tc.tcid
AND c.cname = 'sql';
EXPLAIN SELECT
tc.tcdesc
FROM
teacherCard tc
WHERE
tc.tcid = (
SELECT
t.tcid
FROM
teacher t
WHERE
t.tid = ( SELECT c.tid FROM course c WHERE c.cname = 'sql' ) );
EXPLAIN SELECT
tc.tcdesc
FROM
teacherCard tc
WHERE
tc.tcid = (
SELECT
t.tcid
FROM
teacher t
WHERE
t.tid = ( SELECT c.tid FROM course c WHERE c.cname = 'sql' ) );
select_type 查询类型
PRIMARY: 包含子查询SQL中的 主查询 （最外层）
SUBQUERY： 包含子查询SQL中的 子查询 （非最外层）
-
SIMPLE: 简单查询（不包含子查询、union）
在from子查询中， 如果有table1 union table2 ，则table1 就是derived,table2就是union
DERIVED: 衍生查询(使用到了临时表)
UNION: 上例
UNION RESULT : 告知开发人员，哪些表之间存在union查询
EXPLAIN SELECT
cr.cname
FROM
( SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2 ) cr;
type 索引类型、类型
重点 核心优化标准
system>const>eq_ref>ref>range>index>all
其中：system,const只是理想情况；
调优目的 ： 实际能达到 ref、range、 index就可以了
system
（忽略）: 只有一条数据的系统表 ；或 衍生表只有一条数据的主查询
const
1.仅仅能查到一条数据的SQL
2.用于Primary key 或unique索引 （类型 与索引类型有关）
3.只有命中唯一索引或者主键索引
准备sql
create table test01
(
tid int(3),
tname varchar(20)
);
insert into test01 values(1,'a') ;
commit;
alter table test01 add constraint tid_pk primary key(tid) ;
explain select * from (select * from test01 )t where tid =1 ;
eq_ref
唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）
alter table teacherCard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid) ;
explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;
以上SQL，用到的索引是 t.tcid,即teacher表中的tcid字段； 如果teacher表的数据个数 和 连接查询的数据个数一致（都是3条数据），则有可能满足eq_ref级别；否则无法满足。
ref
非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
准备数据：
insert into teacher values(4,'tz',4) ;
insert into teacherCard values(4,'tz222');
## 增加普通索引
alter table teacher add index index_name (tname) ;
测试：
explain select * from teacher where tname = 'tz';
range
检索指定范围的行 ,where后面是一个范围查询(between ,> < >=,
特殊:in有时候会失效 ，从而转为 无索引all)
alter table teacher add index tid_index (tid) ;
explain select t.* from teacher t where t.tid in (1,2) ;
explain select t.* from teacher t where t.tid <3 ;
index
-- tid 是索引， 只需要扫描索引表，不需要所有表中的所有数据
查询全部索引中数据
explain select tid from teacher ;
all
-- cid不是索引，需要全表所有，即需要所有表中的所有数据
explain select cid from course ;
possible_keys 可能用到的索引
是一种预测，不准
key ：实际使用到的索引
key_len ：索引的长度
作用：用于判断复合索引是否被完全使用 （a,b,c）。
utf8:1个字符3个字节 gbk:1个字符2个字节 latin:1个字符1个字节
如果索引字段可以为Null,则会使用1个字节用于标识。
ref 表的关联字段
常量用const表示
rows: 被索引优化查询的数据个数
(实际通过索引而查询到的 数据个数)
Extra
优化的重点之一
using filesort
性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。
create table test02
(
a1 char(3),
a2 char(3),
a3 char(3),
index idx_a1(a1),
index idx_a2(a2),
index idx_a3(a3)
);
explain select * from test02 where a1 ='' order by a2 ;
using temporary
性能损耗大 ，用到了临时表。一般出现在group by 语句中。
准备sql
干掉所有单键索引,创建复合索引。匹配最左原则
drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;
alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;
explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;
符合最左匹配查询 不会用到临时表
group by a2 用到了临时表
explain select a2 from test02 where a1 in ('1','2','3') group by a2 ;
using where
（需要回表查询）
explain select a1,a3 from test02 where a3 = '' ;
using index
性能提升; 索引覆盖（覆盖索引）
