1、哪些字段适合加索引
- 经常被查询的字段
- 字段长度太长不适合建立索引
- 经常更新的字段不适合建立索引，因为更新还会更新索引文件
- 粒度较大的字段不适合建立索引，比如性别字段
- 几个字段一起查询可以建立组合索引，组合索引的建立顺序要按照字段的频度来确定
- 对长字符串字段建立索引采用最左边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时使用了索引)，则加行排他锁，其他写操作此行都需要等待（不管用的哪个索引定位到此行）；否则会加表排他锁，一旦加了表锁，这个表的所有行数据的写操作都会阻塞；
加锁方式
- 对于写操作（inset、update、delete），innodb会自动给要操作的数据集加排它锁；
- 对于普通的SELECT语句，innoDb不会加任何锁
- 如果在事务中，为了保证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的时候比较是否相等；
网友评论