SQL基础到进阶到运维 整理笔记

网上学习笔记–>黑马

一.sql 分类

• DDL(数据库定义语言) ：用来定义数据库对象（数据库，表–跟表数据无关，字段）
• DML(数据库操作语言)：用来对数据库表数据进行增删改
• DQL(数据库查询语言)：用来查询数据库表的记录
• DCL(数据库控制语言)：用来创建数据库用户，控制数据库的访问权限

二.DDL–定义数据库对象（数据库，表–跟表数据无关，字段）

DDL-数据库操作

1. 查询所有数据库

show databases;

2. 查询当前数据库 (就是看当前在那个数据库)

 select database();

3. 创建

create database [if not exists] 数据库名 []

4. 删除

drop database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

5. 使用

use 数据库名;

DDL-表操作-查询

1. 查询当前数据库所有表

show tables;

2. 查询表结构

desc 表名；

3. 查询指定表的建表语句

show create table 表名；

DDL-表操作

1. 表创建

CREATE TABLE 表名(
字段1 字段1类型 [COMMENT字段1注释] ，
字段2 字段2类型 [COMMENT 字段2注释] ,
字段3 字段3类型 [COMMENT字段3注释] ,
字段n 字段n类型 [COMMENT字段n注释]
)[ COMMENT 表注释];

2. 字段类型

1. 数值类型

类型	大小(byte)	有符号范围	无符号范围	描述
tinyint	1	(-128，127)	(0，255)	小整数值
smallint	2	(-32768,32767)	(0，65535)
mediumint	3	(-8388608,8388607)
int / integer	4	(-2147483648,2147483647)
bigint	8	(-2^63 , 2^63 -1)
float	4
double	8			score double(4,1)就是小数一位（99.5分）
decimal

2. 字符串类型

类型	大小(byte)	描述
char	0-255	定长字符串（不够的用空客补充，性能好）性别只有男或女用这个
VARCHAR	0-65535 bytes	变长字符串（不够的不会用空客补充，性能较差）年龄字数不同用这个
TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65535 bytes	二进制形式的长文本数据
TEXT	0-65535 bytes	长文本数据
MEDIUMBLOB		二进制形式的中等长度文本数据
MEDIUMTEXT		中等长度文本数据
LONGBLOB		二进制形式的极大文本数据
LONGTEXT		极大文本数据

3. 日期类型

类型	大小	范围	格式	描述
DATE	3	1000-01-01 至9999-12-31	YYY-MM-DD	日期值
TIME	3	-838:59:59 至838:59:59	HH:MM: SS	时间值或持续时间
YEAR	1	1901 至2155	YYYY	年份值
DATETIME	8	1000-01-0100:00:00至9999-12-31 23:59:59	YYYY-MM-DD HH :MM:SS	混合日期和时间值
TIMESTAME	4	1970-01-01 00:00:01至2038-01-19 03:14:07	YYYY-MM-DD HH :MM:SS	混合日期和时间值,时间戳

3. 表修改

1. 增加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT '注释'] [约束];

2. 修改数据类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

3. 修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

4. 修改表名

ALTER TABLE 表名 RENAME TO 新表名;

4. 表删除

1. 删除表==全删

DROP TABLE [ IF EXISTS ] 表名 ;

3. 删除指定表，并重新创建该表==删除数据

TRUNCATE TABLE 表名;

三.DML–对数据库表数据进行增删改

DML-添加数据

1. 给指定字段添加数据

INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,..);

3. 给全部字段添加数据

INSERT INTO 表名VALUES(值1,值2,.…);

5. 批量添加数据

INSERT INTO 表名(字段名1,字段名2, ..) VALUES(值1,值2...),(值1,值2,...),(值1，值2...);
INSERT INTO 表名 VALUES (值1,值2,..),(值1,值2,..),(值1,值2,...);

DML-修改数据

UPDATE 表名 SET 字段名1=值1,字段名2=值2,....[ WHERE 条件 ];

DML-修改数据

DELETE FROM 表名 [ WHERE条件 ]

四.DQL–查询数据库表的记录

SELECT>4
			字段列表
FROM>1
			表名列表
WHERE>2
			条件列表
GROUP BY>3
			分组字段列表
HAVING
			分组后条件列表
ORDER BY>5
			排序字段列表
LIMIT>6
			分页参数
住 123....6是执行顺序

DQL-基本查询

1. 查询多个字段

SELECT 字段1,字段2,字段3... FROM 表名;
SELECT * FROM 表名;

2. 设置别名

SELECT 字段1 [AS 别名1] , 字段2 [AS 别名2] ... FROM 表名;

3. 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

DQL-条件查询

1. 语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2. 条件

> >= < <= = <>或!=
between and
in()
like 占位符----- _——单个字符 %——多个字符 
is null
and或&&
or或||
not或!

DQL-聚合函数

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

DQL-分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件] GROUP BY 分组字段名 [ HAVING 分组后过滤条件];

where与having区别
执行时机不同: where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
判断条件不同: where不能对聚合函数进行判断，而having可以。
例：
查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress,count(*) from emp where age< 45 group by wonkaddress having count(*) >= 3;

DQL-排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 ，字段2 排序方式2;
ASC:升序（小的在第一行）默认值
DESC:降序

DQL-分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

五.DCL–创建数据库用户，控制数据库的访问权限

DCL-管理用户

1. 查询用户

首先进入mysql数据库，
USE mysql;
SELECT * FROM user;

2. 创建用户

CREATEUSER ‘用户名'@'主机名’ IDENTIFIED BY ‘密码’;

3. 修改用户密码

ALTER USER ‘用户名'@'主机名’ IDENTIFIED with mysql_native_password BY‘新密码';

4. 删除用户

DROP USER ‘用户名'@'主机名';

DCL-权限控制

1. 查询权限

SHOW GRANTS FOR ‘用户名'@'主机名’;

2. 授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

3. 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM  ‘用户名'@'主机名';

六.函数

字符串函数

函数	功能
CONCAT(S1,S2.….Sn)	字符串拼接，将S1，S2,…Sn拼接成一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str,n,pad)	左填充,用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,Rad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

由于业务需求变更,企业员工的工号,统一次5位数，目前不足5位数的全部在前面补0。比如:1号员工的工号应该为00081。

update emp set workno = lpad( workno , 5 , ' 0 ' ) ;

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(xy)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入的值,保留y位小数

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数

流程函数

函数	功能
IF(value , t, f)	如果value为true，则返回t,否则返回f
IFNULL(value1 , value2)	如果value1不为空,返回value1,否则返回value
CASE WHEN [ val1 ] THEN [res1] …ELSE [ default ] END	如果val1为true，返回res1,…否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN [res1] … ELSE [ default] END	如果expr的值等于val1，返回res1,…否则返回default默认值

七.约束

概述

1．概念: 约束是作用于表中字段上的规则，用于限制存储在表中的数据。
2．目的: 保证数据库中数据的正确、有效性和完整性。
3．分类:

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接,保证数据的一致性和完整性	DEFAULT

使用

create table user(
id int primary key auto_increment comment '主键 ,
name varchar(10) not null unique comment '姓名',
age int check ( age > 0 && age <= 120 ) comment '年龄' ,
status char(1) default '1' comment '状态'，
gender char(1)comment ‘性别'
)comment '用户表;

外键约束

1.建表时添加外键
CREATE TABLE表名(
字段名数据类型 ,
…
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);

2.建表之后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

3.删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除 / 更新行为

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与RESTRICT一致)
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与NO ACTIO
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(这就要求该外键允许取nul)。
SET DEFAULT	父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)

举例：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGNKEY(外键字段) REFERENCES 主表名(主表字段名）ON UPDATE CASCADE ON DELETE CASCADE;

八.多表查询

连接查询–内连接

隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件..;
>显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件... ;

连接查询-外连接

左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件...;
相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据

右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件...;
相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据

连接查询-自连接

自连接查询语法:
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

联合查询-union , union all

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A....
UNION [ ALL ]
SELECT 字段列表 FROM 表B .... ;

对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

将薪资低于5000的员工，和年龄大于50的员工查出来
select * from emp where salary<5000
union
select * from emp where age>50

子查询

概念:SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT * FROM t1 WHERE column1 =(SELECT column1 FROM t2) ;

查询在“方东白”入职之后的员工信息

a．查询方东白 的入职日期
select entrydate from emp where name = '方东白';

b．查询指定入职日期之后入职的员工信息
select * from emp where entrydate >方东白的entrydate 
select * from emp where entrydate > (select entrydate from emp where name = '方东白');

子查询–列子查询

列子查询

子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询。
常用的操作符:IN .NOT IN、ANY 、SOME 、 ALL

操作符	描述
IN	在指定的集合范围之内,多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

查询比财务部所有人工资都高的员工信息

 a．查询所有财务部人员工资
 
 select id from dept where name = '财务部';
 select salary from emp where dept_id = (select id from dept where name = '财务部');
 
 b．比财务部所有人工资都高的员工信息
select * from emp where salary > all ( select salary from emp where dept_id = (select id fron dept where nane= ‘财务部'〕);

子查询–行子查询

行子查询
子查询返回的结果是一行（可以是多列)，这种子查询称为行子查询。
常用的操作符 : =、<>、IN、NOT IN

查询与"张无忌”的薪资及直属领导相同的员工信息;

a,查询“张无忌”的薪资及直属领导

select salary, managerid from emp where name = '张无忌';

 b．查询与"张无忌”的薪资及直属领导相同的员工信息;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name= ‘张无忌');

子查询–表子查询

表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符:IN

查询入职日期是“2806-01-01”之后的员工信息，及其部门信息

a,入职日期是“2086-01-01”之后的员工信思

select * from emp where entrydate > '2006-01-01';

b、查询这部分员工,对应的部门信悬;
select e.* , d.*  from (select * from emp where entrydate > '2086-81-01') e left join dept d on e.dept_id = d.id ;

九.事务

事务操作

方式一

• 开启事务

START TRANSACTION或 BEGIN ;

• 提交事务

COMMIT;

• 回滚事务

ROLLBACK;

方式二

• 查看/设置事务提交方式

SELECT @@autocommit;
SET @@autocommit =0;（0/false是手动提交，1/true是自动提交）

• 提交事务

COMMIT;

• 回滚事务

ROLLBACK;

并发事务问题

问题	描述	解读
脏读	一个事务读到另外一个事务还没有提交的数据。	事务A读到事务B修改的数据，但事务B未提交，事务B回滚
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。	事务A两次读取数据，事务B对数据修改，事务A再次读取，数据前后不一样
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了’	事务A两次读取数据，事务B添加数据，事务A再次读取，数据不一致（删除不是幻读）
脏写	两个事务，一个事务修改了另一个未提交的修改事务	事务A修改数据时，未提交前，事务B也去修改这条数据，事务B未提交，而这时事务A提交了，然后事务B回滚了，发现事务A修改的不存在了
更新丢失	两个事务对同一个数据进行更新，一个事务修改的值覆盖另一个事务	事务A更新了一条数据并提交了，事务B更新了一条数据并提交了，然后事务A继续查询更新的数据，发现与自己更新的不一样

事务四大特性

• 原子性(Atomicity)∶事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性(Consistency) : 事务完成时，必须使所有的数据都保持一致状态(全成功或全失败)。
• 隔离性(lsolation) : 数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability) : 事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

事务隔离级别

隔离级别(从上到下，级别越高，性能越低)	赃读	不可重复读	幻读
Read uncommitted	不能	不能	不能
Read committed		不能	不能
Repeatable Read(默认)			不能
Serializable

注：以上隔离级别，脏写都能解决

• 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

• 设置事务隔离级别

SET [ SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READUNCOMMITTED | READ COMMITED│ REPEATABLEREAD | SERALZABLE}

十.存储引擎

存储引擎简介

1. 在创建表时，指定存储引擎

CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释]，
	字段n 字段n类型 [COMMENT 字段n注释]
)ENGINE = INNODB [ COMMENT 表注释];

2. 查看当前数据库支持的存储引擎

SHOW ENGINES;

存储引擎特点

lnnoDB

• 介绍

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

• 特点
  1. DML操作遵循ACID模型，支持事务;
  2. 行级锁，提高并发访问性能;
  3. 支持外键FOREIGN KEY约束，保证数据的完整性和正确性;

MylSAM

• 介绍

    MyISAM是MySQL早期的默认存储引
  

• 特点
  1. 不支持事务，不支持外键
  2. 支持表锁，不支持行锁
  3. 访问速度快

引擎对比

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	–	–
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	–	–	支持
全文索引	支持(5.6版本之后)	支持	–
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持	–	–

存储引擎选择

• InnoDB : 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM : 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY : 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

十一.索引

索引概述

介绍

1. 索引是数据库中的一种数据，用于指向实际的数据。
2. 索引 (index）是帮助MySQL高效获取数据的数据结构(有序)，没有索引查找数据是全盘检索的方式。

索引优缺点

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消	索引大大提高了查询效率，同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。

索引结构

B-Tree ---- MyISAM 支持

      B-Tree是一种平衡搜索多叉树，树是排序过的，采用是二分查找方式，查找的效率跟树的高度有关

B+Tree ---- MyISAM，InnoDB 支持

    MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

相对于B-Tree区别:
①.所有的数据都会出现在叶子节点
②.叶子节点形成一个单向链表

为什么InnoDB存储引擎选择使用B+tree索引结构?

1. 相对于二叉树，层级更少，搜索效率高;
2. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;
3. 相对Hash索引，B+tree支持范围匹配及排序操作;

索引语法

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 tableiname ;

drop table if exists employee;
create table employee
(
	emp_id int primary key auto_increment, -- 主键索引
	emp_no varchar(20) not null unique, -- 唯一索引
	emp_name varchar(20) not null,
	emp_info text not null,
	emp_age int not null,
	emp_gender varchar(2) not null
)ENGINE = myisam;

create index ix_emp_name on employee(emp_name); -- 普通索引

create fulltext index ix_emp_text on employee(emp_info);-- 全文索引

create index ix_emp_name_age on employee(emp_name,emp_age);-- 组合索引

drop index ix_emp_name on employee;-- 删除索引

SQL性能分析

SQL执行频率

MySQL客户端连接成功后，通过show[sessiongloball status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:

SHOW GLOBAL STATUS LIKE 'Com___' ;

慢查询日志

慢查询日志记录了所有执行时间超过指定参数( long_query_time，单位:秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf）中配置如下信息:

#查看慢查询日志是否开启
show variables like 'slow_ query 1og' ;

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

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

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。

profile详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作:

SELECT @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling:

SET profiling=1;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时:

#查看每一条SQL的耗时基本情况
show profiles;

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

#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain执行计划

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

#直接在select 语句之前加上关键字explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

索引使用—即失效情况

最左前缀法则

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

explain select * from tb_user where profession= "软件工程 and age = 31 and status = '0'; （没有失效，先后顺序可以改变）
explain select * from tb_user where profession= "软件工程' and age = 31;（部分失效）
explain select * from tb_user where profession='软件工程";（部分失效）
explain select * from tb_user where age = 31 and status = '0';（全失效）
explain select * from tb_user where profession= "软件工程 and status = '0'; （部分失效）
explain select * from tb_user where status = '0';（全失效）

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效

explain select * from tb_user where profession= '软件工程' and age > 30 and status = '0';（status 失效）

explain select * from tb_user where profession= '软件工程' and age >= 30 and status = '0';（没有失效）

索引列运算

不要在索引列上进行运算操作,索引将失效。

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

字符串不加引号

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

explain select * from tb_user where profession='软件工程' and age = 31 and status = 0;

explain select * from tb_user where phone = 17799990015;

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配,索引失效。

explain select * from tb_user where profession like '软件%';（没有失效）

explain select* from tb_user where profession like '%工程';（失效）

explain select * from tb_user where profession like '%工% ';（失效）

or连接的条件

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。两边都有索引就不会失效

explain select* from tb_user where id= 10 or age = 23;（全失效）
explain select * from tb_user where phone = '17799990017' or age = 23;（全失效）

数据分布影响

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

select *from tb_user where phone >='17799990005';
select * from tb_user where phone >= '17799990015';

极少数据走索引，大多数放弃索引走全表检索

SQL提示

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

use index:

explain select * from tb_user use index(idx_user_pro) where profession='软件工程';

ignore index:

explain select * from tb_user ignore index(idx_user_pro) where profession= "软件工程;

force index:

explain select *from tb_user force index(idx_user_pro) where profession= "软件工程';

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中能全部能够列)，减少select *。

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

1. select * from tb_user where id = 2 ：id是主键，为聚集索引，此语句可以直接走聚集索引
2. select id, name from tb_user where name =‘Arm’：id是主键，为聚集索引。name是辅助索引，此语句走辅助索引，发现select 后面需要的列都能在这找到，就不需要走聚集索引
3. select id,name,gender from tb_user where name =‘Arm’：id是主键，为聚集索引。name是辅助索引，gender不是索引，先走name所在的辅助索引（二级索引），发现select 后面需要的gender列不能在这找到，就需要走聚集索引，因为聚集索引包含所有的列，如果name和gender是联合索引，就不需要走聚集索引

前缀索引

当字段类型为字符串(varchar， text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘lO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

1. 语法

create index idx_xxoxx on table_name(column(n));

2. 前缀长度

   可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email, 1,5)) / count(*) from tb_user ;( 越接近1 distinct substring(email, 1,5)  越好)