此文章为个人学习时所总结
有问题还请评论 留言~
支持的请点个赞或收藏~
文章目录
事务
事务的ACID(acid)特性
➢ 1. 原子性(Atomicity)
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
➢ 2. 一致性( Consistency)
事务必须使数据库从一个一致性状态 变换到另外一个 一致性状态
➢ 3. 隔离性( Isolation )
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
➢ 4. 持久性( Durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响
事务特性直观明了的总结
原子性(Atomicity): 一个事务不可再分割，要么都执行要么都不执行
一致性( Consistency): 一个事务执行会使数据从一个 一致状态切换到另外一个 一致状态
隔离性( Isolation ): 一个事务的执行不受其他事务的干扰
持久性( Durability): 一个事务一旦提交，则会永久的改变数据库的数据.
事务的创建和使用
隐式事务：事务没有明显的开启和结束的标记
比如 insert、update、delete语句
delete from 表 where id =x ;
显式事务： 事务具有明显的开启和结束的标记
前提： 必须先设置自动提交功能为禁用
SHOW VARIABLES LIKE “auto%” 执行后可查看到事务默认为自动提交的
set autocommit=0; 关闭事务自动提交，只对当前事务生效一次，不是永久关闭
SET AUTOCOMMIT=1 开启自动提交
事务使用的步骤
步骤 1. 关闭自动提交事务
set autocommit=0;
start transaction; （可选的）
步骤 2.编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...
步骤 3.结束事务
commit; 提交事务
rollback; 回滚事务
开启事务的语句：
update 表 set 用户1余额=500 where name='用户1'：
结束事务的语句
update 表 set 用户2余额=1500 where name = '用户2';
事务使用步骤例子
#建表语句
DROP TABLE IF EXISTS account;
CREATE TABLE accout(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
INSERT INTO accout (username, balance)
VALUES
('admin',1500),('tizi',3000);
#同时全部执行以下sql语句
#关闭自动提交事务
SET autocommit=0;
0START TRANSACTION;
#编写一组事务的语句
UPDATE accout SET balance= 1000 WHERE username='admin';
UPDATE accout SET balance=1200 WHERE username='tizi';
#结束事务
COMMIT;
SELECT * FROM accout;
#添加数据 体验事务的回滚
INSERT INTO accout (username, balance)
VALUES
('test1',1500),('test2',3000);
#指定保存点，保存点名为back
SAVEPOINT insert;
#可再次添加多数据 等会数据表数据可直接回滚到back的保存点的状态
INSERT INTO accout (username, balance)
VALUES
('test13',1500),('test14',3000)，('test15',3000);
SAVEPOINT back;
#回滚到back的保存点中，可看到保存点之后所有insert的数据都已没有
ROLLBACK TO SAVEPOINT back;
视图
基本概念
视图是一个虚拟表，是sql的查询结果，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成。视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ] ; 创建视图需要create view 权限，并且对于查询涉及的列有select权限；使用create or replace 或者 alter修改视图，那么还需要改视图的drop权限。
CREATE VIEW v1
AS
SELECT s.name,s.pos FROM class c
INNER JOIN staffs s
ON c.`id` = s.`id`;
SELECT * FROM v1 WHERE NAME LIKE "%1";
视图的基本使用
如何创建视图(基本语法)
create view 视图名 as select 字段名 from 表名;
Mysql视图的定义在from关键字后面不能包含子查询
修改了视图，对基表数据有影响
修改了基表，对视图也有影响
如何修改视图
基本语法
alter view 视图名 as select 语句;
alter view 视图名 as select 视图;
显示视图创建情况
show create view 视图名;
查看视图
Show tables；
Show table status [ from db_name ] [ like ‘pattern’ ]****；
SELECT * FROM information_schema.views where table_name = ‘my_view’;
删除视图
drop view 视图名[,视图名…];
重命名视图
Rename table 视图名 to 新视图名;;
使用视图的好处
1、安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图， 在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2、性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN) 。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3、灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。
视图详解参考链接： https://www.cnblogs.com/cshaptx4869/p/10481749.html
索引
索引（Index）是帮助mysql高效获取数据的数据结构，可以得到索引的本质，索引是数据结构.
排好序的快速查找数据结构.
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，
这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引 .BTREE、
索引优势
类似于大学图书馆建书目索引，提高数据检索的效率，降低数据库的io成本
通过索引对数据库进行排序，降低数据排序的成本，降低了cpu的消耗
索引劣势
1、实际上索引页是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以也是要占用内存空间的
2、虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update、delete
3、因为更新表时，mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会
新带来的键值变化后的索引信息。
4、索引只是提高效率的一个因素，如果mysql有大数据量的表，就要花时间研究建立最优秀的索引，或优化查询
索引分类
单值索引：一个索引只为一个列或者字段建立的索引叫做单值索引
唯一索引：通常每个表的主键为该表的唯一索引，允许有空值
复合索引：为多个列建立的索引称为复合索引
索引相关命令语句
创建索引
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length),....);
ALTER table tablename ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
创建举例
CREATE INDEX bname ON books(bookname,detail);
ALTER TABLE test ADD INDEX idx_address(address)
删除索引
DROP INDEX [indexName] ON mytable;
查看索引
SHOW INDEX FROM table_name #尾加上\G后，cmd的形式操作会以行的形式展现
四种方式来添加数据表的索引:
ALTER TABLE tbl_ name ADD PRIMARY KEY (column_ list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
ALTER TABLE tbl_ name ADD UNIQUE index_ _name (column_ list). 这条语句创建索引的值必须是唯一的(除了NULL外，NULL 可能会出现多次)
ALTER TABLE tbl name ADD INDEX index_ name (column_ list): 添加普通索引，索引值可出现多次
ALTER TABLE tbl_ name ADD FULLTEXT index_ name (column_ lst):该语句指定 了索引为FULLTEXT，用于全文索引
mysql索引结构
*BTree索引：
检索原理：
[初始化介绍]
一棵b树， 浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)，
如磁盘块1包含数据项17和35，包含指针P1、P2、P3,
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、 28、29、 36、60、 75、79、90、 99。
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
[查找过程]
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次I0，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短(相比磁盘的I0)可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次I0，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次I口，同时内存中做二分查找找到29，结束查询，总计三次I0。
真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次I0，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IQ，显然成本非常非常高。
做全栈开发只需了解btree即可，以下三种索引可不掌握
Hash索引
full-text全文索引
R-Tree索引
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系建立索引
- 频繁更新的字段不建立索引，因每次更新不单单是更新了记录还会更新索引
- where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序效率
- 查询中统计或者分组字段
哪些情况不要创建索引
- 表记录太少
- 经常增删改查的表。提高了查询速度，同时会降低更新表的速度，如对表进行insert、update、delete 更新表时，mysql不仅要保存数据，还要保存索引文件
- 某个数据列包含许多重复的内容，为他建立索引没有太大的实际效果
性能调优三步走：优化器、看瓶颈、Explain
Explain详解*
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。
Explain能干嘛
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 那些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
Explain用法
explain+SQL语句即可
-- 实际SQL，查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可，explain相当于sql的体检报告单
explain select * from emp where name = 'Jefabc';
explain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
EXPTION查询概要描述
id: 选择标识符
select_type: 表示查询的类型。
table: 输出结果集的表
partitions: 匹配的分区
type: 表示表的连接类型
possible_keys: 表示查询时，可能使用的索引
key: 表示实际使用的索引
key_len: 索引字段的长度
ref: 列与索引的比较
rows: 扫描出的行数(估算的行数)
filtered: 按表条件过滤的行百分比
Extra: 执行情况的描述和说明
下面对这些EXPTION查询出的字段进行详细解释
一、 id
SELECT识别符。这是SELECT的查询序列号
我的理解是SQL执行的顺序的标识，SQL从大到小的执行
-
id相同时，执行顺序由上至下
-
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
-
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
-- 查看在研发部并且名字以Jef开头的员工，经典查询
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研发部';
二、select_type
示查询中每个select子句的类型
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
三、table
显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称
四、type
对表访问类型排列，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有(从最好到最差依次排列)：
**system > const > eq_ref > ref >r ange > index > ALL **
system：表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计
const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref： 非唯一性索引扫描，返回匹配某个单独值的所有行本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找封多个符合条件的行，所以他应该属于查找和扫描的混合体
range： 只检索给定范围的行，使用一个索引来选择行
index： Full Index Scan，index与ALL区别为index类型只遍历索引树
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
一般来说，得保证查询至少达到range级别.最好能达到ref
五、possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
六、Key
key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中，如为null，则没有使用索引
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
七、key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好
八、ref
列与索引的比较，显示索引的哪一列被使用了，是一个常数，即哪些列或常量被用于查找索引列上的值
九、rows
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
rows用的越少越好
十、Extra
该列包含MySQL解决查询的详细信息,有以下几种情况：
Using filesort*(九死一生)：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”
Using temporary*:(十死无生) 使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
Using index*(ok): 表示相应的select操作用使用覆盖索引，避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using
-- 测试Extra的filesort
explain select * from emp order by name;
Using join buffer：使用了连接缓存。改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used：Query语句中使用from dual 或不含任何from子句
-- explain select now() from dual;
Explain详解表格形式展现
| 信息 | 描述 |
|---|---|
| id | 查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序 两种情况 id相同，执行顺序从上往下 id不同，id值越大，优先级越高，越先执行 |
| select_type | 查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询 1、simple ——简单的select查询，查询中不包含子查询或者UNION 2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记 3、subquery——在select或where列表中包含了子查询 4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6、union result:UNION 的结果 |
| table | 输出的行所引用的表 |
| type | 显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序 1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。 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快，因为索引文件比数据文件小很多。 7、all：遍历全表以找到匹配的行 注意:一般保证查询至少达到range级别，最好能达到ref。 |
| possible_keys | 指出MySQL能使用哪个索引在该表中找到行 |
| key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。 |
| key_len | 表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。 |
| ref | 列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 |
| rows | 根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数 |
| Extra | 包含不适合在其他列中显示，但是十分重要的额外信息 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。 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操作，在找到第一匹配的元组后即停止找同样值的动作。 |
总结：
- EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
- EXPLAIN不考虑各种Cache• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
- 部分统计信息是估算的，并非精确值
- EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。
索引优化
单表优化
CREATE TABLE IF NOT EXISTS article(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) UNSIGNED NOT NULL,
category_id INT(10) UNSIGNED NOT NULL,
views INT(10) UNSIGNED NOT NULL,
comments INT(10) UNSIGNED NOT NULL,
title VARBINARY(255) NOT NULL,
content TEXT NOT NULL
);
INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,1,3,'3','3');
SELECT * FROM article;
优化案例
#单表优化 `atricle` 表
#需求：查询category_id为1且comments大于1的情况下,views最多的article_id,查询后分析sql
EXPLAIN SELECT * FROM `article` WHERE category_id =1 AND comments>1 ORDER BY views DESC LIMIT 1;
#结论:很显然,type是ALL即最坏的情况。Extra 里还出现了Using filesor,也是最坏的情况。优化是必须的。
SHOW INDEX FROM article;
#开始优化
#1.1新建索引+删除索引
ALTER TABLE article ADD INDEX idx_article_ccv(category_id,comments,views);
CREATE INDEX idx_article_ccv ON `atricle`(category_id,comments,views);
SHOW INDEX FROM `article`
#此时 type全表扫面已经解决，但是extra还有问题 using filesort 说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序
EXPLAIN SELECT * FROM `article` WHERE category_id =1 AND comments>1 ORDER BY views DESC LIMIT 1;
#尽量提出=1的范围查询 不然后面的会失效
EXPLAIN SELECT * FROM `article` WHERE category_id =1 AND comments=1 ORDER BY views DESC LIMIT 1;
#正确解决方法，删除最开始创建的索引
DROP INDEX idx_acricle_ccv ON `article`;
#修改后的索引
CREATE INDEX idx_article_cv ON article(category_id,views)
#既不出现全表扫描，mysql无法利用索引对后面的views部分进行检索，即range类型查找后面的字段失效
SHOW INDEX FROM `article`
#此时完美解决
EXPLAIN SELECT * FROM `article` WHERE category_id =1 AND comments>1 ORDER BY views DESC LIMIT 1;
两表优化
CREATE TABLE IF NOT EXISTS class(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS book(
bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (bookid)
);
#建议20条
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
优化案例
#两表优化 `book` 和 `class`表
SELECT * FROM book,class WHERE book.`card`=class.`card`;
SELECT * FROM class INNER JOIN book ON class.`card`=book.`card`;
#结论 type为all 全表扫面
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.`card`=book.`card`;
#试着给book表建立一个索引,此时book表的type正常
ALTER TABLE book ADD INDEX idx_book(card);
DROP INDEX idx_book ON book;
#试着给class表建立一个索引,此时class表的type为index。比book表的状态更好
#由于左连接的特性决定的，left join条件用于确定如何从右表搜索行，左边一定都有
#所以右编是我们的关键点，一定需要建立索引
ALTER TABLE class ADD INDEX idx_class(card);
DROP INDEX idx_class ON class;
SHOW INDEX FROM book;
SHOW INDEX FROM class;
ALTER TABLE book ADD INDEX idx_book(card);
#试着将left join改为right join 查看效果，建立索引
三表优化
CREATE TABLE IF NOT EXISTS phone(
phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(phoneid)
)ENGINE=INNODB;
#建议20条
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));
SELECT * FROM phone;
优化案例
#三表优化 `book` `class` `phone`表
ALTER TABLE phone ADD INDEX idx_phone(card);
#sql实例
SELECT * FROM class LEFT JOIN book ON class.`card`=book.`card` LEFT JOIN `phone` ON book.`card` = phone.`card`;
#分析sql
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.`card`=book.`card` LEFT JOIN `phone` ON book.`card` = phone.`card`;
#type为ref表示优化很好，效果不错，因此索引最好设置在需要经常查询的字段中
#结论
#join语句的优化
#尽可能的减少join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”
#优先优化NestedLoop的内层循环
#保证join语句中被驱动表上join条件字段被索引且内存资源充足的前提下，不要太吝惜joinBuffer的设置
如何避免索引失效
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(24) NOT NULL DEFAULT '' COMMENT'姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(20) NOT NULL DEFAULT '' COMMENT'职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT '员工记录表';
insert into staffs(name,age,pos,add_time) values('zhi1',20,'manger',now());
insert into staffs(name,age,pos,add_time) values('zhi2',23,'dev',now());
insert into staffs(name,age,pos,add_time) values('zhi3',22,'dev',now());
SELECT * FROM staffs;
ALTER TABLE staffs ADD idx_staffs_nameAgePos(NAME,age,pos);
SHOW INDEX FROM staffs;
#避免索引失效
SHOW INDEX FROM staffs;
ALTER TABLE staffs ADD idx_staffs_nameAgePos(NAME,age,pos);
EXPLAIN SELECT * FROM staffs WHERE NAME='zhi2'
EXPLAIN SELECT * FROM staffs WHERE NAME='zhi1' AND age =20;
EXPLAIN SELECT * FROM staffs WHERE NAME='zhi1' AND age =20 AND pos='manger';
#索引失效
EXPLAIN SELECT * FROM staffs WHERE age=23 AND pos='dev';
EXPLAIN SELECT * FROM staffs WHERE pos='dev';
#最左前缀法则，除去name外，其他两个索引全失效。因为建立的索引第一个字段不能丢失，相当于一个火车头，没有头，后面的怎么跑?有头后后面的车厢链接运行
EXPLAIN SELECT * FROM staffs WHERE NAME="zhi3"
#虽然引用到了索引，但只用到了一部分，三个索引中，中间的未使用
EXPLAIN SELECT * FROM staffs WHERE NAME="zhi3" AND pos="dev";
#最左前缀法则：1.索引带头大哥不能si 2.中间兄弟不能断
避免索引失效方法总结
-
全值匹配查询我最爱
-
最左前缀法则：如果索引了多列，要遵守最左前缀法则，指的是查询 从索引的 最左前列开始并且 不跳过索引中间的列 简单了解就是 1.索引带头大哥不能si 2.中间兄弟不能断
-
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
-
存储引擎不能使用索引中范围条件右边的列
-
尽量使用覆盖索引（脂肪纹索引的查询（索引列和查询列一致）），减少select *
EXPLAIN SELECT NAME,age,pos FROM staffs WHERE NAME=“zhi3” AND age>20 AND pos=“dev”;
-
使用不等于( !=或者< 和 >)的时候无法使用索引会导致全表扫描
-
is null，is not null 无法使用索引
-
like以通配符开头（‘%abc…’）mysql索引失效会变成全表扫描的操作。%abc%全表扫描。abc%这样才会避免索引失效，但结果有问题
问题：解决like ‘%字符%’ 索引不被使用的方法，覆盖索引
create index idx_user_nameAage on table(name,age);
select 字段 from table; #索引不会失效
select * from table; #索引会失效
-
字符串不加引号索引会失效
-
少用or，用它来连接时索引会失效
索引总结
-
复合索引的使用一定是从建立索引时列的顺序开始,1,2,3…排序,使用时1不能少,第一位索引没有,则后面索引失效,或者第一位索引存在,后面紧接第三位索引,通常从第一位索引后开始索引失效,最好的状态是三个使用都能够使用到,也就是能够全职匹配
-
不允许在索引列上做任何的函数或者类型转换操作,否则会导致索引失效
-
尽量避免在sql中使用范围查找,如使用范围查找极大可能会导致,范围查找后的缩小失效
-
尽量使用覆盖索引,什么是覆盖索引呢,也就是在select 后面的查询列尽量和建立的索引列一致,查询列只能比索引列少,不能多,一旦查询列比索引列多,则无法使用覆盖索引
-
mysql在使用了 != 或者<> 后索引容易失效
-
mysql使用 is null 或者 is not null 也会导致索引失效
-
mysql的sql查询中,字符串数据不加单引号索引也会失效,上面已经提到过了,索引列不允许做函数和数据类型转换,字符串不加单引号虽然也可以使用,那是因为mysql在底层为你做了数据类型转换
-
like 以 “%123” 通配符开头的sql,like后面的索引将会失效,而如果%不放在第一位,比如"123%",索引失效的概率较小.
-
少用or,用它来连接时索引为失效
表连接时,索引的使用情况
-
当两张表使用左连接时,索引应该尽量建立在右边中,因为左连接左边的表示必须全部显示出来的,也就是说左边的表必须是全表扫描,所以左连接索引最好建立在右边中,反之右连接也是如此
-
多表连接的话,有左连接永远先加右边的索引
group by 和 order by 索引排序
如果对某一个列进行排序或者分组,且排序或者分组的不是第一位索引,那么第一位的索引需要出现在where后面,且不能是范围查询,这样排序或者分组的索引才能够生效,单独使用某一个索引进行排序极大可能会出现失效的情况
mysql索引总结可参考链接：https://blog.csdn.net/weixin_42214548/article/details/80981168.
感谢参考，总结全文不易，点个赞支持下呗，您的支持是我最大的动力 !
1298
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
