文章目录

  • 先修：为什么用B+树存放索引？
  • 索引优化规则
  • 一、SQL优化
  • 二、定位慢查询SQL
  • 三、查看具体SQL执行时间
  • 四、执行计划显示的extra信息
  • 一些常识
  • 一、MySQL的层次
  • 二、delete加limit
  • 三、IP地址用int存
  • 四、语句执行顺序


先修：为什么用B+树存放索引？

MySQL中InnoDB引擎的索引是B+树，具体要去了解一下为什么不用其他的数据结构

链表：显然不合适，不方便查找

BST：当数据是递增的时候，退化成了链表

AVL：能保持树的平衡，但条件严格，高度不能超过2，需要不停左旋右旋，是一种插入慢，检索快的数据结构

红黑树：算是BST和AVL的折中版本，但如果数据量过，一侧的树会很深

B树：一个磁盘块16KB中能存的数据较少，存放的是index和data

B+树：只有叶子结点才会有数据data，并且是按顺序排的，存放的数据更多

索引列如果是varchar，优化的时候可以进行截取，以减少在磁盘中索引占据的位置，索引长度应尽量短。

*.frm：数据表结构相关信息存储的文件

*.idb：索引和数据存储的文件

注意：*.idb 这个文件本身就是 B+Tree 的文件，叶子节点包含完整的数据记录。

索引优化规则

一、SQL优化

组合索引如果带了>、<、between、like，则会失效，就是被计算了，还有情况是第一个字符是%，也会失效，还有函数、隐式转换，int变varhcar。

归结起来就是：

1. 使用最左前缀规则（组合索引里面的）

index(a,b,c)

where语句

索引使用状态

where a=3

使用了a

where a=3 and b=5

使用了a,b

where a=3 and b=5 and c=4

使用了a,b,c

where b=3 或者where b=3 and c=4

没使用

where a=3 and c=5

使用了a，没有c，因为b断掉了

where a=3 and b>5 and c=4

使用了a,b，c不能又在范围之后，b断掉了

where a=3 and b like’K%’ and c=4

使用了a,b,c

where a=3 and b like’%K’ and c=4

使用了a，bc因为like里面的%在第一位断掉

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引。

2. 索引列不能参与计算

假设salary上有一个索引，那么salary*12>10000不可取，要写成salary>10000/12

3. 模糊查询不能利用索引

那么 like ‘%引’ 不可取，查看业务逻辑，进行修正，第一个不可以是%

因为%号，模糊的条件，会进行逐个查找，这是全表扫描

4. 索引不能创建过多

5. 索引不能频繁更新

null不走索引，走的是全文扫描，所以尽量避免比null，可以设定默认值为0

少用select * 尽量使用具体的id，以及多使用别名.id ，因为这样可以使用到覆盖索引，而且大概率不会出现歧义

6. 数据量少的时候放弃索引查询会更快

7. 保证从内存中读取数据，把数据保存在内存中

推荐将数据全然保存在 innodb_buffer_pool_size ，即按存储量规划 innodb_buffer_pool_size 的容量。这样你能够全然从内存中读取数据。最大限度降低磁盘IO操作。

SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';

8. not in和<>查询以及子查询

最好把这类查询自行改写成关联查询。

子查询执行效率不高，使用关联查询(join)代替子查询。

效率不高的原因：查询的过程中需要建立一个临时表，查询完毕，再撤销临时表。消耗性能。临时表都不会使用索引。

--修改前：
SELECT id,name,email FROM customer 
WHERE id 
NOT IN(SELECT id FROM payment)
--修改后：
SELECT a.id,a.name,a.email 
FROM customer a 
LEFT JOIN payment b ON a.id=b.id 
WHERE b.id IS NULL

9. 少用or连接条件

应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20
--可以这样查询：
select id from t where num=10
union all
select id from t where num=20

10. 给被驱动表添加索引，驱动表加不加都行

优化器满足小表驱动大表

如：student是驱动表，book是被驱动表 ：

EXPLAIN SELECT * FROM student LEFT JOIN book ON student.card = book.card；

11. limit相关操作

这个太好了，我就搬过来了，

mysql在mybaitis中的 begin end mysql bnl_b树

如果是全表扫描，加上limit 1。找到数据就不会再继续查找了，加快查找效率
如果是唯一索引，找到数据也不会继续查找了，limit 1 就不管用了。

12. 用union all替换union
当SQL语句需要union两个查询结果集合时，即使检索结果中不会有重复的记录，如果使用union这两个结果集同样会尝试进行合并，然后在输出最终结果前进行排序，因此如果可以判断检索结果中不会有重复的记录时候，应该用union all，这样效率就会因此得到提高。

二、定位慢查询SQL

slow_query_log：是否启动慢查询日志，默认不启动，on 启动；
slow_query_log_file：指定慢查询日志的存储路径及文件，默认保存在 MySQL 的数据目录中；
long_query_time：指定记录慢查询日志 SQL 执行时间的阈值，单位秒，默认10，对于一个繁忙的系统，改为0.001比较合适；
log_queries_not_using_indexes：是否记录未使用索引的 SQL；
set global slow_query_log = on;
set global long_query_time = 1;

和二进制日志不同，慢查询日志会记录所有符合条件的 SQL，包括查询语句、数据修改语句、已经回滚的 SQL。需要使用一些工具来进行分析。

三、查看具体SQL执行时间

set profiling = 1 开启显示数据查询时间

show profiles 查看粗略的，去掉s，可以查看详细的时间

查看正在执行的线程，并按Time倒排序，查看执行时间过长的线程。

select * from processlist where Command != 'Sleep' order by Time desc;

四、执行计划显示的extra信息

explain 一个sql语句之后查看执行计划的extra额外信息这一栏：

null：回表：带着数据又回去查了一遍

using index：覆盖索引：

将被查询的字段，建立到组合索引（如果只有一个字段，普通索引也可以）里去。如以下这句sql，我在name字段建立了一个索引

explain select id,name from student

这是因为索引叶子节点存储了主键id，而name也是索引，所以查询为覆盖索引。

当我们添加一个不是索引的字段时，那么就无法从索引树上查询，必须回表查询出这个字段

explain select id,name,age from student

using index condition ：索引下推：

必须是查范围值，如果是常量比较，不会触发的，比如这样a>10会触发，如果是a=10，则不触发

type 字段的返回值，性能从最优到最差：

system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_merge -> unique_subquery -> index_subquery -> range -> index -> all

index 和 all 表示本次查询走的是全表扫描。如果 type 值是这两个，表明 SQL 是需要优化的。

有个key_len的列，计算方法：int占4字节；不为空占1字节；varchar根据括号里的来占字节，如果是utf-8则乘以3，变长数据类型2字节

一些常识

一、MySQL的层次

客户端-server-存储三层

server里面有连接器（进行连接的操作）、语法器（判断语法是否有误，生成AST抽象语法树）、优化器（进行SQL语句的优化）、执行器。

语句的操作，其实质是IO，只要能降低IO，就能在一方面起到调优的功效。

二、delete加limit

假设删除10000条数据，最佳方法是循环5次，一次删500条，

delete from table where age>30 order by age limit 500

当delete后只有order by而没有limit时，order会被优化器优化掉，因为无意义。
再说一下为什么建议加order by，
1.不加排序会随机删除（或者说无法删除你预想的那行数据），如我本想删除第一行id=1的这行数据，但我执行delete xx limit 1;删除成功后发现，第一行还在，说明删除了其他行；
2.为什么delete语句加上 ‘order by 索引列’ 会更快,是因为order by id会触发索引查询，而普通删除则需全表扫描一下。

三、IP地址用int存

select inet_aton(‘192.168.1.1’)

select inet_ntoa(3232235777)

字符集选择：utf-8mb4

四、语句执行顺序

简单的没有子查询嵌套的话，顺序是这样：

---->from 获取数据表，需要从哪个表取数据

—>where 筛选出合格的数据，得到一个数据集

—>group by 将上面的数据集按条件进行分组

---->having 将分好组的数据按条件进行过滤

---->select 针对每一组的数据进行select，返回结果集

---->order by 进行排序

PS：from后面的表示自右向左进行解析的，where后面的是自下而上进行解析的。所以，在写SQL的时候，尽量把数据量小的表放在最右边进行关联，把能筛选出小量数据的条件放在where语句的最左边，来达到用小表匹配大表