mysql相关知识点

mysql相关知识点

1.mysql数据存储在什么地方
磁盘
2.查询数据比较慢，一般情况下卡在那里？IO
提高IO效率 1.次数 2.量
3.去磁盘读取数据的时候，是用多少读取多少吗？
磁盘预读
4.索引存储在那里
磁盘，查询数据的时候会优先将索引加载到内存中
5.索引在存储的时候需要什么信息？需要存储什么字段
key：实际数据行中存储的值 文件地址
6.这种格式的数据要使用什么样的数据结构来进行存储
k-V 哈希表，树（二叉树，红黑树，AVL树，B树，B+树）
7.mysql的索引系统中不是按照刚刚说的格式存储的，为什么？
1.哈希冲突会造成数据散列不均匀，会产生大量的线性查询，比较浪费时间
2.不支持范围，当进行范围查询的时候，必须要挨个遍历
3.对于内存空间的要求比较高，
如果是等值查询，那么非常块
在mysql中有没有hash索引？
1.memory存储引擎使用的是hash索引
2.innodb支持自适应hash

树 二叉树 BST AVL 红黑树 B树 B+树
Binary Search Tree
插入树据的时候必须有序，左子树必须小于根节点，右子树必须保证大于根节点
使用二分查找来提高查询效率
如果数据插入是递增或者递减的顺序的话，怎么办

为了让树平衡引入AVL 平衡二叉树
经过左旋或者右旋让树平衡起来
为了保证平衡，在插入数据的时候必须要选择，保证插入性能的损失来弥补查询性能的提升

如果读写频率一样多，该怎么办？
红黑树
最长子树只要不超过最短子树的两倍即可查询性能和插入性能近视取得一个平衡
随着数据的插入，发现树的深度会变深，树的深度越深，意味着IO次数越多，影响数据读取的效率
红黑树的性质
性质1：每个节点要么是黑色，要么是红色
性质2：根节点是黑色
性质3：每个叶子节点（NIL）是黑色
性质4：每个红色节点的两个子节点一定都是黑色。不能有两个红色节点相连
性质5：任意一节点到每个子节点的路径都包含数量相同的黑节点，俗称：黑高
从性质5可以推出
如果一个节点存在黑子节点，那么该节点肯定有两个子节点

红黑树并不是一个完美平衡二叉树，从图上可以看出，根结点P的左子树显然比右子树高。但左子树和右子树的黑色结点
的层数是相等的，所以我们叫红黑树这种平和为黑色完美平衡

B树 把原来的有序二叉树变成有序多叉树

B树->叶子节点才存储数据，非叶子节点不存储数据->B+树

sql优化的一些方法
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。    
    
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：    
select id from t where num is null    
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：    
select id from t where num=0    
    
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。    
    
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：    
select id from t where num=10 or num=20    
可以这样查询：    
select id from t where num=10    
union all    
select id from t where num=20    
    
5.in 和 not in 也要慎用，否则会导致全表扫描，如：    
select id from t where num in(1,2,3)    
对于连续的数值，能用 between 就不要用 in 了：    
select id from t where num between 1 and 3    
    
6.下面的查询也将导致全表扫描：    
select id from t where name like '%abc%'    
    
7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：    
select id from t where num/2=100    
应改为:    
select id from t where num=100*2    
    
8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：    
select id from t where substring(name,1,3)='abc'--name以abc开头的id    
应改为:    
select id from t where name like 'abc%'    
    
9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。    
    
10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。    
    
11.不要写一些没有意义的查询，如需要生成一个空表结构：    
select col1,col2 into #t from t where 1=0    
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：    
create table #t(...)    
    
12.很多时候用 exists 代替 in 是一个好的选择：    
select num from a where num in(select num from b)    
用下面的语句替换：    
select num from a where exists(select 1 from b where num=a.num)    
    
13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。    
    
14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，    
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。    
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。    
    
15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。    
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。    
    
16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，    
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。    
    
17.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。