mysql总结
~~# 基本sql语句
1.基本SQL语句
1.查询
SELECT * FROM tableName ORDER BY fieldnames DESC; (ASC|DESC)
默认情况下，即不指定的时候，
innodb，会根据主键大大小，由小到大；
myisam，就根据数据插入顺序先后来排；
再者，有时候会根据索引把数据查询出来，
默认排序结果可能不一定符合要求。
是否指定排序方式，看情况考虑吧。
SELECT fieldNames FROM tableName where 范围 Group BY fieldnam Having 范围
Group by 分组查询
Having 条件，此处最好别用sql函数，似乎也不允许，复杂条件尽量在前面完成。
SELECT DISTINCT field from table where 范围
DISTINCT 非重复记录
2.插入
INSERT INTO table_name(column1,column2,column3,…)
VALUES (value1,value2,value3,…) ;
3.更新
UPDATE table_name
SET column1=value1,column2=value2,…
WHERE some_column=some_value;
4.删除
DELETE FROM table_name WHERE some_column=some_value;
delete记得带条件，否则会删除全表记录
2.其他sql语句
LIKE 操作符
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
where name like ‘%入%’ 字符串，单引号
IN 操作符
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,…);
in 后可跟子查询
**inner join(等值连接，内连接)： 返回两个表中联结字段相等的行。
left join(左联接)： 返回包括左表中的所有记录和右表中联结字段相等的记录。
right join(右联接)： 返回包括右表中的所有记录和左表中联结字段相等的记录。
**
UNION ，创建视图，BETWEEN 操作符
SQL函数，Avg() Count() Max() Min() Sum()
建表，等。。。。。
参考
1基础知识点.https://blog.csdn.net/wolflikeinnocence/article/details/80099283
2.百度https://www.baidu.com
索引-排好序的数据结构
理解：对数据进行组织和排序-------排好序的数据结构
常见索引结构与我的理解：
Hash
散列，通过散列计算，确定数据的存储地址，
会有hash冲突，但能尽量避免，可完成O(1)的查找，但不支持范围查找。
排序二叉树（二叉查找树）BST
二叉树，对任意节点，均满足
所有左子树的值 < 节点值 < 所有右子树值。
查询时，根据值的比较，确定走哪一棵子树。
平衡二叉树ALV
对排序二叉树的改进，防止其退化成类似于直链表的那种较差情况。每当数据插入、删除时，要通过左旋、右旋等操作，以保证任意节点的左、右子树高度差 <= 1。为保证这一特性，每次插入、删除节点时，可能要树进行多次调整，性能消耗大！
红黑树 RED-BLACK-TREE
对ALV的优化，对树的要求没有ALV那么严格。
会对非叶节点进行着色，使其变成红、黑节点。
插入、删除节点时，通过变色、左旋、右旋三种操作，在至多两次内完成数的调整。
B树 B+树
两者都是多叉树，即多分支。
主要区别在于
1.
B树 上层进行比较时，每个节点除了key值外还会附带整个节点的完整信息，每一个节点只会出现一次。而B+树，上层只有节点的key值信息，只有在最底层的叶子节点，才能找到节点的完整信息，而且所有节点数据，最终都会保存在叶子节点中、
2
B+树最底层的叶子节点，会成为一个排好序的链表结构(可改进成双向链表)。
—支持范围查找
B树
B+树
红黑树具体1
红黑树具体2
各种树结构的总结~~
存储引擎MyISAM和InnoDB
事实上，大家不要怕这两个概念，可以把它简单的理解成mysql对数据的两种组织方式。在我看来，正是因为对数据的组织方式的不同，造成了这两种存储引擎在 查询速度、事务支持、锁粒度等方面的差异。
Mysql存储数据时，这两种存储引擎都会创建 .frm文件，存储表结构。
区别在于：
MyISAN存储引擎会将数据和索引分成两个文件进行存储(数据：.MYD ，索引：.MYI)。而InnoDB存储引擎会将数据和索引合并成一个文件进行存储( .ibd)。
表结构----ENGINE=InnoDB表示选择的存储引擎，每张表都可以使用不同的存储引擎
c盘下mysql目录及存储的文件-其中db_online_shop是数据库名
MyISAM和InnoDB性能上的区别主要在于
1.查询速度MyISAM 优于 InnoDB
2.MyISAM不支持事务，InnoDB支持
3.MyISAM只支持到表级锁，InnoDB支持到、页锁、间隙锁、行级锁等
4.Mysql默认使用InnoDB
非聚集索引和聚集索引
mysql中，index和key都称为索引
聚集索引和非聚集索引的区别主要就是看索引与数据是否存储在一个文件上。
MyISAM将索引与数据以文件分开，B+树的叶子节点最终只存储了该条记录在磁盘上的位置。
InnoDB将数据与索引放在一起，B+树叶子节点最终直接存储记录的值
为Col1列创建索引(非聚集索引)：
为Col1列创建索引(聚集索引，数据和前面相同)：
主键索引和非主键索引
MyISAM存储时-索引与数据分开
对主键索引和非主键索引在本质上没有区别，只是进行排序的字段发生改变，最终的叶子节点还是只存储了该条记录在磁盘上的位置。
为主键Col1建立主键索引
为Col1列建立辅助索引
InnoDB存储时-索引与数据在一个文件
- 主键索引
InnoDB将索引和数据是存入同一个.idb文件中的，其使用的是聚集索引。在叶子节点中，直接存储的就是每条记录的完整信息。
2. 辅助（非主键）索引：
在叶子节点中，存储的是每条记录的主键ID，通过这种索引获取记录时，需要根据查到的主键ID再去主键索引上进行查找，也就是回表。
ps：
1.一般而言，InnoDB表都有一个自增主键，其就是用于
主键索引的创建。对InnoDB表而言，必须创建一个索引，来完成数据的组织，如果表没有自增主键，其会找一个主键字段(主键唯一)创建主键索引，如果还是找不到，InnoDB会默认维护一个隐藏列，用于主键索引的创建
2.推荐使用一个整型的自增主键就是为了方便主键索引的创建。 整型：是因为整型的比较比字符串更快。自增：是为了保证在新增数据时，对原索引的影响尽量小，尽量小的对B+树进行分裂、等调整操作。
3.非主键索引结构叶子节点存储的是主键值
是为了 (1.提高并发的性能 2.保证一致性 3.节省存储空间)
联合索引—即多个字段的索引
和普通的索引没有本质上的差别，排序时使用的是多重排序，为了进行排序，树上的每一节点都要携带排序信息，叶子节点才存储记录的完整值。联合索引思想虽然没本质改变，但其在mysql中的使用时最多的，有很大的使用空间。
B+树存储效率分析
InnoDB存储引擎默认一页大小为16KB，
假设每个节点大小也为16KB(一次只需从磁盘加载InnoDB的一页)
假设每个key占8B，每个索引占8B，则每个节点可存储
16KB / (8B+8B) 约 1000 对 key-索引
假设每条记录占1KB = 1024 字节，一般而言足够了吧。
则每个节点可存放16KB / 1KB ，即16条记录
三层可存放
1000 * 1000 * 16 = 1600 万条记录
因此，一般B+树，2-3层即可满足需求。
简单sql优化
原则
1.加索引，联合索引
2.牢记最左前缀原则
3.牢记联合索引的数据结构、
使用explain工具分析sql的执行计划
eg:
set session optimizer_switch=‘derived_merge=off’; #关闭对衍
生表的优化
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
了解各字段(id,select_type,table,type,possible_keys,keys,keys_len,ref,rows,exreas)的含义，并理解其值的含义，
从中分析出如何对查询进行优化
使用trace工具查看各索引的具体代价
eg:
1 mysql> set session optimizer_trace=“enabled=on”,end_markers_in_json=on; ‐‐开启trace
2 mysql> select * from employees where name > ‘a’ order by position;
3 mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
ps:
即使查询的字段列、条件字段列命中索引，也不一定会走索引。
因为有时候会有回表等操作，造成更大的开销，但这也并不意味着有回表就一定会有更大的开销。mysql内对会先对使用各种索引、不使用索引、做一个代价评估，再决定具体的查询方式
常见易优化的普通sql语句:
带where条件的sql查询、order by、group by，分页、inner join、 count()
总体而言，还是依靠最左前缀法则和联合索引的数据结构理解
数据库范式
第一范式：是表，列不能再分
第二范式：非码属性必须完成依赖于候选码----即候选码全码才能确定一条记录。
消除非码属性对主键的部分函数依赖
第三范式：非主属性不依赖于其它非主属性
消除非码属性对主键的传递函数依赖
BC范式：任何非码属性不能对主键的子集依赖
事务特性ACID
A:原子性
C:一致性
I:隔离性
D:持久性
并发引起的问题
丢失修改
脏度
不可重复读
幻读
数据库隔离级别
读未提交
读已提交 oracle
可重复读 mysql
可串行化
MVCC 多版本并发控制
为每个事务生成一个数据库快照，提升读的并发能力。高性能mysql这本书的解释是MVCC是行级锁的一个变种，使大多数的读操作可以不用加锁，写操作也只锁定必要的行。。
mysql InnoDB实现MVCC的方式（以可重复读为例）
1.为每条记录添加隐藏字段隐式字段
2.undo_log 日志
3.Read View(读视图)
个人理解：
先理解两点：1.把数据库的每条记录都理解成由事务产生的。2.每个事务真正开始时，mysql都会为其分配一个递增的事务ID
大致流程是这样的：
每条记录都有隐藏字段（1.最近对其更新的事务ID，2回滚指针，指向这条记录的上一个版本 3.是否删除的标志字段. 4.隐藏的自增主键—没有主键时用来创建索引）
每个事务真正开始时(执行sql语句后)，系统分配给其一个递增事务ID,并生成一个数据库快照，并产生读视图(Read View)—里面会记录当前活跃的事务ID等信息–并且在事务期间这个读视图是不会改变的。
每个事务修改、删除数据时(插入的情况不一样)，是在undo_log中添加一条记录。
每个事务进行查询时，是进行快照查，通过记录的回滚指针、undo_log日志、ReadView(读视图)，去日志中查询到对自己可见的该条记录的版本，最终保证查询的结果和快照生成时的数据是一致的，以保证可重复读。
读以提交的隔离级别也大致是这个流程。
扫一扫
322
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
