mysql

Mysql索引

索引是什么

索引是帮助mysql 高效获取数据的数据结构 ，数据库索引好比一本书前的目录， 能加快数据库的查询速度 。

索引往往是存储在磁盘上的文件中

优势：可以提高数据检索的效率

劣势：索引会占据磁盘空间，会降低更新表的效率

索引类型

主键索引：索引列中的值必须是唯一的，不允许有空值

普通索引：mysql中普通索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值

唯一索引：索引列中的值必须是唯一的，但是允许为空值

全文索引：在进行like模糊查询时效率比较低，这时可以创建全文索引

空间索引：mysql在空间索引这方面遵循OpenGIS几何数据模型规则

前缀索引：可以指定索引列的长度，但是数值类型不能指定

索引的数据结构

Hash表

二叉树

B+树

mysql的索引实现

两种存储引擎的索引实现： MyISAM索引 和 InnoDb索引

• 创建单个字段索引的语法：CREATE INDEX 索引名 on 表名(字段名)
• 创建联合索引的语法：CREATE INDEX 索引名 on 表名(字段名1，字段名2)
• 索引命名格式一般可以这样：idx_表名_字段名。注意有长度限制
• 删除索引：DROP INDEX 索引名 ON 表名

• 中到大数据量表适合使用索引
• 小数据量表，大部分情况全表扫描效率更高
• 特大数据量表，建立和使用索引的代价会随之增大，适合使用分区或分库

索引：排好序的快速查找数据结构

单值索引：即一个索引只包含单个列，一个表可以有多个单例索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列

基本语法：

创建：

• create [unique] index indexName ON mytable(columnname(length));
• alter mytable add[unique] index[indexName] ON(columnname(length));

删除：DROP INDEX[indexName] ON mytable;

查看：SHOW INDEX FROM table_name\G

mysql索引结构：BTree索引、Hash索引

频繁作为查询条件的字段应该创建索引

频繁更新的字段不适合创建索引

where条件里用不到的字段不创建索引

不适合建立索引

表记录太小

经常增删改的表

数据重复且分布平均的表的字段

MyISAM和InnoDB

MyISAM：只有表级锁

InnoDB：支持行级锁、和表级锁。默认为行级锁

表级锁

MySQL中锁定粒度最大的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁，触发锁冲突的概率最高，并发度最低，MyISAM和InnoDB都支持表级锁

行级锁

MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁，行级锁能大大减少数据库操作冲突，其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁

MyISAM只有表级锁，InnoDB行级锁和表级锁（默认行级锁）

垂直分区与水平分区

根据数据库里面数据表的相关性进行拆分。

垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

垂直拆分的优点：可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。垂直分区可以简化表的结构，易于维护

垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起join操作，可以通过在应用层进行join来解决。垂直分区会让事务变得更加复杂。

水平分区：保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量

SQL语句的执行流程

权限校验 -> 查询缓存 -> 分析器 -> 优化器 -> 权限校验 -> 执行器 -> 引擎