1索引

介绍

索引本质上是一张表，保存了主键与索引字段，在对数据做频繁的查询或排序时，可在某些字段上添加索引，提高检索的的效率，降低IO成本，并可以使用索引列，对数据进行排序，降低排序的成本。盲目的添加索引是不正确的。索引表保存数据占用空间，在对数据进行增删改操作时，降低了操作的速度，在更新表的同时不仅要保存操作数据还需要更新索引表中的数据，因此我们可以在频繁的作为查询条件的字段上添加索引，查询中与其他表关联的字段，外检关系建立索引，在高并发下创建组合索引等恰当的使用索引事务场景。索引分为四类有唯一索引，复合索引，主键索引，单值索引。唯一索引的索引列必须是唯一的，但允许有空值，复合索引指一个索引包含多个列。例如用户表需要用户名以及手机号确定一个信息，就需要添加复合索引了。主键索引是一个特殊的唯一索引，非空且默认存在，单值索引，一个索引值包含单个列，一个表可以有多个单列索引。

1.2失效原因

1.索引失效的原因

1.模糊查询like以%开头，
2.数据类型错误
3.对索引字段使用内部函数
4.索引列是null
5.索引列运行四则运算
6.复合索引不按索引列最左特性开始查找

1.3.索引的类型

复合(最左特性)
主键
单值
唯一

1.4如何创建创建索引

创建主键索引：

alter table table_name add primary key(column);

添加UNIQUE(唯一索引)

alter table table_name add unique(column);

添加普通索引

alter table table_name add index index_name(column);

添加多列索引
sql alter table table_name add index index_name(column1,column2,column3);

2.Sql语句

2.1基础函数

lower

SELECT 'ABC',LOWER('ABC') from dept; --数据转小写

upper

select upper(dname) from dept --数据转大写

length

select length(dname) from dept --数据的长度

substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

concat

select dname,concat(dname,'123') X from dept --拼接数据

replace

select dname,replace(dname,'a','666') X from dept --把a字符替换成666

ifnull

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换

round四舍五入，ceil向上取整，floor向下取整

select comm,round(comm) from emp–直接四舍五入取整
select comm,round(comm,1) from emp–四舍五入并保留一位小数
select comm,ceil(comm) ,floor(comm) from emp–ceil向上取整,floor向下取整

now

select now() -- 年与日 时分秒
select curdate() --年与日
select curtime() --时分秒

2.2分组

group by

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno,job #deptno和job都满足的

having

select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having

2.3聚合

count

select count(*) from emp --底层优化了
select count(1) from emp --效果和*一样
select count(comm) from emp --慢,只统计非NULL的

max / min

select max(sal) from emp --求字段的最大值
select max(sal) sal,max(comm) comm from emp
select min(sal) min from emp --获取最小值
select min(sal) min,max(sal) max from emp --最小值最大值
SELECT ename,MAX(sal) FROM emp group by ename --分组

sum / avg

select count(*) from emp --总记录数
select sum(sal) from emp --求和
select avg(sal) from emp --平均数

2.4排序

order by

SELECT * FROM emp order by sal #默认升序
SELECT * FROM emp order by sal desc #降序

2.5链接

left join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
right join : 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
inner join : 内连接，又叫等值连接，只返回两个表中连接字段相等的行。

2.6多表联查

1.子查询
语法：

select  字段名  
from 表1，表2 …
where 表1.字段 = 表2.字段
and 其它查询条件

例：以学生表student和班级表class为例

Select   student.sid,  student.sname,  student.classid, class.classid,  class.classname
from student，class
where student.classid = class.classid

2.join连接
内连接 inner join
语法：

from 表1
inner join 表2
on 表1.字段 = 表2.字段

外连接:

1. left join
   语法：

select  字段名  
from 表1
left join 表2
on 表1.字段 = 表2.字段

2.right join
语法：

select  字段名  
from 表1
right  join 表2
on 表1.字段 = 表2.字段

3. left join union right join
   语法：

select  字段名  
from 表1
left join 表2
on 表1.字段 = 表2.字段
union
select  字段名  
from 表1
right  join 表2
​​​​​​​on 表1.字段 = 表2.字段

例：

select   student.*  , class.*from student
left join class
on student.classid = class.classid
union
select  student.*  , class.*from student
right join class
on student.classid = class.classid

3.笛卡尔积

select * from t_user, t_address;

2.7条件查询

distinct
使用distinct关键字，去除重复的记录行

SELECT DISTINCT loc FROM dept;

like模糊查询

select * from emp where ename like 'l%' --以l开头的
select * from emp where ename like '%a' --以a结束的
select * from emp where ename like '%a%' --中间包含a的
select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置

null

select * from emp where mgr is null --过滤字段值为空的
select * from emp where mgr is not null --过滤字段值不为空的

between and

select * from emp where sal between 3000 and 10000

limit
在mysql中，通过limit进行分页查询：

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

3.事务

3.1 特性（ACID）

原子性：一个事务要么全部成功，全部失败，不会停在某个阶段
一致性 ：事务的前后处于一致状态
隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

3.2隔离级别

1.读未提交，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）；
2.提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读），SQL server 的默认级别；
3.可重复读，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读），
MySQL 的默认级别；
4.序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

3.3三大范式

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
第三范式：任何非主属性不依赖于其它非主属性。

3.4约束

1.主键约束（Primary Key ）
保证数据的唯一性，并且主键列数据不能为空（唯一性，非空性）。

alter table 表名
add constraint 主键名 primary key(id)

2.唯一约束（Unique ）：
保证数据的唯一性，唯一约束的列可以为空（唯一性，可以空，但只能有一个）。

alter table 表名
add constraint 唯一约束名 unique(字段)

3.检查约束（Check ）：
保证数据的有效性，让值在有效范围内取值，对该列数据的范围、格式的限制（如：年龄、性别等）

alter table 表名
add constraint 检查约束名 check(age between 10 and 40)

4.默认约束（Default ）：
保证数据的完整性，如果没有入信息时，会使用默认信息填入，保证数据的完整性。

alter table 表名
add constraint 默认约束名 default('地址不详') for address

5.外键约束（Foreign Key ）：
需要建立两表间的关系并引用主表的列，保证数据的完整性，互相依赖的数据不能缺失。

alter table 表名
add constraint 外键名 foreign key(order) references info(userId)

其他操作
1.删除约束

alter table 表名
drop constraint 约束名

2.关闭约束

alter table 表名
disable constraint 约束名 cascade
//如果没有被引用则不需CASCADE关键字

3.打开约束

alter table 表名
enable constraint 约束名

4. MySQL 问题排查都有哪些手段？

使用 show processlist 命令查看当前所有连接信息。
使用 explain 命令查询 SQL 语句执行计划。
开启慢查询日志，查看慢查询的 SQL。

5.Sql优化