mysql调优部分基础（部分）（简单易懂）

索引的作用：数据结构提升检索效率

索引的缺点：降低增、删、改效率，占空间 需要维护

数据库底层结构：b+树

为何数据库底层为b+树？

（1）二叉树容易出现层高问题，且可能会出现所有数据全部存在一侧使效率降低

（2）红黑树为二叉树的一种，仍然会出现层高问题

（3）b树也会出现层高问题，

（4）b+树由于根叶子节点不存储数据，不会出现层高问题，

（5）哈希表不支持范围查询，且一旦出现哈希碰撞，则效率会降低。

索引的种类： 主键索引、 唯一索引、 普通（单列）索引、 联合（多列）索引、 全文索引

索引的分类：

（1）聚集索引：主键

（2）二级索引（辅助索引）： 唯一索引、 普通（单列）索引、 联合（多列）索引、 全文索引

使用时尽量避免回表查询，会降低效率。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取 数据的方式，就称之为回表查询。

有关索引的sql语句：

创建索引 ：create index idx_字段名 on表名(字段名)

创建唯一索引：create unique index idx_字段名 on表名(字段名)

创建联合索引 ：create index idx_字段名 on表名(字段名1，字段名2)

查看索引 ：show index from 表名

删除索引： drop index idx_字段名 on 表名

查询语句执行频率： show global status like 'Com___(七个下划线)'

查看是否开启慢查询 ：show variables like 'slow_query_log%'

开启慢查询： set global slow_query_log='ON'

开超时时间： set global long_query_time=0.3

是否支持查看详情： select @have_profiling

关闭详情查看 ：set profiling = 0

查看所有sql执行时间 ：show profiles

查看sql语句每个阶段耗时： show profile cpu for query 16

查看执行计划： explain或者DESC， explain select*from 表名 where 条件

索引失效的几种情况：

（1）联合索引：最左前缀 不能跳过字段

（2）函数运算

（3）范围判断 or ： or左右两边的字段都要有索引

（4）字符串不加引号

（5）模糊查询模糊头部

（6）当查全表比索引快

前缀索引：

为tb_user表的email字段，建立长度为5的前缀索引。

create index idx_email_5 on tb_user(email(5));

select count(distinct substring(email,1,5)) from tb_user （5为变动值找到重复与不重复的边界值）

单列索引：即一个索引只包含单个列。

联合索引：即一个索引包含了多个列。

索引设计原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。

2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增 删改的效率。 1 create unique index idx_user_phone_name on tb_user(phone,name);

7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时，它可以更好地确定哪个索引最有效地用于查询。

插入优化：使用批量插入

批量插入insert into tb_student values(1,'Tom'),(2,'Sam'),(3,'Bob');

主键顺序插入：

主键乱序插入：索引维护性能降低（引起页分裂）

主键自有一个字段

order by优化（联合索引优化）

group by优化（建立索引）

范围查询优化（用自连接+聚合索引）

count优化

count(普通字段)（将值取出来，在判断空不空）

count（id）（主键不用判断是否为空）

count（1）/count（0）（效率一样）

count(*) 5.6版本之前（取出全部数据）8.0版本以后

运行速度

count() 5.6版本之前<count(普通字段)<count（主键）<count（1）/ count（0）<count() 8.0版本以后。

补充：

update优化：

根据主键修改    表锁

根据其他字段(无索引字段)修改    锁全表

行锁：锁数据库表中的一行

表锁：锁数据库中的一个表

全局锁：锁数据库所有表

加锁后整个实例就处于只读状态，只能查看不能修改

开启全局锁：flush tables with read lock；

关闭全局锁：unlock tables；

数据备份（使用时会锁全表）：

(do命令)mysqldump  -uroot -proot b063-mysql > d://b0363-mysql.sql

数据备份（使用时不会锁全表）（快照技术实现）：

mysqldump --single-transaction  -uroot -proot b063-mysql > d://b0363-mysql.sql

对于表锁分为两类

表锁

​        表共享读锁（read lock）--表读锁

lock tables 表名 read

特点：自己和别人只能查询不能修改

​        表独占写锁（write lock）--表写锁

特点：自己可以修改、查询，别人不能修改和查询

lock tables 表名 write

元数据锁（meta data lock，MDL）

无需显示使用手动加锁和释放，锁表结构

意向锁

会根据该表上所加的意向锁来判断是否成功加表锁，不用逐行判断行锁情况（相当于标识符）

意向共享锁（意向读锁）

select * from tb_user where id= 30 **lock in share mode**（查询语句要加上黑体字才有意向锁，增删改不需要写也会有意向锁）；

意向读锁：和表读锁互相兼容

意向写锁：和表读锁和表写锁都不兼容

行级锁

行锁：锁索引里的数据，没有索引锁表

间隙锁：

Begin

update 表名 set 字段名 =修改的值 where 条件

当向表中插入数据时

另一个事务在两行数据中间不可以再插入数据

临键锁：范围判断时用