mysql-高性能索引策略

最新推荐文章于 2023-06-08 14:45:32 发布
最新推荐文章于 2023-06-08 14:45:32 发布
阅读量139 收藏
点赞数
分类专栏： mysql

原文转自：http://www.cnblogs.com/happyflyingpig/p/7655762.html

独立索引：

　　独立索引是指索引列不能是表达式的一部分，也不能是函数的参数

　　例1：

SELECT actor_id FROM actor WHERE actor_id+1=5 --这种写法，就算在actor_id上建立了索引，也不起效

　　例2:

SELECT .... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10 --这也是一种错误的写法

 

多列索引（联合索引）&选择合适的索引列顺序：

　　多列索引（Multiple-Column Indexes）也称为复合索引（composite index），也即同时对多个列建立索引。

　　什么时候用多列索引？

  • 当出现服务器对多个索引做相交操作时(通常有多个AND条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
  • 当服务器需要对多个索引做联合操作时(通常有多个OR条件)，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回大量数据的时候。

　　多列索引的生效规则：

　　比如(a,b,c)，abc都是拍好序的，在任意一段a的下面b都是排好序的,任何一段b下面c都是拍好序的。多列索引的生效原则是从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；

还需注意：(a,b,c)多列索引和 (a,c,b)是不一样的，看上面的图也看得出来关系顺序是不一样的；

分析几个实际例子来加强理解

 

复制代码

复制代码

(0)select * from mytable where a=3 and b=5 and c=4;   --abc三个索引都在where条件里面用到了，而且都发挥了作用
(1)select * from mytable where  c=4 and b=6 and a=3;  --这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样
(2)select * from mytable where a=3 and c=7;  --a用到索引，b没有用，所以c是没有用到索引效果的
(3)select * from mytable where a=3 and b>7 and c=3;  --a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
(4)select * from mytable where b=3 and c=4;  --因为a索引没有使用，所以这里 bc都没有用上索引效果
(5)select * from mytable where a>4 and b=7 and c=9;  --a用到了  b没有使用，c没有使用
(6)select * from mytable where a=3 order by b;  --a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的
(7)select * from mytable where a=3 order by c;  --a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
(8)select * from mytable where b=3 order by a;  --b没有用到索引，排序中a也没有发挥索引效果

复制代码

复制代码

　　对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。(参考①)

　　当不需要考虑排序和分组时，将选择性最高的列放到前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找

 

前缀索引和索引的选择性：

　　前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：

　　1.不能再OORDER BY 或 GROUP BY 中使用前缀索引；

　　2.也不能把他们用作覆盖索引(Covering index)。

　　建立前缀索引的语法：

　　ALTER TABLE table_name ADD KEY(column_name(prefix_length));

　　示例：

　　ALTER TABLE city ADD KEY(cityname(7));

　　什么叫做索引的选择性呢？①

　　所谓索引的选择性(Selectivity),是指不重复索引值(也叫作基数，Cardinality)与表记录数（#T）的比值

　　Selectivity = Cardinality / #T

　　显然选择性的取值范围为(0,1]，选择性越高的索引值价值越大

复制代码

复制代码

   SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

  1. +-------------+
  2. | Selectivity |
  3. +-------------+
  4. | 0.0379      |
  5. +-------------+

复制代码

复制代码

 

　　比如employees表只有一个索引<emp_no>，那么如果我们想按名字搜索一个人，就只能全表扫描了：

复制代码

复制代码

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra 　　　　|
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| 1  | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

复制代码

复制代码

 

这样全表扫描效率很低，所以我们考虑到把名字建立索引，有两种选择，建<first_name>或<first_name,last_name>，看下两个索引的选择性:

复制代码

复制代码

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

复制代码

复制代码

 

　　从结果看显然<first_name>选择性太低，<first_name,last_name>选择性好。但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如<first_name, left(last_name, 3)>，看看其选择性：

复制代码

复制代码

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

复制代码

复制代码

 

　　选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

复制代码

复制代码

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+

复制代码

复制代码

 

　　这时选择性已经很理想了，而这个索引的长度只有18，比<first_name, last_name>短了接近一半，我们把这个前缀索引 建上：

ALTER TABLE employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

 

　　此时再执行一遍按名字查询，比较分析一下与建索引前的结果：

复制代码

复制代码

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

复制代码

复制代码

 　　性能的提升是显著的，查询速度提高了120多倍。

聚簇索引

覆盖索引

冗余索引和覆盖索引

未使用的索引

　　应该删除未被使用的索引。有两个工具可以帮助定位未使用的索引。

　　1.在Percona Server或者MariaDB中先打开userstates服务器变量(默认是关闭的)，然后让服务器正常运行一段时间，再通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引的使用频率。

　　2.在Percona Toolkit中的pt-index-usage,该工具可以读取查询日志，并对日志中的每条查询进行EXPLAIN操作，然后打印出关于索引和查询的报告

索引和锁　　

　　InnoDB只有在访问行的时候才会对其枷锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量　　

　　InnoDB在二级索引上使用共享(读)锁，但访问主键索引需要排他(写)锁。这消除了覆盖索引的可能性，并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE 或非锁定查询要慢很多

InnoDB的主键选择与插入优化

　　在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。为什么呢？

　　因为InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

　　如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

    　　　　　　　　　　　　　　　　　　　　　　　　

 

　　这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

　　如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

　　　　　　　　　　　　　　　　　　　　　　　　　　

　　此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

　　因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

参考文献:

 [1] Baron Schwartz等 著，宁海元等 译 ；《高性能MySQL》（第3版）； 电子工业出版社 ，2013

 [2] 张洋blog， http://blog.codinglabs.org/articles/theory-of-mysql-index.html

 [3]匿名blog， http://www.cnblogs.com/codeAB/p/6387148.html

z1012890225
关注
专栏目录
mysql 重复率高字段 索引_MySQL的高性能索引策略
weixin_42508785的博客
02-02 2920
前言首先，如果不是很理解索引的底层结构原理的话，建议先看一下我的另一篇介绍索引的底层原理。(点击跳转)本篇文章是大部分摘入高性能MySQL的笔记，也有参考网上的各种博客整理。仅供大家学习和复习！独立的列独立的列是指索引不能参与运算，要将索引索引单独的放在表达式的一列。像下面的语句就不会用到索引。mysql> SELECT actor_id FROM sakila.actor WHERE ac...
mysql-server-mysql-5.7.36.tar.gz
09-16
1. **InnoDB存储引擎优化**：MySQL 5.7对InnoDB存储引擎进行了大量优化，包括更快的插入速度、更高的并发性能以及更强大的事务处理能力。InnoDB也是默认的存储引擎，支持事务处理和行级锁定。 2. **查询优化器改进*...
mysql 参考文献_后记&参考文献
weixin_33026067的博客
02-01 6729
这篇文章断断续续写了半个月，主要内容就是上面这些了。不可否认，这篇文章在一定程度上有纸上谈兵之嫌，因为我本人对MySQL的使用属于菜鸟级别，更没有太多数据库调优的经验，在这里大谈数据库索引调优有点大言不惭。就当是我个人的一篇学习笔记了。其实数据库索引调优是一项技术活，不能仅仅靠理论，因为实际情况千变万化，而且MySQL本身存在很复杂的机制，如查询优化策略和各种引擎的实现差异等都会使情况变得更加复杂...
MYSQL高可用方案探究(七)参考文献
weixin_30553777的博客
09-04 880
参考文献 http://www.xifenfei.com/1465.html http://blog.chinaunix.net/uid-20639775-id-154605.html http://blog.chinaunix.net/uid-20639775-id-154604.html http://www.eit.name/blog/read.php?472 h...
mysql alter 语句用法,添加、修改、删除字段、索引、主键等
weixin_30265171的博客
10-26 464
修改表名： ALTERTABLEadmin_userRENAMETOa_use //增加主键 [sql]view plaincopy altertabletabelnameaddnew_field_idint(5)unsigneddefault0notnullauto_increment...
mysql索引之九：重复索引和冗余索引，索引和锁
weixin_34075268的博客
12-03 106
索引的创建对数据库的查询性能有很好的优化，但是不恰当的索引使用也会给数据库带来性能上的影响。 一、重复索引和冗余索引 SQL索引创建需要注意的情况： 重复索引：一个字段上建立多个索引 冗余索引：指多个索引的前缀列相同，或者在联合索引中包含了主键的索引 。 　　mysql允许在相同列上创建多个索引，无论是有意还是无意，mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL优化五-高性能的8个索引策略
qiuweifan的博客
06-08 1070
正确创建和使用索引策略是实现高性能查询的基础，本文总结8个索引策略。
MySQL高性能索引策略
浪人与酒
04-03 594
高性能索引策略 正确地创建和使用索引是实现高性能查询的基础。高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。 独立的列 我们通常会看到一些查询不当地使用索引，或者使得 MySQL 无法使用已有的索引。如果查询的列不是独立的，则 MySQL 就不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。 例如，下面查询无法使用索引 select actor_id from where sakila.actor where actor_id + 1
mysql-server-mysql-8.3.0.tar.gz
03-28
2. **InnoDB存储引擎优化**：InnoDB是MySQL默认的事务处理引擎，8.3.0版本可能会进一步优化其性能，包括更快的索引构建、更高效的锁机制以及更好的内存管理。 3. **窗口函数**：这是MySQL 8.0引入的新特性，允许在...
mysql面试题-mysql经典面试题目-数据库的基本概念-SQL语法-事务处理-索引优化-性能调优-mysql-面试题目
01-20
【MySQL面试题】在面试MySQL相关的职位时，面试官可能会问到一系列关于数据库基础、SQL语法、事务处理、索引优化以及性能调优的问题。以下是一些可能的面试重点： 1. **数据库基本概念**： - 数据库是用于存储和...
基于Vue的老年人健康管理平台设计源码
09-24
该项目是基于Vue的老年人健康管理平台设计源码，共计136个文件，其中包括50个JavaScript文件、17个Python源文件、14个Python字节码文件、11个SVG图像文件、8个Markdown文档、5个XML文件、5个JSON配置文件、5个PNG图片文件、5个Vue组件文件以及2个Git忽略文件，实现了前端界面与后端逻辑的完整构建。
基于Vue.js的麻将计分系统设计源码
09-24
该项目是基于Vue.js的麻将计分系统设计源码，共包含61个文件，涵盖28个JavaScript文件、6个map文件、5个CSS样式文件、5个SVG图形文件、4个HTML页面、3个JSON配置文件、2个Vue组件文件、1个开发环境配置文件、1个生产环境配置文件、1个Git忽略文件。项目前端采用Vue框架开发，后端代码尚未开源。
Bulk Rename Utility(批量改名) v4.0.0 一款功能强大的批量文件重命名工具.rar
09-24
Bulk Rename utility(文件重命名软件)是一款简便实用的批量文件重命名工具。怎么快速多个文件改名？Bulk Rename utility(文件重命名软件)轻松帮助用户批量修改文件名。该软件具有灵活规则的自动编号文件，而且还能够根据多个灵活的标准快速重命名多个文件。 激活信息（大飞哥软件自习室）在压缩包文档内 软件特色 1、快速重命名多个文件，根据许多灵活的标准。 2、多功能的重命名方式：添加，替换文件名中插入文本。转换的情况下，添加号码。删除或更改文件扩展名。 3、在文件重命名前可以即时预览。 4、重命名的照片EXIF元数据(即“拍摄日期”，“分辨率”等信息嵌入在所有JPG图片文件)重命名你的照片文件名从一个毫无意义的dsc1790.jpg到一个新的文件名。 5、重命名MP3文件的ID3标签(又名MP3 ID3标签重命名)。 6、更改文件的创建日期和修改时间等。
geopandas安装包(pip install)_geopandas-0.14.1.tar.zip
09-24
在使用Python来安装geopandas包时，由于geopandas依赖于几个其他的Python库（如GDAL, Fiona, Pyproj, Shapely等），因此安装过程可能需要一些额外的步骤。以下是一个基本的安装指南，适用于大多数用户： 使用pip安装 确保Python和pip已安装： 首先，确保你的计算机上已安装了Python和pip。pip是Python的包管理工具，用于安装和管理Python包。 安装依赖库： 由于geopandas依赖于GDAL, Fiona, Pyproj, Shapely等库，你可能需要先安装这些库。通常，你可以通过pip直接安装这些库，但有时候可能需要从其他源下载预编译的二进制包（wheel文件），特别是GDAL和Fiona，因为它们可能包含一些系统级的依赖。 bash pip install GDAL Fiona Pyproj Shapely 注意：在某些系统上，直接使用pip安装GDAL和Fiona可能会遇到问题，因为它们需要编译一些C/C++代码。如果遇到问题，你可以考虑使用conda（一个Python包、依赖和环境管理器）来安装这些库，或者从Unofficial Windows Binaries for Python Extension Packages这样的网站下载预编译的wheel文件。 安装geopandas： 在安装了所有依赖库之后，你可以使用pip来安装geopandas。 bash pip install geopandas 使用conda安装 如果你正在使用conda作为你的Python包管理器，那么安装geopandas和它的依赖可能会更简单一些。 创建一个新的conda环境（可选，但推荐）： bash conda create -n geoenv python=3.x anaconda conda activate geoenv 其中3.x是你希望使用的Python版本。 安装geopandas： 使用conda-forge频道来安装geopandas，因为它提供了许多地理空间相关的包。 bash conda install -c conda-forge geopandas 这条命令会自动安装geopandas及其所有依赖。 注意事项 如果你在安装过程中遇到任何问题，比如编译错误或依赖问题，请检查你的Python版本和pip/conda的版本是否是最新的，或者尝试在不同的环境中安装。 某些库（如GDAL）可能需要额外的系统级依赖，如地理空间库（如PROJ和GEOS）。这些依赖可能需要单独安装，具体取决于你的操作系统。 如果你在Windows上遇到问题，并且pip安装失败，尝试从Unofficial Windows Binaries for Python Extension Packages网站下载相应的wheel文件，并使用pip进行安装。 脚本示例 虽然你的问题主要是关于如何安装geopandas，但如果你想要一个Python脚本来重命名文件夹下的文件，在原始名字前面加上字符串"geopandas"，以下是一个简单的示例： python import os # 指定文件夹路径 folder_path = 'path/to/your/folder' # 遍历文件夹中的文件 for filename in os.listdir(folder_path): # 构造原始文件路径 old_file_path = os.path.join(folder_path, filename) # 构造新文件名 new_filename = 'geopandas_' + filename # 构造新文件路径 new_file_path = os.path.join(folder_path, new_filename) # 重命名文件 os.rename(old_file_path, new_file_path) print(f'Renamed "{filename}" to "{new_filename}"') 请确保将'path/to/your/folder'替换为你想要重命名文件的实际文件夹路径。
设计模式-java实现.zip
最新发布
09-24
设计模式--java实现.zip
基于白鹭群优化算法ESOA-BiTCN-BiGRU-Attention的风电预测算法研究Matlab实现.rar
09-24
1.版本：matlab2014/2019a/2024a 2.附赠案例数据可直接运行matlab程序。 3.代码特点：参数化编程、参数可方便更改、代码编程思路清晰、注释明细。 4.适用对象：计算机，电子信息工程、数学等专业的大学生课程设计、期末大作业和毕业设计。 替换数据可以直接使用，注释清楚，适合新手
MySQL高性能索引策略：最左前缀原理与优化实践
"本文主要探讨MySQL的高性能索引策略，属于结构优化的一部分，旨在帮助读者理解和应用索引背后的机制，从而提升数据库性能。文中通过分析MySQL官方提供的employees示例数据库，解释了最左前缀原理及其在索引优化中的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值