导致sql 语句执行慢的原因
导致sql 执行慢的原因有很多，不一定是sql 语言的问题，但是在这的讨论默认为就是sql 语句导致的。一般有以下两方面的原因：
sql 的执行时间长：
- sql 语句写的太烂
- 索引失效：建立了索引，但是没有使用
- 关联查询有太多的join
等待的时间太长
SQL 的执行顺序：
自己写sql 语句的顺序：命令词，FROM ,ON,WHERE,GROUP BY,HAVING,ORDER BY
机器执行sql 语句的顺序：FROM ,ON,WHERE,GROUP BY,HAVING,命令词，DISTINCT,ORDER
索引
索引(index) 帮助Mysql尬笑获取数据的数据结构，索引的本质：数据结构
排好序的快速查找数据结构
索引会影响查找和排序，在sql 语句中的体现，影响where 后的条件 和 order by 后的排序
类比图书管理系统：如果没有图书查找系统，但当我们要查找某本书的时候，我们要一本一本的找图书馆中的书；但是当我们使用图书搜索，可以马上查到书在几楼的那个书架上（查找的过程相当于索引）
而数据库中是如何实现这种定位的：数据库中除了数据以外，还维护者满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，在这样就可以在这些数据结构上实现高级的查找算法，这种数据结构就是索引，一般没有特别的说明，索引都由B-Tree 数据结构组织。
利用排好序的数据结构，实现快速的查找，避免全表遍历。
一般索引都比较大不会全部保存在内存中，因此索引往往以索引文件的形式保存在磁盘上。
B-Tree 索引实现的理解：
可以把B-Tree 换为二叉树：
优势：
- 提高查找效率，提高I/O效率
数据库的数据文件保存在磁盘上，当我们需要对数据进行操作的时候就要将数据加载到内存中，这是I/O操作。数据在磁盘上存储的划分，区，块，段。(理解为一个表中的数据保存在不同的块上)例如现在进行一个查询操作：
没有索引的情况：要把查找范围内的所有数据块都加载到内存中，如下图加载7次
有索引的情况：数据块有一定的逻辑排序（B-Tree）要查找叶子节点的数据，I/O 操作的次数等于数的高度。如下图，加载三次。
- 减小排序的CPU消耗
数据存在磁盘上，但是在逻辑上是有顺序的，不需要在操作数据的时候对数据进行排序
劣势：索引也是一张表保存了主键和索引字段，并且指向了实体表的记录，所以索引列也是需要存储空间的，并且需要维护。
索引虽然提高了插叙的速度，但是会降低表更新的速度，再更新表中的数据的同时还要对索引进行修改（修改所以指向的位置）
数据库中的数据修改是创建新的数据列，并不是在原数据行上面进行修改
索引在实际的使用中要不断的优化和改变，不停的修改。
索引分类：
单值索引：一个索引包含一个单列，一个表可以有多个单列索引
唯一索引：索引列的值是唯一的
复合索引：一个索引包含多个列
关于索引的基本语法：
索引是数据库级别的
#创建
create [unique] index indeName ON tableName(columnName1,..)
#删除
drop index [indexName] on mytable
#查看
show index from tableName
------------------------------------------------------------------------------------------------------
#添加索引：alter
alter table tableName ADD primary key (column..)
#主键索引，其实在你创建表的时候创建了主键，数据库就会为你自动创建主键索引
alter table tableName ADD uinique(column...)
#唯一索引，同样的在创建表的时候写了唯一约束，数据库也会自动帮你创建唯一索引
alter table tableName ADD index indexName(column...)
#普通索引
alter table tableName ADD FULLTEXT (column...)
#全文索引
alter table tableName ADD index indexName('多个列名')
#多列索引
MySQL索引结构：B-Tree
需要建索引的情景：
主键会自动创建唯一索引
频繁作为查询条件的字段应该创建索引（单列或者多列索引，一般多列的情况比较多）
查询中作为其它标关联的字段，外键关系建立索引
频繁更新的数据不适合建立索引，在修改数据的同时还要修改索引指向的地址
where 用不到的查询调教不需要创建索引
单键/多键索引，在高并发的情况下更倾向于建立多键索引
在插叙中排序的字段，排序字段如果通过索引去访问将大大提高排序的速度
查询中的统计或者分组字段（分组也要先排序）
不要建索引的情景：
记录很少（索引在百万级的数据搜索上使用算得上是物尽其用）
经常更新的表（在更新数据的同时还要修改索引文件）
值是比较固定的值的列（例如性别，数据的变化值不大）没有必要创建索引
索引的选择性：索引列中不同数值的数目和表中数据的比，比值越大创建索引的效率就越高
Mysql 性能分析
我们在进行数据库操作的时候，使用者向数据库发送一个请求（例如SELECT）数据库中的解析器（Mysql Query Optimizer） 会对语句进行解析，优化，他是如何优化执行这个请求的发送请求的人是不知道的。有时候数据库认为最优的优化执行未必是我们希望的执行方式。
影响Mysql性能的还有可能是CPU，磁盘大小，服务器自己的相关配置
如何使我们可以看见sql 的优化执行，explain 命令可以模拟数据库中的优化执行sql 语句，从而知道mysql 是样处理你的sql 请求。
explain 命令
explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。
#数据库中一共有六条数据，id=16 是最后添加的数据。
EXPLAIN SELECT * from tb_type WHERE id=16;
表格中的字段的相关解释：
表连接(Join)下的索引优化
索引的创建是要不断的优化的，一般单表的索引创建要比较好测试出最优的索引，两个表（左连接索建在右表；右连接索引建在左表 ）
三表连接的情况下，和两表优化类似。
使用小集合驱动大集合，优先优化内层；要保证索引有效，就是你创建了索引在使用的时候要使用上
避免索引失效
创建了索引但是在使用的时候没有用到，如何避免索引失效
- 最左前缀法则：查询从最左边开始，并且中间不能跳过索引的中间列
如果你的索引是：5，7，9这三个属性组成的多值属性，那么在查询语句中的where 条件中 5一定要有，并且条件要连续
可以是 5 AND 7;
但是不能是 5 AND 9 ; 如果这样索引有一部分会失效。查找 5 字段的时候会使用索引但是查询字段9的时候就无法使用索引。
“带头不能少，中间不能断”
但是sql 请求会在数据库里面做优化，你的查询语句的顺序有可能会被数据库优化为最佳顺序
- 不在索引列上面做任何操作（计算，函数，（手动/自动）类型转化）。都会导致索引失效
EXPLAIN SELECT * FROM tb_type WHERE name='语言学习';
EXPLAIN SELECT * from tb_type WHERE left(name,4)='语言学习';
#left(),相当于string 的subString 方法
#这两个语句都能得到争取的答案，但是他们的效率是完全不同的，第二个语句的查询索引会失效。
- 索引尽量不要用在范围的查询语句上
将索引用于范围查询的语句，索引只会用户排序，不会用于查找
例如where age >5(假设在age 上面设置的索引)对于这个语句的查询，数据库并不能进行数据行的定位，where age= 5会立即定位到某行数据，但是where age>5数据要是做的还是要从5 开始找age >5 的数据行，因为查找的本身就是一个范围，所以这个操作根本就是不是操作一行数据可以完成的，所以创建索引提高查询速度的意义也不大。
范围之后的索引都会失效，做范围查询的不会使用索引进行查询但是会在排序的时候使用索引
-
覆盖索引：在查找的时候尽量保证，索引列和目标列相同(select 后面的目标列)，尽量减少
select*
例如：创建的索引 table(name,age)
那么在查询的时候：where name from table /where age from table/where name,age from table
保证目标列是索引列的一个子集，并且顺序一致。 -
在使用
!= ,> ,<时索引会失效导致全表搜索 -
is null / is not null也无法使用索引 -
使用
like以通配符% 开头索引也会失效， 在索引失效的时候可以采用覆盖索引的搜索方式，可以将效率提高 ，不会全表搜索。#例如现在创建的多值索引：Student(Sname,Sage),主键 id select * from Student where Sname like '%小%' #这样的查询语句会搜索全表找出数据，不会使用索引 select id,Sname from Student where Sname like '%小%' # 这样的搜索会使用索引 select id,Sname,Sage,email from Student where Sname like '%小%' #(email 没有建索引) 不会使用索引会全表搜索 -
字符串没有单引号也会导致索引失效；在底层数据类型会发生隐式转换。
-
条件中使用or 也会导致索引失效
总结：
多值索引开头不能缺，中间不能断（中间断了，后面的就不能使用索引了，但是前面的部分可以使用）
在索引列上进行范围查询会失效
覆盖索引更佳
字符串引号不能少
使用OR索引会失效
会使索引失效的查询方式不是不能用，在实际情况是要具体考虑，不能实际就是需要这样查询但你不这样写那也不行。所以这也体现出了，索引是需要不断的优化的。
#创建了符合索引 Student(Sname,Sage,Smajor)
查找的时候会先查Sname,再查Sage，最后查Smajor.所以如果中间那个索引失效，那么后面的索引也会失效
order by /group by
多值索引
order by /group by 后面的索引也要按照索引顺序排序（在where 以后的索引排序要满足索引的顺序），不然会大大的降低效率。order by 会产生内排序，group by 会产生临时表
order by 和group by 后面使用索引目的不是在查询的时候使用索引而是在排序的时候使用索引，提高排序效率
# 假设创建索引 T(a1,a2,a3,a4)
select * from T where a1='' and a2='' and a4='' order by a3='' #查询的时候使用a1,a2 ,排序的时候使用a3
select * from T where a1='' and a2='' order by a4='' # 查询的时候使用a1,a2 ，排序时会产生内排序导致性能下降(filesort)
select * from T where a1='' and a2='' and a5='' order by a3='' #查询的时候使用a1,a2 ,排序的时候使用a3
select * from T a1='' order by a3='',a2='' # 查询的时候使用a1 ,但是在排序的时候产生内排序，导致性能下降
select * from T a1='' and a2='' order by a3='',a2=''
# 这个查询排序也是有效的，查询的时候使用a1,a2.排序的时候使用a3.这里不按顺序仍然正确的原因
#a2 在之前的查询中使用过，其实此时的a2 就是一个常量，所以对于order by 相当于a3,常量。所以查询没有问题。
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
