Mysql note

2019-06-14  本文已影响0人  OOMNPE

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+树优势：

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

image.png

5、B+ 树

B+树的插入、删除

引用
引用

聚集索引

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

非聚集索引（辅助索引）

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

6、数据表的水平扩容

引用

背景

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

水平扩容的难点

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

解决方案

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乐观锁和悲观锁
上一篇 下一篇

猜你喜欢

热点阅读