mysql高级篇

mysql的存储引擎

主要有InnoDB引擎和MyISAM引擎
除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎

索引
索引（Index）是帮助MySQL高效获取数据的数据结构

索引的分类

1. 普通索引：？
2. 唯一性索引：？
3. 主键索引：设定为主键后数据库会自动建立索引
4. 单列索引：只有一个字段的索引
5. 多列(组合、联合)索引：两个字段组合成一起的索引
6. 全文索引
7. 补充：空间索引

1.显示表的所有索引

show INDEX from employees;

2.创建索引

• CREATE INDEX idx_salary on employees(salary); 单值索引
• CREATE INDEX index_last_name_commission_pct on employees(last_name,commission_pct); 组合索引

删除索引
DROP INDEX idx__dname_mid on departments

3.建议建立索引

• 频繁作为查询条件
• 字段的数值有唯一性的限制 比如身份证号
• 查询中与其他表关联的字段
• 单值和组合索引 组合性价比更高
• 经常 GROUP BY 和 ORDER BY 的列
• DISTINCT 字段需要创建索引

4.不建议使用索引

• 表记录太少
• 经常增删改的字段
• where条件用不到的字段
• 过滤性不好的不用索引 比如 只有字段只有两值男 女

5.索引失效情况

• 使用复合索引的时候没有使用最左前缀原则 即查询从索引的最左前列开始并且不跳过索引中的列。带头索引不能没有、中间索引不能断，假设有索引A B C，查询时候的where条件 ABC可以，AB可以，AC只能用上A，BC不可以
• 不在索引上做操作 计算 函数 类型转换等都会导致索引失效。比如
• 需要范围查询的字段 建立索引要放在索引的最右边 不然会导致范围查询的右边的索引都会失效 这里注意范围字段索引依旧会生效 只是范围的右边不生效 (> < between and)
• not、!= 负判断 会导致索引失效 比如 is not null、!=
• like 前面有% 会导致索引失效
• or连接 or左边条件和右边的条件都要加上索引
• order by和group by会导致索引失效

6.order by group by解决方案
group by和order by很像 但是GROUP BY 没有过滤条件也可以用索引 order by则不行
遵照索引的最佳左前缀原则可以大大提高这两个的效率

https://www.cnblogs.com/yqzc/p/12541917.html

这个先有待补充
7.多表 JOIN 连接操作时
连接表的数量尽量不要超过 3 张
对 WHERE 条件创建索引
对用于连接的字段创建索引

8.实际开发中应避免使用*，子查询
保证被驱动表的JOIN字段已经创建了索引

修改数据类型，节省存储空间的同时，你要考虑到数据不能超过取值范围
必要的时候可以反范式化设计