索引数据结构
1.二叉树，极端情况有可能会出现在一边的情况，二叉树就变成了链表
2.平衡二叉搜索树（AVL 树）
- 每个节点的左子树和右子树的高度差不超过1
- 对于n个节点,数的深度是log2n ,查询的时间复杂度是O(log2n)
B-Tree & B+Tree
B-Tree 特性
- 根节点的子节点个数2 <= X <= m，m是树的阶
假设m = 3 ,则根节点可以有2-3个孩子 - 中间节点的子节点个数m/2 <=y <= m
假设m = 3 ,中间节点至少有2个孩子,最多3个孩子 - 每个中间节点包含n个关键字，n =子节点个数-1 ,且按升序排序
如果中间节点有3个子节点，则里面会有2个关键字,且按升序排序 - Pi(i=1…n+ 1)为指向子树根节点的指针。其中P[1]指向关键字小于Key[1]的子树，P[]指向关键字属于(Key[i-1], Key[i]) 的子树, P[n+ 1]指向关键字大于Key[n]的子树
P1、P2、P3为指向子树根节点的指针。P1指向关键字小于Key1的树 ;P2指向key1-key2之间的子树; P3指向大于Key2的树
B+Tree
- B+ Tree是在B-Tree基础.上的一种优化
- InnoDB存储引擎使用B+ Tree实现其索引结构
B-Tree和B+Tree的差异
- B+ Tree有n个子节点的节点中含有n个关键字，B-Tree是n个子节点的节点有n-1个关键字
- B+Tree中,所有的叶子节点中包含了全部关键字的信息，且叶子节点按照关键字的大小自小而大顺序链接，构成一个有序链表
B-Tree的叶子节点不包括全部关键字 - B+ Tree中，非叶子节点仅用于索引，不保存数据记录，记录存放在叶子节点中
B-Tree中，非叶子节点既保存索引，也保存数据记录
举例：
select * from table where column = 1;
B-Tree 中，查询的时候有可能在某一层非叶子节点就找到了；而 B+Tree 中如果存在的话一定是在叶子节点中找到的
select * from table where column between 1 and 10;
B-Tree 中是一个一个查出来然后再组装到一起，最后返回；而 B+Tree，只要查到1，然后通过 1 后面的有序链表，一直遍历到 10 就行了。
InnoDB存储方式
- B+Tree
- 主键索引：叶子节点存储主键及数据
- 非主键索引(二级索引、辅助索引) ：叶子节点存储索引及主键
MyISAM存储方式
- B+ Tree
- 主键/非主键索弓|的叶子节点都是存储指向数据块的指针
InnoDB Vs MyISAM
InnoDB :聚簇索引
MyISAM :非聚簇索引
Hash
Hash 有可能会产生 hash 冲突，查询的时间复杂度 O(1)
create table test_ hash_ table (
name varchar( 45)
not null
age tinyint(4) not null,
key using hash(name)
) engine=memory;
Hash索引支持情况
- Memory引擎支持显式Hash索引
- InnoDB引擎支持“自适应Hash索引”
用show variables like ‘innodb_adaptive_hash_index’ 查看开关情况
set global innodb_adaptive_hash_index = ‘ON’
空间索引( R-Tree索引)
存储GIS数据,基于R-Tree
MySQL 5.7开始InnoDB支持空间索引
R-Tree 介绍 :
https://blog.csdn.net/sjyttkl/article/details/70226192
空间索引使用：
https://www.cnblogs.com/oloroso/p/9579720.html
全文索引
适应全文搜索的需求
MySQL 5.7之前,全文索引不支持中文,经常搭配Sphinx
MySQL 5.7起,内置ngram ,支持中文
ngram : https://dev.mysql.com/doc/refman/8.0/en/fulltext-search-ngram.html
全文索引使用:
https://blog.csdn.net/mrzhouxiaofei/article/details/79940958?spm=1001.2014.3001.5501
B-Tree(B+ Tree) &Hash索引特性与限制
B-Tree ( B+Tree ) 特性
- 完全匹配 : index(name) = > where name = 'name ’
- 范围匹配 : index(age) => where age > 5
- 前缀匹配 : index(name) => where name like ‘name%’
B-Tree ( B+Tree ) 限制
- index(name, age, sex)
查询条件不包括最左列，无法使用索引
●where age=5 and sex=1 无法使用索引
●跳过了索引中的列，则无法完全使用索引
●where name = ‘name’ and sex = 32 =>只能用name这一 -列
查询中有某个列的范围(模糊)查询，则其右边所有列都无法使用索引
where name = ‘name’ and age > 32 and sex = 1 =>只能用name、age两列
Hash索引
一般性能比B-Tree ( B+Tree )要好一些
Hash索引限制
- Hash索引并不是按照索引值排序，所以没法使用排序
- 不支持部分索引列匹配查找
hash(a, b) => where a = 1 - 只支持等值查询（例如 =、IN），不支持范围查询、模糊查询
- Hash 冲突越严重，性能下降越厉害
创建索引的原则
建议创建索引
- 如果经常根据一个字段select/update/delete ，则这个字段可以建立索引，如果经常多个字段组合查询，则可以按顺序创建组合索引（最左前缀原则），频繁作为 where 条件的字段
- 需要分组、排序的字段
- distinct 所使用的字段
- 字段的值有唯一性约束
- 对于多表的查询，联结字段应创建索引，且类型务保持一致（避免隐式转换）
不建议创建索引
- where 子句里用不到的字段
- 表的记录非常少
- 有大量重复数据，选择性低
索引的选择性越高，查询效率越好，因为可以在查找过程中过滤更多的行 - 频繁更新的字段，如果创建索引需要考虑索引维护的开销
索引失效与解决方案
可能导致索引失效的场景:
- 索引列不独立。独立是指:列不能是表达式的一部分，也不能是函数的参数
select * from employees where emp_no+1 = 10004;
解决方案：事先计算好表达式的值，再放到入参，避免sql where条件 =左侧的计算
select * from employees where emp_no = 10003;
select * from employees where SUBSTRING(first_name,1,3)= ‘Geo’;
解决方案：预先计算好结果，再传过去，在 where 条件的左侧，不要使用函数；或者使用等价的 sql
select * from employees where first_name like ‘Geo%’;
- 使用了左模糊
select * from employees where first_name like ‘%Geo%’;
解决方案：尽量避免左模糊，如果避免不了可以考虑使用搜索引擎
- 使用OR查询的部分字段没有索引
select * from employees where first_name= ‘Geo’ or last_name = ‘Geo’;
解决方案：两边都加上索引
- 字符串条件未使用’ '引起来
select * from dept_emp where dept_no = 2;
解决方案：select * from dept_emp where dept_no =’ 2’;
- 不符合最左前缀原则的查询
select * from employees where last_name= ‘Geo’
索引为 index(last_name,first_name)，不符合最左前缀原则
解决方案：顺序颠倒一下 index(first_name,last_name)
- 索引字段建议添加NOT NULL约束
单列索引无法储null值，复合索引无法储全为null的值
查询时，采用is null条件时，不能利用到索引，只能全表扫描
MySQL官方建议尽量把字段定义为NOT NULL，可以设置默认值
select * from employees where last_name is null;
- 隐式转换导致索引失效
同 4
索引调优技巧
长字段索引的调优
字段的长度应该比较的小，SHA1/MD5是不合适的
应当尽量避免hash冲突，就目前来说，流行使用CRC32 ()或者FNV64()
select * from employees where first_name_hash = CRC32(‘xu’) and first_name = ‘xu’;
前缀索引
alter table employees and key(first_name(5))
索引选择性=不重复的索引值 / 数据表的总记录数
数值越大,表示选择性越高,性能越好
完整列的选择性: 0. 0042[这个字段的最大选择性了]
select count(distinct first_name) / count(*) from employees;
5: 0.0038 6: 0.0041 7: 0.0042
select count(distinct left(first_ name ,6))/count(*) from employees;
结论：
alter table employees and key(first_name(7))
局限性：无法做 order by、group by；无法使用覆盖索引
“后缀索引”：额外创建一个字段，比如说 first_name_reverse,在存储的时候，把 first_name的值反转过来再存储。比方说：
xu -> ux 存储到 first_name_reverse
- 引入Hash字段，作为索引
伪"Hash索弓|" - 使用前缀索引
单列索引 vs 组合索引
- SQL存在多个条件，多个单列索引,会使用索引合并
- 如果出现索引合并,往往说明索引不够合理
- 如果SQL暂时没有性能问题,暂时可以不管
覆盖索引
对于索引 X , SELECT的字段只需从索引就能获得,而无需到表数据里获取,这样的索引就叫覆盖索引
- 尽量只返回想要的字段
●使用覆盖索引
●减少网络传输的开销
重复索引、冗余索引、未使用的索引
- 索引是有开销的
重复索引
- 在相同的列上按照相同的顺序创建的索引
- 尽量避免重复索引,如果发现重复索引应该删除
冗余索引
- 如果已经存在索引index(A, B) ,又创建了index(A) , 那么 index(A) 就是 index(A, B) 的冗余索引
- 一定要避免，但有特例！一定要避免掉进陷进里
index(from_date)：type=ref extra=null，使用了索引
index(from_date)：某种意义上来说就相当于 index(from_date, emp_no)
index(from_date, to_date)：type=ref extra=using filesort ，order by 子句无法使用
index(from_date, to_date)：某种意义上来说就相当于index(from_date, to_date, emp_no)
未使用的索引
- 某个索引未曾使用
- 累赘，删除
如何发现 重复索引、冗余索引、未使用的索引
- 人工分析
- 工具
JOIN 优化
- join的种类有哪些?彼此的区别?
- join有哪些算法?
- join语句如何优化?
JOIN算法1-Nested-Loop Join ( NLJ )
JOIN算法2-Block Nested-Loop Join( BNLJ )
扫描次数计算公式
- (S * C)/join_ buffer_ size + 1
●S :缓存的t1/t2表的一-行数据
●C:缓存的行数
●join_ buffer_ size : join buffer的大小
使用join buffer的条件
- 连接类型是ALL、index或range
- 第一个 nonconst table 不会分配 join buffer ，即使类型是 ALL 或者 index
- join buffer只会缓存需要的字段，而非整行数据
- 可通过 join_ buffer_size变量设置join buffer大小
- 每个能被缓存的 join 都会分配一个join buffer ，一个查询可能拥有多个join buffer
- join buffer在执行联接之前会分配，在查询完成后释放。
JOIN 算法 3-Batched Key Access Join ( BKA )
Mysql5.6 引入
BKA 的基石：Multi Range Read （MRR）
MRR核心:将随机IO转换成顺序IO ,从而提升性能
MRR参数
optimizer_ switch的子参数
mrr :是否开启mrr , on开启, off关闭
mrr_ cost_ _based :表示是否要开启基于成本计算的MRR
read_rnd_buffer_size：指定 mrr 缓存大小
BKA参数
optimizer_ switch的子参数
· batched_key_ access : on 开启 , off 关闭
JOIN 算法 4 - HASH JOIN
MySQL 8.0.18引入,用来替代BNLJ
join buffer缓存外部循环的hash表，内层循环遍历时到hash表匹配
HASH JOIN注意点
-
MySQL 8.0.18才引入,且有很多限制,比如不能作用于外连接,比如left join/right join等等。从8.0.20开始限制少了很多,建议用8.0.20或更高版本
-
从MySQL 8.0.18开始，hash join的join buffer是递增分配的,这意味着，你可以为将join_buffer_size设置得比较大。而在MySQL 8.0.18中,如果你使用了外连接,外连接没法用hash join，此时join_ buffer. size会按照你设 置的值直接分配内存。因此join buffer_ _size还是得谨慎设置。8.0.20开始，BNLJ已被删除了，用hash join替代了BNLJ
小结
- 各种join的区别
- 熟练掌握NLJ、BNLJ的原理
- 了解BKA、HASH JOIN的原理
驱动表vs被驱动表
外层循环的表是驱动表，内层循环的表是被驱动表.
JOIN调优原则
-
用小表驱动大表
●一般无需人工考虑，关联查询优化器会自动选择最优的执行书序
●如果优化器抽风，可使用STRAIGHT_JOIN
●如果有where条件，应当要能够使用索引，并尽可能地减少外循环的数据量 -
join的字段尽量创建索引
一个坑，join 的时候，条件对应字段的类型不同，会导致索引无法使用，全表扫描
当join字段的类型不同时，索引无法使用
join 字段的类型要保持一致 -
尽量减少扫描的行数（explain-row）
尽量控制在百万以内（经验之谈，仅供参考） -
参与join的表不要太多
阿里编程规约建议不超过3张 -
如果被驱动表的join字段用不了索引，且内存较为充足，可以考虑把join buffer设置得大一些
分页查询优化
- limit ,
offset：返回第一行的偏移量（想要跳过多少行）
size：指定返回多少条
查询第一页的时候，花费92ms
查询第30001页的时候，花费174ms
expLain seLect * from empLoyees limit 300000,10;
- 方案1:覆盖索引(108ms)
seLect emp_no from empLoyees limit 300000, 10;
- 方案2:覆盖索引+join(109ms)
seLect * from empLoyees e inner join
(seLect emp_no from empLoyees limit 300000,10) t using(emp_no) ;
- 方案 3：覆盖索引+子查询
select * from empLoyees where emp_no ≥ (seLect emp_no from empLoyees limit 300000,10) limit 10;
- 方案 4：范围查询+limit 语句
select * from employees limit 10;
select * from employees here emp_no ≥ 10010 limit 10;
- 方案 5：如果能获得起始主键值 & 结束主键值
select * from employees where emp_no between 20000 and 20010;
- 方案 6：禁止传入过大的页码
count 优化
- 当没有非主键索引的时候会使用主键索引
- 如果存在非主键索引的时候，会使用主键索引
- 如果存在多个非主键索引，会使用最小的非主键索引
为什么？
innodb 非主键索引：叶子节点存储的是：索引+主键
主键索引叶子节点：主键+表数据
在一个 page 里，非主键索引可以存储更多的条数，对于一张表，一百万条数据，使用非主键索引，有可能扫描page 100，主键索引有可能扫描 page 500
count (字段) 只会针对该字段统计，使用这个字段上面的索引（如果有的话）
count (字段) 会排除掉该字段值为 null 的行
count(*) 不会排除 null
count(1) 和 count( *)的区别
- innodb 中 count(1) 和 count(*) 没有区别
- 对与 MyISAM 引擎，如 count(*) 没有 where 条件 ，查询会非常快
- 对于 MySQL 8.0.13，Innodb 引擎如 count(*) 没有 where 条件 ，查询也会被优化，性能会有所提升
参考官方文档：
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
- COUNT(*)会选择最小的非主键索引，如果不存在任何非主键索引，则会使用主键
- COUNT()不会排除为nul的行,而COUNT(字段)会排除
对于不带查询条件的COUNT()语句, MyISAM及InnoDB(MySQL >= 8.0.13) ,都做了优化 - 如果没有特殊需求，尽量使用 count(*)
select count(* ) from salaries;
120ms
innodb
版本 8.0.18 > 8.0.13, 可以针对无条件的 count 语句去优化
show create table salaries;
select version() ;
mysql 5.6， 相同数据量，相同SQL需要花费841ms
expLain select count(*) from salaries;
优化 select count(*) from salaries;
-
方案1:创建一个更小的非主键索引
-
方案2:把数据库引擎换成MyISAM -> 实际项目用的很少，一般不会修改数据库引擎
-
方案3:汇总表table[table_name, count] = employees， 2000000
好处:结果比较准确table[emp_no, count]
缺点:增加了维护的成本 -
方案4: sql_calc_ found_ rows
seLect * from saLaries Limit 0, 10;
seLect count(*) from salaries;
在做完本条查询之后，自动地去执行COUNT
seLect sql_calc_found_ rows * from salaries limit 0, 10;
select found_ rows() as salary_ count;
缺点: mysql 8.0.17已经废弃这种用法，未来会被删除
注意点:需要在MYSQL终端执行，IDEA无法正常返回结果。
-
方案5:缓存
seLect * from saLaries ;
存放到缓存
优点：性能比较高；结果比较准确，有误差但比较小（除非在缓存更新期间，新增或删除了大量数据）
缺点：引入了额外的组件，增加了架构的复杂度 -
方案 6：select * from information_ schema . TABLES where
TABLE SCHEMA = ‘employees’ and TABLE _ NAME 'salaries ';
好处:不操作 salaries 表，不论salaries有 多少数据，都可以迅速地返回结果
缺点:估算值，并不是准确值 -
方案 7：show table status where Name =’ salaries’ ;
好处:不操作 salaries 表，不论salaries有 多少数据，都可以迅速地返回结果
缺点:估算值，并不是准确值 -
方案 8 ：expLain select * from salaries;
好处:不操作 salaries 表，不论salaries有 多少数据，都可以迅速地返回结果
缺点:估算值，并不是准确值
order by 优化
最好的做法：利用索引避免排序
实验:哪些情况下ORDER BY子句能用索引,哪些情况不能;
MySQL排序原理
ORDER BY调优原则与技巧
RDER BY调优实战
利用索引本身的有序性,让MySQL跳过排序过程
为什么一会儿是ALL一会儿是INDEX：MySQL优化器发现全表扫描开销更低时，会直接用全表扫描，利用使用索引避免排序
explain select * from employees order by first_ name , last_ name limit 10;
可以使用索引避免排序
[Bader , last_name1 , emp_no]
[Bader , last_name2 , emp_no]
[Bader , last_name3 , emp_no]
[Bader , last_name4 , emp_no]
…
explain select * from employees where first_name = ‘xx’ order by last_ name ;
排序模式- rowid排序(常规排序)
1、从表中获取满足WHERE条件的记录
2、对于每条记录,将记录的主键及排序键(id,order_colum) 取出放入sort buffer (由sort_ buffer_size控制)
3、如果sort buffer能存放所有满足条件的(id,order_column) ,则进行排序;否则sort buffer满后,排序并写到临时文件
●排序算法:快速排序算法
4、若排序中产生了临时文件,需要利用归并排序算法,从而保证记录有序
5、循环执行上述过程,直到所有满足条件的记录全部参与排序
6、扫描排好序的(id,order_column)对,并利用id去取SELECT需要返回的其他字段
rowid 排序特点：
- 看sort buffer是否能存放结果集里面的所有(id,order_column) ,如果不满足,就会产生临时文件
- 一次排序需要两次IO
第二步:把(id, order_ column )扔到sort_buffer. ;第六步 :通过id去获取需要返回的其他字段。 由于返回结果是按照order_column排序的,所以id是乱序的 ，会存在随机i的问题。MySQL内部针对这种情况做了个优化，在用ID取数据之前,会按照id排排序并放到一个缓存里面,这个缓存大小由read_ rnd_buffer_ size控制，接着再去取记录,从而把随机IO转换成顺序IO
排序模式 2：全字段排序（优化排序）
直接取出SQL中需要的所有字段,放到sort buffer由于sort buffer已经包含了查询需要的所有字段,因此在sortbuffer中排序完成后可直接返回
全字段排序 vs rowid排序
- 好处:性能的提升,无需两次IO
- 缺点:一行数据占用的空间一般比rowid排序多;如果sort buffer比较小,容易导致临时文件
算法如何选择?
max_length_for_ sort_ data :当ORDER BY SQL中出现字段的总长度小于该值,使用全字段排序,否则使用rowid排序
排序模式3-打包字段排序
- MySQL 5.7引入
- 全字段模式的优化,工作原理一样,但是将字段紧密地排列在一起，而不是使用固定长度空间
varchar(255) ‘yes’ : 不打包：255 字节；打包：2=3 字节
参数汇总
| 变量 | 作用 |
|---|---|
| sort_ buffer_size | 指定sort buffer的大小 |
| max_ length_ for_ sort_data | 当ORDER BY SQL中出现字段的总长度小于该值，使用全字段排序，否则使用rowid排 |
| read_ rnd_buffer_size | 按照主键排序后存放的缓存区 |
filesort_summary解读
- memory_available :可用内存,其实就是sort_ buffer_size配置的值
- num_ rows_found :有多少条数据参与排序,越小越好
- num_ initial chunks spilled_to_disk :产生了几个临时文件，0表示完全基于内存排序
- sort_ mode
· <sort_ key, rowid> : 使用了 rowid 模式
· <sort_ key, additional_ fields> : 使用了全字段模式
· <sort_ key, packed_ additional_ fields> : 使用了打包字段模式
如何调优ORDER BY
利用索引,防止filesort的发生
如果发生了filesort ,并且没办法避免,想办法优化filesort
如何调优 filesort
- 调大 sort_ buffer_ size , 减少/避免临时文件、归并操作
optimizer trace 中 num_ initial_chunks_ spilled_ to_ disk的值
sort_ merge_ passes 变量的值 - 调大 read_rnd_buffer_size ，让一次顺序 IO 返回的结果更多
- 设置合理的max_ length_ for_ sort_ data的值
一般不建议随意调整 - 调小max_ sort_ length(排序时最多取多少字节)
小节
- 什么场景下能用索引|避免排序,什么场景下不能
- MySQL排序的三种模式
特点必须掌握，了解大概的步骤 - 会用optimizer trace去分析filesort的结果
- 知道如何调优filesort
GROUP BY优化
- 松散索引扫描 ( Loose Index Scan )
- 紧凑索引招描( Tight Index Scan )
- 临时表( Temporary table )
- 性能一次递减
松散索引扫描
- 无需扫描满足条件的所有索引键即可返回结果
例子：查询员工最小的工资
1.先扫描emp_ no = 10001的数据，并计算出最小的salary是多少，
2.扫描emp_no = 10002， 并计算出最小的salary是多少
3.遍历出每个员工的最小薪资，并返回
改进: （松散索引扫描）
1.先扫描emp_no = 10001的数据，取出第一条→就是这个员工工资最小的数据
2.直接跳过所有的emp_no = 10001的数据，继续扫描emp_no = 10002的数据， 取第一条
3.以此类推
- explain 的 extra 展示 Using index for group-by ->说明使用了松散索引扫描
explain select emp_ no, min(salary ) from salaries group by emp_ no
使用松散索引扫描的条件
-
查询作用在单张表上
-
GROUP指定的所有字段要符合最左前缀原则,且没有其他字段
比如有索引index(c1, c2, c3) ,如果GROUP BY c1,c2则可以使用松散索引扫描；但GROUP BY c2, c3、GROUP BY c1, c2, c4则不能使用 -
如果存在聚合函数,只支持MIN()/MAX() , 并且如果同时使用MIN()和MAX() ,则必须作用在同-一个字段。聚合函数作用的字段必须在索引中，并且要紧跟GROUP BY所指定的字段
比如有索引：index(c1,c2,c3) ，select c1,c2,MIN(c3),MAX(c4) from t2 group by c1,c2，可以使用松散索引扫描 -
如果查询中存在除GROUP BY指定的列以外的其他部分,则必须以常量的形式出现
SELECT c1,c3 FROM t1 GROUP BY c1,c2 :不能使用
SELECT c1,c3 FROM t1 where c3=3GROUP BY c1,c2 ; 可以使用 -
索引必须索引整个字段的值,不能是前缀索引
比如有字段c1 VARCHAR(20) ,但如果该字段使用的是前缀索引 index(c1(10)) 而不是index(c1) ,无法使用松散索引扫描
能使用松散索引扫描的SQL一览
假设有index(c1,c2,c3)作用在表t1(c1,c2,c3,c4)上，下面这些SQL都能使用松散索引
SELECT c1，c2 FROM t1 GROUP BY c1，c2 ;
SELECT DISTINCT c1，c2 FROM t1 ;
SELECT c1，MIN(c2) FROM t1 GROUP BY c1 ;
SELECT c1，c2 FROM t1 WHERE c1 < const GROUP BY c1，c2 ;
SELECT MAX(c3) ，MIN(c3)， c1， c2 FROM t1 WHERE c2 > const GROUP BY c1 , c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1，c2 ;
SELECT c1，c2 FROM t1 WHERE C3 = const GROUP BY c1，c2;
不能使用松散索引扫描的SQL一览
- 聚合函数不是 MIN() 或 MAX()
select c1,SUM(c2) FROM t1 GROUP BY c1;
- 不符合最左前缀元祖
select c1,c2 FROM t1 GROUP BY c2;
- 查询量 c3 字段，但是 c3 字段上没有等值查询
改成 select c1,c3 FROM t1 where c3=const GROUP BY c1,c2; 可以使用
select c1,c3 FROM t1 GROUP BY c1,c2;
特定聚合函数用法能用上松散索引扫描的条件
- AVG(DISTINCT)、 SUM(DISTINCT)、 COUNT(DISTINCT) ,其中AVG(DISTINCT)、SUM(DISTINCT)可接受单个参数;而COUNT(DISTINCT)可接受多个参数
- 查询中必须不存在GROUP BY或DISTINCT语句
- 满足前面所有使用松散索引扫描的条件
假设有index(c1,c2,c3)作用在表t1(c1,c2,c3,c4)上，下面这些SQL都能使用松散索引扫描
SELECT COUNT ( DISTINCT c1) ,SUM (DISTINCT c1 ) FROM t1 ;
SELECT COUNT (DISTINCT c1, c2) , COUNT (DISTINCT c2, c1) FROM t1 ;
紧凑索引扫描
- 需要扫描满足条件的所有索引键才能返回结果
- 性能一般比松散索引|扫描差,但一般都可接受
临时表
- 紧凑索引扫描也没有办法使用的话, MySQL将会读取需要的数据,并创建一个临时表,用临时表实现GROUP BY操作
一旦出现临时表，将会在explain-extra 显示Using temporary
如何优化 GROUP BY 语句？
如果GROUP BY使用了临时表,想办法用上松散索引扫描或紧凑索引扫描
DISTINCT优化
- DISTINCT是在GROUP BY操作之后,每组只取1条
- 和GROUP BY 优化思路一样
小节
- 处理GROUP BY的三种方式
松散索引扫描,性能最好, explain中会有Using index for group-by
紧凑索引扫描,性能第二, explain中无明显标识
临时表，性能最差，explain-extra 显示Using temporary - 优化措施：避免临时表，使用松散/紧凑索引扫描
表结构优化
第一范式( 1NF )
- 字段具有原子性，即数据库表的每一个字段都是不可分割的原子数据项,不能是集合、数组、记录等非原子数据项
- 当实体中的某个属性有多个值时,必须拆分为不同的属性
第二范式( 2NF )
满足1NF的基础上,要求每一-行数据具有唯一性,并且非主键字段完全依赖主键字段
第三范式( 3NF )
满足2NF的基础.上,不能存在传递依赖
表设计原则
- 字段少而精，建议20个以内(经验之谈) , 超过可以拆分
把常用的字段放到一起
把不常用的字段独立出去
大字段( TEXT/BLOB/CLOB等等)独立出去 - 尽量使用小型字段
eg.用数字代替字符串 - 避免使用允许为 null 的字段
允许为 null 字段的索引需要额外的空间 - 合理平衡范式与冗余
- 如果数据量非常大，考虑分库分表
151
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
