一切福田，不離方寸，從心而覓，感無不通。

Category Archives: MySQL

MySQL优化

一、SQL语句优化 （1）使用limit对查询结果的记录进行限定 （2）避免select *，将需要查找的字段列出来 （3）使用连接（join）来代替子查询 （4）拆分大的delete或insert语句 二、选择合适的数据类型 （1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob （2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数 （3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar （4）尽可能使用not null定义字段 （5）尽量少用text，非用不可最好分表 三、选择合适的索引列 （1）查询频繁的列，在where，group by，order by，on从句中出现的列 （2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列 （3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好 （4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：

1
mysql> SELECT COUNT(DISTINCT column_name) FROM table_name;

四、使用命令分析 （1）SHOW查看状态 1.显示状态信息

1
mysql> SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';

session（默认）：取出当前窗口的执行 global：从mysql启动到现在 （a）查看查询次数（插入次数com_insert、修改次数com_insert、删除次数com_delete）

1
mysql> SHOW STATUS LIKE 'com_select';

（b）查看连接数(登录次数)

1
mysql> SHOW STATUS LIKE 'connections';

（c）数据库运行时间

1
mysql> SHOW STATUS LIKE 'uptime';

（d）查看慢查询次数

1
mysql> SHOW STATUS LIKE 'slow_queries';

（e）查看索引使用的情况：

1
mysql> SHOW STATUS LIKE 'handler_read%';

handler_read_key：这个值越高越好，越高表示使用索引查询到的次数。 handler_read_rnd_next：这个值越高，说明查询低效。 2.显示系统变量

1
mysql> SHOW VARIABLES LIKE '%Variables_name%';

3.显示InnoDB存储引擎的状态

1
mysql> SHOW ENGINE INNODB STATUS;

（2）EXPLAIN分析查询

1
mysql> 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执行时间

1
2
mysql> SET PROFILING=ON;
mysql> SHOW profiles;

2.查看所有用户的当前连接。包括执行状态、是否锁表等

1
mysql> SHOW processlist;

（4）PROCEDURE ANALYSE()取得建议 通过分析select查询结果对现有的表的每一列给出优化的建议

1
mysql> SELECT column_name FROM table_name PROCEDURE ANALYSE();

（5）OPTIMIZE TABLE回收闲置的数据库空间

1
mysql> OPTIMIZE TABLE table_name;

[…]

龙生   15 Dec 2020
View Details

MySQL索引类型

一、简介 MySQL目前主要有以下几种索引类型： 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引 二、语句

1
2
CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引 2.index和key为同义词，两者作用相同，用来指定创建索引 3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择 4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值 5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度 6.asc或desc指定升序或降序的索引值存储 三、索引类型 1.普通索引 是最基本的索引，它没有任何限制。它有以下几种创建方式： （1）直接创建索引

1
CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

1
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

1
2
3
4
5
6
7
8
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）删除索引

1
DROP INDEX index_name ON table

2.唯一索引 与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式： （1）创建唯一索引

1
CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

1
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

1
2
3
4
5
6
7
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.主键索引 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

1
2
3
4
5
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4.组合索引 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

1
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）创建表的适合添加全文索引

1
2
3
4
5
6
7
8
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）修改表结构添加全文索引

1
ALTER TABLE article ADD FULLTEXT index_content(content)

（3）直接创建索引

1
CREATE FULLTEXT INDEX index_content ON article(content)

四、缺点 1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。 2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。 索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。 五、注意事项 使用索引时，有以下一些技巧和注意事项： 1.索引不会包含有null值的列 只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。 2.使用短索引 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 3.索引列排序 查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。 4.like语句操作 一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。 5.不要在列上进行运算 这将导致索引失效而进行全表扫描，例如

1
SELECT * FROM table_name WHERE YEAR(column_name)&lt;2017;

6.不使用not in和<>操作   from:https://www.cnblogs.com/luyucheng/p/6289714.html

龙生   15 Dec 2020
View Details

MySql连接数据库

C#中连接MySql数据库，需要配置MySql.Data。   1.配置 项目- 管理NuGet程序包 – 安装MySql.Data。 2.示例代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
using MySql.Data.MySqlClient;
using System;
namespace MySQL
{
    class Program
    {
        static void Main(string[] args)
        {
            string server = "172.0.0.1";
            string database = "test";
            string uid = "root";
            string password = "123456";
            string SslMode = "none";
            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";" + "SslMode=" + SslMode;
            MySqlConnection mycon = new MySqlConnection(connectionString);
            mycon.Open();
            MySqlCommand mycmd = new MySqlCommand("insert into buyer(name,password,email) values('小王','dikd3939','1134384387@qq.com')", mycon);
            if (mycmd.ExecuteNonQuery() > 0)
            {
                Console.WriteLine("数据插入成功！");
            }
            Console.ReadLine();
            mycon.Close();          
        }
    }
}

  3.注意事项 运行上述代码，如果出现异常 “IOException: Unable to read data from the transport connection: 由于连接方在一段时间后没有正确答复或连接的主机没有反应，连接尝试失败”。首先，确认数据库是否允许远程连接；其次，防火墙是否打开；最后，连接字符串是否正确，是否支持SSL。 出现异常“The host localhost does not support SSL connections.”说明不支持SSL，需要在连接字符串里添加SslMode = "none"。   from:https://blog.csdn.net/liyazhen2011/article/details/82845279

龙生   04 Dec 2020
View Details

MySQL 截取部分日期

使用  DATE_FORMAT(get_date, '%Y-%m-%d') 函数截取。 其中：get_date 是需要截取的字段名；’%Y-%m-%d' 是截取后的日期格式。 select date_format('1997-10-04 22:23:00′,’%y %M %b %D %W %a %Y-%m-%d %H:%i:%s %r %T'); 结果：97 October Oct 4th Saturday Sat 1997-10-04 22:23:00 10:23:00 PM 22:23:00 get_date = "2006-12-07" SELECT count(*) FROM t_get_video_temp Where DATE_FORMAT(get_date, '%Y-%m-%d')=’2006-12-07′; SELECT count(*) FROM t_get_video_temp Where get_date like '2006%-07%'; from:https://blog.csdn.net/hlbt0112/article/details/50600191/

龙生   19 Nov 2020
View Details

mysql中去重 distinct 用法

在使用mysql时，有时需要查询出某个字段不重复的记录，这时可以使用mysql提供的distinct这个关键字来过滤重复的记录，但是实际中我们往往用distinct来返回不重复字段的条数（count(distinct id)）,其原因是distinct只能返回他的目标字段，而无法返回其他字段，例如有如下表user: 用distinct来返回不重复的用户名：select distinct name from user;，结果为： 这样只把不重复的用户名查询出来了，但是用户的id，并没有被查询出来：select distinct name,id from user;，这样的结果为： distinct name,id 这样的mysql 会认为要过滤掉name和id两个字段都重复的记录，如果sql这样写：select id,distinct name from user，这样mysql会报错，因为distinct必须放在要查询字段的开头。 所以一般distinct用来查询不重复记录的条数。 如果要查询不重复的记录，有时候可以用group by ： select id,name from user group by name;   from:https://www.cnblogs.com/lxwphp/p/11339949.html

龙生   17 Nov 2020
View Details

MYSQL把查询多个的结果用逗号隔开，放在一个列里

mysql关键字：GROUP_CONCAT select name from 表1 t1 INNER JOIN 表2 t2 on t1.id=t2.word_id（关联字段） 结果： 2.select GROUP_CONCAT(name) from word t1 INNER JOIN relation_word_customer t2 on t1.id=t2.word_id 结果   from:https://blog.csdn.net/my_sweetxue/article/details/106380267

龙生   17 Nov 2020
View Details

MySQL 错误1418 的原因分析及解决方法

具体错误： 使用mysql创建、调用存储过程，函数以及触发器的时候会有错误符号为1418错误。 [Err] 1418 – This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) 查阅相关资料，意思是说binlog启用时，创建的函数没有声明类型，因为binlog在主从复制需要知道这个函数创建语句是什么类型，否则同步数据会有不一致现象。 mysql开启了bin-log, 我们就必须指定我们的函数是否是哪种类型： 1 DETERMINISTIC 不确定的 2 NO SQL 没有SQl语句，当然也不会修改数据 3 READS SQL DATA 只是读取数据，当然也不会修改数据 4 MODIFIES SQL DATA 要修改数据 5 CONTAINS SQL 包含了SQL语句 为了解决这个问题，MySQL强制要求： 在主服务器上，除非子程序被声明为确定性的或者不更改数据，否则创建或者替换子程序将被拒绝。这意味着当创建一个子程序的时候，必须要么声明它是确定性的，要么它不改变数据。 声明方式有两种： 第一种:声明是否是确定性的 DETERMINISTIC和NOT DETERMINISTIC指出一个子程序是否对给定的输入总是产生同样的结果。 如果没有给定任一特征，默认是NOT DETERMINISTIC，所以必须明确指定DETERMINISTIC来声明一个子程序是确定性的。 这里要说明的是:使用NOW() 函数（或它的同义）或者RAND() 函数不会使一个子程序变成非确定性的。对NOW()而言，二进制日志包括时间戳并会被正确的执行。RAND()只要在一个子程序内被调用一次也可以被正确的复制。所以，可以认为时间戳和随机数种子是子程序的确定性输入，它们在主服务器和从服务器上是一样的。 第二种:声明是否会改变数据 CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL用来指出子程序是读还是写数据的。 无论NO SQL还是READS SQL DATA都指出，子程序没有改变数据，但是必须明确地指定其中一个，因为如果任何指定，默认的指定是CONTAINS SQL。 默认情况下，如果允许CREATE PROCEDURE 或CREATE […]

龙生   15 Oct 2020
View Details

MySQL表名转大写

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_Uppercase`(IN `dbname` varchar(200))
BEGIN
DECLARE done INT DEFAULT 0;  
 
DECLARE oldname VARCHAR(200);  
 
DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = dbname;  
 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
 
OPEN cur;  
 
REPEAT  
 
FETCH cur INTO oldname;  
 
SET @newname = UPPER(oldname);  
SET @isNotSame = @newname <> BINARY oldname;  
IF NOT done && @isNotSame THEN  
SET @SQL = CONCAT('rename table `',oldname,'` to `', LOWER(@newname), '_tmp` ');  
PREPARE tmpstmt FROM @SQL;  
EXECUTE tmpstmt;  
 
SET @SQL = CONCAT('rename table `',LOWER(@newname),'_tmp` to `',@newname, '`');  
PREPARE tmpstmt FROM @SQL;  
EXECUTE tmpstmt;
 
DEALLOCATE PREPARE tmpstmt;  
 
END IF;  
UNTIL done END REPEAT;  
CLOSE cur;  
END//
DELIMITER ;

 

龙生   11 Sep 2020
View Details

MySQL锁总结

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。 加锁是消耗资源的，锁的各种操作，包括获得锁、检测锁是否是否已解除、释放锁等。 锁机制 共享锁与排他锁 共享锁（读锁）：其他事务可以读，但不能写。 排他锁（写锁） ：其他事务不能读取，也不能写。 粒度锁 MySQL 不同的存储引擎支持不同的锁机制，所有的存储引擎都以自己的方式显现了锁机制，服务器层完全不了解存储引擎中的锁实现： MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking） BDB 存储引擎采用的是页面锁（page-level locking），但也支持表级锁 InnoDB 存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 默认情况下，表锁和行锁都是自动获得的， 不需要额外的命令。 但是在有的情况下， 用户需要明确地进行锁表或者进行事务的控制， 以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。 不同粒度锁的比较： 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。 表级锁更适合于以查询为主，并发用户少，只有少量按索引条件更新数据的应用，如Web 应用 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 最大程度的支持并发，同时也带来了最大的锁开销。 在 InnoDB 中，除单个 SQL 组成的事务外， 锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。 行级锁只在存储引擎层实现，而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。 MyISAM 表锁 MyISAM表级锁模式： 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求； 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作； MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。 默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。 （This ensures that updates to a table are not “starved” even when there is heavy SELECT activity for the table. However, if there are many updates for […]

龙生   25 Aug 2020
View Details

asp.net+mysql解决Incorrect string value: '\xF0\x9F\x98\xAD

ALTER DATABASE 数据库 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ALTER TABLE 表 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 数据库连接字符串加上：charset=utf8mb4    

龙生   13 Apr 2020
View Details
1 … 5 6 7 … 22

Copyright © 2024 Longsheng All rights reserved