网站设计运行费用餐饮淡季营销100种方案

当前位置： 首页 > news >正文

网站设计运行费用,餐饮淡季营销100种方案,证券公司如何拉客户,网站seo外包价格第1章 存储过程 1.1 什么是存储过程#xff1f; 存储过程可称为过程化SQL语言#xff0c;是在普通SQL语句的基础上增加了编程语言的特点#xff0c;把数据操作语句(DML)和查询语句(DQL)组织在过程化代码中#xff0c;通过逻辑判断、循环等操作实现复杂计算的程序语言。 换…第1章 存储过程 1.1 什么是存储过程 存储过程可称为过程化SQL语言是在普通SQL语句的基础上增加了编程语言的特点把数据操作语句(DML)和查询语句(DQL)组织在过程化代码中通过逻辑判断、循环等操作实现复杂计算的程序语言。 换句话说存储过程其实就是数据库内置的一种编程语言这种编程语言也有自己的变量、if语句、循环语句等。在一个存储过程中可以将多条SQL语句以逻辑代码的方式将其串联起来执行这个存储过程就是将这些SQL语句按照一定的逻辑去执行所以一个存储过程也可以看做是一组为了完成特定功能的SQL 语句集。 每一个存储过程都是一个数据库对象就像table和view一样存储在数据库当中一次编译永久有效。并且每一个存储过程都有自己的名字。客户端程序通过存储过程的名字来调用存储过程。 在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。 1.2 存储过程的优点和缺点 优点速度快。 - 降低了应用服务器和数据库服务器之间网络通讯的开销。尤其在数据量庞大的情况下效果显著。缺点移植性差。编写难度大。维护性差。 - 每一个数据库都有自己的存储过程的语法规则这种语法规则不是通用的。一旦使用了存储过程则数据库产品很难更换例如编写了mysql的存储过程这段代码只能在mysql中运行无法在oracle数据库中运行。- 对于数据库存储过程这种语法来说没有专业的IDE工具集成开发环境所以编码速度较低。自然维护的成本也会较高。在实际开发中存储过程还是很少使用的。只有在系统遇到了性能瓶颈在进行优化的时候对于大数量的应用来说可以考虑使用一些。 1.3 第一个存储过程 存储过程的创建 create procedure p1() beginselect empno,ename from emp; end;存储过程的调用 call p1();存储过程的查看 查看创建存储过程的语句 show create procedure p1;通过系统表information_schema.ROUTINES查看存储过程的详细信息 information_schema.ROUTINES 是 MySQL 数据库中一个系统表存储了所有存储过程、函数、触发器的详细信息包括名称、返回值类型、参数、创建时间、修改时间等。 select * from information_schema.routines where routine_name p1;information_schema.ROUTINES 表中的一些重要的列包括 SPECIFIC_NAME存储过程的具体名称包括该存储过程的名字参数列表。ROUTINE_SCHEMA存储过程所在的数据库名称。ROUTINE_NAME存储过程的名称。ROUTINE_TYPEPROCEDURE表示是一个存储过程FUNCTION表示是一个函数。ROUTINE_DEFINITION存储过程的定义语句。CREATED存储过程的创建时间。LAST_ALTERED存储过程的最后修改时间。DATA_TYPE存储过程的返回值类型、参数类型等。 存储过程的删除 drop procedure if exists p1;delimiter命令 在 MySQL 中delimiter 命令用于改变 MySQL 解释语句的定界符。MySQL 默认使用分号 ; 作为语句的定界符。而使用 delimiter 命令可以将分号 ; 更改为其他字符从而可以在 SQL 语句中使用分号 ;。 例如假设需要创建一个存储过程。在存储过程中通常会包括多条 SQL 语句而这些语句都需要以分号 ; 结尾。但默认情况下执行到第一条语句的分号 ; 后MySQL 就会停止解释导致后续的语句无法执行。解决方式就是使用 delimiter 命令将分号改为其他字符使分号 ; 不再是语句定界符。例如 delimiter //CREATE PROCEDURE my_proc () BEGIN SELECT * FROM my_table; INSERT INTO my_table (col1, col2) VALUES (value1, value2); END //delimiter ;在这个例子中我们使用 delimiter // 命令将定界符改为两个斜线 //。在存储过程中以分号 ; 结尾的语句不再被解释为语句的结束。而使用 delimiter ; 可以将分号恢复为语句定界符。 总之delimiter 命令可以改变 MySQL 数据库系统中 SQL 查询语句的分隔符从而可使一条 SQL 查询语句包含多个 SQL 语句。这样的话就方便了我们在一个语句里面加入多个语句而且不会被错 1.4 MySQL的变量 mysql中的变量包括系统变量、用户变量、局部变量。 系统变量 MySQL 系统变量是指在 MySQL 服务器运行时控制其行为的参数。这些变量可以被设置为特定的值来改变服务器的默认设置以满足不同的需求。 MySQL 系统变量可以具有全局global或会话session作用域。 全局作用域是指对所有连接和所有数据库都适用会话作用域是指只对当前连接和当前数据库适用。 查看系统变量 show [global|session] variables;show [global|session] variables like ;select [global|session.]系统变量名;注意没有指定session或global时默认是session。 设置系统变量 set [global | session] 系统变量名 值;set [global | session.]系统变量名 值;注意无论是全局设置还是会话设置当mysql服务重启之后之前配置都会失效。可以通过修改MySQL根目录下的my.ini配置文件达到永久修改的效果。my.ini是MySQL数据库默认的系统级配置文件默认是不存在的需要新建并参考一些资料进行配置。 windows系统是my.ini linux系统是my.cnf my.ini文件通常放在mysql安装的根目录下如下图 这个文件通常是不存在的可以新建新建后例如提供以下配置 [mysqld] autocommit0这种配置就表示永久性关闭自动提交机制。不建议这样做。 用户变量 用户自定义的变量。只在当前会话有效。所有的用户变量’开始。 给用户变量赋值 set name jackson; set age : 30; set gender : 男, addr : 北京大兴区; select email : jackson123.com; select sal into sal from emp where ename SMITH;读取用户变量的值 select name, age, gender, addr, email, sal;注意mysql中变量不需要声明。直接赋值就行。如果没有声明变量直接读取该变量返回null 局部变量 在存储过程中可以使用局部变量。使用declare声明。在begin和end之间有效。 变量的声明 declare 变量名 数据类型 [default …];变量的数据类型就是表字段的数据类型例如int、bigint、char、varchar、date、time、datetime等。 注意declare通常出现在begin end之间的开始部分。 变量的赋值 set 变量名 值; set 变量名 : 值; select 字段名 into 变量名 from 表名 …;例如以下程序演示局部变量的声明、赋值、读取 create PROCEDURE p2() begin /声明变量/declare emp_count int default 0;/声明变量/declare sal double(10,2) default 0.0;/给变量赋值/select count(*) into emp_count from emp;/给变量赋值/set sal : 5000.0;/读取变量的值/select emp_count;/读取变量的值/select sal; end;call p2();1.5 if语句 语法格式 if 条件 then …… elseif 条件 then …… elseif 条件 then …… else …… end if;案例员工月薪sal超过10000的属于“高收入”6000到10000的属于“中收入”少于6000的属于“低收入”。 create procedure p3( ) begindeclare sal int default 5000;declare grade varchar(20);if sal 10000 thenset grade : 高收入;elseif sal 6000 thenset grade : 中收入;elseset grade : 低收入;end if;select grade; end;call p3();1.6 参数 存储过程的参数包括三种形式 in入参未指定时默认是inout出参inout既是入参又是出参 不管入参还是出参都是变量 案例员工月薪sal超过10000的属于“高收入”6000到10000的属于“中收入”少于6000的属于“低收入”。 create procedure p4(in sal int, out grade varchar(20)) beginif sal 10000 thenset grade : 高收入;elseif sal 6000 thenset grade : 中收入;elseset grade : 低收入;end if; end;call p4(5000, grade); select grade;案例将传入的工资sal上调10% create procedure p5(inout sal int) beginset sal : sal * 1.1; end;set sal : 10000; call p5(sal); select sal;1.7 case语句 语法格式 case 值when 值1 then……when 值2 then……when 值3 then……else…… end case;casewhen 条件1 then……when 条件2 then……when 条件3 then……else…… end case;案例根据不同月份输出不同的季节。3 4 5月份春季。6 7 8月份夏季。9 10 11月份秋季。12 1 2 冬季。其他非法。 create procedure mypro(in month int, out result varchar(100)) begin case monthwhen 3 then set result : 春季;when 4 then set result : 春季;when 5 then set result : 春季;when 6 then set result : 夏季;when 7 then set result : 夏季;when 8 then set result : 夏季;when 9 then set result : 秋季;when 10 then set result : 秋季;when 11 then set result : 秋季;when 12 then set result : 冬季;when 1 then set result : 冬季;when 2 then set result : 冬季;else set result : 非法月份;end case; end;create procedure mypro(in month int, out result varchar(100)) begin case when month 3 or month 4 or month 5 then set result : 春季;when month 6 or month 7 or month 8 then set result : 夏季;when month 9 or month 10 or month 11 then set result : 秋季;when month 12 or month 1 or month 2 then set result : 冬季;else set result : 非法月份;end case; end;call mypro(9, season); select season;1.8 while循环 语法格式 while 条件 do循环体; end while;案例传入一个数字n计算1~n中所有偶数的和。 create procedure mypro(in n int) begindeclare sum int default 0;while n 0 doif n % 2 0 thenset sum : sum n;end if;set n : n - 1;end while;select sum; end;call mypro(10);1.9 repeat循环 语法格式 repeat循环体;until 条件 end repeat;注意条件成立时结束循环。 案例传入一个数字n计算1~n中所有偶数的和。 create procedure mypro(in n int, out sum int) begin set sum : 0;repeat if n % 2 0 then set sum : sum n;end if;set n : n - 1;until n 0end repeat; end;call mypro(10, sum); select sum;1.10 loop循环 语法格式 create procedure mypro() begin declare i int default 0;mylp:loop set i : i 1;if i 5 then leave mylp;end if;select i;end loop; end;create procedure mypro() begin declare i int default 0;mylp:loop set i : i 1;if i 5 then iterate mylp;end if;if i 10 then leave mylp;end if;select i;end loop; end;1.11 游标cursor 游标cursor可以理解为一个指向结果集中某条记录的指针允许程序逐一访问结果集中的每条记录并对其进行逐行操作和处理。 使用游标时需要在存储过程或函数中定义一个游标变量并通过 DECLARE 语句进行声明和初始化。然后使用 OPEN 语句打开游标使用 FETCH 语句逐行获取游标指向的记录并进行处理。最后使用 CLOSE 语句关闭游标释放相关资源。游标可以大大地提高数据库查询的灵活性和效率。 声明游标的语法 declare 游标名称 cursor for 查询语句;打开游标的语法 open 游标名称;通过游标取数据的语法 fetch 游标名称 into 变量[,变量,变量……]关闭游标的语法 close 游标名称;案例从dept表查询部门编号和部门名创建一张新表dept2将查询结果插入到新表中。 drop procedure if exists mypro;create procedure mypro() begin declare no int;declare name varchar(100);declare dept_cursor cursor for select deptno,dname from dept;drop table if exists dept2;create table dept2(no int primary key,name varchar(100));open dept_cursor;while true dofetch dept_cursor into no, name;insert into dept2(no,name) values(no,name);end while;close dept_cursor; end;call mypro();执行结果 出现了异常异常信息中显示没有数据了。这是因为while true循环导致的。 不过虽然出现了异常但是表创建成功了数据也插入成功了 注意声明局部变量和声明游标有顺序要求局部变量的声明需要在游标声明之前完成。 1.12 捕捉异常并处理 语法格式 DECLARE handler_name HANDLER FOR condition_value action_statementhandler_name 表示异常处理程序的名称重要取值包括 CONTINUE发生异常后程序不会终止会正常执行后续的过程。(捕捉)EXIT发生异常后终止存储过程的执行。上抛 condition_value 是指捕获的异常重要取值包括 SQLSTATE sqlstate_value例如SQLSTATE ‘02000’SQLWARNING代表所有01开头的SQLSTATENOT FOUND代表所有02开头的SQLSTATESQLEXCEPTION代表除了01和02开头的所有SQLSTATE action_statement 是指异常发生时执行的语句例如CLOSE cursor_name 给之前的游标添加异常处理机制 drop procedure if exists mypro;create procedure mypro() begin declare no int;declare name varchar(100);declare dept_cursor cursor for select deptno,dname from dept;declare exit handler for not found close dept_cursor;drop table if exists dept2;create table dept2(no int primary key,name varchar(100));open dept_cursor;while true dofetch dept_cursor into no, name;insert into dept2(no,name) values(no,name);end while;close dept_cursor; end;call mypro();1.13 存储函数 存储函数带返回值的存储过程。参数只允许是in但不能写显示的写in。没有out也没有inout。 语法格式 CREATE FUNCTION 存储函数名称(参数列表) RETURNS 数据类型 [特征] BEGIN–函数体RETURN …; END;“特征”的可取重要值如下 deterministic用该特征标记该函数为确定性函数什么是确定性函数每次调用函数时传同一个参数的时候返回值都是固定的。这是一种优化策略这种情况下整个函数体的执行就会省略了直接返回之前缓存的结果来提高函数的执行效率。no sql用该特征标记该函数执行过程中不会查询数据库如果确实没有查询语句建议使用。告诉 MySQL 优化器不需要考虑使用查询缓存和优化器缓存来优化这个函数这样就可以避免不必要的查询消耗产生从而提高性能。reads sql data用该特征标记该函数会进行查询操作告诉 MySQL 优化器这个函数需要查询数据库的数据可以使用查询缓存来缓存结果从而提高查询性能同时 MySQL 还会针对该函数的查询进行优化器缓存处理。 案例计算1~n的所有偶数之和 – 删除函数 drop function if exists sum_fun;– 创建函数 create function sum_fun(n int) returns int deterministic begin declare result int default 0;while n 0 do if n % 2 0 then set result : result n;end if;set n : n - 1;end while;return result; end;– 调用函数 set result sum_fun(100); select result;1.14 触发器 MySQL 触发器是一种数据库对象它是与表相关联的特殊程序。它可以在特定的数据操作例如插入INSERT、更新UPDATE或删除DELETE触发时自动执行。MySQL 触发器使数据库开发人员能够在数据的不同状态之间维护一致性和完整性并且可以为特定的数据库表自动执行操作。 触发器的作用主要有以下几个方面 强制实施业务规则触发器可以帮助确保数据表中的业务规则得到强制执行例如检查插入或更新的数据是否符合某些规则。数据审计触发器可以声明在执行数据修改时自动记日志或审计数据变化的操作使数据对数据库管理员和 SQL 审计人员更易于追踪和审计。执行特定业务操作触发器可以自动执行特定的业务操作例如计算数据行的总数、计算平均值或总和等。 MySQL 触发器分为两种类型: BEFORE 和 AFTER。BEFORE 触发器在执行 INSERT、UPDATE、DELETE 语句之前执行而 AFTER 触发器在执行 INSERT、UPDATE、DELETE 语句之后执行。 创建触发器的语法如下 CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN – 触发器执行的 SQL 语句 END;其中 trigger_name触发器的名称BEFORE/AFTER触发器的类型可以是 BEFORE 或者 AFTERINSERT/UPDATE/DELETE触发器所监控的 DML 调用类型table_name触发器所绑定的表名FOR EACH ROW表示触发器在每行受到 DML 的影响之后都会执行触发器执行的 SQL 语句该语句会在触发器被触发时执行 需要注意的是触发器是一种高级的数据库功能只有在必要的情况下才应该使用例如在需要实施强制性业务规则时。过多的触发器和复杂的触发器逻辑可能会影响查询性能和扩展性。 关于触发器的NEW和OLD关键字 在 MySQL 触发器中NEW 和 OLD 是两个特殊的关键字用于引用在触发器中受到修改的行的新值和旧值。具体而言 NEW在触发 INSERT 或 UPDATE 操作期间NEW 用于引用将要插入或更新到表中的新行的值。OLD在触发 UPDATE 或 DELETE 操作期间OLD 用于引用更新或删除之前在表中的旧行的值。 通俗的讲NEW 是指触发器执行的操作所要插入或更新到当前行中的新数据而 OLD 则是指当前行在触发器执行前原本的数据。 在MySQL 触发器中NEW 和 OLD 使用方法是相似的。在触发器中可以像引用表的其他列一样引用 NEW 和 OLD。例如可以使用 OLD.column_name 从旧行中引用列值也可以使用 NEW.column_name 从新行中引用列值。 示例 假设有一个名为 my_table 的表其中包含一个名为 quantity 的列。当在该表上执行 UPDATE 操作时以下触发器会将旧值 OLD.quantity 累加到新值 NEW.quantity 中 CREATE TRIGGER my_trigger BEFORE UPDATE ON my_table FOR EACH ROW BEGIN SET NEW.quantity NEW.quantity OLD.quantity; END;在此触发器中OLD.quantity 引用原始行的 quantity 值旧值而 NEW.quantity 引用更新行的 quantity 值新值。在触发器执行期间数据行的 quantity 值将设置为旧值加上新值。 需要注意的是在使用 NEW 和 OLD 时需要根据 DML 操作的类型进行判断以确定哪个关键字表示新值哪个关键字则表示旧值。 案例当我们对dept表中的数据进行insert delete update的时候请将这些操作记录到日志表当中日志表如下 drop table if exists oper_log;create table oper_log(id bigint primary key auto_increment,table_name varchar(100) not null comment 操作的哪张表,oper_type varchar(100) not null comment 操作类型包括insert delete update,oper_time datetime not null comment 操作时间,oper_id bigint not null comment 操作的那行记录的id,oper_desc text comment 操作描述 );触发器1向dept表中插入数据时记录日志 create trigger dept_trigger_insert after insert on dept for each row begininsert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values (null,dept,insert,now(),new.deptno,concat(插入数据deptno, new.deptno, ,dname, new.dname,,loc, new.loc)); end;查看触发器 show triggers;删除触发器 drop trigger if exists dept_trigger_insert;向dept表中插入一条记录 日志表中多了一条记录
触发器2修改dept表中数据时记录日志 create trigger dept_trigger_update after update on dept for each row begininsert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values (null,dept,update,now(),new.deptno,concat(更新前deptno, old.deptno, ,dname, old.dname,,loc, old.loc, ,更新后deptno, new.deptno, ,dname, new.dname,,loc, new.loc)); end;更新一条记录 update dept set loc 北京 where deptno 60;日志表中多了一条记录 注意更新一条记录则对应一条日志。如果一次更新3条记录那么日志表中插入3条记录。 触发器3删除dept表中数据时记录日志 create trigger dept_trigger_delete after delete on dept for each row begininsert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values (null,dept,delete,now(),old.deptno,concat(删除了数据deptno, old.deptno, ,dname, old.dname,,loc, old.loc)); end;删除一条记录 delete from dept where deptno 60;日志表中多了一条记录
第2章 存储引擎 2.1 存储引擎概述 MySQL存储引擎决定了数据在磁盘上的存储方式和访问方式。不同的存储引擎实现了不同的存储和检索算法因此它们在处理和管理数据的方式上存在差异。 MySQL常见的存储引擎包括InnoDB、MyISAM、Memory、Archive等。每个存储引擎都有自己的特点和适用场景。 例如 InnoDB引擎支持事务和行级锁定适用于需要高并发读写的应用MyISAM引擎不支持事务但适用于读操作较多的应用Memory引擎数据全部存储在内存中适用于对读写速度要求很高的应用等等。基于Hash算法 选择适合的存储引擎可以提高MySQL的性能和效率并且根据应用需求来合理选择存储引擎可以提供更好的数据管理和查询功能。 2.2 MySQL支持哪些存储引擎 使用show engines \G;命令可以查看所有的存储引擎 *************************** 1. row **************************Engine: MEMORYSupport: YESComment: Hash based, stored in memory, useful for temporary tables Transactions: NOXA: NOSavepoints: NO ************************** 2. row **************************Engine: MRG_MYISAMSupport: YESComment: Collection of identical MyISAM tables Transactions: NOXA: NOSavepoints: NO ************************** 3. row **************************Engine: CSVSupport: YESComment: CSV storage engine Transactions: NOXA: NOSavepoints: NO ************************** 4. row **************************Engine: FEDERATEDSupport: NOComment: Federated MySQL storage engine Transactions: NULLXA: NULLSavepoints: NULL ************************** 5. row **************************Engine: PERFORMANCE_SCHEMASupport: YESComment: Performance Schema Transactions: NOXA: NOSavepoints: NO ************************** 6. row **************************Engine: MyISAMSupport: YESComment: MyISAM storage engine Transactions: NOXA: NOSavepoints: NO ************************** 7. row **************************Engine: InnoDBSupport: DEFAULTComment: Supports transactions, row-level locking, and foreign keys Transactions: YESXA: YESSavepoints: YES ************************** 8. row **************************Engine: ndbinfoSupport: NOComment: MySQL Cluster system information storage engine Transactions: NULLXA: NULLSavepoints: NULL ************************** 9. row **************************Engine: BLACKHOLESupport: YESComment: /dev/null storage engine (anything you write to it disappears) Transactions: NOXA: NOSavepoints: NO ************************** 10. row **************************Engine: ARCHIVESupport: YESComment: Archive storage engine Transactions: NOXA: NOSavepoints: NO ************************** 11. row ***************************Engine: ndbclusterSupport: NOComment: Clustered, fault-tolerant tables Transactions: NULLXA: NULLSavepoints: NULLSupport是Yes的表示支持该存储引擎。当前MySQL的版本是8.0.33 MySQL默认的存储引擎是InnoDB 2.3 指定和修改存储引擎 2.3.1 指定存储引擎 在MySQL中你可以在创建表时指定使用的存储引擎。通过在CREATE TABLE语句中使用ENGINE关键字你可以指定要使用的存储引擎。 以下是指定存储引擎的示例 CREATE TABLE my_table (column1 INT, column2 VARCHAR(50)) ENGINE InnoDB;在这个例子中我们创建了一个名为my_table的表并指定了使用InnoDB存储引擎。 如果你不显式指定存储引擎MySQL将使用默认的存储引擎。默认情况下MySQL 8的默认存储引擎是InnoDB。 2.3.2 修改存储引擎 在MySQL中你可以通过ALTER TABLE语句修改表的存储引擎。下面是修改存储引擎的示例 ALTER TABLE my_table ENGINE MyISAM;在这个例子中我们使用ALTER TABLE语句将my_table表的存储引擎修改为MyISAM。 请注意在修改存储引擎之前你需要考虑以下几点 修改存储引擎可能需要执行复制表的操作因此可能会造成数据的丢失或不可用。确保在执行修改之前备份你的数据。不是所有的存储引擎都支持相同的功能。要确保你选择的新存储引擎支持你应用程序所需的功能。修改表的存储引擎可能会影响到现有的应用程序和查询。确保在修改之前评估和测试所有的影响。ALTER TABLE语句可能需要适当的权限才能执行。确保你拥有足够的权限来执行修改存储引擎的操作。 总而言之修改存储引擎需要谨慎进行且需要考虑到可能的影响和风险。建议在进行修改之前进行适当的测试和备份。 2.4 常用的存储引擎及适用场景 在实际开发中以下存储引擎是比较常用的 InnoDB MySQL默认的事务型存储引擎支持ACID事务具有较好的并发性能和数据完整性支持行级锁定。还支持外键适用于大多数应用场景尤其是需要事务支持的应用。 MyISAM 是MySQL早期版本中常用的存储引擎支持全文索引和表级锁定不支持事务由于其简单性和高性能在某些特定的应用场景中会得到广泛应用如读密集的应用。 MEMORY基于Hash算法 称为HEAP是将表存储在内存中的存储引擎具有非常高的读写性能但数据会在服务器重启时丢失。适用于需要快速读写的临时数据集、缓存和临时表等场景。 CSV 将数据以纯文本格式存储的存储引擎适用于需要处理和导入/导出CSV格式数据的场景。 ARCHIVE 将数据高效地进行压缩和存储的存储引擎适用于需要长期存储大量历史数据且不经常查询的场景。
第3章 索引 3.1 什么是索引 索引是一种能够提高检索查询效率的提前排好序的数据结构。例如书的目录就是一种索引机制。索引是解决SQL慢查询的一种方式。 3.2 索引的创建和删除 主键会自动添加索引 主键字段会自动添加索引不需要程序员干涉主键字段上的索引被称为主键索引 unique约束的字段自动添加索引 unique约束的字段也会自动添加索引不需要程序员干涉这种字段上添加的索引称为唯一索引 给指定的字段添加索引 建表时添加索引 CREATE TABLE emp (…name varchar(255),…INDEX idx_name (name) ); 如果表已经创建好了后期给字段添加索引 ALTER TABLE emp ADD INDEX idx_name (name);也可以这样添加索引 create index idx_name on emp(name);删除指定字段上的索引 ALTER TABLE emp DROP INDEX idx_name;查看某张表上添加了哪些索引 show index from 表名;3.3 索引的分类 不同的存储引擎有不同的索引类型和实现 按照数据结构分类 B树 索引mysql的InnoDB存储引擎采用的就是这种索引采用 B树 的数据结构Hash 索引仅 memory 存储引擎支持采用 哈希表 的数据结构 按照物理存储分类 聚集索引索引和表中数据在一起数据存储的时候就是按照索引顺序存储的。一张表只能有一个聚集索引。非聚集索引索引和表中数据是分开的索引是独立于表空间的一张表可以有多个非聚集索引。 按照字段特性分类 主键索引primary key唯一索引unique普通索引index全文索引fulltext仅 InnoDB和MyISAM 存储引擎支持 按照字段个数分类 单列索引、联合索引也叫复合索引、组合索引
3.4 MySQL索引采用了B树数据结构 常见的树相关的数据结构包括 二叉树红黑树B树B树 区别树的高度不同。树的高度越低性能越高。这是因为每一个节点都是一次I/O 3.4.1 二叉树 有这样一张表 如果不给id字段添加索引默认进行全表扫描假设查询id10的数据那至少要进行10次磁盘IO。效率低。可以给id字段添加索引假设该索引使用了二叉树这种数据结构这个二叉树是这样的推荐一个数据结构可视化网站Data Structure Visualizations是旧金山大学USFCA的一个网站https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 如果这个时候要找id10的数据需要的IO次数是4次。效率显著提升了。 但是MySQL并没有直接使用这种普通的二叉树这种普通二叉树在数据极端的情况下效率较低。比如下面的数据 如果给id字段添加索引并且该索引底层使用了普通二叉树这棵树会是这样的 你虽然使用了二叉树但这更像一个链表。查找效率等同于链表查询O(n)【查找算法的时间复杂度是线性的】。查找效率极低。 因此对于MySQL来说它并没有选择这种数据结构作为索引。 3.4.2 红黑树自平衡二叉树 通过自旋平衡规则进行旋转子节点会自动分叉为2个分支从而减少树的高度当数据有序插入时比二叉树数据检索性能更好。 例如有以下数据 给id字段添加索引并且该索引使用了红黑树数据结构那么会是这样 如果查找id10的数据磁盘IO次数为5次。效率比普通二叉树要高一些。 但是如果数据量庞大例如500万条数据也会导致树的高度很高磁盘IO次数仍然很多查询效率也会比较低。 因此MySQL并没有使用红黑树这种数据结构作为索引。 3.4.3 B TreesB树 B Trees首先是一个自平衡的。 B Trees每个节点下的子节点数量 2。 B Trees每个节点中也不是存储单个数据可以存储多个数据。 B Trees又称为平衡多路查找树。 B Trees分支的数量不是2是大于2具体是多少个分支由阶决定。例如 3阶的B Trees一个节点下最多有3个子节点每个节点中最多有2个数据。4阶的B Trees一个节点下最多有4个子节点每个节点中最多有3个数据。5阶5, 46阶6, 5…16阶16, 15【MySQL采用了16阶】 采用B Trees你会发现相同的数据量B Tree 树的高度更低。磁盘IO次数更少。 3阶的B Trees 假设id字段添加了索引并且采用了B Trees数据结构查找id10的数据只需要3次磁盘IO。 4阶的B Trees
更加详细的存储是这样的请看下图 在B Trees中每个节点不仅存储了索引值还存储该索引值对应的数据行。 并且每个节点中的p1 p2 p3是指向下一个节点的指针。 B Trees数据结构存在的缺点是不适合做区间查找对于区间查找效率较低。假设要查id在[3~7]之间的需要查找的是3,4,5,6,7。那么查这每个索引值都需要从头节点开始。 因此MySQL使用了B Trees解决了这个问题。 3.4.4 B TreesB 树★ B Trees 相较于 B Trees改进了哪些 B树将数据都存储在叶子节点中。并且叶子节点之间使用链表连接这样很适合范围查询。B树的非叶子节点上只有索引值没有数据所以非叶子节点可以存储更多的索引值这样让B树更矮更胖提高检索效率。 假设有这样一张表 B Trees方式存储的话如下图所示
经典面试题 mysql为什么选择B树作为索引的数据结构而不是B树 非叶子节点上可以存储更多的键值阶数可以更大更矮更胖磁盘IO次数少数据查询效率高。所有数据都是有序存储在叶子节点上让范围查找分组查找效率更高。数据页之间、数据记录之间采用链表链接让升序降序更加方便操作。 经典面试题 如果一张表没有主键索引那还会创建B树吗 当一张表没有主键索引时默认会使用一个隐藏的内置的聚集索引clustered index。这个聚集索引是基于表的物理存储顺序构建的通常是使用B树来实现的。 3.5 其他索引及相关调优 3.5.1 Hash索引 支持Hash索引的存储引擎有 InnoDB不支持手动创建Hash索引系统会自动维护一个自适应的Hash索引 对于InnoDB来说即使手动指定了某字段采用Hash索引最终show index from 表名的时候还是BTREE。 Memory支持Hash索引 Hash索引底层的数据结构就是哈希表。一个数组数组中每个元素是链表。和java中HashMap一样。哈希表中每个元素都是key value结构。key存储索引值value存储行指针。 原理如下 如果name字段上添加了Hash索引idx_name Hash索引长这个样子
检索原理假设 name‘孙行者’。通过哈希算法将’孙行者’转换为数组下标通过下标找链表在链表上遍历找到孙行者的行指针。 注意不同的字符串经过哈希算法得到的数组下标可能相同这叫做哈希碰撞/哈希冲突。【不过好的哈希算法应该具有很低的碰撞概率。常用的哈希算法如MD5、SHA-1、SHA-256等都被设计为尽可能减少碰撞的发生。】 Hash索引优缺点 优点只能用在等值比较中效率很高。例如name‘孙悟空’缺点不支持排序不支持范围查找。 3.5.2 聚集索引和非聚集索引 按照数据的物理存储方式不同可以将索引分为聚集索引聚簇索引和非聚集索引非聚簇索引。 存储引擎是InnoDB的主键上的索引属于聚集索引。 存储引擎是MyISAM的任意字段上的索引都是非聚集索引。 InnoDB的物理存储方式当创建一张表t_user并使用InnoDB存储引擎时会在硬盘上生成这样一个文件 t_user.ibd InnoDB data表索引 数据t_user.frm 存储表结构信息 MyISAM的物理存储方式当创建一张表t_user并使用MyISAM存储引擎时会在硬盘上生成这样一个文件 t_user.MYD 表数据t_user.MYI 表索引t_user.frm 表结构 注意从MySQL8.0开始不再生成frm文件了引入了数据字典用数据字典来统一存储表结构信息例如 information_schema.TABLES 表包含了数据库中所有表的信息例如表名、数据库名、引擎类型等information_schema.COLUMNS表包含了数据库中所有表的列信息例如列名、数据类型、默认值等 聚集索引的原理图B树叶子节点上存储了索引值 数据
非聚集索引的原理图B树叶子节点上存储了索引值 行指针
聚集索引的优点和缺点 优点聚集索引将数据存储在索引树的叶子节点上。可以减少一次查询因为查询索引树的同时可以获取数据。缺点对数据进行修改或删除时需要更新索引树会增加系统的开销。 3.5.3 二级索引 二级索引也属于非聚集索引。也有人把二级索引称为辅助索引。 有表t_userid是主键。age是非主键。在age字段上添加的索引称为二级索引。所有非主键索引都是二级索引 二级索引的数据结构
二级索引的查询原理 假设查询语句为 select * from t_user where age 30;为什么会“回表”因为使用了select * 避免“回表【回到原数据表】”是提高SQL执行效率的手段。例如select id from t_user where age 30; 这样的SQL语句是不需要回表的。 3.5.4 覆盖索引 覆盖索引Covering Index顾名思义是指某个查询语句可以通过索引的覆盖来完成而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时查询需要的所有列都可以从索引中提取到而不需要再去查询实际数据行获取查询所需数据。 当使用覆盖索引时MySQL可以直接通过索引也就是索引上的数据来获取所需的结果而不必再去查找表中的数据。这样可以显著提高查询性能。 假设有一个用户表user包含以下列id, username, email, age。 常见的查询是根据用户名查询用户的邮箱。如果为了提高这个查询的性能可以创建一个覆盖索引包含username, email这两列。 创建覆盖索引的SQL语句可以如下 CREATE INDEX idx_user_username_email ON user (username, email);当执行以下查询时 SELECT email FROM user WHERE username lucy;MySQL可以直接使用覆盖索引idx_user_username_email来获取查询结果而不必再去查找用户表中的数据。这样可以减少磁盘I/O并提高查询效率。而如果没有覆盖索引MySQL会先使用索引username来找到匹配的行然后再回表查询获取邮箱这个过程会增加更多的磁盘I/O和查询时间。 值得注意的是覆盖索引的创建需要考虑查询的字段选择。如果查询需要的字段较多可能需要创建包含更多列的覆盖索引以满足完全覆盖查询的需要。 覆盖索引具有以下优点 提高查询性能覆盖索引能够满足查询的所有需求同时不需要访问表中的实际数据行从而可以提高查询性能。这是因为DBMS可以直接使用索引来执行查询而不需要从磁盘读取实际的数据行。减少磁盘和内存访问次数当使用覆盖索引时DBMS不需要访问实际的数据行。这样可以减少磁盘和内存访问次数从而提高查询性能。减少网络传输由于在覆盖索引中可以存储所有查询所需的列因此可以减少数据的网络传输次数从而提高查询的性能。可以降低系统开销在高压力的数据库系统中使用覆盖索引可以减少系统开销从而提高系统的可靠性和可维护性。 覆盖索引的缺点包括 需要更多的内存覆盖索引需要存储查询所需的所有列因此需要更多的内存来存储索引。在大型数据库系统中这可能会成为一项挑战。会使索引变得庞大当索引中包含了许多列时它们可能会使索引变得非常庞大从而影响查询性能并且可能会占用大量的磁盘空间。只有在查询中包含了索引列时才能使用只有当查询中包含了所有的索引列时才能使用覆盖索引。如果查询中包含了其他列DBMS仍然需要访问实际的数据行并且无法使用覆盖索引提高查询性能。 3.5.5 索引下推 索引下推Index Condition Pushdown是一种 MySQL 中的优化方法它可以将查询中的过滤条件下推到索引层级中处理从而减少回表次数优化查询性能。 具体来说在使用索引下推时MySQL 会在索引的叶节点层级执行查询的过滤条件过滤掉无用的索引记录仅返回符合条件的记录的主键这样就可以避免查询时回表读取表格的数据行从而缩短了整个查询过程的时间。 假设有以下表结构 表名users idnameagecity1John25New York2Alice30London3Bob40Paris4Olivia35Berlin5Michael28Sydney 现在我们创建了一个多列索引索引下推通常是基于多列索引的。 ALTER TABLE users ADD INDEX idx_name_city_age (name, city, age);假设我们要查询年龄大于30岁并且所在城市是London的用户假设只给age字段添加了索引它就不会使用索引下推。传统的查询优化器会将所有满足年龄大于30岁的记录读入内存然后再根据城市进行筛选。 使用索引下推优化后在索引范围扫描的过程中优化器会判断只有在城市列为London的情况下才会将满足年龄大于30岁的记录加载到内存中。这样就可以避免不必要的IO和数据传输提高查询性能。 具体的查询语句可以是 SELECT * FROM users WHERE age 30 AND city London;在执行这个查询时优化器会使用索引下推技术先根据索引范围扫描找到所有满足条件的记录然后再回到原数据表中获取完整的行数据最终返回结果。 3.5.6 单列索引单一索引 单列索引是指对数据库表中的某一列或属性进行索引创建对该列进行快速查找和排序操作。单列索引可以加快查询速度提高数据库的性能。 举个例子假设我们有一个学生表student其中有以下几个列学生编号student_id、姓名name、年龄age和性别gender。 如果我们针对学生表的学生编号student_id列创建了单列索引那么可以快速地根据学生编号进行查询或排序操作。例如我们可以使用以下SQL语句查询学生编号为123456的学生信息 SELECT * FROM student WHERE student_id 123456;由于我们对学生编号列建立了单列索引所以数据库可以直接通过索引快速定位到具有学生编号123456的那一行记录从而加快查询速度。 3.5.7 复合索引组合索引 复合索引Compound Index也称为多列索引Multi-Column Index是指对数据库表中多个列进行索引创建。 与单列索引不同复合索引可以包含多个列。这样可以将多个列的值组合起来作为索引的键以提高多列条件查询的效率。 举个例子假设我们有一个订单表Order其中包含以下几个列订单编号OrderID、客户编号CustomerID、订单日期OrderDate和订单金额OrderAmount。 如果我们为订单表的客户编号和订单日期这两列创建复合索引CustomerID, OrderDate那么可以在查询时同时根据客户编号和订单日期来快速定位到匹配的记录。 例如我们可以使用以下SQL语句查询客户编号为123456且订单日期为2021-01-01的订单信息 SELECT * FROM Order WHERE CustomerID 123456 AND OrderDate 2021-01-01;由于我们为客户编号和订单日期创建了复合索引数据库可以使用这个索引来快速定位到符合条件的记录从而加快查询速度。复合索引的使用能够提高多列条件查询的效率但需要注意的是复合索引的创建和维护可能会增加索引的存储空间和对于写操作的影响。 相对于单列索引复合索引有以下几个优势 减少索引的数量复合索引可以包含多个列因此可以减少索引的数量减少索引的存储空间和维护成本。提高查询性能当查询条件中涉及到复合索引的多个列时数据库可以使用复合索引进行快速定位和过滤从而提高查询性能。覆盖查询如果复合索引包含了所有查询需要的列那么数据库可以直接使用索引中的数据而不需要再进行表的读取从而提高查询性能。排序和分组由于复合索引包含多个列因此可以用于排序和分组操作从而提高排序和分组的性能。 3.6 索引的优缺点 索引是数据库中一种重要的数据结构用于加速数据的检索和查询操作。它的优点和缺点如下 优点 提高查询性能通过创建索引可以大大减少数据库查询的数据量从而提升查询的速度。加速排序当查询需要按照某个字段进行排序时索引可以加速排序的过程提高排序的效率。减少磁盘IO索引可以减少磁盘IO的次数这对于磁盘读写速度较低的场景尤其重要。 缺点 占据额外的存储空间索引需要占据额外的存储空间特别是在大型数据库系统中索引可能占据较大的空间。增删改操作的性能损耗每次对数据表进行插入、更新、删除等操作时需要更新索引会导致操作的性能降低。资源消耗较大索引需要占用内存和CPU资源特别是在大规模并发访问的情况下可能对系统的性能产生影响。 3.7 何时用索引 在以下情况下建议使用索引 频繁执行查询操作的字段如果这些字段经常被查询使用索引可以提高查询的性能减少查询的时间。大表当表的数据量较大时使用索引可以快速定位到所需的数据提高查询效率。需要排序或者分组的字段在对字段进行排序或者分组操作时索引可以减少排序或者分组的时间。外键关联的字段在进行表之间的关联查询时使用索引可以加快关联查询的速度。 在以下情况下不建议使用索引 频繁执行更新操作的表如果表经常被更新数据使用索引可能会降低更新操作的性能因为每次更新都需要维护索引。小表对于数据量较小的表使用索引可能并不会带来明显的性能提升反而会占用额外的存储空间。对于唯一性很差的字段一般不建议添加索引。当一个字段的唯一性很差时查询操作基本上需要扫描整个表的大部分数据。如果为这样的字段创建索引索引的大小可能会比数据本身还大导致索引的存储空间占用过高同时也会导致查询操作的性能下降。 总之索引需要根据具体情况进行使用和权衡需要考虑到表的大小、查询频率、更新频率以及业务需求等因素。 第4章 MySQL优化 4.1 MySQL优化手段 MySQL数据库的优化手段通常包括但不限于 SQL查询优化这是最低成本的优化手段通过优化查询语句、适当添加索引等方式进行。并且效果显著。库表结构优化通过规范化设计、优化索引和数据类型等方式进行库表结构优化需要对数据库结构进行调整和改进系统配置优化根据硬件和操作系统的特点调整最大连接数、内存管理、IO调度等参数硬件优化升级硬盘、增加内存容量、升级处理器等硬件方面的投入需要购买和替换硬件设备成本较高 我们主要掌握SQL查询优化 4.2 SQL性能分析工具 4.2.1 查看数据库整体情况 通过以下命令可以查看当前数据库在SQL语句执行方面的整体情况 show global status like Com_select; show global status like Com_insert; show global status like Com_delete; show global status like Comupdate;show global status like Com______;这些结果反映了从 MySQL 服务器启动到当前时刻所有的 SELECT 查询总数。对于 MySQL 性能优化来说通过查看 Com_select 的值可以了解 SELECT 查询在整个 MySQL 服务期间所占比例的情况 如果 Com_select 次数过高可能说明查询表中的每条记录都会返回过多的字段。表示该数据库是属于读密集型的数据库如果 Com_select 次数很少同时insert或delete或update的次数很高可能说明服务器运行的应用程序过于依赖写入操作和少量读取操作。表示该数据库是属于写密集型的数据库 总之通过查看 Com_select 的值可以了解 MySQL 服务器的长期执行情况并在优化查询性能时帮助我们了解 MySQL 的性能瓶颈。 4.2.2 慢查询日志 慢查询日志文件可以将查询较慢的DQL语句记录下来便于我们定位需要调优的select语句。 通过以下命令查看慢查询日志功能是否开启 show variables like slow_query_log;慢查询日志功能默认是关闭的。请修改my.ini文件来开启慢查询日志功能在my.ini的[mysqld]后面添加如下配置 注意slow_query_log1表示开启慢查询日志功能long_query_time3表示只要SELECT语句的执行耗时超过3秒则将其记录到慢查询日志中。 重启mysql服务。再次查看是否开启慢查询日志功能
尝试执行一条时长超过3秒的select语句 select empno,ename,sleep(4) from emp where enamesmith;慢查询日志文件默认存储在C:\dev\mysql-8.0.36-winx64\data 目录下默认的名字是计算机名-slow.log 通过该文件可以清晰的看到哪些DQL语句属于慢查询
4.2.3 show profiles 通过show profiles可以查看一个SQL语句在执行过程中具体的耗时情况。帮助我们更好的定位问题所在。 查看当前数据库是否支持 profile操作 select have_profiling;查看 profiling 开关是否打开 select profiling;将 profiling 开关打开 set profiling 1;可以执行多条DQL语句然后使用 show profiles; 来查看当前数据库中执行过的每个SELECT语句的耗时情况。 select empno,ename from emp; select empno,ename from emp where empno7369; select count(*) from emp; show profiles;查看某个SQL语句语句在执行过程中每个阶段的耗时情况 show profile for query 4;想查看执行过程中cpu的情况可以执行以下命令 show profile cpu for query 4;4.2.4 explain explain命令可以查看一个DQL语句的执行计划根据执行计划可以做出相应的优化措施。提高执行效率。 explain select * from emp where empno7369;id id反映出一条select语句执行顺序id越大优先级越高。id相同则按照自上而下的顺序执行。 explain select e.ename,d.dname from emp e join dept d on e.deptnod.deptno join salgrade s on e.sal between s.losal and s.hisal;由于id相同反映出三张表在执行顺序上属于平等关系执行时采用先d再e最后s。 explain select e.ename,d.dname from emp e join dept d on e.deptnod.deptno where e.sal(select sal from emp where enameford);反映出先执行子查询然后让e和d做表连接。 select_type 反映了mysql查询语句的类型。常用值包括 SIMPLE表示查询中不包含子查询或UNION操作。这种查询通常包括一个表或是最多一个联接JOINPRIMARY表示当前查询是一个主查询。主要的查询UNION表示查询中包含UNION操作SUBQUERY子查询DERIVED派生表表示查询语句出现在from后面 table 反映了这个查询操作的是哪个表。 type 反映了查询表中数据时的访问类型常见的值 NULL效率最高一般不可能优化到这个级别只有查询时没有查询表的时候访问类型是NULL。例如select 1;system通常访问系统表的时候访问类型是system。一般也很难优化到这个程度。const根据主键或者唯一性索引查询索引值是常量值时。explain select * from emp where empno7369;eq_ref根据主键或者唯一性索引查询。索引值不是常量值。ref使用了非唯一的索引进行查询。range使用了索引扫描了索引树的一部分。index表示用了索引但是也需要遍历整个索引树。all全表扫描 效率最高的是NULL效率最低的是all从上到下从高到低。 possible_keys 这个查询可能会用到的索引 key 实际用到的索引 key_len 反映索引在查询中使用的列所占的总字节数。 rows 查询扫描的预估计行数。 Extra 给出了与查询相关的额外信息和说明。这些额外信息可以帮助我们更好地理解查询执行的过程。 4.3 索引优化 加索引 vs 不加索引 将这个sql脚本初始化到数据库中初始化100W条记录t_vip.sql 根据id查询id是主键有索引 select * from t_vip where id 900000;根据name查询name上没有索引 select * from t_vip where name4c6494cb;给name字段添加索引 create index idx_t_user_name on t_vip(name);再次根据name查询此时name上已经有索引了
select * from t_vip where name4c6494cb;最左前缀原则★ 假设有这样一张表 create table t_customer(id int primary key auto_increment,name varchar(255),age int,gender char(1),email varchar(255) );添加了这些数据 insert into t_customer values(null, zhangsan, 20, M, zhangsan123.com); insert into t_customer values(null, lisi, 22, M, lisi123.com); insert into t_customer values(null, wangwu, 18, F, wangwu123.com); insert into t_customer values(null, zhaoliu, 22, F, zhaoliu123.com); insert into t_customer values(null, jack, 30, M, jack123.com);添加了这样的复合索引 create index idx_name_age_gender on t_customer(name,age,gender);最左前缀原则当查询语句条件中包含了这个复合索引最左边的列 name 时此时索引才会起作用。 验证1 explain select * from t_customer where namezhangsan and age20 and genderM;验证结果完全使用了索引
验证2 explain select * from t_customer where namezhangsan and age20;验证结果使用了部分索引
验证3 explain select * from t_customer where namezhangsan;验证结果使用了部分索引
验证4 explain select * from t_customer where age20 and genderM and namezhangsan;验证结果完全使用了索引
验证5 explain select * from t_customer where genderM and age20;验证结果没有使用任何索引
验证6 explain select * from t_customer where namezhangsan and genderM;验证结果使用了部分索引
验证7 explain select * from t_customer where namezhangsan and genderM and age20;验证结果完全使用了索引
范围查询时在“范围条件”右侧的列索引会失效 验证 explain select * from t_customer where namezhangsan and age20 and genderM;验证结果name和age列索引生效。gender列索引无效。 怎么解决建议范围查找时带上“” explain select * from t_customer where namezhangsan and age20 and genderM;索引失效情况★ 有这样一张表 create table t_emp(id int primary key auto_increment,name varchar(255),sal int,age char(2) );有这样一些数据 insert into t_emp values(null, 张三, 5000,20); insert into t_emp values(null, 张飞, 4000,30); insert into t_emp values(null, 李飞, 6000,40);有这样一些索引 create index idx_t_emp_name on t_emp(name); create index idx_t_emp_sal on t_emp(sal); create index idx_t_emp_age on t_emp(age);索引列参加了运算索引失效 explain select * from t_emp where sal 5000;验证结果使用了索引
explain select * from t_emp where sal*10 50000;验证结果索引失效
索引列进行模糊查询时以 % 开始的索引失效 explain select * from t_emp where name like 张%;验证结果索引有效
explain select * from t_emp where name like %飞;验证结果索引失效
索引列是字符串类型但查询时省略了单引号索引失效 explain select * from t_emp where age20;验证结果索引有效
explain select * from t_emp where age20;验证结果索引失效
查询条件中有or只要有未添加索引的字段索引失效 explain select * from t_emp where name张三 or sal5000;验证结果使用了索引
将t_emp表sal字段上的索引删除 alter table t_emp drop index idx_t_emp_sal;再次验证 explain select * from t_emp where name张三 or sal5000;验证结果索引失效
当查询的符合条件的记录在表中占比较大索引失效 复制一张新表emp2 create table emp2 as select * from emp;给sal添加索引 alter table emp2 add index idx_emp2_sal(sal);验证1 explain select * from emp2 where sal 800;不走索引
验证2 explain select * from emp2 where sal 1000;不走索引
验证3 explain select * from emp2 where sal 2000;走索引
关于is null和is not null的索引失效问题 给emp2的comm字段添加一个索引 create index idx_emp2_comm on emp2(comm);将emp2表的comm字段值全部更新为NULL update emp2 set commnull;验证此时条件使用is null是否走索引 explain select * from emp2 where comm is null;验证结果不走索引。 验证此时条件使用is not null是否走索引
将emp2表的comm字段全部更新为非NULL update emp2 set comm100;验证此时条件使用is null是否走索引 explain select * from emp2 where comm is null;验证结果走索引 验证此时条件使用is not null是否走索引 explain select * from emp2 where comm is not null;验证结果不走索引
结论走索引还是不走索引根数据分布有很大关系如果符合条件的记录占比较大会考虑使用全表扫描而放弃走索引。 指定索引 当一个字段上既有单列索引又有复合索引时我们可以通过以下的SQL提示来要求该SQL语句执行时采用哪个索引 use index(索引名称)建议使用该索引只是建议底层mysql会根据实际效率来考虑是否使用你推荐的索引。ignore index(索引名称)忽略该索引force index(索引名称)强行使用该索引 查看 t_customer 表上的索引 show index from t_customer;可以看到name age gender三列添加了一个复合索引。 现在给name字段添加一个单列索引 create index idx_name on t_customer(name);看看以下的语句默认使用了哪个索引 explain select * from t_customer where namezhangsan;通过测试得知默认使用了联合索引。 如何建议使用单列索引idx_name explain select * from t_customer use index(idx_name) where namezhangsan;如何忽略使用符合索引 idx_name_age_gender explain select * from t_customer ignore index(idx_name_age_gender) where namezhangsan;如何强行使用单列索引idx_name explain select * from t_customer force index(idx_name) where namezhangsan;覆盖索引★ 覆盖索引我们在讲解索引的时候已经提到过了覆盖索引强调的是在select后面写字段的时候这些字段尽可能是索引所覆盖的字段这样可以避免回表查询。尽可能避免使用 select *因为select * 很容易导致回表查询。本质就是能在索引上检索的就不要再次回表查询了。 例如有一张表 emp3其中 ename,job添加了联合索引idx_emp3_ename_job以下这个select语句就不会回表 drop table if exists emp3; create table emp3 as select * from emp; alter table emp3 add constraint emp3_pk primary key(empno); create index idx_emp3_ename_job on emp3(ename,job); explain select empno,ename,job from emp3 where enameKING;如果查询语句要查找的列没有在索引中则会回表查询例如 explain select empno,ename,job,sal from emp3 where enameKING;面试题t_user表字段如下id,name,password,realname,birth,email。表中数据量500万条请针对以下SQL语句给出优化方案 select id,name,realname from t_user where name鲁智深;如果只给name添加索引底层会进行大量的回表查询效率较低建议给name和realname两个字段添加联合索引这样大大减少回表操作提高查询效率。 前缀索引★ 如果一个字段类型是varchar或text字段字段中存储的是文本或者大文本直接对这种长文本创建索引会让索引体积很大怎么优化呢可以将字符串的前几个字符截取下来当做索引来创建。这种索引被称为前缀索引例如 drop table if exists emp4; create table emp4 as select * from emp; create index idx_emp4_ename_2 on emp4(ename(2));以上SQL表示将emp4表中ename字段的前2个字符创建到索引当中。 使用前缀索引时需要通过以下公式来确定使用前几个字符作为索引 select count(distinct substring(ename,1,前几个字符)) / count() from emp4;以上查询结果越接近1表示索引的效果越好。原理做索引值的话索引值越具有唯一性效率越高 假设我们使用前1个字符作为索引值 select count(distinct substring(ename,1,1)) / count() from emp4;假设我们使用前2个字符作为索引值 select count(distinct substring(ename,1,2)) / count(*) from emp4;可见使用前2个字符作为索引值能够让索引值更具有唯一性效率越好因此我们选择前2个字符作为前缀索引。 create index idx_emp4_ename_2 on emp4(ename(2));执行以下的查询语句则会走这个前缀索引 explain select * from emp4 where enameKING;单列索引和复合索引怎么选择 当查询语句的条件中有多个条件建议将这几个列创建为复合索引因为创建单列索引很容易回表查询。 例如分别给emp5表enamejob添加两个单列索引 create table emp5 as select * from emp; alter table emp5 add constraint emp5_pk primary key(empno);create index idx_emp5_ename on emp5(ename); create index idx_emp5_job on emp5(job);执行以下查询语句 explain select empno,ename,job from emp5 where enameSMITH and jobCLERK;ename和job都出现在查询条件中可以给emp6表的ename和job创建一个复合索引 create table emp6 as select * from emp; alter table emp6 add constraint emp6_pk primary key(empno);create index idx_emp6_ename_job on emp6(ename,job); explain select empno,ename,job from emp6 where enameSMITH and jobCLERK;对于以上查询语句使用复合索引避免了回表因此这种情况下还是建议使用复合索引。 注意创建索引时应考虑最左前缀原则主字段并且具有很强唯一性的字段建议排在第一位例如 create index idx_emp_ename_job on emp(ename,job);和以下方式对比 create index idx_emp_job_ename on emp(job,ename);由于ename是主字段并且ename具有很好的唯一性建议将ename列放在最左边。因此这两种创建复合索引的方式建议采用第一种。 复合索引底层原理
索引创建原则 表数据量庞大通常超过百万条数据。经常出现在whereorder bygroup by后面的字段建议添加索引。创建索引的字段尽量具有很强的唯一性。如果字段存储文本内容较大一定要创建前缀索引。尽量使用复合索引使用单列索引容易回表查询。如果一个字段中的数据不会为NULL建议建表时添加not null约束这样优化器就知道使用哪个索引列更加有效。不要创建太多索引当对数据进行增删改的时候索引需要重新重新排序。如果很少的查询经常的增删改不建议加索引。 4.4 SQL优化 order by的优化 准备数据 drop table if exists workers;create table workers(id int primary key auto_increment,name varchar(255),age int,sal int );insert into workers values(null, 孙悟空, 500, 50000); insert into workers values(null, 猪八戒, 300, 40000); insert into workers values(null, 沙和尚, 600, 40000); insert into workers values(null, 白骨精, 600, 10000);explain查看一个带有order by的语句时Extra列会显示using index 或者 using filesort区别是什么 using index: 表示使用索引因为索引是提前排好序的。效率很高。using filesort表示使用文件排序这就表示没有走索引对表中数据进行排序排序时将硬盘的数据读取到内存当中在内存当中排好序。这个效率是低的应避免。 此时name没有添加索引如果根据name进行排序的话 explain select id,name from workers order by name;显然这种方式效率较低。 给name添加索引 create index idx_workers_name on workers(name);再根据name排序 explain select id,name from workers order by name;这样效率则提升了。 如果要通过age和sal两个字段进行排序最好给age和sal两个字段添加复合索引不添加复合索引时 按照age升序排如果age相同则按照sal升序 explain select id,age,sal from workers order by age,sal;这样效率是低的。 给age和sal添加复合索引 create index idx_workers_age_sal on workers(age, sal);先按照age升序排如果age相同则按照sal升序 explain select id,age,sal from workers order by age,sal;这样效率提升了。 在B树上叶子结点上的所有数据默认是按照升序排列的如果按照age降序如果age相同则按照sal降序会走索引吗 explain select id,age,sal from workers order by age desc,sal desc;可以看到备注信息是反向索引扫描使用了索引。 这样效率也是很高的因为B树叶子结点之间采用的是双向指针。可以从左向右升序也可以从右向左降序。 如果一个升序一个降序会怎样呢 explain select id,age,sal from workers order by age asc, sal desc;可见age使用了索引但是sal没有使用索引。怎么办呢可以针对这种排序情况创建对应的索引来解决 create index idx_workers_ageasc_saldesc on workers(age asc, sal desc);创建的索引如下A表示升序D表示降序。 再次执行 explain select id,age,sal from workers order by age asc, sal desc;我们再来看看对于排序来说是否支持最左前缀法则 explain select id,age,sal from workers order by sal;通过测试得知order by也遵循最左前缀法则。 我们再来看一下未使用覆盖索引会怎样 explain select * from workers order by age,sal;通过测试得知排序也要尽量使用覆盖索引。 order by 优化原则总结 排序也要遵循最左前缀法则。使用覆盖索引。针对不同的排序规则创建不同索引。如果所有字段都是升序或者所有字段都是降序则不需要创建新的索引如果无法避免filesort要注意排序缓存的大小默认缓存大小256KB可以修改系统变量 sort_buffer_size
show variables like sort_buffer_size;group by优化 创建empx表 create table empx as select * from emp;job字段上没有索引根据job进行分组查看每个工作岗位有多少人 select job,count() from empx group by job;看看是否走索引了 explain select job,count() from empx group by job;使用了临时表效率较低。 给job添加索引 create index idx_empx_job on empx(job);再次执行 explain select job,count() from empx group by job;效率提升了。 我们再来看看group by是否需要遵守最左前缀法则给deptno和sal添加复合索引 create index idx_empx_deptno_sal on empx(deptno, sal);根据部门编号分组查看每个部门人数 explain select deptno,count() from empx group by deptno;效率很高因为deptno是复合索引中最左边的字段。 根据sal分组查看每个工资有多少人 explain select sal, count() from empx group by sal;使用了临时表效率较低。 通过测试得知group by也同样遵循最左前缀法则。 我们再来测试一下如果将部门编号deptno复合索引的最左列添加到where条件中效率会不会提升 explain select sal, count() from empx where deptno10 group by sal;效率有提升的这说明了group by确实也遵循最左前缀法则。where中使用了最左列 limit优化 数据量特别庞大时取数据时越往后效率越低怎么提升mysql官方给出的解决方案是使用覆盖索引子查询的形式来提升效率。 怎么解决使用覆盖索引加子查询 使用覆盖索引速度有所提升 使用子查询形式取其他列的数据 通过测试这种方式整体效率有所提升。 主键优化 主键设计原则 主键值不要太长二级索引叶子结点上存储的是主键值主键值太长容易导致索引占用空间较大。尽量使用auto_increment生成主键。尽量不要使用uuid做主键因为uuid不是顺序插入。最好不要使用业务主键因为业务的变化会导致主键值的频繁修改主键值不建议修改因为主键值修改聚集索引一定会重新排序。在插入数据时主键值最好是顺序插入不要乱序插入因为乱序插入可能会导致B树叶子结点频繁的进行页分裂与页合并操作效率较低。 主键值对应聚集索引插入主键值如果是乱序的B树叶子结点需要不断的重新排序重排过程中还会频繁涉及到页分裂和页合并的操作效率较低。B树上的每个节点都存储在页page中。一个页面中存储一个节点。MySQL的InnoDB存储引擎一个页可以存储16KB的数据。如果主键值不是顺序插入的话会导致频繁的页分裂和页合并。在一个B树中页分裂和页合并是树的自动调整机制的一部分。当一个页已经满了再插入一个新的关键字时就会触发页分裂操作将页中的关键字分配到两个新的页中同时调整树的结构。相反当一个页中的关键字数量下降到一个阈值以下时就会触发页合并操作将两个相邻的页合并成一个新的页。如果主键值是随机的、不是顺序插入的那么页的利用率会降低页分裂和页合并的次数就会增加。由于页的分裂和合并是比较耗时的操作频繁的分裂和合并会降低数据库系统的性能。因此为了优化B树的性能可以将主键值设计成顺序插入的这样可以减少页的分裂和合并的次数提高B树的性能。在实际应用中如果对主键值的顺序性能要求不是特别高也可以采用一些技术手段来减少页分裂和合并例如B树分裂时采用“延迟分裂”技术或者通过调整页的大小和节点的大小等方式来优化B树的性能。 insert优化 insert优化原则 批量插入数据量较大时不要一条一条插入可以批量插入当然建议一次插入数据不超过1000条 insert into t_user(id,name,age) values (1,jack,20),(2,lucy,30),(3,timi,22);mysql默认是自动提交事务只要执行一条DML语句就自动提交一次因此当插入大量数据时建议手动开启事务和手动提交事务。不建议使用数据库事务自动提交机制。主键值建议采用顺序插入顺序插入比乱序插入效率高。超大数据量插入可以考虑使用mysql提供的load指令load指令可以将csv文件中的数据批量导入到数据库表当中并且效率很高过程如下 第一步登录mysql时指定参数
mysql –local-infile -uroot -p1234第二步开启local_infile功能 set global local_infile 1;第三步执行load指令 use powernode;create table t_temp(id int primary key,name varchar(255),password varchar(255),birth char(10),email varchar(255) );load data local infile E:\powernode\05-MySQL高级\resources\t_temp-100W.csv into table t_temp fields terminated by , lines terminated by \n;文件中的数据如下
导入表中之后数据如下
count()优化 分组函数count的使用方式 count(主键) 原理将每个主键值取出累加 count(常量值) 原理获取到每个常量值累加 count(字段) 原理取出字段的每个值判断是否为NULL不为NULL则累加。 count() 原理不用取值底层mysql做了优化直接统计总行数效率最高。
结论如果你要统计一张表中数据的总行数建议使用 count(*) 注意 对于InnoDB存储引擎来说count计数的实现原理就是将表中每一条记录取出然后累加。如果你想真正提高效率可以自己使用额外的其他程序来实现例如每向表中插入一条记录时在redis数据库中维护一个总行数这样获取总行数的时候直接从redis中获取即可这样效率是最高的。对于MyISAM存储引擎来说当一个select语句没有where条件时获取总行数效率是极高的不需要统计因为MyISAM存储引擎维护了一个单独的总行数。 update优化 当存储引擎是InnoDB时表的行级锁是针对索引列添加的锁如果索引失效了或者不是索引列时会提升为表级锁。 什么是行级锁A事务和B事务开启A事务后通过A事务修改表中某条记录修改后在A事务未提交的前提下B事务去修改同一条记录时无法继续直到A事务提交B事务才可以继续。 有一张表t_fruit create table t_fruit(id int primary key auto_increment,name varchar(255) );insert into t_fruit values(null, 苹果); insert into t_fruit values(null, 香蕉); insert into t_fruit values(null, 橘子);开启A事务和B事务演示行级锁 事务A没有结束之前事务B卡住 事务A结束之后事务B继续执行 当然如果更新的不是同一行数据事务A和事务B可以并发
行级锁是对索引列加锁以上更新语句的where条件是idid是主键当然有索引所以使用了行级锁如果索引失效或者字段上没有索引则会升级为表级锁
因此为了更新的效率建议update语句中where条件中的字段是添加索引的。