Bagaimana untuk meningkatkan kelajuan pertanyaan mysql

Kaedah untuk meningkatkan kelajuan pertanyaan MySQL: 1. Pilih atribut medan yang paling sesuai; 2. Gunakan JOIN untuk menggantikan subqueries; pertanyaan dan cuba elakkan imbasan jadual penuh 6. Cuba gunakan pembolehubah jadual dan bukannya jadual sementara, dsb.

Persekitaran pengendalian tutorial ini: sistem windows7, versi mysql8, komputer Dell G3.

Sebab kelajuan pertanyaan lambat

Dari perspektif pengaturcara

• Pernyataan pertanyaan tidak ditulis dengan baik

• Indeks tidak dibina, indeks dibina secara tidak munasabah atau indeks tidak sah

• Terdapat terlalu banyak pertanyaan berkaitan menyertai

Dari perspektif pelayan

• Ruang cakera pelayan tidak mencukupi
• Penalaan pelayan Tetapan parameter konfigurasi optimum yang tidak munasabah

Lapan cara untuk mengoptimumkan pangkalan data MySQL

1 Pilih atribut medan yang paling sesuai

MySQL boleh menyokong akses sejumlah besar data, tetapi secara amnya, lebih kecil jadual dalam pangkalan data, lebih cepat pertanyaan dilaksanakan padanya. Oleh itu, apabila mencipta jadual, untuk mendapatkan prestasi yang lebih baik, kita boleh menetapkan lebar medan dalam jadual sekecil mungkin .

Sebagai contoh, apabila mentakrifkan medan kod pos, jika anda menetapkannya kepada CHAR(255), ia jelas akan menambah ruang yang tidak diperlukan pada pangkalan data Walaupun menggunakan VARCHAR adalah berlebihan, kerana CHAR(6 ) boleh melengkapkan tugas dengan baik sekali. Begitu juga, jika boleh, kita harus menggunakan MEDIUMINT dan bukannya BIGIN untuk menentukan medan integer.

Satu lagi cara untuk meningkatkan kecekapan ialah dengan menetapkan medan kepada NOTNULL apabila boleh, supaya pangkalan data tidak perlu membandingkan nilai NULL apabila melaksanakan pertanyaan pada masa hadapan.

Untuk sesetengah medan teks, seperti "wilayah" atau "jantina", kami boleh mentakrifkannya sebagai jenis ENUM. Oleh kerana dalam MySQL, jenis ENUM diproses sebagai data berangka dan data berangka diproses lebih cepat daripada jenis teks. Dengan cara ini, kami boleh meningkatkan prestasi pangkalan data.

2. Gunakan gabungan (JOIN) dan bukannya sub-pertanyaan (Sub-Queries)

MySQL menyokong subqueries SQL bermula dari 4.1. Teknik ini membolehkan anda menggunakan pernyataan SELECT untuk mencipta satu lajur hasil pertanyaan, dan kemudian menggunakan hasil ini sebagai syarat penapis dalam pertanyaan lain. Sebagai contoh, jika kami ingin memadamkan pelanggan yang tidak mempunyai sebarang pesanan dalam jadual maklumat pelanggan asas, kami boleh menggunakan subkueri untuk mendapatkan semula ID semua pelanggan yang mengeluarkan pesanan daripada jadual maklumat jualan dahulu, dan kemudian menyerahkan hasilnya kepada pertanyaan utama, seperti yang ditunjukkan di bawah :

DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo)

Menggunakan subquery boleh menyelesaikan banyak operasi SQL yang secara logiknya memerlukan beberapa langkah untuk diselesaikan pada satu masa Ia juga boleh mengelakkan transaksi atau kunci jadual, dan memang begitu juga mudah untuk menulis. Namun, dalam beberapa kes, subkueri boleh digantikan dengan gabungan yang lebih cekap (SERTAI).. Sebagai contoh, katakan kami ingin mengambil semua pengguna yang tidak mempunyai rekod pesanan, kami boleh menggunakan pertanyaan berikut untuk melengkapkan:

SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)

Jika anda menggunakan sambungan (SERTAI)... untuk selesaikan pertanyaan ini, kelajuannya akan menjadi lebih pantas . Terutama apabila terdapat indeks pada CustomerID dalam jadual info jualan, pertanyaannya adalah seperti berikut:

SELECT * FROM customerinfo LEFT JOIN salesinfo 
ON customerinfo.CustomerID=salesinfo.CustomerID 
WHERE salesinfo.CustomerID ISNULL

Sertai (JOIN Sebab mengapa ia lebih cekap ialah MySQL melakukannya). tidak perlu Buat jadual sementara dalam ingatan untuk menyelesaikan pertanyaan dua langkah logik ini.

3. Gunakan kesatuan (UNION) untuk menggantikan jadual sementara yang dibuat secara manual

MySQL menyokong pertanyaan kesatuan bermula dari versi 4.0, ia boleh Menggabungkan dua atau lebih pilih pertanyaan yang memerlukan penggunaan jadual sementara ke dalam satu pertanyaan. Apabila sesi pertanyaan pelanggan tamat, jadual sementara akan dipadamkan secara automatik untuk memastikan pangkalan data adalah kemas dan cekap. Apabila menggunakan kesatuan untuk membuat pertanyaan, kita hanya perlu menggunakan UNION sebagai kata kunci untuk menyambungkan berbilang penyata pilihan Perlu diingat bahawa bilangan medan dalam semua penyata pilihan mestilah sama. Contoh berikut menunjukkan pertanyaan menggunakan UNION.

SELECT Name,Phone FROM client UNION

SELECT Name,BirthDate FROM author UNION

SELECT Name,Supplier FROM product

4

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN; INSERT INTO salesinfo SET CustomerID=14; 
UPDATE inventory SET Quantity=11 WHERE item='book'; COMMIT;

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';

...

UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES

这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。

6、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。

CREATE  TABLE  customerinfo(CustomerIDINT  NOT    NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;

CREATE  TABLE  salesinfo(SalesIDNT  NOT  NULL,CustomerIDINT  NOT  NULL,

PRIMARYKEY(CustomerID,SalesID),

FOREIGNKEY(CustomerID)  REFERENCES  customerinfo(CustomerID)  ON  DELETE    CASCADE)TYPE=INNODB;

注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

28种 SQL 查询语句的优化方法：

1、应尽量避免在 where 子句中使用 != 或者 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null;

可以在 num 上设置默认值 0 ，确保表中 num 列没有 null 值，然后这样查询：

select id from t where num = 0;

3、查询语句的查询条件中只有OR关键字，并且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

4、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

5、前导模糊查询不能利用索引(like '%XX'或者like '%XX%')，可以使用索引覆盖避免。

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 < ’2005-12-1′;

10、不要在 where 子句中的 “=” 左边进行函数，算术运算或者其他表达式运算，否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12、很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b);

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num);

13、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。