mysql三层架构+------------+|客户|+------------+↓+-------------+|服务器|+------------+↓+------------+|存储引擎|+------------+mysqlserver层结构当client需要查询一条sql时，在server内部进行以下4个步骤+----------------+|接头|+------------+↓+------------+|分析仪|+------------+↓+------------+|优化器|+--------------+↓+------------+|执行人|+------------+索引加速数据访问的作用将随机io改为顺序io大大减少了服务器需要扫描的数据量，帮助服务器避免排序和临时表，减少了ios的数量，并提高了磁盘寿命。索引缺点加入索引后，查询速度会变快，但是当数据量很大时，增删改查就会慢下来；因为每次修改数据，除了数据本身，数据库还需要维护索引的B+树；一张表最多可以建多少列和索引innoDB：最多创建1017列，最多64个二级索引，加上主键65个，单个索引最多可以包含16列，最大长度索引的大小为767字节（其执行格式为REDUNDANT，COMPACT最大长度为767字节，行格式为DYNAMIC，COMPRESSED最大长度为3072字节）。最大大小为65536字节mysiam：最多4096列，最多64个二级索引，单个索引最多可包含16列，一个索引的最大长度为1000字节，最大行大小为65536字节主索引和secondaryindex==primaryindex==：索引和数据存储在一起，都在同一个B+树的叶子节点中。通常，主键索引是主索引。主键就是聚簇索引，表有主键，表有聚簇索引，所以综上所述，==主键=主索引=聚簇索引====二级索引==：二级叶子节点索引树存储主键而不是数据。也就是说，找到索引后，得到对应的主键，再回到主索引，找到主键对应的数据记录。在innodb中，所有的二级索引B+树都指向主索引的key值，所以在查询二级索引的时候，需要回表找一整行数据；索引不管是存储在内存中还是在磁盘上都是存储在磁盘中，因为需要持久化存储，所以内存中也是有的，只是每次查询的时候从磁盘加载到内存中。你如何找到有或没有索引的数据？没有索引：没有索引，会扫描整张表，也就是一张一张的查找，这无疑是低效的，也是成本io的；Withanindex：如果有一个索引，就会为这个索引生成一个B+树。借助这棵树，查询数据的效率将提高数千倍；查询比较慢，一般卡在哪里？卡在io上，也就是输入输出。解决方法是提高io效率，减少io次数，减少io量：query尽量减少查询数据，尽量避免使用select*fromxxx从磁盘读取数据，多少是用来读书的？肯定不是，mysql与磁盘交互时，是以页为单位传输的。默认情况下每页的大小是16K，就像我们在电脑上新建一个txt文件，里面什么都没有，但是还是占用4KB，mysql也是一样，有自己的最小页大小，可以查看通过innodb_page_size参数的数据页大小mysql>showvariableslike'innodb_page_size';+-----------------+--------+|变量名|值|+------------------+------+|innodb_page_size|16384|+-----------------+--------+很重要的概念：局部性原理数据和程序有狙击组的倾向，已访问before数据很可能会被再次查询。空间局部性和时间局部性。磁盘预读存储器在与磁盘交互时，一般有一个最小的逻辑单元，称为页（datapage）。页面的大小由系统决定。一般为4k或者8k，必须是整数倍，4,8,16,32,64,128.....数据交互的时候，可以到页面的整数倍读取，innodb存储引擎，每次读取取出的数据都是16k。为什么索引可以加快查询速度？要解决这个问题，首先要知道索引是如何存储的。索引是如何存储的？一个索引对应一个B+树。如果一张表有10个索引，那么就有10棵B+树。OLAP在线分析处理---数据仓库--hive分析海量历史数据产生决策影响OLTP在线事务处理——关系型数据库要求在短时间内返回正确的数据为什么要用B+树来存储索引？哈希索引不适合数据库的结构。如果是单个等值查询（通过key查找value），会非常快。它不支持范围查询。进行范围查询时，必须逐一遍历，比较内存需求。high，哈希冲突会造成数据哈希不均匀，会产生大量的线性查询，浪费时间。mysql中有哈希索引吗答：有些内存存储引擎使用哈希索引。innodb支持adaptivehash，由mysql提供来决定是用hash还是tree来存储，手动不能干预存储引擎的分类innodb：持久化+内存memory：只存储在内存中，不支持持久化，会掉电消失，结构为hashtablemyisam:persistencecanbein建表就是自己指定存储引擎，像这样createtable(idbigint(20)primarykey,namevarchar(10))engine='innodb';MySQL5.5之前，默认内置的存储引擎是Myisam，MySQL5.5之后的版本，MySQL默认内置的存储引擎已经是InnoDB。树的分类二叉树BST树（二叉搜索树）必须保证序列AVL树平衡二叉树，而有序红黑树B-树B+树很早很早很早以前，索引是用二叉树实现的。二叉树本身是无序的，因此开发了有序的BST树。BST插入数据时必须保证顺序，左子树必须小于根节点，右子树必须大于根节点；如果bst树按连续递增或递减的顺序插入，就会退化为链表，于是推导出会旋转的平衡二叉树avl。avl树插入慢查询快，因为在插入的时候，为了保证平衡，需要进行轮换操作。有一个平衡二叉树的条件。为了保证平衡，最短子树和最长子树的长度差不能超过1，所以经常需要轮换，而轮换也需要性能开销。所以平衡二叉树只能用于插入少查询多的数据。当我们的查询和插入一样多的时候，用平衡二叉树是不合适的，所以这时候就衍生出一个新的数据结构：redandblack只要红黑树的最长子树不超过2次最短子树，发现树的深度会随着数据的插入而变深。树越深，io次数越多，会影响数据读取的效率；所以为了解决这个问题，需要把有序二叉树变成有序多叉树，也就是B-treeB-tree每一层存储的数据，但是每个磁盘块可以存储的内容是有限的。除了索引之外，还必须存储数据，而数据占用的空间较多，这就使得可以存储的索引变少了。如果要插入更多数据，则必须添加一个层。变成了四层，但是这样会增加io量，所以为了解决这个问题，衍生了一颗B+树；B+树只在叶子节点存储数据，非叶子层只存储索引，B+树不仅可以自上而下查找，还可以自下而上查找数据；聚簇索引和非聚簇索引的区别InnoDB只能有一个聚簇索引，但是非聚簇索引有很多聚簇索引：数据和索引放在一起，就是聚簇索引，像这样+----------+|指标值|+----------+|data|+------------+非聚集索引：数据和索引分开存储。在B+树中，索引值对应文件地址，是非聚集索引。可以肯定的是，所有的非聚簇索引都指向聚簇索引。像这样+-------------+|索引值|+------------+|数据文件地址|+-----------+Innodb，如果id是主键，那我加名字字段作为索引，这棵树是如何存储的？一开始我的主键是id字段，所以B+树中叶子节点的结构是这样的+---------+|主键索引值|+----------+|data|+----------+这时候我把name字段设置为索引。这个时候mysql也为这个name索引创建了一个B+树。这棵树的叶节点存储在它不再是数据。如果存储数据，会造成冗余，所以name索引树存储的是主键id，像这样+----------+|叶|+-----------+/\+--------++---------+|鑫||洞|+----------++----------+|主键ID||primarykeyid|+----------++----------+Innodb插入数据时必须包含一个索引键值向innodb插入数据时，必须包含一个索引键值包括。这个索引的键值可以是主键，如果没有主键，就是唯一键。如果没有唯一键，则为自生成的6字节rowid；myisam使用非聚集索引；innodb只有一个聚簇索引和多个非聚簇索引；什么是存储引擎索引的创建与存储引擎挂钩。存储引擎指示不同的数据以不同的文件格式存储在磁盘上。Mysql常用的存储引擎有三种内存：内存级存储引擎，不支持持久化，断电丢失数据。哈希索引myisam：mysql5.5之前默认的存储引擎，每次修改数据都会锁表，不支持事务。Innodb：mysql5.5后默认的存储引擎，支持事务和行锁；mysql会自动创建索引。Innodb只能有一个Clusteredindex，但是有很多个non-clusteredindex。向InnoDB中插入数据时，必须包含索引键值。这个索引的关键值可以是有主见的。如果没有主键，则为唯一键。如果没有唯一键，则为自生成的6字节rowid；为什么只能有一个聚簇索引，因为只有一个主键，而聚簇索引对应主键字段，只有主键的索引B+树会存储数据，另外两个一级索引存储主键的值；如果每个二级索引树都存储数据，会造成数据冗余；myisam和innodb的区别是myisam支持表锁，innodb支持表锁和行锁。myisam不支持外键，innodb支持外键，myisam不支持事务，innodb支持事务。在电脑内存充足的情况下，innodb比myisam效率更高，因为innodb是优先读缓存，而myisam直接从磁盘读取数据。数据存储存放在哪个目录？在：你的mysql目录/data/db1/目录下，其中后缀为.opt的文件为配置文件，指定数据库的字符集编码后缀。后缀为.frm的文件为表结构，后缀为.idb表示当前表使用的存储引擎为innodb，后缀为.myd为数据文件。使用的存储引擎是myisam，后缀为.myi的是索引文件，使用的存储引擎是myisam。索引监控查询语句为：showstatuslike'Handler_read%';结果说明：Handler_read_first代表索引头被读取的次数。如果这个值很高，说明全索引扫描次数很多。handler_read_key：代表一个索引被使用的次数。如果我们增加一个新的索引，然后使用这个索引查询数据，可以查看Handler_read_key是否增加了。如果有增加，说明SQL使用了索引。数字越大越好，数字越大，使用的索引查询越多；handler_read_next：表示后面的索引读取，根据（主）键依次读取接下来的N行Handler_read_last：从（主）键的最后一个位置开始读取Handler_read_prev：表示读取索引的上列，一般会出现在ORDERBY...DESC.handler_read_rnd：表示读取固定位置的行。如果这个值很高，说明大量的结果集被排序，进行了全表扫描，关联查询没有使用合适的KEY。handler_read_rnd_next：代表表扫描多，查询性能低。mysql的mysql回表、索引覆盖、最左匹配、索引下推请看我的另一篇文章：添加链接说明
