mysql中betwwen and_mysql原理、索引和sql优化

一、数据库索引

1.1、MySql索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

(注：一般数据库默认都会为主键生成索引。)

1.2、建立索引的优缺点

为什么要创建索引呢？

这是因为，创建索引可以大大提高系统的性能。

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

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

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

-->外键所参照的键必须是主键。

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

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

-->虽然索引有很多的优点，但是为每个字段建立索引是不明智的。

因为增加索引也有许多不利的方面:

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

-->是通过B-tree维护的索引

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

-->表中存在千万条的记录的时候，索引文件也会非常大。

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

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

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

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

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

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

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

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

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

什么样的字段不适合建立索引

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

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

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

第四，当修改性能远远大于检索性能时，不应该创建索 引。这是因为，修改性能和检索性能是互相矛盾的。

当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。

因此，当修改性能远远大于检索性能时，不应该创建索引。

1.3、如何创建索引

1.3.1、普通索引

创建索引

CREATE INDEX name_index ON user(name(5)); -- 长度也可以不写。默认是等长度的，即长索引。问题存储空间大。 数字表示截取的字符个数。

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)；

创建表的时候直接指定

CREATE TABLEmytable(

IDINT NOT NULL,

usernameVARCHAR(16) NOT NULL,INDEX [indexName](username(length))

);

删除索引的语法

DROP INDEX [indexName] ON mytable;

1.3.2、唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLEmytable(

IDINT NOT NULL,

usernameVARCHAR(16) NOT NULL,UNIQUE [indexName](username(length))

);

使用ALTER 命令添加和删除索引

ALTER TABLE tbl_name ADD PRIMARY KEY(column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。ALTER TABLE tbl_name ADD UNIQUEindex_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。ALTER TABLE tbl_name ADD INDEXindex_name (column_list): 添加普通索引，索引值可出现多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

使用 ALTER 命令来使用DROP 子句来删除索引：

ALTER TABLE cst_customer DROP INDEXcust_name_index；

或者DROP INDEX cust_address_index ON cst_customer;

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，需要确保该主键默认不为空(NOT NULL)。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;

mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

-->删除主键时，只要指定PRIMARY KEY，但是在删除索引的时候，必须知道索引名。

显示索引信息：

mysql> SHOW INDEX FROM table_name;

二、关于MySql调优

1、对查询进行优化，应尽量避免全表扫描，首先考虑的是在 WHERE 和ORDER BY 设计到的列上建立索引。

2、应尽量不要在where 子句中对字段进行 null 值的判断，否则会导致引擎放弃使用索引而进行全表扫描。

select id from t where num is null;

可以在设计表的时候，在num 上设置默认值为0。确保num列没有 null值。然后进行如下查询

select id from t where num =0;

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

4、应尽量避免在 WHERE子句中使用or 来连接条件，---；如：

select id from t where num=10 or num　＝20；

可以这样进行查询

select id form t where num =10

union all

select id from t where num =20；

5、IN 和 NOT IN 也要慎用，否则会导致全表扫描 -->能用betwwen and 的尽量使用

SELECT id FROM t WHERE num IN(1,2,3);

对于连续的数值，能用 BETWEEN就不要用 IN 了。

SELECT id FROM WHERE BETWEEN 1 AND 3；

6、下面的查询也会导致全表扫描

SELECT id FROM t WHERE name LIKE '%abc%';

7、应尽量避免在WHRE子句中对字段进行表达式操作，---；

SELECT id FROM WHERE num/2 =100;

应改为：

SELECT id FROM t WHERE num =200;

8、应尽量避免在WHERE 子句中对字段进行函数操作，---；

SELECT id FROM t WHERE substring(name,1,3) ='abc'

应改为

SELECT id FROM t WHERE name LIKE 'abc%'

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

10、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引的第一个字段作为条件时才能保证系统使用该索引。

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

select col1,col2 into #t from where i=0;

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

create table #t (...)

12、很多时候使用exists 代替 in 是一个好的选择：

select num from where num in (select num from b)  -->in 是需要全表扫描的

可以改成：

select num from a where exists (select 1 form where b.num =a.num);

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

14、索引并不是越多越好，索引固然是可以去提高对应字段的 select 的效率，但同时他也降低了 insert 和update的效率。

因为insert和update时，有可能会重建索引，所以怎么建立索引需要慎重考虑，是具体情况而定。一个表的索引数最好不要超过 6 个。如果超过，则需要考虑下，在不常用的列上建立索引是否有必要。

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

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

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

三、操作数据库引擎

1、查看据库支持的引擎

show engines；

2、查看当前数据库当前默认引擎

show variables like '%storage_engine%'

-->不同的表可以使用不同的引擎

3、查看数据表使用的引擎

show create table cst_customer;

4、修改表的数据引擎

alert table table_name engine =engin_name；

mysql数据库引擎介绍

1、ISAM(indexed sequential method)

ISAM是一个定义明确且历经时间考验考验的数据表管理方法，它在设计时就考虑到数据库查询的次数要远大于更新的次数。因此ISAM 执行读取的速度很快。并且不用占用大量的内存和存储资源。

ISAM 主要的两个不足在于：不支持事务，也不能容错。如果硬盘崩溃，数据无法恢复。需要经常备份实时数据。  --->一般不会使用。

2、MyISAM

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

3、InnoDB

InnoDB给mysql提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB的锁定在行级并且也在SELECT 语句提供一个oracle风格一致的非锁定读。这些特点增加了多用户部署和性能。没有在InnoDb中扩大锁定的需要，因为在InnoDB中行级锁适合非常小的空间。InnoDB也支持 Foreign Key强制。在mysql中不同引擎类型的表可以混合使用。设置在一个查询中也可以混合使用。

InnoDB存储引擎为在主内存中缓存数据和索引二维持自己的缓存池。InnoDB存储它的表和索引在一个表空间中，表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同，MyISAM

表中的每个表被存储在分离的文件中。InnoDB 表可以是任何尺寸，即使是在文件尺寸被限制在2G的操作系统上。

InnoDB的特点：

支持事务安装

数据多版本读取

锁定机制的改进

实现外键

InnoDB和MyISAM区别

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

2、InnoDB支持事务，MyISAM不支持。对于包含外键的InnoDB表转为MyISAM会失败。

3、InnoDB是聚集索引，数据文件和索引是绑在一起的，必须有主键。但是辅助索引需要两次索引，显示查询到主键，然后通过主键查询到数据。因此主键不应过大，因为主键过大其他索引页很大。MyISAM 是非聚集索引，数据文件和索引是分离的。索引保存的是数据文件的指针。主键索引和辅助索引都是独立的。

4、InnoDB不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而MyISAM  用一个变量保存整个表的行数，执行上述语句时，只需读取该变量。

5、InnoDB不支持全文索引，而MyISAM 支持全文索引。MyISAM查询效率高。

如何选择：

1、是否支持事务，如果要，请选择InnoDB。

2、如果表中的绝大多数只是读取，可以考虑MyISAM，如果既有读，写很频繁，使用InnoDB。

3、系统崩溃后，MyISAM恢复起来麻烦。

4、mysql5.5 后 InnoDB 已经称为默认引擎，之前是MyISAM。