超全MySQL学习笔记

超全MySQL学习笔记

⽬录

MyISAM和InnoDB

性能下降SQL慢的原因：

Mysql执⾏顺序

SQLJoin

索引

索引的优劣

1.优势

2.劣势

索引分类

创建

删除

查看

mysql索引结构

那些情况建索引

哪些情况不要建索引

性能分析

能⼲什么

id三种情况

select_type

type：：

Extra

索引优化

单表优化

双表优化

三表优化

索引⼝诀

索引案例

Order By 索引优化

exsites

Mysql慢查询⽇志命令

函数操作批量插⼊数据

show Profile分析sql

全局查询⽇志

Mysql锁

锁命令

表锁：读锁

表锁：写锁

⾏锁

事务

⾏锁测试

间隙锁

⾏锁总结

MyISAM和InnoDB

对⽐MyISAM InnoDB

主外键不⽀持⽀持

事务不⽀持⽀持

⾏表锁表锁，操作时即使操作⼀条记录也会锁住⼀整张表，不适合⾼并发的操作⾏锁，操作时只锁住某⼀⾏，不会影响到其他⾏，适合⾼并发

缓存只缓存索引，不缓存其他数据缓存索引和真实数据，对内存要求较⾼，⽽且内存⼤⼩对性能有影响表空间⼩⼤

关注点性能事务

默认安装Y Y

性能下降SQL慢的原因：

查询语句写的差

索引失效

关联查询太多join （设计缺陷或不得已的需求）

服务器调优及各个参数设置(缓冲，线程参数)

Mysql执⾏顺序

⼿写

机读先从from开始

SQLJoin

a表

mysql> select * from tbl_dept;

+----+----------+--------+

| id | deptName | locAdd |

+----+----------+--------+

| 1 | RD | 11 |

| 2 | HR | 12 |

| 3 | MK | 13 |

| 4 | MIS | 14 |

| 5 | FD | 15 |

+----+----------+--------+

5 rows in set (0.00 sec)

b表

+----+------+--------+

| id | name | deptId |

+----+------+--------+

| 1 | z3 | 1 |

| 2 | z4 | 1 |

| 3 | z5 | 1 |

| 4 | w5 | 2 |

| 5 | w6 | 2 |

| 6 | s7 | 3 |

| 7 | s8 | 4 |

| 8 | s9 | 51 |

+----+------+--------+

8 rows in set (0.00 sec)

mysql不⽀持全连接

使⽤以下⽅式可以实现全连接

mysql> select * from tbl_dept a right join tbl_emp b on a.id=b.deptId -> union

-> select * from tbl_dept a left join tbl_emp b on a.id=b.deptId; +------+----------+--------+------+------+--------+

| id | deptName | locAdd | id | name | deptId |

+------+----------+--------+------+------+--------+

| 1 | RD | 11 | 1 | z3 | 1 |

| 1 | RD | 11 | 2 | z4 | 1 |

| 1 | RD | 11 | 3 | z5 | 1 |

| 2 | HR | 12 | 4 | w5 | 2 |

| 2 | HR | 12 | 5 | w6 | 2 |

| 3 | MK | 13 | 6 | s7 | 3 |

| 4 | MIS | 14 | 7 | s8 | 4 |

| NULL | NULL | NULL | 8 | s9 | 51 |

| 5 | FD | 15 | NULL | NULL | NULL |

+------+----------+--------+------+------+--------+

9 rows in set (0.00 sec)

a的独有和b的独有

mysql> select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.id is null

-> union

-> select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null;

+------+----------+--------+------+------+--------+

| id | deptName | locAdd | id | name | deptId |

+------+----------+--------+------+------+--------+

| 5 | FD | 15 | NULL | NULL | NULL |

| NULL | NULL | NULL | 8 | s9 | 51 |

+------+----------+--------+------+------+--------+

2 rows in set (0.01 sec)

索引

索引的定义：

索引是帮助SQL⾼效获取数据的数据结构，索引的本质：数据结构

可以简单的理解为：排好序的快速查找数据结构

在数据之外，数据库系统还维护着满⾜特定查找算法的数据结构，这些数据结构以某种⽅式（引⽤）指向数据，这样就可以在这些数据结构上实现⾼级查找算法。这种数据结构，就是索引，下图就是⼀种⽰例：

⼀般来说索引也很⼤，因此索引往往以索引⽂件的⽅式存储在磁盘上

我们平常所说的索引，如果没有特别指明，⼀般都是指B树（多路搜索树，不⼀定是⼆叉的）结构组织的索引，

其中聚集索引，次要索引，复合索引，前缀索引，唯⼀索引默认都是使⽤B+树索引，统称索引，当然除了B+树这种类型的索引之外，还有哈希索引。

索引的优劣

1.优势

类似⼤学图书馆图书编号建索引，提⾼了数据检索的效率，降低数据库的IO成本

通过索引对数据进⾏排序，降低数据排序的成本，降低了CPU的消耗

2.劣势

实际上索引也是⼀张表，该表保存了主键与存在索引的字段，并指向实体表的记录，所以索引列也是占⽤空间的

虽然索引⼤⼤提⾼了查询速度，但是会降低更新表的速度，⽐如 update，insert，delete操作，因为更新表时，MySQL不仅要数据也要保存索引⽂件每次更新添加了索引的字段，都会调整因为更新所带来的键值变化后的索引信息

索引只是提⾼效率的⼀个因素，在⼀个⼤数据量的表上，需要建⽴最为优秀的索引或者写优秀的查询语句，⽽不是加了索引就能提⾼效率

索引分类

单值索引

唯⼀索引

复合索引

基本语法:

创建

create [unique] index indexName on mytable(cloumnname(length));

alter mytable add [unique] index [indexName] on (columnname(length));

删除

drop index [indexName] on mytable

查看

show index from table_name\G

有四种⽅式来添加数据表的索引