Mysql索引详解及基本⽤法
特朗普确认将再次参选总统索引的概念
索引是⼀种特殊的⽂件(InnoDB数据表上的索引是表空间的⼀个组成部分)，它们包含着对数据表⾥所有记录的引⽤指针。更通俗的说，索引好⽐是⼀本书前⾯的⽬录，能加快数据库的查询速度。
索引分为聚簇索引和⾮聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，⽽⾮聚簇索引就不⼀样了；聚簇索引能提⾼多⾏检索的速度，⽽⾮聚簇索引对于单⾏的检索很快
要注意的是，建⽴太多的索引将会影响更新和插⼊的速度，因为它需要同样更新每个索引⽂件。对于⼀个经常需要更新和插⼊的表格，就没有必要为⼀个很少使⽤的where字句单独建⽴索引了，对于⽐较⼩的表，排序的开销不会很⼤，也没有必要建⽴另外的索引。
1. 普通索引
普通索引(由关键字KEY或INDEX定义的索引)的唯⼀任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能，就应该选择⼀个数据最整齐、最紧凑的数据列(如⼀个整数类型的数据列)来创建索引。
[sql]
1.
2. –直接创建索引(length表⽰使⽤名称前1ength个字符)
3. CREATE INDEX index_name ON table_name(column_name(length))
4. –修改表结构的⽅式添加索引
5. ALTER TABLE table_name ADD INDEX index_name ON (column_name)
6. –创建表的时候同时创建索引
7. CREATE TABLE table_name (
8. id int(11) NOT NULL AUTO_INCREMENT ,
9. title char(255) NOT NULL ,
10. PRIMARY KEY (id),
11. INDEX index_name (title)
12. ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
13. –删除索引
14. DROP INDEX index_name ON table_name;
15.
16. 建⽴复合索引 。
17. CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);
18.  注意命名时的习惯了吗？使⽤“表名字段1名字段2名”的⽅式
2. 唯⼀索引
与普通索引类似，不同的就是：索引列的值必须唯⼀，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯⼀，创建⽅法和普通索引类似。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该⽤关键字UNIQUE把它定义为⼀个唯⼀索引。这么做的好处：⼀是简化了MySQL对这个索引的管理⼯作，这个
索引也因此⽽变得更有效率；⼆是MySQL会在有新记录插⼊数据表时，⾃动检查新记录的这个字段的值是否已经在某个记录的这个字段⾥出现过了；如果是，MySQL将拒绝插⼊那条新记录。也就是说，唯⼀索引可以保证数据记录的唯⼀性。事实上，在许多场合，⼈们创建唯⼀索引的⽬的往往不是为了提⾼访问速度，⽽只是为了避免数据出现重复。
2. –创建唯⼀索引
3. CREATE UNIQUE INDEX index_name ON table_name(column_name)
4. –修改表结构
5. ALTER TABLE table_name ADD UNIQUE index_name ON (column_name)
6. –创建表的时候直接指定
mysql语句的执行顺序
7. CREATE TABLE table_name (
8. id int(11) NOT NULL AUTO_INCREMENT ,
9. title char(255) NOT NULL ,
10. PRIMARY KEY (id),
11. UNIQUE index_name (title)
12. );
3.主索引
  在前⾯已经反复多次强调过：必须为主键字段创建⼀个索引，这个索引就是所谓的”主索引”。主索引与唯⼀索引的唯⼀区别是：前者在定义时使⽤的关键字是PRIMARY⽽不是UNIQUE。
4.外键索引
  如果为某个外键字段定义了⼀个外键约束条件，MySQL就会定义⼀个内部索引来帮助⾃⼰以最有效率的⽅式去管理和使⽤外键约束条件。
5. 全⽂索引（FULLTEXT）静态局部变量的作用域
MySQL从3.23.23版开始⽀持全⽂索引和全⽂检索，fulltext索引仅可⽤于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的⼀部分被创建，或是随后使⽤ALTER TA
BLE 或CREATE INDEX被添加。对于较⼤的数据集，将你的资料输⼊⼀个没有FULLTEXT索引的表中，然后创建索引，其速度⽐把资料输⼊现有FULLTEXT索引的速度更为快。不过切记对于⼤容量的数据表，⽣成全⽂索引是⼀个⾮常消耗时间⾮常消耗硬盘空间的做法。
⽂本字段上的普通索引只能加快对出现在字段内容最前⾯的字符串(也就是字段内容开头的字符)进⾏检索操作。如果字段⾥存放的是由⼏个、甚⾄是多个单词构成的较⼤段⽂字，普通索引就没什么作⽤了。这种检索往往以LIKE %word%的形式出现，这对MySQL来说很复杂，如果需要处理的数据量很⼤，响应时间就会很长。
  这类场合正是全⽂索引(full-text index)可以⼤显⾝⼿的地⽅。在⽣成这种类型的索引时，MySQL将把在⽂本中出现的所有单词创建为⼀份清单，查询操作将根据这份清单去检索有关的数据记录。全⽂索引即可以随数据表⼀同创建，也可以等⽇后有必要时再使⽤下⾯这条命令添加：
  ALTER TABLE table_name ADD FULLTEXT(column1, column2)
  有了全⽂索引，就可以⽤SELECT查询命令去检索那些包含着⼀个或多个给定单词的数据记录了。下⾯是这类查询命令的基本语法：  　SELECT * FROM table_name
  WHERE MATCH(column1, column2) AGAINST(‘word1’, ‘word2’, ‘word3’)
  上⾯这条命令将把column1和column2字段⾥有word1、word2和word3的数据记录全部查询出来。
2. –创建表的适合添加全⽂索引
3. CREATE TABLE table_name (
4. id int(11) NOT NULL AUTO_INCREMENT ,
5. content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
6. PRIMARY KEY (id),
7. FULLTEXT (content)
8. );
9. –修改表结构添加全⽂索引
10. ALTER TABLE table_name ADD FULLTEXT index_name(column_name)
11. –直接创建索引
12. CREATE FULLTEXT INDEX index_name ON table_name (column_name)
6. 单列索引、多列索引
多个单列索引与单个多列索引的查询效果不同，因为执⾏查询时，MySQL只能使⽤⼀个索引，会从多个索引中选择⼀个限制最为严格的索引。
5. 组合(复合)索引（最左前缀）
平时⽤的SQL查询语句⼀般都有⽐较多的限制条件，所以为了进⼀步榨取MySQL的效率，就要考虑建⽴组合索引。例如上表中针对title 和time建⽴⼀个组合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建⽴这样的组合索引，其实是相当于分别建⽴了下⾯两组组合索引：
–title,time
–title
为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左⾯的开始组合。并不是只要包含这两列的查询都会⽤到该组合索引，如下⾯的⼏个SQL所⽰
–使⽤到上⾯的索引
SELECT * FROM article WHREE title=’测试’ AND time=1234567890;
SELECT * FROM article WHREE title=’测试’;
–不使⽤上⾯的索引特斯拉召回 刹车
SELECT * FROM article WHREE time=1234567890;
MySQL索引的优化
上⾯都在说使⽤索引的好处，但过多的使⽤索引将会造成滥⽤。因此索引也会有它的缺点：虽然索引⼤⼤提⾼了查询速度，同时却会降低更新表的速度，如对表进⾏INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存⼀下索引⽂件。建⽴索引会占⽤磁盘空间的索引⽂件。⼀般情况这个问题不太严重，但如果你在⼀个⼤表上创建了多种组合索引，索引⽂件的会膨胀很快。索引只是提⾼效率的⼀个因素，如果你的MySQL有⼤数据量的表，就需要花时间研究建⽴最优秀的索引，或优化查询语句。下⾯是⼀些总结以及收藏的MySQL索引的注意事项和优化⽅法。
1. 何时使⽤聚集索引或⾮聚集索引？
动作描述使⽤聚集索引使⽤⾮聚集索引
列经常被分组排序使⽤使⽤
返回某范围内的数据使⽤不使⽤
⼀个或极少不同值不使⽤不使⽤
⼩数⽬的不同值使⽤不使⽤
⼤数⽬的不同值不使⽤使⽤
频繁更新的列不使⽤使⽤
外键列使⽤使⽤
主键列使⽤使⽤
频繁修改索引列不使⽤使⽤
2. 索引不会包含有NULL值的列
typedef类的基础用法只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有⼀列含有NULL值，那么这⼀列对于此复合索引就是⽆效的。所以我们在数据库设计时不要让字段的默认值为NULL。
3. 使⽤短索引
对串列进⾏索引，如果可能应该指定⼀个前缀长度。例如，如果有⼀个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟⼀的，那么就不要对整个列进⾏索引。短索引不仅可以提⾼查询速度⽽且可以节省磁盘空间和I/O操作。
4. 索引列排序
MySQL查询只使⽤⼀个索引，因此如果where⼦句中已经使⽤了索引的话，那么order by中的列是不会使⽤索引的。因此数据库默认排序可以符合要求的情况下不要使⽤排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
5. like语句操作
⼀般情况下不⿎励使⽤like操作，如果⾮使⽤不可，如何使⽤也是⼀个问题。like “%aaa%” 不会使⽤索引⽽like “aaa%”可以使⽤索引。
6. 不要在列上进⾏运算
例如：select * from users where YEAR(adddate)<2007，将在每个⾏上进⾏运算，这将导致索引失效⽽进⾏全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这⼀点可以围观：⼀个单引号引发的MYSQL性能损失。
最后总结⼀下，MySQL只对⼀下操作符才使⽤索引：<,<=,=,>,>=,between,in，以及某些时候的like(不以通配符%或_开头的情形)。⽽理论上每张表⾥⾯最多可创建16个索引，不过除⾮是数据量真的很多，否则过多的使⽤索引也不是那么好玩的，⽐如我刚才针对text类型的字段创建索引的时候，系统差点就卡死了。
补充EXPLAIN ⽤法：
只有当数据库⾥已经有了⾜够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库⾥只有⼏百条数据记录，它们往往在执⾏完第⼀条查询命令之后就被全部加载到内存⾥，这将使后续的查询命令都执⾏得⾮常快–不管有没有使⽤索引。只有当数据库⾥的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。
  在不确定应该在哪些数据列上创建索引的时候，⼈们从EXPLAIN SELECT命令那⾥往往可以获得⼀些帮助。这其实只是简单地给⼀条普通的SELECT命令加⼀个EXPLAIN关键字作为前缀⽽已。有了这个关键字，MySQL将不是去执⾏那条SELECT命令，⽽是去对它进⾏分析。MySQL将以表格的形式把
查询的执⾏过程和⽤到的索引(如果有的话)等信息列出来。
  在EXPLAIN命令的输出结果⾥，第1列是从数据库读取的数据表的名字，它们按被读取的先后顺序排列。type列指定了本数据表与其它数据表之间的关联关系(JOIN)。在各种类型的关联关系当中，效率最⾼的是system，然后依次是const、eq_ref、ref、range、index和All(All的意思是：对应于上⼀级数据表⾥的每⼀条记录，这个数据表⾥的所有记录都必须被读取⼀遍–这种情况往往可以⽤⼀索引来避免)。
  possible_keys数据列给出了MySQL在搜索数据记录时可选⽤的各个索引。key数据列是MySQL实际选⽤的索引，这个索引按字节计算的长度在key_len数据列⾥给出。⽐如说，对于⼀个INTEGER数据列的索引，这个字节长度将是4。如果⽤到了复合索引，在key_len数据列⾥还可以看到MySQL具体使⽤了它的哪些部分。作为⼀般规律，key_len数据列⾥的值越⼩越好(意思是更快)。
  ref数据列给出了关联关系中另⼀个数据表⾥的数据列的名字。row数据列是MySQL在执⾏这个查询时预计会从这个数据表⾥读出的数据⾏的个数。row数据列⾥的所有数字的乘积可以让我们⼤致了解这个查询需要处理多少组合。
————————————————————————————————————–
8.key和index区别
mysql的key和index多少有点令⼈迷惑，这实际上考察对数据库体系结构的了解的。
1).key 是数据库的物理结构，它包含两层意义，⼀是约束（偏重于约束和规范数据库的结构完整性），⼆是索引（辅助查询⽤的）。包括primary key, unique key, foreign key 等。
primary key 有两个作⽤，⼀是约束作⽤（constraint），⽤来规范⼀个存储主键和唯⼀性，但同时也在此key上建⽴了⼀个index；
unique key 也有两个作⽤，⼀是约束作⽤（constraint），规范数据的唯⼀性，但同时也在这个key上建⽴了⼀个index；
foreign key也有两个作⽤，⼀是约束作⽤（constraint），规范数据的引⽤完整性，但同时也在这个key上建⽴了⼀个index；
可见，mysql的key是同时具有constraint和index的意义，这点和其他数据库表现的可能有区别。（⾄少在上建⽴外键，不会⾃动建⽴index），因此创建key也有如下⼏种⽅式：
（1）在字段级以key⽅式建⽴， 如 create table t (id int not null primary key);
（2）在表级以constraint⽅式建⽴，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
（3）在表级以key⽅式建⽴，如create table t(id int, primary key (id));
其它key创建类似，但不管那种⽅式，既建⽴了constraint，⼜建⽴了index，只不过index使⽤的就是这个constraint或key。
2).index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以⼀个类似⽬录的结构存储。索引要分类的话，分为前缀索引、全⽂本索引等；
因此，索引只是索引，它不会去约束索引的字段的⾏为（那是key要做的事情）。
如，create table t(id int, index inx_tx_id  (id));
3).最后的释疑：
(1).我们说索引分类，分为主键索引、唯⼀索引、普通索引(这才是纯粹的index)等，也是基于是不是把index看作了key。
⽐如 create table t(id int, unique index inx_tx_id  (id));  –index当作了key使⽤
(2).最重要的也就是，不管如何描述，理解index是纯粹的index，还是被当作key，当作key时则会有两种意义或起两种作⽤。
简述css的特点索引在查询中如何使⽤
SELECT * FROM test_tab WHERE name = ⼀个外部输⼊的数据
刚开始，数据不多的时候，执⾏效果还不错。
随着数据量的增加，这个查询，执⾏起来，越来越慢了。
然后在 name 上⾯ 建⽴了索引
CREATE INDEX idx_test4_name ON test_tab (name );
这样， 可以加快前⾯那个查询的速度。
但是，某天，你执⾏了下⾯这个SQL， 发现速度⼜慢了
SELECT * FROM test_tab WHERE age = 25
为啥呢？ 因为 age 字段上⾯，没有索引
索引只在 name 上⾯有
换句话说， 也就是 WHERE ⾥⾯的条件， 会⾃动判断，有没有 可⽤的索引，如果有， 该不该⽤。
多列索引，就是⼀个索引，包含了2个字段。
例如：CREATE INDEX idx_test_name_age ON test_tab (name, age)；那么SELECT * FROM  test_tab WHERE name LIKE ‘张%’AND age = 25
这样的查询，将能够使⽤上⾯的索引。
多列索引，还有⼀个可⽤的情况就是， 某些情况下，可能查询，只访问索引就⾜够了， 不需要再访问表了。
例如：SELECTAVG ( avg ) AS 平均年龄FROM test_tab WHERE name LIKE ‘张%’
这个时候， name 与 age 都包含在索引⾥⾯。 查询不需要去检索表中的数据。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。