mysql学习总结

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模糊查询索引是有效的