Mysql优化

之前小公司数据量比较小，没有积累相关的经验，面试了个大公司被问尿了。

问题：用没用过索引？有没有数据库优化经验？

先从索引的原理开始看起，索引调优属于结构调优。

优质文章传送门：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

我做下自己的重点总结：

1.myISAM引擎和InnoDB都使用B+Tree结构的索引。

2.B+Tree叶结点存储数据并指向相邻的叶结点方便范围查找。

3.myISAM的B+树叶结点存储的是数据地址，顾称为非聚集索引。

4.InnoDB的B+树叶节点直接存储的是数据，顾称为聚集索引。

5.InnoDB的要求表拥有唯一的主键，没有指明则找表内唯一性列，如果找不到就生成一个隐含字段，6字节，长整型。

6.InnoDB的辅助索引存储的是主键的值。

7.索引会增加查询效率降低存储效率，因为要维护索引，故加索引要适当。

了解到基本原理可以总结出几条优化建议：

1.主键的单调性，主键随机生成会导致维护B+树频繁的分裂调整，降低性能。

2.所有的辅助索引都会引用主键，所以主键太长会使辅助索引过大。

使用Explain 解析SQL语句：

参考博客传送门：http://blog.csdn.net/zhuxineli/article/details/14455029

 

看了很多sql优化的帖子，对于相同的理论有的说yes有的说no，还是直接看书吧。

以下摘自<高性能MySQL>

结构优化：

• 更小通常更好，使用正好能存储和表示数据的最小类型，小的数据使用更少的磁盘空间、内存缓存，需要的CPU周期越小。
• 简单就好，越简单的类型需要的cpu周期越小，比如比较整数代价小于比较字符串，这个大家都懂，尽量少用字符串。
• 尽量避免使用NULL，如果需要NULL最好初始值设为0，或特殊字符串，Mysql难以优化可空列的查询，使索引和索引统计和值更加复杂。可空列需要占用额外的存储空间（- -？）当可空列被索引时，每条记录都需要一个额外的字节。但是null到not null 性能提升很小。

1.整数

　　tinyint ,smallint ,mediumint,int ,bigint  分别占用8,16,24,32,64   范围-2(n-1)次~2(n-1)-1

　　整数有个signed 和unsigned   ,unsigned会使整数的范围变为0~255,因为把符号位也算进去了。

　　定义字宽int(1) 和int(10)并无存储上的区别，只是某些客户端上显示的位数。

2.实数

　　decimal 尽量少用，会产生额外的存储空间和计算开销，尽量使用整数存储金额。

3.字符串

　　varchar 可变长度，比固定长度的占用更少的存储空间，对性能是有帮助的，会用1～2字节保存列的最大长度，小于等于255(varchar(255))时是一个字节，大于255时(varchar(1000))用两个字节。

　　varchar(1) 表示可以存储一个字符 ：可以是a，可以是1，可以是一。这是我一直困惑的，也是傻，一试就知道了。

　　ps：慷慨是不明智的,存储"hello" varchar(5),varchar(100)虽然占用的存储空间一样，但是较大列会使用较大的内存，mysql通常会分配固定大小的内存块来保存值，这对排序或使用基于内存的零时表尤其不好。只分配正好的空间 name varchar(4) tel varchar(11)。

4.blob和text 

　　blob不常用 ，mysql不会按照字符的完整长度排序，只会按照max_sort_length规定的排序前几位，mysql不能索引这些数据类型的完整长度，也不能为排序索引，是不是可以理解为就算排序了也不能索引？

b树索引的局限性：

1.联合索引必须是从做到又连续  index(a,b,c) 等价于 index(a),index(a,b),index(a,b,c) 

2.select * from table where b=1,a Like "b%",c=1 ;  其中c不会用到索引，引擎不会优化范围查询后的列

3.a Like "%b" 是不会用到索引的，最左前缀定理。

hash索引　　

建立在hash表上的，用于某一列的精确查询，HashMap也是。

InnoDB有一个特别的功能，自适应哈希索引，当InnoDB注意到一些值被很频繁访问，就会在b-tree顶端为这些值建立hash索引，人工智能，66666

前缀索引和索引的选择性：

　　有时候索引很长的字符串会使索引很大很慢，索引字符串的前几位可以节约空间并能提高性能，使索引的空间减小但是会降低选择性，选择性是不重复索引值和表的所有行t的比值，范围是1/t～1之间。高选择性的索引好处：可以在查找匹配时过滤掉多余的行，唯一的索引选择性为1。

select  count(distinck left(city ,3))/count(*) as sel3,

　　　　count(distinck left(city ,4))/count(*) as sel4,

　　　　count(distinck left(city ,5))/count(*) as sel5,

　　　　count(distinck left(city ,6))/count(*) as sel6,

　　　　count(distinck left(city ,7))/count(*) as sel7

from city;

可以看出前7位的选择性最好，选择率提升的幅度减小。

alter table city add key(city(7))

前缀索引的缺点不能使用group by 和order by。

后缀索引有时候也有用，可以用来查询电子邮件之类的。

聚集索引：

1.缺点严重依赖插入顺序，如果没有按照主键插入最好在插入之后使用optimize table重新组织一下表。

2.第二索引可能比预想的大，包含了被应用行的主键列。

3.接上，因为包含了主键的列 查询需要两次。

 

转载于:https://www.cnblogs.com/fastLearn/p/6752407.html