mysql优化问题
mysql关键字执行顺序
from > on > join > where > group by > having > select > distinct > union > order by
sql语句优化
(1)使用limit对查询结果的记录进行限定
(2)避免select *,将需要查找的字段列出来
(3)使用连接(join)来代替子查询
(4)拆分大的delete或insert语句
选择合适的数据类型
(1)使用可存下数据的最小的数据类型,整型 < date,time < char,varchar < blob
(2)使用简单的数据类型,整型比字符处理开销更小,因为字符串的比较更复杂。如,int类型存储时间类型,bigint类型转ip函数
IPv4地址。人们常用VARCHAR(15)列来存储IP地址。然而,它们实际上是32们无符号整数,不是字符串。用小数点将地址分成四段的表示方法只是让人们阅读容易 。所以应该用无符号整数 存储IP地址。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。
例:select inet_aton(‘192.168.222.255‘); 输出:3232292607
select inet_ntoa(3232292607); 输出:192.168.222.255
(3)使用合理的字段属性长度,固定长度的表会更快。使用enum、char而不是varchar
(4)尽可能使用not null定义字段
(5)尽量少用text,非用不可最好分表
使用命令分析
1、show查看状态
1) 显示状态信息
SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';
session(默认):取出当前窗口的执行
global:从mysql启动到现在
(1)查看查询次数(插入次数com_insert、修改次数com_insert、删除次数com_delete)
SHOW STATUS LIKE 'com_select';
(2)查看连接数(登录次数)
SHOW STATUS LIKE 'connections';
(3)数据库运行时间
SHOW STATUS LIKE 'uptime';
(4)查看慢查询次数
SHOW STATUS LIKE 'slow_queries';
(5)查看索引使用的情况:
SHOW STATUS LIKE 'handler_read%';
handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高,说明查询低效。
2、显示系统变量
SHOW ENGINE INNODB STATUS;
3、EXPLAIN分析查询
EXPLAIN SELECT column_name FROM table_name;
explain查询sql执行计划,各列含义:
table:表名;
type:连接的类型
-const:主键、索引;
-eq_reg:主键、索引的范围查找;
-ref:连接的查找(join)
-range:索引的范围查找;
-index:索引的扫描;
-all:全表扫描;
possible_keys:可能用到的索引;
key:实际使用的索引;
key_len:索引的长度,越短越好;
ref:索引的哪一列被使用了,常数较好;
rows:mysql认为必须检查的用来返回请求数据的行数;
extra:using filesort、using temporary(常出现在使用order by时)时需要优化。
-Using filesort 额外排序。看到这个的时候,查询就需要优化了
-Using temporary 使用了临时表。看到这个的时候,也需要优化
3、PROFILING分析SQL语句
1)开启profile。查看当前SQL执行时间
SET PROFILING=ON;
SHOW profiles;
2)查看所有用户的当前连接。包括执行状态、是否锁表等
SHOW processlist;
4、PROCEDURE ANALYSE()取得建议
通过分析select查询结果对现有的表的每一列给出优化的建议
SELECT column_name FROM table_name PROCEDURE ANALYSE();
5、OPTIMIZE TABLE回收闲置的数据库空间
OPTIMIZE TABLE table_name;
对于MyISAM表,当表上的数据行被删除时,所占据的磁盘空间并没有立即被回收,使用命令后这些空间将被回收,并且对磁盘上的数据行进行重排(注意:是磁盘上,而非数据库)。
对于InnoDB表,OPTIMIZE TABLE被映射到ALTER TABLE上,这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。
只需在批量删除数据行之后,或定期(每周一次或每月一次)进行一次数据表优化操作即可,只对那些特定的表运行。
6、REPAIR TABLE修复被破坏的表
REPAIR TABLE table_name;
7、CHECK TABLE检查表是否有错误
CHECK TABLE table_name;
mysql索引类型
1.普通索引,是最基本的索引,它没有任何限制。
(1)直接创建索引
CREATE INDEX index_name ON table(column(length))
(2)修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
(3)创建表的时候同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)
(4)删除索引
DROP INDEX index_name ON table
2.唯一索引,与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
(1)创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))
(2)修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
(3)创建表的时候直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
UNIQUE indexName (title(length))
);
3.主键索引,是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) NOT NULL ,
PRIMARY KEY (`id`)
);
4.组合索引
指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
5.全文索引
主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。
(1)创建表的适合添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER NOT NULL ,
`content` text CHARACTER NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
(2)修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
(3)直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)
索引的缺点
1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete。因为更新表时,不仅要保存数据,还要保存一下索引文件。
2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会增长很快。
索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
索引失效的情况(在表中建立索引,优先考虑where、group by使用到的字段)
1、如果where中有or,及时其中有条件带索引也不会使用(若想使用or且还想用到索引,只能在or条件的每个列都加上索引),尽量避免使用in 和not in,会导致数据库引擎放弃索引进行全表扫描
2、对于多列索引,不是使用的第一部分,则不会使用索引
3、like查询是以%开头
4、存在隐式转换,如果列列类型是字符串,一定要在条件将数据使用引号引用起来,否则不使用索引
5、mysql估计使用全表扫描比使用索引快,则不使用索引(数据量较小的时候)
6、where条件中使用函数或运
7、范围条件查询导致索引失效
什么样的列不适合加索引
1、唯一性差
2、where条件中不使用的字段
3、使用<>时,效果一般(如果where后含有is null/is not null/like '%值%/where 1 = 1)
4、频繁更新不用的字段,增加数据库的工作量,降低效率
为什么说B+ 树比B 树更适合实际应用中操作系统的文件索引和数据库索引?
B树:有序数组+平衡多叉树
B+树:有序数组链表+平衡多叉树;
1)B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
2) B+-tree的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
3)由于B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,所以通常B+树用于数据库索引。
详情参考:https://www.cnblogs.com/tiancai/p/9024351.html