一、基本概念
1. 查询是在数据库中查找到合适数据，并返回结果集
2. 查询优化可以从如下几个方面去解决：
2.1 为数据表编写索引
2.2 如何最大程度的利用索引
二、优化方式
- 优化第一个着手点：该字段是否有索引及是否生效
- 滥用索引，有可能反而会导致查询性能变慢
1. 常见索引
1. PRIMARY KEY（主键索引）： 列值唯一（不可以有null， 表中只有一个
2. UNIQUE(唯一索引) ： 列值唯一（可以包含一个null）,一个表中可以有多个唯一索引
3. 加INDEX(普通索引) ： 仅仅为查找速度加快
4. FULLTEXT(全文索引) ： 对文本的内容进行分词，进行搜索
5. 多列索引： 多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
-- 展示所有索引
show index from table_name
2. 索引引入
2.1 使用前
- 不添加索引，数据行无序， select * from table where company_num < = 17。会对每一行数据去判断字段否满足条件
- 如果表中数据很大，但符合查询条件的数据行很少。遍历数据去检索很浪费时间的
2.2 引入后
- 查找为13数据，索引到14时，则不会继续往下匹配，直接返回结果
- 添加索引，索引值是经过分类的，因此从第几行数据开始及结束(定位算法)
- 数据表中支持多索引，多个索引单独在查询时候进行工作；
- 索引中的数据行一般会比数据文件的数据行短一些，当增删该数据时，来回移动较短的索引值比来回移动较长的数据文件的数据方便一些
- 对于写数据很频繁的数据表，不宜建立太多的索引，因为每次写数据，都要对该表的所有索引进行重新排列；
2.3 索引位置
- 索引位置和具体的数据库引擎有关，mysql5.5之后默认内置存储引擎是InnoDB
- InnoDB使用一个表空间，将所有的InnoDB类型的数据表的数据和索引存储。可以通过配置，使其为每个数据表分别创建一个自己的表空间，但是该表的数据和索引，都是保存在自己的同一个表空间里面；
3. 索引查询优化解释
- where子句后的字段的匹配(如上面的例子)；
- 关联操作中与其他数据表里的数据行相匹配的时候；
#三个表t1，t2，t3，每个数据表都各自包含c1，c2，c3的列，每个数据列包含1-1000;
select t1.c1，t2.c2，t3.c3 from t1 inner join t2 inner join t3 where t1.c1=t2.c2 and t1.c1= t3.c3;
- 如果不使用索引，则检索基数100010001000 = 10亿种数据组合，再从其中筛选符合的1000中；
- 对t2.c2，t3.c3添加索引后： t1表中检索第一行数据，t2的时候，可以直接匹配到相等的数据(定位算法)，同理可以匹配到t3；t1表仍然使用的是全局搜索，但是t2和t3都会很快得到匹配的列；
- 使用MIN(),MAX()函数的列，添加索引后可直接定位到数据，而不用全表扫描；
- 使用order by 和group by的字段，添加索引可快速响应；
4. 索引缺点
- 频繁写数据的表：写数据的时候，不仅要为数据列写入数据，而且要更新该表中所有字段上的索引项，索引 更新方面的开销会很大，写入性能较差
- 索引需要占用一定的磁盘空间，滥用索引导致表空间很快达到上限；
5. 索引注意
- where子句后：=匹配字段，order by，group by，min()，max()，以及联查(最适合)，不需要为select 后面的字段添加索引；
- 数据列维度值： 一个列中出现的不重复的个数，如一个列中包含1,2,3,4,4,4,5，那么维度值为五；维度越高，不重复的数据越多，索引也就能更好的对数据行进行分区。如果一个数据列只有两个值如性别，即使添加索引，但查询优化程序发现某个值的出现频率超过30%，就会放弃索引而进行全局扫描，索引失效；
- 数据列的值尽量短小： 尽量选用比较小的数据类型，比如char(20)能满足，就不要用char(100)，可以提高索引的性能；
- 短小的值可以让比较操作更快进行，加快索引查找速度；
- 短小的值可以让索引体积更小，减少磁盘IO工作
- 字符串的前缀添加索引： 假如一个char(200)的字符串，前10个字符串都是唯一的，后面的字符串相同，则仅仅为前面10个添加索引，就是变相的把数据列的值减小了;
- 不要过分使用索引： 过分使用索引，会导致写入性能极大降低；
三、mysql查询优化器
1. 概念
发出一条sql查询时，mysql会分析这条sql，并考虑是否可以优化该条sql以加快查询；
2. 优化机理
- 主要针对的是查看该条sql是否可以用索引来进行查询；
- 以最严格的查询条件，尽可能多，快地将数据排除；
- 可以通过在查询的sql前面加explain来查看具体的执行；
# 1. 最严格的条件： false条件，将所有数据都会排除，而不再去用id去匹配了(ID带索引的)
select * from cnip where id>1000 and false;
# 2. 条件一满足的条数：900个，条件二满足的条数：300个， 两个都满足的条数：70；
# 查询优化器先检索条件二，再去检索条件一，尽可能每次检索都能更多更快排除不符合的数据
select * from cnip where column1="value1" and column2="value2";
3. 优化查询具体方案
- 尽量使用数据类型相同的列比较： 如果两个列都带索引，两个列数据类型相同时，查询的性能就会高一些；(如Char(10)和Varchar(10)，Int和BigInt就属于不同的数据类型)，索引虽然不会失效，但是查询性能会受到影响；
- 使带索引的列在表达式中单独出现： 如果某个带索引的列，参与了数字运算或者函数，就会导致索引失效，但可以优化sql来进行改善；
# id带索引，但是查询的时候索引失效，因为要把所有id检索到，并去除2，改为下面进行优化
# 可以通过explain来查看具体索引是否失效了
explain select * from cnip where id*2<1000;
explain select * from cnip where id<1000/2;
# 给birthday建立索引，按照第一个查询，索引失效，可以改为第二行的
explain select * from student where YEAR(birthday)<'2020';
explain select * from student where birthday<'2020-01-01';
- 不要在like开始的地方写出通配符----索引失效
-- 模糊匹配，索引可以正常使用，下面两个sql等价
select * from student where name like 'sd%';
select * from student where name >= 'sd' and name<='sf';
-- 模糊匹配，索引失效，进行全局搜索
select * from student where name like '%sd%';
- 尽量不要在sql中使用or----导致索引失效：
explain select * from student where name = '4' or name = '3';
- 避免过多使用mysql的类型转换----导致索引失效：
# name字段为字符串，这样写，系统会将'4'，'4th'，'4.0'都会转换为4，因此会放弃索引全局搜索
explain select * from student where name = 4;
# 可以正常使用索引
explain select * from student where name = '4';
- 当mysql分析全表扫描比使用索引快的时候 ---- 导致索引失效
- 多使用联结查询而不是子查询： 很多时候联结查询可能会比子查询速度快很多；
4. explain语句详解
explain select * from student where name like 'sd%'
- select_type： 包含SIMPLE等；
- table： 查询的表名；
- partition： 数据的分区；
- type： 数据查找的类型；
- all： 全表扫描，一般这种的具有最大的优化空间；
- range： 基于索引的扫描；
- possible keys： 可能用到的索引；
- key： 实际用到的索引；
- key_len： 索引长度；
- **row：**数据要最少检索几行才能得到所有结果；
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
