mysql数据库和sql优化

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源（系统资源占用很低）。ISAM的两个主要不足之处在于，它不支持事务处理（容易出现脏数据），也不能够容错。如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

注意：使用ISAM注意点：必须经常备份所有实时数据。

2.2 MyISAM

MyISAM是MySQL的ISAM扩展格式（MySQL5.5之前版本的缺省数据库引擎）数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间（释放表格占用的空间）。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

MyISAM引擎使用注意：必须经常使用Optimize Table命令清理空间；必须经常备份所有实时数据。工具有用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。不支持事务。数据越多，写操作效率越低。因为要维护数据和索引信息。（索引列越多，相对效率越低。）

如果使用该数据库引擎，会生成三个文件：

.frm:表结构信息

.MYD:数据文件

.MYI:表的索引信息

MyISAM底层是怎样做到查询快的？

因为文件存储方式的问题。它将一个表拆分成三块，一个是frm文件：表结构信息，存放一些表结构信息的文件，比如表名字，表的字段，字段类型等等都在这个文件里。下一个MYD文件，是数据文件，这个就是你表中的具体数据。MYI表的索引信息，主键索引、唯一外键索引、普通索引、合并索引、组合索引、全文索引，这些索引会形成一个独立的索引文件，这个文件远远小于数据文件。只要查询中带有索引，他就会找MYI文件，找到索引，而索引中记录的是key-value格式，key是你索引这一列的值，value是当条数据在数据文件MYD所在的磁盘物理地址，所以查询快，但也导致了增删改效率慢。进行增删改查操作是，你要讲数据存入到数据文件中，同时还需要维护索引信息。

2.3 InnoDB

InnoDB数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MYSQL++ API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理（transaction process）也不支持外键。尽管要比ISAM和MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。是现在的MySQL（5.5以上版本）常用版本默认引擎

MySQL 官方对InnoDB是这样解释的：InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署的性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。

InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

在MySQL5.7版本中，InnoDB存储引擎管理的数据文件为两个：分别是frm,idb文件。

InnoDB特点：

1）、支持事务

2）、数据多版本读取（InnoDB+MyISAM+ISAM）

3）、锁定机制的改进

4）、实现外键

2.3.1 innodb与myisam区别

1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin transaction和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3. InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4. InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5. Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

2.3.2 如何选择

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM

2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB。

3. 系统崩溃后，MyISAM恢复起来更困难，能否接受；

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

2.4 Memory存储引擎

Memory存储引擎，通过名字就很容易让人知道，他是一个将数据存储在内存中的存储引擎。Memory存储引擎不会将任何数据存放到磁盘上，仅仅存放了一个表结构相关信息的.frm文件在磁盘上面。所以一旦MySQLCrash或者主机Crash（死机）之后，Memory的表就只剩下一个表结构了。Memory表支持索引，并且同时支持Hash和B－Tree两种格式的索引。由于是存放在内存中，所以Memory都是按照定长的空间来存储数据的，而且不支持BLOB和TEXT类型的字段。Memory存储引擎实现页级锁定。

2.5 NDBCluster存储引擎

NDB存储引擎也叫NDBCluster存储引擎，主要用于MySQLCluster分布式集群环境，Cluster是MySQL从5.0版本才开始提供的新功能。

2.6 Merge存储引擎

MERGE存储引擎，在MySQL用户手册中也提到了，也被大家认识为MRG_MyISAM引擎。Why？因为MERGE存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM表，通过一些特殊的包装对外提供一个单一的访问入口，以达到减小应用的复杂度的目的。要创建MERGE表，不仅仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致。BDB存储引擎

BDB存储引擎全称为BerkeleyDB存储引擎，和Innodb一样，也不是MySQL自己开发实现的一个存储引擎，而是由SleepycatSoftware所提供，当然，也是开源存储引擎，同样支持事务安全。

2.7 FEDERATED存储引擎

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

2.8 ARCHIVE存储引擎

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

作，仅支持插入和查询操作。锁定机制为行级锁定。

2.9 BLACKHOLE存储引擎

BLACKHOLE存储引擎是一个非常有意思的存储引擎，功能恰如其名，就是一个“黑洞”。就像我们unix系统下面的“/dev/null”设备一样，不管我们写入任何信息，都是有去无回。

2.10 CSV存储引擎

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

3 存储引擎管理

3.1 查看数据库支持的存储引擎

show engines

3.2 查看数据库当前使用的存储引擎

就是默认引擎是什么。

show variables like '%storage_engine%'

也可以在MySQL配置文件中查看。 windows - my.ini。 Linux - my.cnf

3.3 查看数据库表所用的存储引擎

show create table table_name

3.4 创建表指定存储引擎

create table table_name (column_name column_type) engine = engine_name

3.5 修改表的存储引擎

alter table table_name engine=engine_name

3.6 修改默认的存储引擎

在MySQL配置文件中修改下述内容：

default-storage-engine=INNODB

MySQL配置文件：

windows系统 - MySQL安装目录/my.ini （5.7版本my.ini文件在数据目录中。 C:/programdata/MySQL Server 5.7/mysql/）

linux系统 - /etc/my.cnf

4 MySQL中的索引简介

4.1 索引的优点

为什么要创建索引？这是因为，创建索引可以大大提高系统的查询性能。

第一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二、可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五、通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能。

4.2 索引的缺点

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然，索引有许多优点，但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面:

第一、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

第三、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

4.3 什么样的字段适合创建索引

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在具备下述特性的列上创建索引：

第一、在经常需要搜索的列上，可以加快搜索的速度；

第二、在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

第三、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

第四、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

第五、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

第六、在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

建立索引，一般按照select的where条件来建立，比如： select的条件是where f1 and f2，那么如果我们在字段f1或字段f2上建立索引是没有用的，只有在字段f1和f2上同时建立索引才有用等。

4.4 什么样的字段不适合创建索引:

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的这些列具有下述特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

5 MySQL中的索引种类

5.1 B-Tree索引

B-Tree索引，顾名思义，就是所有的索引节点都按照balance tree的数据结构来存储。B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。

B-tree中，每个结点包含：

1、本结点所含关键字的个数；

2、指向父结点的指针；

3、关键字；

4、指向子结点的指针；

对于一棵m阶B-tree，每个结点至多可以拥有m个子结点。各结点的关键字和可以拥有的子结点数都有限制，规定m阶B-tree中，根结点至少有2个子结点，除非根结点为叶子节点，相应的，根结点中关键字的个数为1~m-1；非根结点至少有[m/2]（[]，向上取整）个子结点，相应的，关键字个数为[m/2]-1~m-1。

B-tree有以下特性：

1、关键字集合分布在整棵树中；

2、任何一个关键字出现且只出现在一个结点中；

3、搜索有可能在非叶子结点结束；

4、其搜索性能等价于在关键字全集内做一次二分查找；

5、自动层次控制；

由于限制了除根结点以外的非叶子结点，至少含有M/2个儿子，确保了结点的至少利用率，其最低搜索性能为：

其中，M为设定的非叶子结点最多子树个数，N为关键字总数；

所以B-树的性能总是等价于二分查找（与M值无关），也就没有B树平衡的问题；

由于M/2的限制，在插入结点时，如果结点已满，需要将结点分裂为两个各占M/2的结点；删除结点时，需将两个不足M/2的兄弟结点合并。

5.2 Full-text索引

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

6 MySQL中的索引管理

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

6.1 普通索引

这是最基本的索引，它没有任何限制MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。

6.1.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)))

6.1.2 查看索引

SHOW INDEX FROM [table_name]

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

6.1.3 删除索引

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

6.2 唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似

6.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)))

6.3 全文索引（FULLTEXT）

MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

6.3.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))

6.4 组合索引（最左前缀）

CREATE TABLE article(id int not null, title varchar(255), time date);

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

–title,time

–title

为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：

1，使用到上面的索引

SELECT * FROM article WHERE title='测试' AND time=1234567890;

SELECT * FROM article WHERE title='测试';

2，不使用上面的索引

SELECT * FROM article WHERE time=1234567890;

参考：https://segmentfault.com/a/1190000008131735#articleHeader5

6.4.1 创建索引

CREATE INDEX index_name ON table_name (column_list)

7 MySQL中的索引优化

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE次数大于查询次数时，放弃索引。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

7.1 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，组合索引中只要有一列含有NULL值，那么这一列对于此组合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。create table table_name(c1 varchar(32) default ‘0’)

7.2 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

CREATE INDEX index_name ON table_name (column(length))

7.3 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

7.4 like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

7.5 不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′

8 索引总结

最后总结一下，MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的。

建议：一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

9 MySQL中的SQL的常见优化策略

9.1 避免全表扫描

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

9.2 避免判断null值

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

9.3 避免不等值判断

应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

9.4 避免使用or逻辑

应尽量避免在 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

9.5 慎用in和not in逻辑

in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t1 where num in(select id from t2 where id > 10)

此时外层查询会全表扫描，不使用索引。可以修改为：

select id from t1,(select id from t1 where id > 10)t2 where t1.id = t2.id

此时索引被使用，可以明显提升查询效率。

9.6 注意模糊查询

下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

模糊查询如果是必要条件时，可以使用select id from t where name like 'abc%'来实现模糊查询，此时索引将被使用。如果头匹配是必要逻辑，建议使用全文搜索引擎（Elastic search、Lucene、Solr等）。

9.7 避免查询条件中字段计算

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9.8 避免查询条件中对字段进行函数操作

应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id

应改为:

select id from t where name like 'abc%'

9.9 WHERE子句“=”左边注意点

不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

9.10 组合索引使用

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

9.11 不要定义无异议的查询

不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(...)

9.12 exists

很多时候用 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)

9.13 索引也可能失效

并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

9.14 表格字段类型选择

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

尽可能的使用 varchar 代替 char ，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

9.15 查询语法中的字段

任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

9.16 索引无关优化

不使用*、尽量不使用union，union all等关键字、尽量不使用or关键字、尽量使用等值判断。

表连接建议不超过5个。如果超过5个，则考虑表格的设计。（互联网应用中）

表连接方式使用外联优于内联。

外连接有基础数据存在。如：A left join B,基础数据是A。

A inner join B，没有基础数据的，先使用笛卡尔积完成全连接，在根据连接条件得到内连接结果集。

大数据量级的表格做分页查询时，如果页码数量过大，则使用子查询配合完成分页逻辑。

Select * from table limit 1000000, 10

Select * from table where id in (select pk from table limit 100000, 10)

二、 MySQL+MyCat分库分表

1 MyCat简介

java编写的数据库中间件

Mycat运行环境需要JDK.

Mycat是中间件.运行在代码应用和MySQL数据库之间的应用.

前身 : corba. 是阿里开发的数据库中间件.实现MySQL数据库分库分表集群管理的中间件.曾经出现过重大事故. 二次开发,形成Mycat.

使用MyCat之后,编写的所有的SQL语句,必须严格遵守SQL标准规范.

insert into table_name(column_name) values(column_value);

使用MyCat中间件后的结构图如下：

2 MyCat术语简介

2.1 切分

逻辑上的切分. 在物理层面,是使用多库[database],多表[table]实现的切分.

2.1.1 纵向切分

把一个数据库切分成多个数据库,配置方便

只能实现两张表的表连接查询.

将一张表中的数据，分散到若干个database的同结构表中。多个表的数据的集合是当前表格的数据。

2.1.2 横向切分

把一个表切分成多个表,相比纵向切分配置麻烦

无法实现表连接查询.

将一张表的字段，分散到若干张表中，将若干表连接到一起，才是当前表的完整数据。

2.2 逻辑库

Mycat中定义的database.是逻辑上存在的.但是物理上未必存在.

主要是针对纵向切分提供的概念.

访问MyCat，就是将MyCat当做MySQL使用。

Db数据库是MyCat中定义的database。通过SQL访问MyCat中的db库的时候，对应的是MySQL中的db1，db2，db3三个库。物理上的database是db1，db2，db3.逻辑上的database就是db。

2.3 逻辑表

Mycat中定义的table.是逻辑上存在,物理上未必存在.

主要是针对横向切分提供的概念

MyCat中的表格table，其字段分散到MySQL数据库的表格table1,table2,table3中。

2.4 默认端口

Mycat默认端口是8066

2.5 数据主机 - dataHost

物理MySQL存放的主机地址.可以使用主机名,IP,域名定义.

2.6 数据节点 - dataNode

物理的database是什么.数据保存的物理节点.就是database.

2.7 分片规则

当控制数据的时候,如何访问物理database和table.

就是访问dataHost和dataNode的算法.

在Mycat处理具体的数据CRUD的时候,如何访问dataHost和dataNode的算法.如:哈希算法,crc16算法等.

3 Mycat搭建

3.1 安装JDK

略

3.2 主从备份搭建完成

3.3 安装mycat

解压缩: tar -zxf mycat-xxxx.tar.gz

3.4 Master提供可被Mycat访问的用户

在Mycat中通过Master数据库的root用户访问Master数据库.

grant all privileges on *.* to ‘username’@’ip’ identified by ‘password’ with grant option;

grant all privileges on *.* to 'mycat'@'%' identified by 'mycat' with grant option;

3.5 上传mycat

Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

3.6 解压缩

tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

3.7 Mycat配置文件详解

Mycat所有的配置文件,都在应用的conf目录中.

3.7.1 rule.xml

用于定义分片规则的配置文件.

主要是查看.很少修改.

mycat默认的分片规则: 以500万为单位,实现分片规则.

逻辑库A对应dataNode - db1和db2. 1-500万保存在db1中, 500万零1到1000万保存在db2中,1000万零1到1500万保存在db1中.依次类推.

<rule>

</rule>

</tableRule>

crc32slot规则: 在CRUD操作时,根据具体数据的crc32算法计算,数据应该保存在哪一个dataNode中. 算法类似模运算.

<rule>

</rule>

</tableRule>

3.7.2 schema.xml

用于定义逻辑库和逻辑表的配置文件.在配置文件中可以定义读写分离,逻辑库,逻辑表,dataHost,dataNode等信息.

配置文件解释:

3.7.2.1 标签schema

配置逻辑库的标签

3.7.2.1.1 属性name

逻辑库名称

3.7.2.1.2 属性checkSQLschema

是否检测SQL语法中的schema信息.

如: Mycat逻辑库名称 A, dataNode名称B

SQL : select * from A.table;

checkSQLschema值是true, Mycat发送到数据库的SQL是select * from table;

checkSQLschema值是false,Mycat发送的数据库的SQL是select * from A.table;

3.7.2.1.3 sqlMaxLimit

Mycat在执行SQL的时候,如果SQL语法中没有limit子句.自动增加limit子句. 避免一次性得到过多的数据,影响效率. limit子句的限制数量默认配置为100.如果SQL中有具体的limit子句,当前属性失效.

SQL : select * from table . mycat解析后: select * from table limit 100

SQL : select * from table limit 10 . mycat不做任何操作修改.

3.7.2.2 标签table

定义逻辑表的标签，如果需要定义多个逻辑表，编写多个table标签。要求逻辑表的表名和物理表（MySQL数据库中真实存在的表）的表名一致。

3.7.2.2.1 属性name

逻辑表名

3.7.2.2.2 属性dataNode

数据节点名称. 配置文件中后续需要定义的标签（即物理数据库中的database名称）.多个名称使用逗号分隔.

多个database定义后，代表分库。

3.7.2.2.3 属性rule

分片规则名称.具体的规则名称参考rule.xml配置文件.

SQL语句发送到Mycat中后，Mycat如何计算，应该将当期的SQL发送到哪一个物理数据库管理系统或物理database中。

3.7.2.3 标签dataNode

定义数据节点的标签，定义具体的物理database信息的。

3.7.2.3.1 属性name

数据节点名称, 是定义的逻辑名称,对应具体的物理数据库database

3.7.2.3.2 属性dataHost

引用dataHost标签的name值

搞钱自律

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库和sql优化

**MySQL数据库与SQL优化MySQL数据库与SQL优化一、MySQL数据库 - SQL优化1结构图2MySQL数据库引擎简介2.1ISAM(Indexed Sequential Access Method)2.2MyISAM2.3InnoDB2.4Memory存储引擎2.5NDBCluster存储引擎2.6Merge存储引擎2.7FEDERATED存储引擎2.8ARCHIVE存储引擎2.9BLACKHOLE存储引擎2.10CSV存储引擎3存储引擎管理3.1查看数据库支持的存储引擎3.2查看数据库当前
复制链接

扫一扫

专栏目录