mysql总结
一、存储引擎
(1)两种存储引擎
MyISAM:专注性能,主键索引和辅助索引是独立的
InnoDB:专注事务,支持事务、行锁、外键、聚集索引
(2)MyISAM 和 InnoDB 的区别
1、InnoDB支持事务,MyISAM不支持
2、InnoDB支持外键,而MyISAM不支持
3、InnoDB是聚集索引,数据文件是和索引绑在一起的,必须要有主键,通过主键索引效率很高
4、InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快;
5、Innodb不支持全文索引,而MyISAM支持全文索引,查询效率上MyISAM要高
二、主键与索引
(1)索引类型
B+树索引、Hash索引
MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候,首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
InnoDB: 其数据文件本身就是索引文件。其表数据文件本身就是按B+Tree组织的一个索引结构,树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引(或聚集索引)”。而其余的索引都作为辅助索引,辅助索引的data域存储相应记录主键的值而不是地址,+这也是和MyISAM不同的地方。
(2)联合索引
1、联合索引是两个或更多个列上的索引,只能左侧匹配。
2、利用索引中的附加列,可以缩小搜索的范围,但使用一个具有两列的索引不同于使用两个单独的索引。
(3)什么情况下应不建或少建索引
1、表记录太少
2、经常插入、删除、修改的表
3、数据重复且分布平均的表字段
4、经常和主字段一块查询但主字段索引值比较多的表字
(4)使用数据索引能提高效率
1、数据索引的存储是有序的
2、在有序的情况下,通过索引查询一个数据是无需遍历索引记录的
3、极端情况下,数据索引的查询效率为二分法查询效率,趋近于 log2(N)
(5)B+树索引和哈希索引的区别
B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的。
哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可,是无序的。
1、哈希索引的优势
等值查询,没有大量重复键值(哈希碰撞问题),哈希索引具有绝对优势
2、哈希索引不适用的场景
不支持范围查询
不支持索引完成排序
不支持联合索引的最左前缀匹配规则
通常,B+树索引结构适用于绝大多数场景,像下面这种场景用哈希索引才更有优势:
在HEAP表中,如果存储的数据重复度很低(也就是说基数很大),对该列数据以等值查询为主,没有范围查询、没有排序的时候,特别适合采用哈希索引
(6)B树和B+树
1、区别
1)B树,每个节点都存储key和data,所有节点组成这棵树,并且叶子节点指针为nul,叶子结点不包含任何关键字信息。
2)B+树,所有的叶子结点中包含了全部关键字的信息,及指向含有这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大的顺序链接
所有的非终端结点可以看成是索引部分,结点中仅含有其子树根结点中最大(或最小)关键字。 (而B 树的非终节点也包含需要查找的有效信息)
2、B+比B树更适合实际应用中操作系统的文件索引和数据库索引
1)B+的磁盘读写代价更低。
B+的内部结点并没有指向关键字具体信息的指针,因此其内部结点相对B树更小。
2)B+-tree的查询效率更加稳定。
由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
(7)主键生成方式
Mysql自增长主键策略
时间戳相关生成策略(数据库无关,移植性较好 ,但长度太长。雪花算法)
UUID、GUID(移植性较好,但没有排序,无法保证趋势递增、查询的效率较低)
每次取主键最大值+1做为新的主键(主键长度可控,移植性较好 ,并发写会造成主键冲突)
单独建一个存放主键的表(并发问题、性能影响较大)
(8)为什么用自增列作为主键
1、使用自增列(INT/BIGINT类型)做主键,写入顺序是自增的,和B+数叶子节点分裂顺序一致
2、不指定自增列做主键,同时也没有可以被选为主键的唯一索引(上面的条件),这时候InnoDB会选择内置的ROWID作为主键,写入顺序和ROWID增长顺序一致;
三、数据库锁
(1)锁的粒度划分
表级锁、行级锁、页级锁
MyISAM采用表级锁(table-level locking)。
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁
(2)表级锁
Mysql中锁定粒度最大的一种锁,对当前操作的整张表加锁,实现简单,资源消耗也比较少,加锁快,不会出现死锁。其锁定粒度最大,触发锁冲突的概率最高,并发度最低,MyISAM和 InnoDB引擎都支持表级锁。
表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)
(3)行级锁
Mysql中锁定 粒度最小 的一种锁,只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小,并发度高,但加锁的开销也最大,加锁慢,会出现死锁。
1、两阶段锁协议
InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放
2、InnoDB的三种行锁算法
Record lock:单个行记录上的锁
Gap lock:间隙锁,锁定一个范围,不包括记录本身
Next-key lock:临键锁,record+gap 锁定一个范围,包含记录本身
3、优缺点
优点:
1)当在许多线程中访问不同的行时只存在少量锁定冲突。
2)回滚时只有少量的更改
3)可以长时间锁定单一的行。
缺点:
1·)比页级或表级锁定占用更多的内存。
2)当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。
3)如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表,比其它锁定明显慢很多。
4)用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。
(4)MVCC(行锁)
1、锁实现
LBCC基于锁的并发控制、MVCC多版本并发控制
MySQL InnoDB存储引擎,实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)
MVCC只在读提交和可重复读隔离级别下工作
2、MVCC最大的好处
读不加锁,读写不冲突。在读多写少的OLTP应用中,读写不冲突是非常重要的,极大的增加了系统的并发性能,现阶段几乎所有的RDBMS,都支持了MVCC。
基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低,MVCC是在基于锁的并发控制上的改进,主要是在读操作上提高了并发量。
3、MVCC读操作(RR级别下防止幻读)
快照读 (snapshot read):读取的是记录的可见版本 (有可能是历史版本),不用加锁(共享读锁s锁也不加,所以不会阻塞其他事务的写)
当前读 (current read)(临键锁):读取的是记录的最新版本,并且,当前读返回的记录,都会加上锁,保证其他事务不会再并发修改这条记录
(5)死锁和死锁检测
并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁
死锁两种处理策略:
1、直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
2、发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑。
(6)锁类型
共享/排它锁(Shared and Exclusive Locks):S、X
意向锁(Intention Locks):表级锁,IS、IX
记录锁(Record Locks):行级锁
间隙锁(Gap Locks):行级锁
临键锁(Next-key Locks):行级锁、记录锁与间隙锁的组合
插入意向锁(Insert Intention Locks):间隙锁(GapLocks)的一种
自增锁(Auto-inc Locks):表级锁
IS与IX兼容,X与IS、IX互斥,S与IX互斥、与IS兼容
四、事务
(1)事务特性ACID
原子性: 事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;
一致性: 执行事务前后,数据保持一致;
隔离性: 并发访问数据库时,一个用户的事物不被其他事物所干扰,各并发事务之间数据库是独立的;
持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库 发生故障也不应该对其有任何影响。
(2)InnoDB四种事务隔离级别
读未提交(脏读)、读提交(不可重复读)、可重复读(幻读)、串行化
Mysql 默认采用的 REPEATABLE_READ隔离级别
Oracle 默认采用的 READ_COMMITTED隔离级别.
五、表分区
表分区,是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成。
(1)表分区与分表的区别
分表:指的是通过一定规则,将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。
分表与分区的区别在于:分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表。
(2)表分区的好处
1、存储更多数据。分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比,可以存储更多数据
2、优化查询。在where语句中包含分区条件时,可以只扫描一个或多个分区表来提高查询效率;涉及sum和count语句时,也可以在多个分区上并行处理,最后汇总结果。
3、分区表更容易维护。例如:想批量删除大量数据可以清除整个分区。
4、避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问,ext3问价你系统的inode锁竞争等。
(3)分区表的限制因素
一个表最多只能有1024个分区
MySQL5.1中,分区表达式必须是整数,或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都必须包含进来。即:分区字段要么不包含主键或者索引列,要么包含全部主键和索引列。
分区表中无法使用外键约束
MySQL的分区适用于一个表的所有数据和索引,不能只对表数据分区而不对索引分区,也不能只对索引分区而不对表分区,也不能只对表的一部分数据分区。
(4)如何判断当前MySQL是否支持分区?
命令:show variables like '%partition%' 运行结果:
have_partintioning 的值为YES,表示支持分区。
(5)MySQL支持的分区类型
RANGE分区: 这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
LIST分区: 这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区,与RANGE的区别是,range分区的区间范围值是连续的。
HASH分区 :这中模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
KEY分区 :上面Hash模式的一种延伸,这里的Hash Key是MySQL系统产生的
六、不走索引
1、在索引字段上运算、函数操作
2、隐性转换、JOIN操作主键和外键的数据类型不相同
3、非左Like
4、OR操作符,其中一个条件没有索引、非主键列和不同索引(取决于MySQL查询优化器)
5、GROUP BY、ORDER BY子句子句,WHERE条件不包含索引列
6、联合索引,没有使用索引前缀,使用了联合索引的全部列,但索引键不是AND操作
反向查询区分情况
7、not , not in, not like, <>, != ,!>,!< ,不包含null
8、null值
七、MySQL优化方法
1、IN包含的值不应过多
2、SELECT语句指明字段
3、只需要一条数据,limit 1
4、排序没有用到索引,尽量少排序
5、限制条件中其他字段没有索引,少用or
6、用union all代替union
7、不使用ORDER BY RAND()
8、区分in和exists, not in和not exists
9、分段查询
10、避免where 子句中对字段进行 null 值
11、不建议使用%前缀模糊查询
12、where子句中对字段进行表达式
13、避免隐式类型转换
14、联合索引来说,要遵守最左前缀法则
15、联合索引范围查询索引字段失效
16、JOIN优化:尽量使用inner join,避免left join、小表去驱动大表、STRAIGHT_JOIN用索引
MySQL优化
-
开启查询缓存,优化查询
-
explain你的select查询,这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的
-
当只要一行数据时使用limit 1,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据
-
为搜索字段建索引
-
使用 ENUM 而不是 VARCHAR。如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是VARCHAR
-
Prepared StatementsPrepared Statements很像存储过程,是一种运行在后台的SQL语句集合,我们可以从使用 prepared statements 获得很多好处,无论是性能问题还是安全问题。
Prepared Statements 可以检查一些你绑定好的变量,这样可以保护你的程序不会受到“SQL注入式”攻击
-
垂直分表
-
选择正确的存储引擎
四、CHAR 和 VARCHAR
1、CHAR 和 VARCHAR 类型在存储和检索方面有所不同
2、CHAR 列长度固定为创建表时声明的长度,长度值范围是 1 到 255 当 CHAR
值被存储时,它们被用空格填充到特定长度,检索 CHAR 值时需删除尾随空格。