Mysql引擎、索引与Sql优化

一、Mysql数据库架构图

MySQL DBMS （MySQL Database Management System） 数据库管理系统

图片来源于网络

二、存储引擎介绍

2.1、ISAM

1）特点：读取操作的速度很快，占内存和存储资源相对较少；
2）不足：不支持事务处理；也不能够容错；（所以需要经常备份所有实时数据）

2.2、MyISAM

1）ISAM 扩展格式，5.5 之前版本的默认数据库引擎；
2）与ISAM 相比：支持表格锁定机制，来优化多个并发的读写操作，但经常需使用 Optimize Table 命令清理空间
3）不足：不支持事务；表损坏后无法恢复数据；

2.3、InnoDB

1）特点：支持事务；实现外键；锁定机制的改进；
2）与myisam相比：
2.1、InnoDB 支持事务，MyISAM 不支持
2.2、InnoDB 支持外键，而 MyISAM 不支持，对一个包含外键的 InnoDB 表转为 MYISAM 会 失败；
2.3、 Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；
2.4、InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引 效率很高。而 MyISAM 是非聚集索引，数据文 件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的；
2.5、. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很 快；

2.4、Memory

1）将数据存储在内存，，仅仅存放了一个表结构相关信息 的.frm 文件在磁盘上面；
2）MySQL Crash 或者主机 Crash 之后，Memory 的表就只剩下一个结构了;
3）由于是 存放在内存中，所以 Memory 都是按照定长的空间来存储数据的，而且不支持 BLOB 和 TEXT 类型的字段；

2.5、NDBCluster

主要用于 MySQLCluster分布式集群环境，Cluster 是 MySQL 从 5.0 版本才开始提供的新功能；

2.6、Merge

其实现了对结构相同的 MyISAM 表， 通过一些特殊的包装对外提供一个单一的访问入口，以达到减小应用的复杂度的目的。要创 建 MERGE 表，不仅仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一 致；

2.7、FEDERATED

FEDERATED 存储引擎所实现的功能，和 Oracle 的 DBLINK 基本相似，主要用来提供对远 程 MySQL 服务器上面的数据的访问接口。如果我们使用源码编译来安装 MySQL，那么必须 手工指定启用 FEDERATED 存储引擎才行，因为 MySQL 默认是不起用该存储引擎的。

2.8、ARCHIVE

ARCHIVE 存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。 ARCHIVE 表不支持索引，通过一个.frm 的结构定义文件，一个.ARZ 的数据压缩文件还有一 个.ARM 的 meta 信息文件。由于其所存放的数据的特殊性，ARCHIVE 表不支持删除，修改操 作，仅支持插入和查询操作。锁定机制为行级锁定；

2.9、BLACKHOLE

类似于 unix 系统下面的“/dev/null”设备一样，不管我们写入任何信息，都是有去无回的个“黑洞”；

2.10、CSV

CSV 存储引擎实际上操作的就是一个标准的 CSV 文件，他不支持索引。起主要用途就是 大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而 CSV 文件是很多软件 都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张 CSV 表，然后将生 成的报表信息插入到该表，即可得到一份 CSV 报表文件了；

2.11、存储引擎管理

2.11.1、查看存储引擎

show engines;

2.11.2、查看当前存储引擎

show variables like ‘%storage_engine%’;
也可以在 配置文件中查看，windows中 my.ini文件， Linux中my.cnf文件

2.11.3、查看表所用的存储引擎

show create table table_name;

2.11.4、建表指定存储引擎

create table table_name (column_name column_type) engine = engine_name;

2.11.5、修改存储引擎

alter table table_name engine=engine_name;

2.11.6、修改默认的存储引擎

在配置文件中修改下述内容： default-storage-engine=INNODB

三、索引介绍

3.1、种类

3.1.1、B-Tree 索引

所有的索引节点都按照 balance tree 的数据结构来存储， B-tree 结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。（尽量深度转为宽度，这样节点搜索路径更短）

3.1.2、Full-text 索引

全文索引：主要是为了解决在 我们需要用 like 查询的低效问题。只能解决’xxx%’的 like 查询。如：字段数据为 ABCDE，索 引建立为- A、AB、ABC、ABCD、ABCDE 五个；（存储结构也是 b-tree）

3.2、优点

1）索引可以提高系统的查询性能（直接通过物理地址查询）
2）可以加速表和表之间的连接
3）索引可以在查询的过程中，使用查询优化器，提高系统的性能

3.3、缺点

1）创建和维护索引要耗费时间，且随数据量的增加而增加
2）索引需要占物理空间
3）增删改时，索引也要动态的维护，效率变低

3.4、建议

1）查询多的列加索引，可提高速度；（增删改相反）
2）外键加索引，可以加快连接的速度；
3）where子句中的列上面创建索引，加快条件的判断速度；
4）有很少数据值的列也不应该增加索引；
5）尽量使用短索引，可以提高查询速度、节省磁盘空间，CREATE INDEX index_name ON table_name (column(length))
6）like 语句操作，like “%aaa%” 不会使用索引，而 like “aaa%”可以使用索引；
7）不要在列上进行运算，会导致索引失效而进行全表扫描；
8）一张表的索引数不要过多（如6个）；

3.5、索引管理

在 MySQL 中，对索引的查看和删除操作是所有索引类型通用的

3.5.1、普通索引

最常用索引，无限制，MyIASM 中默认的 BTREE 类型的索引；
1）创建索引

CREATE INDEX index_name ON table_name (column(length)) 
ALTER TABLE table_name ADD INDEX index_name (column(length)) 
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , INDEX index_name (title(5)))

2）查看索引

SHOW INDEX FROM [table_name] 
SHOW KEYS FROM [table_name] # 只在 MySQL 中可以使用 keys 关键字。

3）删除索引

DROP INDEX index_name ON talbe_name 
ALTER TABLE table_name DROP INDEX index_name 
ALTER TABLE table_name DROP PRIMARY KEY

3.5.2、唯一索引

索引列的值必须唯一，但允许有空值， 如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似
1）创建索引

CREATE UNIQUE INDEX index_name ON table_name (column(length)) 
ALTER TABLE table_name ADD UNIQUE index_name (column(length)) 
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , UNIQUE index_name (title(length)))

3.5.3、全文索引（FULLTEXT）

FULLTEXT 索引仅可用于 MyISAM 表；
对于较大的数据集, 其搜索速度更快，但生成全文索引非常消耗时间和、硬盘空间；
1）创建索引

CREATE FULLTEXT INDEX index_name ON table_name(column(length)) 
ALTER TABLE table_name ADD FULLTEXT index_name( column) 
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , FULLTEXT index_name (title))

3.5.4、组合索引（最左前缀）

1）创建索引

CREATE INDEX index_name ON table_name (column_list)

四、Sql优化

4.1、避免全表扫描

1）尽量避免在 where 子句中对字段进行null 值判断；
2）尽量避免在 where 子句中使用!=或<>操作符；
3）尽量避免在 where 子句中使用 or 来连接条件，可使用union all；
4）in 和 not in使用不当（可以考虑exists）
5）like查询使用不当，如like ‘%abc%’，可使用like ‘abc%’；
6）尽量避免在 where 子句中对字段进行表达式操作；
7）星号*查询；

4.2、索引失效

1）不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则可能索引失效
2）组合索引使用：在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一 个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让 字段顺序与索引顺序相一致。
3）当索引列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段 sex，male、female 几乎各 一半，那么即使在 sex 上建了索引也对查询效率起不了作用。

4.3、其它

1）表连接建议不超过 5 个（否则可考虑表格的设计）；
2）表连接方式使用外联优于内联；
外连接有基础数据存在。如：A left join B,基础数据是 A
A inner join B，没有基础数据的，先使用笛卡尔积完成全连接，在根据连接条件得到内 连接结果集。
3）大数据量级的表格做分页查询时，如果页码数量过大，则使用子查询配合完成分页逻辑。
Select * from table limit 1000000, 10
Select * from table where id in (select pk from table limit 100000, 10)