Linux MySQL 以及SQL调优

MYSQL

SQL语言

DDL语句：

数据库定义语言： 数据库、表、视图、 索引、存储过程

DML语句：

数据库操纵语言：插入数据INSERT、删除数据DELETE、更新数据UPDATE

DCL语句

数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

DQL语句

数据库查询语言：查询数据SELECT

创建业务数据库

语法：

创建数据库

create databases;

删除存在库

drop database;

数据类型

• 数值类型
  • 整数类型 tinyint smallint mediumint int bigint
  • 浮点数类型： float double
  • 定点数类型 dec
  • 位类型： bit
• 字符串类型
  • char varchar
  • 文本类型 ： tinytext text mediumtext longtext
  • BLOB类型：tinyblob blob mediumblob longblob
  • BINARY类型: binary varbinary
  • 枚举类型: enum
  • 集合类型： set
• 日期数据类型
  • date time datetime

表格

• 创建表格

create table -name(变量名 数据类型); 需要先use 表

• 显示表格内的数据类型
  • desc -name
• 插入数据
  • insert into -name values( , )
  • insert into -name(参数） values( );
  • 默认为有符号型， 超过会报错
  • unsigned 无符号，只能存正值
• 查看表中的数据
  • select * from -name; //*表示查看所有数据

zerofill 0填充 int(8) 8位显示宽度 不是限制 ，因此整形无需指定宽度

---

浮点数类型

create table t3(float_test float(5,2)); //一共5位，小数占2位 小数可以多，整数不可以多

多出来的部分 会进行四舍五入

---

定点数decimal 精度不如float 会警告

---

位类型

bit（）二进制

hex（）十六进制

---

时间和日期类型

mysql> create table test_time( -> d date, -> t time, -> dt datetime -> );

mysql> insert into test_time values( -> now(), //获取当前时间 -> now(), -> now() -> );

---

字符串类型 char varchar

char 0~255

varchar 0~65525

char会将尾部空格删除 varchar会保留尾部空格

查看字符串长度

mysql> select length(c),length(v) from t9;

mysql> select concat(c,'='),length(v) from t9; //查看后加上=

建议使用varchar

---

字段的值 只能在给定的范围内选择

常见的是单选按钮和复选框

enum 单选

set 多选

mysql> create table student3( -> name varchar(40), -> sex enum('m','f'), -> hobby set('book','music','disc','game');

mysql> insert into student3 values('tom','m','music,book'); //插入

表操作 DDL

• 创建表 create table
• 查看表的结构 desc table show create table
• 表完整性约束 数据完整性和一致性
• 修改表 alter table
• 复制表 create table
• 删除表 drop table

---

约束条件

primary key PK ： 表的主键，可以唯一的标识记录，不可以为空

foreign key FK ： 表的外键，实习表与表之间的关联

unique key UK ： 该字段是唯一的，可以为空，一个表中可以有多个UK；

not null : 该字段不能为空

auto_increment :该字段的值自动增长

default： 该字段设置为默认值

unsigned : 无符号，正数

zerofill ：使用0填充 例如00000001；

create table st2(

id int auto_increment not null,

name varchar(50) not null,

sex enum('m','f')notnull default 'm',

age int not null default 18,

primary key(id)); //确认主键

mysql> create table service( -> host_ip varchar(15) not null, -> service_name varchar(10) not null, -> port varchar(5) not null, -> allow enum('Y','N') default 'N', -> primary key (host_ip,port) -> ); //共为主键 复合主键

当单列无法表示时

自增必须是主键， 且为整数

---

父表主键 子表外键 同步更新 同步删除

• 父表

mysql> create table employees( -> name varchar(50) not null, -> mail varchar(20), -> primary key(name));

• 子表

mysql> create table payroll( -> id int auto_increment, -> name varchar(50) not null, -> payroll float(8,2) not null, -> primary key (id), -> foreign key (name) //子表外键 -> references employees(name) //指定父表主键 -> on update cascade on delete cascade); // 同步更新 同步删除

同步更新 、

mysql> update employees set name='jackcc' where name='jack';

同步删除

mysql> delete from employees where name='tom';

修改表alter table

mysql> alter table t1 engine=innodb; //修改表的存储方式

• 添加数据

mysql> alter table t1 -> add name varchar(50) not null, -> add age int not null default 18;

• 添加数据在前后

mysql> alter table t1 -> add stu_num int not null after name, //在name后 -> add sex enum('m','f') default 'm' first); //在第一位

• 删除数据

mysql> alter table t1 drop sex;

• 修改字段类型

sql> alter table t1 modify age tinyint; //保留原有 的约束条件

mysql> alter table t1 modify id int not null primary key;

mysql> alter table t1 modify id int not null auto_increment; //已定义主键，后面增加无需保留

//增加主键可用add

mysql> alter table t2 add primary key (id,name);

//删除主键和自增 自增依赖于主键

先删自增，再删主键

• 复制表

mysql> creat table ser1 select * from service; //key不会复制 （主键，外键，和索引）

mysql> create table ser1 select * from ser where 1=2; //完全复制，但主键不会复制

DML语言

• 更新数据 INSERT
• 更新数据 UPDATA
• 删除数据 DELETE

---

插入查询结果

mysql> insert into ser2

select * from ser1

where sex='m'; //符合条件才导入

---

修改数据 update

mysql> update ser2 set name='jaccck' ,id=6 where name ='toffmdd';

---

删除数据

mysql> delete from ser2 -> where id>5; 条件

---

备份

mysqldump -uroot -p’Aa123456+’ -B company —single-transaction

conmany.sql

查询语句

单表查询 DQL

• 简单查询

mysql> select name, salary,post from employees;

• 去重查询

mysql> select distinct post from employees; 只能查单一重复

• 通过四则运算

mysql> select name ,salary*14 from employees;

mysql> select name ,salary,salary*14 as Annual_salary from employees; 取别名

• 定义显示格式

mysql> select concat(name,'annual_salary= ',salary*14) as Annual_salary from employees; 字符串拼接起来

• 单条件查询

mysql> select name, post from employees where salary>10000;

• 多条件查询

mysql> select name, post,salary from employees where salary<10000 and salary >1000;

• 范围查询

mysql> select name, post,salary from employees -> where salary between 1000 and 5000;

• 关键字

mysql> select name job from employees where job is null;

• 集合查询in

mysql> select name ,job,salary from employees where salary =4000 or salary =5000 or salary =20000;

mysql> select name ,job,salary from employees

where salary in(4000,2000,20000); 在这里面集合

where salary not in(4000,2000,20000); 不是在这里面

• like 模糊查询

mysql> select name ,salary from employees -> where name like 'al%' ; 此处% 表示任意多个字符

-> where name like ‘al___’; 确定此处多少个字符

---

• 查询排序

mysql> select * from employees -> order by salary; 升序 asc

-> order by salary desc; 降序

mysql> select * from employees

order by heir_date desc, salary asc; 先按照heir_date 降序排序 在按照salary升序排序

mysql> select sex,salary from employees where sex='male'order by salary;

---

• 限制查询的记录数

mysql> select name ,salary from employees order by salary desc limit 5; 只取前五个

mysql> select name ,salary from employees order by salary desc limit 4,5; 从第四个后取五个

---

• 集合函数查询

mysql> select count(*) from employees; 查询数量

mysql> select max(salary) from employees where sex ='male'; 查询最大

ql> select avg(salary) from employees where sex ='male'; 查询平均数

mysql> select * from employees -> where salary =(select max(salary) from employees); 子查询

小括号为先查询

---

• 分组查询 group_concat group by 结合使用

mysql> select dep_id, group_concat(name) from employees group by dep_id;

• 分组查询和集合函数结合

mysql> select dep_id,sum(salary) from employees group by dep_id;

---

• 正则表达式查询 regexp

mysql> select *from employees where name regexp '^ali'; 以ali为开头

mysql> select *from employees where name regexp 'yun$'; 以yun为结尾

mysql> select * from employees where name regexp 'm{2}'; m出现两次

• 对字符串的匹配的方式
  • where name =’tom’;
  • where name like ‘to%’;
  • where name regexp ‘^tom’;

---

• 多表查询

mysql> select e1.name,e1.age,e1.dep_id,e2.name from e1,e2;

交叉连接 无意义

---

• 内连接 互相匹配

mysql> select e1.name,e1.age,e1.dep_id,e2.name from e1,e2

where e1.dep_id=e2.id; 内连接

---

• 外连接语法

mysql> select e1.name,age,dep_id,e2.name from e1 left join e2 on e1.dep_id=e2.id; 以左边为准 就算无法匹配也会显示出来 会显示为NULL

---

• 复合条件连接查询

mysql> select e1.name,age,dep_id,e2.name

from e1 left join e2 on e1.dep_id=e2.id where e1.age<25;

mysql> select e1.name,age,dep_id,e2.name

from e1,e2 where e1.dep_id=e2.id and e1.age<22;

mysql> select e1.name,age,dep_id,e2.name

from e1,e2 where e1.dep_id=e2.id order by e1.age desc;

---

• 子查询

mysql> select * from e1

> where dep_id in(select id from e2);   子条件

mysql> select id,name from e2

>where id in (select **distinct** dep_id from e1 where age<=22);

---

• 带exists 表示 内层查询不返回查询的记录 而是返回一个真假值 true false

mysql> select * from e1 where exists(select * from e1 where age>25); 如果子查询为真则查询 ，为假则不查询

MySQL索引

mysql> \d $$ 重新定义结束符

> BEGIN 开始 -> declare i int default 1; 声明变量i -> while(i<200000)do while 循环 -> insert into company.e3 values(i,'xxx'); 存储数据 -> set i=i+i 变量i变化 -> -> end while; 结束循环 -> END$$ 结束

mysql> \d $$

mysql> create procedure test1() -> begin -> declare i int default 1; -> while (i<20)do -> insert into e3 values(i*2,'ss'); -> set i=i+1; -> end while; -> end$$

• 创建索引

mysql> create table department10( -> id int, -> name varchar, -> comment varchar, -> index(name));

• 创建唯一索引

mysql> create table department11( -> id int, -> name varchar, -> comment varchar, -> unique index(name));

• 创建全文索引

mysql> create table department12( -> id int, -> name varchar(50), -> comment varchar(50), -> log text, -> fulltext index(log));

• 建立多列索引

mysql> create table department12( -> id int, -> name varchar(50), -> comment varchar(50), -> log text, -> fulltext index(log));

• create在已存在的表上建立索引

mysql> create unique index di_index on e2(id);

mysql> alter table e2 add unique index id_index (id);

• 删除索引

mysql> drop index id_index on e2; 需要知道索引的名字

MySQL视图

• 使复杂的查询简单化，查询易于理解和使用
• 安全原因，视图可以隐藏一些数据

---

• 单表视图

mysql> create view u -> as select id,name from e2;

• 多表视图

mysql> create view purchase_detail -> as select -> product.name,product.price, -> product.price*purchase.quantity as total_value -> from product,purchase -> where [product.name=purchase.name](http://product.name%3Dpurchase.name/);

一般不修改视图，选择删除视图后重新创建视图

MySQL触发器

特殊的存储过程，由事件触发，一般进行DQL语句时会执行 insert update delete

mysql> \d $$ 修改 mysql> create trigger student_insert_trigger after insert 触发时机 -> on student for each row 每一行 -> begin 开始 -> update student_total set total=total+1; 触发行为 -> end$$ 结束

mysql> create trigger student_delete_trigger after delete -> on student for each row -> begin -> update student_total set total=total-1; -> end$$

• 两个表之间进行触发 需要old new来定位修改数据

delete from tab1 where name=’cc’;

delete from tab2 where name=old.name; //不能这样写

delete from tab2 where id=old.id; //必须用主键primary key来删除，唯一性

insert into tab1(name) values(’tom’);

insert into tab2(name,salary) values(new.name,50000);

update tab1 set name=’ccc11’ where name=’cc’;

update tab2 set name=new.name where name=old.name; //不能这样

update tab2 set name=new.name ，id =new.id ,age=new.age // 所有字段数据都要改

where id =old.id; //更新根据主键来更新 ，唯一性

• 问题：可能会删除同名的数据
• 更新和删除利用主键
• 更新需要全部更新
• 表2 需要有初始值

MySQL存储过程

存储过程和函数是事先经过编译并存储再数据库中的一段SQL语句的集合

优点：

• 存储过程只在创建时进行编译 ，提高存储过程可以提高数据库执行速度
• 简化复杂操作，结合事物一起封装
• 复用性好
• 安全性高，可指定存储过程的使用权

mysql> \d $$

mysql> create procedure t1() -> begin -> select count(*) from mysql.user; -> end$$

mysql> \d $$ mysql> create procedure t11_a() -> begin -> declare i int default 1; -> while(i<=20)do -> insert into t11(i,md5(i)); -> set i=i+1;

->end while -> end$$

• IN OUT INOUT 参数

mysql> \d $$ mysql> create procedure aut1**(in a int)** -> begin -> declare i int default 1; -> while**(i<=a)**do -> insert into t1(id,cc) values(i,md5(i)); -> set i=i+1; -> end while; -> end$$

call aut1(10);

• OUT

mysql> \d $$ mysql> create procedure p2(out per int) -> begin -> select count(*) into per from mysql.user; //将查询到的结果count值赋给per 然后输出出来 -> end$$

mysql> call p2(@num);

mysql> select @num;

---

• 输入和输出结合

mysql> \d $$ mysql> create procedure num**(in p1 varchar(50),out p2 int) 一个参数为输入一个为输出** -> begin -> select count(*) into p2 from employees -> where post=p1; -> end$$

mysql> create procedure mu(in p1 varchar(50),in p2 float(5,2),out p3 int) -> begin -> select count(*) into p3 from employees -> where post=p1 and salary >=p2; -> end$$

mysql> create procedure isA(inout p1 int) 既是输入也是输出 -> begin -> if(p1 is not null)then -> set p1=p1+1; -> else -> select 100 into p1;

-> end if; -> end$$

• 变量
• 用户变量 以 ‘@’开始 形式为‘@变量名’ 由客户端定义的边
• 全局变量 set global 变量名 或者 set @@global.变量名 对所有客户端生效，但是只有具有super权限才可以设置全局变量
• 局部变量 再begin…..end语句块中的变量
• 定义变量

set num =20;

select 100 into @num; 赋值

SQL 调优

索引

快速定位的一种存储结构，设计思想是以空间换时间

• B+tree 索引
• Hash索引
• Full-text索引
• 聚集索引
• 非聚集索引
• 主键索引 primary key
• 唯一索引 unique
• 普通索引 index
• 全文索引 fulltext
• 单列索引
• 联合索引

B+数再B-树的基础上进行优化，更适合做存储索引结构

• 非叶子节点可以存储更多的键值，能减少磁盘I/O的次数，查询效率更快
• 所有数据有序存储在叶子节点，使得范围查找，排序查找，分组查找以及去重查找变得异常简单
• 数据页之间，数据记录之间都是通过链表连接的，有了这个结构的支持就可以方便在数据查询后进行升序或者降序操作

---

• 表没有创建主键，表中也会存在隐藏主键

InnDB是MySQL的一种存储引擎，会使用一个隐藏，自动生成的主键来创建索引

---

• Hash索引 但是InnDB不支持Hash索引 只有在Memory中

---

• 物理存储，聚集索引-InnoDB 非聚集索引-MyISAM

---

• 二级索引

所有非主键索引为二级索引

利用回表来查询数据

---

• 覆盖索引

需要查询的字段都在索引列中的情况就被称为覆盖索引，索引列覆盖了查询字段的意思

例如：

• select * from user where age =35;

  如果age为二级索引，需要回表利用主键来查询age=35的数据

• select id from user where age =35;

  利用age的表，直接查询到id，就无需回表。

尽量不要select * 需要什么就查什么

---

• 索引下推

利用索引下推在范围查询减少回表次数

范围查询一次性回表

---

• 单列索引 中文字符串也可以排序

• 联合索引 遵守 最左前缀原则

• alter table test add index (name ,age,id);

  先以name排序，再以age排序，再以id排序

  最左前缀原则： 查询表的时候的条件 where name= ‘ ‘ , + age/+id

各自的优势：

如果只有一个字段就用单列索引

联合索引的优势：

1. 减少开销 建立联合索引（a，b，c），单列索引会创建每一个索引表
2. 覆盖索引 尽量不用* 而是查询什么就查询什么
3. 效率高

---

索引的优点：

1. 提高检索效率
2. 减低排序成本，索引对应的字段是会有一个自动排序功能的，默认是升序asc

索引的缺点：

1. 创建索引和维护索引会耗费时间，会随数据量的增加而增加
2. 会占用物理空间，数据量越大，占用空间越大
3. 会降低表的增删改的效率，因为每次增删改查索引，都需要动态维护

优化

• 创建索引减少扫描量
• 调整索引减少计算量
• 索引副高（减少不必访问的列，避免回表查询
• 干预执行计划
• SQL改写

1. 全值匹配
2. 最左前缀原则
   1. 从索引的最左前列开始并且不跳过索引中的列
   2. 带头不能死，中间不能断
   3. 结合B+树解释 无最左前缀原则会导致B+树叶子节点数据无序查询
3. 不在索引列上做任何操作**（计算、函数、类型转化）**，会导致索引失效而转向全表扫描 ，不符合索引的有序性，利用范围索引
4. 存储引擎不能使用索引中范围条件右边的列
   1. 范围查询会使后面的字段无序，造成部分索引失效
5. 尽量使用覆盖索引，减少select* 覆盖索引无需回表
6. 不等空值和or，索引失效要少用
   1. MySQL在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描，比较判断,导致内部优化器会根据检索比例、表的大小等多个因素整体评估是否使用索引。
7. like百分号写最右 ‘张%’ 而不使用 ‘%三’
   1. 无法避免，使用覆盖索引
8. 字符串不加单引号索引失效 ，避免类型转化
9. 范围查询优化

SQL优化

1. 不用select * 查询 利用覆盖索引
2. 小表驱动大表
   1. 小表驱动大表☞用数据量较小、索引比较完备的表，然后使用索引和条件对大表进行数据筛选，从而减少数据计算量，提高查询速率
3. 连接查询代替子查询
   1. 子查询需要执行两次数据库查询，一次外部查询，一次嵌套子查询，因此连接查询可以减少数据库查询的次数，提高查询的效率
4. 提高group by 的效率
   1. 使用group by 的列没有索引，创建一个或者多个适当的索引来加速查询
5. 批量插入 values（），（），（）；
6. 使用limit
   1. 可以限制返回的数据行数，减轻系统负担，提高查询效率
   2. 避免过度提取数据
   3. 优化分页查询
   4. 简化查询结构
7. 用union all 代替union
   1. union all 获取所有数据但是数据不去重，会包含重复数据
   2. union 获取所有数据且数据去重，不包含重复数据
8. join的表不宜过多
   1. 查询效率下降：多表join查询数据对比时间长
   2. 维护难度加大
   3. 系统负载增加