MySQL高级

1、存储引擎

• 存储引擎就是存储数据，建立索引，更新查询数据等技术的实现方法。
• 存储引擎是基于表的，不是基于库的。也称为表类型
• oracle等数据库只有一种存储引擎。MySQL提供插件式存储引擎架构
• 默认InnoDB，支持事务，行级锁，外键

1.1、各种存储引擎特性

2、索引

索引是帮助MySql高效查询数据的数据结构

2.1、优势

• 类似书籍的目标索引，提高数据检索的效率，降低数据IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

2.2、劣势

• 索引也是一张表，保存了主键和索引字段，并指向实体类记录，故索引也要占用空间
• 增删改操作表数据，则索引也需要调整，降低操作速度

2.3、索引的分类

• 单值索引：即一个索引只包含单个列，一个表可以有多个单值索引
• 唯一索引：索引列的值必须唯一，但允许多个空值
• 复合索引：即一个索引包含多个列

2. 4、索引的语法

创建索引

create  index index_name on table_name(name...)

查看索引

show index from table_name

删除索引

drop index index_name on table_name

alter命令添加索引

alter table table_name add index index_name(name...)

3、explain分析执行计划

3. 1、explain之id

1）id相同表示加载表的顺序从上到下

2）id不同id值越大，优先级越高，越先被执行

3. 2、explain之select_type

1）SIMPLE：简单的select查询，查询中不包含子查询或union

2）PRIMARY：查询中若包含任何复杂的子查询，最外层查询标记为该标识 3）SUBQUERY：在select或where列表中包含子查询

4）DERIVED：在from列表中包含子查询，被标记为DERIVED（衍生）MYSQL会递归执行这些子查询，把结果放在临时表中

5）UNION：若第二个select出现在union之后，则标记为union；若union包含在from子句的子查询中，外层select将被标记为DERIVED 6）UNION RESULT：从union表获取结果的select

执行顺序：2、1、null

3. 3、explain之table

此行数据来自于那张表

3. 4、explain之type

1）NULL：MySQL不访问任何表，索引，直接返回结果

2）system：表只有一行记录，这是const类型的特例，一般不会出现 3）const：表示通过索引一次就找到。const用于primary key或者unique索引。因为只匹配一条数据，所以很快。MYSQL将查询转换为一个常量，const将主键或唯一索引的所有部分与常量进行比较

4）eq_ref：类似ref，区别在于使用的是唯一索引，使用主键关联查询，查询结果只有一条。常见于主键或唯一索引扫描

5）ref：非唯一性索引扫描，返回所有匹配的单个或多个值

6）rang：where之后出现between，<,>,in等操作

7）index：与all的区别是，index只遍历索引树，通常比all快

在这里插入图片描述

8）all：全表扫描

从上到下，速度依次减慢。一般来说，得保证查询至少达到rang级别，最好能达到ref

3. 5、explain之key

1）possible_keys：显示可能用到这张表的索引，一个或多个

2）key：实际使用的索引，如果为NULL，则没有使用索引

3）key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，根据表定义所得。长度越短越好

3. 6、explain之rows

扫描行的数量

3. 7、explain之ref

显示索引的那一列被使用了，如果可能的话，是一个常量。哪些列或常量被用于查找索引列上的值

3. 8、explain之extra

1）using filesort：mysql会对数据使用一个外部的索引排序，而不是根据表内的顺序进行读取，称为文件排序

2）using temporary：使用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于order by 和 group by

3）using index：使用了覆盖索引，避免访问表的数据行，效率好

4）using where：使用where过滤

覆盖索引：select的数据列只用从索引中就能获取到，不必读取数据行

4、索引的使用

4.1、全值匹配

对索引所有的列都指定具体的值

4.2、最左前缀法则

查询从复合索引的最左列开始，并且不跳过索引的列

1）索引生效（与顺序无关）

1）索引失效（没有最左列“name”）

4.3、失效情况

1）范围查询后面的索引字段失效

2）不要再索引列上进行运算操作

3）字符串不加单引号，数据底层添加‘’，是运算操作，故索引失效

4）用or分隔开的条件，如果or前后任意一个字段列没有索引，整个语句不走索引

5）以%开头的like模糊查询，索引失效

6）全表扫描更快

7）null和not null，系统会根据数据情况选择最优方式

8）in走索引，not in不走索引

4.4、order by 优化

1）查询字段不是索引字段或者排序字段不是索引字段，都是using filesort

2）查询字段和排序字段都是索引字段，using index

order by 的顺序和复合索引顺序必须相同，并且要么都升序要么都降序

4.5、group by 优化

1）非索引添加order by null 或者字段加索引

4.6、 or 优化

使用union

4.7、 limit 优化

两种方式对比：

5、MySql锁

5.1、锁的分类

从对数据操作的粒度分： 1）表锁：操作时，会锁定整个表 2）行锁：操作时，会锁定当前操作行

从对数据操作的类型分： 1）读锁（共享锁）：针对同一份数据，多个读操作可以同时进行不互不影响 2）写锁（排它锁）：当时操作没有完成之前，它会阻断其他写锁和读锁

5.2、MyISAM表锁

1）MyISAM存储引擎只支持表锁。

2）MyISAM在执行查询前会自动添加读锁，在执行增删改前会自动添加写锁，一般不需要用显示加锁。

3）显示加锁语法

加锁：lock table table_name read;
读锁：lock table table_name write;

总结： 读锁会阻塞写，但不会阻塞读。而写锁，及阻塞读，有阻塞写 MyISAM不适合做写为主的表的存储引擎。

5.3、InnoDB行锁

1）InnoDB与MyISM最大区别是：支持事务；采用行级锁

2）对应普通查询语句，InnoDB不会加任何锁。对应增删改回自动添加排它锁

3）显示加锁语法

共享锁：select * from table_name where ... lock in share mode;
排它锁：select * from table_name where ... for update

4）无索引行锁升级为表锁 如果不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，实际效果和表锁一样

5）间隙锁 当我们用范围条件检索数据，比如1,3,5，查询<=5数据，虽然2,4不存在，但依然上锁了。不能新增2,4数据。