mysql性能调优（三）——列选取原则

1、列类型选择

      1）整型 > data、time > char、varchar > blob

           整型、date、time运算快

           char、varchar要考虑字符集的转换与时序的校对集，速度慢

           Blob无法使用内存临时表，每次都要去磁盘找

       2）长度够用就行

       3）避免用null：不利于索引且占用的索引更大

       4）Enum枚举类型

create table t5(

gender enum('male', 'female') not null default 'male'

)engine myisam charset utf8;

enum在内部是用整型来存储的

 加入新建一张表，查询其索引

explain select * from t3 where name='a' \G                 (来查看这个表如何使用索引)

 2、多列索引生效规则

      在讲多列索引生效规则前，先解释一下mysql的一些基本知识

     aa、 MYSQL引擎分为2中：MyISAM、InnoDB、MEMORY、MERGE。

      1）MyISAM是非事务性的存储引擎

      2）InnoDB是最流行的事务性引擎

      3）Memory存储引擎使用存在于内存中的内容来创建表，为非事务性的以及非持久性的存储引擎。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。

      4）Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

      对于4种引擎的更加详细的讲解可以翻看下面链接（因为我讲的主要是索引）

Mysql的四种引擎介绍

       bb、MYSQL内置的存储引擎对各种索引技术有不同的实现方式：B-树，B+树，R-树以及hash散列类型      

       其中，B-树是有2种节点的，一种是索引节点，另外一种是叶子节点，叶子节点是用来存储数据的，索引节点则是用来告诉用户存储在叶子节点中的数据顺序并帮助用户找到对应的数据

       B+树中底层数据是根据被提及的索引列进行排序的（即数据是排好顺序的），B+树还通过叶子节点之间的附加引用来优化扫描性能。

      B-树与B+树结构差不多，只不过B+树只有达到叶子节点才命中（B-树可以在非叶子节点命中）

      B+树的特性：
   （1）所有关键字都出现在叶子节点的链表中，叶子节点相当于存储数据的数据层。
   （2）不可能在非叶子节点上命中。
   （3）非叶子节点相当于是叶子节点的索引，叶子节点相当于数据层。

     cc、注意注意O(∩_∩)O哈哈~啦！重点来了

            mysql有4中引擎，但每一个存储引擎其索引使用的数据结构是不一样的，而不同数据结构的实现也是有不同的（B-tree在InnoDB和MyISAM中节点存储的实际数据不同）

	InnoDB	MyISAM
主码索引	B+树数据结构	B-树数据结构
非主码索引	B-树数据结构	B-树数据结构

InnoDB中的B-树结构实现和MyISAM中并不一样。在InnoDB中，非主码索引存储的是主码的实际值。得到主码 的索引值之后就可以通过主码索引（聚簇索引查看对应存储的数据data）

而MyISAM中，非主码索引存储的包含主码值的数据指针，查找数据的时候就需要“回行”查询（其主码索引指向的也是物理行）

讲了上面的内容之后，再说

 联合索引：index(a, b, c),其索引在查询的时候不一定要用全，但要按索引的顺序来（左前缀要求）

对于hash索引：hash索引是计算这行数据在哪里很快，但读取数据的时候很慢。因为hash计算的结果是随机的，随着id数量的增长，id对应的行在磁盘随意放置，所以读取会慢；hash不对范围进行优化；不对排序优化；无法利用前缀索引；必须回行，拿到数据位置，必须回到表中取数据

下面再补充几个知识点：

如何优化查询语句？

       看表咯，是InnoDB还是myisam，InnoDB节点下挂载的是实际的数据，而myisam存储的是物理行，再看看联合索引、是否允许索引覆盖（索引覆盖是比较快的） ，建立合适的索引并且要用到位，比如：是否符合多列索引的左前缀要求。用explain/profile去分析一下sql语句，尽量不要做全表扫描、不要让其生成临时、数据不要一次返回太多（返回太多就会写磁盘 太慢），主键的使用最好是整型的并且递增。