点击刷新
mysql精讲(从mysql底层源码解析)
1.基于计算机的局部性原理,从磁盘经过io将数据存到内存中,一次取1页数据(4kb大小);
2.如a字段为自增张主键,在插入的时候,其实就按照顺序插入,虽然会影响插入性能,但是会提升查询性能;
3.
mysql索引
覆盖索引以及回表：
如果查询内容是索引及pk(主键)的子集,此时就是覆盖索引，不需要回表查询其他字段了。
前缀索引:指的是定义字符串的一部分作为索引。
优点：节省空间
缺点：会增加扫描次数，并且不能使用覆盖索引(区分度不够)
mysql什么情况下会放弃索引：
1.如果对字段做了函数计算，就用不上索引了，但是如果是对索引字段的参数，如下面的7用函数，这样就可以用上索引了
eg: where month(create_time) = 7;
2.隐式类型转换：
eg: 如tradeid为字符串类型。当为如下查询时
select * from log where tradeid = 1;
-----由于输入的参数是整型，所以需要做类型转换，此时会做全表扫描，放弃了索引。
详解：(在mysql中，字符串和数字做比较的话，是将字符串转换成数字)
对于优化器来说，相当于：
select * from log where CAST(tradeid as signed int) = 1;
也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。
但是：select * from tradelog where id="1"; ---仍然会走索引。
详解：如果输入的类型和字段类型不一致，如字段为字符串类型，由于很多不同的字符串都可以转化为
1，如：'1'，' 1'或 '1a'。但是本身是int型，转化成字符串是唯一的，所以会用到索引。------总结：看的是：字段类型转化的是不是唯一的
3.连表查询表的字符集不一致，即使连表字段加上了索引也会不起作用。
eg: select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;
其中tradelog字符集为utf8mb4,trade_detail字符集为utf8
方法1:使用convert()函数临时将字符集转换成一致;
方法2:转换trade_detail字段的字符集为utf8mb4或者将整张表字符集为utf8mb4;
另外utf8mb4是utf8的超集，所以当这两个类型的字符串进行比较的时候，mysql内部操作是，先把utf8字符串转化成utf8mb4字符集
关键在于第三步由于字符集不同，拆分后等同于下面的写法：
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
这就再次触发了我们上面说到的原则：对索引字段做函数操作，优化器会放弃走树搜索功能
即：连接过程中在被驱动表的索引字段上加上函数操作，导致被驱动表做全表扫描的原因；
所以函数加在驱动表上不会触发全表扫描（指的是在条件中的字段加的函数，在select加的没影响）
4.连表查询字段类型不一致导致索引失效：
from work_order left join work_order_asset_details b
on work_order.id = b.work_order_id
1.伴随着Using join buffer (Block Nested Loop)一起出现
解决方式：连接条件上加上索引
2.work_order表是驱动表，其中id为整型,b表为被驱动表，触发了隐式类型转化
on cast(b.work_order_id as signed int) = work_order.id
由于函数作用在了被驱动表上，导致索引失效
tip1: 修改字段类型一致（推荐） alter table work_order_asset_details modify column work_order_id int(11) ;
tip2: 在驱动表上将其类型转化成和被驱动表上类型一致(治标不治本，临时方案)
on work_order.id = cast(b.work_order_id as char)
总结： 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
5.连表的排序规则(utf8mb4_general_ci)不一致，不仅会导致索引失效，而且还会报错
mysql 连表分析：
前提：可以使用被驱动表的索引
I.使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；
II.如果使用join语句的话，需要让小表做驱动表。
判断使用join的先决条件:
t1(id index_a b ) ---在t1上有主键索引，a字段的索引 b上没有索引
t2(id index_a b) ---在t2上有主键索引，a字段的索引 b上没有索引
explain select * from t1 straight_join t2 on t1.a = t2.b
(straight_join------内左连接）
由于被驱动表t2的b字段上没有索引，可以在explain中extra中可以看到
“Block Nested Loop”字样 ------分块嵌套循环(由于被驱动表上没有用的上的索引导致的), 会导致扫描被驱动表很多次，占用大量的系统资源，所以这种join尽量不要用
如果是“Index Nested-Loop Join” ，表示用上了被驱动表的索引字段，是没问题的
当然应该选择小表做驱动表
如果是Block Nested-Loop Join算法,无法优化sql(无法加索引)，可以使用以下方式
优化：
1.在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表
2.在join_buffer_size足够大的时候，是一样的；
小结:在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
所以inner join自动选择驱动表并不是万能的，这时候分析sql后，使用straight_join的好处就凸显了
当查询的的条件和查询的结果都是索引中的字段的时候，这个索引我们可以称之为覆盖索引，这个时候，使用like模糊查询索引是有效的
© 著作权归作者所有
举报
相关热门文章
发表评论
0/200
