| 索引（Index） | 提高表的where条件查询语句的速度 | | --- | --- | | 语法 | | ``` CREATE TABLE table_name[col_name data type] [unique|fulltext][index|key][index_name](col_name[length])[asc|desc] 1.unique|fulltext为可选参数，分别表示唯一索引、全文索引 2.index和key为同义词，两者作用相同，用来指定创建索引 3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择 4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值 5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度 6.asc或desc指定升序或降序的索引值存储 ``` ``` 使用场景 经常充当where条件的字段 1．选择唯一性索引 2．为经常需要排序、分组和联合操作的字段建立索引 3．为常作为查询条件的字段建立索引 4．限制索引的数目 5．尽量使用数据量少的索引 6．尽量使用前缀来索引 7．删除不再使用或者很少使用的索引 索引分类 1、主键索引：唯一、非空 2、唯一索引：唯一 3、普通索引：没限制 4、全文索引：text字段效果好 删除索引 drop index 索引名 on 表名； drop index t_index on bbsinfo; 查看表索引 show index from 表名； SHWO KEYS FROM tablename; 创建索引 1、主键索引 1）建表的同时，直接指定主键索引 CREATE TABLE table_name( 'id' int(10) NOT NULL AUTO_INCREMENT, 'title' CHAR(50) NOT NULL, PRIMARY KEY('id') ); 2、唯一索引 （1）建表的同时，直接指定唯一约束 （2）create unique index 索引名 on 表名（字段名）; create unique index t_index on bbsinfo(title); ALTER TABLE table_name ADD UNIQUE indexName ON (column(length)) CREATE TABLE tanle_name( 'id' int NOT NULL AUTO_INCREMENT, 'title' char(50) CHARACTER NOT NULL, 'content' text CHARACTER NOT NULL, 'time' int(10) NULL DEFAULT NULL, UNIQUE index_name (title(length)) ); 3、普通索引 create index 索引名 on 表名（字段）； create index t_index on bbsinfo(title); 4、全文索引 create fulltext index 索引名 on 表名（字段名）； create fulltext index t_title on bbsinfo(title); CREATE TABLE tanle_name( 'id' int NOT NULL AUTO_INCREMENT, 'title' char(50) CHARACTER NOT NULL, 'content' text CHARACTER NOT NULL, 'time' int(10) NULL DEFAULT NULL, FULLTEXT (content) ); ALTER TABLE table_name ADD FULLTEXT index_name(content) 5、组合索引 ALTER TABLE table_name ADD INDEX index_name (name,city,age); 索引的知识点 1、介绍，作用 2、分类 3、创建，删除、查看 索引注意： 1、索引是施加给字段 2、索引分类：主键索引，唯一索引，普通索引 3、什么样的字段应该添加索引：经常充当where条件的字段 ``` | PRIMARY KEY | 主键索引 | | --- | --- | >说明： 唯一、非空，其实就是主键，一般在建表时就指定了，不需要额外添加 | UNIQUE | 唯一索引 | | --- | --- | >说明： 要求索引字段值在表中是唯一的，这一点和主键索引类似，但是不同的是，唯一索引允许有空值。唯一索引一般适用于身份证号码、用户账号等不允许有重复的属性字段上 | INDEX | 普通索引(normal) | | --- | --- | ``` 说明： 没限制,一般使用于name,email等一般属性 对于普通索引而言 在使用like进行通配符模糊查询时,如果首尾之间都使用了通配符，索引时无效的。 假设查询内容的关键词为'abc' SELECT * FROM tab_name WHERE index_column LIKE  'abc%';  #索引是有效的 SELECT * FROM tab_name WHERE index_column LIKE  '%abc';  #索引是无效的 SELECT * FROM tab_name WHERE index_column LIKE  '%cba';  #索引是有效的 SELECT * FROM tab_name WHERE index_column LIKE  '%abc%';  #索引是无效的 当检索的字段内容比较大而且检索内容前后部分都不确定的情况下，可以改为全文索引，并使用特定的检索方式 ``` | FULLTEXT | 全文索引 | | --- | --- | ``` 说明： 只适用于VARCHAR和Text类型的字段 注意事项： MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表，如果是其他数据引擎，则全文索引不会生效。此外，MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。另外使用MySql自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略 ``` | 组合索引 | 多列索引 | | --- | --- | ``` 组合索引又称多列索引，就是建立索引时指定多个字段属性。有点类似于字典目录，比如查询 'guo' 这个拼音的字时，首先查找g字母，然后在g的检索范围内查询第二个字母为u的列表，最后在u的范围内查找最后一个字母为o的字。比如组合索引(a,b,c)，abc都是排好序的，在任意一段a的下面b都是排好序的，任何一段b下面c都是排好序的 组合索引的生效原则是  从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用 造成断点的原因： 前边的任意一个索引没有参与查询，后边的全部不生效。 前边的任意一个索引字段参与的是范围查询，后面的不会生效。 断点跟索引字字段在SQL语句中的位置前后无关，只与是否存在有关 比如： where a=3 and b=45 and c=5 .... #这种三个索引顺序使用中间没有断点，全部发挥作用； where a=3 and c=5... #这种情况下b就是断点，a发挥了效果，c没有效果 where b=3 and c=4... #这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果； where b=45 and a=3 and c=5 .... #这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关 （a,b,c） 三个列上加了联合索引（是联合索引 不是在每个列上单独加索引）而是建立了a,(a,b),(a,b,c)三个索引，另外(a,b,c)多列索引和 (a,c,b)是不一样的。 具体实例可以说明： (0) select * from mytable where a=3 and b=5 and c=4; #abc三个索引都在where条件里面用到了，而且都发挥了作用 (1) select * from mytable where c=4 and b=6 and a=3; #这条语句为了说明 组合索引与在SQL中的位置先后无关，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样 (2) select * from mytable where a=3 and c=7; #a用到索引，b没有用，所以c是没有用到索引效果的 (3) select * from mytable where a=3 and b>7 and c=3; #a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引 (4) select * from mytable where b=3 and c=4; #因为a索引没有使用，所以这里 bc都没有用上索引效果 (5) select * from mytable where a>4 and b=7 and c=9; #a用到了 b没有使用，c没有使用 (6) select * from mytable where a=3 order by b; #a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的 (7) select * from mytable where a=3 order by c; #a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort (8) select * from mytable where b=3 order by a; #b没有用到索引，排序中a也没有发挥索引效果 ``` | SPATIAL | 空间索引 | | --- | --- | | | 哈希索引 | | --- | --- | >在MySQL中，只有Memory 引擎显示支持哈希索引。这也是Memory 引擎表的默认索引类型，Memory 引擎同时也支持B-Tree索引 | 索引之间的区别 | | | --- | --- | ``` 全文索引和普通索引的区别 如果建立的是普通索引，一般会使用like进行模糊查询，只会对查询内容前一部分有效，即只对前面不使用通配符的查询有效，如果前后都有通配符，普通索引将不会起作用。对于全文索引而言在查询时有自己独特的匹配方式 例如我们在对一篇文章的标题和内容进行全文索引时： ALTER TABLE article ADD FULLTEXT ('title', 'content'); 在进行检索时就需要使用如下的语法进行检索： SELECT * FROM article WHERE MATCH('title', 'content') AGAINST ('查询字符串'); ``` 注意：在查询时，MYSQL只能使用一个索引，如果建立的是多个单列的普通索引，在查询时会根据查询的索引字段，从中选择一个限制最严格的单例索引进行查询。别的索引都不会生效 | 使用场景 | | | --- | --- | ``` 1、在join表的时候使用相当类型的列，并将其索引 如果在程序中有很多JOIN查询，应该保证两个表中join的字段时被建立过索引的。这样MySQL颞部会启动优化JOIN的SQL语句的机制。注意：这些被用来JOIN的字段，应该是相同类型的。例如：如果要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）   例如： SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = “user_id” 两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。 2、切记不要使用ORDER BY RAND() 如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序） 3、避免使用SELECT * 从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果我们的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。 所以，我们应该养成一个需要什么就取什么的好的习惯。 Hibernate性能方面就会差，它不用*，但它将整个表的所有字段全查出来  优点：开发速度快 4、永远为每张表设置一个ID主键 我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的 AUTO_INCREMENT标志。 就算是我们 users 表有一个主键叫 “email”的字段，我们也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在我们的程序中，我们应该使用表的ID来构造我们的数据结构。 而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区…… 在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键 5、使用ENUM而不是VARCHAR ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。 如果我们有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，我们知道这些字段的取值是有限而且固定的，那么，我们应该使用 ENUM 而不是 VARCHAR 6、尽可能的不要赋值为NULL 如果不是特殊情况，尽可能的不要使用NULL。在MYSQL中对于INT类型而言，EMPTY是0，而NULL是空值。而在Oracle中 NULL和EMPTY的字符串是一样的。NULL也需要占用存储空间，并且会使我们的程序判断时更加复杂。现实情况是很复杂的，依然会有些情况下，我们需要使用NULL值 7、固定长度的表会更快 如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要我们包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。 固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。 并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论我们用不用，他都是要分配那么多的空间。另外在取出值的时候要使用trim去除空格  8、垂直分割 “垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的 9、拆分大的DELETE或INSERT 如果我们需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，我们需要非常小心，要避免我们的操作让我们的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。如果我们把我们的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让我们的WEB服务Crash，还可能会让我们的整台服务器马上掛了。所以在使用时使用LIMIT 控制数量操作记录的数量 10、越小的列会越快 对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把我们的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。 参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。 如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果我们不需要记录时间，使用 DATE 要比 DATETIME 好得多 11、选择正确的存储引擎 在MYSQL中有两个存储引擎MyISAM和InnoDB,每个引擎都有利有弊。 MyISAM适合于一些需要大量查询的应用，但是对于大量写操作的支持不是很好。甚至一个update语句就会进行锁表操作，这时读取这张表的所有进程都无法进行操作直至写操作完成。另外MyISAM对于SELECT  COUNT(*)这类的计算是超快无比的。InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。 MyISAM是MYSQL5.5版本以前默认的存储引擎，基于传统的ISAM类型，支持B-Tree，全文检索，但是不是事务安全的，而且不支持外键。不具有原子性。支持锁表。 InnoDB是事务型引擎，支持ACID事务(实现4种事务隔离机制) 在5.5之后默认的存储引擎是INNODB 可以单独进行修改也可以在创建表时修改： ALTER TABLE tab_name ENGINE INNODB； 12、小心永久链接 永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了，它会永远处在连接的状态，就算是数据库操作已经结束了。而且，自从我们的Apache开始重用它的子进程后——也就是说，下一次的HTTP请求会重用Apache的子进程，并重用相同的 MySQL 链接。  而且，Apache 运行在极端并行的环境中，会创建很多很多的了进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在我们决定要使用“永久链接”之前，我们需要好好地考虑一下我们的整个系统的架构 ``` | 索引缺点 | | | --- | --- | >1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。 2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。 | 注意事项 | | | --- | --- | >使用索引时，有以下一些技巧和注意事项： 1.索引不会包含有null值的列，只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。 2.使用短索引 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 3.索引列排序 查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。 4.like语句操作 一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。 5.不要在列上进行运算 这将导致索引失效而进行全表扫描，例如 ``` SELECT * FROM table_name WHERE YEAR(column_name)<2017; ``` >6.不使用not in和<>操作 | 加索引和不加索引的区别 | | | --- | --- | 当你的数据量很大的时候，如果经常拿来查询的字段做查询，将会很久才能查的出来。比如 16000000条数据 加索引只要 0点几秒 不加索引可能要几十秒