制作网站什么制作软件,淘宝联盟里的网站推广怎么做,一键生成app下载,泉州网站建设技术外包MySQL索引
MySQL支持诸多存储引擎#xff0c;而各种存储引擎对索引的支持也各不相同#xff0c;因此MySQL数据库支持多种索引类型#xff0c;如BTree索引#xff0c;哈希索引#xff0c;全文索引等等。为了避免混乱#xff0c;本文将只关注于BTree索引#xff0c;因为这…MySQL索引
MySQL支持诸多存储引擎而各种存储引擎对索引的支持也各不相同因此MySQL数据库支持多种索引类型如BTree索引哈希索引全文索引等等。为了避免混乱本文将只关注于BTree索引因为这是平常使用MySQL时主要打交道的索引。
MySQL官方对索引的定义为索引Index是帮助MySQL高效获取数据的数据结构。提取句子主干就可以得到索引的本质索引是数据结构。
MySQL索引原理
索引目的
索引的目的在于提高查询效率可以类比字典如果要查“mysql”这个单词我们肯定需要定位到m字母然后从下往下找到y字母再找到剩下的sql。如果没有索引那么你可能需要把所有单词看一遍才能找到你想要的如果我想找到m开头的单词呢或者ze开头的单词呢是不是觉得如果没有索引这个事情根本无法完成
咱们去图书馆借书也是一样如果你要借某一本书一定是先找到对应的分类科目再找到对应的编号这是生活中活生生的例子通用索引可以加快查询速度快速定位。
索引原理
所有索引原理都是一样的通过不断的缩小想要获得数据的范围来筛选出最终想要的结果同时把随机的事件变成顺序的事件也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样但显然要复杂许多因为不仅面临着等值查询还有范围查询(、、between)、模糊查询(like)、并集查询(or)、多值匹配in【in本质上属于多个or】等等。数据库应该选择怎么样的方式来应对所有的问题呢
我们回想字典的例子能不能把数据分成段然后分段查询呢最简单的如果1000条数据1到100分成第一段101到200分成第二段201到300分成第三段……这样查第250条数据只要找第三段就可以了一下子去除了90%的无效数据。但如果是1千万的记录呢分成几段比较好
稍有算法基础的同学会想到搜索树其平均复杂度是lgN具有不错的查询性能。但这里我们忽略了一个关键的问题复杂度模型是基于每次相同的操作成本来考虑的数据库实现比较复杂数据保存在磁盘上而为了提高性能每次又可以把部分数据读入内存来计算因为我们知道访问磁盘的成本大概是访问内存的十万倍左右所以简单的搜索树难以满足复杂的应用场景。
索引结构
任何一种数据结构都不是凭空产生的一定会有它的背景和使用场景我们现在总结一下我们需要这种数据结构能够做些什么其实很简单那就是每次查找数据时把磁盘IO次数控制在一个很小的数量级最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢就这样b树应运而生。
b树的索引结构解释 浅蓝色的块我们称之为一个磁盘块可以看到每个磁盘块包含几个数据项深蓝色所示和指针黄色所示如磁盘块1包含数据项17和35包含指针P1、P2、P3P1表示小于17的磁盘块P2表示在17和35之间的磁盘块P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据只存储指引搜索方向的数据项如17、35并不真实存在于数据表中。
b树的查找过程
如图所示如果要查找数据项29那么首先会把磁盘块1由磁盘加载到内存此时发生一次IO在内存中用二分查找确定29在17和35之间锁定磁盘块1的P2指针内存时间因为非常短相比磁盘的IO可以忽略不计通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存发生第二次IO29在26和30之间锁定磁盘块3的P2指针通过指针加载磁盘块8到内存发生第三次IO同时内存中做二分查找找到29结束查询总计三次IO。
真实的情况是3层的b树可以表示上百万的数据如果上百万的数据查找只需要三次IO性能提高将是巨大的如果没有索引每个数据项都要发生一次IO那么总共需要百万次的IO显然成本非常非常高。
b树性质
1、通过上面的分析我们知道间越小数据项的数量越多树的高度越低。这就是为什么每个数据项即索引字段要尽量的小比如int占4字节要比bigint8字节少一半。这也是为什么b树要求把真实的数据放到叶子节点而不是内层节点一旦放到内层节点磁盘块的数据项会大幅度下降导致树增高。当数据项等于1时将会退化成线性表。
2、当b树的数据项是复合的数据结构比如(name,age,sex)的时候b数是按照从左到右的顺序来建立搜索树的比如当(张三,20,F)这样的数据来检索的时候b树会优先比较name来确定下一步的所搜方向如果name相同再依次比较age和sex最后得到检索的数据但当(20,F)这样的没有name的数据来的时候b树就不知道下一步该查哪个节点因为建立搜索树的时候name就是第一个比较因子必须要先根据name来搜索才能知道下一步去哪里查询。
比如当(张三,F)这样的数据来检索时b树可以用name来指定搜索方向但下一个字段age的缺失所以只能把名字等于张三的数据都找到然后再匹配性别是F的数据了 这个是非常重要的性质即索引的最左匹配特性。MySQL 索引实现在MySQL中索引属于存储引擎级别的概念不同存储引擎对索引的实现方式是不同的本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。
MyISAM索引实现
MyISAM引擎使用BTree作为索引结构叶节点的data域存放的是数据记录的地址。
下图是MyISAM索引的原理图 这里设表一共有三列假设我们以Col1为主键则上图便是一个MyISAM表的主索引Primary key示意图。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中主索引和辅助索引Secondary key在结构上没有任何区别只是主索引要求key是唯一的而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引则此索引的结构如下图所示 同样也是一颗BTreedata域保存数据记录的地址。因此MyISAM中索引检索的算法为首先按照BTree搜索算法搜索索引如果指定的Key存在则取出其data域的值然后以data域的值为地址读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的之所以这么称呼是为了与InnoDB的聚集索引区分。
InnoDB索引实现
虽然InnoDB也使用BTree作为索引结构但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道MyISAM索引文件和数据文件是分离的索引文件仅保存数据记录的地址。而在InnoDB中表数据文件本身就是按BTree组织的一个索引结构这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键因此InnoDB表数据文件本身就是主索引。 上图是InnoDB主索引同时也是数据文件的示意图可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集所以InnoDB要求表必须有主键MyISAM可以没有如果没有显式指定则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键如果不存在这种列则MySQL自动为InnoDB表生成一个隐含字段作为主键这个字段长度为6个字节类型为长整形。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说InnoDB的所有辅助索引都引用主键作为data域。例如下图为定义在Col3上的一个辅助索引 这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效但是辅助索引搜索需要检索两遍索引首先检索辅助索引获得主键然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助例如知道了InnoDB的索引实现后就很容易明白为什么不建议使用过长的字段作为主键因为所有辅助索引都引用主索引过长的主索引会令辅助索引变得过大。再例如用非单调的字段作为主键在InnoDB中不是个好主意因为InnoDB数据文件本身是一颗BTree非单调的主键会造成在插入新记录时数据文件为了维持BTree的特性而频繁的分裂调整十分低效而使用自增字段作为主键则是一个很好的选择。
如何建立合适的索引
建立索引的原理
一个最重要的原则是最左前缀原理在提这个之前要先说下联合索引MySQL中的索引可以以一定顺序引用多个列这种索引叫做联合索引一般的一个联合索引是一个有序元组其中各个元素均为数据表的一列。另外单列索引可以看成联合索引元素数为1的特例。
索引匹配的最左原则具体是说假如索引列分别为ABC顺序也是ABC
那么查询的时候如果查询【A】【AB】 【ABC】那么可以通过索引查询如果查询的时候采用【AC】那么C这个虽然是索引但是由于中间缺失了B因此C这个索引是用不到的只能用到A索引如果查询的时候采用【B】 【BC】 【C】由于没有用到第一列索引不是最左前缀那么后面的索引也是用不到了如果查询的时候采用范围查询并且是最左前缀也就是第一列索引 那么可以用到索引但是范围后面的列无法用到索引
因为索引虽然加快了查询速度但索引也是有代价的索引文件本身要消耗存储空间同时索引会加重插入、删除和修改记录时的负担另外MySQL在运行时也要消耗资源维护索引因此索引并不是越多越好
在使用InnoDB存储引擎时如果没有特别的需要请永远使用一个与业务无关的自增字段作为主键。如果从数据库索引优化角度看使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。
InnoDB使用聚集索引数据记录本身被存于主索引一颗BTree的叶子节点上。这就要求同一个叶子节点内大小为一个内存页或磁盘页的各条数据记录按主键顺序存放因此每当有一条新的记录插入时MySQL会根据其主键将其插入适当的节点和位置如果页面达到装载因子InnoDB默认为15/16则开辟一个新的页节点。如果表使用自增主键那么每次插入新的记录记录就会顺序添加到当前索引节点的后续位置当一页写满就会自动开辟一个新的页。如下 这样就会形成一个紧凑的索引结构近似顺序填满。由于每次插入时也不需要移动已有数据因此效率很高也不会增加很多开销在维护索引上。
如果使用非自增主键如果身份证号或学号等由于每次插入主键的值近似于随机因此每次新纪录都要被插到现有索引页得中间某个位置如下 此时MySQL不得不为了将新记录插到合适位置而移动数据甚至目标页面可能已经被回写到磁盘上而从缓存中清掉此时又要从磁盘上读回来这增加了很多开销同时频繁的移动、分页操作造成了大量的碎片得到了不够紧凑的索引结构后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
因此只要可以请尽量在InnoDB上采用自增字段做主键。
建立索引的常用技巧
1、最左前缀匹配原则非常重要的原则mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配比如a 1 and b 2 and c 3 and d 4 如果建立(a,b,c,d)顺序的索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用到a,b,d的顺序可以任意调整。
2、和in可以乱序比如a 1 and b 2 and c 3 建立(a,b,c)索引可以任意顺序mysql的查询优化器会帮你优化成索引可以识别的形式
3、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)表示字段不重复的比例比例越大我们扫描的记录数越少唯一键的区分度是1而一些状态、性别字段可能在大数据面前区分度就是0那可能有人会问这个比例有什么经验值吗使用场景不同这个值也很难确定一般需要join的字段我们都要求是0.1以上即平均1条扫描10条记录
4、索引列不能参与计算保持列“干净”比如from_unixtime(create_time) ’2014-05-29’就不能使用到索引原因很简单b树中存的都是数据表中的字段值但进行检索时需要把所有元素都应用函数才能比较显然成本太大。所以语句应该写成create_time unix_timestamp(’2014-05-29’);
5、尽量的扩展索引不要新建索引。比如表中已经有a的索引现在要加(a,b)的索引那么只需要修改原来的索引即可当然要考虑原有数据和线上使用情况
MySQL优化
配置优化
配置优化指的MySQL 的 server端的配置一般对于业务方而言可以不用关注毕竟会有专门的DBA来处理但是对于原理的了解我想我们开发是需要了解的。
MySQL优化也可以参考
超级全面的MySQL优化面试解析
基本配置
innodb_buffer_pool_size
这是安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方这个值越大越好这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)20-25GB(32GB内存)100-120GB(128GB内存)。
innodb_log_file_size
这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1它都难于调整因为一方面你想让它更大来提高性能另一方面你想让它更小来使得崩溃后更快恢复。
幸运的是从MySQL 5.5之后崩溃恢复的性能的到了很大提升这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。一直到MySQL 5.5redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高了。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6你可以一开始就把它这是成4G。
max_connections
如果你经常看到‘Too many connections’错误是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接你需要比默认的151连接数更大的值。
max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。
InnoDB配置
innodb_file_per_table
这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里innodb_file_per_table OFF 或者为每张表的数据单独放在一个.ibd文件innodb_file_per_table ON。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。
这对于一些高级特性也是有必要的比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是有非常多的表比如10k。MySQL 5.6中这个属性默认值是ON因此大部分情况下你什么都不需要做。对于之前的版本你必需在加载数据之前将这个属性设置为ON因为它只对新创建的表有影响。
innodb_flush_log_at_trx_commit
默认值为1表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的比如在一个主节点上。但是对于磁盘读写速度较慢的系统它会带来很巨大的开销因为每次将改变flush到redo日志都需要额外的fsyncs。
将它的值设置为2会导致不太可靠reliable因为提交的事务仅仅每秒才flush一次到redo日志但对于一些场景是可以接受的比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了但在系统崩溃时可能丢失一些数据只适用于备份节点。
innodb_flush_method
这项配置决定了数据和日志写入硬盘的方式。一般来说如果你有硬件RAID控制器并且其独立缓存采用write-back机制并有着电池断电保护那么应该设置配置为O_DIRECT否则大多数情况下应将其设为fdatasync默认值。sysbench是一个可以帮助你决定这个选项的好工具。innodb_log_buffer_size这项配置决定了为尚未执行的事务分配的缓存。其默认值1MB一般来说已经够用了但是如果你的事务中包含有二进制大对象或者大文本字段的话这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量如果它不是0增加innodb_log_buffer_size。
其他设置
query_cache_size
query cache查询缓存是一个众所周知的瓶颈甚至在并发并不多的时候也是如此。最佳选项是将其从一开始就停用设置query_cache_size 0现在MySQL 5.6的默认值并利用其他方法加速查询优化索引、增加拷贝分散负载或者启用额外的缓存比如memcache或redis。
如果你已经为你的应用启用了query cache并且还没有发现任何问题query cache可能对你有用。这是如果你想停用它那就得小心了。
log_bin
如果你想让数据库服务器充当主节点的备份节点那么开启二进制日志是必须的。如果这么做了之后还别忘了设置server_id为一个唯一的值。就算只有一个服务器如果你想做基于时间点的数据恢复这开启二进制日志也是很有用的从你最近的备份中恢复全量备份并应用二进制日志中的修改增量备份。
二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽你可以用 PURGE BINARY LOGS 来清除旧文件或者设置 expire_logs_days 来指定过多少天日志将被自动清除。记录二进制日志不是没有开销的所以如果你在一个非主节点的复制节点上不需要它的话那么建议关闭这个选项。
skip_name_resolve
当客户端连接数据库服务器时服务器会进行主机名解析并且当DNS很慢时建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了因此在添加这项设置到一个已有系统中必须格外小心。
SQL 调优
一般要进行SQL调优那么就说有慢查询的SQL系统或者server可以开启慢查询日志尤其是线上系统一般都会开启慢查询日志如果有慢查询可以通过日志来过滤。但是知道了有需要优化的SQL后下面要做的就是如何进行调优
慢查询优化基本步骤
先运行看看是否真的很慢注意设置SQL_NO_CACHEwhere条件单表查锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起单表每个字段分别查询看哪个字段的区分度最高explain查看执行计划是否与1预期一致从锁定记录较少的表开始查询order by limit 形式的sql语句让排序的表优先查了解业务方使用场景加索引时参照建索引的几大原则观察结果不符合预期继续从0分析
常用调优手段
执行计划explain
在日常工作中我们有时会开慢查询去记录一些执行时间比较久的SQL语句找出这些SQL语句并不意味着完事了我们常常用到explain这个命令来查看一个这些SQL语句的执行计划查看该SQL语句有没有使用上了索引有没有做全表扫描这都可以通过explain命令来查看。
所以我们深入了解MySQL的基于开销的优化器还可以获得很多可能被优化器考虑到的访问策略的细节以及当运行SQL语句时哪种策略预计会被优化器采用。
使用explain 只需要在原有select 基础上加上explain关键字就可以了如下
mysql explain select * from servers;
---------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------
| 1 | SIMPLE | servers | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
---------------------------------------------------------------------------------
1 row in set (0.03 sec)简要解释下explain各个字段的含义
id : 表示SQL执行的顺序的标识,SQL从大到小的执行select_type表示查询中每个select子句的类型table显示这一行的数据是关于哪张表的有时不是真实的表名字type表示MySQL在表中找到所需行的方式又称“访问类型”。常用的类型有ALL, index, range, ref, eq_ref, const, system, NULL从左到右性能从差到好possible_keys指出MySQL能使用哪个索引在表中找到记录查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询使用Keykey列显示MySQL实际决定使用的键索引如果没有选择索引键是NULL。key_len表示索引中使用的字节数可通过该列计算查询中使用的索引的长度key_len显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出的ref表示上述表的连接匹配条件即哪些列或常量被用于查找索引列上的值rows表示MySQL根据表统计信息及索引选用情况估算的找到所需的记录所需要读取的行数理论上行数越少查询性能越好Extra该列包含MySQL解决查询的详细信息
EXPLAIN的特性
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况EXPLAIN不考虑各种CacheEXPLAIN不能显示MySQL在执行查询时所作的优化工作部分统计信息是估算的并非精确值EXPALIN只能解释SELECT操作其他操作要重写为SELECT后查看执行计划。
实战演练
表结构和查询语句
假如有如下表结构
circlemessage_idx_0 | CREATE TABLE circlemessage_idx_0 (circle_id bigint(20) unsigned NOT NULL COMMENT 群组id,from_id bigint(20) unsigned NOT NULL COMMENT 发送用户id,to_id bigint(20) unsigned NOT NULL COMMENT 指定接收用户id,msg_id bigint(20) unsigned NOT NULL COMMENT 消息ID,type tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT 消息类型,PRIMARY KEY (msg_id,to_id),KEY idx_from_circle (from_id,circle_id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_bin通过执行计划explain分析如下查询语句
mysql explain select msg_id from circlemessage_idx_0 where to_id 113487 and circle_id10019063 and msg_id6273803462253938690 and from_id ! 113487 order by msg_id asc limit 30;
-------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | circlemessage_idx_0 | range | PRIMARY,idx_from_circle | PRIMARY | 16 | NULL | 349780 | Using where |
-------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql explain select msg_id from circlemessage_idx_0 where to_id 113487 and circle_id10019063 and from_id ! 113487 order by msg_id asc limit 30;
---------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | circlemessage_idx_0 | index | idx_from_circle | PRIMARY | 16 | NULL | 30 | Using where |
---------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)问题分析
通过上面两个执行计划可以发现当没有msg_id xxx这个查询条件的时候检索的rows要少很多并且两者查询的时候都用到了索引而且用到的还只是主键索引。那说明索引应该是不合理的没有发挥最大作用。
分析这个执行计划可以看到当包含msg_id xxx 查询条件的时候rows有34w多行这种情况说明检索太多要么就是表里面确实有这么大要么就是索引不合理没有用到索引大都情况是没用合理用到索引。列中所用到的索引也是PRIMARY那就可能是(msg_id,to_id)的其中一个注意我们建立表的时候msg_id索引的顺序是在to_id前面的因此MySQL查询一定会优先用msg_id索引在使用了msg_id索引后就已经检索出了34w行并且由于msg_id的查询条件是大于等于因此再这个查询条件后就不能再用到to_id的索引。
然后再看key_len长度为16结合 key为PRIMARY那么可以分析得知只有一个主键索引被用到。
最后看看 type 值是range那么就说明这个查询要么是范围查询要么就是多值匹配。 请注意from_id ! xxx这样的语句是无法用到索引的。只有from_id xxx就可以用到所以因此from id 的索引其实可以不用建立索引的时候就要考虑清楚 如何优化
既然知道索引不合理那么就要分析并调整索引。一般而言我们既然要从单表里面查询那么就需要能够知道大体单表里面大致会有哪些数据现在的量级大概是多少。
然后开始下一步的分析既然msgid是被设置为了主键那一定是全局唯一的所有有多少数据量就至少会有多少条msgid那么检索msg_id基本就是检索整个表了。我们要做的优化就是要尽量减少索引减少查询的行数那么就需要思考通过查询哪些字段才能够减少行数比如一个张表里面所属某个用户的数据会不会比查询msgid的行数要少查询某个用户并且是属于某个圈子的那会不会就更少了等等。
然后根据实际情况分析单表里面命中to_id 的行数应该是会小于命中msg_id的因此要首先保证能够使用到to_id的索引为此可以设置主键的时候把msg_id和to_id的顺序交互一下但是由于已经是线上的表已经有了大量数据并且业务开始运行这种情况下修改主键会引发很多问题当然修改索引是OK的因此不建议直接修改主键。
那么为了保证有效使用to_id的索引就要新建一个联合索引那么新建的联合索引的第一索引字段必然是to_id针对此业务场景最好能够再加上circle_id索引这样可以快速索引这样就得到了新的联合索引(to_id,circle_id)的索引然后因为要找msg_id为此在此基础上再加上msg_id。最终得到的联合索引为(to_id,circle_id,msg_id)这样的话就能够快速检索这样的查询语句了where to_id xxx and circle_id xxx and msgId xxx
当然索引的建立也不是说某个sql 语句需要啥索引就建立某个联合索引这样的话索引太多的话写的性能受影响插入、删除、修改然后存储空间也会相应增大另外mysql在运行时也会消耗资源维护索引所以索引并不是越多越好需要结合查询最频繁、最影响性能的sql来建立合适的索引。需要再说明的是一个联合索引或者一组主键就是一个btree多个索引就是多个btree
总结
首先我们需要深入理解索引的原理和实现当理解了原理后才能够更有助于我们建立合适的索引。然后我们建立索引的时候不要想当然要先想清楚业务逻辑再建立对应的表结构和索引。 需要再次强调如下几点:
索引不是越多越好区分主键和索引理解索引结构原理理解查询索引规则
参考 http://blog.codinglabs.org/articles/theory-of-mysql-index.html https://tech.meituan.com/2014/06/30/mysql-index.html
