1.索引失效的几种情况
1.1 全值匹配我最爱
1.2 最佳左前缀法则
1.3 主键插入顺序,主键不是递增,可能造成页分裂、性能损耗
1.4 计算、函数、类型转换(自动或手动)导致索引失效
1.5 类型转换导致索引失效
1.6 范围条件右边的列索引失效
CREATE INDEX idx_age_classId_name on student(age,classId,NAME);
EXPLAIN SELECT SOL NO CACHE * FRoM studentWHERE student,age=30 AND student.classId>20 AND student.name = ‘abc’ ;
因为classid 使用了范围条件,导致name索引没用上。
1.7 不等于(!= 或者<>)索引失效
1.8 is null可以使用索引,is not null无法使用索引
1.9 like以通配符%开头索引失效
1.10 OR前后存在非索引的列,索引失效
1.11 数据库和表的字符集统一使用utf8mb4
不同的字符集进行比较前需要转换,会造成索引失效。
2.关联查询优化
2.1、整体效率比较:INLJ>BNLJ>SNLJ
2.2 、永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)(小的度量单位指的是 表行数"每行大小
2.3 、为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)
2.4 、增大join buffer size的大小(一次缓存的数据越多,那么内层包的扫表次数就越少)
2.5 、减少驱动表不必要的字段査询(字段越少,join buffer 所缓存的数据就越多)
两个结论:
对于内连接来说,查询优化器可以决定谁作为驱动表,谁作为被驱动表出现的
对于内连接来讲,如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会被作为被驱动表
3.JOIN语句原理
3.1、Simple Nested -Loop Join(简单嵌套循环连接)
类似于java程序中双重嵌套循环一样
3.2、Index Nested-Loop Join(索引嵌套循环连接)
连接条件使用到了索引
3.3、Block Nested-Loop Join(块嵌套循环连接)
不再是逐条获取驱动表的数据,而是一块一块的获取,引入了join buffer缓冲区,将驱动表join相关的部分数据列(大小受ioin bufer的限制)缓存到ioin bufer中,然后全表扫描被驱动表,被驱动表的每一条记录一次性和joinbuffer中的所有驱动表记录进行匹配(内存中操作),将简单嵌套循环中的多次比较合并成一次,降低了被驱动表的访问频率。单条数据匹配变成批量匹配。
3.4、Hash Join
从MySQL的8.0.20版本开始将废弃BNLJ,因为从MySQL8.0.18版本开始就加入了hash join默认都会使用hash join。
Nested Loop:对于被连接的数据子集较小的情况,Nested Loop是个较好的选择。
Hash Join是做大数据集连接时的常用方式,优化器使用两个表中较小(相对较小)的表利用Join Key在内存中建立 散列表,然后扫描较大的表并探测散列表,找出与Hash表匹配的行。
4、子查询优化
MySQL从4.1版本开始支持子查询,使用子查询可以进行SELECT语句的嵌套查询,即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。
子查询是 MySQL 的一项重要的功能,可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是,子查询的执行效率不高。原因:
① 执行子查询时,MySQL需要为内层查询语句的查询结果 建立一个临时表 ,然后外层查询语句从临时表中查询记录。查询完毕后,再 撤销这些临时表 。这样会消耗过多的CPU和IO资源,产生大量的慢查询。
② 子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都 不会存在索引 ,所以查询性能会受到一定的影响。
③ 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。
在MySQL中,可以使用连接(JOIN)查询来替代子查询。连接查询 不需要建立临时表 ,其 速度比子查询要快 ,如果查询中使用索引的话,性能就会更好。
5、排序优化
5.1、filesort算法:双路排序和单路排序
双路排序(慢)
①MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,读取行指针和order by列对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出
②从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。
单路排序(快)
①从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每行都保存在内存中了。
6、GROUP BY优化
①group by使用索引的原则几乎跟order by一致,group by即使没有过滤条件用到索引,也可以直接使用索引。
②group by先排序再分组,遵照索引建的最佳左前缀法则
③当无法使用索引列,增大max_length_for_sort_data和sort_buffer_size 参数的设置
④where效率高于having,能写在where限定的条件就不要写在having中了
减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做。
⑤order by、group by、distinct这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的。
⑥包含了order by、group by、distinct这些査询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢。
7、优化分页查询
一般分页查询时,通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10此时需要MVSQL排序前2000010 记录,仅仅返回2000000-2000010的记录,其他记录丢弃,查询排序的代价非常大。
优化思路一:
在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。
EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2888888,10) aWHERE t.il = a.id;
优化思路二:
该方案适用于主键自增的表,可以把Limit 查询转换成某个位置的查询
EXPLAIN SELECT * FROM student WHERE id >2000000 LIMIT 10:
8、优先考虑覆盖索引
8.1、什么是覆盖索引?
理解方式一:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
理解方式二:非聚簇复合索引的一种形式,它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列(即建索引的字段正好是覆盖查询条件中所涉及的字段)。
简单说就是,`索引列+主键`包含 SELECT 到 FROM之间查询的列 。
8.2 覆盖索引的利弊
1.避免Innodb表进行索引的二次查询(回表)
2.可以把随机I0变成顺序I0加快查询效率
9、如何给字符串添加索引
alter table teacher add index index2(email(6));
使用前缀索引,定义好长度,就可以做到既节省空间,又不用额外增加太多的查询成本
10、索引下推
首先将index key条件满足的索引记录区间确定,然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃,不回表、也不会返回server层。
EXPLAIN SELECT *FRoM peopleWHERE zipcode='000001' AND lastname LIKE %张%
10.1、ICP的使用条件
1、如果表访问的类型为 range、ref、eq_ref和ref_or_null 可以使用ICP
2、ICP可以用于 InnoDB 和MyISAM表,包括分区表 InnoDB和 MyISAM 表
3、对于 InnoDB 表,ICP 仅用于二级索引。ICP 的目标是减少全行读取次数,从而减少 I0 操作。
4、当SQL使用覆盖索引时,不支持ICP。因为这种情况下使用ICP 不会减少 I/0。
5、相关子查询的条件不能使用ICP
11、其它查询优化策略
11.1、不太理解哪种情况下应该使用 EXISTS,哪种情况应该用 IN。选择的标准是看能否使用表的索引吗?
索引是个前提,其实选择与否还是要看表的大小。你可以将选择的标准理解为 小表驱动大表。在这种方式下效率是最高的。
SELECT*FROM A WHERE CC IN(SELECT CC FROM B)
SELECT *FROM A WHERE EXISTS(SELECT CC FROM B WHERE B.CC=A.CC)
当A小于B时,用 EXISTS。因为 EXISTS的实现,相当于外表循环,实现的逻辑类似于,减少外层循环数。
for i in A
for j in B
if j.cc == i.cc then ..
当B小于A时用 IN,因为实现的逻辑类似于:
for i in B
for j in A
if j.cc == i.cc then
哪个表小就用哪个表来驱动,A表小就用 EXISTS,B 表小就用 IN。
11.2、COUNT(*)与COUNT(具体字段)效率
在 MySQL中统计数据表的行数,可以使用三种方式:SELECT COUNT()、SELECT COUNT(1)和SELECTCOUNT(具体字段),使用这三者之间的查询效率是怎样的?
情况1、 COUNT()和 COUNT(1)都是对所有结果进行 COUNT,COUNT()和COUNT(1)本质上并没有区别(二者执行时间可能略有差别,不过你还是可以把它俩的执行效率看成是相等的)。如果有 WHERE 子句,则是对所有符合筛选条件的数据行进行统计;如果没有WHERE 子句,则是对数据表的数据行数进行统计。
情况2:如果是 MyISAM 存储引擎,统计数据表的行数只需要0(1)的复杂度,这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count 值,而一致性则由表级锁来保证。
如果是 InnoDB 存储引擎,因为InnoD8 支持事务,采用行级锁和 MVCC 机制,所以无法像 MyISAM 一样,维护-个row_count变量,因此需要采用扫描全表,是0(n)的复杂度,进行循环+计数的方式来完成统计,
情况3:在 InnoDB 引擎中,如果采用 COUNT(具体字段)来统计数据行数,要尽量采用二级索引。因为主键采用的索引是聚簇索引,聚簇索引包含的信息多,明显会大于二级索引(非聚簇索引)。对于 COUNT()和 COUNT(1)来说,它们不需要查找具体的行,只是统计行数,系统会自动采用占用空间更小的二级索引来进行统计。
如果有多个二级索引,会使用 key_len 小的二级索引进行扫播。当没有二级索引的时候,才会采用主键索引来进行统计。
12.3 关于SELECT(*)
在表查询中,建议明确字段,不要使用"作为查询的字段列表,推荐使用SELECT<字段列表>查询。原因
① MySQL 在解析的过程中,会通过 査询数据字典 将"*"按序转换成所有列名,这会大大的耗费资源和时间。
② 无法使用 覆盖索引
12.4 LIMIT1对优化的影响
针对的是会扫描全表的 SQL语句,如果你可以确定结果集只有一条,那么加上 LIMIT1的时候,当找到一条结果的时候就不会继续扫描了,这样会加快查询速度。
如果数据表已经对字段建立了唯一索引,那么可以通过索引进行查询,不会全表扫描的话,就不需要加上 LIMIT1 了。
12.5多使用COMMIT
只要有可能,在程序中尽量多使用 COMMIT,这样程序的性能得到提高,需求也会因为 COMMIT 所释放的资源而减少。
COMMIT 所释放的资源:
。回滚段上用于恢复数据的信息
。被程序语句获得的锁
。redo/undo log buffer 中的空间
。管理上述3种资源中的内部花费
13、淘宝数据库,主键如何设计的?
①建议尽量不要用跟业务有关的字段做主键。毕竟,作为项目设计的技术人员,我们谁也无法预测在项目的整个生命周期中,哪个业务字段会因为项目的业务需求而有重复,或者重用之类的情况出现。
②非核心业务:对应表的主键自增ID,如告警、日志、监控等信息。核心业务: 主键设计至少应该是全局唯一且是单调递增。
③认识UUID
全局唯一,占用36字节,数据无序,插入性能差。
为什么UUID占用36个字节?
UUID根据字符串进行存储,设计时还带有无用"-"字符串,因此总共需要36个字节。
为什么UUID是无序的?
UUID=时间+UUID版本(16字节)-时钟序列(4字节)-MAC地址(12字节)
因为UUID的设计中,将时间低位放在最前面,而这部分的数据是一直在变化的,并且是无序。
d为16进制数制,每一个数字或字母代表一个四位的二进制数。所以32个16进制数字占32*4=128位,转换为二进制之后占用存储空间为:128位/8位 = 16个字节。每个字节包含8个bit。