mysql

来自视频学习：黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1Kr4y1i7ru?p=153&spm_id_from=pageDriver

一、基础

1、关于注释

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

2、数据库语言sql分类

a、DDL（data definition language）：数据定义语言，用来定义数据库对象（数据库、表、字段）
b、DML（data manipulation language）：数据操作语言，用来对数据库表中的数据进行增删改
c、DQL（data query language）：数据查询语言，用来查询数据库表中的记录
d、DCL（data control language）：数据控制语言，用来创建数据库用户、控制数据库的访问权限

3、DDL语句

a、show databases;  #查询所有数据库
b、select datadese(); #查询当前的数据库
c、create database test_database; #创建数据库test_database（最简洁的创建数据库）
d、create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]; #[]中的为可选项
    eg1：create database if not exists test1 default charset utf8mb4; 
        数据库字符集不建议使用UTF8，因为UTF8一个位只有三个字节，但是有些字符是占4个字节的，所以用UTF8MB4（它是占四个字节的）
e、drop database [if exists] 数据库名;  #删除数据库，[]中的为可选项
f、use 数据库名;  #使用数据库
g、show tables; #查看当前数据库的所有表
h、creat table 表名(
    字段1 类型 [COMMENT '含义'],
    字段2 类型 [COMMENT '含义'],
    ......
    字段n 类型 [COMMENT '含义']) [comment '表含义'];   #创建表
        eg：create table user (
            id int comment '编号',
            name varchar(50) COMMENT '名字',
            age int comment '年龄'
              ) comment '用户信息表';   
i、desc 表名;  #查看表结构
j、show CREATE TABLE 表名;  #查看表的建表语句
k、数据库数据类型

        eg: age tinyint unsigned comment '年龄'    unsigned表示没有符号
              score double(4,1) comment '分数'   代表整体4位长度，小数占一位

 l、alter table 表名 add 字段1 类型 [COMMENT '含义'] [约束];  #向表中添加字段
    eg：alter table user add score INTEGER comment '分数' AFTER age; 
m、alter table 表名 字段名 类型;  #修改指定字段的字段类型
n、alter table 表名 新字段 旧字段 类型 [comment '含义'] [约束];  #修改字段名和字段类型
o、alter table 表名 drop 字段名;  #删除指定字段
p、alter table 表名 rename to 新表名;  #修改表名
q、drop table [if exists] 表名;  #删除指定表
r、truncate table 表名; #删除指定表，并重新创建该表（数据会被删除）

4、DML语句

a、insert into 表名(字段名1,字段名2,...) values(值1,值2,...);  #给指定字段添加数据，其他数据位默认值
b、insert into 表名 values(值1,值2,...);  #给全部字段添加数据
c、insert into 表名(字段名1,字段名2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...),...;
     insert into 表名 values(值1,值2,...),(值1,值2,...),(值1,值2,...),...;
     #批量添加数据
a、b、c注意点：插入数据时，指定的字段顺序要与值一一对应；2、字符串和日期类型数据要包含引号；3、插入的数据大小应该在规定的范围内 
d、update 表名 set 字段名1=值1,字段名2=值2,... [where 条件];  #修改指定字段的值；如果没有条件语句，会修改整张表的数据
e、delete from 表名 [where 条件];  #删除表数据；如果没有条件语句，将会删除整个表的值

5、DQL语句

a、关键字

b、select * from 表名;  #查询表，显示出所有字段（生产中尽量不要写*，不直观而且影响效率）
c、select 字段1, 字段2... from 表名;  #查询表，结果显示指定字段
d、select 字段1 [as] 别名1, 字段2 [as] 别名2... from 表名;  #查询表，结果显示指定字段并显示位字段别名(as可以不要)
e、select distinct 字段1 [as] 别名1, 字段2 [as] 别名2  from 表名;  #去除重复记录，可以一个或者多个字段去重，注意字段一定要取别名
f、select 字段列表 from 表名 where 条件列表;  #条件查询
     条件使用的关键字：

eg:select * from user where score is null;  #查询score为空
    select * from user where score is not null;   #查询score不为空
    select * from user where id between 1 and 2;  #查询id在1和2之间的数据，包括1和2
    select * from user where  id in (0,1,3);
    select * from user where  name like '___';  #查询name为3个字符的数据（‘’中间时三个下划线）
    select * from user where  name like '%4';
    select * from user where  name like '__4';  #查询name有三个字符，并且以4结尾的
g、聚合函数：将一列数据作为一个整体，进行纵向计算。
     常见的聚合函数：count统计数量；max最大值；min最小值；avg平均值；sum求和
     语法：select 聚合函数(字段列表) from 表名 [where 条件列表];  #先条件列表再聚合函数，注：所有的null值不参与聚合函数的运算
     eg:select count(*) from user;  
            select count(score) from user;  
            select avg(age) from user where id > 2;
            select max(id) from user;
            select min(id) from user;
            select sum(id) from user;
h、分组查询：select 字段列表 from 表名 [where 条件列表] group by 分组字段名 [having 分组后过滤条件];
     where与having的区别：where是分组之前对整个表的过滤，而having是对分组之后的结果进行过滤；where不能对聚合函数进行判断，而having可以
     eg:select gender, count(*) from user group by gender;  #统计不同性别的数量。
            select gender, avg(age) from user group by gender;  #计算不同性别的平均年龄
            select gender, avg(age) age_avg from user where id >=0 group by gender having age_avg > 16; #统计ID大于等于0，不同性别的年龄平均值大于16的数据
     注意事项：group by不能单独使用，要配上聚合函数
                       执行顺序：where > 聚合函数 > having
                       分组之后。查询的字段一般为聚合函数和分组字段，查询或显示其他字段没有任何意义
i、排序查询：select 字段排序 from 表名 order by 字段1 排序方式1, 字段2 排序方式2...;
              排序方式：ASC升序（默认值）、DESC降序
        注：如果是多字段排序，当地一个字段值相同时，才会根据第二个字段进行排序
        eg: select * from user order by age desc, id asc;
j、分页查询：select 字段列表 from 表名 limit 起始索引, 查询记录数;
        注：起始索引从0开始，起始索引=（查询页码-1）* 每页显示记录数；
              分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit；
              省略起始索引的写法：limit 10，表示默认从0开始查起
                   eg:这里有个问题，用这种语法不生效，要用以下这种语法：limit 显示数量 offset 记录所在行（从0开始算）
             select * from user limit 2 OFFSET 3;
k、DQL的执行顺序：from --> where --> group by --> having --> select --> order by --> limit 

6、DCL语句

a、查询用户：每个数据库服务器默认都有mysql数据库，该库中的user表就是所有用户信息；user表中host(主机名) + user(用户名)决定了一个用户
b、创建用户：create user '用户名'@'主机名' identified by '密码';
    eg：create user 'xiaoling'@'localhost' IDENTIFIED by '123456';  #只能在本机上访问该数据库
           create user '用户名'@'%' IDENTIFIED by '密码';  #该命令可以在任意服务器上访问该数据库
           创建完后就可以取控制台用'mysql -u xiaoling -p'命令，然后用密码登录即可，以上命令创建出来的权限只能看到information_schema库
c、修改用户密码：alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
    eg：alter user 'xl1'@'%' IDENTIFIED with mysql_native_password by '654321';
d、删除用户：drop user '用户名'@'主机名';
e、权限控制：mysql中的常用权限类型

 f、查询权限：show grants for '用户名'@'主机名';
    eg:show grants for 'xiaoling'@'localhost';  

 

 g、授予权限：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';  
    eg:grant all on test.user to 'xiaoling'@'localhost';
h、撤销权限：revoke 权限列表 on 数据库名.表名 from '表名'@'主机名';  
    eg:revoke all on test.`user` from 'xiaoling'@'localhost';
注：多个权限之间用逗号分隔；数据库名和表名可以用*代替，表示所有数据库或表

7、函数

a、概念：是指一段可以直接被另一段程序调用的程序或者代码
b、字符串函数，常见的字符串函数：

    eg:select concat('xiaol','ling');  #xiaolling
        select lower('xiaOLIng');  #xiaoling
        select upper('xiaOLIng') upper;  #XIAOLING  还可以取别名
        select lpad('xl',5,'*');  #***xl
        select rpad('xl',5,'#');  #xl###
        select trim('  x l  ');  #x l
        select substring('hallo sl',2,4);  #allo
         实际运用：update user set name = lpad(name, 4, '0') where id = 1; #将id为1的记录的name前面补0
c、数值函数，常见的数值函数： 

 eg:select ceil(1.1);  #2
        select ceil(1.5);  #2
        select floor(1.9);  #1
        select mod(3,2);  #1
        select mod(3,4);  #3
        select rand();  #0.5965218773079308
        select round(2.456,1);  #2.5
        select rpad(round(rand()*1000000,0),6,0);   #生成一个6位数的随机验证码，先生成0-1的随机数，然后变成6位整数，但是整数可能前面存在0的情况，所以要在右侧补0（左侧也行）

d、日期函数，常见的有：

     eg:select curdate();  #2022-03-06
        select curtime();  #12:31:06
        select now();  #2022-03-06 12:31:23
        select year(now());  #2022
        select month(now());  #3
        select day(now());  #6
        select date_add(now(), interval 70 day);  #2022-05-15 12:33:26
        select date_add(now(), interval 70 month);  #2028-01-06 12:33:41
        select date_add(now(), interval 70 year);  #2092-03-06 12:33:53
        select datediff('2021-11-1', '2022-11-1');  #-365
        实际运用：select datediff('2021-11-1', '2022-11-1') diff, id from user ORDER BY diff;  #函数可以直接用在select后，也可用来排序

e、流程函数，常见的有

     eg:select if(1=2, 'ok', 'error');  #error
        select if(1=1, 'ok', 'error');  #ok
        select if(true, 'ok', 'error');  #ok
        select ifnull('ok', 'default');  #ok
        select ifnull('', 'default');  #ok
        select ifnull(null, 'default');  #default
        select id, case 
                when gender = 'X' THEN '女' 
                when gender = 'Y' THEN '男' 
                ELSE '不男不女' end gender1
                from user;

8、约束

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

b、创建以下表

        create table user_info(
                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 '用户信息表';

 c、外键约束

    语法：create table 表名(字段名 数据类型, ... [constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名))； #创建时添加
                alter table 表名 add constraint 外键名称  foreign key(外键字段名) references 父表(父表列名))；  #后面再添加
                alter table 表名 drop foreign key 外键名称;   #删除外键

d、外键的删除和更新：

         语法

9、多表查询

a、多表关系：项目开发中，再进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本分为三种：一对多(多对一)、多对多、一对一

 

b、基本语法：select * from user_info, dept_info where user_info.dept_id = dept_info.id;
c、内连接查询:  查询A表与B表交集的部分
    隐式内连接：select 字段列表 from 表1, 表2 where 条件 ...;
        select * from user_info a, dept_info where a.dept_id = dept_info.id ORDER BY a.id ;
    显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件 ... where ...;
        select * from user_info a inner join dept_info b on a.dept_id = b.id;
        select * from user_info a join dept_info b on a.dept_id = b.id;
d、外连接：
    左外连接：查询左表(表1)所有数据，以及两张表交集部分的数据。select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;
        select * from user_info a LEFT JOIN dept_info b on a.dept_id = b.id;
        select * from user_info a LEFT outer JOIN dept_info b on a.dept_id = b.id;
    右外连接：查询右表(表2)所有数据，以及两张表交集部分的数据。select 字段列表 from 表1 right [outer] join 表2 on 条件 ...;
        select * from user_info a RIGHT JOIN dept_info b on a.dept_id = b.id;
        select * from user_info a RIGHT outer JOIN dept_info b on a.dept_id = b.id;
e、自连接：当前表与自身的连接查询，自连接必须使用表别名。语法与其他多表查询一致
        注：如果给表取了别名，就直接用别名，不能再用原表名
f、联合查询--union，union all：把多次查询的结果合并起来，形成一个新的查询结果集
    语法：select 字段列表 from 表A ...    UNION [ALL] select 字段列表 from 表B ...;  #结果为A表的查询结果加上B表的查询结果
    区别：union all 会把所查询的结果全部显示；union会去重
    注：多张表的列数和字段类型都要保持一致
g、子查询：也称为嵌套查询
    eg:select * from t1 where column1 = (select cloumn2 from t2);  #子查询的外部语句可以为insert/update/delete/select
    根据子查询结果，子查询可以分为
        标量子查询（子查询结果为单个值）
        列子查询（子查询结果为一列）
        行子查询（子查询结果为一行）
        表子查询（子查询结果为多行多列）
    子查询的位置可以在WHERE/FROM/SELECT后
h、标量子查询。常用操作符：=  <>  >  >=  < <=
    eg: select * from user_info where dept_id > (select b.id from dept_info b where b.name = '信息技术部')
i、列子查询。常用操作符有：in、any、some(返回的列中满足其中一个即可)；not in(不在返回的列的值当中)；all(返回的列的所有值都要满足)
    eg:select * from user_info where dept_id in (select b.id from dept_info b where b.name in ('信息技术部', '业务部'));
         select * from user_info where dept_id = any (select b.id from dept_info b where b.name in ('信息技术部', '业务部'));
         select * from user_info where dept_id = some (select b.id from dept_info b where b.name in ('信息技术部', '业务部'));
        以上三个都是查询部门为'信息技术部'和'业务部'的用户
         select * from user_info where age >= all (select a.age from user_info a where dept_id = some (select b.id from dept_info b where b.name in ('信息技术部', '业务部')));
        查询比部门为'信息技术部'和'业务部'的用户都等且大的用户
         select * from user_info where age > any (select a.age from user_info a where dept_id = some (select b.id from dept_info b where b.name in ('信息技术部', '业务部')));
             查询比部门为'信息技术部'和'业务部'的任意一个用户年龄大的用户
    注：any/some/all需要配合=  <>  >  >=  < <=来使用
j、行子查询：常用操作符：=、<>、 in、not in（=和in是一样的效果，<>和not in是一样的效果）
    额外小知识：select * from user_info where gender = '男' and dept_id = 2;  
            select * from user_info where (gender, dept_id) = ("男",2);
            以上两种式一样的效果
    eg:select * from user_info where (gender, dept_id) = (select b.gender,b.dept_id from user_info b where b.name = '张五');
        查询与张五部门和性别一样的用户
k、表子查询。常用操作符：in
    eg：select * from user_info where (gender, dept_id) in (select b.gender,b.dept_id from user_info b where b.name in ('张五','张三'));
        查询性别和部门信息与张三和张五一致的用户
          select * from (select * from user_info where age > 5) e LEFT JOIN dept_info b on e.dept_id = b.id;
        查询年龄大于5的用户信息及他们的部门信息
l、例句
    #查询拥有用户的部门并去重（使用隐式内连接）
        select distinct a.id, a.name from dept_info a  JOIN user_info b on a.id = b.dept_id;

10、事务

a、概念：事务时一组操作的集合，它是不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作请求要么同时成功，要么同时失败

        事务流程：

         注：默认mysql的事务时自动提交的，也就是说，当执行一条DML语句时，mysql会立即隐式的提交事务

b、mysql事务操作演示

        方式一：修改默认的事务提交方式，手动执行提交或者回滚

                

         方式二：不用修改事务提交方式为手动，用start transaction或者begin

                

c、事务的四大特性
            原子性(atomicity)：事务时不可分割的最小操作单元，要么全部成功，要么全部失败
            一致性(consistency)：事务完成时，必须使所有的数据都保持一致状态（也就是保持正常执行完或者异常回滚完的状态）
            隔离性(isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行（每个事务都是隔离的，事务与事务之间不受干扰）
            持久性(durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
d、并发事务的问题。
            脏读：一个事务读到了另外一个事务还没有提交的数据
            不可重复读：一个事务先后读取同一条记录，但两次读取的数据不一致
            幻读：一个事务按照条件查询数据时，没有对应的数据，然后接着插入数据时，发现这行数据又已经存在了（在查询和插入之间，另外一个事务插入了该条数据）

e、事务的隔离级别(解决并发事务的问题)

         注：Oracle默认为read committed；

                从上到下，效率越来越低，数据安全性越来越高

                serializable原理，当不同事务操作更改同一条数据时，会进行串行模式，后面的事务要等前面的事务提交了才能继续更改

         设置隔离级别

二、进阶

1、mysql体系介绍

2、存储引擎 

 a、简介：存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎时基于表的，不是基于库的，索引存储引擎也可被称为表类型
b、创建表时，指定存储引擎：create table 表名(......) engine = innodb/MyISAM/ARCHIVE... [comment 注释];
c、查询数据库所支持的存储引擎：show engines;

d、innoDB：         上图中的innodb_file_per_table参数可以用以下语句来查看，如果为on，表示每张表都会有一个表名.ibd文件；如果不为on，表示所有表公用一个.ibd文件：

                show variables like 'innodb_file_per_table';

                 可以在存放ibd文件的目录下进入控制台，输入ibd2sdi dept_info.ibd命令查看表详细信息

        逻辑存储结构

 e、MyISAM

 

 f、memory

g、innodb、MyISAM、memory的对比（重点） 

h、存储引擎选择

 3、索引

a、简介：索引(index)是帮助mysql高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
b、优缺点：

 c、索引结构分类：

        不同存储引擎支持的索引结构的情况： d、b树

详细存储过程从以下网页的第3分钟看起

黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1Kr4y1i7ru?p=68

 e、b+tree

 详细存储过程看以下网页

黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括_哔哩哔哩_bilibilihttps://www.bilibili.com/video/BV1Kr4y1i7ru?p=69

f、mysql中的b+树

 g、hash索引

 

 h、为什么要使用b+树作为数据库的索引

i、索引分类

 j、根据存储形式进行分类

 

如果我们给name创建了一个二级索引，当执行以下语句：select * from user where name = 'kit';  就会先取二级索引找到‘kit‘对应的主键，再拿主键取聚集索引中进行查询，拿到整行数据

 k、思考题

 l、创建索引：create [unique | fulltext] index 索引名 on 表名 (列名1, 列名2....);  
    注：如果选unique代表唯一索引；如果选fulltext代表全文索引；如果都不选代表常规索引
          如果索引只有一列，称为单列索引；如果超过一列，称为联合索引或者组合索引
    eg：create unique index idx_name_age on user_info (name, age);
m、查看索引：show index from 表名;
n、删除索引：drop index 索引名 on 表名;

4、索引高级使用

a、sql执行频率：mysql客户端连接成功后，通过show [session | global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的insert、update、delete、select的防访问次数
    注：session是值当前会话，global是值全部的
    eg：show global status like 'Com_______';  #Com后面跟的是7个下划线
b、sql慢查询日志：慢查询日志记录了所有执行时间超过指定参数的所有sql语句的日志
    mysql慢查询日志默认是没有开启的，可以用show variables like 'slow_query_log';语句查询是否有开启(on为开启，off为关闭) ；需要在mysql的配置文件配置如下信息）
        注：linux版本的配置文件是/etc/my.cnf；windows版本是my.ini（我也没找着。。。）

         慢查询日志中的记录信息

 c、慢sql查询方式二：profile详情
    第一步：先查看当前数据库是否支持profile操作：select @@have_profiling;  #yes即为支持
    第二步：打开profiling操作（默认是关闭的）：set [session | global] profiling = 1;
    第三步：查询所有sql语句耗时的基本情况：show profiles;

        第四步：查看指定query_id(第三步中获取)的sql语句各个阶段的耗时情况：show profile for query query_id;

            查看指定query_id(第三步中获取)的sql语句各个阶段的cpu耗费情况：show profile cpu for query query_id;

 d、explain执行计划：explain或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序
    语法：explain/desc select语句;

        各个字段的含义：
                id:select查询的序列号，表示查询中执行select字句或者是操作表的顺序。id相同，执行顺序从上到下；id不同，值越大越先执行 

         select_type：表示select的类型，常见的取值有simple（简单表，即不使用表连接或者子查询）、primary(主查询，即外层的查询)、union（union中的第二个或者后面的查询语句）、subquery（select/where之后包含了子查询）等
        type：表示连接类型，性能由好到差的连接类型为：null(不查询表时才能达到)、system(查询系统表时才能达到)、const(用主键或唯一索引查询时可达到)、eq_ref、ref(用普通索引时可达到)、range、index(也用了索引，但是还是遍历了所有索引)、all。在写sql时，尽量往前优化，尽量不要出现all和index
        possibel_key：显示可能应用在这张表上的索引，一个或多个
        key：实际使用的索引，如果为null，表示没有使用索引
        key_len：索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
        rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
        filtered：表示返回结果的行数占需读取行数的百分比，越大越好
        extra：其他信息
    主要关注type、possibel_key、key、key_len、extra

 e、索引使用

    e.1、最左前缀法则：如果索引了多列（联合索引），要遵守最左前缀法则（查询从索引的最左列开始，并且不跳过索引中的列，如果跳过某一列，后面的字段索引会失效）。

        e.2、范围查询：联合索引中，出现范围查询（>、<）,范围查询列的右侧所有索引列失效 

        e.3、不要在索引列上运算，否则索引列会失效 

         e.3、索引为字符串时，查询如果不加字符串，索引会失效
         e.4、如果查询时索引字段模糊匹配，尾部模糊索引不会失效，头部模糊索引会失效

          e.5、用or分开的条件，只有or两边的条件都用到了索引，所有索引才会生效，否则都不会生效 

        e.6、数据分布影响：如果mysql评估走索引比全表更慢，则不使用索引（查询出来的值大于全表的一半，就会全表扫描，不走索引）
        e.7、sql提示：有一些列会参与到多个索引中，数据库会根据自己的判断来选择用哪个索引，如果向认为指定，可以用以下几种方式（是优化数据库的一个重要手段）

        e.8、尽量使用覆盖索引，减少select *。意思就是查询出的字段就在主键和索引列中。因为当找完二级索引后，已经可以拿到索引字段和主键的值了，不用再去聚合索引中回表查询 

        e.9、前缀索引：当索引字段类型为字符串时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，提高索引效率
        建立前缀索引的语法：create index 索引名 on 表名(列名(n));  #n表示将该列的前n位用来建立索引
        前缀长度的选择：可以根据索引的选择性来决定，索引选择性是索引字段去重后的记录数和数据表的总记录数的比值；索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能是最好的
        选择性的推算：
            select count(a_a) from a;  #查询a表中a_a列不为空的数目
            select count(distinct a_a) from a;  #查询a表中a_a列不为空且去重后的数目
            select count(distinct substring(a_a, 1, 5)) from a;  #查询a表中a_a列的前5位不为空且去重后的数目
            索引整列索引的选择性算法为：select count(distinct a_a) / count(*) from a;
            索引一列的前n位索引的选择性算法为：select count(distinct substring(a_a, 1, n)) / count(*) from a;
        前缀索引的索引结构：

         e.10：单列索引与联合索引的选择：再业务场景中，如果存在多个查询条件，考虑对于查询字段建立索引时，建议建立联合索引

 ​

         e.11、联合索引的结构和查询流程（创建联合索引要考虑好顺序）

 f、索引的设计原则：

 5、sql优化

 a、insert优化

b、主键优化

        数据的组织方式：在innoDB存储引擎中，表数据都是根据组件顺序存放的，这种存储方式的表称为索引组织表（index organized table IOT）

        页(这里的页是值本文档“逻辑存储结构”图中的page)：也可以为空，也可以填充一半，也可以填充100%，每页至少有两行数据(如果是一行的话就成了链表了)，根据主键排列（如果一行数据太大，会行溢出）。

        页分裂：当主键的插入顺序为乱序时，如果一页最多存3条，现在已经有4、67、100这三条数据，再存57时，就会把当前页分为两页，4、57、67页和100页，然后页中间的指针需要重新调整

        页合并：当删除一行记录时，实际上记录被没有被物理删除，只是记录被标记(flaged)为删除，并且它的空间变得允许被其他记录声明并使用，当页中删除的记录达到merge_threshold(默认页的50%),innoDB会开始寻找最靠近的页看看是否能将两个页进行合并，来优化空间

 c、order by优化：order by主要以以下两种方式进行排序

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

 explain select id, age, phone from tg_user order by phone, age;  #会走using filesort，因为违背了最左原则

 explain select id, age, phone from tg_user order by age asc, phone desc;  #会走using filesort，因为创建的索引时默认降序的

创建一个age升序，phone倒叙的索引：create index idx_user_age_pho_ad on tb_user(age asc, phone desc);

 再执行explain select id, age, phone from tg_user order by age asc, phone desc;  #会走using index

explain select id, age, phone from tg_user order by age desc, phone asc;  #会走using index，只是会反向查找

 explain select * from tg_user order by age, phone;  #会走using filesort，因为违背了覆盖索引规则

所以oder by优化可以用以下几点

 d、group by优化

 

 

        再分组操作时，可以通过筛选索引来提高效率

        分组操作时，索引的使用也是满足最左前缀法则的 

e、limit优化：当分页到很后面的数据时（如limit 8000000, 10），需要mysql排序前80000010的数据，又仅仅返回后10条，其他记录丢弃，查询排序的代价非常大。

        优化思路：一般分页查询时，用覆盖索引能够较好的提高性能，可以通过覆盖索引加子查询的形式进行优化

f、count(*)优化    
    myISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高，但是不能加条件；
    innoDB没有以上这种操作，它执行count(*)时，需要把数据一行一行的从引擎里面读出来，然后累积计数。索引目前没有好的优化方式，要么只能自己累计表数目
    count()是一个聚合函数，对于返回的结果集，一行行的判断，如果count函数的参数不是null，累计值就加1，否则不加，最后返回累计值。以下是count的几种用法 

 g、update优化：innoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引再执行sql时不能失效，否则会从行锁升级为表锁