mySql的基础，优化，原理通俗解释！

优化的基本方向：

对于MySQL层优化我一般遵从五个原则：

减少数据访问： 设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
返回更少的数据： 只返回需要的字段和数据分页处理 减少磁盘io及网络io
减少交互次数： 批量DML操作，函数存储等减少数据连接次数
减少服务器CPU开销： 尽量减少数据库排序操作以及全表查询，减少cpu 内存占用
利用更多资源： 使用表分区，可以增加并行操作，更大限度利用cpu资源
总结到SQL优化中，就三点:

最大化利用索引；
尽可能避免全表扫描；
减少无效数据的查询；
理解SQL优化原理 ，首先要搞清楚SQL执行顺序：
 

怎么验证sql的性能得到了优化：

统计mysql的语句的执行时间就可以：

1、统计MySQL中每条sql的执行时间2

 set profiling=1; 

show profiles;

show profile;

show profile for query 1;

2、timestampdiff来查看测试时间#注意三条语句今年一起执行！

set @d=now(); 

 select * from comment; 

 select timestampdiff(second,@d,now()); 

大数据量多种优化案例代码验证：

生成的大数据量的方法可以参考这篇文章：

mysql插入100000+条记录的方法

查看索引： 

primary 和index哈

 

查看数据量：

 

 

一、避免不走索引的场景

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE '%陈%'
优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE '陈%'
如果需求是要在前面使用模糊查询，

使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置，可参阅《MySQL模糊查询用法大全（正则、通配符、内置函数等）》
使用FullText全文索引，用match against 检索
数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like '%xx%'。
 

2. 尽量避免使用in 和not in，会导致引擎走全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)
优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3
如果是子查询，可以用exists代替。详情见《MySql中如何用exists代替in》如下：

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

 代码验证：

-- IN
SELECT * FROM test WHERE id IN (2222,2229);
-- 优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM test WHERE id BETWEEN 2222 AND 2229;
show PROFILES;
-- ________or

结果展示：

 

 

3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3
优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

代码验证：

SELECT * FROM test WHERE id = 10000 OR id = 30000;
-- union
SELECT * FROM test WHERE id = 10000 UNION SELECT * FROM test WHERE id = 30000;
show PROFILES;

结果展示：

这个有点和结果不一样，具体原因后面探究？？？

 

 

4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

 

SELECT * FROM t WHERE score IS NULL
优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

可以将表达式、函数操作移动到等号右侧。如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9

代码验证：

-- 全表扫描
SELECT * FROM test WHERE score/10 = 9;
-- 走索引
SELECT * FROM test WHERE score = 10*9;

show profiles;

结果展示：

 

 

6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT username, age, sex FROM T WHERE 1=1
优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。

代码验证：

SELECT * FROM test  limit 10000;
SELECT * FROM test where 1=1 limit 10000;
show PROFILES;

结果展示：

注意这个是随机的时间，我测试了多次才有这个结果，具体原因还不清楚 

 

 

 

7. 查询条件不能用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

8. where条件仅包含复合索引非前置列

如下：复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引。详情参考《联合索引的使用原理》。

select col1 from table where key_part2=1 and key_part3=2

9. 隐式类型转换造成不使用索引 

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。 

select col1 from table where col_varchar=123; 

10. order by 条件要与where中条件一致，否则order by不会利用索引进行排序

-- 不走age索引
SELECT * FROM t order by age;
 
-- 走age索引
SELECT * FROM t where age > 0 order by age;

代码验证：

SELECT * FROM test order by id limit 10000;
SELECT * FROM test where id > 0 order by id limit 10000;
show PROFILES;

结果展示：

mysq底层原理和解释：

由于涉及到底层的c++源码，详细情况，需要长时间的研究才可以得出结论！所以这方面还是需要进行对应的时间

 

真正关心的是：查询慢的sql

使用事务

BEGIN;
 
explain select * from user;
COMMIT;
-- 尽量使用事务注意了

 

 

 

参考文章：

mysql必知必会

SQL优化最干货总结 - MySQL（2020最新版）

MySQL执行计划（MySQL调优的重要利器）

统计MySQL中每条sql的执行时间

MySQL数据库优化的八种方式(经典必看)