13518219792
一、MySQL有哪些索引类型
龙胜网站建设公司成都创新互联公司,龙胜网站设计制作，有大型网站制作公司丰富经验。已为龙胜数千家提供企业网站建设服务。企业网站搭建\成都外贸网站建设要多少钱，请找那个售后服务好的龙胜做网站的公司定做！
索引的分类可以从多个角度进行，下面分别从数据结构，物理存储和业务逻辑三个维度进行划分。
关于B+树索引，后面会深入解析
现在MyISAM和InnoDB引擎都支持了
用于对GIS数据类型创建SPATIAL索引
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
举个例子说明下：
create table student (
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(255),
PRIMARY KEY(`id`),
KEY(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;该表中主键id是该表的聚集索引、name为非聚集索引；表中的每行数据都是按照聚集索引id排序存储的；比如要查找name='Arla'和name='Arle'的两个同学，他们在name索引表中位置可能是相邻的，但是实际存储位置可能差的很远。name索引表节点按照name排序，检索的是每一行数据的主键。聚集索引表按照主键id排序，检索的是每一行数据的真实内容。
主键索引是一种特殊的唯一索引，不允许有空值
复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建.
CREATE TABLE table_name[col_name data type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]create table table_name(
id int(11),
name varchar(20),
sex boolean,
INDEX(id)
);查看表结构
show create table table_name;
可以使 EXPLAIN 语句查看索引是否被使用
explain select * from table_name where id = 1\Gcreate table index2(
id int unique,
name varchar(20),
unique INDEX index_2(id asc)
);全文索引只能在char，varchar或者text 类型的字段上。而且，只有MyISAM 储存引擎支持全文索引。
create table idnex3(
id int,
info varchar(20),
FULLTEXT INDEX index3_info(info)
)ENGINE=MyISAM;create table index4(
id int,
subject varchar(255),
index index4_st(subject(10))
);这里需要注意的，subject 的长度为255，但是index4_st索引只有10。这样做的目的还是为了提高查询速度。对于字符型的数据，可以不用查询全部信息，只查询其前面的若干字符信息。
create table index5(
id int,
name varchar(20),
sex char(4),
index index5_ns(name.sex)
);这是我们可以看到，name 和sex字段上已经创建了index_ns索引。
在example0() 表中的id 创建名为index7_id 的索引。
create index index7_id on example0(id);create UNIQUE index index_name on table_name(name);create FULLTEXT index index_name on table_name(info);create INDEX index_name ON table_name(name(10));create INDEX index_name ON table_name(name,sex);在name字段上创建名为indx_name 的索引
alter table table_name ADD INDEX index_name(name(20));alter table table_name ADD UNIQUE INDEX index_name(id);alter table table_name ADD FULLTEXT INDEX index_name(info);alter table table_name ADD INDEX index_name(name(4));alter tabel table_name ADD INDEX index_name(name.sex);DROP INDEX index_name ON table_name;目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，那么索引树是如何维护的？
查找是数据结构和算法中一个非常重要的概念。
B-Tree是一种多路搜索树（并不是二叉的）：
B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；B-Tree上查找算法的伪代码如下：
BTree_Search(node, key) { if(node == null) return null; foreach(node.key) { if(node.key[i] == key) return node.data[i]; if(node.key[i] > key) return BTree_Search(point[i]->node); } return BTree_Search(point[i+1]->node); } data = BTree_Search(root, my_key);B树中每一个内部节点会包含一定数量的键值。通常，键值的数量被选定在d和2d之间。在实际中，键值占用了节点中大部分的空间。因数2将保证节点可以被拆分或组合。如果一个内部节点有2d个键值，那么添加一个键值给此节点的过程，将会拆分2d键值为2个d键值的节点，并把此键值添加给父节点。每一个拆分的节点需要最小数目的键值。相似地，如果一个内部节点和他的邻居两者都有d个键值，那么将通过它与邻居的合并来删除一个键值。删除此键值将导致此节点拥有d-1个键值;与邻居的合并则加上d个键值，再加上从邻居节点的父节点移来的一个键值。结果为完全填充的2d个键值。
下面是往B树中依次插入
6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4
B-Tree有许多变种，其中最常见的是B+Tree，MySQL就普遍使用B+Tree实现其索引结构。
与B-Tree相比，B+Tree有以下不同点：
B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；
下面是往B+树中依次插入
6 10 4 14 5 11 15 3 2 12 1 7 8 8 6 3 6 21 5 15 15 6 32 23 45 65 7 8 6 5 4
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
假如每个盘块可以正好存放一个B树的结点（正好存放2个文件名）。那么一个BTNODE结点就代表一个盘块，而子树指针就是存放另外一个盘块的地址。
下面，咱们来模拟下查找文件29的过程：
B+-tree的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+
树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
索引查询是数据库中重要的记录查询方法，要不要进入索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑，下面给出实际中的一些通用的原则：
一个单独的索引扫描只能用于这样的条件子句：使用被索引字段和索引操作符类中的操作符， 并且这些条件以AND连接。
假设在(a, b)上有一个索引， 那么类似WHERE a = 5 AND b = 6的条件可以使用索引，但是像WHERE a = 5 OR b = 6的条件就不能直接使用索引。
一个类似WHERE x =42 OR x = 47 OR x = 53 OR x = 99 这样的查询可以分解成四个在x上的独立扫描，每个扫描使用一个条件， 最后将这些扫描的结果OR 在一起，生成最终结果。
另外一个例子是，如果我们在x 和y上有独立的索引，一个类似WHERE x = 5 AND y = 6 这样的查询可以分解为几个使用独立索引的子句，然后把这几个结果AND 在一起，生成最终结果。
联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。
对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。
这里记录两种方式，分别是
show status like ‘Handler_read%';大家可以注意：
+-----------------------+--------------+
| Variable_name | Value |
+-----------------------+--------------+
| Handler_read_first | 153 |
| Handler_read_key | 364 |
| Handler_read_next | 425 |
| Handler_read_prev | 598 |
| Handler_read_rnd | 605 |
| Handler_read_rnd_next | 860571 |
+-----------------------+--------------+
6 rows in set (0.00 sec)
————————————————分析这几个值，我们可以查看当前索引的使用情况：
查询 schema_unused_indexes库。
root@localhost [sys]>select * from schema_unused_indexes;
+-------------------+-------------+------------+
| object_schema | object_name | index_name |
+-------------------+-------------+------------+
| sysbench_testdata | sbtest1 | k_1 |
| sysbench_testdata | sbtest10 | k_10 |
| sysbench_testdata | sbtest3 | k_3 |
| sysbench_testdata | sbtest4 | k_4 |
| sysbench_testdata | sbtest5 | k_5 |
| sysbench_testdata | sbtest6 | k_6 |
| sysbench_testdata | sbtest7 | k_7 |
| sysbench_testdata | sbtest8 | k_8 |
| sysbench_testdata | sbtest9 | k_9 |
+-------------------+-------------+------------+
9 rows in set (0.00 sec)explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
新建一张表，
CREATE TABLE IF NOT EXISTS `article` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`author_id` int(10) unsigned NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`views` int(10) unsigned NOT NULL,
`comments` int(10) unsigned NOT NULL,
`title` varbinary(255) NOT NULL,
`content` text NOT NULL,
PRIMARY KEY (`id`)
);执行查询，
EXPLAIN
SELECT author_id
FROM `article`
WHERE category_id = 1 AND comments > 1
ORDER BY views DESC
LIMIT 1响应数据如下，
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: article
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra: Using where; Using filesort
1 row in set (0.00 sec)type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。
MySQL 在表里找到所需行的方式。包括（由左至右，由最差到最好）：
| All | index | range | ref | eq_ref | const,system | null |
