课程来源:黑马程序员 MySQL数据库入门到精通
老师讲解很细致呀,本文只是对讲课笔记的整理,有问题欢迎大家指正!
引擎请看上一篇:【黑马程序员数据库】数据库引擎
0. 索引总结(课程P88,复习用)
0.1 本章框架
0.2 索引分类:
第一种分类
- 常规索引:正常建立的索引,允许被索引的数据列包含重复的值
- 唯一索引:如果数据在表中是唯一的无重复,可以用唯一(比如身份证号)
- 主键索引:是一种特殊的唯一索引,一张表只能定义一个主键索引,确定主键时自动创建
- 全文索引:select *
第二种分类:根据 InnoDB 引擎中,根据存储结构分为两类
- 聚集索引:一张表中必须存在,默认主键索引就是聚集索引,没有主键会选择第一个唯一索引作为聚集索引
- 二级索引
覆盖索引:查询返回的列在索引中都包含了,不需要回表查询
前缀索引:字符串长度较长,用来缩小索引体积
单列/联合索引:多列返回值选择联合索引,避免回表查询(单列索引包括:常规、唯一、主键)
1. 索引分类
1.1 分类一
1.2 分类二
1.2.1 存储结构
1.3 聚集索引选取规则
1.4 回表查询
二级索引拿到id之后会回到聚集索引查(回表查询)
所以直接用聚集索引查效率更高
指针永远比主键多一个(怎么来的,再看看 )
一页16行,一行1k(1024字节)=16*1024
我咋没明白绿色数据到底存哪了,图里面的页是啥意思?
2. 索引语法
会重复可以使用行索引,b+tree,idx_user_name 是键名,tb_user是表名
create index idx_user_name on tb_user(name);
唯一索引
create unique index idx_user_phone on tb_user(phone);
联合索引:profession,age,status顺序是有讲究的
create index idx_user_pro_age_sta on tb_user(profession,age,status);
删除索引:从 tb_user 表中删除 idx_user_name
drop index idx_user_name on tb_user;
3. SQL性能分析
3.1 执行频次
SQL执行频率,主要看查询频率,如果不经常不用优化
模糊查询,com后面7个下划线代表七个字符
看那个执行的多就对谁优化
这里查询有1653 就主要对查询进行优化
3.2 慢查询日志
使用linux配置
shell 中使用vi编辑器
vi /etc/my.cnf
按G切换到最后
增加上面写的两个配置信息
执行后重启mysql systemctl restart mysqld
使用Windows配置
直接打开文件夹
3.3 profiles
开启 profiles
show profiles; 看sql语句的耗时情况
3.4 explain
没有索引是全表搜索,索引是用空间换时间
4. 索引的使用
4.1 联合索引使用
只要存在就可以,在什么位置无所谓,mysql会自动调节顺序
范围查询(age>30)的右边的索引会失效,使用>=右边不会失效
4.2 索引失效
4.2.1 索引列运算
4.2.2 字符串不加引号
4.2.3 模糊查询
前面加%就不能用索引,性能就会差
4.2.4 or连接
or:一侧有索引,一侧没有,两次索引都会失效
4.3 SQL提示
4.3.1 mysql优化器自动选择
走索引没有全表扫描快,放弃索引直接全表扫描
isnull是否走索引,由表中有多少null数据决定
SQL提示:比如联合索引和单列索引都有,如果不规定会按mysql优化器自动选择
4.3.2 SQL提示
use index是你建议用哪个索引,如果mysql认为这个索引不合适可以用其他的
force会强制 不听mysql的
4.4 覆盖索引
尽量使用覆盖索引笔面使用select *
查询使用索引,并且需要返回的列都能找到。
在三个字段建立联合索引效率比较高,在itcast建立单列索引需要 回表查询
4.5 前缀索引
可以将字符串的一部分前缀提取出来简历索引,节约索引空间提高效率
语法:create index idx_xxx on 表名(column(n))
4.6 单列索引和联合索引的选择
在多条件查询时,联合索引效率更高,单条件查询使用单列索引。
例子:
即使每个字段都建立了单列索引,MySQL还是会选择最优索引,而不是使用所有索引,就会有回表查询。