• 1.合理建表
  • 1.1定长字段
  • 1.2非定长字段
  • 1.3定长与非定长对查询的影响
• 2.字段类型的优先级
• 3.使用联合索引
  • 3.1 索引的作用
  • 3.2 索引建立的一些原则
  • 3.3 为何使用联合索引
  • 3.4 聚簇索引与非聚簇索引
  • 3.5 索引覆盖
  • 3.6 理想的索引
  • 3.7 索引碎片与维护
• 4.sql语句优化原则
  • 4.1sql语句优化思路

1.合理建表

​ 合理的表结构对性能的影响至关重要。建表时，定长的字段尽可能放在一张表中，长度不确定的或类型如：text，blob则单独建表，在查询时可提高效率。那何为定长字段和非定长字段呢？

1.1定长字段

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` char(32) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `introduce` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

​ 上面的表中有五列，分别是id，username，password，birthday，introduce；类型则是有int，char，varchar和date四种。其中id，username，password都给定了初始化长度，当输入的数据小于给定长度则会自动填充，当输入的长度大于给定长度则会自动截取给定的长度，这便是定长字段；而date为时间格式，只能有几个特定的格式，所有不需要设置长度，每种格式都是定长的，一个表中只会用一种格式的时间，所以birthday也是定长字段。

1.2非定长字段

​ 上面表中的introduce列的类型是varchar，虽然给定了初始长度，但当插入的数据长度小于给定的初始长度时，插入多长就存储多长，不会去自动填充；当所插入的字符串超出它的长度时，视情况来处理，如果是严格模式，则会拒绝插入并提示错误信息，如果是宽松模式，则会截取然后插入 。因此introduce就属于非定长字段。

1.3定长与非定长对查询的影响

​ 当一个表中的字段都是定长时，也就意味着该表中的每一条数据也都是长度相同的，在磁盘上存储时所跨的扇区也是一样的，这样我们在查询某一条数据时，只需要跨过一定的扇区拿数据就能得到，显然比一条一条的向下查找效率高得多。

​ 当一个表中既有定长字段又有非定长字段时，该表中每一条数据的长度不再是相同的，在磁盘上存储时所跨的扇区也不再一样，这时我们在查询某一条数据时，只能一条一条的往下查，性能便会大受影响。

​ 因此，出于对性能的考虑，建表时，定长的字段尽可能放在一张表中，长度不确定的单独建表。

2.字段类型的优先级

​ 整型 ＞ date，time ＞ enum，char ＞ varchar ＞ blob，text

​ 能使用整型的就不使用别的类型，尽量使用优先级靠前的，这样能提升效率。理由可以简单的理解为：数字1，2，65这些是全世界通用的，不存在编码问题；而时间和日期是有着一定规则的数字组合；特殊字符如汉字等需要进行特殊的编码处理，使用的越多自然也越影响性能。

3.使用联合索引

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` char(32) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `introduce` varchar(255) DEFAULT NULL,
  KEY idusername(`id`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

​ 上面的创建表的语句中，KEY idusername(id,username)将id和username一起使用了联合索引，为什么要这样做呢？

3.1 索引的作用

• 提高查询速度

• 提高排序速度
• 提高分组速度

3.2 索引建立的一些原则

1. 选择 where,on,group by,order by 中出现的列
2. 选择较小的数据列，减少索引文件大小
3. 使用联合索引
4. 不能滥用索引
5. 索引不包含有NULL值的列（若联合索引包含NULL值的列则整个组合索引无效）

3.3 为何使用联合索引

​ 索引既能提高查询速度，又能提高排序和分组速度，故使用索引会大大提升性能；联合索引能减低索引文件的大小，使用速度也优于多个单列索引 ，因此联合索引相对于多个单列索引有一定优势 。

3.4 聚簇索引与非聚簇索引

​ 聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。

​ mysql中的MyISAM : 是旧版本mysql的默认引擎，现在默认引擎是InnoDB。MyISAM引擎的主要特点就是快，没有事务处理操作，也不支持外键操作。适合于多读取插入，少更新删除的操作表。存储数据分成三个文件：.frm(存储表定义) .MYD(存储数据) .MYI(存储索引)。MyISAM 使用的是非聚簇索引，它的次级索引和主索引都指向物理行（磁盘位置）。

​ mysql中的InnoDB ：是新版本mysql的默认引擎，支持事务处理和外键，但是其缺点就是慢了些。存储方式分为两种：1.共享表空间存储。[.frm(表结构) 和 innodb_data_home(数据)和innodb_data_file_path(索引)] 2.多表空间存储。 [.frm(表结构) 和 .idb（数据）]。适用于对于事务由较高要求的表的创建。InnoDB 使用的是聚簇索引，它的主索引文件上直接存放该行的数据，次级索引指向对主键的引用。

3.5 索引覆盖

​ 查询索引时，就得到了想要的数据，无需回行，称为索引覆盖。

​ 简单的理解如：在进行用户登录时，将用户名和密码建立联合索引，在查到用户名时就带有密码，这样无需回行便可就行密码验证。利用索引覆盖建立联合索引时应当结合实际情况，在大量的数据分析后才有意义。

3.6 理想的索引

理想的索引应满足下面的要求：

• 查询频繁

• 区分度高

• 长度小

• 尽量覆盖常用的查询字段

​ 当然，在不同的实际情况中可能还需要满足其他要求，查询的频繁需要分析大量的用户习惯获得，长度也要经过相应的测试来确定，这里只是提供一些理论上的分析，具体应用还得考虑实际情况。

3.7 索引碎片与维护

​ optimize table 表名，也可以修复。

​ 但要注意，修复表的数据及索引，就会把所有的数据文件重新整理一遍，使之对齐，如果表的行数比较大，也是非常消耗资源的操作。所以，不能频繁的修复。如果是更新操作很频繁，可以按周/月来修复，如果不频繁，可以更长周期来修复。

4.sql语句优化原则

​ sql语句的时间主要有等待时间和执行时间，而执行时间是影响sql语句执行快慢的重要因素。sql语句的执行主要有查找和取出，取出数据并不消耗太多时间，大部分时间还是用来查找了。

​ 如何查询的快？

• 查询的快 —-> 联合索引

• 取得快 —-> 索引覆盖

• 传输的少 —-> 更少的行和列​

在进行大量数据操作时，为了提高性能，可以采用切分查询或分解查询，将大量数据拆成多次，按逻辑把多表联查分成多个简单的sql。

4.1sql语句优化思路

不查 —-> 少查 —->高效的查

不查：通过业务逻辑来计算，比如论坛的注册人数，可以根据前几个月统计的每天注册人数来估算。

少查：尽量精准数据，少查行。

高效：尽量在索引上查询。

取数据时，取尽量少的列，不建议取所有的列。