安徽建设银行网站,信用信息查询公示系统,小广告的胶怎么清理,windows优化大师怎么样目录
二、MySQL高级02
2.1 触发器
2.1.1 触发器介绍
2.1.2 创建触发器
2.2 MySQL的体系结构
2.3 存储引擎
2.3.1 存储引擎概述
2.3.2 各种存储引擎特性
2.3.3 InnoDB
2.3.4 MyISAM
2.3.5 MEMORY
2.3.6 MERGE
2.3.7 存储引擎的选择
2.4 优化sql
2.4.1 查看sql执行…目录
二、MySQL高级02
2.1 触发器
2.1.1 触发器介绍
2.1.2 创建触发器
2.2 MySQL的体系结构
2.3 存储引擎
2.3.1 存储引擎概述
2.3.2 各种存储引擎特性
2.3.3 InnoDB
2.3.4 MyISAM
2.3.5 MEMORY
2.3.6 MERGE
2.3.7 存储引擎的选择
2.4 优化sql
2.4.1 查看sql执行效率
2.4.2 定位低效率执行sql
2.4.3 explain分析执行计划
2.4.4 用show profile分析SQL
2.4.5 trace分析优化器执行计划
2.5 索引的使用
2.5.1 验证索引提升查询效率
2.5.2 避免索引失效
2.5.3 查看索引使用情况
2.6 sql优化
2.6.1 优化insert语句
2.6.2 优化order by语句
2.6.3 优化group by语句
2.6.4 优化嵌套查询 二、MySQL高级02
2.1 触发器
2.1.1 触发器介绍
触发器是与表有关的数据库对象指在 insert/update/delete 之前或之后触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容这与其他的数据库是相似的。现在触发器还只支持行级触发不支持语句级触发。
触发器类型NEW 和 OLD的使用INSERT 型触发器NEW 表示将要或者已经新增的数据UPDATE 型触发器OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据DELETE 型触发器OLD 表示将要或者已经删除的数据
2.1.2 创建触发器
语法
create [or replace] trigger 触发器名触发时间 {before | after} -- view 中是 instead of触发事件 {insert | update | delete} -- dml、ddl、database
on 触发对象 -- table、view、schema、database 触发频率 {for each row} -- 行级触发器。默认语句级触发器[follows 其它触发器名] -- 多个触发器执行的 前后顺序[when 触发条件]
beginpl/sql 语句;
end;
示例
-- 创建日志表
create table emp_logs(id int(11) not null auto_increment,operation varchar(20) not null comment 操作类型, insert/update/delete,operate_time datetime not null comment 操作时间,operate_id int(11) not null comment 操作表的ID,operate_params varchar(500) comment 操作参数,primary key(id)
)engineinnodb default charsetutf8;
1、 创建 insert 型触发器完成插入数据时的日志记录 :
-- 测试添加的触发器添加触发器生效一般都是在添加操作之后进行的
CREATE TRIGGER tri_emp_insert
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
-- 在emp表操作之后在emp_logs表中留下日志INSERT INTO emp_logs VALUES(null,insert,NOW(),new.id,CONCAT(名字是,new.name1,工资是,new.salary));
END;
-- 测试添加操作
INSERT INTO emp VALUES(null,王中王,28,10000);
-- 查看emp_logs表中的数据
SELECT * FROM emp_logs;
2、 创建 update 型触发器完成更新数据时的日志记录 :
-- 测试修改的触发器修改触发器生效一般都是在修改操作之后进行的
CREATE TRIGGER tri_emp_update
AFTER UPDATE ON emp
FOR EACH ROW
BEGIN
-- 在emp表进行修改操作之后在emp_logs表中留下日志,这里使用的还是旧的idINSERT INTO emp_logs VALUES(null,update,NOW(),old.id,CONCAT(新的名字是,new.name1,新的工资是,new.salary,--旧的名字是,old.name1,旧的工资是,old.salary));
END;
-- 如果列名是关键字时要使用反引号将其引起来 比如name在进行操作时就需要写成name
UPDATE emp SET name1test,salary6666 WHERE id 5;
-- 查看emp_logs表中的数据
SELECT * FROM emp_logs;
案例1周六的时候不能修改emp表的数据
① DAYOFWEEK(date)
返回日期date的星期索引(1星期天2星期一, ……7星期六)。这些索引值对应于ODBC标准。
② WEEKDAY(date)
返回date的星期索引(0星期一1星期二, ……6 星期天)。
-- 周六的时候不能修改emp表中的数据
-- 这里使用before或者after是不影响触发器操作的
DROP TRIGGER IF EXISTS tri_emp_update_class1;
CREATE TRIGGER tri_emp_update_class1
BEFORE UPDATE ON emp
FOR EACH ROW
BEGINDECLARE wday INT; -- 定义一个变量用于判断SELECT WEEKDAY(NOW()) INTO wday; -- 查询当前时间是周几并赋值给wday-- 根据今天是周几进行判断IF wday5 THEN-- 错误代码设置错误信息SIGNAL SQLSTATE 40000 SET message_text 今天是周六不能修改数据;END IF;
END;
-- 案例1修改测试
UPDATE emp SET name1test1,salary5555 WHERE id 5;
-- 测试结果
[SQL] -- 案例1修改测试
UPDATE emp SET name1test1,salary5555 WHERE id 5;
[Err] 1644 - 今天是周六不能修改数据
案例2周五的时候不能修改emp表中的数据周六可以修改emp的员工的工资不能降低
-- 周五的时候不能修改emp表中的数据
-- 周六可以修改emp的员工的工资不能降低
DROP TRIGGER IF EXISTS tri_emp_update_class2;
CREATE TRIGGER tri_emp_update_class2
BEFORE UPDATE ON emp
FOR EACH ROW
BEGINDECLARE wday INT; -- 定义一个变量用于判断SELECT WEEKDAY(NOW()) INTO wday; -- 查询当前时间是周几并赋值给wday-- 根据今天是周几进行判断IF wday4 THEN-- 错误代码设置错误信息SIGNAL SQLSTATE KL900 SET message_text 今天是周五不能修改员工的工资;ELSEIF wday5 THENIF old.salary new.salary THENSIGNAL SQLSTATE 50000 SET message_text 今天是周六员工工资不可以降低;END IF;END IF;
END;
-- 案例2修改测试
UPDATE emp SET name1test1,salary5555 WHERE id 5;
[SQL] UPDATE emp SET name1test1,salary5555 WHERE id 5;
[Err] 1644 - 今天是周六员工工资不可以降低
3、删除触发器
drop trigger [schema_name.]trigger_name
如果没有指定 schema_name默认为当前数据库 。
-- 删除触发器
DROP TRIGGER IF EXISTS emp_logs_delete_trigger;
CREATE TRIGGER emp_logs_delete_trigger
AFTER DELETE
ON emp
FOR EACH ROW
BEGININSERT INTO emp_logs (id,operation,operate_time,operate_id,operate_params) VALUES(null,delete,now(),old.id,concat(删除前(id:,old.id,, name:,old.name1,, age:,old.age,, salary:,old.salary,)));
END;
-- 删除的触发器测试
DELETE FROM emp WHERE id 5;
-- 查看emp_logs表中的数据
SELECT * FROM emp_logs;
触发器在获取登录用户的id时可能获取不到
4、查看触发器
可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。语法结构
-- 查看触发器测试
show triggers ;
2.2 MySQL的体系结构
官网 https://www.cnblogs.com/zh-ch/p/12803325.html 整个MySQL Server由以下组成
- Connection Pool : 连接池组件
- Management Services Utilities : 管理服务和工具组件
- SQL Interface : SQL接口组件
- Parser : 查询分析器组件
- Optimizer : 优化器组件
- Caches Buffers : 缓冲池组件
- Pluggable Storage Engines : 存储引擎
- File System : 文件系统
注 SQL Interface 中
DMLData Manipulation Language数据操作语言-数据库的基本操作SQL中处理数据等操作统称为数据操纵语言,简而言之就是实现了基本的“增删改查”操作。包括的关键字有select、update、delete、insert、merge
DDLData Definition Language数据定义语言-用于定义和管理 SQL 数据库中的所有对象的语言对数据库中的某些对象(例如database,table)进行管理。包括的关键字有create、alter、drop、truncate、comment、grant、revoke
1 连接层最上层是一些客户端和链接服务包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2 服务层第二层架构主要完成大多数的核心服务功能如SQL接口并完成缓存的查询SQL的分析和优化部分内置函数的执行。所有跨存储引擎的功能也在这一层实现如 过程、函数等。在该层服务器会解析查询并创建相应的内部解析树并对其完成相应的优化如确定表的查询的顺序是否利用索引等 最后生成相应的执行操作。如果是select语句服务器还会查询内部的缓存如果缓存空间足够大这样在解决大量读操作的环境中能够很好的提升系统的性能。
3 引擎层存储引擎层 存储引擎真正的负责了MySQL中数据的存储和提取服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能这样我们可以根据自己的需要来选取合适的存储引擎。
4存储层数据存储层 主要是将数据存储在文件系统之上并完成与存储引擎的交互。
和其他数据库相比MySQL有点与众不同它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
2.3 存储引擎
2.3.1 存储引擎概述
存储引擎是基于表的而不是基于库的 和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。存储引擎就是存储数据建立索引更新查询数据等等技术的实现方式 。存储引擎是基于表的而不是基于库的。所以存储引擎也可被称为表类型。OracleSqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎可以根据需要使用相应引擎或者编写存储引擎。MySQL5.0支持的存储引擎包含 InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等其中InnoDB和BDB提供事务安全表其他存储引擎是非事务安全表。创建新表时如果不指定存储引擎那么系统就会使用默认的存储引擎MySQL5.5之前的默认存储引擎是MyISAM5.5之后就改为了InnoDB。
可以通过指定 show engines 来查询当前数据库支持的存储引擎 查看Mysql数据库默认的存储引擎 指令 show variables like %storage_engine% ; 2.3.2 各种存储引擎特性
下面重点介绍几种常用的存储引擎 并对比各个存储引擎之间的区别 如下表所示
特点InnoDBMyISAMMEMORYMERGENDB存储限制64TB有有没有有事务安全支持锁机制行锁(适合高并发)表锁表锁表锁行锁B树索引支持支持支持支持支持哈希索引支持全文索引支持(5.6版本之后)支持集群索引支持数据索引支持支持支持索引缓存支持支持支持支持支持数据可压缩支持空间使用高低N/A低低内存使用高低中等低高批量插入速度低高高高高支持外键支持
表锁
一般存在于MyISAM存储引擎开销小加锁快无死锁锁定粒度大发生锁冲突的概率最高,并发度最低。
我们在编辑表或者执行修改表的语句的时候一般都会给表加上表锁可以避免一些不同步的事情出现表锁分为两种一种是读锁一种是写锁。
语法
加锁lock table 表名 read(write);
释放所有表的锁unlock tables;
查看加锁的表show open tables where in_use0; 行锁
一般存在于InnoDB存储引擎开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低,并发度也最高。
行锁支持事务行锁只要是靠索引来运行的
行为
1、当我们对一行进行更新但是不提交的时候其他进程也对该行进行更新则需要进行等待这就是行锁。
2、如果我们对一行数据进行更新其他进程更新别的行是不会受影响的。
测试之前我们需要把事务提交方式改为手动提交
show variables like autocommit;
set autocommit0;
mysql show variables like autocommit;-- 查看事务是否自动提交
----------------------
| Variable_name | Value |
----------------------
| autocommit | ON |
----------------------
1 row in set (0.00 sec)
-- 这里也可以使用 set autocommitoff;
mysql set autocommit0; -- 将事务的提交方式改为手动提交
Query OK, 0 rows affected (0.00 sec)
mysql show variables like autocommit;
----------------------
| Variable_name | Value |
----------------------
| autocommit | OFF |
----------------------
1 row in set (0.00 sec)
-----------------------测试行锁-------------------
for update
如果在一条select语句后加上for update则查询到的数据会被加上一条排它锁其它事务可以读取但不能进行更新和插入操作
start transaction -- 开始事务
select * from emp where id1 for update;-- A用户对id1的记录进行加锁
-- B用户无法对该记录进行操作
update user set name1test where id1;
-- A用户commit以后则B用户可以对该记录进行操作
如下图所示 行锁升级为表锁
当我们的行锁涉及到索引失效的时候会触发表锁的行为。
由于在column字段id上面建了索引如果没有正常使用会导致行锁变表锁
比如加单引号导致索引失效行锁变表锁
使用主键索引进行查询 -- 使用主键id
select * from emp where id1;
不使用主键索引进行查询 -- 不使用主键id
select * from emp where id1; -- 类型发生变化
2.3.3 InnoDB
InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
InnoDB存储引擎不同于其他存储引擎的特点
1、事务控制
create table goods_innodb(id int NOT NULL AUTO_INCREMENT,name varchar(20) NOT NULL,primary key(id)
)ENGINEinnodb DEFAULT CHARSETutf8;
-- ENGINEINNODB 就是设置当前表的引擎为innodb,不设置默认也是innodb
-- 设置字符编码格式为utf8
-- 开始事务
start transaction; -- begin
insert into goods_innodb(id,name)values(null,Meta22);
-- 提交事务 commit
commit; 2、外键约束
MySQL支持外键的存储引擎只有InnoDB 在创建外键的时候 要求父表必须有对应的索引 子表在创建外键的时候 也会自动的创建对应的索引。
下面两张表中 country_innodb是父表 country_id为主键索引city_innodb表是子表country_id字段为外键对应于country_innodb表的主键country_id 。
-- 创建国家表父表
create table country_innodb(country_id int NOT NULL AUTO_INCREMENT,country_name varchar(100) NOT NULL,primary key(country_id)
)ENGINEInnoDB DEFAULT CHARSETutf8;
-- 创建城市表子表
create table city_innodb(city_id int NOT NULL AUTO_INCREMENT,city_name varchar(50) NOT NULL,country_id int NOT NULL,primary key(city_id),key idx_fk_country_id(country_id),CONSTRAINT fk_city_country FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE-- 设置外键在删除是对应的操作时RESTRICT修改时对应的操作是CASCADE
)ENGINEInnoDB DEFAULT CHARSETutf8;
-- 添加数据
insert into country_innodb values(null,China),(null,America),(null,Japan);
insert into city_innodb values(null,Xian,1),(null,NewYork,2),(null,BeiJing,1);
在创建索引时 可以指定在删除、更新父表时对子表进行的相应操作包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。
1RESTRICT和NO ACTION相同 是指限制在子表有关联记录的情况下 父表不能更新如果外键中对应修改的操作是RECTRICT或者NO ACTION那么如果子表中有对应的数据则父表不能进行修改如果外键中对应删除的操作是RECTRICT或者NO ACTION那么字表中有数据时父表不能删除eg如果子表中有country_id1的数据那么父表中country_id1的country_id这一列就不能修改2CASCADE表示父表在更新或者删除时更新或者删除子表对应的记录如果外键中对应修改的操作是CASCADE那么父表修改country_id这一列时会一并修改子表中对应的country_id如果外键中对应删除的操作是CASCADE那么父表删除country_id这一列时会一并删除子表中对应的行eg如果父表将country_id1修改为country_id10那么子表中country_id1的列也会修改为103SET NULL 则表示父表在更新或者删除的时候子表的对应字段被SET NULL 。注对应的子表里面的字段不能设置为not null。如果外键中对应修改或的操作是SET NULL那么父表进行修改或删除时会将子表中对应的列设置为null所以字表中的外键字段不能设置为not null;
-- 查看这一张表的外键信息
show create table city_innodb ;
--删除外键
ALTER TABLE city_innodb DROP FOREIGN KEY idx_fk_country_id
--添加外键
ALTER TABLE city_innodb ADD CONSTRAINT idx_fk_country_id FOREIGN KEY ( country_id )
REFERENCES country_innodb ( country_id )
ON DELETE CASCADE ON UPDATE CASCADE
3、存储方式
InnoDB 存储表和索引有以下两种方式
①. 使用共享表空间存储 这种方式创建的表的表结构保存在.frm文件中 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中可以是多个文件。
②. 使用多表空间存储 这种方式创建的表的表结构仍然存在 .frm 文件中但是每个表的数据和索引单独保存在 .ibd 中。
使用命令查看存储地址show global variables like %datadir%windows系统中位置
mysql show global variables like %datadir%;
------------------------------------------------------------
| Variable_name | Value |
------------------------------------------------------------
| datadir | C:\ProgramData\MySQL\MySQL Server 5.7\Data\ |
------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)
Linux系统中位置
mysql show global variables like %datadir%;
--------------------------------
| Variable_name | Value |
--------------------------------
| datadir | /var/lib/mysql/ |
--------------------------------
1 row in set (0.00 sec) .opt文件代表着这是一个备份文件
MySQL中.frm文件保存了每个表的元数据包括表结构的定义等该文件与数据库引擎无关。
MySQL中.ibd文件InnoDB引擎开启了独立表空间(my.ini中配置innodb_file_per_table 1)产生的存放该表的数据和索引的文件。
2.3.4 MyISAM
MyISAM 不支持事务、也不支持外键其优势是访问的速度快对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。有以下两个比较重要的特点
1、不支持事务
create table goods_myisam(id int NOT NULL AUTO_INCREMENT,name varchar(20) NOT NULL,primary key(id)
)ENGINEmyisam DEFAULT CHARSETutf8;
insert into goods_myisam values(null,test); 2、文件存储方式
每个MyISAM在磁盘上存储成3个文件其文件名都和表名相同但拓展名分别是
① .frm (存储表定义) -- *.frm--表定义是描述表结构的文件。
② .MYD(MYData , 存储数据) -- *.MYD--D数据信息文件是表的数据文件。
③ .MYI(MYIndex , 存储索引) -- *.MYI--I索引信息文件是表数据文件中任何索引的数据树 2.3.5 MEMORY
Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件格式是.frm 该文件中只存储表的结构而其数据文件都是存储在内存中这样有利于数据的快速处理提高整个表的效率。MEMORY 类型的表访问非常地快因为他的数据是存放在内存中的并且默认使用HASH索引 但是服务一旦关闭表中的数据就会丢失。
2.3.6 MERGE
1MERGE存储引擎是一组MyISAM表的组合这些MyISAM表必须结构完全相同MERGE表本身并没有存储数据对MERGE类型的表可以进行查询、更新、删除操作这些操作实际上是对内部的MyISAM表进行的。
2对于MERGE类型表的插入操作是通过INSERT_METHOD子句定义插入的表可以有3个不同的值
值为first 对应的插入操作是作用在第一个表上
值为last 对应的插入操作是作用在最后一个表上
值为no 不能对折个merge表执行插入操作
3可以对MERGE表进行DROP操作但是这个操作只是删除MERGE表的定义对内部的表是没有任何影响的。
4被合并之后的表是不能轻易删除的 -- 测试merge
-- 创建order_1990表
create table order_1990(order_id int ,order_money double(10,2),order_address varchar(50),primary key (order_id)
)engine myisam default charsetutf8;
-- 创建order_1991表
create table order_1991(order_id int ,order_money double(10,2),order_address varchar(50),primary key (order_id)
)engine myisam default charsetutf8;
-- 添加数据
insert into order_1990 values(1,100.0,北京);
insert into order_1990 values(2,100.0,上海);
insert into order_1991 values(10,200.0,北京);
insert into order_1991 values(11,200.0,上海);
-- 合并两张表
-- 三张表的字段和主键都是一样的
-- 这里的存储引擎是merge第一张表是order_1990第二张表是order_1991
-- 添加的方法选择的是last也就是在对order_all进行插入操作时会作用到order_1991上面
create table order_all(order_id int ,order_money double(10,2),order_address varchar(50),primary key (order_id)
)engine merge union (order_1990,order_1991) INSERT_METHODLAST default charsetutf8;
查询表中的数据
mysql select * from order_1990;
--------------------------------------
| order_id | order_money | order_address |
--------------------------------------
| 1 | 100.00 | 北京 |
| 2 | 100.00 | 上海 |
--------------------------------------
2 rows in set (0.00 sec)
mysql select * from order_1991;
--------------------------------------
| order_id | order_money | order_address |
--------------------------------------
| 10 | 200.00 | 北京 |
| 11 | 200.00 | 上海 |
--------------------------------------
2 rows in set (0.00 sec)
mysql select * from order_all;
--------------------------------------
| order_id | order_money | order_address |
--------------------------------------
| 1 | 100.00 | 北京 |
| 2 | 100.00 | 上海 |
| 10 | 200.00 | 北京 |
| 11 | 200.00 | 上海 |
--------------------------------------
4 rows in set (0.00 sec)
向order_all中添加数据再次查询三张表的数据
-- 添加数据
insert into order_all values(100,10000.0,西安);
mysql select * from order_1990;
--------------------------------------
| order_id | order_money | order_address |
--------------------------------------
| 1 | 100.00 | 北京 |
| 2 | 100.00 | 上海 |
--------------------------------------
2 rows in set (0.00 sec)
mysql select * from order_1991;
--------------------------------------
| order_id | order_money | order_address |
--------------------------------------
| 10 | 200.00 | 北京 |
| 11 | 200.00 | 上海 |
| 100 | 10000.00 | 西安 |
--------------------------------------
3 rows in set (0.00 sec)
mysql select * from order_all; -- 合并的表是不存储数据的即使删掉合并的表对另外两张表也是没有影响的
--------------------------------------
| order_id | order_money | order_address |
--------------------------------------
| 1 | 100.00 | 北京 |
| 2 | 100.00 | 上海 |
| 10 | 200.00 | 北京 |
| 11 | 200.00 | 上海 |
| 100 | 10000.00 | 西安 |
--------------------------------------
5 rows in set (0.00 sec)
2.3.7 存储引擎的选择 在选择存储引擎时应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。InnoDB : 是Mysql的默认存储引擎用于事务处理应用程序支持外键。如果应用对事务的完整性有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询意外还包含很多的更新、删除操作那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定 还可以确保事务的完整提交和回滚对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统InnoDB是最合适的选择。MyISAM 如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事务的完整性、并发性要求不是很高那么选择这个存储引擎是非常合适的。
MEMORY将所有数据保存在RAM中在需要快速定位记录和其他类似数据环境下可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制太大的表无法缓存在内存中其次是要确保表的数据可以恢复数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表用以快速得到访问结果。
MERGE用于将一系列等同的MyISAM表以逻辑方式组合在一起并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制并且通过将不同的表分布在多个磁盘上可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。
2.4 优化sql
在应用的的开发过程中由于初期数据量小开发人员写 SQL 语句时更重视功能上的实现但是当应用系统正式上线后随着生产数据量的急剧增长很多 SQL 语句开始逐渐显露出性能问题对生产的影响也越来越大此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈因此我们必须要对它们进行优化本章将详细介绍在 MySQL 中优化 SQL 语句的方法。当面对一个有 SQL 性能问题的数据库时我们应该从何处入手来进行系统的分析使得能够尽快定位问题 SQL 并尽快解决问题。
2.4.1 查看sql执行效率
MySQL 客户端连接成功后通过 show [session|global] status 命令可以提供服务器状态信息。
show [session|global] status
参数
[session|global]可以不写
session当前连接的统计结果默认使用
global查看自数据库上次启动至今的统计结果
下面的命令显示了当前 session 中所有统计参数的值
-- com后面是七个下划线代表搜索操作为6个字符的操作进行的次数
mysql show status like Com_______;
----------------------
| Variable_name | Value |
----------------------
| Com_binlog | 0 |
| Com_commit | 3 |
| Com_delete | 0 |
| Com_import | 0 |
| Com_insert | 2 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 10 |
| Com_signal | 0 |
| Com_update | 0 |
| Com_xa_end | 0 |
----------------------
11 rows in set (0.00 sec)
Com_xxx 表示每个 xxx 语句执行的次数我们通常比较关心的是以下几个统计参数。
Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。
Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的累加的算法也略有不同。
mysql show global status like Com_______;
----------------------
| Variable_name | Value |
----------------------
| Com_binlog | 0 |
| Com_commit | 3 |
| Com_delete | 2 |
| Com_import | 0 |
| Com_insert | 10 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 93 |
| Com_signal | 1 |
| Com_update | 8 |
| Com_xa_end | 0 |
----------------------
11 rows in set (0.00 sec)
mysql show status like Innodb______________;
----------------------------------
| Variable_name | Value |
----------------------------------
| Innodb_redo_log_uuid | 1075899837 |
| Innodb_os_log_fsyncs | 312 |
| Innodb_pages_created | 206 |
| Innodb_pages_written | 1017 |
| Innodb_row_lock_time | 5222 |
| Innodb_rows_inserted | 14 |
----------------------------------
6 rows in set (0.00 sec)
参数含义Com_select执行 select 操作的次数一次查询只累加 1。Com_insert执行 INSERT 操作的次数对于批量插入的 INSERT 操作只累加一次。Com_update执行 UPDATE 操作的次数。Com_delete执行 DELETE 操作的次数。Innodb_rows_readselect 查询返回的行数。Innodb_rows_inserted执行 INSERT 操作插入的行数。Innodb_rows_updated执行 UPDATE 操作更新的行数。Innodb_rows_deleted执行 DELETE 操作删除的行数。Connections试图连接 MySQL 服务器的次数。Uptime服务器工作时间。Slow_queries慢查询的次数。
2.4.2 定位低效率执行sql
可以通过以下两种方式定位执行效率较低的 SQL 语句。
1、慢sql日志
慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句用--log-slow-queries[file_name]选项启动时mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。 -- 查看是否开启慢sql日志
SHOW global VARIABLES LIKE %query%;
-- 开启慢sql
set global slow_query_logon;
-- 设置慢sql的时间代表超过1秒就是一个慢sql
set global long_query_time 1;
mysql SHOW global VARIABLES LIKE %query%;
-----------------------------------------------------------------
| Variable_name | Value |
-----------------------------------------------------------------
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | NO |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
-----------------------------------------------------------------
8 rows in set (0.00 sec)
mysql set global slow_query_logon;
Query OK, 0 rows affected (0.01 sec)
mysql set global long_query_time1;
Query OK, 0 rows affected (0.00 sec)
2、线程列表show processlist
show processlist : 慢查询日志在查询结束以后才纪录所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题可以使用show processlist命令查看当前MySQL在进行的线程包括线程的状态、是否锁表等可以实时地查看 SQL 的执行情况同时对一些锁表操作进行优化 1 id列用户登录mysql时系统分配的connection_id可以使用函数connection_id()查看
2 user列显示当前用户。如果不是root这个命令就只显示用户权限范围的sql语句
3 host列显示这个语句是从哪个ip的哪个端口上发的可以用来跟踪出现问题语句的用户
4 db列显示这个进程目前连接的是哪个数据库
5 command列显示当前连接的执行的命令一般取值为休眠sleep查询query连接connect等
6 time列显示这个状态持续的时间单位是秒
7 state列显示使用当前连接的sql语句的状态很重要的列。
state描述的是语句执行中的某一个状态。一个sql语句以查询为例
可能需要经过copying to tmp table、sorting result、sending data
等状态才可以完成
8 info列显示这个sql语句是判断问题语句的一个重要依据
开启慢sql之后就会在日志文件中记录慢sql查询慢sql的日志的位置
-- 查询慢sql日志的名字
show variables like %slow%;
-- 查看慢sql日志存放的位置
show variables like %datadir%; 2.4.3 explain分析执行计划
通过以上步骤查询到效率低的 SQL 语句后可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息包括在 SELE
查询sql语句的执行计划
explain select * from emp where id 1;
或 desc select * from emp where id 1;
CT 语句执行过程中表如何连接和连接的顺序。 字段含义idselect查询的序列号是一组数字表示的是查询中执行select子句或者是操作表的顺序。select_type表示 SELECT 的类型常见的取值有 SIMPLE简单表即不使用表连接或者子查询、PRIMARY主查询即外层的查询、UNIONUNION 中的第二个或者后面的查询语句、SUBQUERY子查询中的第一个 SELECT等table输出结果集的表type表示表的连接类型性能由好到差的连接类型为( system --- const ----- eq_ref ------ ref ------- ref_or_null---- index_merge --- index_subquery ----- range ----- index ------ all ) 如果是all代表没使用索引效率过低possible_keys表示查询时可能使用的索引key表示实际使用的索引key_len索引字段的长度rows扫描行的数量extra执行情况的说明和描述
案例
数据库表准备
-- 创建t_role表
DROP TABLE IF EXISTS t_role;
CREATE TABLE t_role (id int NOT NULL AUTO_INCREMENT,role varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,status int DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT11 DEFAULT CHARSETutf8mb3;
-- 创建t_user表
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user (id int NOT NULL AUTO_INCREMENT,username varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT 用户名,password varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT 用户密码,birthday date DEFAULT NULL COMMENT 用户生日,sex varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL COMMENT 用户性别,rid int DEFAULT NULL,status int DEFAULT NULL COMMENT 状态,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT22 DEFAULT CHARSETutf8mb3;
-- 创建t_user_role表
DROP TABLE IF EXISTS t_user_role;
CREATE TABLE t_user_role (id int NOT NULL AUTO_INCREMENT,uid int DEFAULT NULL COMMENT 用户的id,rid int DEFAULT NULL COMMENT 角色的id,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8mb3;
-- 添加数据
INSERT INTO t_user_role VALUES (1, 1, 1);
INSERT INTO t_user VALUES (1, zs, 123456, 2023-01-18, 男, 1, 2);
INSERT INTO t_user VALUES (7, 666, 666, 2023-02-08, 女, 2, 1);
INSERT INTO t_user VALUES (8, 3, 3, 2022-09-06, 男, 1, 1);
INSERT INTO t_role VALUES (1, admin, 1);
INSERT INTO t_role VALUES (2, user, 1);
INSERT INTO t_role VALUES (10, superUser, 1);
1、explain之id
id 字段是 select查询的序列号是一组数字表示的是查询中执行select子句或者是操作表的顺序。
id 情况有2种
1 id 相同表示加载表的顺序是从上到下。
2 id 不同id值越大优先级越高越先被执行。
explain select * from t_role r, t_user u, t_user_role ru where r.id ru.rid and u.id ru.uid ;
explain select * from t_role where id in(select rid from t_user_role where uid (select id from t_user where username张三)); 2、explain之select_type
表示 SELECT 的类型常见的取值如下表所示
select_type含义SIMPLE简单的select查询查询中不包含子查询或者UNIONPRIMARY查询中若包含任何复杂的子查询最外层查询标记为该标识SUBQUERY在SELECT 或 WHERE 列表中包含了子查询DERIVED在FROM 列表中包含的子查询被标记为 DERIVED衍生 MYSQL会递归执行这些子查询把结果放在临时表中UNION若第二个SELECT出现在UNION之后则标记为UNION 若UNION包含在FROM子句的子查询中外层SELECT将被标记为 DERIVEDUNION RESULT从UNION表获取结果的SELECT
3、 explain 之 table
展示这一行的数据是关于哪一张表的
4、 explain 之 type
type 显示的是访问类型是较为重要的一个指标可取值为
type含义NULLMySQL不访问任何表索引直接返回结果system表只有一行记录(等于系统表)这是const类型的特例一般不会出现const表示通过索引一次就找到了const 用于比较primary key 或者 unique 索引。因为只匹配一行数据所以很快。如将主键置于where列表中MySQL 就能将该查询转换为一个常亮。const于将 主键 或 唯一 索引的所有部分与常量值进行比较eq_ref类似ref区别在于使用的是唯一索引使用主键的关联查询关联查询出的记录只有一条。常见于主键或唯一索引扫描ref非唯一性索引扫描返回匹配某个单独值的所有行。本质上也是一种索引访问返回所有匹配某个单独值的所有行多个range只检索给定返回的行使用一个索引来选择行。 where 之后出现 between , , in 等操作。indexindex 与 ALL的区别为 index 类型只是遍历了索引树 通常比ALL 快 ALL 是遍历数据文件。all将遍历全表以找到匹配的行
结果值从最好到最坏以此是
NULL system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
system const eq_ref ref range index ALL
一般来说 我们需要保证查询至少达到 range 级别再不济也应该是index 最好达到ref 。
5、 explain 之 key
key_len : 表示索引中使用的字节数 该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下 长度越短越好 。
key 实际使用的索引 如果为NULL 则没有使用索引。
possible_keys : 显示可能应用在这张表的索引 一个或多个。
6、 explain 之 rows
扫描行的数量。
7、 explain 之 extra
其他的额外的执行计划信息在该列展示 。
extra含义using filesort说明mysql会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取 称为 “文件排序”, 效率低。using temporary使用了临时表保存中间结果MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by 效率低using index表示相应的select操作使用了覆盖索引 避免访问表的数据行 效率不错。
2.4.4 用show profile分析SQL
Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
1、通过 have_profiling 参数能够看到当前MySQL是否支持profile
mysql select have_profiling ;
------------------
| have_profiling |
------------------
| YES |
------------------
1 row in set, 1 warning (0.00 sec)
2、默认profiling是关闭的可以通过set语句在Session级别开启profiling
mysql select profiling;
-------------
| profiling |
-------------
| 0 |
-------------
1 row in set, 1 warning (0.00 sec)
3、开启profiling 开关
mysql set profiling1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql select profiling;
-------------
| profiling |
-------------
| 1 |
-------------
1 row in set, 1 warning (0.00 sec)
通过profile我们能够更清楚地了解SQL执行的过程。1、先随便执行两条命令
select * from t_user ;
select count(*) from t_user;
2、再执行查看命令查看耗时
show profiles;
mysql show profiles;
---------------------------------------------------
| Query_ID | Duration | Query |
---------------------------------------------------
| 1 | 0.00015800 | select profiling |
| 2 | 0.00023600 | select * from t_user |
| 3 | 0.00017600 | select * from t_user |
| 4 | 0.00012925 | commit |
| 5 | 0.00028350 | select * from t_user |
| 6 | 0.05431525 | select count(*) from t_user |
---------------------------------------------------
6 rows in set, 1 warning (0.00 sec)
3、通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间
mysql show profile for query 6;
------------------------------------------
| Status | Duration |
------------------------------------------
| starting | 0.000067 | --
| Executing hook on transaction | 0.000003 | --
| starting | 0.000007 | -- 开启
| checking permissions | 0.000005 | -- 检查权限
| Opening tables | 0.000024 | -- 打开表
| init | 0.000005 | -- 初始化
| System lock | 0.000007 | -- 检查系统锁
| optimizing | 0.000005 | -- 分析
| statistics | 0.000014 | -- 统计
| preparing | 0.000010 | -- 准备
| executing | 0.054088 | -- 执行
| end | 0.000017 | -- 结束
| query end | 0.000005 | -- 结束查询
| waiting for handler commit | 0.000012 | --
| closing tables | 0.000010 |
| freeing items | 0.000025 |
| cleaning up | 0.000012 |
------------------------------------------
17 rows in set, 1 warning (0.00 sec)
注Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端而不仅仅是返回给客户端。由于在Sending data状态下MySQL线程往往需要做大量的磁盘读取操作所以经常是整各查询中耗时最长的状态。 在获取到最消耗时间的线程状态后MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如选择查看CPU的耗费时间
mysql show profile cpu for query 6;
----------------------------------------------------------------
| Status | Duration | CPU_user | CPU_system |
----------------------------------------------------------------
| starting | 0.000067 | 0.000035 | 0.000026 |
| Executing hook on transaction | 0.000003 | 0.000002 | 0.000001 |
| starting | 0.000007 | 0.000003 | 0.000003 |
| checking permissions | 0.000005 | 0.000003 | 0.000002 |
| Opening tables | 0.000024 | 0.000014 | 0.000010 |
| init | 0.000005 | 0.000003 | 0.000002 |
| System lock | 0.000007 | 0.000004 | 0.000003 |
| optimizing | 0.000005 | 0.000003 | 0.000002 |
| statistics | 0.000014 | 0.000008 | 0.000006 |
| preparing | 0.000010 | 0.000006 | 0.000004 |
| executing | 0.054088 | 0.053940 | 0.000000 |
| end | 0.000017 | 0.000009 | 0.000000 |
| query end | 0.000005 | 0.000005 | 0.000000 |
| waiting for handler commit | 0.000012 | 0.000012 | 0.000000 |
| closing tables | 0.000010 | 0.000010 | 0.000000 |
| freeing items | 0.000025 | 0.000025 | 0.000000 |
| cleaning up | 0.000012 | 0.000012 | 0.000000 |
----------------------------------------------------------------
17 rows in set, 1 warning (0.00 sec)
字段含义Statussql 语句执行的状态Durationsql 执行过程中每一个步骤的耗时CPU_user当前用户占有的cpuCPU_system系统占有的cpu
2.4.5 trace分析优化器执行计划
MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。
1、打开trace 设置格式为 JSON并设置trace最大能够使用的内存大小避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_traceenabledon,end_markers_in_jsonon;
set optimizer_trace_max_mem_size1000000;
2、执行sql语句
select * from t_user;
3、检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的
select * from information_schema.optimizer_trace\G;
mysql select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************QUERY: select * from t_userTRACE: {steps: [{join_preparation: {select#: 1,steps: [{expanded_query: /* select#1 */ select t_user.id AS id,t_user.username AS username,t_user.password AS password,t_user.birthday AS birthday,t_user.sex AS sex,t_user.rid AS rid,t_user.status AS status from t_user}] /* steps */} /* join_preparation */},{join_optimization: {select#: 1,steps: [{table_dependencies: [{table: t_user,row_may_be_null: false,map_bit: 0,depends_on_map_bits: [] /* depends_on_map_bits */}] /* table_dependencies */},{rows_estimation: [{table: t_user,table_scan: {rows: 10,cost: 0.25} /* table_scan */}] /* rows_estimation */},{considered_execution_plans: [{plan_prefix: [] /* plan_prefix */,table: t_user,best_access_path: {considered_access_paths: [{rows_to_scan: 10,access_type: scan,resulting_rows: 10,cost: 1.25,chosen: true}] /* considered_access_paths */} /* best_access_path */,condition_filtering_pct: 100,rows_for_plan: 10,cost_for_plan: 1.25,chosen: true}] /* considered_execution_plans */},{attaching_conditions_to_tables: {original_condition: null,attached_conditions_computation: [] /* attached_conditions_computation */,attached_conditions_summary: [{table: t_user,attached: null}] /* attached_conditions_summary */} /* attaching_conditions_to_tables */},{finalizing_table_conditions: [] /* finalizing_table_conditions */},{refine_plan: [{table: t_user}] /* refine_plan */}] /* steps */} /* join_optimization */},{join_execution: {select#: 1,steps: [] /* steps */} /* join_execution */}] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)
2.5 索引的使用
索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。
2.5.1 验证索引提升查询效率
在我们准备的表结构t_user中 一共存储了 300 万记录
对应的存储的sql语句
-- 建立存储过程
DROP PROCEDURE IF EXISTS proc1;
CREATE PROCEDURE proc1(cnt INT)
BEGINDECLARE i INT DEFAULT 1;START TRANSACTION;REPEATINSERT INTO t_user(id,username,password,sex)VALUES(null,CONCAT(lwl,i),CONCAT(123,i),男);SET i i 1;UNTIL i cnt -- 结束条件END REPEAT;COMMIT;
END;
-- 调用存储过程
CALL proc1(3000000);
1、根据id进行查询
select * from t_user where id1000000 \G;
explain select * from t_user where id1000000 \G;
mysql select * from t_user where id1000000\G;
*************************** 1. row ***************************id: 1000000
username: lwl969979
password: 123969979
birthday: NULLsex: 男rid: NULLstatus: NULL
1 row in set (0.00 sec)
-- 查看执行计划
mysql explain select * from t_user where id1000000 \G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_userpartitions: NULLtype: const
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: constrows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.00 sec)
2、根据名字进行精确查询
mysql select * from t_user where usernamelwl1000000\G;
*************************** 1. row ***************************id: 1030021
username: lwl1000000
password: 1231000000
birthday: NULLsex: 男rid: NULLstatus: NULL
1 row in set (1.30 sec)
-- 查看sql执行计划
mysql explain select * from t_user where usernamelwl1000000\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_userpartitions: NULLtype: ALL
possible_keys: NULL -- 什么也没有使用key: NULL -- 什么也没有使用key_len: NULLref: NULLrows: 3025062filtered: 10.00Extra: Using where
1 row in set, 1 warning (0.00 sec)
如果想提高sql的效率需要给username添加索引
create index idx_item_name on t_user(username);
再对名字进行精确查询
mysql explain select * from t_user where usernamelwl1000000\G;
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: t_userpartitions: NULLtype: ref -- 使用了引用
possible_keys: idx_item_name -- 可能使用了索引key: idx_item_name -- 使用了索引key_len: 768ref: const rows: 1filtered: 100.00Extra: NULL
1 row in set, 1 warning (0.01 sec)
2.5.2 避免索引失效
-- 创建表
create table tb_seller (sellerid varchar (100),name varchar (100),nickname varchar (50),password varchar (60),status varchar (1),address varchar (100),createtime datetime,primary key(sellerid)
)engineinnodb default charsetutf8mb4;
-- 添加数据
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(alibaba,阿里巴巴,阿里小店,e10adc3949ba59abbe56e057f20f883e,1,北京市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(baidu,百度科技有限公司,百度小店,e10adc3949ba59abbe56e057f20f883e,1,北京市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(huawei,华为科技有限公司,华为小店,e10adc3949ba59abbe56e057f20f883e,0,北京市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(itcast,传智播客教育科技有限公司,传智播客,e10adc3949ba59abbe56e057f20f883e,1,北京市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(itheima,AAA软件教育,AAA软件教育,e10adc3949ba59abbe56e057f20f883e,0,北京市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(luoji,罗技科技有限公司,罗技小店,e10adc3949ba59abbe56e057f20f883e,1,北京市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(oppo,OPPO科技有限公司,OPPO官方旗舰店,e10adc3949ba59abbe56e057f20f883e,0,北京市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(ourpalm,掌趣科技股份有限公司,掌趣小店,e10adc3949ba59abbe56e057f20f883e,1,北京市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(qiandu,千度科技,千度小店,e10adc3949ba59abbe56e057f20f883e,2,北京市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(sina,新浪科技有限公司,新浪官方旗舰店,e10adc3949ba59abbe56e057f20f883e,1,北京市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(xiaomi,小米科技,小米官方旗舰店,e10adc3949ba59abbe56e057f20f883e,1,西安市,2088-01-01 12:00:00);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(yijia,宜家家居,宜家家居旗舰店,e10adc3949ba59abbe56e057f20f883e,1,北京市,2088-01-01 12:00:00);
-- 创建复合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
就相当于创建了三个索引
name
name status
name status address
1、 全值匹配 对索引中所有列都指定具体值。
该情况下索引生效执行效率高。
explain select * from tb_seller where name小米科技 and status1 and address北京市; 2、最左前缀法则
最左前缀法则指的是查询从索引的最左前列开始并且不跳过索引中的列。
也就是如果索引了多列要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。①system const eq_ref ref range index All。一般而言我们要保证查询至少达到ranag级别最好能到达到ref。②possible_keys和key的值说明语句使用了索引index_nit。③ref值是const即某个常量被用于查找索引列上的值。
匹配最左前缀法则走索引
一个索引生效的时候 值是403
两个索引都生效的时候值是 410
三个索引全部生效的时候值是813
如果符合最左法则但是出现跳跃某一列只有最左列索引生效。
explain select * from tb_seller where name小米科技 and address北京市; 3、 如果含有范围查询那么右边的列就不能使用索引 。
explain select * from tb_seller where name小米科技 and status1 and address北京市;
4、 不要在索引列上进行运算操作 索引将失效。
explain select * from tb_seller where substring(name,3,2)科技;
5、 字符串不加单引号造成索引失效。
explain select * from tb_seller where name小米科技 and status1 and address北京市;
-- 在查询时没有对字符串加单引号MySQL的查询优化器会自动的进行类型转换造成索引失效。 6、尽量使用覆盖索引
覆盖索引查询的列刚好与创建的索引列的列名及顺序全部匹配或者部分匹配
1、复合索引产生覆盖索引减少select *
2、如果查询列超出索引列也会降低性能。
3、执行情况描述using index 使用覆盖索引的时候就会出现using where在查找使用索引的情况下需要回表去查询所需的数据using index condition查找使用了索引但是需要回表查询数据using index ; using where查找使用了索引但是需要的数据都在索引列中能找到所以不需要回表查询数据 7、尽量少使用or
用or分割开的条件 如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。
示例
name字段是索引列 而createtime不是索引列中间是or进行连接是不走索引的
explain select * from tb_seller where nameAAA软件教育 or createtime 2088-01-01 12:00:00;
注使用and的时候有索引 8、 以%开头的Like模糊查询索引失效。
如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。
explain select * from tb_seller where name like 教育%
可以使用覆盖索引来解决 9、is null 和is not null
is null 和is not null都是走索引的
is null不会使索引失效
is not null 在不使用覆盖索引时会使索引失效在使用覆盖索引时不会使索引失效
explain select * from tb_seller where name is null;
explain select name from tb_seller where name is null;
explain select * from tb_seller where name is not null;
explain select name from tb_seller where name is not null;
10、in 走索引 not in 索引失效。 11、单列索引和复合索引
尽量使用复合索引而少使用单列索引 。
1、创建复合索引create index idx_name_sta_address on tb_seller(name, status, address);就相当于创建了三个索引 name name status name status address
2、创建单列索引create index idx_seller_name on tb_seller(name);create index idx_seller_status on tb_seller(status);create index idx_seller_address on tb_seller(address);数据库会选择一个最优的索引辨识度最高索引来使用并不会使用全部索引 。
2.5.3 查看索引使用情况
-- 查看本次会话的索引使用情况
show status like Handler_read%;
-- 查看从上次开启至今的索引使用情况
show global status like Handler_read%;
mysql show status like Handler_read%;
------------------------------
| Variable_name | Value |
------------------------------
| Handler_read_first | 3 |
| Handler_read_key | 319 |
| Handler_read_last | 0 |
| Handler_read_next | 236 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 7 |
------------------------------
7 rows in set (0.01 sec)
mysql show global status like Handler_read%;
------------------------------
| Variable_name | Value |
------------------------------
| Handler_read_first | 52 |
| Handler_read_key | 3950 |
| Handler_read_last | 0 |
| Handler_read_next | 4852 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 32183 |
------------------------------
7 rows in set (0.01 sec)
Handler_read_first索引中第一条被读的次数。如果较高表示服务器正执行大量全索引扫描这个值越低越好。
Handler_read_key如果索引正在工作这个值代表一个行被索引值读的次数如果值越低表示索引得到的性能改善不高因为索引不经常使用这个值越高越好。
Handler_read_next 按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列该值增加。
Handler_read_prev按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。
Handler_read_rnd 根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高意味着运行效率低应该建立索引来补救。
Handler_read_rnd_next在数据文件中读下一行的请求数。如果你正进行大量的表扫描该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。
2.6 sql优化
2.6.1 优化insert语句
创建一张表
CREATE TABLE tb_user_2 (id int(11) NOT NULL AUTO_INCREMENT,username varchar(45) NOT NULL,PRIMARY KEY (id),UNIQUE KEY unique_user_username (username)
) ENGINEInnoDB DEFAULT CHARSETutf8 ;
原始添加方案
insert into tb_user_2 values(2,Cat);
insert into tb_user_2 values(3,Jerry);
insert into tb_user_2 values(1,Tom);
1、连续添加
如果需要同时对一张表插入很多行数据时应该尽量使用多个值表的insert语句这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
-- mybatis 进行批量添加的时候
insert into tb_test values(1,Tom),(2,Cat)(3,Jerry);
2、使用事务进行添加
start transaction; -- 或者使用begin
insert into tb_test values(1,Tom);
insert into tb_test values(2,Cat);
insert into tb_test values(3,Jerry);
commit;
3、数据有序插入
insert into tb_test values(1,Tom);
insert into tb_test values(2,Cat);
insert into tb_test values(3,Jerry);
2.6.2 优化order by语句
-- 创建一张表
CREATE TABLE emp (id int(11) NOT NULL AUTO_INCREMENT,name varchar(100) NOT NULL,age int(3) NOT NULL,salary int(11) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4;
-- 添加数据
insert into emp (id, name, age, salary) values(1,Tom,25,2300);
insert into emp (id, name, age, salary) values(2,Jerry,30,3500);
insert into emp (id, name, age, salary) values(3,Luci,25,2800);
insert into emp (id, name, age, salary) values(4,Jay,36,3500);
insert into emp (id, name, age, salary) values(5,Tom2,21,2200);
insert into emp (id, name, age, salary) values(6,Jerry2,31,3300);
insert into emp (id, name, age, salary) values(7,Luci2,26,2700);
insert into emp (id, name, age, salary) values(8,Jay2,33,3500);
insert into emp (id, name, age, salary) values(9,Tom3,23,2400);
insert into emp (id, name, age, salary) values(10,Jerry3,32,3100);
insert into emp (id, name, age, salary) values(11,Luci3,26,2900);
insert into emp (id, name, age, salary) values(12,Jay3,37,4500);
-- 创建复合索引
create index idx_emp_age_salary on emp(age,salary);
两种排序方式
1). 第一种是通过对返回数据进行排序也就是通常说的 filesort 排序所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
explain select * from emp order by age desc;
2). 第二种通过有序索引顺序扫描直接返回有序数据这种情况即为 using index不需要额外排序操作效率高。
explain select id,age from emp order by age desc;
多字段排序
explain select id,age from emp order by age desc,salary desc; 了解了MySQL的排序方式优化目标就清晰了尽量减少额外的排序通过索引直接返回有序数据。where 条件和Order by 使用相同的索引并且Order By 的顺序和索引顺序相同 并且Order by 的字段都是升序或者都是降序。否则肯定需要额外的操作这样就会出现FileSort。
2.6.3 优化group by语句
由于GROUP BY 实际上也同样会进行排序操作而且与ORDER BY 相比GROUP BY 主要只是多了排序之后的分组操作。
当然如果在分组的时候还使用了其他的一些聚合函数那么还需要一些聚合函数的计算。所以在GROUP BY 的实现过程中与 ORDER BY 一样也可以利用到索引。
如果查询包含 group by 但是用户想要避免排序结果的消耗 则可以执行order by null 禁止排序。
如下
drop index idx_emp_age_salary on emp;
-- 优化前
explain select age,count(*) from emp group by age;
-- 优化后
explain select age,count(*) from emp group by age order by null;
上面的例子可以看出第一个SQL语句需要进行filesort而第二个SQL由于order by null 不需要进行 filesort 而上文提过Filesort往往非常耗费时间。
create index idx_emp_age_salary on emp(age,salary);
如果创建了索引那么就会使用覆盖索引进行查询。
2.6.4 优化嵌套查询
Mysql4.1版本之后开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作同时也可以避免事务或者表锁死并且写起来也很容易。但是有些情况下子查询是可以被更高效的连接JOIN替代。
示例 查找有角色的所有的用户信息
优化前
explain select * from t_user where id in (select uid from t_user_role);
优化后
explain select * from t_user u,t_user_role ru where u.idru.uid; 2.7 练习面试题
MySQL数据库中有1000万条数据现在需要删除500万条。DROP PROCEDURE IF EXISTS DeleteOverDueData;
CREATE PROCEDURE DeleteOverDueData()BEGINdeclare i INT DEFAULT 1;while i50 doDELETE FROM t_user LIMIT 50000;SET ii1;END WHILE;
END;
CALL DeleteOverDueData();
-- 效率较高
DROP PROCEDURE IF EXISTS DeleteOverDueData2;
CREATE PROCEDURE DeleteOverDueData2(IN min INT,IN max INT)BEGINSELECT min from t_user;select max from t_user;WHILE minmax DODELETE FROM t_user WHERE idmin AND idmax LIMIT 50000; ##一次性删除五万条CALL DeleteOverDueData2();END WHILE;
END;
CALL DeleteOverDueData2(50,3030021);
