1.索引索引可以显著降低查询数据库的耗时索引本质就是一种数据结构B树数据交互MySQL是处于应用层的程序逻辑上MySQL和磁盘中的数据库文件进行curd操作实际上MySQL只和os的文件缓冲区进行io操作而os又和磁盘进行io操作申请对应的文件内容到文件缓冲区先申请大块空间bufferpool用来和文件缓冲区交互提高page查询效率引入页间目录与页内目录页内目录在一个page内部设置目录将页内数据分割为多段从而查找的时候不用始终从索引为1的起始位置遍历查找而是可以就近选择不同的起始点页间目录让部分page只保存其他page的地址信息让他变为目录page以后的查找都可以实现页间的目录级别查找不需要线性遍历所有的pageeg图中顶部的可以叫二级目录page他指向一级目录page而一级目录page再指向实际存储了数据的page从而让page查询具有目录结构注意1.只有实际存储了数据的page节点之间用链表连接起来2.我们进行对应表的查找不是将所有的页都加载到内存中而是根据目录查询以页为单位逐渐加载需要经过的路上page节点以及最终存储数据的page节点3.由于数据库的存储有两大要求范围查找修改以及查询效率io次数所以我们淘汰了线性的链表结构树结构较高的AVL树和红黑树无法范围修改的哈希疑问1为什么向一个具有主键的表中插入数据发现数据自动排序因为需要对主键索引值进行排序从而方便引入页内目录提高页内page查找效率疑问2如何理解MySQL中的page概念他是经过class page管理起来的一次申请一个page对象相当于是一个预加载机制提高计算机效率聚簇索引VS非聚簇索引B树结构中将叶子节点的数据和叶子节点对象本身分离存储的索引叫非聚簇索引反之将数据和叶子page节点存储在一起就是聚簇索引1.1主键索引查看表中索引show index form 表名;删除表中主键索引alter table 表名 drop primary key;创建主键索引创建完表之后alter table 表名 add primary key(id);注意创建了主键之后innodb引擎会自动创建出主键索引1.2唯一键索引创建唯一键索引alter table 表名 add unique(name);删除唯一键索引alter table 表名 drop index 唯一键索引字段名在进行除主键外的索引删除的时候不能和主键一样写drop unique key因为唯一键索引不是唯一的一个表可以有多个唯一键索引只能有一个主键索引1.3普通索引创建普通索引create index 索引名 on 表名(字段名);删除普通索引alter table 表名 drop index 索引名;索引创建规范1.频繁作为查询条件的字段应该创建索引2.唯一性太差的字段不适合作为索引例如性别3.频繁变动的字段不适合创建索引4.不会作为筛选条件的字段不适合2.事务事务就是一组DML语句组成这些语句在逻辑上存在相关性这一组DML语句要么全部成功要么全部 失败是一个整体事务使用场景主要用于处理操作量大复杂度高的数据事务需要满足的四个特性1原子性一个事物在执行时要么全部执行成功要么全部执行失败不会存在中间状态。一旦执行过程出错这个事务就会回滚就像没有执行过任何操作一样2一致性事务开始前和结束后数据库的完整性没有被破坏一直处于既定规则下一致性通过其他三个特性进行技术支持技术上的一致性可以由数据库自身保证业务上的一致性则由数据库用户来保证最终才能实现实际上的一致性3隔离性隔离性可以保证数据库有并发执行事务时不会由于交叉执行导致数据不一致事务隔离会分成不同级别4持久性事务处理结束后可以保证数据永久修改2.1事务的提交方式事务的提交方式分两种1.自动提交2.手动提交相关命令1查看提交方式show variables like autocommit;2利用set启动或关闭事务自动提交set autocommit0;//关闭 set autocommit1;//启动2.2事务的案例演示1正常演示1.启动事务start transaction;//语句1 begin;//语句22.插入数据保存点insert into table values (zhangsan);//插入数据 savepoint s1;//保存事物节点3.定向回滚/全事物回滚rollback;//全回滚 rollback to s1;//定向回滚s14.提交commit;即使设置了autocommit手动开启的事务也需要手动commit当我们设置了保存点之后如果需要将前面的数据回滚掉可以使用定向回滚如果没有保存事物节点我们只能全回滚在事务确认正确的时候可以提交到数据库从而事物就具有了持久性2未commit时客户端崩溃此时我们没commit的所有事物都会被回滚从而保证事物具有原子性3commit成功客户端崩溃此时数据不会被回滚因为提交之后数据就具有了持久性疑问那么autocommit到底有什么用在innoDB引擎下其实我们在mysql中使用的单SQL语句都是一个非手动启动事务当我们的autocommit启动后单个SQL语句就可以直接持久性修改数据库若autocommit关闭单个SQL语句不手动commit就无法保存会自动回滚疑问原子性和持久性我们理解了隔离性怎么体现在事务场景中隔离是必要的主要隔离的是多个并发执行的事务让并发事务不会互相影响体现隔离性2.3隔离级别1读未提交(read uncommitted)2读提交(read committed)3可重复性(repeatable read)4串行化(serializable)查看与设置隔离性查看select global.tx_isolation;//查看全局隔离级别 select session.tx_isolation;//查看当前会话的全局隔离级别global的级别是所有会话都默认的隔离级别若我们启动mysql新会话后不特别设置session的隔离级别他会直接拷贝global的隔离级别若我们特别设置当前会话的隔离级别即使global级别改变也不会影响session隔离级别设置set [session/global] transaction isolation level 隔离级别;1读未提交(read uncommitted)在读写并发场景且隔离级别为该级别下所有事务可以看到其他事务没有提交的执行结果相当于没有任何隔离性eg事务1需要插入三条数据由于设置了读未提交的隔离级别另一个用户在读取表的时候可以看到事务1逐条插入的过程这丧失了事务的原子性与隔离性此时用户读到的数据属于脏数据这个读取事务未提交的数据的过程叫脏读2读提交(read committed)这是大多数数据库默认的隔离级别该级别下一个事务只能看到其他已经提交的事务所做的改变由于它的该特性在一个运行的事务内部会变得不可重复读取高并发场景下会存在多次commit从而让查询数据多次变化eg我们需要根据同学成绩进行奖品发放tom同学原本的分数是60但是他发现试卷有问题和老师说将分数改为了71我们有两个事务在运行事务1负责查询各个分数段的学生人数事务2负责更新tom的成绩如果事务1在执行的过程中事务2commit了在读提交条件下事务1就被影响了可能出现tom即属于60~69区间又属于70~79区间这就是不可重复读问题3可重复读(repeatable read)这是MySQL默认隔离级别在该隔离级别下读取事务可以重复读取数据可以看到同样的东西但是可能存在幻读问题幻读读取事务在多次重复读取过程中出现新增数据数据条数增加大部分数据库在RR隔离级别下对于insert的待插入数据没有实现隔离仍然会影响正在读取的事务因为RR隔离级别是利用锁完成的而原先没有的数据的锁并没有被占用。MySQL通过其他方式解决了这个问题4串行化(serializable)事务最高隔离级别给所有事务加上共享锁强制事务排序完全保证了事务的隔离但是由于所有事务争夺同一个锁所以效率极低现实中几乎不会使用3.视图视图是一个虚拟表他是由查询定义的1创建视图create view 视图名 as select语句;2删除视图drop view 视图名;特性1.基表和视图修改任意一方都会对另一方的数据产生影响2.必须命名唯一3.可以和表一起使用4.用户管理4.1用户创建mysql的用户信息都存储在user表中host表示用户可以从哪个主机登录localhost表示允许本机登录1创建用户create user 用户名主机名/ip identified by 密码;ip/主机名填写处可以使用%代替从而允许用户从任意主机登录2删除用户drop user 用户名主机名;3修改密码set passwordpassword(新的密码);//修改用户自身密码 set password for 用户名主机名password(新的密码);//root修改指定用户密码其实修改密码也可以使用普通SQL语句查询user表中指定用户的数据摘要利用password()对需要改成的密码进行加密赋值给指定用户摘要4.2用户权限1给用户授权grant 权限列表 on 库.用户名 to 用户名主机名/ip [identified by 用户密码];若对应用户存在就相当于修改密码若不存在就创建用户权限列表全授权all在库.用户名下只有‘ . ’表示MySQL所有数据库下的所有对象 库. 表示特定数据库下的所有对象2查看指定用户现有权限show grants for 用户名主机名/ip;3回收权限revoke 权限列表 on 库.用户名 from 用户名主机名/ip;5.语言级别访问数据库5.1相关接口1初始化MySQLMYSQL* mysql_init(MYSQL* mysql);初始化完毕之后需要先链接数据库才能进行后续操作一般初始化时mysql_init(nullptr);2建立数据库连接MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port, const char *unix_socket, unsigned long clientflag);mysql初始化后返回的mysql指针host登录地址ipuser用户名passwd用户密码db连接数据库名port端口号unix_socket:Unix域套接字文件路径仅本地连接clientflag客户端标志3下发SQL指令int mysql_query(MYSQL *mysql, const char *q);参数1mysql指针包含各种mysql相关信息参数2要执行的sql语句4获取执行结果MYSQL_RES *mysql_store_result(MYSQL *mysql);返回值MYSQL_RES可以看成是char**数组他所保存的数据每一个都是表示一行的char*数组从而表示出整个表的所有数据5获取结果行数my_ulonglong mysql_num_rows(MYSQL_RES *res);从获取的执行结果中进行行数提取6获取结果列数unsigned int mysql_num_fields(MYSQL_RES *res);7获取所有字段的元数据MYSQL_FIELD *mysql_fetch_fields(MYSQL_RES *res);元数据举例8获取结果内容MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);MYSQL_ROW也是char**类型和前面的MYSQL_RES类似9关闭mysql链接void mysql_close(MYSQL *sock);