mysql事务、视图、索引 总结

此文章为个人学习时所总结 有问题还请评论 留言~ 支持的请点个赞或收藏~

文章目录

事务事务的ACID(acid)特性事务特性直观明了的总结事务的创建和使用 视图基本概念视图的基本使用使用视图的好处 索引索引优势索引劣势索引分类索引相关命令语句四种方式来添加数据表的索引:mysql索引结构哪些情况需要创建索引哪些情况不要创建索引Explain详解*Explain能干嘛Explain用法EXPTION查询概要描述一、 id二、select_type三、table四、type五、possible_keys六、Key七、key_len八、ref九、rows十、Extra 索引优化单表优化两表优化三表优化如何避免索引失效避免索引失效方法总结索引总结

事务

事务的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查询，查询中不包含子查询或者UNION2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记3、subquery——在select或where列表中包含了子查询4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived6、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以通配符开头（‘