select count(distinct col_name),... from table_name
,如性别的离散度比较低不适合做索引InnoDB
表的普通索引都会保存主键的值,所以主键要尽可能选择比较短的数据类型,可以有效地减少索引的磁盘占用,提高索引的缓存效果。 插件式存储引擎是MySQL
最重要的特性之一
优化表碎片:OPTIMIZE TABLE table_name
存储过程和函数是事先经过编译并存储在数据库中的一段SQL
语句的集合,可以减少开发人员很多工作;
lock tables
锁定用于当前线程的表。如果表被其他线程锁定,则当前线程会等待,直到可以获取所有锁定为止
事务控制:
start transaction/begin
...
commit/rollback
当前分布式事务只支持InnoDB
存储引擎。
Tips:如果想更清楚地了解SQL的执行过程:show profile for query
show [session|global] status
session:当前连接 global:自数据库上次启动至今
show status like 'Com_%';
通过以上几个参数,可以了解到当前数据库的应用是插入更新为主还是以查询操作为主。
各属性含义: id: 查询的序列号 select_type: 查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询
table: 输出的行所引用的表 type: 访问类型
从左至右,性能由差到好
select 1 from test where 1
possible_keys: 表示查询时可能使用的索引。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引
key: 显示MySQL实际决定使用的索引。如果没有索引被选择,是NULL
key_len: 使用到索引字段的长度
注:key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
ref: 显示哪个字段或常数与key一起被使用
rows: 这个数表示mysql要遍历多少数据才能找到,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,在innodb上可能是不准确的
Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。
索引是在MySQL的存储引擎层实现的,而不是在服务器层实现的,所以每种存储引擎的索引都不一定完全相同。
最左匹配原则可以算是MySQL中B-Tree索引使用的首要原则
以%
开头的like
查询不能够利用B-Tree
索引,执行计划中key
的值为NULL
表示没有使用索引
数据类型出现隐式转换的时候也不会使用索引,特别是当列类型是字符串,那么一定记得在where条件中把字符常量用引号引起来。如select * from test where last_name='1';
用or
分割的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及的索引都不会被用到。因为or后面的条件列没有索引,那么后面的查询肯定走全表扫描,在存在全表扫描的情况下,就没有必要多一次索引扫描增加I/O
访问,一次全表扫描过滤条件就足够了。
查看索引使用情况:
show [global] status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 2225 |
| Handler_read_key | 2486 |
| Handler_read_last | 0 |
| Handler_read_next | 78 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 2 |
| Handler_read_rnd_next | 47857 |
+-----------------------+-------+
Handler_read_rnd_next
的值高则意味着查询运行低效,并且应该建立索引补救
InnoDB类型的表是按照主键的顺序保存的。 优化insert语句,如果同时从同一个客户端插入多行,应尽量使用多个值表的insert语句,这种方式大大缩减客户端与数据库之间的连接、关闭等消耗
优化嵌套查询:有些情况下,子查询可以被更有效的连接(join)代替。连接(join)之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作
优化分页查询:消息私信MySQL的limit用法和分页查询的性能分析及优化
InnoDB采用redo log机制来保证事务更新的一致性和持久性
在MySQL中有4种不同的日志:错误日志、二进制日志(BINLOG)、查询日志和慢查询日志
二进制日志记录了所有的DDL和DML语句,但是不包括数据查询语句。语句以“事件”的形式保存,它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。
由于二进制日志以二进制存储,不能直接读取,需要用mysqlbinlog
工具来查看:mysqlbinlog log-file
备份要在系统负载较小的时间进行
备份数据库test:
mysqldump -uroot -p test > test.sql
备份数据库test下的表emp:
mysqldump -uroot -p test emp > emp.sql
恢复:
mysqldump -uroot -p daname < bakfile
物理备份:冷备份(停掉数据库)和热备份(本质是将要备份的表加锁)