mysql优化

最新推荐文章于 2023-07-31 23:13:38 发布

磨人的阿木木

最新推荐文章于 2023-07-31 23:13:38 发布

阅读量130

点赞数 6

文章标签： MySQL

本文链接：https://blog.csdn.net/qq_29560801/article/details/85263225

版权

sql 优化

2.1.1：数据库引擎

数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。
总的思想：
MyIASM引擎是为了查和增加，效率高。所有功能都围绕这这个
Innodb引擎功能更强（事务等）效率低一些。

2.1.2：索引

（1）什么是索引?

索引，我们可以通俗的理解为就像是书的目录，字典，车次表等等。通过不断的缩小想要获取的数据的范围最终得到我们想要获得的数据。

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。在数据库关系图中，可以在选定表的"索引/键"属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。

（2）索引干什么用的？有什么作用?

创建索引可以大大提高系统的性能。第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因；第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

1.索引的作用

(1)快速取数据;

(2)保证数据记录的唯一性;

(3)实现表与表之间的参照完整性;

(4)在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间

2.索引的优，缺点

优点：

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

（3）索引的分类

1．普通索引

在创建普通索引时，不添加任何限制条件。这类的索引可以创建在任何的数据类型中。这类索引的唯一作用就是增加查询的速度，所以一般为那些最常出现在查询条件中的字段添加索引。

2．唯一索引

使用unique参数可以设置索引为唯一索引。在创建索引时，就规定该索引的值必须是唯一的。即不允许出现相同的值。通过唯一索引，可以更快速的确定某条记录。主键索引就是一种特殊的唯一索引，但是主键索引不允许值为空，唯一索引值可以为空。

3．主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

4．全文索引

使用fulltext参数可以设置索引为全文索引，全文索引只能创建在char，varchar，text类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。但只有MyISAM存储引擎支持全文检索。

5．复合索引

多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

6．空间索引

使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。对于初学者来说，这类索引很少会用到。

（4）索引的操作

查看索引

show index in table_name；

普通索引 （简单索引）

CREATE INDEX index_name ON table_name (column_name)

唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name)

主键索引

在创建表时，添加主键，便自动创建朱建索引；

    CREATE TABLE user(
        id int unsigned not null auto_increment,
        name varchar(50) not null,
        email varchar(40) not null,
        primary key (id)
    );

复合索引

CREATE INDEX index_name ON table_name (column_name, column_name，….)

在创建表时创建复合索引

    CREATE TABLE test (
        id INT NOT NULL,
        last_name CHAR(30) NOT NULL,
        first_name CHAR(30) NOT NULL,
        PRIMARY KEY (id),
        INDEX name (last_name,first_name)
   );

全文索引

CREATE FULLTEXT INDEX index_name ON table_name(column_name)

删除索引

drop index index_name on table_name ;

2.1.3：性能分析

介绍
- 使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈；
- 分析：
- - id
  - - select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序；
    - id相同，执行顺序由上至下；
    - id不同如果子查询，id的序号会递增，id值越大优先级约高，越先被执行；
    - id相同不同，同时存在；（id大的先执行，然后相同的顺序执行）（derived）;
  - select_type
  - - 查询的类型；
    - 1，simple：简单的select查询，查询中不包含子查询或者union；
    - 2，primary：查询中若包含任何复杂的子部分，最外层查询则标记为；（最后加载的）
    - 3，subquery：在select或者where列表中包含了子查询；
    - 4，derived：在from列表中包含的子查询被标记问derived（衍生），mysql会递归执行这些子查询，把结果放在临时表里；
    - 5，union：若第二个select出现在union之后，则被标记为nunion；
    - 6，union result：从union表获取结果的select ；
  - table :显示这一行的数据是关于哪张表的；
  - type：
  - - 访问类型
    - 从最好到最差依次是：
    - - system>const>eq_ref>ref>range>index>ALL；
    - 1，system：表只有一行记录（等同于系统表），平时不会出现，这个也可以忽略不计；
    - 2，const ：表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转换为一个常量；
    - 3，eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描；
    - 4，ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体；
    - 5，range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现了between，<，>，in 等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
    - 6，index：全索引扫描，index和ALL的区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小，但index是从索引中读取的，而all是从硬盘中读取的；
    - 7，ALL：全表扫描；最差的结果；
  - possible_keys：显示可能应用在这张表中的索引，一个或者多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定别查询实际使用；
  - key：实际使用了那个索引；如果为null，则没有使用索引；查询中入使用了覆盖索引，则该索引仅出现在key列表中。
  - key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
  - ref：显示索引的哪一列被使用了，如果可能的话，是一个常数（const），哪些列或常量被用于查找索引列上的值；
  - rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数；
  - Extra：包含不适合在其他列中显示但十分重要的额外信息；
    1：using filesort，（九死一生）文件内排序，就是我们自己创建的索引没有被使用到，而是自己内部重新排序，（出现这个应该尽快优化）；
    2：using temporary：（死定了）使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询 group by。
    3：using index：表示相应的select操作中送了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现using where ，表明索引被用来执行索引键值的查找，如果没有同时出现using where ，表明索引来读取数据而非执行查找动作；
    - - 覆盖索引：就是select的数据列只用从索引中就能够取到，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说，查询列要背所建的索引覆盖；
        4：using where ：表示使用到了where ；
        5：using join buffer ：使用到了连接缓存；
        6：impossible where ： where子句的值总是false，不能用来获取任何元素；
        7：select tables optimized away ：在没有group by子句的情况下，基于索引优化min/max操作或者对于myISAM存储引擎优化count(*)操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即完成优化。
        8：distinct ：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作；
作用
- 表的读取顺序；
- 数据读取操作的操作类型；
- 那些索引可以使用；
- 那些索引被实际使用；
- 表之间的引用；
- 每张表有多少行别优化器查询；

2.2：函数

1.mysql函数介绍

MySQL函数是MySQL数据库提供的内部函数。这些内部函数可以帮助用户更加方便的处理表中的数据。 MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。

2.函数的类型

数学函数、字符串函数、日期和时间函数、控制流程函数、系统信息函数、加密函数、其它函数

3.MySQL自定义函数简介

MySQL本身提供了内置函数，这些函数的存在给我们日常的开发和数据操作带来了很大的便利，比如我前面提到过的聚合函数SUM()、AVG()以及日期时间函数等等，可是我们总会出现其他的需求：我们需要调用一个函数，这个函数需要按照我们的要求来实现我们自己的功能，可是这个函数式系统不能提供的因为这中需求的不确定性。因此我们需要自己来解决这种需求。还好MySQL设计的扩展性给了我们这个机会，我们可以通过自定义函数的功能解决这个问题。

--在MySQL中使用自定义函数也需要相应的要求，语法如下，

　　--创建新函数：

　　　　Create function function_name()

　　　　returns返回值类型

　　　　函数体内容

　　--相关说明，

--函数名：应该合法的标识符，并且不应该与已有的关键字冲突。一个函数应该属于某数据库，可以使用  			db_name.funciton_name的形式执行当前函数所属数据库，否则默认为当前数据库。
--参数列表：可以有一个或者多个函数参数，甚至是没有参数也是可以的。对于每个参数，由参数名和参数类型组成。
--返回值：指明返回值类类型
--函数体：自定义函数的函数体由多条可用的MySQL语句，流程控制，变量声明等语句构成。需要指明的是函数体中一定要含有return 返回语句。

a.无参函数定义

mysql> DROP FUNCTION IF EXISTS hello;

mysql> DELIMITER $$
mysql> CREATE FUNCTION hello()
    -> RETURNS VARCHAR(255)
    -> BEGIN
    -> RETURN 'Hello  world,i am mysql';
    -> END $$

调用自定义函数：

mysql> DELIMITER ;
mysql> SELECT hello();
+-------------------------+
| hello()                 |
+-------------------------+
| Hello  world,i am mysql |
+-------------------------+

b:含有参数的自定义函数

mysql> DELIMITER $$

mysql> CREATE FUNCTION   test.formatDate(fdate datetime)
    -> RETURNS VARCHAR(255)
    -> BEGIN
    -> DECLARE x VARCHAR(255) DEFAULT '';
    -> SET x= date_format(fdate,'%Y年%m月%d日%h时%i分%s秒');
    -> RETURN x;
    -> END $$

mysql> DELIMITER ;
mysql> SELECT formatDate(now());
+----------------------------+
| formatDate(now())          |
+----------------------------+
| 2014年11月21日03时41分21秒 |
+----------------------------+

变量声明：　DECLARE var_name[,…] type [DEFAULT value] ，这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。使用语序使用 set 和 select into语句为变量赋值。

2.3：存储过程

1、什么是mysql存储过程?

存储例程是存储在数据库服务器中的一组sql语句，通过在查询中调用一个指定的名称来执行这些sql语句命令.

2、存储过程和函数的区别

函数必须有返回值，而存储过程没有。

存储过程的参数可以为IN,OUT,INOUT类型，而函数只能是IN。

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名数据类形…])
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回

3、优点

1、存储过程只在创建时进行编译，而sql语句每次执行都需要编译，所以存储过程可以提高数据库执行速度

2、简化复杂业务逻辑，结合事物一起封装

3、复用性好，根据需要可重复使用。

3、安全性好，可指定存储过程的使用权。

4、缺点

1、可移植性差，相同的存储过程并不能跨多个数据库进行操作。

2、大量使用存储过程后，首先会使服务器压力增大，而且维护难度逐渐增加。

5：语法

一：概述
存储过程和函数是事先经过编译并存储在数据库中的一段sql语句的集合。
二：存储过程和函数的区别：
函数必须有返回值，而存储过程没有。
存储过程的参数可以是IN,OUT,INOUT,类型，而函数只能是IN。
三：优点：
存储过程只在创建时进行编译，而sql语句每次执行都需要编译，所以存储过程可以提高数据库执行速度。
简化复杂操作，结合事务一起封装。
复用性好；
安全性高，可指定存储过程的使用权。

创建存储过程：
DELIMITER //
CREATE PROCEDURE p1()
BEGIN
SELECT COUNT(*) FROM t_user;
END;//

执行存储过程：
call p1();

---------------------------in-------------------------------
先创建一个表：
create table t1(
id int ,
name varchar(100)
)

创建一个存储过程
DELIMITER //
CREATE PROCEDURE p2(IN a INT)
BEGIN
– DECLARE 定义一个变量i
DECLARE i INT DEFAULT 0;
WHILE(i<a) DO
INSERT INTO t1 VALUES(i,MD5(i));
SET i = i+1;
END WHILE;
END//

执行存储过程：
call p1(20);

---------------------------out------------------------------
创建一个存储过程
DELIMITER //
CREATE PROCEDURE p2(OUT pp INT)
BEGIN
SELECT COUNT(*) INTO pp FROM t1;
END//
调用存储过程
call p2(@cc)；
查询@cc
select @cc;

---------------------------in 和 out------------------------------
创建一个存储过程
DELIMITER //
CREATE PROCEDURE p4(IN p1 VARCHAR(50) , OUT p2 INT)
BEGIN
SELECT COUNT(*) INTO p2 FROM t_user WHERE NAME = p1;
END//
调用存储过程
CALL p4(‘xx’,@num);
查询@cc
select @num;

统计指定人员年龄超过18的人数；
– 统计指定人员年龄超过18的总人数
DELIMITER//
CREATE PROCEDURE p6(IN p1 VARCHAR(100) , IN p2 INT , OUT p3 INT)
BEGIN
SELECT COUNT(*) INTO p3 FROM t_user
WHERE NAME = p1 AND age > p2;
END//
调用存储过程
CALL p6(‘王炎’ , 18 , @agenum);
查询@cc
SELECT @agenum;

---------------------------inout------------------------------
创建一个存储过程
DELIMITER//
CREATE PROCEDURE p1(INOUT p1 INT)
BEGIN
IF(p1 IS NOT NULL) THEN
SET p1 = p1+1;
ELSE
SET p1 = 100;
END IF;
END//
查询@cc
SELECT @aaa;
调用存储过程
CALL p1(@aaa)

2.4：触发器

触发器：triggers

触发器是一个特殊的存储过程，他的执行不是有程序调用，也不是手工启动，而是有事件来触发。比如当对一个表进行操作（insert,delete,update）时就会激活它执行，触发器经常用于加强数据的完整性约束和业务的规则等。

表一：t_cust (客户表)
字段：cust_id , cust_name 
表二：t_num （统计表）
字段：num （统计的个数）
业务场景：t_num 表是用来统计客户的个数的。
如果增加客户，需要修改t_num中 +1；
这个时候，我们就可以创建一个触发器。


创建增加的触发器：
DELIMITER //
CREATE TRIGGER cust_insert_trigger AFTER INSERT
ON t_cust FOR EACH ROW
BEGIN
	UPDATE t_num SET num = num+1;
END//
调用增加的方法：然后查询t_num表，数据增加了。

再创建一个删除的触发器；
DELIMITER //
CREATE TRIGGER cust_delete_trigger AFTER DELETE
ON t_cust FOR EACH ROW
BEGIN
	UPDATE t_num SET num = num - 1 ;
END//

2.5：三范式

数据库的三范式：

一：1NF一范式的理解：

1NF是关系型数据库中的最基本要求，就是要求记录的属性是原子性，不可分，就是属性不能分，这是关系型数据库的基本要求，不满足这个就不能叫关系型数据库了

例如：

讲师 性别 班级 教室 代课时间 代课时间（开始，结束）
韩*明	Male	php0331	102	30天	2013-03-31,2013-05-05
韩*明	Male	php0228	106	30天	2013-02-28,2013-03-30
韩*平	male	Php0228	106	15天	2013-03-31,2013-05-05

上面的代课时间字段设计就不满足原子性，因为它可以再分的，开始时间和结束时间，需要按照下面来设计拆分：

讲师 性别 班级 教室 代课时间 开始 结束
韩*明	Male	php0331	102	30天	2013-03-31	2013-05-05
韩*明	Male	php0228	106	30天	2013-02-28	2013-03-30
韩*平	male	Php0228	106	15天	2013-03-31	2013-04-20

上面的就满足了，但是有些时候也需要去违背这个1NF的设计：在用dede设计cms的时候后台的下载地址是将格式，名称，分辨率这些进行评级在一个属性里面，但是可以看作一个整体，因为这个属性即使分开，那么各个成员的关系不是平等的，但是代课时间是不一样的，比如分为开始时间和结束时间，两个成员的关系基本一样！所以也不矛盾！

二：2NF二范式的理解

2NF是不能有部分依赖，部分依赖的前提条件是有组合主键，就是每条记录是需要一个主键的，这个主键可以是一个单独的属性，但也可以是组合主键，就是由记录的多个属性来唯一确定一条记录，那么只要出现了组合主键就可以产生部分依赖，部分依赖是组合主键出现的前提下，剩余的属性，不完全依赖于组合主键，也是部分依赖组合主键，比如该表的N条记录中由组合主键中的一条或者几条就可以确定剩余属性的属性，那么就可以说产生部分依赖，而在实际开发中，一般不采用组合主键，而是自己增加一个字段id自增长，作为主键，这样的单属性主键是不会产生部分依赖的！

例如：

讲师P 性别 班级P         教室 代课时间 开始 结束
韩*明	Male	php0331	102	30天	2013-03-31	2013-05-05
韩*明	Male	php0228	106	30天	2013-02-28	2013-03-30
韩*平	male	Php0228	106	15天	2013-03-31	2013-04-20

上面的设计可以用讲师P，班级，两个字段作为组合主键，但是问题来了，那么后面的教室仅仅由班级字段(组合主键中的一条)就可以确定，即所谓教室部分依赖于组合主键，那么就不符合2NF

应该按照下面就行设计：

IDP 讲师 性别 班级 教室 代课时间 开始 结束
1	韩*明	Male	php0331	102	30天	2013-03-31	2013-05-05
2	韩*明	Male	php0228	106	30天	2013-02-28	2013-03-30

可以看到仅仅增加一个主键IDP就不存在部分依赖了，这是实际项目中开发中常用的手段！

三：3NF的理解：不能出现传递依赖：就是主键，非主键1，非主键2三者之间不能出现传递依赖关系，如果出现由主键可以推出非主键1,然后由非主键1可以推出非主键2，那么主键与非主键2就产生了传递依赖关系，这就不满足三范式，通俗来讲，在一个表的，当然以一条记录为单位，主键和非主键之间可以产生父子关系，但是非主键之间是不能出现父子关系的！

例如：

IDP 讲师 性别 班级 教室 代课时间 开始 结束
1	韩*明	Male	php0331	102	30天	2013-03-31	2013-05-05
2	韩*明	Male	php0228	106	30天	2013-02-28	2013-03-30
3	韩*平	male	php0228	106	15天	2013-03-31	2013-04-20

上面的设计不满足3NF:

主键1--推出--->班级php0331，班级php0331-----推出---->教室102,这样给人的感觉，教室不是由主键IDP1直接推出的，好像由班级通过传递推出的；当然还存在

主键IDP---->讲师------>性别

这样的坏处就是会产生数据冗余，解决方案是把中间的代理抽出来作为另外一张表：

应该如下设计：

IDP 讲师 班级 代课时间 开始 结束
1	韩*明	php0331	30天	2013-03-31	2013-05-05|
2	韩*明	php0228	30天	2013-02-28	2013-03-30|
3	韩*平	php0228	15天	2013-03-31	2013-04-20|

--------------------------------------------------------

讲师 性别
韩*明	male
韩*平	male

-----------------


班级 教室

php0228 106

php0331 102

-------------

可以看到这样设计只需要用22个字段，而上面那种设计需要用24个字段，这样就节省了2个字段，如果数据是海量的那么节省的数据应该更加多

这样设计，其实为了避免非笛卡尔积数据的重复，当然，笛卡尔积的数据的重复是必须的！

那么数据库的设计可以先按照自己的想法设计一个"大表"出来,然后进行拆分成符合三范式的表，当然一般的规律的实体都单独作为一个表格

比如讲师实体，班级实体，代课关系实体，但是最大的问题是开始不知道哪些是实体，其实除了看得见的，其实说的清的可以描述的关系也可以作为一个实体

，这一点是容易被忽视的，但是随着经验的提升，慢慢会了解！

2.6：其他

（1）插入当前时间

有时候我们会有这样的需求。

往数据库添加一条数据。然后表中有一个字段是creat_time。添加数据的同时，这个字段默认为当前时间。

其实很简单。

设置字段类型为timestamp。然后下面默认值填写CURRENT_TIMESTAMP

磨人的阿木木

关注

6
点赞
踩
0

收藏

觉得还不错? 一键收藏
3
评论
mysql优化

sql 优化2.1.1：数据库引擎数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。总的思想：MyIASM引擎是为了查和增加，效率高...
复制链接

扫一扫