| MySQL数据库 | 您所在的位置：网站首页 › ip分组的结构图 › MySQL数据库 |
MySQL数据库
|
MySQL数据库---笔记3
一、储存引擎1.1、MySQL体系结构1.2、存储引擎简介1.3、存储引擎特点1.3、存储引擎选择
二、索引2.1、索引概述2.2、索引-结构2.2.1、索引结构-B-Tree2.2.2、索引-结构-B+Tree2.2.3、索引-结构-hash2.2.4、索引-结构-思考题
2.3、索引-分类思考题
2.4、索引-语法2.5、索引-性能分析2.5.1、索引-性能分析-查看执行频次2.5.2、索引-性能分析-慢查询日志2.5.3、索引-性能分析-show profiles2.5.4、索引-性能分析-explain
2.6、索引-使用规则2.6.1、最左前缀法则2.6.2、索引失效情况一2.6.3、索引失效情况二2.6.4、SQL提示2.6.5、覆盖索引&回表查询2.6.6、前缀索引2.6.7、单列索引&联合索引
2.7、索引设计原则
三、SQL优化3.1、插入数据3.2、主键优化3.3、order by优化3.4、group by优化3.5、limit优化3.6、count优化3.7、update优化
一、储存引擎
1.1、MySQL体系结构
MySQL体系结构图
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。 默认存储引擎：InnoDB 在创建表时，指定存储引擎 CREATE TABLE表名( 字段1 字段1 类型[COMMENT 字段1 注释], ...... 字段n 字段n 类型[COMMENT 字段n 注释] )ENGINE = INNODB[COMMENT 表注释]; 查看当前数据库的存储引擎 SHOW ENGINES; 1.3、存储引擎特点 InnoDB介绍 InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎。 特点 DML操作遵循ACID模型，支持事务;行级锁，提高并发访问性能;支持外键FOREIGN KEY约束，保证数据的完整性和正确性;文件 xxx.ibd: xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件存储该表的表结构(frm.sdi)、数据和索引。 参数: innodb_file_per_table
一个Extent（区）的大小固定为 1M 一个 Page（页）的大小固定为 16K 一个区包含64个页 MyISAM 介绍 MyISAM是MySQL早期的默认存储引擎 特点 不支持事务，不支持外键支持表锁，不支持行锁访问速度快文件 xxx.sdi :存储表结构信息 xxx.MYD:存储数据 xxx.MYI:存储索引 Memory 介绍 Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响只能将这些表作为临时表或缓存使用。特点 内存存放hash索引（默认) 文件 xxx.sdi:存储表结构信息存储引擎的区别 特点InnoDBMyISAMMemory存储限制64TB有有事务安全支持--锁机制行锁表锁表锁B+tree索引支持支持支持Hash索引--支持全文索引高低N/A空间使用支持(5.6版本之后)支持-内存使用高低中等批量插入速度低高高支持外键支持-- 1.3、存储引擎选择 InnoDB：是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。
MySQL 8.0 对于密码格式有以下要求： 密码长度必须大于等于 8 个字符；密码必须包含大小写字母、数字和特殊字符；特殊字符至少包括 !@#$%^&*()_±=[]{}|;':",./?;不允许出现空格字符。如果修改的密码不符合上述要求，系统会提示密码不符合要求，并且不允许设置。需要注意的是，为了保证数据库的安全性，在设置 MySQL 8.0 的密码时，应该避免使用过于简单的口令，以免被攻击者轻易破解。建议使用强密码生成器来生成随机复杂的密码，并妥善保管密码信息。另外，为了提高安全性，尽量避免使用相同的密码同时连接到多个数据库服务器。 该错误提示为“拒绝访问”，说明 MySQL 数据库用户没有足够的权限或者用户名或密码输入错误。常见的解决方法如下： 首先，确认用户名和密码正确。如果忘记了密码，可以使用以下命令来修改密码： mysql -u root -p ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码'; FLUSH PRIVILEGES; exit; 如果用户名和密码正确，那么请确认 MySQL 是否允许远程连接。在 MySQL 8.0 中，默认情况下只允许本地连接。可以通过以下步骤开启远程连接： 使用 root 用户登录 MySQL 服务器；输入以下命令查看当前用户和主机： SELECT user,host FROM mysql.user; 如果没有可用于远程连接的用户，请添加新用户。例如： CREATE USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'user'@'%'; FLUSH PRIVILEGES;注意，在上述示例中，“%”表示允许所有主机远程连接。如果指定特定主机，请使用相应的 IP 地址或主机名； 在 MySQL 服务器上检查防火墙设置，确保允许 MySQL 数据库的端口通过防火墙。默认情况下，MySQL 数据库使用 3306 端口。 如果以上方法均无效，则可能是由于安全组或防火墙等其他网络安全策略导致，请检查相关设置。总之，需要仔细排查错误原因，逐步解决问题。 2.1、索引概述索引(index)是帮助MysQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。 索引的优缺点 优势劣势提高数据检索的效率，降低数据库的IO成本索引列也是要占用空间的。通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。 2.2、索引-结构MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包括以下几种： 索引结构描述B+Tree索引最常见的索引类型，大部分引擎都支持B+树索引Hash索引底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询R-tree(空间索引)空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少Full-text(全文索引)是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES索引在不同引擎当中的支持情况 索引InnoDBMyISAMMemoryB+tree索引支持支持支持Hash索引不支持不支持支持R-tree索引不支持支持不支持Full-text5.6版本之后支持支持不支持我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。 2.2.1、索引结构-B-Tree 二叉数 与 红黑树
二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。 B-Tree（多路平衡查找树）以一颗最大度数〈max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key，5个指针):
插入100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250数据为例。 MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。  Hash索引特点
Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，, |
备注：上述二叉树索引结构的只是一个示意图，并不是真实的索引结构
相当于B-Tree区别：
【本文地址】