目录
一、引擎
一、存储引擎
InnoDB
是一种兼顾高可靠性和高性能的通用存储引擎，在
MySQL 5.5
之后，
InnoDB
是默认的
MySQL 存储引擎。常用的存储引擎有三种MyISAM、Memory、 InnoDB。
1、 查询当前数据库支持的存储引擎
show engines;
2、 创建数据库时指定引擎
create table student(
id int,
name varchar(10)
) engine = MyISAM ;
二、三种引擎的特点
InnoDB:
1、DML操作遵循ACID模型，支持事务；2、行级锁，提高并发访问性能；3、支持外键 FOREIGN KEY 约束，保证数据的完整性和正确性；文件： innoDB引擎的每张表都会对应的一个表空间ibd文件，存储该表的表结构（ frm- 早期的 、 sdi- 新版的）、数据和索引。show variables like 'innodb_file_per_table' ;这个参数开启， 代表对于 InnoDB 引擎的表，每一张表都对应一个 ibd文件。一般存放在mysql安装目录data下、可以使用ibd2sdi+文件打开查看。
MyISAM:
1、不支持事务，不支持外键2、支持表锁，不支持行锁3、访问速度快文件：xxx.sdi储存表结构信息xxx.MYD储存数据xxx.MYI储存索引
Memory:
Memory 引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。1、内存存放2、hash索引（默认）文件：xxx.sdi存储表结构信息
三、区别
|
特点
|
InnoDB
|
MyISAM
|
Memory
|
|---|---|---|---|
|
存储限制
| 64TB | 有 | 有 |
| 事务安全 | 支持 | - | - |
| 锁机制 | 行锁 | 表锁 | 表所 |
| B+tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | - | - | 支持 |
| 全文索引 | 5.6之后 | 支持 | - |
| 空间索引 | 高 | 低 | N/A |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 | - | - |
二、索引
一、什么是索引
索引（
index
）是帮助
MySQL
高效获取数据的数据结构
(
有序
)
。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
二、特点
| 优势 | 劣势 |
|---|---|
|
提高数据检索的效率，降低数据库
的
IO
成本
|
索引列也是要占用空间的。
|
|
通过索引列对数据进行排序，降低
数据排序的成本，降低
CPU
的消
耗。
|
索引大大提高了查询效率，同时却也降低更新表的速度，
如对表进行
INSERT
、
UPDATE
、
DELETE
时，效率降低。
|
三、索引结构
| 索引结构 | 描述 |
|---|---|
|
B+Tree
索引
|
最常见的索引类型，大部分引擎都支持
B+
树索引
|
|
Hash
索引
|
底层数据结构是用哈希表实现的
,
只有精确匹配索引列的查询才有效
,
不支持范围查询
|
|
R-tree(
空间索
引）
|
底层数据结构是用哈希表实现的
,
只有精确匹配索引列的查询才有效
,
不
支持范围查询
|
| Full-text(全文 索引) |
是一种通过建立倒排索引
,
快速匹配文档的方式。类似于
Lucene,Solr,ES
|
四、索引与存储引擎的关系
在
MySQL
中，支持
hash
索引的是
Memory
存储引擎。 而
InnoDB
中具有自适应
hash
功能，
hash
索引是 InnoDB存储引擎根据
B+Tree
索引在指定条件下自动构建的。
无、索引分类
MySQL数据库，将索引的具体类型主要分为：主键索引、唯一索引、常规索引、全文索引。
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 |
针对于表中主键创建的索引
|
默认自动创建
,
只能
有一个
|
PRIMARY
|
| 唯一索引 |
避免同一个表中某数据列中的值重复
|
可以有多个
|
UNIQUE
|
| 常规索引 |
快速定位特定数据
|
可以有多个
| |
| 全文索引 |
全文索引查找的是文本中的关键词，而不是比较索引中的值
|
可以有多个
|
FULLTEXT
|
六、聚焦索引
| 分类 | 含义 | 特点 |
|---|---|---|
|
聚集索引
(Clustered
Index)
|
将数据存储与索引放到了一块，索引结构的叶子 节点保存了行数据
|
必须有
,
而且只 有一个
|
|
二级索引
(Secondary
Index)
|
将数据与索引分开存储，索引结构的叶子节点关 联的是对应的主键
|
可以存在多个
|
聚集索引选取规则:
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
七、创建索引
1、创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;
2、查看索引
SHOW INDEX FROM table_name ;
3、删除索引
DROP INDEX index_name ON table_name ;三、SQL性能分析
一、SQL执行频率
1、命令可以提供服务器状态信息
show [session|global] status；
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
2、可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
SHOW GLOBAL STATUS LIKE 'Com_______';
二、慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有
SQL语句的日志
查看系统变量
show variables like 'slow_query_log';
三、profile详情
show profiles
能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
1、通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling ;
2、打开profile
SET profiling = 1;
3、查看每一条SQL的耗时基本情况
show profiles;
四、explain
EXPLAIN 或者
DESC
命令获取
MySQL
如何执行
SELECT
语句的信息，包括在
SELECT
语句执行 过程中表如何连接和连接的顺序。
1、直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;五、索引使用
1、在查询慢的字段上创建索引
create index ind_sku_sn on tb_sku(sn);
2、最左前缀法则
最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。
CREATE INDEX IND_USER_pro_age_sta ON tb_user(profession,age,status)
要使用这个索引查询时必须存在profession这个字段3、范围查询
联合索引中，出现范围查询(>,<)
，范围查询右侧的列索引失效。
explain select * from tb_user where profession = '软件工程' and age > 30
and status = '0';4、索引失效
1、不要在索引列上进行运算操作， 索引将失效
explain select * from tb_user where phone = '17799990015';
2、当根据phone字段进行函数运算操作之后，索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';5、字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。如果字符串不加单引号，
对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效6、模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，
索引将会失效7、or连接条件
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，
那么涉及的索引都不会被用到8、数据分部影响
如果MySQL评估使用索引比全表更慢，则不使用索引9、覆盖索引
尽量使用覆盖索引，减少select *，覆盖索引是指 查询使用了索引，并
且需要返回的列，在该索引中已经全部能够找到 。10、前缀索引
当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，
这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，
建立索引，这样可以大大节约索引空间，从而提高索引效率。
例：为tb_user表的email字段，建立长度为5的前缀索引。
create index idx_email_5 on tb_user(email(5));11、单列索引与联合索引
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。四、索引设计原则
一、针对于数据量较大，且查询比较频繁的表建立索引。
二、针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
三、尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
四、如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
五、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
六、要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
七、create unique index idx_user_phone_name on tb_user(phone,name);
八、如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
五、SQL优化
一、插入数据
1、批量插入数据
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');2、手动控制事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;3、主键顺序插入，性能要高于乱序查询
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89二、大批量插入数据
1、如果一次性需要插入大批量数据(比如: 几百万的记录)，可以使用MySQL数据库提供的load指令进行插入。
可以执行如下指令，将数据脚本文件中的数据加载到表结构中：
1、客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p/
2、设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
3、执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;
主键顺序插入性能高于乱序插入2、举例：
1、先创建表结构
CREATE TABLE `tb_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(50) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
2、 设置参数
-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
3、load加载数据
load data local infile '/root/load_user_100w_sort.sql' into table tb_user
fields terminated by ',' lines terminated by '\n' ;三、主键优化
正在更新...
四、order by 优化
五、group by优化
六、limit优化
七、count优化
八、update优化
六、视图
一、视图
视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视 图的查询中使用的表，并且是在使用视图时动态生成的。 通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作 就落在创建这条SQL查询语句上。
二、语法
1、创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [
CASCADED | LOCAL ] CHECK OPTION ]2、查询
查看创建视图语句：SHOW CREATE VIEW 视图名称;
查看视图数据：SELECT * FROM 视图名称 ...... ;3、修改
方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH
[ CASCADED | LOCAL ] CHECK OPTION ]
方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |
LOCAL ] CHECK OPTION ]
4、删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...示例：
-- 创建视图
create or replace view stu_v_1 as select id,name from student where id <= 10;
-- 查询视图
show create view stu_v_1;
select * from stu_v_1;
select * from stu_v_1 where id < 3;
-- 修改视图
create or replace view stu_v_1 as select id,name,no from student where id <= 10;
alter view stu_v_1 as select id,name from student where id <= 10;
-- 删除视图
drop view if exists stu_v_1;三、检查选项
当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插 入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视 图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL ，默认值为 CASCADED 。
1、CASCADED
级联。 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图 创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。
2、LOCAL
本地。 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创 建时未指定检查选项。 则在执行检查时，知会检查v2，不会检查v2的关联视图v1。
四、视图的更新
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一 项，则该视图不可更新：
1、聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
2、DISTINCTC.GROUP BY
3、HAVING
4、UNION 或者 UNION ALL
示例：
create view stu_v_count as select count(*) from student;五、视图的作用
1、简单
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视 图，从而使得用户不必为以后的操作每次指定全部的条件。
2、安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
3、数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。
六、案例
1、为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽 手机号和邮箱两个字段。
create view tb_user_view as select id,name,profession,age,gender,status,createtime
from tb_user;
select * from tb_user_view;2、查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操 作，定义一个视图。
1、create view tb_stu_course_view as select s.name student_name , s.no student_no ,
c.name course_name from student s, student_course sc , course c where s.id =
sc.studentid and sc.courseid = c.id;
2、select * from tb_stu_course_view;
七、存储过程
一、介绍
存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发 人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
特点:
1、封装，复用 --------> 可以把某一业务SQL封装在存储过程中，需要用到 的时候直接调用即可。
2、可以接收参数，也可以返回数据 --------> 再存储过程中，可以传递参数，也可以接收返回值。
3、减少网络交互，效率提升 -------------> 如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。
二、基本语法
1、创建
CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END ;2、调用
CALL 名称 ([ 参数 ]);3、查看
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';
-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称 ; 4、删除
DROP PROCEDURE [ IF EXISTS ] 存储过程名称 ；
注意: 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的 结束符。
三、变量
在MySQL中变量分为三种类型: 系统变量、用户定义变量、局部变量。
1）、系统变量
系统变量 是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话 变量（SESSION）。
1、查看系统变量：
-- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES ;
-- 可以通过LIKE模糊匹配方式查找变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......';
-- 查看指定变量的值
SELECT @@[SESSION | GLOBAL] 系统变量名;
2、设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;
注意:
如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
A. 全局变量(GLOBAL): 全局变量针对于所有的会话。
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。
B. 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。演示示例:
-- 查看系统变量
show session variables ;
show session variables like 'auto%';
show global variables like 'auto%';
select @@global.autocommit;
select @@session.autocommit;
-- 设置系统变量
set session autocommit = 1;
insert into course(id, name) VALUES (6, 'ES');
set global autocommit = 0;
select @@global.autocommit;3）、用户定义变量
用户定义变量 是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量 名" 使用就可以。其作用域为当前连接。
1、赋值
1、
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
赋值时，可以使用 = ，也可以使用 := 。
2、
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;
2、使用
SELECT @var_name ;注意: 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
示例:
-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
-- 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;
select @abc;
638
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
