mysql相关-再总结

索引是是什么?

索引是一种特殊的文件,在mysql-data下面有frm(表结构)、myd(表数据)、myi(表索引)几个文件,这个特殊的文件存储了对应表行数据的引用,通过这个引用可以快速的检索到想要的行数据,但是也需要系统给他开辟一定的存储空间去存储这个结构,当表数据进行更新时,也需要去维护这个索引结构,所以说索引并不是创建的越多越好,但是当数据量大,且对于那种查询多更新少的表数据,使用索引就能大大的提高查询性能。之所以有这么高得性能是因为其底层数据结构的特殊性。我知道的索引结构有btree、b+tree、hash等,mysql默认使用的是b+tree作为索引的底层数据结构。

  • 索引分类
    单值索引、唯一索引、复合索引

使用索引为什么查询快?

首先我们知道,mysql的基本存储结构是页,所有的记录都是在页里面的,页包含文件头、页头、上界、用户记录、页目录等,所有的行记录都是存储在上界和用户记录中的,一页总共16kb。**各个数据页可以组成一个双向链表,每页中的记录又可以组成一个单向链表,**每个数据页都会为存储的记录生成一个页目录,在通过主键查找记录时,可以在页目录中使用二分法快速的定位大到应的槽,然后遍历槽对应的记录即可快速的获得指定记录,而使用其他非主键作为搜索条件,就只能从最小记录开始遍历单链表中的记录。
使用了索引之后,索引其实就是把无序的数据变的相对有序。
没有用索引我们是需要遍历双向链表来定位对应的页,然后在遍历单向链表的数据,时间复杂度是On(n)的,现在通过“目录”就可以很快地定位到对应的页上了!(二分查找,时间复杂度近似为O(logn))。其实底层结构就是B+树,B+树作为树的一种实现,能够让我们很快地查找出对应的记录。

BTree:
一个m叉树,每个节点最多包含m个孩子
除根节点、叶子节点,每个节点最少有ceil(m/2)个孩子
每个非叶子节点由n个key和n+1个指针组成,其中n的范围【ceil(m/2)-1 , m-1】

B+Tree:
每个节点n个key,n个指针
B+Tree只有叶子节点保存key信息,查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。
所有的叶子指针通过链指针指向下一个叶子节点,提高区间访问的性能。

hash:
基于hash表实现,对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码(hash code),对于hash相同的,采用链表的方式解决冲突。类似于hashmap。因为索引的结构是十分紧凑的,所以hash索引的查询很快。

索引设计原则

  • 查询频次较高的、数据量较大的表建立索引
  • 索引字段从where中提取,挑选最常用、过滤效果最好的字段组合
  • 唯一索引区分度越高,效率也就越高
  • 尽量使用短字段索引,提升io效率
  • 复合索引中,要利用最左前缀原则提升查询效率
  • 索引数量也不能太多

使用UUID和自增id索引结构的对比

  • 自增id
    优点:自增的主键值是顺序的,所以主键页就会近乎是顺序的记录填满,提升最大填充率,不会有页的浪费。插入的行一定在原有最大数据行,所以子定位和寻址就比较快,不会再计算新行位置而做出额外的消耗。还减少也得分裂和碎片的产生。
    缺点:如果别人爬取了数据框id自增的信息,就很容易分析出业务情况。auto_increment锁会造成自增锁的抢夺,有一定的性能损失。
  • UUID或雪花id
    因为写入时乱序的,所以不得不频繁的做页分裂操作(以便为新的行分配空间,页分裂导致移动大量的数据,一次插入最少需要修改三个页以上),频繁的页分裂,页会变得稀疏并被不规则的填充,最终会导致数据会有碎片。

innoDB和Myisam索引的区别

  • innoDB使用的是聚集索引,myisam使用的是非聚集索引,
  • innoDB的主键索引存储的是主键信息和行数据,非主键索引存储主键信息和非主键索引列,所以innoDB中使用主键索引检索是非常高效的,使用非主键索引检索时尽量使用覆盖索引,避免回表操作
  • myisam的索引存储的是行数据引用,需要再次寻址才能检索到数据。

密集索引:叶子节点保存的不只是键值,还保存了位于同一行记录里的其他列的信息,由于密集索引决定了表的物理排列顺序, 一个表只有一个物理排列顺序,所以一个表只能创建一个密集索引
稀疏索引:叶子节点仅保存了键位信息以及该行数据的地址,有的稀疏索引只保存了键位信息机器主键

  • innodb使用的是密集索引,将主键组织到一颗B+tree中,行数据就存储在叶子节点上。因为innoDB的主键索引和对应的数据是保存在同一个文件当中的,所以检索的时候在加载叶子节点的主键进入内存的同时也加载了对应的数据,即若使用where id=14这样的条件查询主键,则按照B+tree的检索算法,即可查找到对应的叶子节点,并获得对应的行数据。若对稀疏索引进行条件筛选,则需要经历两个步骤,第一步:在稀疏索引的B+tree中检索该键,比如说检索Ellison,就定位到这个主键信息。第二步,也不就是使用这个主键where id=14在b+tree中再执行一遍我们B+tree的检索操作,最终在到达叶子节点获取整行的数据。
  • 而myisam使用的均为稀疏索引,对MyISAM 来说, 主键索引和其他索引没有任何区别, 都是稀疏索引 , 表数据存储在独立的地方, 表数据和索引的分开的, 索引用地址指向表数据,稀疏索引的两颗B+tree看上去没有什么不同,节点的结构完全一致,只是存储的内容不一样而已。主键索引B+tree的节点的存储了主键,辅助键索引B+tree存储辅助键,表数据存储在独立的地方,即主键和他的数据是分开存储的。这两个B+tree的叶子节点都使用一个地址指向真正的表数据,对于表数据来说这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

什么是sql注入?如何防止

攻击者把sql命令插入到表单的输入域或者是页面请求的查询字段中,欺骗服务器执行恶意的sql命令。例如:

select * from user where username=' ' and password=' ' 
select * from user where username=' ' or 1=1 -- and password =' ' 

防止:

  • 对用户输入的内容进行合法校验
  • 限制表单输入域的输入长度
  • 使用正则表达式去检查过滤用户输入的参数,或者自己写过滤方法进行检查
  • 对提取出数据进行检查(如对结果集的判断等)
  • 使用预编译语句集PrepareStatement,使用setXX的方式传值,避免攻击者使用sql命令拼接进行攻击。

事务

事务四特性

  • **原子性atomicity:(undo log实现)**一个事务中的所有操作是一个不可分割的整体,要么全部执行成功要么全部失败。
  • **一致性consistency:(通过原子性和持久性和隔离性来实现)**事务执行前后,事务的完整性保持一致,前后逻辑一致。事务执行前后,总是从一个一致性状态变为另一个一致性状态。
  • **隔离性isolation:(读写锁+MVCC实现)**各个事务间是互不干扰的,每个事务在独一的环境中进行。
  • **持久性durability:(redo log实现)**事务一旦提交,事务中的所有操作是永久持久到数据库的。

总之,ACID只是个概念,事务最终目的是要保障数据的可靠性,一致性。

事务的效果

  • 可靠性:数据库要保证更新前后数据的一致性,那么就需要知道修改前后的状态,所以就有了undo log和redo log
  • 并发处理:多个请求并发时,为了避免脏读和其他并发问题,所以要对事务的读写进行隔离,隔离程度和业务系统的场景有关,也就是隔离级别。

redo log 、undo log

  • redo log:重做日志,是用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redo log buffer)以及重做。作用:mysql为了提高性能不会吧每次的修改都同步到磁盘,而是先存到一个缓冲池中,然后使用后台线程去做缓冲池和磁盘的同步。redo log就是为了解决如果发生宕机和断电时没没来的即执行缓冲池和磁盘的同步而导致丢失已提交事务的修改信息,redo log记录已成功提交的修改信息,并会把redo log持久化到磁盘,系统重启后会读取redolog恢复最新数据。即:redolog是用来恢复数据的,用于保障已提交事务的持久化特性。
  • undo log:与redo log相反,他记录的是数据被修改之前的信息,发生错误时可以回滚,保证未提交事务的原子性。

MVCC

MVCC:(MultiVersion Concurrency Control多版本并发控制)InnoDB的 MVCC ,是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列,
一个保存了行的创建时间,一个保存了行的过期时间,当然存储的并不是实际的时间值,而是系统版本号。
主要思想:通过数据的多版本实现读写分离,从而实现不加锁读进而做到读写并行。通过undo log和read view实现。undo log :undo log 中记录某行数据的多个版本的数据。read view :用来判断当前版本数据的可见性。

事务的并发问题

  • 丢失更新:多个事务在进行对数据的更新时,会覆盖之前事务的更新操作
  • 不可重复读:多个事务读取同一数据发现两次读取的数据不一致。
  • 脏读:该事务读取到另一事务更新但是未提交的数据,并且使用了该数据。
  • 幻读:事务查询数据时,又有事务进行插入数据,导致查询结果不同。

事务隔离级别

  • 串行serializable:可以解决所有问题,但是性能最低
  • 可重复读repeatable_read:mysql默认,可解决丢失更新、脏读、可重复读
  • 读已提交read_committed:可防止丢失更新、脏读
  • 读未提交read_uncommitted:只能解决丢失更新问题,性能最好,但是一般不用,不安全

隔离级别再深究

每种级别都规定事务中的修改,那些是事务间可见的,那些是不可见的,级别越低的级别可以执行越高的并发,但是实现的复杂度以及开销也越大。

read_uncommitted这个级别下,事务还没有提交对其他事务也是可见的。读不会加任何锁,所以会读到未提交的脏数据。好处是提高并发性能。

read_committed一个事务在提交之前,对其他事务是不可见的,所以只能读到已提交的修改。innoDB使用了排他锁,读数据时不加锁而是用了MVCC。采用了读写分离机制。

repeatable_read产生不可重复读的原因是,MVCC每次select都会新生成一个版本号,所以每次读到的不是一个副本而是不同的副本。在每次select之间有其他事务更新了我们读取的数据并提交了,那就出现了不可重复读。
那么让这个版本号一致,使用读写锁,只要没释放读锁,在每次读的时候可以读到第一次读的数据,那么就可以做到每次读取到的是一样的数据

serializable

关联/连接查询

(交叉连接、内连接、全连接、外连接、联合查询)

  • 内连接:
    等值连接:ON A.id=B.id
    不等值连接:ON A.id > B.id
    自连接:SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid
  • 外连接:
    左外连接:LEFT OUTER JOIN, 以左表为主,先查询出左表,按照ON后的关联条件匹配右表,没有匹配到的用NULL填充,可以简写成LEFT JOIN
    右外连接:RIGHT OUTER JOIN, 以右表为主,先查询出右表,按照ON后的关联条件匹配左表,没有匹配到的用NULL填充,可以简写成RIGHT JOIN

简谈Mysql的锁

Mysql中锁(按锁粒度分):表锁、行锁、页面锁
MyISAM采用表级锁(table-level locking)。
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

锁的类别
共享锁:多个事务可以共享同一把锁,都能访问数据,但是不能更新数据。又叫读锁
排他锁:排他锁就是不能与其他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,只有获取排他锁的事务是可以对数据就行读取和修改。又叫写锁
读锁会阻塞写不阻塞读,写锁即阻塞读又阻塞写

  • 行锁:粒度最细的锁,只针对当前操作的行数据加锁,可以大大减少数据库操作冲突,但是开销比较大,行锁也分为共享锁和排它锁 。特点:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
  • 表锁:表锁是粒度最大的锁,对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。特点:开销小,加锁快;不会出现死锁;锁定粒度大,发出锁冲突的概率最高,并发度最低。
  • 页面锁:页面锁粒度介于行级锁和表锁之间。所以取了折衷的页级,一次锁定相邻的一组记录。 特点:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

sql优化

几个方面:可以做读写分离,做分库分表、

  • 创建表时
    1. 长度能满足时,尽量使用小类型:如tinyint、samllint,而不直接使用int;字符串长度确定时,尽量使用char类型;varchar能满足就不要使用text;尽量采用timestamp而非datetime等。
    2. 避免空值:MySQL中字段为NULL时依然占用空间,会使索引、索引统计更加复杂。从NULL值更新到非NULL无法做到原地更新,容易发生索引分裂影响性能。
    3. text字段很容易让表容量涨上去,建议把text抽取到子表中,用业务主键关联。
  • 避免索引失效(最大利用索引、尽量避免全表扫描、减少无效数据查询)
    1. 最左前缀法则(不可跳跃索引列)
    2. 索引列不能运算
    3. or的条件查询,其中有索引列,索引失效,union优化
    4. %开头的模糊查询,索引失效
    5. not in会导致索引失效,会导致走全表扫描,连续可以使用between优化,子查询可以使用exists
    6. 避免null字段的判断,可以给null字段统一赋一个默认零值
    7. 隐式类型转换会导致索引的失效,例如varchar给定数值的话,会隐式类型转换,索引会失效
  • sql语句优化
    1. 减少select * ,尽量使用覆盖索引,减少回表次数
    2. insert: 1.多个值使用一条sql语句插入、2. 通过手动提交事务批量插入,3. 数据有序的插入
    3. order by:1.避免FileSort(使用有序索引顺序扫描返回数据),目的在于减少额外排序,尽量通过索引直接返回有序数据。group by:1.先排序,在分组,所以排序时和order by一样的策略。2.对分组字段创建索引,提高效率。如果显式包括一个包含相同的列的 ORDER BY 子句,MySQL 可以毫不减速地对它进行优化,尽管仍然进行排序。因此,如果查询包括 GROUP BY 但你并不想对分组的值进行排序,你可以指定 ORDER BY NULL 禁止排序。
    4. Join:可以把子查询优化为join关联查询,但是也要避免join关联太多表,mysql最多可以关联61个表,但是建议不要超过5个。关联过多会使关联缓存过大,内存就占用的大,容易造成服务器内存溢出,导致性能不稳定。
    5. or语句:1.or之间的每个条件列都必须用到索引 , 而且不能使用到复合索引; 如果没有索引,则应该考虑增加索引。2.建议union替换or
    6. 分页优化:1.在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。例如:explain select * from tb_item t,(select id from tb _item order by id limit 2000000,10) a where t.id = a.id 2.如果主键自增的表,那么可以将limit转换为某个位置的查询。explain select * from tb_item where id>1000000 limit 10;
    7. SQL提示:1.use index希望mysql参考索引列表查询 2.ignore index 想让mysql忽略某个索引 3.force index强制mysql使用特定索引

mysql中 in 和 exists 区别

mysql中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。

性能比较:

  1. 如果两个表大小相当,则他们差别不大,如果子查询是大表建议使用exists,子查询是小表建议使用in。
  2. not in会使内外表都进行全表扫描,索引失效。not exists的子查询不会使索引失效,所以无论什么情况not exists都比not in要快。

count(1)和count(*)

从执行结果来说:
count(1)和count( * )之间没有区别,因为count( * )count(1)都不会去过滤空值,但count(列名)就有区别了,因为count(列名)会去过滤空值。
从执行效率来说:
他们之间根据不同情况会有些许区别,MySQL会对count( * )做优化。
1)如果列为主键,count(列名)效率优于count(1)
2)如果列不为主键,count(1)效率优于count(列名)
3)如果表中存在主键,count(主键列名)效率最优
4)如果表中只有一列,则count( * ) 效率最优
5)如果表有多列,且不存在主键,则count(1)效率优于count(*)

char和varchar的区别?

char存储的定长的长度,属于固定长度的字符串,一旦指定了字符长度,如果实际长度没有达到,那么会使用空格补齐,而超过定长,那么只会存储指定长度的字符。由于是定长的,所以处理速度要比varchar快,缺点是浪费空间。(空间换时间)可以存储定长的身份证、手机等。

varchar会根据实际的值进行存储,存储时会默认在最后增肌一个字符串长度占用一个字节。不会填充字符串。虽然根据实际长度存储,但是在声明时varchar(200)会比varchar(20)更消耗内存(时间换空间)。

mysql中int(10)和char(10)以及varchar(10)的区别

int(10):表示数据长度为10位,9999999999,占32个字节,int型4位
char(10)和varchar(10)的10表示存储数据的大小,即表示存储多少个字符。

©️2020 CSDN 皮肤主题: 像素格子 设计师:CSDN官方博客 返回首页