MySQL学习(五)--索引优化

最新推荐文章于 2024-07-17 10:15:00 发布

可爱的小沫沫

最新推荐文章于 2024-07-17 10:15:00 发布

阅读量262

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/sfgu4444/article/details/90082077

mysql 专栏收录该内容

5 篇文章

订阅专栏

本文深入解析数据库索引的种类，包括普通索引、唯一索引、主键索引和全文检索，探讨B-tree和Hash索引的特点与使用限制，以及索引在查询优化、排序、减少I/O操作等方面的作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引类型:（存储引擎层）

索引分为：普通索引(create index)、唯一索引(creare unique index)、主键索引(primary index)、全文检索(fulltext)

索引语句:
CREATE INDEX index_name ON table (column(lenght)) --直接创建索引
ALTER TABLE table_name ADD INDEX index_name ON (column(lenght)) --创建表时候同时创建索引
ALTER TABLE em_ca ADD INDEX INDEX_CA_CONTACT (`CA_CONTACT`); 创建普通索引
ALTER TABLE em_ca ADD INDEX INDEX_CA_CONTACT (`CA_CONTACT`，‘CA_NAME’); 创建组合索引
CREATE unique INDEX INDEX_CA_ALIAS on em_ca(CA_ALIAS); 创建唯一索引
ALTER TABLE 表名 add primary key(列名)  创建主键索引
ALTER TABLE admin_credence_info DROP INDEX ACI_PK; 删除索引

B-tree索引特点:
B-tree过引以B+树的结构存储数据
B-tree索引能过加快数据的查询速度
B-tree索引更适合进行范围查找
适合进行范围查找

在什么情况用到b树索引
全值匹配查询
匹配最左前缀的查询
匹配列前缀查询

匹配范围值得查询
orer_sn>9876659 and order_sn<9876325

精确匹配左前列并范围匹配另外一列
只访问索引的查询

Btree索引的使用限制
如果不是按照索引最左列开始查找，则无法使用索引
使用索引时不能跳过索引中的列
Not in和<>操作无法使用索引
如果查询中有莫格列的范围查询，则其右边所有列都无法使用索引

Hash索引的特点
Hash索引时基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到hash索引。
对于Hash索引中的多有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码。

Hash索引的限制
Hash索引必须进行二次查询
Hash索引无法用于排序
Hash索引不支持部分索引查找也不支持范围查找
Hash索引中的Hash码的计算可能存在Hash冲突

为什么要是有索引
1.索引大大减少了存储引擎需要扫描的数据量
2.索引可以帮助我们进行排序以避免使用临时表
3.索引可以把随机I/O变成顺序I/O

索引不是越多越好
索引会增加写操作的成本
大多的索引会增加查询优化器的选择时间

索引列上不能使用表达式或函数
前缀索引和索引列的选择性
索引的选择性是不重复的索引值和表的记录数的比值

联合索引
如何选择索引列的顺序
   经常会被使用的列优先
   选择性高的列优先
   宽度小的列优先

覆盖索引
   优点:可以优化缓存，减少磁盘IO操作
   可以减少随机IO，变随机IO操作变为顺序IO操作
   可以避免对Innodb主键索引的二次查询
   可以避免MyISAM表进行系统调用

无法使用覆盖索引
   存储引擎不支持覆盖索引
   查询中使用了太多的列
   使用了双%号的like查询

使用索引扫描来优化排序
索引的列顺序和Order By子句的顺序完全一致
索引中所有的方向(升序、降序)和Order by子句完全一致
Order by 中的字段全部在关联表的第一张表中

使用Hash索引优化查询
支出处理建筑的全值匹配查找
所使用的Hash函数决定者索引键的大小

利用索引优化锁
索引可以减少锁定的行数
索引可以加快处理速度，同时加快了锁的释放

索引的维护和优化
删除重复和冗余的索引
工具pt-duplicate-key-checker h=127.0.0.1

查找未被使用过得索引
select object_schema,object_name,index_name,b.'TANLE_ROWS' FROMperormance_schema.table_io_waits_summary_by_index_usage a JOIN information_schema.table b ON a.'OBJECT_SCHEMA'=b.'TABLE_NAME' a.'OBJECT_NAME'=b.'TABLE_NAME' WHERE index_name IS NOT NULL AND count_star=0 ORDER BY object_schema,onject_name;

更新索引统计信息及减少索引碎片
   analyze table table_name
   optimize table table_name
       使用不当会导致锁表