Select [distinct ] <字段名称> from 表 1 [ <join 类型> join 表 2 on <join 条件> ] where <where 条件> group by <字段>
having <having 条件> order by <排序字段> limit <起始偏移量,行数>
(8)Select (9)distinct 字段名 1,字段名 2, (7)[fun(字段名)] (1)from 表 1 (3)<join 类型>join 表 2 (2)on <join 条件> (4)where <where 条件> (5)group by <字段> (6)having <having 条件> (10)order by <排序字段> (11)limit <起始偏移量,行数>
MYSQL 多表查询主要使用连接查询 , 连接查询的方式主要有 :
MYSQL 索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过
单列索引 : 在 MYSQL 数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为
组合索引 : 在 MYSQL 数据库表的多个字段组合上创建的索引 , 称为组合索引也叫联合索引
创建索引语法 : 1). 创建索引 CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ; 2). 查看索引 SHOW INDEX FROM table_name ; 3). 删除索引 DROP INDEX index_name ON table_name ;
索引是在存储引擎中实现的,也就是说不同的存储引擎,会使用不同的索引
MyISAM 和 InnoDB 存储引擎:只⽀支持B+ TREE 索引, 也就是说默认使用 BTREE,不能够更换
MEMORY/HEAP 存储引擎:支持 HASH 和 BTREE 索引
MYSQL 存储引擎有很多, 常用的就二种 : MyISAM和InnerDB , 者两种存储引擎的区别 ;
聚簇索引
在使用InnoDB
存储引擎的时候, 主键索引 B+树叶子节点会存储数据行记录,简单来说数据和索引在一起存储 , 这就是聚簇索引
非聚簇索引
在使用MyISAM
存储引擎的时候, B+树叶子节点只会存储数据行的指针,简单来说数据和索引不在一起 , 这就是非聚簇索引
需要查询二次
如果使用MyISAM
存储引擎 , 会首先根据索引查询到数据行指针, 再根据指针获取数据
如果是InnoDB
存储引擎 , 会根据索引查找指定数据关联的主键 ID , 再根据主键 ID 去主键索引中查找数据
当我们为一张表的name
字段建立了索引 , 执行如下查询语句 :
select name,age from user where name='Alice'
那么获取到数据的过程为 :
name='Alice'
查找索引树 , 定位到匹配数据的主键值为 id=18
id=18
到主索引获取数据记录 (回表查询)
**先定位主键值,再定位行记录就是所谓的回表查询,它的性能较扫一遍索引树低 **
覆盖索引是指只需要在一棵索引树上就能获取 SQL 所需的所有列数据 , 因为无需回表查询效率更高
实现覆盖索引的常见方法是:将被查询的字段,建立到联合索引里去。
执行如下查询语句 : select name,age from user where name='Alice'
因为要查询 name
和 age
二个字段 , 那么我们可以建立组合索引
create index index_name_age on user(name,age)
那么索引存储结构如下 :
这种情况下, 执行select name,age from user where name='Alice'
, 会先根据name='Alice'
, 找到记录 , 这条记录的索引上刚好又包含了 age 数据 , 直接把 Alice 77
数据返回 , 就不会执行回表查询 , 这就是覆盖索引
在 mysql 建立联合索引时会遵循左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,组合索引的第一个字段必须出现在查询组句中,这个索引才会被用到 ;
例如 : create index index_age_name_sex on tb_user(age,name,sex);
上述 SQL 语句对 age
,name
和sex
建一个组合索引index_age_name_sex
,实际上这条语句相当于建立了(age) , (age,name) , (age,name,sex)
三个索引 .
select * from tb_user where age = 49 ; -- 使用索引
select * from tb_user where age = 49 and name = 'Alice' ; -- 使用索引
select * from tb_user where age = 49 and name = 'Alice' and sex = 'man'; -- 使用索引
select * from tb_user where age = 49 and sex = 'man'; -- 使用索引 , 但是只有 age 匹配索引 sex没有走索引
select * from tb_user where name = 'Alice' and age = 49 and sex = 'man' ; -- 使用索引 , 因为MySQL的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引
select * from tb_user where name = 'Alice' and sex = 'man' ; -- 不会使用索引
MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度,编写合理化的 SQL 能够提高 SQL 的执行效率
需要创建索引情况
不要创建索引情况
2.从功能方面可以对索引优化,采用缓存缓解数据库压力,分库分表。
3.从架构方面可以采用主从复制,读写分离,负载均衡
MYSQL 不是跳过 offset 行, 而是取 offset+N 行, 然后放弃前 offset 行 , 返回 N 行, 所以当 offset 比较法的情况下分页效率很低
正确的处理方法是 : 先快速定位需要获取的 id 再关联查询获取数据
可以在 MYSQL 配置文件中开启慢查询 , 有两种方式可以开启慢查询
方式一 : 修改my.ini
配置文件 , 重启 MySQL 生效
[mysqld]
log_output='FILE,TABLE'
slow_query_log='ON'
long_query_time=0.001
方式二 : 设置全局变量
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL long_query_time = 0.001;
首先可以开启慢查询, 通过慢查询日志或者命令, 获取到执行慢的 SQL 语句 , 其次可以使用EXLPAIN
命令分析 SQL 语句的执行过程
EXLPAIN 命令, 比较重要的字段(加黑加粗的是重要的) :
select_type 重点解读
type 重点解读:查询性能从上到下依次是最好到最差
extra 重点解读
MYSQL 锁按照锁的粒度分,分为以下三类: