mysql+uuid+分页_MySQL基础

索引

锁

优化

SQL总结

索引 & 各种树

AVL树 & 红黑树

都是平衡二叉树，O(log n)时间内查找，插入和删除。

AVL绝对平衡，翻转保持平衡的频率比较高。

红黑树局部平衡，最长路径不超过最短路径的2倍，翻转的频率比较低。

重新染色也会消耗性能，总体上AVL树和红黑树性能相差不大。查找多修改少用AVL树

添加删除操作多使用红黑树

B+树 & B树

都是平衡多叉树，比二叉树矮得多，适用于文件和数据库索引。B+树比B树扁平，树的深度过大会导致磁盘I/O读写频繁

B+树所有的值一定在叶子结点

B+树全表扫描只需要遍历叶子结点组成的链表

Trie树

空间换时间：利用字符串的公共前缀来降低查询时间的开销以达到提高效率的目的，最大限度地减少无谓的字符串比较。

常用于解决字符串检索、字符串排序、最长公共前缀等问题。

使用UUID的缺点

插入值随机，需要重新调整结点。频繁的移动、分页操作产生大量的碎片。

hash索引只能用等号，无法用比较符号

无法用部分索引

索引原则最左前缀原则

索引列的值尽可能变化多：例如日期适合作索引，而性别不适合(各占一半，索引提高不了多少效率)

MySQL锁

1. 表级锁MyISAM引擎使用

锁粒度最大，冲突概率最高，并发性能最差

开销最小，加锁最快

不会出现死锁(一次性获取所有锁or等待)

2. 页级锁(历史遗留)BDB引擎使用

指标折中于表锁和行锁

3. 行级锁开销最大，但并发性能最好

会出现死锁

MyISAM vs InnoDB

InnoDB底层是B+树，推荐使用自增主键。

InnoDB会选择主键作为索引，按顺序存储记录。当一页写满，就会自动开辟一个新的页。

区别InnoDB支持事务，MyISAM不支持。

InnoDB支持行级锁，MyISAM只支持表级锁。

InnoDB支持外键，MyISAM不支持。

InnoDB的主键范围比MyISAM更大。

InnoDB写的处理效率较差，并占用更多磁盘空间以保留数据和索引。

场景MyISAM适合以SELECT、INSERT为主或对事务完整性没有要求的场景

InnoDB适合需要大量按索引修改，同时并发查询的场景

优化策略

索引优化

1.不要对索引列运算，把算式、函数等移到比较符右边。正例：select * from record where amount < 1000*10

2.不要用逻辑非，改为正向的表达。反例：update t_test set status=0 where status<>0;

正例：update t_test set status=0 where status=1 or status=2;

分表垂直拆分：表字段过多，重新组合字段，拆分为多张表。

水平拆分：数据太多，需要拆分。例如根据时间拆分为多张表。

分区

物理上划分，而逻辑上不影响，提升磁盘IO性能。

分库

分表多了数据库容易膨胀，需要分库。

SQL总结

列转行 & 行转列

列显示：

行显示：

列转行：SELECT

ID,

MAX(CASE ROW WHEN 1 THEN VALUE ELSE 0 END) AS ROW_1,

MAX(CASE ROW WHEN 2 THEN VALUE ELSE 0 END) AS ROW_2,

MAX(CASE ROW WHEN 3 THEN VALUE ELSE 0 END) AS ROW_3

FROM

TB_TEST

GROUP BY

ID

行转列：SELECT

ID,

ROW_1 AS ROW

FROM

tb_test2

UNION

SELECT

ID,

ROW_1 AS ROW

FROM

tb_test2

UNION

SELECT

ID,

ROW_3 AS ROW

FROM

tb_test2

SQL执行顺序

1)from(inner join、on)：连表查询，先将所有记录的笛卡尔积存入虚表，再通过on筛选出需要的记录行。

2)where：对from筛选

3)group by：按照指定列分组

4)having：对group by筛选

5)select(distinct)

6)order by：对结果集排序