数据库学习（三）---存储引擎及索引，sql优化

存储引擎

我所有用的图都是b站黑马程序员ppt里的图片哦！
存储引擎是基于表的
show create table accout可以查看自己的存储引擎。
show engines查看本数据库支持什么存储引擎
在创建表时指定存储引擎
create table mytable（id int, name varchar(10) ）engine=MYISAM

innoDB

高可靠性搞性能的通用存储引擎。

innoDB:事务，行级锁，外键
myisam:没有事务，支持表锁，没有支持外键

锁

全局锁（全库逻辑备份）
对所有表进行锁定，加锁后实例处于实力状态，后序的dml,ddl已经更新操作的事务豆浆被阻塞。

加全局锁后：

语法：
加锁
flush table with read lock
数据备份
mysqldump -uroot -p1234 itcast >itcast.sql
释放锁
unlocj table

存在问题，业务期间不能执行更新，在从库备份，会导致主从延迟。
也可以在数据备份命令上加上一个–single-transaction,可以在不加锁的一致性数据备份

表级锁

1.读锁
lock tables 表名 read/write
2.写锁
unlock tables
加读锁的情况

加写锁的情况
3.元数据锁（MDL）
维护表结构的数据一致性
4.意向锁

意向锁的作用是提高效率的。
当有两个事务时，事务一对此表加了行级锁，但你不知道确定是哪一行，当事务二去操作数据时，又要加自己对应的锁，但是事务二又不能直接加锁，他只能先看这张表之前加过锁吗，如果对方加过行级锁，那你事务二就需要遍历列表去查看到底是哪一行加了行级锁，再判断自己是否要加锁进行操作。意向锁的出现就使得事务二无需遍历就可得知表的加锁情况。

意向锁与表锁的兼容情况
意向读锁，与表锁共享锁兼容，与表级排它锁互斥
意向写锁，与表锁互斥。
意向锁之间不是互斥的

4.行级锁
锁的粒度很小，并发度高，发生所冲突的概率最低。

索引

是用来帮助mysql高效获取数据的（有序）数据结构.

创建前缀索引：create index inx_email_5 on user(email(5));

这句话的意思是根据email的前五个字符开始创建索引。和后面的前缀索引一起使用！（这里只做了解后面索引使用里细讲如何创建索引）

索引的数据结构

索引是在存储引擎实现的


B树（多路平衡查找树）

https://www.cs.usfca.edu/~gallers/visualization
二叉树中，每次查找数据时，在最坏的情况下，磁盘io的次数等于索引树的高度。为了减少磁盘io，我们需要把原本瘦高的树变成矮胖的树。
详情请看这里：

B+树，
所有的数据都会出现在叶子结点中，叶子节点形成了单向链表，非叶子节点，表示索引，不存储数据。

索引的分类

默认主键索引就是聚集索引
如果一张表没有主键，将使用第一个唯一索引作为聚集索引。
如果表没有主键也没有唯一索引，那存储引擎会自动生成一个rowid作为隐藏的聚集索引

性能分析

1.慢日志
2.profiling
查看数据库支持profiling
select @@ have_profiling;
查看是否有设置profiling
select @@profiling;
设置profiling开关
set profiling = 1；
查看我们执行的sql语句的耗时情况。。
show profiles；
可以看到16号sql他的具体耗时情况
show profile (cpu) for query 16;
3.explain执行计划
explain select * from tb_user where id = 1
desc select * from tb_user where id = 1
意思是一样的，

id: select中表的执行顺序，id值相同顺序执行，id值不同，值越大越先执行。 select_type 表示select的类型，
type 表示连接类型，性能由好到差的连接类型：null,system,const,eq_ref,ref,range,index,all。
当查询时不访问任何表时才会为null。 根据主键和唯一索引一般会出现const。 非唯一性索引会出现ref。（select *from book where name=‘A’）
possible_key 在这张表中可能用到的索引 key 实际使用的索引 key_len 表示索引中使用的字节数。
rows MySQL认为必须要查询的行数
filtered 查询返回结果的行数占需读取行数的百分比，filtered的值越大越好。

索引使用

针对字段创建索引：
create index idx_sku_sn on tb_sku(sn);

解释：idx_sku_sn 是你想要创建索引的列
tb_sku(sn)是表名

删除索引
drop index index_user_phone_name on tb_user

index_user_phone_name是你想要删除的索引名称
tb_user是表名
这里的在那个表里用的是on

最左前缀法则

联合索引查询时，最左边的索引必须存在，（如果不存在，那联合索引全部失效）而且中间查询时不可跳过中间索引，如果跳过索引将会部分失效。（存在与位置无关）

范围查询：

右边的列索引失效

索引列运算

不要在索引列上进行运算，不然索引会失效。

字符串不加引号

使用字符串时，没有使用单引号，索引将会失效。

模糊查询

如果仅仅是尾部进行模糊匹配（‘软件%’），索引不会失效，头部的话（‘%工程’），索引会失效。会进行全表扫描。

or连接的条件

如果or分割开，

数据分布影响

如果mysql觉得走索引比走全表还慢，那他将会放弃索引。

sql提示

在where前面添加sql提示
给mqsql加入认为的提示，达到优化目的
use index
告诉mysql，你认为规定走那个索引
比如当单列索引和联合索引都在，那Mysql会使用联合索引，那你就可以利用命令建议mysql使用单列索引

ignore index
告诉mysql你不要走那个索引
force index
强制Mysql使用我的索引

覆盖索引

减少使用select *
select *很容易出现回表查询

using index condition 查找使用索引，但是需要回表查询
using where,using index查找使用索引，需要的数据在索引列中都能找到，不需要回表。

前缀索引

主要是为了一些很长的字符串设计的。
字符串很长，会让索引变得很大，磁盘io不便，降低查询效率，所以需要将字符串的前面一部分建立索引。
create index idx_xxxx on table_name(column(n));
select count(distinct email) from user意思是选择email的总数，且email不重复。（distinct 就是去重复的）。

选择性：count(distinct email)/count (*)
选择性的概念：不重复索引值和数据表的记录总数的比值。索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择，性能也是最好的。
理解选择性：其实也很好理解哇，就是你的值越唯一，那你的辨识度越高，越好区分啊。
count(distinct substring(email,1,9))/count (*)
这里substring是在利用取子串的方式进行选择性判断，逐渐缩短email看会不会影响选择性。不影响的情况下肯定是越短越好！之后就可以根据你选择的内部分进行索引创建。
创建前缀索引：
create index inx_email_5 on user(email(5));
前缀索引的查询流程

单列索引和联合索引

单列：就是一个索引包含一个列
联合索引：一个索引包含多个列
推荐使用联合索引。（联合索引很少发生回表查）

索引设计原则

SQL优化

插入数据

1.批量插入
2.手动事务提交，执行insert语句之前开启事务，插入完再提交事务。
3.主键顺序插入
大量插入数据
使用load指令

主键优化

inniDB,表数据根据主键顺序组织存放，这种表就是索引组织表

页分裂：

主键乱序插入就会发生页分裂

页分裂：就是当插入乱序时，你插入的数据不会立刻给自己找一个空白页插入，他会先看前面的页是不是有剩余位置，然后把自己插到合适的位置，如果内个位置的页没有多余的空位的话就会出现页分裂，把后面分裂出来的数据放到新的页中，并把需要插入的数据插到新页中之后在调整页与页的关系。

页合并
MERGE_THRESHOLD合并页的阈值，一般是50%，也可以自己设置，在创建表或者创建索引时指定。

这里说一下页合并：就是当一个页中的数据被删除到只剩50%或者说删除了50%，就可以考虑和其他页进行合并。
举个例子：加入有三页，删除数据在2页时，删除了超过50%，再看页1和页3，发现页3的数据不足50%那就可以进行两页合并

推荐使用AUTO_INCREMENT
不推荐使用uuid，因为uuid他自动生成的id比较长。

order by优化

Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort
Using index :通过有序索引扫描直接返回有序数据，这种情况就是using index,不需要额外排序，操作效率高。

根据排序字段建立合适的索引，多字段排序时也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k).

group by优化

分组使用索满足最左前缀法则

limit优化

优化：通过创建覆盖索引能够比较好的提高性能高，通过覆盖索引加子查询tigaochaxunxiaolv。
explain select * from user t,(select id from book order by id limit 2000000,10) a where a.id = t.id;

count优化

count(主键)：把每一行的主键id值取出，返回给服务层。服务层拿到主键后直接按行进行累加（主键不可能为null）
count(字段)：没有notnull约束innodb引擎会遍历整张表的每一行的字段值并取出，返回给服务层，服务层判断是否null，不为null,计数累加。没有not null约束，innodb引擎会遍历整张表并把每一行的字段值都取出，返回给服务层，并按行进行累加。
count(1):innodb会遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行取值。
count(*):innodb并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加
建议使用count(*)

update优化

innodb的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

意思就是update数据时where 后面的条件应该是有索引的，否则将会从行锁升级为表锁，效率降低。