Mysql note

1、哪些字段适合加索引

  1. 经常被查询的字段
  2. 字段长度太长不适合建立索引
  3. 经常更新的字段不适合建立索引,因为更新还会更新索引文件
  4. 粒度较大的字段不适合建立索引,比如性别字段
  5. 几个字段一起查询可以建立组合索引,组合索引的建立顺序要按照字段的频度来确定
  6. 对长字符串字段建立索引采用最左边n个字符建立索引:index left(address,8)

2、索引的选择

如果有查询条件中有多个字段,并且每个字段都有单独是索引,那么mysql只会选择其中一个索引执行筛选,并且这个索引选择性最强的,即筛选后数据最少;比如:

 select * from tb where  grade = '一年级' and class = '3班' and student ='张三' 

如果grade、class、student三个字段都有索引,那么mysql只会用到student这一个选择性最强的索引;

查询条件的顺序对查询效率没有影响

where grade = '一年级' and class = '3班' and student ='张三'
等价于
where class = '3班' and student ='张三' and grade = '一年级'

组合索引的选择

MySQL遵循最左前缀原理,当查询条件匹配联合索引的前面几列时,可以使用联合索引;否则,不会使用联合索引。








3、索引的实现方式

索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,在数据十分庞大的时候,索引可以大大加快查询的速度,这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

哈希索引

由InnoDB根据情况自动生成,不能人为生成,只有memory(内存)存储引擎支持哈希索引,哈希索引用索引列的值计算该值的hashCode,然后在hashCode相应的位置存执该值所在行数据的物理位置,哈希索引不支持范围查找和排序的功能。

全文索引

对于文本的大对象,或者较大的CHAR类型的数据,如果使用普通索引,那么匹配文本前几个字符还是可行的,但是想要匹配文本中间的几个单词,那么就要使用LIKE %word%来匹配,这样需要很长的时间来处理,响应时间会大大增加,这种情况,就可使用时FULLTEXT索引了,在生成FULLTEXT索引时,会为文本生成一份单词的清单,在索引时及根据这个单词的清单来索引

全文索引的查询也有自己特殊的语法,而不能使用LIKE %查询字符串%的模糊查询语法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

在MySQL中,全文索引支队英文有用,目前对中文还不支持

BTree索引和B+Tree索引

B+树:所有记录节点按照键值大小顺序存放在同一层的叶子节点上;
B树:记录会也会存在非叶子节点上

B+树优势:
  • 高扇出(一个非叶子节点16k可存储更多的索引),意味着树的高度低(一般最多3层),也就意味着查找所用的IO次数少
  • 叶子节点通过指针来连接,范围查找方便

4、InnoDB存储表数据的物理结构

image.png
  • 表空间可以对应一个表或者多个表
  • 真实表数据只有一份,索引只存储数据的主键,通过主键值找到对应数据(聚集索引除外)

5、B+ 树

B+树的插入、删除

引用
引用

聚集索引

聚集索引 = 全表数据 + 基于主键大小构建的B+树
以主键为标准,在物理磁盘储存为顺序,一个表只有一个聚集索引

非聚集索引(辅助索引)

辅助索引 = 主键数据 + 基于某列(或多列)数据构建的B+树
非叶子节点存储的是某列(或多列)的值的索引,而叶子节点不存储完整行数据,而是主键值

6、数据表的水平扩容

引用

背景

当我们在设计一个表的时候,考虑到后续的数据增加,会用到分库分表水平扩展,比如将订单表水平扩展为5个,根据订单号除以5取模来决定放到哪个表,但是如果数据增长很快,当一个表的数据超过500万时,对mysql性能影响很大,所以必须要继续扩容;

水平扩容的难点

第一个就是规则的变化,意味着要改程序代码
第二个就是数据迁移,因为数据量已经很大,如果做全量迁移的话,可能会面临停机的问题;

解决方案
  • 按段分表
    在设计表的时候,根据id的值(自增长,或者用时间)的范围决定放到哪一张表,比如 [0 - 5000000]放在第一张表,[5000001 - 10000000] 放在第二张表,当未来数据增长到1500万的时候再添加第三张表,等等...
    但是这样有个问题:我们做分表就是为了减轻单表的压力,需要将压力分摊到多个地方提升性能?
  • 分库分表
    比如分3个库,用id % 3确定放到哪个库,这样就将压力分摊到了三个数据库实例;每个库初始一张表,当数据快超过500万时,创建第二张水平扩展表;规则为:id [0 -1.5kw] 存每个库的第一张表, [1.5kw - 3kw] 存第二张表 .....

7、Mysql 事务

引用

7.1 重做日志(redo log)

确保事务的持久性。防止在发生故障的时间点,尚有脏页未写入磁盘,在重启mysql服务的时候,根据redo log进行重做,从而达到事务的持久性这一特性。

7.2 回滚日志(undo log)

保存了事务发生之前的数据的一个版本,可以用于回滚,同时可以提供多版本并发控制下的读(MVCC),也即非锁定读

7.3 二进制日志(binlog):

用于复制,在主从复制中,从库利用主库上的binlog进行重播,实现主从同步。
用于数据库的基于时间点的还原。

8、锁

引用

行锁

InnoDB实现了两种类型的行锁

  • 共享锁(读锁):
    允许其他事务读取数据,阻止其他事务获取排它锁(写锁)
  • 排它锁(写锁):
    阻止其他事务获得相同数据集的共享锁和排它锁;
加锁触发条件

mysql所有的写操作,都会在写之前去获取排他锁,如果获取不到则等待,操作完了释放锁(如果在事务里,则需要事务commit了才会释放锁);
如果所操作行能被索引到(explain时使用了索引),则加行排他锁,其他写操作此行都需要等待(不管用的哪个索引定位到此行);否则会加表排他锁,一旦加了表锁,这个表的所有行数据的写操作都会阻塞;

加锁方式
  1. 对于写操作(inset、update、delete),innodb会自动给要操作的数据集加排它锁;
  2. 对于普通的SELECT语句,innoDb不会加任何锁
  3. 如果在事务中,为了保证select数据的有效性,可以显式的给数据集加共享锁或排他锁:
    3.1. 共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 。 其他 session 仍然可以查询记录,并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作,则很有可能造成死锁。
    3.2. 排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录,但是不能对该记录加共享锁或排他锁,而是等待获得锁
行锁实现方式

InnoDB的行锁是通过给索引上的索引项加锁来实现的,意味着只有通过索引条件检索数据(explain时使用了索引),InnoDB才会使用行锁,否则InnoDB将使用表锁

MySQL乐观锁和悲观锁
  • 悲观锁
    共享锁、排他锁 等都是悲观锁
  • 乐观锁
    手动实现,在表中加入version字段,每次查的时候取出,update的时候比较是否相等;
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 201,468评论 5 473
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 84,620评论 2 377
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 148,427评论 0 334
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,160评论 1 272
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,197评论 5 363
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,334评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,775评论 3 393
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,444评论 0 256
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,628评论 1 295
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,459评论 2 317
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,508评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,210评论 3 318
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,767评论 3 303
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,850评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,076评论 1 258
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,627评论 2 348
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,196评论 2 341

推荐阅读更多精彩内容

  • 今天看到一位朋友写的mysql笔记总结,觉得写的很详细很用心,这里转载一下,供大家参考下,也希望大家能关注他原文地...
    信仰与初衷阅读 4,722评论 0 30
  • 问题1:char、varchar的区别是什么?varchar是变长而char的长度是固定的。如果你的内容是固定大小...
    风的低语阅读 1,160评论 0 8
  • 一、MySQL优化 MySQL优化从哪些方面入手: (1)存储层(数据) 构建良好的数据结构。可以大大的提升我们S...
    宠辱不惊丶岁月静好阅读 2,406评论 1 8
  • 数据库的基本是概念名词解释: 数据库名词解释 元组:可以理解为表的每一行就是一个元组 候选码:若关系中的某一属性组...
    杰伦哎呦哎呦阅读 1,097评论 0 6
  • ORA-00001: 违反唯一约束条件 (.) 错误说明:当在唯一索引所对应的列上键入重复值时,会触发此异常。 O...
    我想起个好名字阅读 5,102评论 0 9