MySQL索引和性能分析

MySQL索引

• 本文用python执行SQL语句。
  导入python库

import json
import pymysql
from pymysql.converters import escape_string
import pandas as pd

连接数据库

#连接数据库
conn=pymysql.connect(
    host="localhost",
    port=3306,#端口号
    user="root",#数据库用户
    password="123456",#数据库密码
    #database="world"#要连接的数据库名称
)
cur=conn.cursor()

跳转到指定数据库

sql = """USE PLJNB"""
cur.execute(sql)
cur.fetchall()

介绍

• 索引是帮助MySQL高效获取数据的数据结构（有序）
• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，以此实现高级查找算法。

优点

1. 提高数据检索的效率，降低数据库IO成本。
2. 降低数据排序成本，降低CPU消耗。

缺点

1. 索引列要占用空间。
2. 索引降低了更新表的速度。

索引结构

各种索引结构的特点

索引结构	特点
B+树索引	最常见的索引类型，大部分引擎都支持
HASH索引	只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree索引(空间索引)(B树)	MyISAM的一个特殊索引类型，主要用于地理空间数据类型，使用较少
Full-text(全文索引)	通过建立倒排索引，快速匹配文档的方式(ES)

各种存储引擎支持的索引结构

索引	InnoDB	MyISAM	Memory
B+Tree	支持	支持	支持
Hash	不支持	不支持	支持
R-Tree	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

索引分类

按索引字段约束分类

索引类别	关键字	特点
主键索引	PRIMARY	针对表中主键创建的索引，默认自动创建，只能有一个。
唯一索引	UNIQUE	避免同一个表中某列中的值重复，可以有多个
常规索引	无	快速定位特定数据，可以有多个

按数据结构和存储内容分类

索引类别	关键字	特点
全文索引	FULLTEXT	查找的是文本中的关键字，而不是比较索引中的值，可以有多个。
聚集索引	不一定	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，必须有，且只有一个。
二级索引	不一定	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个。

聚集索引选取规则(InnoDB)

1. 如果存在主键，主键索引就是聚集索引。
2. 如果不存在主键，将使用第一个唯一索引作为聚集索引。
3. 如果表没有主键也没有唯一索引，则会自动生成一个rowid作为隐藏的聚集索引。

索引语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 ON 表名(索引列名列表);

• 例1：创建常规索引

sql = """
       CREATE INDEX idx_emp_gender ON employee(gender);
"""
cur.execute(sql)
cur.fetchall()

• 例2：创建联合索引

sql = """
       CREATE INDEX idx_emp_age_gender ON employee(age,gender);
"""
cur.execute(sql)
cur.fetchall()

• 例3：创建唯一索引

sql = """
       CREATE UNIQUE INDEX idx_account_name ON account(name);
"""
cur.execute(sql)
cur.fetchall()

查看索引

SHOW INDEX FROM 表名;

例：

sql = """
       SHOW INDEX FROM employee; 
"""
cur.execute(sql)
pd.DataFrame(list(cur.fetchall()),columns=[i[0] for i in cur.description])

删除索引

DROP INDEX 索引名 ON 表名;

索引使用

最左前缀法则

• 查询从索引的最左列开始，SQL语句中最左字段存在即可。即索引查询中，SQL语句条件无先后顺序
• 不跳过索引中的列，若跳过，后面的字段索引失效。

范围查询

• 联合索引中，若出现范围查询(>,<)，右侧列索引(<)失效【不同字段间也要遵守此规则】
• 尽量用 >=、<=

索引列运算

• 索引不能运算，否则失效

字符串引号

• 字符串不加引号索引查询将失效

索引模糊匹配

• 尾部模糊匹配索引不会失效，头部模糊匹配索引失效

or连接的条件

• 若or前有索引，而or后没有索引，则设计的索引都不会用到

数据分布影响

• 若MySQL评估使用索引比全表扫描慢，则不使用索引（如查询全表）

SQL提示

• 是优化数据库的一个重要手段
• 是在SQL语句中加入提示来达到优化操作的目的
• SQL提示关键字

关键字	作用
USE INDEX	建议数据库用指定索引
IGNORE INDEX	忽略指定索引
FORCE INDEX	强制数据库用指定索引

• 语法：

SELECT 字段名 FROM 表名 USE|IGNORE|FORCE INDEX(索引名) WHERE 条件

覆盖索引

• 指查询的字段可以在索引中全部找到（索引涉及需要的全部字段），不用回表查数据。
• 尽量使用覆盖索引，减少SELECT *

前缀索引

• 当字段存储较长文本时，以该字段为索引会让索引变得很大，查询时效率降低。
• 前缀索引就是将字符串的一部分前缀建立索引，节约空间
• 前缀长度：
  根据索引的选择性（不重复索引值数量与总记录数的比值）来决定
• 语法：

CREATE INDEX 索引名 ON 表名(字段名(前缀长度));

• 求索引选择性：

SELECT COUT(DISTINCT 字段名) / count(*) FROM 表名;
SELECT COUT(DISTINCT SUBSTRING(字段名,起始位,长度)) / count(*) FROM 表名;

索引设计原则：

设计原则
针对数据量较大，且查询比较频繁的表建立索引。
针对经常作为查询条件、排序、分组操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高索引效率越高
如果是字符串类型的字段，且长度较长，可建立前缀索引
尽量使用联合索引，减少单列索引，查询时联合索引可使用覆盖索引
要控制索引的数量，索引越多，维护索引结构的代价也越大，还占用磁盘空间
如果索引列不能存储NULL值，建表时就使用NOT NULL约束。便于优化器判断索引有效性

SQL性能分析

SQL执行频率

• 查看数据各类操作频率。

SHOW GLOBAL STATUS LIKE 'COM_______';

慢查询日志

• 记录了所有执行时间超过指定阈值的SQL语句，默认不开启。

SHOW VARIABLE LIKE 'slow_query_log';

• 慢查询日志需要在MySQL配置文件(/etc/my.cnf)中配置：
  开启：令slow_query_log=1
  设置慢查询时间：long_query_time=指定阈值
• linux下慢查询日志文件位置：/var/lib/mysql/localhost-slow.log

profile详情

• 能帮助了解每条SQL的消耗时间。
• 查看当前MySQL是否支持profile操作：

SELECT @@have_profiling

• 在session或global级别开启profiling

SET session profiling = 1;

• 查看每一条SQL的耗时基本情况

SHOW PROFILES;

• 查看指定query_id的SQL语句各个阶段的耗时情况

SHOW PROFILE FOR QUERY query_id;

• 查看指定query_id的SQL语句CPU的使用情况

SHOW PROFILE CPU FOR QUERY query_id;

explain执行计划

• 可获取MySQL如何执行SELECT语句的信息，包括表连接顺序。

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

explain执行计划表字段含义

字段名	含义
id	id相同，则执行顺序为从上到下；id不同，则值大的先执行（如子查询）
select_type	说明查询类型
table
partitions
type	连接类型，性能从好到差：
possible_keys	可能应用在这张表上的索引，一个或多个
key	实际用到的索引，没有则NULL
key_len	索引使用的字节数（最大可能长度，不是实际长度）
ref
rows	MySQL认为必须要执行查询的行数，在InnoDB中是个估计值
filtered	返回结果的函数占需读行数的百分比，越大越好
Extra