MySQL高级进阶三

MySQL高级进阶

索引

这里我们先创建一个有量级的表数据，否则体现不出来索引

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号',
  `nick_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户昵称',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '用户邮箱',
  `sex` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）',
  `avatar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '头像地址',
  `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '密码',
  `login_ip` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '最后登录IP',
  `login_date` datetime NULL DEFAULT NULL COMMENT '最后登录时间',
   `text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '测试文本',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;

• nodejs脚本

// 使用 Mock
var Mock = require('mockjs')
// mysql8.0需要执行下边的sql，否则nodejs不支持
// ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xiaobo';
var mysql = require('mysql');
// 定义连接池
const pool = mysql.createPool({
    host: "127.0.0.1", // 主机地址
    port: 3306,
    database: "test", // 数据库名字
    user: "root", // 连接数据库的用户名
    password: "xiaobo", // 连接数据库密码
    connectionLimit: 30, // 连接池最大连接数
    multipleStatements: true // 允许执行多条sql语句
})


function insert(connection) {
    let content = '';
    for (var i = 0; i < 100; i++) {
        // 使用mock.js模拟数据
        var user = Mock.mock({
            userName: Mock.mock('@name(true)'),
            nickName: Mock.mock('@cname()'),
            email: Mock.mock('@email()'),
            sex: Math.random() > 0.5 ? '男' : '女',
            loginIp: Mock.mock('@ip()'),
            loginDate: Mock.mock('@datetime()'),
            password: Mock.mock('@word(5, 10)'),
            avatar: Mock.mock('@url()'),
            text: Mock.mock('@cparagraph(30)'),
        });

        // 每次存入1000条
        let insertData = '('
        for (key in user) {
            insertData += '\'' + user[key] + '\'' + ','
        }
        insertData = insertData.substring(0, insertData.length - 1) + ')';
        content += insertData + ',';
    }

    content = content.substring(0, content.length - 1);
    let sql = `insert into user (user_name,
        nick_name,email,sex,login_ip,login_date,password,avatar,text) values
        ${content}`;
    connection.query(sql);
    connection.release();
}
// 循环60000万次，总计能插入600万条
for (let i = 0; i < 60000; i++)
 {
    
    pool.getConnection(function (err, connection) {
        // 代码
        insert(connection)
        console.log('第'+i+'条sql执行成功！')
    }) 
}

查询对比

SELECT COUNT(*) FROM `user`

SELECT * FROM `user` WHERE user_id = 1097

Innodb的b+树数据结构

innodb中的数据是以行为单位，存在一个个大小为16k的页中，b+树的作用就是按照一个组织形式，将所有的页组织关联起来

B树

B表示balance(平衡)，B树是一种多路自平衡的搜索树，它类似普通的平衡二叉树，不同的一点是B树允许每个节点有更多的子节点

B树的特点

所有键值分布在整颗树中

任何一个关键字出现且只出现在一个结点中

搜索有可能在非叶子结点结束

在关键字全集内做一次查找，性能接近二分查找

B+树

一种多路搜索树

B树 VS B+树

• 相同点

都是一种多路搜索树

• 不同点

B+树所有关键字存储在叶子节点

B+树为所有叶子结点增加了一个双向指针

选型缘由

为什么选择b树或b+树

• mysql数据模型更适合用这类数据结构，一条数据中通常包含id和其他列数据，我们可以很轻松的根据id组织一颗B+树

• innodb使用页保存数据，一页（16k）b+树中的每个节点都是一页数据

为什么选择B+树？

• 相同的空间，不存放整行数据就能存更多的id，b+树能使每个节点能检索的范围更大、更精确，极大的减少了I/O操作，保证b+树的层高较低，通常3到4层的层高就能支持百万级别的访问

• Mysql是一种关系型数据库，区间访问是很常见的一种情况，B+树叶节点增加的双向指针，加强了区间访问性，可使用在范围区间查询的情况

索引发现

当我们使用非id列作为条件是发现会很慢，所以我们构建主节点按照属性排序，在叶子节点中只保存id而不保存全部数据，查到id后再回表（回到原来的结构中根据id进行查询）查询整条记录，这就是创建的索引

索引的分类和创建

聚簇索引

主键和数据共存的索引被称之为聚簇索引，同时聚簇索引只有在innodb引擎中才存在，而在myIsam中是不存在的

聚簇索引默认使用主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的列代替。如果没有这样的列，InnoDB会隐式定义一个主键rowid来作为聚簇索引的列

非聚簇索引

其他列+主键建立的索引，可以称为非聚簇索引/辅助索引/二级索引

MyIsam使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助列。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树

如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyIsam占优势些，因为索引所占空间小，这些操作是需要在内存中完成的

主键为什么建议使用自增id?

• 主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，出现页分裂，导致索引树调整复杂度变大，消耗更多的时间和资源

• 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的id，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高

普通索引（normal）

创建索引

create index idx_user_name on user(user_name);

创建索引很费时间

删除索引

drop index idx_user_name on user; 

修改表的方式添加索引

alter table user add index idx_email (email);

创建表时同时创建索引

create table tbl_name(
    tid int,
    tname varchar(20),
    gender varchar(1),
    index [索引的名字] (字段) 
)

唯一索引（UNIQUE）

索引列的值不能重复

创建表的同时创建索引

create table tbl_name(
    tid int,
    tname varchar(20),
    gender varchar(1),
    unique index unique_index_tname (tname)
)

单独创建索引

create unique index idx_email on user(email);

修改的方式创建索引

ALTER table 表名 ADD UNIQUE [索引名字] (字段)

唯一索引 VS 主键

• 唯一索引列允许空值，而主键列不允许为空值

• 主键列在创建时，已经默认为非空值 + 唯一索引了

• 主键可以被其他表引用为外键，而唯一索引不能

• 一个表最多只能创建一个主键，但可以创建多个唯一索引

• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等

唯一索引 VS 唯一约束

• 唯一约束和唯一索引，都可以实现列数据的唯一，列值可以为null

• 创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据唯一

• 创建一个唯一索引，这个索引就是独立的索引，可以单独删除

• 如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束

多个二级索引的组合

mysql在执行查询语句的时候一般只会使用一个索引，除非是使用or连接的两个索引列会产生索引合并

以三个索引，三个字段分别为品牌、价格、销量举例

• 检索品牌为小米的手机

通过品牌索引检索出所有小米的商品id，回表查询，得到结果，会使用一个索引

• 检索名称为小米，价格在一千到三千之间的手机

通过品牌索引检索出所有小米的商品id

直接回表扫描，根据剩余条件检索结果

只会使用第一个索引

• 我们要检索名称为小米，价格为6800，且销量在50以上的手机

通过品牌索引检索出所有小米的商品id

直接回表扫描，根据剩余条件检索结果

只会使用第一个索引

• 检索名称为小米或名称为华为的手机

通过【品牌索引】检索出所有阿玛尼的商品id，得到结果

or相当于一个in关键字，会使用一个索引

• 检索名称为小米或价格大于8000的手机

通过品牌索引检索出所有小米的商品id

通过价格索引检索出价格大于8000的商品id，和上一步的结果进行并集，得到结果

这个过程叫索引合并，当检索条件有or但是所有的条件都有索引时，索引不失效，可以走两个索引

• 检索名称为小米，且价格大于8000，且产地（该列无索引）在大连的手机

通过品牌索引检索出所有小米的商品id

直接回表扫描，根据剩余条件检索结果

只会使用第一个索引

• 检索名称为小米或价格大于8000，或产地（该列无索引）在大连的手机

关联条件是or，无索引，发生全表扫描，索引失效

复合索引/联合索引

当查询语句中包含多个查询条件，且查询的顺序基本保持一致时使用

• 按照A列、B列、C列进行查询时

alert table 表名 add 索引名 table (a,b,c) 

复合索引可以想象成字典目录，你看假如目录都是四字成语 你查第一个字有多少页，然后前两个字又有多少页，你推理一下

品牌	价格	销量	id
xiaomi	6800	35	13,24,76
xiaomi	6900	35	1,23,74
xiaomi	7000	100	15,21,26
xiaomi	7100	50	73,54,46
huawei	8000	100	243,224,766
apple	9000	110	1123,212,126
apple	9500	120	1633,1234,76123
apple	10000	130	13453,21324,3276
sanxing	9000	150	13745,22344,2136

• 检索名称为小米的手机

通过品牌索引检索出所有小米的商品id，回表查询，得到结果

使用第一部分索引

• 检索名称为小米，价格在七千到八千之间的手机

通过品牌索引检索出所有小米的叶子节点

在满足上一步条件的叶子节点中查询价格在七千到八千之间的手机的列，查询出对应的id，回表查询列数据

使用复合索引的两个部分

• 检索名称为小米或价格大于8000的手机

优化器发现我们并没有一个价格列的单独的二级索引，此时要查询价格大于8000的手机，必须进行全表扫描

查询的条件中没有复合索引的第一部分，索引直接失效，全表扫描

• 检索名称为小米，且价格大于7000，且产地（该列无索引）在北京的手机

通过品牌索引检索出所有小米的叶子节点

在满足上一步条件的叶子节点中查询价格大于7000元的小米的叶子节点

因为产地列无索引，但是and的关系，我们只需要将上一步得到的结果回表查询，在这个很小的范围内，检索产地是不是北京即可

使用复合索引的两个部分

• 检索名称为小米和apple之间，价格为在七千到九千的手机

通过品牌索引检索出所有小米和apple的所有叶子节点

快速定位价格的范围，但是发现一个问题，由于第一步不是等值查询，会导致后边的结果不连续，必须对上一步的结果全部遍历，拿到对应的结果

最左前缀原则

• mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立（a,b,c,d）顺序的联合索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

• =和in可以乱序，比如a = 1 and b < 2 and c = 3 ，咱们建立的索引就可以是（a,c,b）或者（c,a,b）

全文索引（FULLTEXT）

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引

创建表时创建全文索引

create table ydlclass_user (    
    ..   
    FULLTEXT KEY fulltext_text(text)  
) 

单独创建全文索引

create fulltext index fulltext_text  on ydlclass_user(text);

修改的方式创建全文索引

alter table ydlclass_user add fulltext index fulltext_text(text);

删除全文索引

drop index fulltext index on user;

全文检索的语法

select * from user where match(text) against('人民万岁');

hash索引

• hash是一种key-value形式的数据结构。实现一般是数组+链表的结构，通过hash函数计算出key在数组中的位置，然后如果出现hash冲突就通过链表来解决。系统使用HashMap来构建热点数据缓存，存取效率很好

• 即使是相近的key，hash的取值也完全没有规律，索引hash索引不支持范围查询

• hash索引存储的是hash值和行指针，所以通过hash索引查询数据需要进行两次查询,首先查询行的位置，然后找到具体的数据

• hash索引查询数据的前提就是计算hash值，也就是要求key为一个能准确指向一条数据的key，所以对于like等一类的匹配查询是不支持的

• 只要是只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引

将表的数据加载到内存

insert into hash_user select * from user where user_id < 2000000;

mysql配置文件中可对其进行内存大小的配置

tmp_table_size = 4096M
max_heap_table_size = 4096M

空间索引（SPATIAL）

在地理位置领域使用的一种索引

正确的开始 微小的长进 然后持续 嘿 我是小博 带你一起看我目之所及的世界……