MySQL教程

MySQL教程

1. 前言

该教程不是针对完全是小白的同学，是用来复习，回顾的同学。

在这里，我已经下载安装好了MySQL，我这里使用DataGrip(一种远程连接数据库的工具)来连接上了我的数据库

你用DBeaver 或者Navicat 来连接都行。这里就不赘述MySQL安装和工具的安装了。直接开干…

MySQL版本 : mysql-5.7

主机操作系统：Windows10

所用数据库工具：DataGrip

2. MySQL初入

• MySQL 是关系型数据库 ， Redis 是非关系型数据库

• MySQL 语句不区分大小写，关键字建议用大写

• MySQL 语句结束记得加分号;

• 注释符号：

  • 单行注释：-- 或者 #
  • 多行注释：/* 注释内容 */

2.1 SQL语句分类

• DDL ：数据定义语言 （定义数据库、表、字段）

• DML：数据操作语言 （对数据库中表数据进行增删改查）

• DQL：数据查询语言 （用来查询数据库中表的信息）

• DCL：数据控制语言 （用来创建数据库用户，控制访问数据库的权限）

数据定义语言(DDL)

对数据库的操作语句如下

# 查询所有数据库
show databases;
# 查询当前正在使用的数据库（记住，是当前正在使用的哦!）
select database();
# 创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]; 
# 删除数据库
drop database [if exists] 数据库名;
# 使用数据库
use 数据库名;

注意： UTF8字符集长度占3节，有些占4节 ，所以 推荐使用UTF8mb4字符集

对表操作语句如下

# 查询当前数据库所有表
show tables;
# 查询表结构
desc 表名;
# 查询指定表的建表语句
show create table 表名；
# 创建一张表
create table 表名(
    字段1 字段1类型 [commit 字段1注释],
    字段2 字段2类型 [commit 字段2注释],
    字段3 字段3类型 [commit 字段3注释],
    ....
    字段n 字段n类型 [commit 字段n注释]
)[commit 表注释];

对字段的操作语句如下

# 向表里添加字段
alter table 表名 add 字段名 类型(长度) [commit 注释][约束];
#例如 alter table emp add empName varchar(20) commit '员工姓名';

# 修改字段数据类型
alter table 表名 modify 字段名 新数据类型(长度);
# 同时改变字段名和字段的类型
alter table 表名 change 旧字段名 新字段名 类型(长度)[commit 注释][约束]；
# 例如: 将emp 表里的user字段改为username 长度varchar(30)
# alter table emp change user username varchar(30) commit '用户名';

# 删除字段
alter table 表名 drop 字段名;
# 修改表名
alter table 表名 rename 新表名;
# 删除表
drop table [if exists] 表名;
#删除表，并重新创建该表
truncate table 表名;

数据操作语言 （DML）

插入数据

# 给指定字段插入数据
insert into 表名 (字段1,字段2,字段3,....) values(值1,值2,值3,...);
# 给全部字段插入数据
insert into 表名 values(值1,值2,值3,值4,值5,...);
# 给指定字段批量插入数据
insert into 表名 (字段1,字段2,字段3,....) values(值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...);
# 给全部字段批量插入数据
insert into 表名  values(值1,值2,值3,值4,值5,...),(值1,值2,值3,值4,值5,...),(值1,值2,值3,值4,值5,...);

注意 日期数据和字符串数据要用引号引起来

​ 插入的数据大小要在数据类型的规定范围内

修改和删除数据

# 修改表中的数据
update 表名 set 字段名1=值1，字段名2=值2,... [where 条件];
# update emp set username='JACK' where id=1;
# 删除表中的数据
delete from 表名 [where 条件];
# delete from emp where id=5;

数据查询语言(DQL)

语法结构如下

select 
	字段列表 
	from 
		表名字段
		where 
			条件列表
			group by 
				分组字段列表
				having 
					分组后的条件列表
					order by 
						排序字段列表
						limit 
							分页参数

select username,password from emp;
select * from emp;
# 设置别名
select username as user,password as pwd from emp;
select username user,password pwd from emp;   
# 可以省略`as`的

# 去除重复记录
select distinct username,password,age,high from emp;
select distinct * from emp;

# 转义字符/
select * from emp where name like '/_张三' escape '/'
# ESCAPE子句用于定义转义字符 不会把 _ 识别成特殊字符 本来这里的_ 代表单一字符

条件查询语句的运算符

> 大于		< 小于 	= 等于 	>= 大于等于		<=小于等于
!= 不等于 		
between and 在某个范围内(含最小值和最大值)
in(...)在in之后的列表里的值
like 模糊匹配 （ _ 匹配单个字符 % 匹配多个字符）
is null  为NULL

and 和 && 
or 和 ||
not 和 !

例子

# 小于、等于、大于、大于等于、小于等于、不等于 只演示一个
select * from emp where age>30; # 查询年龄大于30岁的员工

select * from emp where age between 20 and 30; # 年龄在20-30 岁之间的员工

select * from emp where age<30 and gender='女'; # 年龄小于30 并且性别为女的

select * from emp where age =21 or age=25; # 查询年龄是等于21或者25的员工数据

select * from emp where age in (19,20,21,22); # 年龄在19,20，21,22 里的员工

select * from emp where name like '__'; #姓名为两个字的

select * from emp where idcard like '%X'; # 身份证最后一位为X的

聚合查询

count 	统计数量
max		最大值
min		最小值
avg 	平均值
sum 	求和

语法：select 聚合函数(字段列表) from 表名

# 例子
select count(id) from emp where address='陕西省'; # 统计是陕西省的有多少个

select max(age) from emp;  # 查询最大的年龄的是谁

select avg(age) from emp;	# 查询平均年龄是多少

select sum(age) from emp;	# 查询总共的年龄是多少

# 通常聚合函数与group by 结合使用，分组统计数量
举个例子
分别统计emp表男女分别年龄的总和是多少
select gender,sum(age) from emp group by gender;

分组查询

# group by 后面通常会跟一个having ,是对分组后的数据再进行一次过滤
# where 和 having 的区别
	执行时机不一样，where 是分组之前进行的过滤，不满足条件的将不参与group by 分组,having 是对分组后的数据进行过滤
	判断条件不同，where 不能对聚合函数进行判断，having 可以
	

# 根据性别分组，统计男性和女性数量（只显示数量，不显示男女）
select count(*) from emp group by gender;
# 根据性别分组，统计男性和女性数量
select gender, count(*) from emp group by gender;
# 根据性别分组，统计男性和女性的平均年龄
select gender, avg(age) from emp group by gender;
# 年龄小于35，并根据地址分组
select address, count(*) from emp where age < 35 group by address;
# 年龄小于35，并根据地址分组，获取员工数量大于等于3的工作地址
select address, count(*) emp_count from emp where age < 35 group by address having emp_count>=3;

排序查询

asc 升序（默认）
desc 降序
select * from emp order by age asc;
select * from emp order by age desc;
# 两字段排序，根据年龄升序排序，入职时间降序排序
select * from emp order by age ASC, entrydate DESC;

分页查询

# 只展示10条数据
select * from emp limit 0,10;
# 查询第二页数据
select * from emp limit 10,10;

# 注意:
	-- 起始索引从0开始; 起始索引=(查询页码-1)*每页显示记录数
	-- 如果查询的是第一页的数据，0 可以忽略 简写 limit 10

数据控制语言(DCL)

管理用户

# 查询用户
use mysql;
select * from user;
# 创建用户
create USER '用户名'@'主机名' identitied by '密码'；
# 修改用户密码
alter USER '用户名'@'主机名' identitied with  mysql_native_password by '新密码';
# 删除用户
drop USER '用户名'@'主机名';

# 例子
-- 创建用户myroot，只能在当前主机访问
create USER 'myroot'@'localhost' identified by '123456';
-- 创建用户myroot，能在任意主机访问
create user 'myroot'@'%' identified by '123456';
-- 修改密码
alter user 'myroot'@'localhost' identified with mysql_native_password by 'qwer123456';
-- 删除用户
drop user 'myroot'@'localhost';

权限控制

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据权限
DELETE	删除数据权限
UPDATE	修改数据权限
INSERT	插入数据权限
ALTER	修改表权限
DROP	删除(数据库/表/视图)权限
CREATE	创建数据库/表权限

# 授予权限
语法: grant [权限列表] on 数据库名.表名 to '用户名'@'主机名';
# 撤销权限
语法: revoke [权限列表] on 数据库名.表名 from '用户名'@'主机名';

# 注意 
	多个权限用逗号分隔
	授权时，数据库名和表名可以用 * 通配，代表所有

2.2 函数

• 字符串函数
• 数值函数
• 日期函数
• 流程函数

字符串函数

select concat ('Hello','World'); # 拼接字符串函数concat 将多个字符串拼接成一个字符串

select lower('HELLO'); # 将字符串变为小写

select upper('hello'); # 将字符串变为大写

select lpad('world',10,'+'); # 用字符串 + 号对字符 world 左边进行填充，以达到总数为10个字符
# 结果为: +++++world

select rpad('hello',10,'-'); # 用字符串 - 号对字符 hello 右边进行填充，以达到总数为10个字符
# 结果为：hello-----

select trim("Hello World"); # 去除空格

select substring("helloworld",1,5);# 查询从第一个字符往后数五个字符的长度

数值函数

select ceil(7.6);  # ceil 向上取整

select floor(5.3); # floor  向下取整

select mod(10,5); # 返回10/5的模

select rand(); # 返回0-1的随机数

select round(7.643,1); # 四舍五入到小数点后1位
select round(7.6,0); # 结果是8 0 代表0位小数点

日期函数

select current_date; # 返回当前日期 2024-04-02
select current_time; # 返回当前时间 22:52:43
select now(); # 返回当前日期和时间
select year('2024/12/21'); # 返回指定时间的年份  这里结果是2024
select month('2024/12/21');# 返回指定时间的月份  12
select day('2024/12/21'); # 返回指定时间的日  21

流程函数

if(value,x,y); # 如果value 为true 返回x 否则返回y
ifnull(value1,value2); # 如果value1不为空，返回value1 否则value2
case when then else [default] end
select name, (case when age > 30 then '中年' else '青年' end) from employee;

2.3 约束

约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

not null 非空约束

unique 唯一约束

primary key 主键约束

default 保存数据时，如果未指定该字段的值，则采用默认值

foreign key 外键约束

2.4 多表查询

内连接查询

# 查询员工姓名，及关联的部门的名称
# 显式
select e.name, d.name from employee inner join dept on e.dept = d.id;

左外连接查询

# 左 left join
# 查询左表所有数据，以及两张表交集部分数据
# 当于查询表1的所有数据，包含表1和表2交集部分数据
select e.*, d.name from employee left outer join dept on e.dept = d.id;

右外连接查询

#右 right join
# 查询右表所有数据，以及两张表交集部分数据
select d.name, e.* from employee right outer join dept as d on e.dept = d.id;

2.5 事务

事务是一组操作的集合，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求, 即这些操作要么同时成功，要么同时失败。

基本操作

方式一

# 模拟张三给李四转账
# 1. 查询张三账户余额
select * from account where name = '张三';
# 2. 将张三账户余额-1000
update account set money = money - 1000 where name = '张三';
#   此语句出错后张三钱减少但是李四钱没有增加
模拟sql语句错误
# 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';

# 查看事务提交方式
SELECT @@AUTOCOMMIT;
# 事务提交方式，1为自动提交，0为手动提交，该设置只对当前会话有效
SET @@AUTOCOMMIT = 0;
# 提交事务
COMMIT;
# 回滚事务
ROLLBACK;

# 设置手动提交后上面代码改为：
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
commit;

方式二

# 开启事务：
# START TRANSACTION 或 BEGIN TRANSACTION;
start transaction;
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
commit;

事务四大特性

• 原子性：事务是不可分割的最小单元，要么同时成功、要么同时失败
• 一致性：事务完成时，所有的数据必须保持一致状态
• 隔离性：数据库系统提供的特有隔离机制，使得事务在执行的时，不受外界影响。
• 持久性：事务一旦提交或回滚，它的数据改变就是永久的

并发事务产生的影响

• 脏读：一个事务读到另一个事务还未提交的数据
• 幻读：查询无此条数据，但是插入同样的数据时，又发现这行数据存在
• 不可重复读：一个事务先后读取同一条数据，但两次读到的数据不一致

并发事务隔离级别

读未提交(Read uncommitted) ：会出现 脏读、不可重复读、幻读

• 该级别效率最高，数据安全性最差

读已提交(Read committed) ： 出现不可重复读，幻读

• 该效率较高，数据安全性较差

可重复读(Repeatable Read(默认)) ：会出现幻读

• 该效率一般，数据安全性较高

序列化读(Serializable) ： 不会出现任何影响

• 效率最低，数据安全性最高

# 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

# 设置事务隔离级别：
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED |REPEATABLE READ | SERIALIZABLE };
# SESSION 是会话级别，表示只针对当前会话有效，GLOBAL 表示对所有会话有效

3. MySQL进阶

3.1 MySQL的存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。

存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。

默认存储引擎是 InnoDB

此外还有 MyISAM 、Memory 等

# 查询支持的存储引擎
show engines;

# 在建表的时候指定存储引擎
create table test(
    .....
)ENGINE=INNODB;

InnoDB

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎。

特点

• 支持事务
• 支持行级锁
• 批量插入速度低
• 支持外键约束

文件

• InooDB存储表结构、数据、索引的文件是 xxxx.ibd 每张表都有这样一个表空间文件，

MyISAM

MyISAM 是 MySQL 早期的默认存储引擎

特点

• 不支持事务、不支持外键
• 支持表锁，不支持行级锁
• 批量插入速度高
• 访问速度快

文件

• xxx.sdi 存储表数据
• xxx.MYD 存储表结构
• xxx.MYI 存储表索引

Memory

Memory 引擎的表数据是存储在内存中的，受硬件、断电的影响，只能将这些表作为临时表或缓存使用

特点

• 存放内存中，访问速度极快
• 支持表锁
• 批量插入速度高
• hash索引 (默认)
• 不支持外键

文件

• xxx.sdi 存储表结构

存储引擎的选择

• InnoDB: 如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性

  数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择

• MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作

  并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的。

• Memory: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存

  Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

电商中的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎。

3.2 索引

• 介绍

索引是帮助MySQL高效获取数据的一种数据结构

• 优缺点

  优点：提高数据的查找效率，降低数据库的I/O成本。

  ​ 通过索引对数据进行排序，降低CPU的消耗成本。

  缺点：索引列也是要占用空间的

  ​ 影响数据表的更新速度，（增删改的效率）

索引数据结构

• B+Tree索引结构：支持的引擎很多（InooDB）
• Hash索引结构：底层使用哈希表实现(只有Memory引擎支持)
• R-Tree索引：很少用，主要是地理空间数据类型用的多
• Full-Text索引：全文索引

面试题:为什么InooDB选择B+Tree结构，而不是使用其他?

回答：相较于二叉树，数据量过大的时候，它的层级会更少，搜索效率高

​ B+树只有叶子节点存储数据，B树每个节点都有存放数据，导致一页中键值变少，影响性能。

​ B+树支持范围匹配以及排序操作，二叉树只支持等值匹配

索引分类

• 主键索引：指定主键的时候，会默认创建一个主键索引，只能有一个。Primary
• 唯一索引：Unique 避免数据列重复 可以有多个
• 常规索引：快速定位特定数据 可以有多个
• 全文索引：它查找的是文中的关键字，而不是比较索引中的值 fulltext 可以有多个

根据索引的存储形式，分为聚集索引、二级索引。

聚集索引必须有且只有一个

二级索引可以存在多个

聚集索引选取标准：有主键选主键

​ 没有主键，有唯一索引就选唯一索引

​ 没有主键且没有唯一索引，就隐式创建一个rowid索引

思考题

# 假设有一张表，字段分别是	id name address
# id 是主键，默认创建了聚集索引，而现在，我们给name创建一个二级索引。
# 说明：聚集索引叶子结点存储的是这一行的数据
#	   二级索引叶子节点存储的是这一行的id

# 那么请看下面两个sql语句，判断谁的效率高。
select * from user where id=8;
select * from user where name="Arm";

# 很明显，第一条SQL语句的效率要高于第二条。
# 第一条是通过id这个索引去B+Tree里查询的，查到数据后，直接返回的就是我们需要的这一行的数据。只需要一次查询。
# 第二条是通过name去查，它先查到Arm，Arm存储的是id,然后，通过id再去id建立的索引树去查，查了两次。效率较低。

我们称二级索引的查询为回表查询

索引语法

• 创建索引

  CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);
  # 如果不加 CREATE 后面不加索引类型参数，则创建的是常规索引
  

• 查看索引

SHOW INDEX FROM table_name;

• 删除索引

DROP INDEX index_name ON table_name;

练习创建、查看、删除索引

准备一张user表，例如有如下字段。

id	name	phone	email	profession	age	status
1	张三	18968233445	158726323@163.com	软件工程	24	在线

# 有如下三个需求
1. 为name这个可能重复的字段创建索引
2. phone 字段是唯一的，也请创建索引
3. 为profession、age、status创建联合索引

# 第一个需求，创建常规索引
create index idx_user_name on user(name);

# 第二个需求，创建唯一索引
create unique index idx_user_phone on user(phone);

# 第三个需求 ，创建联合索引
create index idx_pro_age_sta on user(profession,age,status);

# 查看索引
show index from user;

#删除name索引
drop index idx_user_name on user;

SQL性能分析

SQL优化，优化的是查询语句，增删改优化的的比重较低

查看当前数据库的 INSERT, UPDATE, DELETE, SELECT 访问频次

show global status like 'Com_______' # 七个下划线 _

慢日志查询

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

MySQL慢日志查询默认不开启，需在配置文件里配置 /etc/my.cnf

# 开启慢查询日志开关
slow_query_log=1  # 1开启 0 关闭

# 设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

# 更改后记得重启MySQL服务
# 慢日志文件位置：/var/lib/mysql/localhost-slow.log

# 查看慢查询日志开关状态：
show variables like 'slow_query_log';

profile

• show profile; 能在做SQL优化时帮我们了解时间都耗费在哪里。

• 查看当前MySQL 是否支持 profile 操作： SELECT @@have_profiling;

profile 默认关闭，打开操作 SET profiling = 1;

# 查看所有语句的耗时
show profiles;

# 查看指定query_id的SQL语句各个阶段的耗时：
show profile for query query_id;
# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

最左前缀法则

如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。

索引失效的情况

• 在索引列上进行运算操作，索引将失效。

  ​ 如：explain select * from tb_user where substring(phone, 10, 2) = '15';

• 字符串类型字段使用时，不加引号，索引将失效。

  ​ 如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号。

• 模糊查询中，如果仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。

  ​ 如：explain select * from tb_user where profession like '%工程';，前后都有 % 也会失效。

• 用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。

• 如果 MySQL 评估使用索引比全表更慢，则不使用索引。

SQL提示

是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的

例如

• 使用某个索引
  explain select * from tb_user use index(idx_user_pro) where profession="软件工程";
• 忽略某个索引
  explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";
• 强制使用某个索引
  explain select * from tb_user force index(idx_user_pro) where profession="软件工程";

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少 select *

尽量不要用select *，容易出现回表查询，降低效率，除非有联合索引包含了所有字段

explain 中 extra 字段含义

• using index condition：查找使用了索引，但是需要回表查询数据
• using where; using index;：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

面试题：

有一张表，有四个字段（id, username, password, status），

由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案：
select id, username, password from tb_user where username='itcast';

==解：==给username和password字段建立联合索引，则不需要回表查询，直接覆盖索引

单列索引和联合索引

• 如果存在多个查询条件，考虑查询字段建立索引时，建议建立联合索引，而非单列索引。

• 设计原则

  1. 针对于数据量较大，且【查询】比较频繁的表建立索引（超过一百多万就要建立索引）
  2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
  4. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
  5. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
  6. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

3.3 SQL语句优化

插入语句的优化

• 1. 不要单个插入一条数据，尽量批量插入。且批量插入数据量 < 1000条，如果插入数据量上万，则分开批量插入。

• 2. 要手动提交事务。

     start transaction;
     insert into xxx........;
     insert into xxx.......;
     commit;
     

• 3. 主键顺序插入数据

大批量插入数据

大批量插入数据的时候（比如插入一百万条数据到某个表中）用insert 会非常慢
那么此时就可以用load指令来从本地加载这些数据。
具体操作如下
# 1,客户端连接服务端时，加上 --local-infile 参数
mysql --local-infile -u root -p

# 2.打开load加载开关。 1打开 0 关闭
select @@local_infile; 
set global local_infile=1;

# 3. 将本地数据加载到数据表中
load data local infile '/root/sql_infile' into table t_user fields terminated by ',' lines terminated by '\n';

'/root/sql_infile' # 是资料存储的路径。
fields terminated by ',' #是数据之间的分隔符
lines terminated by '\n'; #是每条数据之间的分隔符

# sql表和数据的例子如下
sql表字段
id , username , password ,name sex

#数据如下
1,aaaa,aaa111,zhangsa1,1
2,bbbb,bbb111,zhangsa2,0
3,cccc,ccc111,zhangsa3,1
4,dddd,ddd111,zhangsa4,0
5,eeee,eee111,zhangsa5,1
6,ffff,fff111,zhangsa6,0
.......

主键优化

• 满足业务需求情况下，尽量降低主键长度
• 使用自增主键
• 尽量不要使用UUID做主键
• 业务操作时，尽量避免对主键的修改

order by 优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

如果order by字段全部使用升序排序或者降序排序，则都会走索引，但是如果一个字段升序排序，另一个字段降序排序，则不会走索引

总结：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则（也就是创建联合索引时的顺序）

尽量使用覆盖索引

多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）

如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小  sort_buffer_size（默认256k）

group by 优化

• 在分组操作时，可以通过索引来提高效率
• 分组操作时，索引的使用也是满足最左前缀法则

如索引为idx_user_pro_age_stat，则句式可以是select ... where profession order by age，这样也符合最左前缀法则

limit 优化

常见的问题如limit 2000000, 10，此时需要 MySQL 排序前2000000条记录，但仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化方案：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

# 此语句耗时19s
select * from tb_sku limit 9000000, 10;
# 通过覆盖索进行排序及查询
select id from tb_sku order by id limit 9000000, 10;
# 下面的语句是错误的，MySQL 不支持 in 里面使用 limit
# select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
# 通过联表查询可实现第一句的效果，并且能达到第二句的速度
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;