mysql 优化
发布时间：2023-02-08 12:51:54 所属栏目：MySql教程 来源：
导读： 一、sql执行顺序
(1)from
(3) join
(2) on
(4) where
(5)group by(开始使用select中的别名，后面的语句中都可以使用)
(6) avg,sum....
(7)having
(1)from
(3) join
(2) on
(4) where
(5)group by(开始使用select中的别名，后面的语句中都可以使用)
(6) avg,sum....
(7)having
|
一、sql执行顺序 (1)from (3) join (2) on (4) where (5)group by(开始使用select中的别名，后面的语句中都可以使用) (6) avg,sum.... (7)having (8) select (9) distinct (10) order by --------------------------------------------------------------------------------------- mysq建立索引的几大原则: 1．选择唯一性索引 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。 2．为经常需要排序、分组和联合操作的字段建立索引 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。 3．为常作为查询条件的字段建立索引 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。 4．限制索引的数目 索引的数目不是越多越好。 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。 5．尽量使用数据量少的索引 如果索引的值很长，那么查询的速度会受到影响。 例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。 6．尽量使用前缀来索引 如果索引字段的值很长，最好使用值的前缀来索引。 例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。 7．删除不再使用或者很少使用的索引 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。 8 . 最左前缀匹配原则，非常重要的原则。 mysql会一直向右匹配直到遇到范围查询(>、 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。 9 .=和in可以乱序。 比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式 10 . 尽量选择区分度高的列作为索引。 区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录 11 .索引列不能参与计算，保持列“干净”。 比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); 12 .尽量的扩展索引，不要新建索引。 比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可 13、当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率 注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。 标准sql执行顺序 -------------------------------------------------------------------------------------------------------------------------------------浅谈MySQL中优化sql语句查询常用的30种方法 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。 3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0 4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20 5.下面的查询也将导致全表扫描： select id from t where name like '%abc%' 若要提高效率，可以考虑全文检索。 6.in 和 not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3 7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： select id from t where num=@num 可以改为强制查询使用索引： select id from t with(index(索引名)) where num=@num 8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where num/2=100 应改为: select id from t where num=100*2 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where substring(name,1,3)='abc'--name以abc开头的id select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id 应改为: select id from t where name like 'abc%' select id from t where createdate>='2005-11-30' and createdate explain select * from t_score; +----+------------+---------+------+--------------+------+---------+------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+------------+---------+------+--------------+------+---------+------+-------+-------+ | 1 | SIMPLE | t_score | ALL | NULL | NULL | NULL | NULL | 12 | | +----+------------+---------+------+--------------+------+---------+------+-------+-------+ expain出来的信息有10列mysql 优化，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra 概要描述： id:选择标识符 select_type:表示查询的类型。 table:输出结果集的表 partitions:匹配的分区 type:表示表的连接类型 possible_keys:表示查询时，可能使用的索引 key:表示实际使用的索引 key_len:索引字段的长度 ref:列与索引的比较 rows:扫描出的行数(估算的行数) filtered:按表条件过滤的行百分比 Extra:执行情况的描述和说明 对表访问方式， 表示MySQL在表中找到所需行的方式，又称“访问类型”。 常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好） ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行 index: Full Index Scan，index与ALL区别为index类型只遍历索引树 range:只检索给定范围的行，使用一个索引来选择行 ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件 const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。 ----------------------------------------------------------------------------------------------------------------------------- 存储引擎 MyISAM 它是MySQL5.5之前的默认存储引擎 优势：访问速度快 适用场景：对事务的完整性没有要求，或以select、insert为主的应用基本都可以选用MYISAM。在Web、数据仓库中应用广泛。 InnoDB MySQL5.5之后的默认存储引擎 应用场景：如果应用对事务的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作中包含读、插入、删除、更新，那InnoDB是最好的选择。在计费系统、财务系统等对数据的准确性要求较高的系统中被广泛应用。 优点：提供了具有提交（Commit）、回滚（Rollback）、崩溃恢复能力的事务安全，支持外键。 缺点：相比较于MyISAM，写的处理效率差一点，并且会占用更多的磁盘空间来存储数据和索引 ------------------------------------------------------------------------------------------------------------------------------------- 索引 Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引 PRIMARY KEY（主键索引） ALTER TABLE `table_name` ADD PRIMARY KEY ( `col` ) UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`col`) INDEX(普通索引) ALTER TABLE `table_name` ADD INDEX index_name (`col`) FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `col` ) 组合索引 ALTER TABLE `table_name` ADD INDEX index_name (`col1`, `col2`, `col3` ) Mysql各种索引区别： 普通索引：最基本的索引，没有任何限制 唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。 主键索引：它 是一种特殊的唯一索引，不允许有空值。 全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。 组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。 组合索引最左字段用in是可以用到索引的，最好explain一下select。 -------------------------------------------------------------------------------------------------------------------------------- sql优化: A 查看sql执行效率: show status like 'cim_%'; com_select : 执行select 操作的次数 ，一次查询累加 对于事务型的应用 。通过com_commit 和 com_rollback 可以了解事务提交和回滚的情况， 对于回滚操作非常频繁的数据库， 可能意味着应用编写存在问题。 2定位执行效率比较低的sql语句 1 通过慢查询日志定位慢sql 2 使用show full processlist 查看当前mysql在进行的线程， 同时对一些锁表操作进行优化 3 通过explain 分析慢sql 语句 4 通过show profile 分析sql 查看当前mysql是否支持profile 默认profiling 是关闭的， 可以通过set 语句在 session 级别开启profiling: set profiling=1; （编辑：我爱资讯网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐