索引
索引失效
OR操作符
- OR条件的两边都是同一个索引列的情况下,如果WHERE条件是主键,则可以使用索引
- OR条件的两边都是同一个索引列的情况下,如果WHERE条件不是主键,则是否使用索引取决于MySQL查询优化器的代价估算。
- OR条件的两边是不同的索引列,是否使用索引取决于MySQL查询优化器的代价估算。如果能使用索引,MySQL会使用索引,如果代价太高,仍然会走全表索引
- 如果多个OR条件中有其中一个条件没有索引,则必须进行全表索引
索引底层原理
索引分类
1)从存储结构上来划分
- Btree 索引(B+tree,B-tree)
- 哈希索引
- full-index 全文索引
2)从应用层次上来划分
- 主键索引:主键索引
- 普通索引:即一个索引只包含单个列,一个表可以有多个单列索引。
- 唯一索引:索引列的值必须唯一,但允许有空值。
- 复合索引:一个索引包含多个列。
3)从表记录的排列顺序和索引的排列顺序是否一致来划分
- 聚集索引:表记录的排列顺序和索引的排列顺序一致。
- 非聚集索引:表记录的排列顺序和索引的排列顺序不一致。
索引建立原则
- 最左前缀匹配原则
- 尽量选择区分度高的列作为索引
- 索引列不能参与计算
- 尽量的扩展索引,不要新建索引
存储引擎
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能
" | MyISAM | InnoDB |
---|---|---|
存储结构 | 每张表被存放在三个文件:frm - 格定义,MYD (MYData)- 数据文件,MYI (MYIndex)- 索引文件 | 所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB |
存储空间 | MyISAM 可被压缩,存储空间较小 | InnoDB 的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
可移植性、备份及恢复 | 由于 MyISAM 的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 | 免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十 G 的时候就相对痛苦了 |
事务安全 | 不支持 每次查询具有原子性 | 支持 具有事务 (commit)、回滚 (rollback) 和崩溃修复能力 (crash recovery capabilities) 的事务安全 (transaction-safe (ACID compliant)) 型表 |
AUTO_INCREMENT | MyISAM 表可以和其他字段一起建立联合索引 | InnoDB 中必须包含只有该字段的索引 |
SELECT | MyISAM 更优 | |
INSERT | InnoDB 更优 | |
UPDATE | InnoDB 更优 | |
DELETE | InnoDB 更优 它不会重新建立表,而是一行一行的删除 | |
COUNT without WHERE | MyISAM 更优。因为 MyISAM 保存了表的具体行数 | InnoDB 没有保存表的具体行数,需要逐行扫描统计,就很慢了 |
COUNT with WHERE | 一样 | 一样,InnoDB 也会锁表 |
锁 | 只支持表锁 | 支持表锁、行锁 行锁大幅度提高了多用户并发操作的性能。但是 InnoDB 的行锁,只是在 WHERE 的主键是有效的,非主键的 WHERE 都会锁全表的 |
外键 | 不支持 | 支持 |
FULLTEXT 全文索引 | 支持 | 不支持(5.6.4 以上支持英文全文索引) 可以通过使用 Sphinx 从 InnoDB 中获得全文索引,会慢一点 |
索引 | 支持 B-tree/FullText/R-tree 索引类型 | 支持 Hash/B-tree 索引类型 |
锁
事务
事务的特性(ACID)
原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)
隔离级别
- 读未提交( read-uncommitted )
- 读已提交( read-committed )
- 可重复读( repeatable-read )
- 串行化 ( serializable )
隔离级别产生问题
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交( read-uncommitted ) | 是 | 是 | 是 |
读已提交( read-committed ) | - | 是 | 是 |
可重复读( repeatable-read ) | - | - | 是 |
串行化( serializable ) | - | - | - |
字符集&字段类型
MySql 类型名 | GetColumnClassName 返回值 | 返回的 Java 类 |
---|---|---|
bit(1)(MySQL-5.0) | BIT | java.lang.Boolean |
bit(大于1)(MySQL-5.0) | BIT | byte[] |
tinyint | TINYINT | 如果 tinyInt1isBit 配置设置为 true(默认为 true),是java.lang.Boolean,存储空间为 1;否则是为 java.lang.Integer |
bool | boolean | TINYINT 参见 TINYINT。这些是 TINYINT(1) 另一种写法 |
smallint(M) | [unsigned] | SMALLINT [UNSIGNED] java.lang.Integer(不管是否无符) |
mediumint(M) | [unsigned] | MEDIUMINT [UNSIGNED] java.lang.Integer |
int integer(M) | [unsigned] | INTEGER [UNSIGNED] java.lang.Integer;无符的话是 java.lang.Long |
bigint(M) | [unsigned] | BIGINT [UNSIGNED] java.lang.Long;无符的话是 java.math.BigInteger |
float(M,D) | FLOAT | java.lang.Float |
double(M,B) | DOUBLE | java.lang.Double |
decimal(M,D) | DECIMAL | java.math.BigDecimal |
date | DATE | java.sql.Date |
datetime | DATETIME | java.sql.Timestamp |
timestamp(M) | TIMESTAMP | java.sql.Timestamp |
time | TIME | java.sql.Time |
year(2/4) | YEAR | 如果 yearIsDateType 配置设置为 false,返回的对象类型为 java.sql.Short;如果设置为 true(默认为 true),返回的对象类型是 java.sql.Date,其具体时间是为一月一日零时零分 |
char(M) | CHAR | java.lang.String(除非该列字符集设置为 BINARY,那样返回 byte[]) |
varchar(M) | [binary] | VARCHAR java.lang.String(除非该列字符集设置为 BINARY,那样返回 byte[]) |
binary(M) | BINARY | byte[] |
varbinary(M) | VARBINARY | byte[] |
tinyblob | TINYBLOB | byte[] |
tinytext | VARCHAR | java.lang.String |
blob | BLOB | byte[] |
text | VARCHAR | java.lang.String |
mediumblob | MEDIUMBLOB | byte[] |
mediumtext | VARCHAR | java.lang.String |
longblob | LONGBLOB | byte[] |
longtext | VARCHAR | java.lang.String |
enum(‘value1’,‘value2’,…) | CHAR | java.lang.String |
set(‘value1’,‘value2’,…) | CHAR | java.lang.String |
高质量高性能sql规范建议
索引建立原则
索引失效情况
减少数据查询与返回,批量
字段类型建立原则
- 查询 SQL 尽量不要使用 select *,而是 select 具体字段
减少网络开销
可能就不会使用到覆盖索引了,就会造成回表查询
- 如果知道查询结果只有一条或者只要最大 / 最小一条记录,无索引情况建议用 limit 1,防止全表扫描
- 应尽量避免在where子句中使用or来连接条件,or可能会使索引失效,从而全表扫描
- 优化limit分页
//方案一 :返回上次查询的最大记录(偏移量)
select id,name from employee where id>10000 limit 10.
//方案二:order by + 索引
select id,name from employee order by id limit 10000,10
- 优化你的like语句,最左原则
- 使用where条件限定要查询的数据,避免返回多余的行
- 避免在索引列上使用mysql的内置函数
- 避免在 where 子句中对字段进行表达式操作,这将导致系统放弃使用索引而进行全表扫
- Inner join 、left join、right join,优先使用Inner join,如果是left join,左边表结果尽量小
- 避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
- 使用联合索引时,注意索引列的顺序,一般遵循最左匹配原则
- 对查询进行优化,应考虑在 where 及 order by 涉及的列上建立索引,尽量避免全表扫描
- 如果插入数据过多,考虑批量插入
- 在适当的时候,使用覆盖索引
- 慎用distinct关键字,过滤的过程会占用系统资源,cpu时间
- 删除冗余和重复索引
- 如果数据量较大,优化你的修改/删除语句,建议分批操作
- where子句中考虑使用默认值代替null
如果把null值,换成默认值,很多时候让走索引成为可能,同时,表达意思会相对清晰一点
如果mysql优化器发现,走索引比不走索引成本还要高,肯定会放弃索引,这些条件!=,>is null,is not null经常被认为让索引失效,其实是因为一般情况下,查询的成本高,优化器自动放弃的
- 不要有超过5个以上的表连接
- exist & in的合理利用,小表驱动大表,小的数据集驱动大的数据集
- 检索结果中不会有重复的记录,尽量用 union all 替换 union
- 索引不宜太多,一般5个以内,索引虽然提高了查询的效率,但是也降低了插入和更新的效率,可能会重建索引
- 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,相对于数字型字段,字符型会降低查询和连接的性能,并会增加存储开销
- 索引不适合建在有大量重复数据的字段上,如性别这类型数据库字段
- 尽量避免向客户端返回过多数据量,分页查询
- 当在SQL语句中连接多个表时,请使用表的别名,并把别名前缀于每一列上,这样语义更加清晰
- 尽可能使用varchar/nvarchar 代替 char/nchar,变长字段存储空间小,可以节省存储空间,对于查询来说,在一个相对较小的字段内搜索,效率更高
- 为了提高group by 语句的效率,可以在执行到该语句前,把不需要的记录过滤掉
- 如何字段类型是字符串,where时一定用引号括起来,否则索引失效,不加单引号时,是字符串跟数字的比较,它们类型不匹配,MySQL会做隐式的类型转换
- 使用explain 分析你SQL的计划